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

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

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

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

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

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

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

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

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

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

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

Изучите синтаксис автоматически созданной формулы. Первый аргумент — поле данных (например, «Продажи»), второй — ячейка сводной таблицы, остальные — пары «имя поля/значение». Проблема в том, что значения (например, «Baybrook», «Jan») жестко заданы.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales",СЖДДТ3!$А$3,"Store","Baybrook","МЕСЯЦ","Jan","ТИП","Actual") (=GETPIVOTDATA("Sales",Sheet3!$A$3,"Store","Baybrook","Month","Jan","Type","Actual"))

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales",СЖДДТ3!$А$3,"Store",$Г6,"МЕСЯЦ",Д$3,"ТИП",Д$4) (=GETPIVOTDATA("Sales",Sheet3!$A$3,"Store",$D6,"Month",E$3,"Type",E$4))

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

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

1. Добавьте новые фактические данные в исходную таблицу.
2. Обновите (Refresh) исходную сводную таблицу.
3. Измените контрольную дату в ячейке отчета (например, P1). Все числа в отчете обновятся автоматически благодаря ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).

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

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

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