Итоговые таблицы с динамическими массивами

Итоговая таблица сжимает большой список исходных строк в небольшой, удобный для чтения блок: по одной строке на категорию, рядом с ней — итоги. Представьте журнал продаж из сотен строк, превращённый в аккуратную таблицу, где указаны каждый регион и его общая выручка.

Раньше для этого использовали сводную таблицу, которую приходилось обновлять вручную. Современный способ — применять формулы динамических массивов, которые обновляются сами сразу после изменения данных. Никаких кнопок и обновления вручную.

В этом уроке Вы объедините три мощных инструмента: UNIQUE, чтобы перечислить категории, SUMIFS, чтобы получить итог по каждой из них, и FILTER, чтобы извлечь соответствующие строки. Вместе они создают динамическую итоговую таблицу.

Представьте лист с именем Продажи и тремя столбцами: Регион в столбце A, Товар в столбце B и Сумма в столбце C; данные занимают строки с 2 по 200.

Наша цель — создать итоговую таблицу, в которой будут указаны каждый уникальный регион и общий объём его продаж. Первая задача — получить чистый список регионов без ручного ввода, поскольку позднее могут появиться новые регионы.

• A2:A200 содержит множество повторяющихся названий регионов, например Восток, Запад, Восток, Север.
• Нам нужны только: Восток, Запад, Север — каждый регион должен быть указан один раз.

Этот список уникальных значений — основа всей итоговой таблицы.

Функция UNIQUE принимает диапазон и возвращает каждое значение только один раз. Она разливается, то есть одна формула заполняет столько ячеек, сколько имеется уникальных значений.

Введите эту формулу в ячейку E2, и список регионов автоматически появится под ней:

Если позднее в данные добавить новый регион, разлитый список самостоятельно расширится. Вам никогда не придётся изменять формулу.

Теперь нужно получить общую сумму для каждого региона из списка в столбце E. SUMIFS складывает значения одного диапазона только тогда, когда другой диапазон соответствует условию.

Структура функции такова: SUMIFS(sum_range, criteria_range, criteria). Поместите эту формулу в F2 рядом с первым регионом:

Ссылка E2# — вот ключевой момент. Знак # означает весь разлитый диапазон, начинающийся в E2. Поэтому одна формула получает итоги для всех регионов, созданных функцией UNIQUE.

Ссылка на разлитый диапазон E2# всегда указывает на весь блок, созданный формулой, независимо от его размера. Именно это делает итоговую таблицу динамической.

Когда UNIQUE находит 3 региона, E2# занимает 3 ячейки по высоте, а SUMIFS возвращает 3 итоговых значения. Когда количество регионов увеличивается до 5, оба диапазона расширяются одновременно, без единого изменения.

• E2 = только одна верхняя ячейка.
• E2# = весь разлитый массив, начинающийся в E2.

Освойте знак #: он лежит в основе формул панелей мониторинга.

Итоговую таблицу удобнее читать, когда значения упорядочены. Оберните список регионов в SORT, чтобы категории отображались в алфавитном порядке, или отсортируйте всю таблицу по итогу.

Чтобы вывести регионы в алфавитном порядке в E2:

Поскольку значения в столбце F по-прежнему ссылаются на E2#, после сортировки регионов итоги автоматически перестраиваются в том же порядке. Оба столбца остаются синхронизированными.

Иногда Вам нужны исходные строки одной категории, а не только её итог. FILTER возвращает каждую строку, соответствующую условию, и разливает результат.

Чтобы показать все строки продаж, в которых регион совпадает со значением в ячейке H1:

Если в H1 указано Восток, Вы получите все строки для Востока. Измените H1 на Запад — и блок сразу перестроится. Это основа представления с детализацией на панели мониторинга.

FILTER выдаёт ошибку #CALC!, если совпадений нет. Чтобы результат выглядел аккуратно, укажите необязательный третий аргумент — резервное сообщение.

Третий аргумент отображается, когда совпадений нет:

Теперь для региона без продаж будет показано понятное сообщение, а не ошибка. Всегда добавляйте этот резервный вариант на панелях мониторинга, чтобы случайно выбранное значение не нарушило структуру.

В итоговой таблице часто показывают не только сумму денег, но и количество заказов для каждого региона. COUNTIFS подсчитывает строки, соответствующие условию, подобно SUMIFS, но без диапазона суммирования.

Поместите эту формулу в столбец G рядом с итоговыми значениями:

Теперь Ваша итоговая таблица из трёх столбцов содержит Регион, Общие продажи и Количество заказов; все они зависят от единого разлитого списка регионов в E2#. Всё обновляется одновременно.

Вот полная схема, размещённая рядом:

• E2: =SORT(UNIQUE(Sales!A2:A200)) выводит регионы.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) получает итог для каждого региона.
• G2: =COUNTIFS(Sales!A2:A200, E2#) подсчитывает строки для каждого региона.

Вручную вводится только формула E2; формулы в F и G разливаются благодаря ссылке с символом #. Добавьте новую продажу в любом месте листа Продажи — и все три столбца обновятся без единого щелчка.

Итоговая таблица на основе формул имеет реальные преимущества перед ручным вводом значений или обновлением сводной таблицы:

• Обновляется сразу: пересчитывается в момент изменения данных.
• Автоматически изменяет размер: новые категории появляются благодаря UNIQUE и ссылке с символом #.
• Понятна: любой пользователь может прочитать логику в ячейке.

Недостаток заключается в том, что разлитым диапазонам нужно свободное место для расширения; заблокированные разливы мы рассмотрим в следующем уроке. А пока оставьте под формулами свободное пространство.

Проверьте, что Вы узнали о создании самостоятельно обновляемой итоговой таблицы.

Вы создали итоговую таблицу, которая поддерживает себя сама:

• UNIQUE выводит каждую категорию один раз и разливает результат.
• SORT упорядочивает этот список для удобства чтения.
• SUMIFS и COUNTIFS получают итог и количество для каждой категории с помощью ссылки на разлитый диапазон E2#.
• FILTER извлекает соответствующие строки для детализации и показывает резервное сообщение, если совпадений нет.

Поскольку каждая формула опирается на разлитый список, добавление новых данных обновляет всю итоговую таблицу без ручных действий. Далее Вы создадите полноценные отчёты в стиле сводных таблиц только с помощью формул.