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