Функция PIVOTBY в Excel позволяет вам группировать ваши данные без необходимости заново создавать данные в сводной таблице. Более того, сводки данных, созданные с помощью PIVOTBY, автоматически обновляются, чтобы отражать изменения в ваших исходных данных, и вы можете настроить то, что они показывают, изменив формулу.
Если вы уже знаете, как использовать функцию GROUPBY, научиться использовать функцию PIVOTBY будет просто. Разница в том, что GROUPBY только группирует переменные в строки, а PIVOTBY имеет дополнительный аргумент, который позволяет вам также группировать определенные переменные в столбцы.
Синтаксис PIVOTBY
Функция PIVOTBY позволяет вам ввести в общей сложности 11 аргументов. Хотя это звучит пугающе, преимущество наличия такого количества аргументов заключается в том, что вы можете настроить результат в соответствии с тем, что вы хотите, чтобы отображали данные.
Однако обязательными являются только первые четыре поля.
Обязательные поля
Обязательные поля PIVOTBY используются для указания Excel того, какие переменные вы хотите отобразить в строках, какие переменные вы хотите отобразить в столбцах, где найти значения и как вы хотите агрегировать данные:
=СВОДКА ПО(a,b,c,d)
в котором
- a — это ячейки, содержащие категорию или категории, которые вы хотите отобразить в качестве заголовков строк в левой части вашего результата,
- b ячейки, содержащие категорию или категории, которые вы хотите отобразить в качестве заголовков столбцов в верхней части результата,
- c — это значения, которые появятся в центре вашего результата в соответствии со строками и столбцами, выбранными вами для аргументов. a и b и
- d — это функция Excel (или функция, созданная вами самостоятельно с помощью LAMBDA), которая определяет, как будут агрегироваться данные.
Хотя аргументы a и b необходимы для того, чтобы PIVOTBY выдавал тот тип результата, для которого он был создан, вы можете их опустить, чтобы сгруппировать данные только по строкам или столбцам.
Дополнительные поля
Необязательные поля PIVOTBY позволяют включать заголовки и итоги, сортировать данные и — в особых случаях — указывать некоторые дополнительные параметры:
=СВОДКА ПО(a,b,c,d,[e,f,g,h,i,j,k])
|
Аргумент |
Что это такое? |
Заметки |
|---|---|---|
|
e |
Число, указывающее, являются ли аргументы a, b и c включить заголовки и хотите ли вы, чтобы заголовки отображались в результате |
0 = Без заголовков (по умолчанию) 1 = Заголовки, но не отображаются 2 = Нет заголовков, но они генерируются 3 = Заголовки и показ |
|
f |
Число, которое определяет, является ли аргумент a должны содержать общие итоги и промежуточные итоги |
0 = Нет итогов 1 = Общие итоги внизу (по умолчанию) 2 = Общие итоги и промежуточные итоги внизу -1 = Общие итоги наверху -2 = Общие итоги и промежуточные итоги вверху |
|
g |
Число, указывающее, как следует сортировать столбцы. |
Число относится к столбцам в аргументе. a, за которым следует столбец в аргументе c. Например, если у вас было два столбца в аргументе a, «2» отсортирует результат по второму столбцу этого аргумента, а «3» отсортирует результат по аргументу cПоложительное число представляет алфавитный или возрастающий порядок, а отрицательное число представляет обратный. |
|
h |
Число, которое определяет, является ли аргумент b должны содержать общие итоги и промежуточные итоги |
0 = Нет итогов 1 = Общие итоги справа (по умолчанию) 2 = Общие итоги и промежуточные итоги справа -1 = Общие итоги слева -2 = Общие итоги и промежуточные итоги слева |
|
i |
Число, указывающее, как следует сортировать строки. |
Число относится к столбцам в аргументе. b, за которым следует столбец в аргументе c, и работает так же, как аргумент g. |
|
j |
Логическое выражение или формула, позволяющая отфильтровывать строки, содержащие определенные значения или текст. |
ARCXNUMX |
|
k |
Число, которое дает вам больше контроля над тем, как выполняются вычисления результата, когда вы используете функцию PERCENTOF в аргументе. d |
0 = Итоги по столбцам (по умолчанию) 1 = Итоги по строкам 2 = Общая сумма 3 = Итог родительского столбца 4 = Общая сумма родительской строки |
PIVOTBY: использование только обязательных аргументов
Сначала позвольте мне показать вам, как использовать функцию PIVOTBY в ее простейшей форме, используя всего четыре обязательных поля.
Допустим, вам передали эту таблицу данных под названием Sports_Viewers. Она показывает цифры просмотра в прямом эфире (столбец D) для шести видов спорта (столбец B) в четырех регионах (столбец C) за четыре года (столбец A), и вас попросили создать сводку общего количества зрителей по виду спорта и году.
могли бы используйте функцию GROUPBY, чтобы сделать это, хотя вам нужно будет использовать необязательные аргументы, чтобы получить результат, который упрощает анализ. Более того, поскольку GROUPBY сортирует данные только по строкам, вы можете получить длинный список информации, которую трудно интерпретировать.
Вместо этого использование функции PIVOTBY позволяет вам взять одну или несколько переменных и поместить их в столбцы, что дает вам более четкую картину того, как складываются ваши числа.
Поскольку формула PIVOTBY создает динамический разбросанный массив, вы должны ввести ее формулу в область вашей электронной таблицы, которая отформатирован в виде таблицы Excel.
Для этого в ячейке F1 введите
=PIVOTBY(Зрители_спорта[Спорт],Зрители_спорта[Год],Зрители_спорта[Зрители],СУММА)
в котором
- Спортивные_зрители[Спорт] — это именованный столбец в именованной таблице, представляющий шесть различных видов спорта. Это будут заголовки строк в левой части вашего результата.
- Спортивные_зрители[Год] представляет столбец «Год» таблицы, а это будут заголовки столбцов в верхней части результата.
- Sports_Viewers[Зрители] — это столбец, показывающий количество людей, которые смотрели эти виды спорта в указанные годы, поэтому эти данные будут центральной частью результата.
- SUM сообщает Excel, что вы хотите, чтобы столбцы итогов суммировали цифры. С другой стороны, если, например, вы используете СРЗНАЧ, результат покажет вам среднее этих цифр.
Вместо того чтобы вручную вводить имена таблиц и столбцов в формуле (также известные как структурированные ссылки), если вы выделите ячейки в таблице с помощью мыши, Excel введет их за вас.
Благодаря тому, как PIVOTBY отображает и организует данные, вы можете сразу же извлечь выводы из результата. Например, вы можете увидеть, что данные отсутствуют для определенных видов спорта в определенные годы. Кроме того, при отсутствии дополнительных полей Excel автоматически добавляет итоги как в строки, так и в столбцы результата PIVOTBY, что означает, что вы можете мгновенно увидеть общие цифры просмотров за год и по каждому виду спорта.
Без дополнительных аргументов Excel также сортирует результаты в алфавитном порядке или по возрастанию аргумента. a, и не включает заголовки столбцов для аргумента a.
Теперь предположим, что вы хотите включить столбец, который делит каждый вид спорта на четыре различных региона. Чтобы сделать это, вам нужно включить и вид спорта, и регион в аргумент a:
=СВОДКА ПО(Спортивные_зрители[[Спорт]:[Регион]],Зрители_Спорта[Год],Зрители_Спорта[Зрители],СУММА)
Чтобы сгруппировать несмежные столбцы в аргумент, используйте функцию CHOOSECOLS.
Этот результат обеспечивает более детальное понимание ваших данных, показывая общие цифры просмотра каждого вида спорта по регионам. Однако, поскольку вы добавили дополнительную переменную в аргумент a, чтобы сделать результат более понятным, вы можете ввести некоторые необязательные аргументы.
PIVOTBY: использование необязательных аргументов
Всякий раз, когда вы создаете формулу Excel, каждый аргумент отделяется запятой. Это означает, что если вы хотите пропустить любой из необязательных аргументов, вам просто нужно ввести первую запятую, чтобы открыть аргумент, а затем вторую запятую, чтобы закрыть его. Тот факт, что между этими двумя запятыми ничего нет, говорит Excel, что вы намеренно пропустили этот аргумент, поэтому вы хотите, чтобы программа приняла настройку по умолчанию.
Пример 1: Общие итоги и промежуточные итоги
Один из способов сделать результат PIVOTBY в предыдущем примере более понятным — добавить строки промежуточных итогов для каждого вида спорта.
Для этого вам необходимо ввести:
=PIVOTBY(Зрители_спорта[[Спорт]:[Регион]],Зрители_спорта[Год],Зрители_спорта[Зрители],СУММА, 2)
Обратите внимание, как после аргумента d (SUM), две запятые указывают, что вы хотите перепрыгнуть через аргумент e (заголовки), но включают аргумент f (в этом случае, 2 означает, что вы хотите включить промежуточный итог в конце каждого вида спорта, а также общие итоги в конце общего результата).
Чтобы сделать данные еще более понятными, я использовал условное форматирование, чтобы раскрасить все строки в результате, где столбец F не пустой, столбец G пустой, и столбец F не содержит слов «Grand Total». Применение прямого форматирования к выброшенным массивам не рекомендуется, так как форматирование прикрепляется к ячейкам, а не к данным. Это означает, что если данные изменятся, форматирование не изменится вместе с ними.
Пример 2: Порядок сортировки
Теперь давайте рассмотрим другой способ обработки результата: сортировку по одному из выходных столбцов.
Печатать:
=PIVOTBY(Зрители_спорта[[Спорт]:[Регион]],Зрители_спорта[Год],Зрители_спорта[Зрители],СУММА, 2,3)
означает, что после пропуска аргумента e, а также включая общие итоги и промежуточные итоги (число 2 в споре f), вы также хотите упорядочить свои данные по общим просматриваемым цифрам в порядке возрастания (число 3 в споре g).
Обратите внимание, что сортировка по общему количеству просмотров не только располагает виды спорта в порядке их общих итогов, но и региональные подкатегории в порядке их промежуточных итогов.
Пример 3: Проценты (PERCENTOF)
Вот как просмотреть результат в процентах.
Печатать:
=PIVOTBY(Зрители_Спорта[Спорт],Зрители_Спорта[Год],Зрители_Спорта[Зрители],ПРОЦЕНТ,,,2,,,,2)
сообщает Excel, что вы хотите, чтобы в заголовках строк были указаны виды спорта (аргумент a), годы в качестве заголовков столбцов (аргумент b), и вы хотите объединить свои данные (аргумент c) в виде процентов (аргумент d). Первый 2 (аргумент g) представляет собой порядок, который в данном случае по значениям. Наконец, второй 2 (аргумент k) сообщает Excel, что проценты должны быть относительно общих данных. Незаполненные запятые сообщают Excel, что вы хотите принять значение по умолчанию для аргументов е, е, з, я, и j.
Теперь выберите ячейки, содержащие десятичные результаты, и щелкните значок «Процентный стиль» в группе «Число» вкладки «Главная» на ленте. Пока вы там, щелкните кнопки «Увеличить десятичную» и «Уменьшить десятичную», чтобы определить количество десятичных знаков.
Выберите дополнительные строки под результатом на случай, если в будущем в исходную таблицу будут добавлены дополнительные данные.
Наконец, уделите немного времени тому, чтобы осмыслить то, что вам говорит этот результат.
Например, количество людей, смотрящих софтбол в 2022 году, составляет 11.5% от общего числа просмотров всех видов спорта за все годы. Кроме того, поскольку данные организованы по столбцу «Общее» в порядке возрастания, вы можете видеть, что почти четверть (23.7%) от общего числа просмотров приходится на людей, смотрящих баскетбол.
Помимо использования SUM, AVERAGE и PERCENTOF в качестве аргумента функции в PIVOTBY, вы также можете использовать другие функции агрегации, такие как COUNT, MIN или MAX.
