Функция PIVOTBY и сводные таблицы Excel суммируют и реорганизуют данные, позволяя проводить детальный анализ путем группировки и агрегации ваших данных. Однако, несмотря на схожие названия, методы их использования существенно различаются.
Использование функции PIVOTBY в Excel
Функция PIVOTBY — это динамическая функция массива в Excel, которая использует формулу для группировки строк и столбцов данных для создания табличных сводок ваших данных. Давайте рассмотрим некоторые из ее основных функций и характеристик.
Ограниченная совместимость
Microsoft представила функцию PIVOTBY для общего использования в Excel в сентябре 2024 года, примерно через год после ее представления участникам программы Microsoft Insiders. В результате на момент написания статьи (апрель 2025 года) она доступна только пользователям Excel для Microsoft 365 на ПК с Windows или Mac.
Это означает, что если вы планируете поделиться файлом Excel, в котором используется функция PIVOTBY, с кем-то, кто использует более старую версию программы, они, скорее всего, увидят ошибку #NAME? вместо красиво сгруппированного массива данных, который вы видите на экране.
Автоматическое обновление
Вероятно, одним из самых больших преимуществ функции PIVOTBY является то, что она автоматически пересчитывает и изменяет форму при каждом изменении источника данных.
С другой стороны, если вы используете сводную таблицу, вам необходимо либо выполнить ручное обновление для отображения последней версии ваших данных, либо создать код VBA для автоматизации этого процесса. Однако в сентябре 2024 года Microsoft изменила поведение приложений Office по умолчанию, чтобы заблокировать файлы, содержащие макросы VBA, для повышения безопасности.
Хотя обновление сводных таблиц вручную в Excel — это простой процесс, легко забыть сделать это, и вы можете подумать, что смотрите на самую последнюю версию своих данных, когда на самом деле это не так. С функцией PIVOTBY вам не нужно беспокоиться о том, что вы забудете выполнить этот важный шаг.
Широкие возможности настройки
Один из аспектов PIVOTBY, который я лично нашел бесконечно полезным до сих пор, это его гибкость, позволяющая тонко настраивать выходные данные. Функция позволяет вам:
- Определите переменные, которые формируют строки и заголовки выходных данных,
- Выберите, хотите ли вы включить заголовки,
- Объединяйте данные, используя множество различных функций Excel или функцию, которую вы создаете самостоятельно с помощью LAMBDA,
- Создание иерархических группировок в выходных данных,
- Сортировать данные по указанным строкам и столбцам,
- Отфильтруйте нежелательные данные и
- Переведите итоговые значения в проценты.
Хотя сводные таблицы также позволяют вам выполнять многие из вышеперечисленных действий, тот факт, что все эти настройки объединены в одной функции, означает, что PIVOTBY — это отличный способ настроить ваши данные в одном месте — вам не нужно знакомиться с панелью полей сводной таблицы или искать различные параметры на ленте.
Более того, функция PIVOTBY работает независимо от того, организованы ли исходные данные в столбцы или в строки, тогда как для сводных таблиц требуется, чтобы исходные данные были организованы только в столбцы.
Зависимость формулы
Лично я иногда предпочитаю использовать PIVOTBY вместо PivotTables, потому что мне нравится создавать и манипулировать формулами Excel. Может быть, это только мне так кажется, но мне нравится добавлять аргумент в формулу, нажимать Enter и смотреть, как это влияет на вывод. По той же причине я не хочу использовать Copilot для выполнения сложных задач в моей электронной таблице.
Если вы придерживаетесь такого же склада ума, то после первого использования функции PIVOTBY она станет для вас интуитивно понятной и, скорее всего, станет для вас тем маршрутом, по которому вы будете анализировать множество источников данных в будущем.
Тем не менее, отправка таблицы Excel, содержащей PIVOTBY, кому-то, кто не знаком с тем, как работает эта функция, может вызвать проблемы, если ему нужно будет скорректировать данные или настроить ее визуализацию. Другими словами, PIVOTBY — это отличная функция, которую можно использовать, если таблица предназначена только для вас или если вы делитесь ею с другими, кто понимает, как она работает.
Использование сводных таблиц в Excel
Инструмент Excel PivotTable хорошо известен как одна из самых мощных функций программы для извлечения данных из больших наборов данных и представления их в управляемой и читаемой форме. Вот некоторые вещи, которые вам следует знать о том, что они предлагают.
Широкая совместимость
Первоначально предназначенные для использования в бизнесе, сводные таблицы были введены в Excel в середине 1990-х годов, и с тех пор Microsoft продолжает улучшать их функциональность. В результате инструмент доступен в большинстве версий программы, а сводные таблицы, какими мы их знаем сегодня, доступны в Excel 2016 и более поздних версиях, Microsoft Office, Excel для веб-сайта и Excel для Microsoft 365 на ПК или Mac.
В результате вам не придется беспокоиться о совместимости, если вы решили не переходить на Office 2024 или если вы отправляете свою книгу другим лицам.
Специальный пользовательский интерфейс
Как только вы выберете данные, щелкните «Сводная таблица» на вкладке «Вставка» на ленте и выберите, хотите ли вы, чтобы таблица отображалась на текущем листе или на новой вкладке, Excel откроет панель «Поля сводной таблицы».
Здесь вы можете установить флажки для имен полей, чтобы решить, какие переменные вы хотите включить в свои данные, а поля в нижней части панели позволяют организовать их в строки и столбцы.
Более того, выбрав сводную таблицу, вы можете дополнительно настроить ее на вкладке «Конструктор» или получить аналитическую информацию из данных с помощью вкладки «Анализ сводной таблицы».
Поскольку сводные таблицы являются назначенным инструментом в Excel, они имеют множество легкодоступных опций, которые позволяют вам манипулировать данными и представлять их удобным для вас способом. Более того, если вы планируете поделиться рабочей книгой с другими, простой пользовательский интерфейс означает, что они, скорее всего, смогут понять и использовать данные, даже если они не являются опытными пользователями Excel.
Обновление вручную
Ранее я подчеркивал, что использование PIVOTBY вместо сводных таблиц может быть предпочтительнее, поскольку эта функция мгновенно реагирует на изменения в исходных данных, тогда как сводные таблицы этого не делают.
Однако если ваша электронная таблица содержит много формул PIVOTBY, поскольку они постоянно пересчитываются, они могут занять большой объем памяти вашего компьютера, что приведет к мучительно медленной работе рабочей книги.
С другой стороны, поскольку сводные таблицы обновляются только тогда, когда вы нажимаете кнопку «Обновить» на вкладке «Анализ сводной таблицы», они не потребляют ресурсы вашего устройства постоянно.
Эффективное форматирование
Поскольку сводные таблицы являются специализированным инструментом Excel, они оснащены простыми в использовании параметрами форматирования, которые позволяют легко различать основную строку заголовка, строку данных, строки промежуточных итогов и строку общего итога.
Более того, с помощью вкладки «Дизайн» на ленте вы можете быстро изменить стиль сводной таблицы, решить, следует ли включать полосы и заголовки, а также настроить другие аспекты представления.
С другой стороны, чтобы добиться того же эффекта с выводом формулы PIVOTBY, вам нужно применить форматирование вручную. Чтобы еще больше усложнить ситуацию, если вы хотите выделить определенные строки в результате PIVOTBY, вам нужно использовать условное форматирование, чтобы гарантировать, что форматирование подстроится под изменения в источнике данных.
Добавлена визуализация
После того, как вы создали сводную таблицу в Excel, веселье на этом не заканчивается. Действительно, одним нажатием кнопки вы можете добавить еще более впечатляющую графику, чтобы визуализировать ваши цифры таким образом, чтобы их было легче читать и организовывать.
Есть два инструмента, которые мне особенно нравится использовать сразу после создания сводной таблицы:
- Сроки: Если данные в сводной таблице отсортированы по разным периодам времени, вы можете добавить динамический фильтр временной шкалы, который позволит быстро отобразить данные за отдельные дни, месяцы, кварталы или годы.
- Сводная диаграмма: Хотя PivotTables позволяют вам легко группировать и анализировать ваши цифры, вы можете пойти на шаг дальше и отобразить ваши данные в PivotChart, которая автоматически адаптируется к изменениям в PivotTable. В результате PivotCharts более гибки, чем обычные диаграммы для обобщения данных.
Оба эти инструмента доступны на вкладке «Анализ сводной таблицы» на ленте.
Разнообразные источники данных
Хотя функция PIVOTBY может ссылаться только на исходные данные в Excel, при создании сводной таблицы в Excel 2016 или более поздней версии либо Excel для Microsoft 365 вы можете извлекать данные из внешнего источника, включая реляционные базы данных, такие как SQL Server или Oracle, другие типы файлов, такие как CSV, XLM или ODC, базы данных PowerBI или веб-страницы.
Эта возможность обеспечивает большую гибкость при работе в разных сетях, что особенно полезно, если вы генерируете и анализируете данные на рабочем месте.
Стоит ли использовать функцию PIVOTBY или сводные таблицы в Excel?
Позвольте мне подчеркнуть в этом месте, что функция PIVOTBY и PivotTables имеют уникальные применения и не обязательно являются заменой друг друга. Однако, если вы рассматриваете лучший способ группировки и агрегации набора данных в таблице Excel, вот основные вещи, которые следует иметь в виду:
Используйте функцию PIVOTBY, если:
- Вы хотите, чтобы результат обновлялся автоматически и, таким образом, отображал самую актуальную информацию,
- Вам нравится работать с формулами, и вы предпочитаете использовать их аргументы, а не вкладки и меню на ленте, для управления выводом данных,
- Вы хотите гибко настраивать способ представления ваших данных и
- Вы хотите включить различные функции, включая настраиваемые формулы LAMBDA, чтобы определить, как будут агрегироваться ваши данные.
С другой стороны, используйте сводные таблицы, если:
- Вы хотите реорганизовать свои данные в форматированные таблицы всего за несколько щелчков мыши,
- Вы предпочитаете работать с вкладками и панелями, а не с подробными формулами для настройки данных,
- Вы хотите добавить дополнительную графику для дальнейшей визуализации ваших фигур,
- Вы, скорее всего, поделитесь рабочей тетрадью с другими, и
- Исходные данные представлены в различных форматах.
Другой способ организовать данные в группы в Excel — использовать функцию GROUPBY. Однако, в то время как функция PIVOTBY позволяет суммировать данные в строках и столбцах, GROUPBY работает только со строками, что означает, что при создании формулы требуется меньше аргументов.
