Копирование формул
Копирование формулы позволяет быстро выполнить одинаковые вычисления для нескольких строк или столбцов. При переносе электронная таблица изменяет относительные ссылки, но сохраняет абсолютные, поэтому важно понимать, какие части адреса ячейки должны перемещаться, а какие — оставаться постоянными.
Что происходит при копировании формулы
Формула записывается в ячейку и обычно начинается со знака равенства. Если формулу скопировать в другую ячейку, программа не просто повторяет символы: она пересчитывает адреса ячеек относительно нового положения формулы. Такое поведение особенно удобно, когда нужно обработать много строк с одинаковыми правилами.
Например, в ячейке C2 записано =A2+B2. При копировании этой формулы в C3 ссылки смещаются на одну строку: получается =A3+B3. При копировании в D2 ссылки смещаются на один столбец: получается =B2+C2.
Относительная ссылка — адрес ячейки, который при копировании формулы изменяется вместе с её положением. В записи A1 изменяются и столбец, и номер строки, если перенос происходит соответственно по горизонтали или вертикали. Подробнее см. относительную ссылку.
Абсолютная ссылка — адрес ячейки, который при копировании не изменяется. Для фиксации столбца и строки перед ними ставят знак $: \(A\)1. Подробнее см. абсолютную ссылку.
Правила смещения ссылок
Перенос формулы можно представить как перемещение на несколько строк и столбцов. Если формулу перенесли на \(\Delta r\) строк и \(\Delta c\) столбцов, относительная ссылка изменяется на эти же величины. Абсолютные части адреса не изменяются.
| Вид ссылки | Пример | Что изменяется при копировании |
|---|---|---|
| Относительная | A1 | Столбец и строка |
| Абсолютная | \(A\)1 | Ничего |
| Смешанная | $A1 | Только строка |
| Смешанная | A$1 | Только столбец |
Смешанная ссылка фиксирует только одну координату. В записи $A1 столбец A постоянен, а номер строки меняется. В записи A$1 строка 1 постоянна, а столбец изменяется. Смешанные ссылки особенно часто встречаются в таблицах умножения и при расчётах по двум направлениям; о них можно прочитать на странице смешанная ссылка.
При переносе формулы на \(\Delta c\) столбцов и \(\Delta r\) строк каждая относительная координата изменяется на соответствующую величину, а каждая координата со знаком $ сохраняется.
При копировании по столбцу обычно меняется номер строки, а буква столбца остаётся прежней. Подробный частный случай разобран на странице копирование формулы по столбцу. При копировании по строке обычно меняется буква столбца; этот случай рассмотрен на странице копирование формулы по строке.
В ячейке B2 находится формула =A2+$F$1. Какой она станет в ячейке B5?
Алгоритм анализа формулы
- Определите исходную ячейку, в которой записана формула.
- Определите конечную ячейку и найдите смещение по столбцам и строкам.
- Разберите каждую ссылку в формуле отдельно: столбец, строка и наличие знака
$. - Измените только незакреплённые координаты на величину смещения.
- Проверьте, не появились ли ссылки за пределами таблицы и не изменились ли диапазоны.
Диапазоны изменяются по тому же правилу. Например, при копировании формулы =SUM(A2:C2) из D2 в D3 получится =SUM(A3:C3). Функция SUM и другие функции не меняют правила адресации: изменяются только ссылки внутри формулы. О синтаксисе и назначении формул см. страницу формулы в электронных таблицах, а о функциях — функции электронных таблиц.
Не пытайтесь мысленно «переписывать» всю формулу целиком. Выпишите каждую ссылку отдельно и перенесите её на одинаковое количество строк и столбцов. Это уменьшает число ошибок в длинных выражениях.
Разобранный пример
В таблице в ячейке E2 записана формула стоимости товара: =C2*D2*(1+$H$1). В C2 находится цена, в D2 — количество, а в H1 — наценка, одинаковая для всех товаров. Требуется определить формулу в E5.
Показать ответ Решение
В ячейке E5 будет формула =C5*D5*(1+$H$1). Изменились только относительные ссылки C2 и D2. Абсолютная ссылка \(H\)1 сохранилась.
Что проверяют в заданиях
В экзаменационных заданиях нужно определить значение формулы после копирования, найти содержимое конкретной ячейки или восстановить исходную формулу. Иногда требуется учитывать не только ссылки, но и порядок выполнения действий. Поэтому перед вычислением полезно вспомнить приоритет операторов в таблице: умножение и деление выполняются раньше сложения и вычитания, если нет скобок.
Если в формуле используются фиксированные параметры, например курс валюты, коэффициент или процент скидки, их обычно помещают в отдельную ячейку и записывают с абсолютной ссылкой. Например, =B2*$F$1 при копировании вниз превращается в =B3*$F$1, =B4*$F$1 и так далее.
1. Изменяют абсолютную ссылку: $F$1 ошибочно превращают в $F$4. 2. Забывают, что при переносе вправо меняются буквы столбцов. 3. Меняют координаты внутри текстовой строки или числа, хотя они не являются ссылками. 4. Неверно учитывают смешанную ссылку: в $A1 строка меняется, а в A$1 меняется столбец. 5. Считают, что знак $ относится ко всей формуле; на самом деле он закрепляет только следующий столбец или номер строки.
Быстрый тест
Проверь себя
=B2+C2 из A2 скопирована в A6. Какой результат получится?=$A2*D$1 из D2 скопирована в F4. Какая формула получится?=SUM(A2:C2) при копировании из D2 в D3?Главное
- При копировании формулы относительные ссылки изменяются в соответствии со смещением, а абсолютные ссылки остаются неизменными.
- В ссылке A1 изменяются столбец и строка; в \(A\)1 не изменяется ничего; в \(A1 и A\)1 фиксируется только одна координата.
- Для решения сначала найдите смещение между исходной и целевой ячейками, затем обработайте каждую ссылку отдельно.
- Диапазоны и ссылки внутри функций подчиняются тем же правилам.
- При длинных формулах дополнительно проверяйте скобки и знак равенства в формуле.