У Билла есть формула, которая суммирует столбец в строках со 2 по 15, используя функцию СУММ. Если он добавляет строку где-нибудь в строках с 3 по 15, формула корректируется правильно. Если он добавляет строку вверху (чуть выше строки 2), формула вообще не корректируется, чтобы включить вставленную строку. Билл задается вопросом, есть ли способ заставить формулу правильно и автоматически настраиваться, когда он добавляет строку вверху того, что суммируется.

Существует множество школ, посвященных тому, как это можно сделать, каждая из которых реализует разные техники для достижения одного и того же результата. Один из подходов — просто преобразовать ваши данные в формальную таблицу на рабочем листе. Самый простой способ — выделить ячейку в данных и нажать Ctrl + T. Excel распознает ваши данные как таблицу, и если вы вставляете какие-либо строки вверху таблицы, прямо под заголовками, они автоматически отражаются при суммировании таблицы.

Другой подход — скорректировать формулу СУММ. Вероятно, вы суммируете строки со 2 по 15, потому что в строке 1 есть заголовок столбца. Если это так (и если заголовок столбца содержит текст), просто измените формулу СУММ так, чтобы она ссылалась на строки с 1 по 15. Теперь, когда кто-то добавляет новую строку в строку 2, она автоматически включается в сумму. Кроме того, текст в заголовке столбца никак не повлияет на эту сумму.

Если ваши заголовки содержат числа, которые могут испортить ваши суммы, вы можете попробовать другой вариант. Вставьте пустую строку в строку 2 и настройте формулу SUM так, чтобы она ссылалась на строки со 2 по 16. (Это будет строка 16 вместо 15, потому что вы добавили новую пустую строку 2.) Затем скройте строку 2, чтобы она не была видимый. Теперь, когда люди добавляют строки вверху, они фактически будут добавлять их в строку 3, а формула SUM по-прежнему будет ссылаться на скрытую строку 2 — корректировки будут происходить правильно.

Вы также можете использовать подход, при котором вы настраиваете формулу СУММ для использования функции КОСВЕННО или СМЕЩЕНИЕ. Предположим на мгновение, что ваша текущая формула СУММ выглядит так:

=SUM(A2:A15)

Измените его так, чтобы он выглядел следующим образом:

=SUM(INDIRECT("A2"):A15)

=SUM(OFFSET(A1,1,0):A15)

Обе эти формулы «закрепляют» верхнюю ячейку диапазона. Когда вы вставляете ячейки в верхнюю часть суммированного диапазона (перед строкой 2), Excel не корректирует ссылки на ячейки ни в функции ДВССЫЛ, ни в функции СМЕЩЕНИЕ. В результате ваша формула СУММ всегда будет ссылаться на нужную вам ячейку верхнего диапазона.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10999) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.