Что вам нужно понять, так это то, что абсолютность абсолютных ссылок, как указано в $ , не является абсолютно абсолютной ;-)
Теперь, когда эта скороговорка исчезла, позвольте мне объяснить.
Абсолютность применяется только при копировании-вставке или заполнении формулы. Вставка строк или столбцов слева от абсолютно ссылочного диапазона "сместит" адрес диапазона так, чтобы данные, на которые указывает диапазон, оставались неизменными.
Кроме того, вставка строк или столбцов в середине диапазона расширит его, чтобы охватить новые строки / столбцы. Таким образом, чтобы "добавить" строку данных в диапазон (таблицу), вам нужно вставить ее после первой строки данных.
Самый простой способ разрешить добавление строки данных выше текущего диапазона данных - всегда иметь строку заголовка и включать строку заголовка в фактический диапазон. Это как раз решение, предложенное cybernetic.nomad в этом комментарии.
Но осталась еще одна проблема - добавление строки данных после конца таблицы. Просто ввести новые данные в строке после последней строки данных не будет работать. Также не будет вставки строки перед строкой после последней строки.
Самое простое решение для этого - использовать специальную "последнюю" строку, включить эту строку в диапазон данных и всегда добавлять новые строки, вставляя перед этой специальной строкой.
Я обычно уменьшаю высоту строки и заполняю ячейки соответствующим цветом:
Для вашего примера, полная "простейшая" формула будет выглядеть так:
=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)
Другой способ достижения той же цели - использовать динамическую формулу, которая автоматически подстраивается под объем данных в таблице. Существует несколько различных вариантов этого, в зависимости от конкретных обстоятельств и того, что должно быть разрешено делать с таблицей.
Если, как это обычно бывает (например, в вашем примере), таблица начинается в верхней части таблицы, имеет заголовок из одной строки и данные являются непрерывными без пропусков, простая динамическая формула будет иметь вид:
=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))
Это лучшее решение, чем использование INDIRECT() качестве
- Он является энергонезависимым и, следовательно, рабочий лист вычисляется быстрее, и
- Это не сломается, если вы вставите столбцы слева от таблицы.
Техника динамических формул может быть дополнительно улучшена путем использования ее в именованной формуле.
Конечно, лучшее решение - преобразовать таблицу в правильную таблицу и использовать структурированные ссылки.
