Как в Excel закрепить ячейку в формуле

Как в Excel закрепить ячейку в формуле

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

Инструкция
1
В простейшем случае, если формула использует данные из одной книги, при вставке функции в поле ввода значений запишите координаты фиксированной ячейки в формате $A$1. Например, вам необходимо просуммировать значения по столбцу B1:B10 со значением в ячейке А3. Тогда в строке функций запишите формулу в следующем формате:=СУММ($A$3;B1).Теперь при автозаполнении будет изменяться только имя строки второго слагаемого.
2
Аналогичным способом можно просуммировать данные из двух разных книг. Тогда в формуле надо будет указать полный путь к ячейке закрытой книги в формате:=СУММ($A$3;’Имя_диска:Каталог_пользователяИмя_пользователяИмя_папки[Имя_файла.xls]Лист1’!А1).Если вторая книга (называемая исходной) открыта и файлы находятся в одной папке, то в конечной книге указывается только путь от файла:=СУММ($A$3;[Имя_файла.xls]Лист1!А1).
3
Однако при такой записи, если вы собираетесь добавлять или удалять строки/столбцы в исходной книге перед первой ячейкой нужного диапазона, в конечной книге значения в формуле будут меняться. При вставке пустых строк выше исходной ячейки, вместо второго слагаемого в формуле конечной книги окажутся нули. Чтобы этого не происходило, книги нужно связать между собой.
4
Для этого в конечную книгу придется добавить столбец связи. Откройте исходную книгу и выберите в ней ячейку, значение которой должно быть зафиксировано вне зависимости от операций с таблицей. Скопируйте это значение в буфер обмена. Перейдите на лист в конечной книге, где будет содержаться формула.
5
В меню «Правка» выберите пункт «Специальная вставка» и в открывшемся окне нажмите кнопку «Вставить связь». По умолчанию в ячейку будет вписано выражение в формате:=[Книга2.xls]Лист1!$А$1.Однако это выражение будет выводиться только в строке формул, а в самой ячейке будет вписано его значение. Если вам необходимо связать конечную книгу с вариационным рядом из исходной, уберите знак $ из указанной формулы.
6
Теперь в следующем столбце вставьте формулу суммирования в обычном формате:=СУММ($A$1;B1),где $A$1 – адрес фиксированной ячейки в конечной книге;В1 – адрес ячейки, содержащей формулу связи с началом вариационного ряда другой книги.
7
При таком способе записи формулы значение В1 исходной таблицы останется неизменным, сколько бы строк вы ни добавили выше. Если же вы измените его вручную, результат вычисления по формуле в конечной таблице тоже изменится.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

1 × пять =