Функция Excel PIVOTBY — формула, альтернативная сводным таблицам

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

Десятилетиями пользователи Excel полагались на сводные таблицы для преобразования строк и столбцов информации в содержательные отчеты. Теперь есть кое-что еще лучше: новая функция PIVOTBY. Этот удобный инструмент позволяет вам суммировать и анализировать данные прямо в формуле без необходимости в отдельных сводных таблицах. Он полностью динамичен, обновляется в режиме реального времени и идеально вписывается в ваши существующие рабочие процессы.

Базовая формула Excel PIVOTBY

Чтобы понять концепцию функции PIVOTBY, давайте рассмотрим простой пример. Представьте, что у вас есть набор данных, показанный на снимке экрана ниже, с продуктами, перечисленными в (B3:B32), странами в (C3:C32) и суммами в (D3:D32). Вы хотите свернуть данные, сгруппировав продукты в строках, а страны в столбцах. Для функции агрегации давайте выберем SUM, чтобы получить общий объем продаж для каждой пары продукт-страна.

Полная формула имеет следующий вид:

=СВОДНОЙ_ЭЛЕМЕНТПО(B3:B32; C3:C32; D3:D32; СУММА)

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

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

Как использовать функцию PIVOTBY в Excel – примеры формул

Теперь, когда вы знакомы с основами функции PIVOTBY, давайте рассмотрим практические примеры ее эффективного использования в формулах Excel.

Группировка на основе нескольких столбцов

Функция Excel PIVOTBY поддерживает группировку по нескольким столбцам, что невероятно удобно, когда вам нужно анализировать сложные наборы данных.

Группировка по соседним столбцам

Чтобы сгруппировать данные по соседним столбцам, просто выберите диапазон из нескольких столбцов для аргументов row_fields и/или col_fields.

Например, чтобы организовать продукты (B3:B32) и страны (C3:C32) в строках, а продавцов (D3:D32) в столбцах, можно использовать следующую формулу:

=СВОДНОЙ_ОБЪЕКТ(B3:C32; D3:D32; E3:E32; СУММА)

Группировка по несмежным столбцам

Если вам нужно сгруппировать данные на основе несмежных столбцов, вы можете использовать функцию CHOOSECOLS, чтобы выбрать нужные столбцы. Например, чтобы сгруппировать продукты и продавцов в строках, а страны в столбцах, формула будет выглядеть так:

=СВОДНОЙ_ЭЛЕМЕНТПО(ВЫБОР_ДЕКАБРЯ(B3:D32; 1;3); C3:C32; E3:E32; СУММА)

В этой формуле CHOOSECOLS выбирает 1-й и 3-й столбцы из диапазона B3:D32.

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

=СВОДНОЙ_ЭЛЕМЕНТ_ПО(HSTACK(B3:B32, D3:D32), C3:C32, E3:E32, СУММА)

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

Объединить несколько столбцов

Иногда возникает необходимость суммировать несколько столбцов или выполнить различные агрегации по одной и той же метрике.

Агрегирование смежных столбцов

Чтобы объединить соседние столбцы, просто выберите диапазон, включающий все столбцы, которые вы хотите суммировать. Например, чтобы рассчитать среднюю цену за единицу и сумму для каждой комбинации продукт-страна, вы можете использовать эту формулу:

=СВОДНАЯ_ИНФОРМАЦИЯ(B2:B32, C2:C32, E2:F32, СРЗНАЧ, 3, 1, ,0)

Формула включает диапазон E2:F32 для агрегации столбцов Unit Price и Amount. Необязательные аргументы задаются для отображения заголовков полей (3 для field_headers) и избегания добавления итогового столбца (0 для column_total_depth).

Суммирование несмежных столбцов

Для агрегации несмежных столбцов можно использовать либо функцию CHOOSECOLS для указания номеров целевых столбцов, либо HSTACK для объединения их в один массив.

Например, чтобы усреднить столбцы Кол-во (D2:D32) и Сумма (F2:F32), формулы будут следующими:

=СВОДКАПО(B2:B32, C2:C32, HSTACK(D2:D32, F2:F32), СРЗНАЧ, 3, , ,0)

=PIVOTBY(B2:B32, C2:C32, CHOOSECOLS(D2:F32, 1, 3), СРЗНАЧ, 3, , ,0)

Выполнение нескольких агрегаций в одном столбце

Чтобы использовать разные функции для агрегации одних и тех же столбцов значений, необходимо предоставить вектор соответствующих лямбда-функций. Это можно сделать с помощью HSTACK.

Например, чтобы получить как среднее значение, так и количество продаж, формула выглядит так:

=СВОДКА ПО(B2:B32, C2:C32, F2:F32, HSTACK(СРЕДНЕЕ, КОЛИЧЕСТВО), , , ,0)

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

Группируйте данные только в строках или столбцах

