Как копировать формулы в Excel

3 методика:Копирование формулы в несколько ячеекТочное копирование формулыИспользование относительных и абсолютных ссылок на ячейки

В Excel вы можете запросто скопировать формулу в масштабах всей строки или столбца, но вы не всегда получите ожидаемые результаты. Если в итоге вы получили не то, что нужно, или ошибки #REF и /DIV0, прочитайте об абсолютных и относительных ссылках на ячейки, чтобы понять, что пошло не так. Но не волнуйтесь: вам не нужно редактировать все ячейки в таблице с 5000 строками, прежде чем вы сможете скопировать и вставить формулу еще раз. Есть простые способы автоматического обновления формулы на основе ее расположения, или можно скопировать формулу, не меняя ни одного значения.

Шаги

Метод 1 из 3: Копирование формулы в несколько ячеек

1. 1 Введите формулу в одну ячейку. Начните с «=», а затем введите нужную вам функцию. Рассмотрим следующий пример, где сложим значения столбца А и столбца B: Пример таблицы Столбец A Столбец B Столбец C

   строка 1	10	9	=A1+B1
   строка 2	20	8
   строка 3	30	7
   строка 4	40	6

2. 2 Нажмите Enter, чтобы формула сработала и отобразился результат вычислений. В нашем примере отобразится только результат: 19, но формула будет сохранена в таблице. Пример таблицы Столбец A Столбец B Столбец C

   строка 1	10	9	19
   строка 2	20	8
   строка 3	30	7
   строка 4	40	6

3. 3 Щелкните по ячейке, в которую вы вводили формулу. В ее правом нижнем углу отобразится значок «+».[1]
4. 4 Удерживая значок «+», перетащите ячейку по строке или столбцу, куда вы хотите скопировать формулу. Введенная формула автоматически скопируется и изменится согласно адресам соответствующих ячеек. В нашем примере (отображаются автоматически измененные формулы и результаты вычислений): Пример таблицы Столбец A Столбец B Столбец C

   строка 1	10	9	=A1+B1
   строка 2	20	8	=A2+B2
   строка 3	30	7	=A3+B3
   строка 4	40	6	=A4+B4

   
   

   Пример таблицы Столбец A Столбец B Столбец C

   строка 1	10	9	19
   строка 2	20	8	28
   строка 3	30	7	37
   строка 4	40	6	46

5. 5 Щелкните по ячейке, в которую вы вводили формулу. В ее правом нижнем углу отобразится значок «+». Дважды щелкните по знаку «+», чтобы автоматически скопировать введенную формулу во все ячейки данного столбца.[2]
   • Автоматическое копирование остановится при достижении пустой ячейки. Если в ваших данных есть разрывы (пустые ячейки), повторите процесс копирования, начав с ячейки, следующей после пустой.

Метод 2 из 3: Точное копирование формулы

1. 1 Используйте этот метод, чтобы быстро скопировать формулу без ее автоматического изменения (то есть без изменения адресов соответствующих ячеек). В этом случае не стоит вставлять абсолютные ссылки на ячейки (как описано в следующем разделе), так как это утомительно, особенно если вы хотите изменить их позже.[3] Вот новый пример, в котором столбец C нужно скопировать в столбец D (без изменений): Пример таблицы Столбец A Столбец B Столбец C Столбец D

   строка 1	944	Лягушки	=A1/2
   строка 2	636	Жабы	=A2/2
   строка 3	712	Тритоны	=A3/2
   строка 4	690	Змеи	=A4/2

   • Если вы просто пытаетесь скопировать формулу в одну ячейку, перейдите к последнему шагу этого раздела.
2. 2 Откройте окно «Найти». Для этого в большинстве версий Excel перейдите на вкладку «Главная» и нажмите «Найти и выделить» в группе «Редактирование». [4]
3. 3 Введите «=», нажмите «Найти все», затем введите другой символ в строке «Заменить на». Это автоматически изменит все формулы (которые всегда начинаются со знака «=») в текстовые строки, начинающиеся с какого-либо другой знака. Всегда используйте символ, который не используется в таблице. Например, замените «=» на «#», или «&», или «##». Пример таблицы Столбец A Столбец B Столбец C Столбец D

   строка 1	944	Лягушки	##&A1/2
   строка 2	636	Жабы	##&A2/2
   строка 3	712	Тритоны	##&A3/2
   строка 4	690	Змеи	##&A4/2

   • Не используйте символы «*» и «?», так как это усложнит процесс на более поздних стадиях.
4. 4 Теперь вы можете выбрать любые ячейки, которые вы хотите скопировать, а затем вставить их в другом месте таблицы. Так как они уже не являются формулами, они будут точно скопированы. Пример таблицы Столбец A Столбец B Столбец C Столбец D

   строка 1	944	Лягушки	##&A1/2	##&A1/2
   строка 2	636	Жабы	##&A2/2	##&A2/2
   строка 3	712	Тритоны	##&A3/2	##&A3/2
   строка 4	690	Змеи	##&A4/2	##&A4/2

