phaggi
@phaggi
лужу, паяю, ЭВМы починяю

Как сделать, чтобы именованный диапазон в excel не смещался при копировании формулы со ссылкой на этот диапазон?

На одном листе - таблица с названием "новая", в ней столбцы "программа", "филиал", "проект", "план", "факт".
На другом листе делаю формулу типа:
=СУММЕСЛИМН(новая[план];новая[филиал];RC1;новая[программа];R4C)


Затем копирую эту формулу в соседнюю ячейку справа от той, откуда копирую, и excel автоматически мне заменяет в формуле колонки из таблицы "новая" на соседние. Кто его просил, заразу?
Получается вот такая формула:
=СУММЕСЛИМН(новая[факт];новая[проект];RC1;новая[филиал];R4C)


В инете искал - там всё про отдельные именованные диапазоны. А у меня здоровенная таблица в реальности, с кучей колонок, что мне, две сотни именованных диапазонов вручную создавать? Скрипты писать?

Как зафиксировать такие ссылки на диапазоны в именованной таблице, чтобы при копировании формулы в соседнюю ячейку excel не менял эти зафиксированные ссылки?
  • Вопрос задан
  • 60 просмотров
Решения вопроса 1
@ClearAirTurbulence
Для создания абсолючных ссылок на табличные строки\колонки в экселе используются следющие приемы:

To create an absolute reference with structured references you need to add an additional and duplicate column reference.

t_Data[[Color]:[Color]]

Duplicating the column reference will anchor the reference when dragging across columns. Here are the full formulas with relative and absolute references.

Relative: =SUMIF(t_Data[Color],[@Color],t_Data[Q1 Units])
Absolute: =SUMIF(t_Data[ [Color]:[Color] ],t_Summary[@ [Color]:[Color] ],t_Data[Q1 Units])
In the absolute formula you'll notice that I anchored the first two arguments. The [@Color] column needs be anchored as well.

To anchor a row reference you need to put the @ symbol before the duplicate column reference and wrap it all in brackets. The table name is also required, even when the reference is in the same table as the formula. So the row reference looks like:

t_Summary[@[Color]:[Color]]

This is an anchored reference to one cell in the same row as the formula.


Подробнее:
https://www.excelcampus.com/tips-shortcuts/absolut...
По этой же ссылке add-in для экселя, который позволяет по нажатию F4 при редактировании формулы переключать абсолютную\относительную адресацию так же, как это делается для обычных ссылок.
Дисклеймер: работоспособность надстройки не проверял.
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы