Archie Goodwin

Что такое фиксирование ссылок?

В формулах эксель используются ссылки на ячейки, что, по сути, координаты размещения используемых ячеек (например, ссылка A1 - это верхняя слева ячейка на рабочем листе в книге эксель, С5 – ячейка в третьем столбце и на пятой строке и т.п.).

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

Для понимания упростим формулу до нельзя – поставим равно и укажем ссылку на ячейку выше из таблицы с «исходными данными» (=B2), поле ввода формулы получим просто дублирование значения из исходной таблицы. Если протянуть эту ячейку с формулой, то получим полную копию исходной таблицы, которая будет изменяться при изменении данных в исходной таблице, то есть продублированная таблица будет как бы «живая».

Это пример того как выглядит «протягивание» ячеек с формулами без фиксирования диапазонов. Фиксирование ссылок же позволяет закрепить ссылку, которая будет неизменной при протягивании либо полностью, либо только по столбцу, либо только по строке.

Фиксирование ссылки в строке формул осуществляется с помощью знака - $, который ставится перед той частью координат в ссылке, которую нужно зафиксировать:

• $ перед буквой – фиксирование по столбцу - $A1
• $ перед цифрой – фиксирование по строке - A$1
• $ и перед буквой, и перед цифрой – полное фиксирование ячейки - $A$1

Проставлять «знак доллара» в строке формул можно самостоятельно либо с помощью горячей клавиши F4, предварительно выделив мышкой именно в строке формул ссылку, которую нужно зафиксировать.

Теория теорией, но давайте посмотрим, как же выглядит на практике различное фиксирование ячеек на том же примере что и раньше.

Полное фиксирование ячейки ($B$2)

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

Фиксирование только по столбцу ($B2)

Протягивание показывает, что неизменным остается только столбец, тогда как номер строки в ссылке благополучно изменяется. Это может быть полезно при «протягивании» в сторону при работе с большими таблицами, использующими в формулах ссылку, например, только первого столбца.

Фиксирование только по строке (B$2)

Номер строки в ячейках с протянутой формулой не изменился, в какую бы мы сторону не «протягивали» формулу, а вот столбец успешно меняется при протягивании.

Фиксирование диапазонов

Многие функции экселя в качестве ссылок на исходные данные требуют указывать диапазоны ячеек, то есть несколько подряд (а иногда и не подряд) расположенных ячеек с данными. Для примера рассмотрим одну из подобного рода функций – СУММ () – функция, которая просто подсчитывает сумму значений указанных в диапазоне.

Формула с прописанной функцией и указанным диапазоном выглядит так =СУММ(B2:B5), что значит, что эксель подсчитает сумму значений находящихся в ячейках B2, B3, B4 и B5. Фиксирование диапазонов производится по аналогии, как и с ячейками:

• $ перед буквами – фиксирование по столбцу - $B2:$B5
• $ перед цифрами – фиксирование по строке – B$2:B$5
• $ и перед буквами, и перед цифрами – полное фиксирование - $B$2:$B$5

Имеет это все такие же эффекты, как и в случае, когда мы рассматривали фиксирование ячеек. Фиксирование также можно проводить, вручную добавляя знаки $ в ссылки или выделяя ссылки нажимая на клавишу F4.

Посмотреть примеры, а также попробовать самому каждый из вариантов фиксирования можно в файле, который прилагается к статье. Также в приложенном файле на листе «Комбинирование», можно посмотреть различные варианты фиксирования на вполне востребованных примерах, а более расписанный пример в этом же файле на листе с названием «пример использования».