GETPIVOTDATA для работы со сводными таблицами в Excel

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



Подготовка исходных данных и сводной таблицы для примера

Для примера составить таблицу с исходными значениями о ежемесячных финансовых показателях за учетный период 2 года:

На основе этих исходных данных построим сводную таблицу. Для этого выделите диапазон ячеек A1:G25 или просто поместите курсор Excel в этот диапазон, а исходная таблица будет распознана автоматически. Затем выберите инструмент: Insert – Tables – PivotTable.

В мастере создания и настройки сводной таблицы заполните два поля, как показано выше на рисунке:

1. «Table/Range:» – здесь укажите ссылку на источник данных Sheet1!$A$1:$G$25.
2. В разделе опций «Choose where you want the PivotTable to be placed» активируйте пункт «Existing Worksheet» и в поле ввода «Location:» - укажите ссылку на ячейку A27 для размещения на текущем листе.

Настройте поля в конструкторе сводной таблицы так чтобы в строках были месяцы, а в их значениях показатель валовой прибыли, как показано ниже на рисунке:

Пример использования функции GETPIVOTDATA в Excel

Возле исходной таблицы с данными в диапазоне ячеек I1:J13 создадим таблицу для построения столбчатой вертикальной гистограммы. В заголовках строк сокращенные название месяцев такие же как в исходной таблицы, а также заголовки соответственных столбцов должны совпадать с исходными данными.

После чего заполняем таблицу чарта значениями из сводной таблицы. Для этого в ячейке J2 введите знак равно и укажите на первое значение во втором столбце сводной таблицы (на против месяца JAN). В результате будет автоматически сгенерирована формула уже с заполненными аргументами GETPIVOTDATA. Функция автоматической генерации включена по умолчанию. Можно проверить, выбрав опцию: PivotTable Analyze – PivotTable – Options - Generate GetPivotData. Если опция отмечена флажком значит автоматическая генерация функции включена.

Нам остается лишь только немного изменить аргументы, чтобы автоматически заполнить весь столбец таблицы чарта. В первом аргументе указывается название выбранного столбца в сводной таблице, вместо текстовой строки «Gross Profit» мы указываем функцию CONCATENATE, а в ее аргументах абсолютную ссылку на ячейку с заголовком столбца $J$1 и пустую строку “”. Таким образом с помощью функции CONCATENATE мы преобразовываем любое значение в ячейке $J$1 в текстовый тип данных. Это требования функции GETPIVOTDATA иначе не прочитает значение и будет возвращать ошибку. В последнем аргументе функции GETPIVOTDATA указывается название строк и нам не нужно преобразовывать данные в текстовый строчный тип, достаточно лишь указать относительную ссылку на ячейку с названием месяца (для JAN – это I2). Заполняем весь столбец формулой, в результате таблица чарта автоматически заполняется значениями из сводной таблицы.

Таблица чарта готова, на ее основе строим горизонтальную столбчатую гистограмму.

GETPIVOTDATA для динамических чартов в Excel

Выделите диапазон ячеек таблицы чарта I1:J13 выберите инструмент: Insert – Charts – 2-D Column – Clustered Column.

На данном этапе возникает закономерный вопрос почему нельзя использовать обычные ссылки на ячейки вместо функции GETPIVOTDATA? Чтобы дать полный ответ с наглядным примером нам понадобиться добавить элемент управления сводной таблицей.

Добавляем кнопки управления визуализацией данных

Добавим главное преимущество сводных таблиц для разработки визуализации данных и дашбордов – это срезы данных. Перейдите курсором Excel в область сводной таблицы A27:B40 и выберите инструмент: Insert – Filters – Slicer.

Теперь мы имеем возможность фильтровать данные сводной таблицы и управлять визуализацией данных на чарте.

Преимущества в использовании функции GETPIVOTDATA

Обратите внимание! Если бы мы использовали обычные ссылки вместо функции GETPIVOTDATA нам не удалось отобразить правильную структуру визуализации на чарте ведь структура сводной таблицы существенно изменилась. Теперь у нее только 3 строки, а месяцы выбраны только из третьего квартала.

Структура сводной таблицы может быть изменена не только по вертикали по строкам, но и по горизонтали по столбцам. В конструкторе сводной таблицы отредактируйте и настройте поля так как показано ниже на рисунке:

В итоговом значении для нового столбца «Debt-to-Equity Ratio» измените операцию вычисления заменив Суммирование на Среднее – так как в данном столбце выводятся процентные значения.

Извлечение итоговых значений из сводной таблицы

Теперь создадим новою мини-таблицу для диаграммы из двух строк и двух столбцов. На этот раз будем ссылаться на итоговое значение нового столбца сводной таблицы в строке с заголовком Grand Total.

Теперь выбрано только 2 месяца соответственно структура сводной таблицы также изменилась по горизонтали и по вертикали, так как мы добавили еще один столбец. Также изменилось место положение итоговых значений на листе. Если бы мы использовали ссылки в таблицах для чартов вместо функции GETPIVOTDATA нам бы пришлось изменять адреса ссылок после редактирования сводной таблицы в конструкторе. Также нам бы пришлось составлять умные формулы чтобы ловить положение ячейки с итоговым значением при изменении количества выбранных месяцев. Намного эффективнее использовать функцию GETPIVOTDATA, но при желании для особенных случаев можно использовать обычные ссылки как вам угодно. Excel нас не ограничивает. В большинстве случаев рекомендуется извлекать данные из сводной таблицы с использованием стандартной специально предназначенной функции GETPIVOTDATA.

Презентация работы функции на готовом примере

Схема работы дашбордов с использованием функции GETPIVOTDATA:

На схеме упрощенно показаны этапы преобразования исходной информации в интерактивную визуализацию данных Excel.

Для примера и полного усвоения материала рекомендуем посмотреть на практике, как используется функция GETPIVOTDATA в шаблоне дашборда с интерактивной визуализацией данных о ключевых финансовых показателях:

Скачать пример использования функции GETPIVOTDATA в Excel

Теперь вы можете изучить на готовом примере по шаблону дашборда Excel. Так вам будет легче научиться самостоятельно создавать интерактивные дашборды с элементами управления срезами данных сводных таблиц.