Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Предположим, что из вот такой базы данных по продажам:

getpivotdata1.png

...вы создали небольшую, но симпатичную сводную таблицу:

getpivotdata2.png

Но ваш руководитель хочет не совсем ее, а что-то похожее на:

getpivotdata3.png

То есть имеем несколько ощутимых трудностей:

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

Сделаем на отдельном от сводной листе заготовку отчета:

getpivotdata4.png

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

getpivotdata5.png

Давайте разберем ее подробно:

  • Первый ее аргумент ("Выручка") - это имя извлекаемого поля.
  • Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
  • Все остальные аргументы начиная с третьего - это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".

А теперь самое интересное.

Аккуратно замените в формуле "Fiesta" на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.

getpivotdata6.png

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

Если исходные данные поменяются, то нужно будет лишь обновить сводную (правой кнопкой мыши - Обновить). Даже если завтра изменится количество моделей или, скажем, Fiesta будет уже не третьей по счету машиной в сводной, наша функция все равно ее найдет и правильно извлечет соответствующий ей результат для нашего отчета.

Ссылки по теме



25.08.2015 09:12:53
прикольно
25.08.2015 15:53:32
Дай Бог вам здоровья и благополучия!
26.08.2015 08:32:20
как всегда отличное  решение
я чаще всего делаю копию сводной и вставляю как значения и дальше уже с ней работаю
но в тех случаях когда точно знаешь что все поля остануться такими как есть, крайне удобное и нужное решение

будем использовать !
31.08.2015 10:09:56
Копирование сводной на новый лист с отвязкой от исходных данных добавил, кстати, в следующую версию PLEX :)
27.08.2015 16:24:12
Добрый день!
Подскажите, пожалуйста, не лучше ли настроить отчет непосредственно на источник данных? Зачем привязываться к сводной?
31.08.2015 05:08:42
Учитывая, что данные берутся из сводной таблицы (сумма множества значений по условию), то как Вы себе представляете ссылку на источник данных? Формулы? В таком случае это более затратно по времени, чем прямая ссылка на сводную (на мой взгляд).
31.08.2015 10:09:01
Можно, конечно, вытаскивать и суммировать по условиям данные напрямую из источника с помощью функций СУММЕСЛИМН и т.д., но это будет, во-первых, сложно и, во-вторых, медленно. Сводная таблица делает за нас всю работу по суммированию, а нам остается только извлечь из нее готовые результаты - так, по-моему, проще :)
Обращение к первоисточнику через СУММЕСЛИМН оправдано, т.к. исключает возможные ошибки:
1. Если в сводной таблице можно поменять фильтр города, то в заготовку для Питера уже попадут данные других городов.
А если выбрать фильтром другого производителя(Mazda вместо Ford), то в форме отчета будет ошибка. Получается, для каждой заготовки (например, по фирме-изготовителю с фирменным корпоративным стилем) нужна своя сводная таблица. На мой взгляд, не очень удобно.
2. В процессе работы с первоисточником можно забыть обновить сводную(ые) таблицу(Ы).
31.08.2015 18:38:12
ок. я так понимаю, целесообразность зависит от объемов источника данных и объемов отчета, куда подтягиваются данные. Если, например, в источнике более 1048 тыс записей, то сводную строить можно, вроде бы через доп.надстройки, а обратиться через формулы к источнику - нет. Тогда это единственный способ.
24.09.2015 10:12:37
В 2013-2016 эту задачу также можно решить при помощи PowerPivot и преобразования сводной таблицы в формулы OLAP. Аккуратнее формулы получаются на мой взгляд

http://bit.ly/1PvLSqy
15.10.2015 11:58:22
Николай,
Возможно такую хитрость перенести и на сводные, постороенные на основе OLAP?

Функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ пользуюсь постоянно, но в случае с OLAP строю сводную в табличном виде, потом на основе этой таблицы строю новую сводную и её уже использую для извлечения данных. Уже привычка сформировалась обновлять файлы дважды.
15.10.2015 12:24:57
Пока писала вопрос, как водится, всё поняла :)
В случае OLAP надо брать переменную ячейку в кавычки и амперсанд
...Номер].&["&B20&"]";"[Дата....

Николай, спасибо большое! :D
13.02.2016 13:17:15
Почему у меня, когда я выбираю ячейку, ввожу знак "равно" и щелкаю по ячейке в сводной таблицы, которая содержит нужные данные Excel 2016 не вставляет автоматически функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ?
16.02.2016 07:43:49
А на вкладке Анализ в выпадающем списке Параметры галочка GetPivotData стоит?
16.02.2016 08:18:28
На вкладке Анализ в выпадающем списке Параметры галочка GetPivotData стоит. Причем, в файле с Вашим примером функция вставляется автоматически, а в моих файлах этого не происходит. В отличие от Вашего примера на вкладке Анализ в выпадающем списке Параметры не активна только команда Отобразить страницы фильтра отчета... (хотя поля в разделе фильтры сводной таблицы присутствуют).
11.04.2016 10:19:26
А у вас сводная не по OLAP-кубу, часом?
23.01.2021 15:51:38
Присоединяюсь к вопросу, сверил настройки сводной, не выходит функция автоматически при ссылке на сводную, галка  GetPivotData стоит.
В чем может быть хитрость?
11.04.2016 08:58:21
Николай, а как сделать так, чтобы и название Ford выносилось на новый лист?
05.05.2016 14:45:08
Пыталась сделать ссылки на сводную таблицу PowerPivot - не работало, пока не изменила ссылки на вот такой вариант, вроде все работает: "["&B20&"]"
03.06.2016 20:43:03
Николай, добрый день!

Почему у меня в колонках сводной таблицы, при попытке сгруппировать даты по месяцам, программа не дает никаких вариантов группировки, а выкидывает сообщение "Выделенные объекты нельзя объединить в группу"? В исходных данных формат столбца стоит правильно, дата.

Спасибо  
05.09.2016 10:55:47
Такое бывает, если в столбце не во всех ячейках даты (например, есть пустые ячейки или дата-как-текст).
19.12.2016 17:56:37
Спасибо. Действительно, если приходится скопировать строчку с одной датой и перебить ее вручную на другую, то это другая как-то сдвигается, относительно всех остальных, видимо сохраняется в другом формате.
23.06.2016 11:35:18
А как сделаны пунктирные значения у графика?
19.12.2016 17:38:50
Спасибо Вам, Николай, огромное.
Никак не получалось нати ответа на вопрос, как добыть данные из сводной, чтобы их можно было отправить кому-нибудь без связей и исходников. И вот, наконец, научился.

Трофим
12.04.2017 13:21:06
Идея прекрасна, спасибо вам, за статью и в целом за сайт

Возник вопрос:
Правильно ли я  понимаю, что связь идет именно с текущей сводной таблицей и, если я в сводной таблице исключу какой либо элемент, например "Focus", то в на листе 2 я получу ошибку "Ссылка".

Существует ли способ избежать этого?
14.04.2017 17:06:35
На практике эту функцию никогда не использовал.
Но помню, что очень много было вопросов от пользователей – что это «..вылезает» при ссылке на ячейку сводной таблицы.

Это происходило из-за того, что параметр GetPivotData был включен по умолчанию в каких-то версиях Excel.

Что-бы его отключить приходилось вручную на вкладке Параметры (Работа со сводными таблицами) в выпадающем списке пункта Параметры снимать галочку GetPivotData
03.09.2017 09:22:33
Добрый день. Спасибо за очень понятную статью, но все меняется когда сводня таблица строится на основе нескольких сводных таблиц. Подскажите, как тогда в этом случае поступать? Что и как нужно заменять?
03.09.2017 09:25:05
Спасибо за комментарий
Sonya Suerte
15.10.2015 12:24:57
Очень помогло
03.11.2017 11:33:17
а как сделать так, чтобы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) работала при закрытой книге. когда я ссылаюсь на эту формулу в другой книге она не работает, если главная книга закрыта. как решить проблему?

">
ОТВЕТИТЬ


07.09.2018 07:26:49
Николай, спасибо за ваши техники, в том числе и за эту и за суперпонятное изложение материала.
08.05.2019 10:29:14
Добрый день!
Николай, большое спасибо за объяснения )) очень помогло в работе))
Подскажите пжт, наименования всех полей и элементов в сводной или в сводной OLAP уникальные?
Например, у меня такая формула
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("[Measures].[Actual]";$A$6;"[IncomeStatementSimplified].[Income Statement]";"[IncomeStatementSimplified].[Income Statement].&[E14000000]";"[Time].[Month]";"[Time].[Month].&[201903]"
E14000000 - уникальный элемент поля таблицы?
И подскажите пжт , есть ли способ отклчить промежуточные итоги для строк. и оставить только для столбцов?
Спасибо!
13.06.2020 19:59:41
Показалось очень полезным применение такого способа. до этого делала Суммеслимн.
но! при изменении полей исходной сводной таблицы, либо вставке среза в сводную таблицу, изменяются (пропадают) результаты, полученные с помощью ПОЛУЧИТЬ ДАННЫЕ СВОДНОЙ ТАБЛИЦЫ!
то есть сводная таблица должна быть недвижимой? или можно где-то отключить это? ну, что-то типа как при работе с диаграммами есть же галочка на использование в графике скрытых ячеек, может и тут возможно, чтобы результат не менялся при "шевелении" сводной?
21.08.2020 10:35:43
!!!!Николай Здравствуйте , отличный способ спасибо очен вам. Но в красивую отчет хотел добавит Дату с помешу выподаяюший список выбират нужный дату Как эту дату связит на Двту сводный таблицу
28.02.2021 14:38:02
а как вытаскивать данные не из тела сводного отчета, а из итогов по строкам и/или столбцам?
24.08.2021 03:48:34
Николай, доброго дня. Очень хороший способ описан в Вашем приеме. Только вот он работает с обычной сводной таблицей, которая построена на  диапазоне данных. А если сводная строится на основе модели данных Power Pivot, то там синтаксис формулы GetPivotData отличается от приведенного в примере и сослаться на ячейки в качестве аргумента функции не получается.
Подскажите, как можно сделать то же самое, только на сводной из Power Pivot?
Наверх