Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Предположим, что из вот такой базы данных по продажам:
...вы создали небольшую, но симпатичную сводную таблицу:
Но ваш руководитель хочет не совсем ее, а что-то похожее на:
То есть имеем несколько ощутимых трудностей:
- Исходный внешний вид сводной таблицы не подходит - дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). "Дорабатывать напильником" дизайн сводной - долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
- Из всей сводной для отчета вам нужны не все данные, а только конкретные модели 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?
При использовании ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ данные не отображаются.
Сводная таблица находится в отдельной книге в другой папке.
При закрытии книги со сводной таблицей - в рабочей ячейке в формуле с использованием ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ появляется #ССЫЛКА!
Заранее благодарен!
Для этого нужны другие инструменты - например, Power Query.
Я правильно понимаю, что необходимо/возможно сводную при помощи PQ делать именно в том файле, в котором я собираюсь делать таблицу при помощи ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ?
Просто в этом файле уже много вкладок (по два сменных задания каждые сутки), с общим доступом и множеством мелких формул (ВПР и прочее) в сменном задании...
Пришлось даже условное форматирование убрать с листов, чтобы файл полегче был...
Вообще я пользуюсь данной функцией, но приходится файл через три-четыре месяца новый делать, а начальство хочет все в одном файле(...
Ну ничего - будем дальше мучиться))
В любом случае - большое спасибо!