В этом видео мы рассмотрим, как настроить промежуточную сумму в таблице Excel.
Настройка промежуточной суммы в таблице Excel немного сложна, потому что не очевидно, как использовать структурированные ссылки.
Это связано с тем, что структурированные ссылки предоставляют обозначение для текущей строки, но не для первой строки в столбце.
Давайте рассмотрим несколько вариантов, начав с формул на основе регулярных ссылок.
Один из распространенных подходов - просто добавить строку выше к значению в текущей строке …
Но это вызовет ошибку, потому что заголовок столбца содержит текст.
В этом случае простой обходной путь - использовать вместо этого функцию СУММ. Поскольку СУММ автоматически обрабатывает текст как ноль.
И эта формула работает.
Но существует проблема. Если я добавлю новую строку, формула правильно расширится.
Если я вставлю строку в середину таблицы, формула будет работать неправильно.
Итак, с помощью этой формулы мне нужно будет исправить формулу вручную, если я вставлю строки.
Другой распространенный подход для подсчета итогов - использование расширяющейся ссылки внутри SUM.
Для этого я дважды ввел бы ссылку на первую ячейку, а затем заблокировал бы первую ссылку с помощью F4.
Результатом является ссылка, которая расширяется при перемещении вниз по таблице.
Сначала кажется, что все работает нормально. Если я вставляю строку, формула корректируется правильно.
Однако обратите внимание, что если я добавлю новую строку, формула будет повреждена.
Как и раньше, мне нужно будет исправить это вручную, если я добавлю новые строки.
Наконец, я создам расширяющийся диапазон, используя структурированные ссылки.
В псевдокоде нам нужен диапазон, который начинается с первой строки столбца и заканчивается в текущей строке.
= СУММ (первый: текущий)
Получить текущую строку очень просто. Если щелкнуть столбец, Excel создаст ссылку с пометкой @.
А как насчет первого ряда?
Ключ в том, чтобы использовать функцию ИНДЕКС.
Для массива мы используем весь столбец. В качестве номера строки я использую 1.
= СУММ (ИНДЕКС ((Итого); 1): (@ Итого))
Теперь ключом к пониманию этого является осознание того, что INDEX фактически возвращает адрес первой ячейке в столбце, а не значение.
Другими словами, диапазон разрешается по той же формуле, которую мы использовали ранее.
Но на этот раз формула может обрабатывать как вставленные, так и новые строки.
Единственным недостатком этой формулы является то, что она будет медленной для очень больших наборов данных, поскольку каждая формула должна генерировать сумму всех ячеек, указанных выше.
Если производительность становится проблемой, вы можете переключиться на первую формулу, которую мы попробовали в видео, и вручную обновить формулу, если вам нужно вставить строки.
Курс
Таблицы ExcelСвязанные ярлыки
Ввести и переместиться вниз Enter Return Удалить символ слева от курсора Backspace Delete Удалить символ справа от курсора Delete Fn + Delete Enter и переместиться вправо Tab Tab Выбрать всю строку Shift + Space ⇧ + Space Вставить строки Ctrl + Shift + + ⌘ + ⇧ + + Заполнить вниз из ячейки выше Ctrl + D ⌃ + D Переместить активную ячейку влево в выделенном фрагменте Shift + Tab ⇧ + Tab Отменить выбор Esc Esc
