Одна из основных функций Excel — работа с формулами, которые могут содержать как конкретные числовые значения, так и ссылки на другие ячейки в таблице. В этом случае ссылки могут быть как относительными, так и абсолютными. В этой статье мы разберемся, в чем их отличия и для чего они нужны.
Относительная ссылка — это ссылка, в которой координаты ячейки меняются при копировании формулы в другую ячейку.
Абсолютная ссылка — это ссылка, в которой координаты ячейки фиксированы и не меняются при копировании.
Относительные ссылки
Чтобы было понятнее, давайте рассмотрим примеры и начнем со связанных ссылок, поскольку они встречаются чаще.
Предположим, у нас есть таблица, в столбцах которой указаны цена за единицу и количество проданных товаров. Наша задача — посчитать сумму по каждой позиции в соответствующей графе.
Вот что нам нужно сделать:
- Перейдите в верхнюю ячейку получившегося столбца (не считая заголовка таблицы), поставьте знак «равно» («=») и запишите в нее формулу: = B2 * C2.
- Когда выражение будет готово, нажимаем на клавиатуре клавишу Enter, после чего получаем результат в ячейке с формулой.
- Осталось произвести аналогичные вычисления в других ячейках столбца. Конечно, если таблица небольшая, вы можете перейти к следующей ячейке и выполнить шаги 1-2 выше. Но что делать, если данных слишком много? Ведь вручную вводить формулы во все ячейки уйдет много времени. В этом случае Excel предоставляет чрезвычайно полезную функцию, позволяющую копировать формулу в другие ячейки. Для этого наведите указатель мыши на правый нижний угол ячейки с результатом и, когда появится маленький черный крестик (индикатор заливки), зажмите левую кнопку мыши и перетащите ее вниз, скопировав таким образом формулу в другие ячейки.
- Отпустив кнопку мыши, мы получим результаты во всех ячейках столбца, до которого была растянута формула.
- Если мы перейдем, например, к ячейке D3, мы увидим следующее выражение в строке формул: = B3 * C3.
Те при копировании изменились координаты ячеек, участвующих в исходной формуле, которую мы писали в ячейке D2. Это результат относительности ссылок.
Возможные ошибки при работе с относительными ссылками
Конечно, благодаря ссылкам по теме многие вычисления в Excel значительно упрощаются. Однако они не всегда помогают решить проблему.
Допустим, нам нужно рассчитать долю каждого товара в общем объеме продаж.
- Находимся в первой ячейке столбца для расчетов, куда записываем формулу: = D2 / D13.
- Нажмите Enter, чтобы получить результат. После копирования формулы в оставшиеся ячейки столбца вместо результатов мы видим следующую ошибку: # DIV / 0!.
Дело в том, что из-за того, что все ссылки на ячейки в скопированной нами формуле относительные, координаты в последующих ячейках сместились. Формула для ячейки E3 выглядит так: = D3 / D14. Но, как мы видим, ячейка D14 пуста, поэтому программа выдает ошибку о невозможности деления на число 0.
Следовательно, нам нужно написать формулу таким образом, чтобы координаты ячейки с общей суммой (D13) оставались неизменными при копировании. В этом нам помогут абсолютные ссылки.
Абсолютные ссылки
Как мы узнали ранее, абсолютные ссылки позволяют фиксировать координаты ячейки. Посмотрим, как это работает на нашем примере.
По умолчанию все ссылки в формулах Excel являются относительными, поэтому, чтобы сделать их абсолютными, выполните следующие действия:
- Сначала в нужную ячейку записываем формулу в обычном виде. В нашем случае это выглядит так: = D2 / D13.
- Когда формула будет готова, не торопитесь нажимать клавишу Enter. Теперь нам нужно исправить координаты ячейки D13. Для этого перед именем столбца и порядковым номером строки печатаем символ «$». Или вы можете просто нажать клавишу F4 на клавиатуре после ввода адреса (курсор может находиться до, после или внутри координат). Следовательно, формула должна выглядеть так: D2 / $ D $ 13.
- Теперь вы можете нажать Enter, чтобы просмотреть результат в ячейке.
- Осталось только скопировать формулу, используя маркер заполнения в нижних строках. На этот раз, поскольку мы исправили ячейку с общей суммой, результат будет отображаться и в других ячейках.
Смешанные ссылки
В дополнение к ссылкам, описанным выше, Excel также предоставляет смешанные ссылки: при копировании формулы вы изменяете одну из координат ячейки (номер столбца или строки).
- Если мы напишем ссылку как «$ G5», это означает, что строка изменится, а столбец будет фиксированным.
- Если мы укажем «G $ 5», то в этом случае номер строки будет фиксированным, а столбец изменится.
Те, на самом деле, можно будет решить вышеупомянутую проблему, определив долю каждого элемента в общих продажах, установив только номер строки, поскольку столбец, однако, даже со ссылкой на него не изменился.
Примечание. Вместо того, чтобы вручную вводить символы «$», вы можете указать тип ссылки (абсолютная, относительная, смешанная) с помощью функциональной клавиши F4. В этом случае курсор должен находиться внутри координат ячейки, относительно которой мы хотим выполнить это действие.
Заключение
С относительными, абсолютными и смешанными ссылками Excel выполняет огромное количество различных вычислений. Поэтому для успешной работы в программе необходимо в них внимательно разбираться, чтобы выполнять следующую задачу максимально качественно и результативно.
