Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Предположим, что из вот такой базы данных по продажам:
...вы создали небольшую, но симпатичную сводную таблицу:
Но ваш руководитель хочет не совсем ее, а что-то похожее на:
То есть имеем несколько ощутимых трудностей:
- Исходный внешний вид сводной таблицы не подходит - дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). "Дорабатывать напильником" дизайн сводной - долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
- Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру - придется руками фильтровать.
- Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах - сводная так не умеет.
- Полученные в сводной результаты - еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но "через одно место" с помощью вычисляемых полей и объектов.
- Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).
Сделаем на отдельном от сводной листе заготовку отчета:
Выделите первую ячейку зеленого диапазона, введите знак "равно" и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля "морской бой" Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:
Давайте разберем ее подробно:
- Первый ее аргумент ("Выручка") - это имя извлекаемого поля.
- Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
- Все остальные аргументы начиная с третьего - это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".
А теперь самое интересное.
Аккуратно замените в формуле "Fiesta" на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.
Теперь с данными в зеленых ячейках можно работать как с обычными формулами, а не как со сводной таблицей с ее жесткими ограничениями, т.е. дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д.
Если исходные данные поменяются, то нужно будет лишь обновить сводную (правой кнопкой мыши - Обновить). Даже если завтра изменится количество моделей или, скажем, Fiesta будет уже не третьей по счету машиной в сводной, наша функция все равно ее найдет и правильно извлечет соответствующий ей результат для нашего отчета.
Ссылки по теме
- Что такое сводные таблицы и как их строить
- Настройка вычислений в сводных таблицах
- Группировка дат, чисел и текста в сводных таблицах
я чаще всего делаю копию сводной и вставляю как значения и дальше уже с ней работаю
но в тех случаях когда точно знаешь что все поля остануться такими как есть, крайне удобное и нужное решение
будем использовать !
Подскажите, пожалуйста, не лучше ли настроить отчет непосредственно на источник данных? Зачем привязываться к сводной?
1. Если в сводной таблице можно поменять фильтр города, то в заготовку для Питера уже попадут данные других городов.
А если выбрать фильтром другого производителя(Mazda вместо Ford), то в форме отчета будет ошибка. Получается, для каждой заготовки (например, по фирме-изготовителю с фирменным корпоративным стилем) нужна своя сводная таблица. На мой взгляд, не очень удобно.
2. В процессе работы с первоисточником можно забыть обновить сводную(ые) таблицу(Ы).
Возможно такую хитрость перенести и на сводные, постороенные на основе OLAP?
Функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ пользуюсь постоянно, но в случае с OLAP строю сводную в табличном виде, потом на основе этой таблицы строю новую сводную и её уже использую для извлечения данных. Уже привычка сформировалась обновлять файлы дважды.
В случае OLAP надо брать переменную ячейку в кавычки и амперсанд
...Номер].&["&B20&"]";"[Дата....
Николай, спасибо большое!
В чем может быть хитрость?
Почему у меня в колонках сводной таблицы, при попытке сгруппировать даты по месяцам, программа не дает никаких вариантов группировки, а выкидывает сообщение "Выделенные объекты нельзя объединить в группу"? В исходных данных формат столбца стоит правильно, дата.
Спасибо
Никак не получалось нати ответа на вопрос, как добыть данные из сводной, чтобы их можно было отправить кому-нибудь без связей и исходников. И вот, наконец, научился.
Трофим
Возник вопрос:
Правильно ли я понимаю, что связь идет именно с текущей сводной таблицей и, если я в сводной таблице исключу какой либо элемент, например "Focus", то в на листе 2 я получу ошибку "Ссылка".
Существует ли способ избежать этого?
Но помню, что очень много было вопросов от пользователей – что это «..вылезает» при ссылке на ячейку сводной таблицы.
Это происходило из-за того, что параметр GetPivotData был включен по умолчанию в каких-то версиях Excel.
Что-бы его отключить приходилось вручную на вкладке Параметры (Работа со сводными таблицами) в выпадающем списке пункта Параметры снимать галочку GetPivotData
15.10.2015 12:24:57
Очень помогло
Николай, большое спасибо за объяснения )) очень помогло в работе))
Подскажите пжт, наименования всех полей и элементов в сводной или в сводной OLAP уникальные?
Например, у меня такая формула
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("[Measures].[Actual]";$A$6;"[IncomeStatementSimplified].[Income Statement]";"[IncomeStatementSimplified].[Income Statement].&[E14000000]";"[Time].[Month]";"[Time].[Month].&[201903]"
E14000000 - уникальный элемент поля таблицы?
И подскажите пжт , есть ли способ отклчить промежуточные итоги для строк. и оставить только для столбцов?
Спасибо!
но! при изменении полей исходной сводной таблицы, либо вставке среза в сводную таблицу, изменяются (пропадают) результаты, полученные с помощью ПОЛУЧИТЬ ДАННЫЕ СВОДНОЙ ТАБЛИЦЫ!
то есть сводная таблица должна быть недвижимой? или можно где-то отключить это? ну, что-то типа как при работе с диаграммами есть же галочка на использование в графике скрытых ячеек, может и тут возможно, чтобы результат не менялся при "шевелении" сводной?
Подскажите, как можно сделать то же самое, только на сводной из Power Pivot?