Абсолютные и относительные ссылки в MS Excel

Комментарии к записи Абсолютные и относительные ссылки в MS Excel отключены


Ссылки на ячейки в MS Excel бывают двух типов: абсолютные и относительные. Каждая из них хороша строго на своем месте, если короче: относительные ссылки изменяются при копировании, а абсолютные всегда остаются неизменными.

Относительные ссылки в MS Excel

По умолчанию, все ссылки на ячейки MS Excel являются относительными. При копировании формул, их содержимое меняется в зависимости от относительного расположения строк и столбцов. Другими словами, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула превратится в =A2+B2.

Простейшая формула в MS Excel состоит из относительных ссылок

Простейшая формула в MS Excel состоит из относительных ссылок

Разумеется это очень удобно в том случае, если мы имеем таблицу, где каждая строка в определенном столбце имеет один и тот же принцип вычисления результата. Например, в таблице из 20 строк нам нужно вычислить сумму ячеек А1 и В1, а результат поместить в ячейку С1. Нет ничего проще — мы помещаем в ячейку С1 формулу =А1+В1 и насаждаемся результатом.

При копировании относительной ссылки, MS Excel автоматически смещает значения формулы на нужное количество строк и столбцов

При копировании относительной ссылки, MS Excel автоматически смещает значения формулы на нужное количество строк и столбцов

А как вычислить результат аналогичного сложения для следующей строки? Поместить в С2 формулу А2+В2 — вариант отличный, но не слишком ли это жирно — пройти по всем строкам таблицы и в каждой записать нужную формулу? К счастью, делать этого не нужно — за счет того, что ссылки в Excel по умолчанию относительны, нам достаточно просто скопировать исходную формулу (=А1+В1) на следующую строку, и программа сама подставит на нужные места изменившиеся за счет смещения относительные данные ячеек (формула изменит вид на =А2+В2).

Относительные ссылки в MS Excel

Да, работая с относительными ссылками мы можем просто протянуть ссылку на на весь столбец и наслаждаться результатом

При этом, формулу можно даже не копировать, а просто протянуть на всю длину таблицы — результат будет тем же самым.

Абсолютные ссылки в MS Excel

Не всегда нужно, чтобы формулы (или часть формулы) изменялись при копировании и перемещении в другую ячейку. Простейший пример: нам нужно провести ту же самую операцию, что и выше, но дополнительно, итоговый результат в столбце «С» нужно умножить на некий коэффициент — постоянный на данный момент времени (в последующем он может изменится).

Такая формула обычно имеет вид:  =(А1+В1)*Коэффициент

К примеру, коэффициент имеет вид 2,6, тогда формула будет выглядеть как =(А1+В1)*2,6  На первый взгляд этот самый коэффициент можно просто вписать в формулу вручную (как я указал выше). Но что если завтра коэффициент изменится? Гораздо проще поместить его в ячейку таблицы (например D1) и привести формулу к виду   =(А1+В1)*D1. Однако как мы помним, по умолчанию все ссылки в MS Excel относительны, а это значит что при копировании этой формулы на строку ниже, она приобретет вид =(А2+В2)*D2.

Абсолютные ссылки в MS Excel

А вот и минус относительных ссылок — смещается вся формула и даже те фрагменты, которые по идее смещать не надо

Выхода тут два: или добавить в таблицу новую колонку «коэффициент» заполненную одинаковыми данными (тогда при абсолютном смещении сместится и ячейка с коэффициентом), или, сделать эту конкретную ячейку D1 не относительной, а абсолютной.

создание абсолютных ссылок в экселе

Можно решить проблему с относительными ссылками введя дополнительную колонку с одинаковыми значениями

Добиться этого просто: к каждому знаку «постоянного» элемента нужно всего лишь добавить специальный символ «$». Иными словами,  формула  =(А1+В1)*$D$1 будет работать совсем не так как раньше: если «относительные» ячейки А1 и В1 будут изменяться при копировании формулы в другое место по обычным правилам относительных ссылок, то $D$1 всегда будет ссылаться на одну и ту же ячейку, как бы мы не перемещали исходную формулу.

абсолютная ссылка в экселе

Как видно из рисунка — относительные ссылки продолжают работать по обычным правилам, а абсолютная — ссылается всегда на одно и то же место в таблице

Таким образом, у нас отпадает необходимость о добавлении в таблицу целого столбца с одинаковыми данными, а формулы становятся более компактными и простыми.

Подробнее об абсолютных ссылках.

 

Также вас может заинтересовать:

Написать ответ:

 
Сайт создан: ООО "Эйфос". Информационные технологии

Наверх