ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA в Excel: как использовать функцию для создания динамических отчетов
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA в Excel часто воспринимается как неудобная, но при правильном использовании она становится мощным инструментом для создания динамических отчетов на основе сводных таблиц. В этом руководстве мы разберем, как превратить ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA из проблемы в решение.
Пример рассчитан на обычную сводную таблицу в настольном Excel. Имена полей и элементов в формулах должны точно соответствовать вашей таблице. Английские имена в примере — подписи данных, их не переводят вместе с именем функции.
Многие пользователи впервые сталкиваются с ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA, когда пытаются создать формулу вне сводной таблицы, ссылающуюся на её данные. Excel автоматически вставляет эту функцию при использовании мыши или клавиш со стрелками для указания на ячейку внутри сводной таблицы.
Если вы не хотите, чтобы функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA появлялась, просто введите формулу вручную, например Русская версия: =D5/C5-1English version: =D5/C5-1, без использования мыши для указания на ячейки. При копировании относительные ссылки изменятся. Проверьте, что C5 не равна нулю, а строки и столбцы сводной не сместились после обновления. Автосоздание GETPIVOTDATA также можно отключить в меню анализа сводной таблицы; название переключателя зависит от версии.
Практический пример: отчет по плану и факту
Представьте набор данных с плановыми показателями и фактическими продажами по магазинам и месяцам. Задача — построить отчет, который показывает факт за завершенные месяцы и план на будущие.
При создании сводной таблицы с магазинами в строках и Месяцем/Типом в столбцах вы получите неидеальный отчет с лишними столбцами, например, «Январь Факт+План».
Вы можете убрать ненужные итоги через «Параметры поля», но проблема со столбцами плана за уже завершенные месяцы (например, Январь-Апрель) останется. Для собственного макета удобнее получать нужные показатели в отдельный диапазон, не удаляя ячейки самой сводной таблицы.
Решение с ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA: лучший рабочий процесс
Вместо ежемесячного перестроения отчета с нуля используйте ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA для создания динамической связи.
- Создайте обычную сводную таблицу из ваших данных (её никто не увидит).
- На отдельном листе постройте красивый отчетный шаблон.
- В первой ячейке отчета (например, «Январь Факт» для магазина «Baybrook») начните формулу со знака Русская версия:
=English version:=. - С помощью мыши кликните на соответствующую ячейку в сводной таблице. Excel вставит ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA.
Изучите синтаксис автоматически созданной формулы. Первый аргумент — имя поля значений (например, «Sales»), второй — ссылка на ячейку внутри сводной таблицы, остальные — пары «имя поля/элемент». Функция возвращает видимые данные; отсутствующий или скрытый фильтром элемент может дать #ССЫЛКА! (#REF!). Проблема в том, что значения (например, «Baybrook», «Jan») жестко заданы.
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales";Sheet3!$A$3;"Store";"Baybrook";"Month";"Jan";"Type";"Actual")=GETPIVOTDATA("Sales",Sheet3!$A$3,"Store","Baybrook","Month","Jan","Type","Actual")Ключевой шаг — отредактируйте формулу, заменив жестко заданные значения на ссылки на ячейки вашего отчета.
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales";Sheet3!$A$3;"Store";$D6;"Month";E$3;"Type";E$4)=GETPIVOTDATA("Sales",Sheet3!$A$3,"Store",$D6,"Month",E$3,"Type",E$4)
Скопируйте эту исправленную формулу и используйте «Специальную вставку» → «Формулы» во всех остальных числовых ячейках отчета.
Ежемесячное обновление отчета
- Добавьте новые фактические данные в исходную таблицу.
- Обновите (Refresh) исходную сводную таблицу.
- Задайте в 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 само по себе не обновляет источник сводной.
Преимущества подхода
- Гибкое форматирование: Вы можете форматировать отчет так, как невозможно в стандартной сводной таблице (пустые строки, валюта только в первой/последней строке, двойное подчеркивание итогов).
- Динамичность: Отчет автоматически обновляется при изменении данных в сводной таблице.
- Простота поддержки: Минимальные действия для ежемесячного обновления.
Использование ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA для связи отчета со скрытой сводной таблицей дает лучшее из двух миров: мощь анализа сводных таблиц и полную свободу в дизайне и представлении итогового отчета.