Переход от GROUPBY к PIVOTBY в Excel: новая динамика сводных таблиц
Функция PIVOTBY в Excel — это мощный инструмент для создания динамических сводок данных, которые обновляются автоматически при изменении исходных данных. Если вы уже освоили GROUPBY, переход к PIVOTBY будет простым и логичным шагом.
Полный синтаксис: =СВОДПО(row_fields;col_fields;values;function;[field_headers];[row_total_depth];[row_sort_order];[col_total_depth];[col_sort_order];[filter_array];[relative_to]). В английском Excel — PIVOTBY с запятыми. Пример для данных без заголовков: A4:A21 — категория, B4:B21 — регион, C4:C21 — продажи; =СВОДПО(A4:A21;B4:B21;C4:C21;СУММ;0) / =PIVOTBY(A4:A21,B4:B21,C4:C21,SUM,0).
Русское имя PIVOTBY — СВОДПО. Здесь используется актуальная Microsoft 365 для Windows, Mac или веба. В алфавитном справочнике PIVOTBY отмечена как Microsoft 365; для постоянных лицензий Excel 2021/2024 доступность не обещается, несмотря на широкий список «Применяется к» на отдельной странице справки.
Руководство пригодится тем, кто хочет понять, как сделать сводную таблицу в Excel, настроить поля, расчеты и получить наглядный отчет по данным.
Главное отличие и преимущество PIVOTBY — это возможность группировать данные не только по строкам, но и по столбцам, создавая полноценные кросс-таблицы формулами.
Синтаксис и аргументы PIVOTBY
Функция PIVOTBY расширяет возможности GROUPBY, добавляя три ключевых аргумента для работы со столбцами:
- Col_Fields — поля для группировки по столбцам.
- Col_Total_Depth — настройка отображения итогов по столбцам.
- Col_Sort_Order — порядок сортировки данных в столбцах.
Эти аргументы работают аналогично параметрам для строк (Row_Fields, Total_Depth, Sort_Order), но применяются к столбцам в итоговом массиве.
PIVOTBY vs. Сводные таблицы: что выбрать?
Возникает закономерный вопрос: зачем нужен PIVOTBY, если можно создать обычную сводную таблицу? Ключевое преимущество — автоматический пересчет.
- Формула
PIVOTBYпересчитывается при изменении данных, если включён автоматический расчёт. В ручном режиме нужен пересчёт. Обновление внешнего источника и расширение фиксированной ссылки остаются отдельными задачами. - Это делает отчеты более надежными и динамичными в средах, где данные часто меняются.
Означает ли это смерть сводных таблиц? Конечно, нет. Классические сводные таблицы по-прежнему предлагают множество функций, недоступных в PIVOTBY.
Ограничения PIVOTBY
СВОДПО возвращает массив формулы, а не объект сводной таблицы с её полями и средствами управления. Однако доли от итогов поддерживаются: используйте ПРОЦЕНТ / PERCENTOF и relative_to: 0 — итог столбца (по умолчанию), 1 — итог строки, 2 — общий итог, 3 — итог родительского столбца, 4 — итог родительской строки. Эти режимы действуют для агрегирующей функции с двумя аргументами. Результат доли оформите как процент.
Заключение
PIVOTBY — это революционный шаг для Excel, который объединяет мощь формул с гибкостью сводных таблиц. Используйте его для создания автоматически обновляемых отчетов и дашбордов, где критически важна актуальность данных. Для более глубокого анализа с расширенными вычислениями продолжайте использовать классические сводные таблицы. Комбинируйте оба инструмента для максимальной эффективности.