Хотя синтаксис PIVOTBY помечает как обязательные поля row_fields и col_fields, у вас есть возможность группировать данные исключительно по строкам или столбцам, задав только один из этих аргументов.

Группировка в ряды

Если вы опустите аргумент col_fields, функция сгруппирует данные по строкам, аналогично функции GROUPBY. Например:

=СВОДНОЙ_ЭЛЕМЕНТПО(B2:B32, , D2:D32, СУММА)

Группировка по столбцам

И наоборот, если вы опустите row_fields, данные будут сгруппированы исключительно по столбцам. Например:

=СВОДНОЙПО(, C2:C32, , D2:D32, СУММА)

Такая универсальность позволяет вам точно настраивать сводки данных для различных сценариев.

Показать общий итог и промежуточные итоги

Чтобы включить общие итоги и промежуточные итоги в результаты PIVOTBY, можно соответствующим образом настроить аргументы row_total_depth и col_total_depth.

Например, если вы хотите отобразить итоги по строкам и промежуточные итоги внизу, а итоги по столбцам — нет, вам следует установить row_total_depth равным 2, а col_total_depth — равным 0:

=СВОДНАЯ_ИНФОРМАЦИЯ(B2:C32, D2:D32, E2:E32, СУММА, , 2, , 0)

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

Примечание. Убедитесь, что значение total_depth соответствует уровню группировки в ваших row_fields и col_fields. Например, если вы задаете значение глубины, включающее промежуточные итоги (2 или -2), убедитесь, что у вас есть несколько уровней группировки в соответствующих полях.

Пользовательская сортировка

По умолчанию функция PIVOTBY упорядочивает метки строк и столбцов в порядке возрастания от A до Z.

Чтобы настроить сортировку в результатах PIVOTBY, вы можете использовать аргументы row_sort_order и col_sort_order. Вот как можно применить пользовательскую сортировку:

• По возрастанию — используйте положительные числа для столбцов, которые вы хотите отсортировать от А до Я.
• По убыванию — используйте отрицательные числа для столбцов, которые вы хотите отсортировать от Я до А.
• Сортировка по нескольким столбцам — укажите одномерный массив чисел, которые соответствуют столбцам в row_fields или col_fields. Вы можете сортировать разные столбцы в разных порядках.

В примере ниже у нас есть 2-уровневая группировка строк (по продукту и стране) и одноуровневая группировка столбцов (по продавцу). По умолчанию все будет отсортировано по возрастанию.

Чтобы отсортировать строки и столбцы от Z до A, укажите массив {-1, -2} для row_sort_order, чтобы расположить столбцы 1 и 2 в row_fields (B2:C32) в порядке убывания. Для col_sort_order используйте -1, чтобы также отсортировать единственный столбец в col_fields (D2:D32) в порядке убывания.

=СВОДНАЯ_ИНФОРМАЦИЯ(B2:C32, D2:D32, E2:E32, СУММА, , 0, {-1,-2}, 0, -1)

В результате вы получите сводную таблицу, обеспечивающую обзор ваших данных сверху вниз:

В некоторых сценариях может быть более информативно сортировать результаты по агрегированным значениям. Для этого можно соответствующим образом настроить аргумент row_sort_order или col_sort_order.

В примере ниже, где строки и столбцы сгруппированы по одному столбцу (по продуктам и по продавцу соответственно), вы можете отсортировать по СУММЕ суммы, используя 2 для возрастающего порядка или -2 для убывания:

=СВОДНОЙ_ЭЛЕМЕНТПО(B2:B32, D2:D32, E2:E32, СУММА, ,0, -2)

В результате значения в столбце «Итого» будут отсортированы от наибольшего к наименьшему, что позволит получить четкое представление о том, какие продукты приносят наибольший доход.

Примечание. Если формула приводит к ошибке #ЗНАЧЕНИЕ!, убедитесь, что числа в аргументах row_sort_order и col_sort_order соответствуют количеству столбцов в row_fields и col_fields соответственно.

Отфильтровать определенные строки

Чтобы отфильтровать определенные строки в результатах PIVOTBY, можно использовать аргумент filter_array. Этот аргумент принимает одномерный массив булевых значений, которые соответствуют строкам в вашем наборе данных (TRUE указывает строки для включения, а FALSE указывает строки для исключения). Чтобы сгенерировать такой массив, можно построить логическое выражение с логическими операторами.

Например, чтобы исключить продажи в Канаде, формула будет следующей:

=PIVOTBY(B2:C32, D2:D32, E2:E32, COUNT, , , , , , C2:C32<>“Канада”)

А если вы хотите посчитать только суммы, превышающие 200 долларов, вы можете сделать это с помощью следующей формулы:

=СВОДНОЙ_ЭЛЕМЕНТПО(B2:C32, D2:D32, E2:E32, СЧЁТ, , , , , , E2:E32>200)

Суммировать по процентам

