У Билла есть формула, которая суммирует столбец в строках со 2 по 15, используя функцию СУММ. Если он добавляет строку где-нибудь в строках с 3 по 15, формула корректируется правильно. Если он добавляет строку вверху (чуть выше строки 2), формула вообще не корректируется, чтобы включить вставленную строку. Билл задается вопросом, есть ли способ настроить формулу должным образом и автоматически, когда он добавляет строку в верхнюю часть суммируемого результата.
Существует множество мнений о том, как это можно сделать. , каждый из которых реализует разные методы для достижения одного и того же. Один из подходов – просто преобразовать ваши данные в формальную таблицу на рабочем листе. Самый простой способ – выделить ячейку в данных и нажать Ctrl + T . Excel распознает ваши данные как таблицу, и если вы вставляете какие-либо строки вверху таблицы, прямо под заголовками, они автоматически отражаются при суммировании таблицы.
Другой подход – настроить ваша формула СУММ. Вероятно, вы суммируете строки со 2 по 15, потому что в строке 1 есть заголовок столбца. Если это так (и если заголовок столбца содержит текст), просто измените формулу СУММ так, чтобы она ссылалась на строки с 1 по 15. Теперь, когда кто-то добавляет новую строку в строку 2, она автоматически включается в сумму. Кроме того, текст в заголовке столбца ни в коей мере не повлияет на эту сумму.
Если ваши заголовки содержат числа, которые могут действительно испортить ваши суммы, вы можете попробовать другой вариант. Вставьте пустую строку в строку 2 и настройте формулу СУММ так, чтобы она ссылалась на строки со 2 по 16. (Это будет строка 16 вместо 15, потому что вы добавили новую пустую строку 2.) Затем скройте строку 2, чтобы она не была видимый. Теперь, когда люди добавляют строки вверху, они фактически будут добавлять их в строку 3, а формула SUM по-прежнему будет ссылаться на скрытую строку 2 – корректировки будут происходить правильно.
Вы также можете использовать подход. где вы настраиваете формулу СУММ для использования функции КОСВЕННО или СМЕЩЕНИЕ. Предположим на мгновение, что ваша текущая формула СУММ выглядит так:
= SUM (A2: A15)
Измените ее так, чтобы она выглядела как одна из следующее:
= SUM (INDIRECT ("A2"): A15) = SUM (OFFSET (A1,1,0): A15)
Обе эти формулы “привязать” верхнюю ячейку диапазона. Когда вы вставляете ячейки в верхнюю часть суммированного диапазона (перед строкой 2), Excel не корректирует ссылки на ячейки ни в функции ДВССЫЛ, ни в функции СМЕЩЕНИЕ. В результате ваша формула СУММ всегда будет ссылаться на нужную вам ячейку верхнего диапазона.