Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

GETPIVOTDATA в Excel: как использовать функцию для создания динамических отчетов

forest_paw 05.11.2025 498

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA в Excel: как использовать функцию для создания динамических отчетов

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA в Excel часто воспринимается как неудобная, но при правильном использовании она становится мощным инструментом для создания динамических отчетов на основе сводных таблиц. В этом руководстве мы разберем, как превратить ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA из проблемы в решение.

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

Многие пользователи впервые сталкиваются с ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA, когда пытаются создать формулу вне сводной таблицы, ссылающуюся на её данные. Excel автоматически вставляет эту функцию при использовании мыши или клавиш со стрелками для указания на ячейку внутри сводной таблицы.

Сводная таблица показывает месяц в столбце B, прошлый год в C и текущий год в D. Формула вне таблицы в столбце E с заголовком % Изменение не копируется в другие строки из-за жестко заданных значений месяца и года.
Формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA с жестко заданными значениями не копируется корректно.

Если вы не хотите, чтобы функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA появлялась, просто введите формулу вручную, например Русская версия: =D5/C5-1English version: =D5/C5-1, без использования мыши для указания на ячейки. При копировании относительные ссылки изменятся. Проверьте, что C5 не равна нулю, а строки и столбцы сводной не сместились после обновления. Автосоздание GETPIVOTDATA также можно отключить в меню анализа сводной таблицы; название переключателя зависит от версии.

Изменение формулы в E5 на =D5/C5-1 позволяет скопировать её в другие ячейки.
Ручной ввод формулы решает проблему копирования.

Практический пример: отчет по плану и факту

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

Набор данных с названием магазина в столбце A. У каждого магазина 12 строк плановых чисел (с января по декабрь) и 12 строк фактических чисел.
Исходные данные для отчета.

При создании сводной таблицы с магазинами в строках и Месяцем/Типом в столбцах вы получите неидеальный отчет с лишними столбцами, например, «Январь Факт+План».

Первая версия сводной таблицы: магазины в столбце A, вверху — Янв Факт, Янв План, затем бессмысленный Янв Итого.
Сводная таблица с лишними итогами.

Вы можете убрать ненужные итоги через «Параметры поля», но проблема со столбцами плана за уже завершенные месяцы (например, Январь-Апрель) останется. Для собственного макета удобнее получать нужные показатели в отдельный диапазон, не удаляя ячейки самой сводной таблицы.

Но проблема остается: нужно удалить столбцы Янв План и Фев План, так как фактические данные уже есть.
Необходимость ручного удаления лишних столбцов плана.

Решение с ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA: лучший рабочий процесс

Вместо ежемесячного перестроения отчета с нуля используйте ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA для создания динамической связи.

  1. Создайте обычную сводную таблицу из ваших данных (её никто не увидит).
  2. На отдельном листе постройте красивый отчетный шаблон.
  3. В первой ячейке отчета (например, «Январь Факт» для магазина «Baybrook») начните формулу со знака Русская версия: =English version: =.
  4. С помощью мыши кликните на соответствующую ячейку в сводной таблице. Excel вставит ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA.
Начните с первой числовой ячейки в отчете. В данном случае это ячейка для Января Факт Baybrook. Введите знак равенства в этой ячейке.
Начало создания формулы.

Изучите синтаксис автоматически созданной формулы. Первый аргумент — имя поля значений (например, «Sales»), второй — ссылка на ячейку внутри сводной таблицы, остальные — пары «имя поля/элемент». Функция возвращает видимые данные; отсутствующий или скрытый фильтром элемент может дать #ССЫЛКА! (#REF!). Проблема в том, что значения (например, «Baybrook», «Jan») жестко заданы.

Русская версия:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales";Sheet3!$A$3;"Store";"Baybrook";"Month";"Jan";"Type";"Actual")
English version:
=GETPIVOTDATA("Sales",Sheet3!$A$3,"Store","Baybrook","Month","Jan","Type","Actual")

Ключевой шаг — отредактируйте формулу, заменив жестко заданные значения на ссылки на ячейки вашего отчета.

Русская версия:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales";Sheet3!$A$3;"Store";$D6;"Month";E$3;"Type";E$4)
English version:
=GETPIVOTDATA("Sales",Sheet3!$A$3,"Store",$D6,"Month",E$3,"Type",E$4)
GETPIVOTDATA со ссылками на магазин в D6, месяц в E3 и тип показателя в E4.
Формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA с динамическими ссылками.

Скопируйте эту исправленную формулу и используйте «Специальную вставку» → «Формулы» во всех остальных числовых ячейках отчета.

Ежемесячное обновление отчета

  1. Добавьте новые фактические данные в исходную таблицу.
  2. Обновите (Refresh) исходную сводную таблицу.
  3. Задайте в P1 настоящую дату первого незавершённого месяца, например 01.05.2026. В E2 и далее разместите даты первого числа каждого месяца. В E3 и далее должны быть соответствующие подписи Month из источника, например «Jan». В E4 введите =ЕСЛИ(E$2<$P$1;"Actual";"Budget") (английская: =IF(E$2<$P$1,"Actual","Budget")) и скопируйте вправо. Здесь Type содержит именно «Actual» и «Budget». Тогда приведённая выше GETPIVOTDATA использует E4 для выбора факта или плана. Расчёт обновится при включённом автоматическом пересчёте; изменение P1 само по себе не обновляет источник сводной.
На иллюстрации контрольная дата находится в R1; в формулах статьи используется P1.
Динамическое переключение между «Факт» и «План».

Преимущества подхода

  • Гибкое форматирование: Вы можете форматировать отчет так, как невозможно в стандартной сводной таблице (пустые строки, валюта только в первой/последней строке, двойное подчеркивание итогов).
  • Динамичность: Отчет автоматически обновляется при изменении данных в сводной таблице.
  • Простота поддержки: Минимальные действия для ежемесячного обновления.

Использование ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA для связи отчета со скрытой сводной таблицей дает лучшее из двух миров: мощь анализа сводных таблиц и полную свободу в дизайне и представлении итогового отчета.

Поделиться:

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