5. 5 Теперь еще раз откройте окно «Найти». В нашем примере мы будем искать символы «##» и заменим их на «=», чтобы скопированные ячейки превратились обратно в формулы. Пример таблицы Столбец A Столбец B Столбец C Столбец D

   строка 1	944	Лягушки	=A1/2	=A1/2
   строка 2	636	Жабы	=A2/2	=A2/2
   строка 3	712	Тритоны	=A3/2	=A3/2
   строка 4	690	Змеи	=A4/2	=A4/2

6. 6 Попробуйте альтернативные методы. Если описанный выше метод не сработал по какой-либо причине, или если вы беспокоитесь о случайном изменении содержимого других ячеек (при использовании функции «Заменить все»), есть несколько других методов, которые вы можете протестировать:
   • Чтобы скопировать формулу без изменения ссылок (адресов соответствующих ячеек), выделите ячейку, а затем скопируйте формулу, приведенную в строке формул (в верхней части окна, а не в самой ячейке). Нажмите Esc, чтобы закрыть панель формул, а затем вставьте формулу, куда необходимо.[5]
   • Нажмите Ctrl + ` (этот символ, как правило, расположен на клавише с символом ~), чтобы перевести таблицу в режим просмотра формул. Скопируйте формулы и вставьте их в текстовый редактор, например, в Блокнот. Скопируйте их еще раз, а затем вставьте в нужные ячейки. [6] Нажмите Ctrl + `, чтобы перевести таблицу из режима просмотра формул в обычный режим.

Метод 3 из 3: Использование относительных и абсолютных ссылок на ячейки

1. 1 Используйте относительные ссылки в формуле. «Ссылка на ячейку» в формуле Excel относится к адресу ячейки, используемой в формуле. Вы можете ввести их вручную или щелкнуть по ячейке, которую вы хотите использовать при вводе формулы. Например, следующая таблица включает формулу со ссылкой на ячейку A1: Относительные ссылки Столбец A Столбец B Столбец C

   строка 1	50	7	=A1*2
   строка 2	100
   строка 3	200
   строка 4	400

2. 2 Объяснение понятия «относительные ссылки». Если вы копируете ячейку, в которую ввели формулу, а затем вставляете ее в другую ячейку, вы не увидите тот же результат. Это происходит потому, что ссылка на ячейку является «относительной». При копировании формул Excel автоматически меняет ссылки на ячейки в соответствии с адресами соответствующих ячеек. Например, если вы используете функции «Копировать» и «Вставить», чтобы скопировать формулу из ячейки С1 в ячейку C3, в формуле ссылка на ячейку А1 поменяется на ссылку на ячейку A3: Относительные ссылки Столбец A Столбец B Столбец C

   строка 1	50	7	=A1*2
   строка 2	100
   строка 3	200		=A3*2
   строка 4	400

   • Это работает для любых ячеек. Например, если вы копируете ту же формулу из ячейки С1 в ячейку D6 (не показана), Excel изменит ссылку на ячейку A1 на ссылку на ячейку В6 (один столбец вправо [C→D] и 5 строк вниз [1→6]).
3. 3 Используйте абсолютные ссылки в формуле. Допустим, вы не хотите, чтобы Excel автоматически изменял формулу. Это можно сделать при помощи абсолютных ссылок, введя символ $ перед строкой или столбцом, который вы хотите оставить без изменений при копировании формулы в другие ячейки.[7] Вот несколько примеров таблиц, где показана исходная формула (жирным шрифтом) и результаты ее копирования в другие ячейки: Относительный столбец, Абсолютная строка (B$1): Столбец A Столбец B Столбец C

   строка 1	50	7	=B$1
   строка 2	100	=A$1	=B$1
   строка 3	200	=A$1	=B$1
   строка 4	400	=A$1	=B$1

   
   

   Абсолютный столбец, Относительная строка ($B1): Столбец A Столбец B Столбец C

   строка 1	50	7	=$B1
   строка 2	100	=$B2	=$B2
   строка 3	200	=$B3	=$B3
   строка 4	400	=$B4	=$B4

   
   

   Абсолютные столбец и строка ($B$1): Столбец A Столбец B Столбец C

   строка 1	50	7	=$B$1
   строка 2	100	$B$1	$B$1
   строка 3	200	$B$1	$B$1
   строка 4	400	$B$1	$B$1

4. 4 Используйте клавишу F4, чтобы переключаться между абсолютными и относительными ссылками. Выделите ссылку на ячейку в формуле. Нажмите F4 и символы $ будут автоматически добавлены или удалены. Нажимайте F4 до тех пор, пока не отобразятся нужные абсолютные или относительные ссылки, а затем нажмите Enter.[8]

Советы

• Если вы копируете формулу в новую ячейку и видите зеленый треугольник, Excel обнаружил возможную ошибку. В этом случае проверьте правильность введения формулы.[9]
• Если вы случайно замените символ «=» на «?» или «*» (в методе «точное копирование формулы»), поиск символов «?» или «*» не приведет к ожидаемым результатам. Исправьте это, выполнив поиск символов «~?» или «~*».[10]
• Выделите ячейку и нажмите Ctrl + ' (апостроф), чтобы скопировать в эту ячейку формулу, расположенную в ячейке сверху.[11]