Чтобы указать, как выполняется расчет PERCENTOF относительно различных итогов, используйте аргумент relation_to функции PIVOTBY:

• Итоги по столбцам (0 или пропущены) — расчет выполняется относительно всех значений в столбце, при этом итог по каждому столбцу равен 100%.
• Итоги по строкам (1) — расчет производится относительно всех значений в строке, при этом итог по каждой строке равен 100%.
• Общий итог (2) – расчет ведется относительно всех значений. Общий итог равен 100%.
• Итог родительского столбца (3) — расчет выполняется относительно всех значений в родительском столбце.
• Итог родительской строки (4) — расчет выполняется относительно всех значений в родительской строке.

Рассмотрим следующий пример с двухуровневой группировкой строк (по продукту и по стране).

Чтобы вычислить процент каждого значения относительно итога его столбца, просто используйте функцию PERCENTOF. Поскольку значение по умолчанию для аргумента relation_to равно 0, что соответствует итогу столбца, вы можете определить его явно или положиться на поведение по умолчанию.

=PIVOTBY(B2:C32; D2:D32; E2:E32; PERCENTOF)

Чтобы вычислить процент каждого значения относительно итога строки, установите аргумент relation_to равным 1:

=PIVOTBY(B2:C32, D2:D32, E2:E32, PERCENTOF, , , , , , ,1)

На рисунке ниже показаны результаты обеих формул.

Поскольку наша формула группирует строки на 2 уровня (по продукту и по стране), вы также можете захотеть вычислить % от общего количества родительской строки, т.е. найти процент каждой страны от общего количества продукта. Для этого установите аргумент relation_to на 4:

=PIVOTBY(B2:C32, D2:D32, E2:E32, PERCENTOF, , 0, , 0, , ,4)

В полученной таблице вы можете увидеть, как продажи каждого продавца определенного продукта распределяются по разным странам. Например, Анна продала около 47% своих яблок в Мексике и около 53% в США. Карлос продал все свои яблоки в Мексике, поэтому у него 100% в ячейке Яблоки-Мексика. А ячейки Эмили пустые, так как она не продала ни одного яблока.

Используя эти вариации функции PIVOTBY с агрегацией PERCENTOF, вы можете создавать подробные и содержательные сводки ваших данных в процентном соотношении.

Группировать даты по годам

При работе с датами сводные таблицы Excel предлагают ряд удобных опций для группировки данных по годам, кварталам и месяцам. Чтобы добиться аналогичной функциональности с функцией PIVOTBY, вы можете использовать функции Excel для работы с датами, такие как MONTH и YEAR.

Например, чтобы сгруппировать даты в ячейках A2:A32 по году, можно использовать следующую формулу:

=СВОДКА ПО(B2:B32, ГОД(A2:A32), E2:E32, СРЕДНЕЕ)

Эта формула сгруппирует ваши данные по году, извлеченному из столбца A, и вычислит среднее значение соответствующих значений в столбце E, предоставив годовую сводку:

Показать заголовки

Чтобы отобразить заголовки полей при использовании функции PIVOTBY, необходимо установить пятый аргумент с именем field_headers равным 3, например:

=СВОДНАЯ_ИНФОРМАЦИЯ(B2:B32; C2:C32; E2:E32;СРЗНАЧ; 3)

При агрегировании значений только в одном столбце заголовки полей могут быть не особенно полезны. Например, в первой таблице ниже результаты говорят сами за себя без заголовков полей — вы можете видеть названия продуктов слева и названия стран вверху. Размещение метки «Страна» в верхней ячейке и дублирование заголовка поля «Сумма» в каждом агрегированном столбце не добавляет особой ясности результатам.

Однако при агрегировании нескольких столбцов, как во втором примере, заголовки полей становятся более полезными:

=СВОДНАЯ_ИНФОРМАЦИЯ(B2:B32, C2:C32, D2:E32, СРЗНАЧ, 3, , , 0)

Здесь заголовки полей «Кол-во» и «Сумма» помогают различать разные типы агрегированных данных, обеспечивая более четкое понимание обобщенной информации.

Сводные данные из нескольких таблиц

При получении данных из нескольких источников, например отчетов из разных региональных офисов или разных департаментов, вы можете использовать функцию PIVOTBY в сочетании с VSTACK для консолидации данных.

Например, чтобы сгруппировать и суммировать две исходные таблицы, как показано на рисунке ниже, можно использовать следующую формулу:

=СВОДНОЙ_ЭЛЕМЕНТ_ПО(VСТЕК(D3:D17, D22:D37), VСТЕК(C3:C17, C22:C37), VСТЕК(E3:E17, E22:E37), СУММА)

Здесь мы используем три отдельные функции VSTACK для объединения продавцов, стран и сумм в двух разных таблицах, которые затем группируются и суммируются с помощью функции PIVOTBY.

Надеюсь, этот урок дал вам базовые знания о функции PIVOTBY и дал представление о том, как использовать ее возможности.