Перейти к содержимому

Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ

Ранее я уже рассказывал о том, что при ссылке на ячейку сводной таблицы вместо обычной ссылки возвращается функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (см. Как научить Excel ссылаться на ячейку в сводной таблице, как на обычную). Если вас интересует как преодолеть это неудобство, рекомендую обратиться к упомянутой заметке. Если же вам интересно, почему так происходит, а также, какие положительные аспекты есть у функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, то предлагаю фрагмент книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013 (глава 15). Рассматриваемая методика позволит справиться со многими проблемами, вызывающими головную боль у пользователей сводных таблиц, в частности:

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

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

Скачать заметку в формате Word или pdf, примеры в формате Excel

Что ж, начнем по порядку.

Как отказаться от проблемной функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

Предположим, в сводной таблице, показанной на рис. 1, нужно сравнить данные за 2015 и 2014 гг.

Рис. 1. Сводная таблица

Рис. 1. Исходная сводная таблица

  1. Добавьте в ячейку D3 заголовок «% роста».
  2. Скопируйте формат из ячейки С3 в ячейку D3.
  3. В ячейку D4 введите знак равенства.
  4. Щелкните на ячейке С4.
  5. Введите знак / (косая черта), обозначающий операцию деления.
  6. Щелкните на ячейке В4.
  7. Введите –1 и нажмите комбинацию клавиш <Ctrl+Enter>, чтобы остаться в той же ячейке. Отформатируйте результат в виде процентного соотношения. Вы увидите, что в регионе Запад наблюдалось падение дохода на 43,8% (рис. 2). Не слишком хорошие результаты.
  8. После завершения ввода своей первой формулы выберите ячейку D4.
  9. Дважды щелкните на маленьком квадратике, находящемся в правом нижнем углу ячейки. Этот квадратик обозначает маркер заполнения, с помощью которого можно скопировать формулу, заполнив весь столбец отчета.

Рис. 2. Создайте формулу в ячейке D4 с помощью мыши и клавиш управления курсором

Рис. 2. Создайте формулу в ячейке D4 с помощью мыши и клавиш управления курсором

После завершения копирования формулы, взглянув на экран, вы поймете, что что-то не так — каждый регион за год продемонстрировал падение на 43,8% (рис. 3).

Рис. 3. Завершив копирование формулы во все ячейки столбца, вы увидите, что что-то не так

Рис. 3. Завершив копирование формулы во все ячейки столбца, вы увидите, что каждый регион продемонстрировал падение на 43,8%

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

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

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

Первая реакция на случившееся у любого пользователя будет следующей: «Что это за странная конструкция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая испортила мой отчет?» Большинство пользователей захотят тут же избавиться от этой функции. Некоторые зададут вопрос: «Зачем компания Microsoft подсунула нам эту функцию?»

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

Блокирование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ путем ввода формулы. Существует простой способ предотвратить появление функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Для этого необходимо создать формулу без использования мыши или клавиш управления курсором. Просто выполните следующие действия.

  1. Перейдите в ячейку D4 и введите = (знак равенства).
  2. Введите С4.
  3. Введите знак / (косая черта, означающая операцию деления).
  4. Введите В4.
  5. Введите –1.
  6. Нажмите Enter.

Теперь вы создали обычную формулу Excel, которую можно скопировать в находящиеся ниже ячейки столбца и с помощью которой можно получить правильные результаты (рис. 5). Как видите, можно создавать формулы в областях, находящихся за пределами сводной таблицы, которые ссылаются на данные внутри сводной таблицы. И те, кто не верят, что такое возможно, пусть выполнят описанные действия самостоятельно.

Рис. 5. Просто введите формулу с клавиатуры

Рис. 5. Просто введите с клавиатуры =С4/В4–1, и формула заработает так, как нужно

Некоторые пользователи будут чувствовать себя некомфортно из-за того, что нарушается привычный порядок ввода формул. К тому предложенный вариант более трудоемкий. Если вы относитесь к числу таких пользователей, для вас – второй способ…

Отключение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Можно навсегда отключить функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Щелкните на ленте меню ФайлПараметры. В открывшемся окне Параметры Excel перейдите на вкладу Формулы и снимите галочку напротив опции Использовать функцию GetPivotData для ссылок в сводной таблице. Нажмите Ok.

Рис. 6. Параметры Excel

Рис. 6. Параметры Excel

Альтернативный вариант. Кликните на сводной таблице, и в появившейся контекстной вкладке Анализ кликните на раскрывающемся списке рядом с кнопкой Параметры. Снимите галочку напротив пункта Создать GetPivotData (рис. 7). По умолчанию галочка включена.

Рис. 7. Параметры сводной таблицы

Рис. 7. Параметры сводной таблицы

Зачем компания Microsoft предложила нам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если эта функция столь ужасна, почему же разработчики из Microsoft включили ее по умолчанию? Почему они заботятся о сохранении поддержки этой функции в новых версиях Excel? Знают ли они о настроениях пользователей? И мы переходим к самому интересному…

Применение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для улучшения сводных таблиц

Сводные таблицы являются великим изобретением человечества. Сводная таблица создается всего лишь несколькими щелчками мыши, исключая необходимость применять расширенный фильтр, функцию БДСУММ и таблицы данных. Благодаря сводным таблицам можно создавать одностраничные отчеты на основе огромных массивов данных. На фоне этих преимуществ уходят в тень некоторые недостатки сводных таблиц, заключающиеся в невыразительном форматировании и необходимости преобразования сводных таблиц в значения для дополнительной настройки. На рис. 8 продемонстрирован типичный процесс создания сводной таблицы. В рассматриваемом случае все начинается с исходных данных. Мы создаем сводную таблицу и пользуемся всеми возможными методиками по ее настройке и улучшению. Иногда мы преобразуем сводную таблицу в значения и выполняем завершающее форматирование.

Рис. 8. Типичный процесс создания сводной таблицы

Рис. 8. Типичный процесс создания сводной таблицы

Новая методика создания сводных таблиц, предложенная Робом Колли (разработчиком Microsoft) и рассматриваемая далее, представляет собой результат усовершенствования, описанного выше процесса. В данном случае сначала создается примитивная сводная таблица. Эту таблицу форматировать не нужно. Затем выполняется одношаговый, относительно трудоемкий процесс по созданию красиво отформатированной оболочки, в которой будет находиться окончательный отчет. После этого используется функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для быстрого наполнения данными отчета, находящегося в оболочке. После получения новых данных можно поместить их на лист, обновить примитивную сводную таблицу и напечатать отчет, находящийся в оболочке (рис. 9). Данная методика обладает рядом неоспоримых преимуществ. Например, вам не придется заботиться о форматировании отчета сразу же после его создания. Процесс создания сводных таблиц становится практически полностью автоматизированным.

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

Рис. 9. Именно таким образом рекомендуется использовать сводные таблицы

Рис. 9. Именно таким образом рекомендуется использовать сводные таблицы

Создание примитивной сводной таблицы. Исходные данные (рис. 10) представлены в виде транзакций, содержащих информацию о плановых и фактических показателях для каждого региона, в котором имеются отделения компании. Плановые показатели детализируются на уровне месяцев, а фактические — на уровне отдельных дней. Плановые показатели создаются на год вперед, а фактические — для прошедших месяцев. Поскольку отчет будет обновляться каждый месяц, этот процесс в значительной степени упрощается, если источник данных сводной таблицы будет увеличиваться в размерах в случае добавления новых данных в нижнюю часть. В устаревших версиях Excel создание подобного источника данных осуществлялось с помощью именованного динамического диапазона, использующего функцию СМЕЩ (подробнее см. Автоматическое обновление сводной таблицы). При работе в Excel 2013 просто выберите одну из ячеек данных, и нажмите комбинацию клавиш Ctrl+T (создайте Таблицу). Возникнет именованный набор данных, автоматически расширяющийся при добавлении новых строк и столбцов.

Рис. 10. Исходный набор данных в виде Таблицы включает плановые и фактические показатели

Рис. 10. Исходный набор данных в виде Таблицы включает плановые и фактические показатели [1]

А теперь создадим сводную таблицу. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — достаточно мощная, но она может возвращать только те значения, которые отображаются в актуальной сводной таблице. Эта функция не в состоянии осуществлять просмотр кеша для вычисления элементов, которые отсутствуют в сводной таблице.

Создайте сводную таблицу:

  1. Выберите команду ВставкаСводная таблица, а затем в диалоговом окне Создание сводной таблицы щелкните ОК.
  2. В списке полей сводной таблицы выберите поле Дата. В левой части сводной таблицы появится перечень дат (рис. 11).
  3. Выберите любую ячейку даты, например, А4. На контекстной вкладке Анализ, находящейся в наборе контекстных вкладок Работа со сводными таблицами, щелкните на кнопке Группировка по полю (подробнее см. Группировка данных сводной таблицы в Excel 2013). В диалоговом окне Группирование выберите параметр Месяцы (рис. 12). Щелкните ОК. В левой части сводной таблицы появятся названия месяцев (рис. 13).
  4. Перетащите поле Дата в область столбцов сводной таблицы.
  5. Перетащите поле Показатель в область столбцов списка полей сводной таблицы.
  6. Выберите поле Регион, которое будет отображаться в левом столбце сводной таблицы.
  7. Выберите поле Доход, которое появится в области значений сводной таблицы.

Рис. 11. Начните с группировки по полю Дата заказа

Рис. 11. Начните с группировки по полю Дата

Рис. 12. Выберите группировку по месяцам

Рис. 12. Выберите группировку по месяцам

Рис. 13. Вместо дат отображаются названия месяцев

Рис. 13. Вместо дат отображаются названия месяцев

На этом этапе наша сводная таблица выглядит довольно примитивно (рис. 14). Мне ужасно не нравятся надписи Названия строк и Названия столбцов. Нецелесообразно отображать итоги по Янв План и Янв Факт в столбце D и т.д. Но не беспокойтесь за внешний вид этой сводной таблицы, ведь, кроме вас, ее никто больше не увидит. Начиная с этого момента мы займемся созданием оболочки отчета, источником данных для которой будет служить только что созданная сводная таблица.

Рис. 14. Примитивна сводная таблица

Рис. 14. Примитивна сводная таблица

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

Выполните следующие действия (рис. 15).

  1. В ячейку А1 введите название отчета – Плановые и фактические показатели по регионам.
  2. Перейдите на вкладку Главная, кликните на кнопку Стили ячеек выберите формат Заголовок 1.
  3. В ячейку А2 введите формулу =КОНМЕСЯЦА(СЕГОДНЯ();0). Эта функция возвращает последний день текущего месяца. Например, если вы читаете эти строки 14 августа 2014 года, в ячейке А2 будет отображаться дата Август 31, 2014.
  4. Выделите ячейку А2. Нажмите комбинацию клавиш Ctrl+1 для отображения диалогового окна Формат ячеек. На вкладке Число щелкните на пункте Все форматы. Введите пользовательский числовой формат в виде "С месяца" ММММ "плановые показатели" (рис. 16). В результате вычисляемая дата будет выглядеть как текст.
  5. В ячейке А5 введите заголовок Регион.
  6. Введите заголовки регионов в остальных ячейках столбца А. Заголовки регионов должны соответствовать названиям регионов, указанным в сводной таблице.
  7. При необходимости добавьте в столбец надписи для итогов по отделениям.
  8. В нижней части отчета добавьте строку Итого по компании.
  9. В ячейку В4 введите формулу =ДАТА(ГОД($А$2);СТОЛБЕЦ(А1);1). Эта формула возвращает даты 01.01.2014, 01.02.2104 и т.д., первые дни всех 12 месяцев текущего года.
  10. Выделите ячейку В4. Нажмите комбинацию клавиш Ctrl+1 для открытия окна Формат ячеек. На вкладке Число в разделе Все форматы введите пользовательский числовой формат МММ. Такой формат отображает трехбуквенное название месяца. Выровняйте текст по правому краю ячейки.
  11. Содержимое ячейки В4 скопируйте в диапазон С4:М4. В верхней части сводной таблицы отобразится строка с названиями месяцев.
  12. В ячейку В5 введите формулу =ЕСЛИ(МЕСЯЦ(B4)<МЕСЯЦ($A$2);"Факт";"План"). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Факт, а для текущего и будущих — План.
  13. В ячейку N5 добавьте заголовок Итог. В ячейку О4 — Итог, О5 — План, Р5 — % отклонения.
  14. Введите обычные формулы Excel, используемые при вычислении итогов по отделениям, строки итога компании, столбца общего итога и столбца % отклонения:
    1. в ячейку В8 введите формулу =СУММ(В6:В7) и скопируйте ее в другие ячейки строки;
    2. в ячейку N6 введите формулу =СУММ(В6:М6) и скопируйте ее в другие ячейки столбца;
    3. в ячейку Р6 введите формулу =ЕСЛИОШИБКА((N6/O10)-1;0) и скопируйте ее в другие ячейки столбца;
    4. в ячейку В13 введите формулу =СУММ(В10:В12) и скопируйте ее в другие ячейки строки;
    5. в ячейку В17 введите формулу =СУММ(В15:В16) и скопируйте ее в другие ячейки строки;
    6. в ячейку В19 введите формулу =СУММ(В6:В18)/2 и скопируйте ее в другие ячейки строки.
  15. К подписям в столбце А и к заголовкам в строках 4 и 5 примените стиль Заголовок 4.
  16. Для диапазона ячеек В6:О19 выберите числовой формат # ##0.
  17. Для ячеек столбца Р выберите числовой формат 0,0%.

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

Рис. 15. Оболочка отчета

Рис. 15. Оболочка отчета перед добавлением формул ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Рис. 16. Пользовательский формат ячеек

Рис. 16. Пользовательский формат ячеек

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

Выберите ячейку В6 оболочки отчета. Эта ячейка соответствует северо-восточному региону и фактическим показателям за январь.

  1. Введите = (знак равенства), чтобы начать ввод формулы.
  2. Перейдите на лист со сводной таблицы и щелкните на ячейке, которая соответствует северо-восточному региону и фактическим показателям за январь – С12 (рис. 17).
  3. Нажмите клавишу Enter, чтобы закончить ввод формулы и вернуться к оболочке отчета. В результате Excel добавит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в ячейку В6. В ячейке отразится значение 277 435 долларов.

Рис. 17. Щелкните мышью на требуемой ячейке сводной таблицы

Рис. 17. Щелкните мышью на требуемой ячейке сводной таблицы

Запомните это число, поскольку оно потребуется при сравнении с результатами выполнения формулы, которую вы будете редактировать в дальнейшем. Формула, сгенерированная программой, имеет следующий вид: =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Доход";’Рис. 11-14′!$A$3;"Регион";"Северо-Восток";"Дата";1;"Показатель";"Факт"). Если вы до сих пор игнорировали функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, пришло время поближе с ней познакомиться. На рис. 18 эта формула показана в режиме редактирования вместе с подсказкой.

Рис. 18. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ с всплывающей подсказкой

Рис. 18. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ с всплывающей подсказкой

Аргументы функции:

  • Поле_данных. Поле из области значений сводной таблицы. Обратите внимание: в данном случае используется поле Доход, а не Сумма по полю Доход.
  • Сводная_таблица. С помощью этого параметра компания Microsoft спрашивает вас: «Какую сводную таблицу вы хотите использовать?» Достаточно указать одну из ячеек сводной таблицы. Запись ‘Рис. 11-14’!$A$3 ссылается на первую ячейку сводной таблицы, в которую вводятся данные. Поскольку в нашем случае можно задать любую ячейку, относящуюся к сводной таблице, оставьте аргумент без изменений. Адрес ячейки $А$3 подходит во всех отношениях.
  • Поле 1; элемент 1. В автоматически сгенерированной формуле в качестве имени поля выбрано имя Регион, а в качестве значения поля — Северо-Восток. Именно здесь кроется причина проблем, возникающих при работе с функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Автоматически выбираемые значения не могут быть скопированы, потому что жестко закодированы. Поэтому в случае копирования формул во всей области отчета придется изменять их вручную. Вместо значения Северо-Восток подставьте ссылку на ячейку в виде $А6. Путем указания знака доллара перед названием столбца А вы определите возможность изменения части ссылки, относящейся к строкам, при копировании формулы в ячейки столбца.
  • Поле 2; элемент 2. Эта пара аргументов определяет поле Дата со значением 1. Если исходная сводная таблица была сгруппирована по месяцам, поле месяца сохраняет исходное имя поля Дата. Числовое значение месяца равно 1, что соответствует январю. Вряд ли целесообразно использовать подобное значение при создании огромных формул, задаваемых в десятках, а то и в сотнях ячеек отчета. Уж лучше воспользоваться формулой, вычисляющей значения поля Дата, наподобие формулы в ячейке В4. Вместо 1 в данном случае можно воспользоваться формулой МЕСЯЦ(В$4). Знак доллара, указываемый перед 4, свидетельствует о том, что формула может присваивать значения полю Дата на основе других месяцев по мере копирования формулы в ячейки строки.
  • Поле 3; элемент 3. В данном случае автоматически присваивается имя поля Показатель и значение поля Факт. Эти значения корректны для января, но для последующих месяцев значение поля придется изменить на План. Измените жестко заданное значение поля Факт на ссылку В$5.
  • Поле 4; элемент 4. Эти аргументы не используются, т.к. поля закончились.

Новая формула показана на рис. 19. За минуту вместо жестко закодированной формулы, предназначенной для работы с единственным значением, была создана гибкая формула, которую можно скопировать во все ячейки набора данных. Нажмите клавишу Enter, и вы получите тот же результат, что и до редактирования формулы. Отредактированная формула принимает следующий вид: =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Доход";’Рис. 11-14′!$A$3;"Регион";$A6;"Дата";МЕСЯЦ(B$4);"Показатель";B$5)

Рис. 19. Отредактированная формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Рис. 19. По завершении редактирования формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ пригодна для копирования во все ячейки диапазона

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

На следующем шаге настроим формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для вычисления итоговых плановых показателей. Если просто скопировать формулу в ячейку О6, отобразится сообщение об ошибке #ССЫЛКА! Причина появления этой ошибки заключается в том, что слово Итог в ячейке О4 не является названием месяца. Для обеспечения корректной работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ требуемое значение должно быть в сводной таблице. Но поскольку в исходной сводной таблице поле Показатель является вторым по счету полем в области столбцов, столбец с данными План итог фактически отсутствует. Переместите поле Показатель таким образом, чтобы оно стало первым в области столбцов (рис. 20).

Рис. 20. Настройте расположение полей в области столбцов

Рис. 20. Настройте расположение полей в области столбцов таким образом, чтобы появился столбец План Итог

Сравните с рис. 14. Там в области КОЛОННЫ первым шло поле Дата, что приводило к тому, что сначала столбцы группировались по дате, а внутри каждого месяца по план/факту. Сейчас же первым расположено поле Показатель, а в сводной, сначала идут столбцы План, внутри отсортированные по месяцам, а затем идут все столбцы Факт.

Вернувшись к листу оболочки отчета, встаньте в ячейку О6, наберите = (знак равно) и сошлитесь на ячейку N12 на листе сводной таблице, соответствующую плановым итогам региона Северо-Восток. Нажмите Enter. Получится формула =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Доход»;’Рис. 11-14′!$A$3;»Регион»;»Северо-Восток»;»Показатель»;»План»). Отредактируйте ее: =ПОЛУЧИТЬ. ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Доход»;’Рис. 11-14′!$A$3;»Регион»;$A6;»Показатель»;O$5). Скопируйте эту формулу в другие ячейки столбца О (рис. 21). Обратите внимание, что даже при перемещении разных областей отчета сводной таблицы, оболочка работает корректно. Конечно, если сделать какие-то поля сводной неактивными, оболочка с этим не справится…

Рис. 21. Итоговый отчет

Рис. 21. Итоговый отчет, который можно представить менеджеру

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

Обновление отчета. Для обновления отчета данными, относящимися к будущим месяцам, выполните следующие действия.

  1. Вставьте фактические показатели ниже исходного набора данных. Поскольку для исходных данных выбран формат таблицы, табличное форматирование автоматически распространяется на новые строки данных. Также расширяется определение исходной сводной таблицы (в Excel-файле я уже добавил фактические показатели за весь год).
  2. Перейдите к сводной таблице. Щелкните правой кнопкой мыши и выберите Обновить. Вид сводной таблицы изменится, но это не страшно.
  3. Перейдите к оболочке отчета. В принципе, все уже сделано для обновления отчета, но не мешает протестировать полученные результаты. Измените формулу в ячейке А2, например, на такую: =КОНМЕСЯЦА(СЕГОДНЯ()+31;0), и посмотрите, что получится.

Ежемесячно добавляя новые данные о фактических продажах, вам не придется беспокоиться о повторном создании форматов, формул и т.п. Описанный процесс обновления отчета настолько прост, что вы навсегда забудете о проблемах, которые возникали при составлении ежемесячных отчетов. Единственная проблема может возникнуть в случае реорганизации компании, в результате чего в сводной таблице могут появиться новые регионы. Чтобы гарантировать корректную работу формул, проверяйте, что итоговая сумма в вашем отчете совпадает с итогами в сводной таблицу. При появлении нового региона, просто добавьте его на лист с оболочкой и «протащите» соответствующие формулы.

Не думал, что когда-либо скажу следующее: «Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — величайшее благо. Как мы существовали без нее раньше?»

 

[1] В оригинале у Джелена исходные данные были устроена так, что дальнейшие формулы корректно отрабатывали только в июле 2015 г. В приложенном к этой заметке Excel-файле я модифицировал исходные данные, а также некоторые формулы так, чтобы всё работало, независимо от даты, когда вы будете экспериментировать с вложенным Excel-файлом. К сожалению, формулы пришлось усложнить.

19 комментариев для “Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ”

  1. добрый день. А где приложенный файлик?
    п.с. ваша статья просто отличная

  2. Файлик ближе к началу статьи. Там есть строчка: «Скачать заметку в формате Word или pdf, примеры в формате Excel»

  3. Добрый вечер!
    Поясните, пожалуйста, зачем всё же это всё надо? Если меняется формат сводной таблицы — в «оболочке» формулы часто будут =ССЫЛКА, или часто надо будет переделывать «оболочку». Не проще ли тогда отказаться от сводной и прописать формулы в «оболочке», вытягивающие данные сразу из источника данных?
    На не менее авторитетных сайтах (Планета Эксель, например) не нашел статей про эту чудо-функцию. Может, не так уж она и актуальна?

  4. IVAN, спасибо, что считаете мой сайт авторитетным)) Согласен с вами, что полезность функции не является однозначной. Я, например, не использую эту функцию, и отключаю ее, так как сслылаться на ячейки сводной таблицы приходится постоянно.

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

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

    В итоге не могу задать элементы на конкретную ячейку чтобы потом протягивать функцию в финальной таблице.

  7. Кирилл, а вы попробовали рекомендации из раздела «Отключение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ», как описано выше? Не помогло?

  8. Добрый день!
    Такой вопрос: в сводной таблице сделал поле фильтр по датам. Можно ли на том же листе в поле, свободном от сводной таблицы вывести минимальную и максимальную дату из фильтра с помощью формулы?

  9. Илья, можно. В одной ячейке введите =МИН(В:В), если предположить, что даты в сводной таблице расположены в столбце В. В другой ячейке введите =МАКС(В:В). Определите этим ячейкам требуемый формат даты.

  10. Добрый день!
    Работаю со сводными таблицами по данным OLAP.
    Используя великолепную функцию ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ получаю следующее ))))):

    =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("
    [Measures]. [FormatedValue]";$B$13;"[DimArticles]. [Articles]";"[DimArticles].[Articles]. &[697]";"[DimOwners].[Owners]"; "[DimOwners].[Owners].&[9]";"[DimObjectsInDirections].[ObjectsInDirections]"; "[DimObjectsInDirections].[ObjectsInDirections].&[2289]")

    Меры и измерения называются как в проекте куба.
    В Translation всё естественно по русски)))
    [DimArticles].[Articles] — Статьи
    [DimObjectsInDirections].[ObjectsInDirections] — Объекты
    Не подскажете, как в сводной таблице всё же можно переименовать меры и измерения в человеческий вид?
    Чтобы можно было писать

    =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Значение";$B$13; "Статьи"; "План";"Владелец";"Рога и копыта";"Объекты";"Путь к коммунизму")

    Заранее ОЧЕНЬ благодарен!!!

  11. Олег, я не являюсь специалистом по OLAP, поэтому проконсультировался с коллегой. Вот что он ответил: «Translation мы не используем, но на тесте сделал и вот что увидел. Если правильно сделать Translation, то названия полей в сводной таблице идет на русском языке – работать с ними удобно и смотреть тоже на это приятно. Но вот функции все равно используют основные исходные названия – можно ли сделать иначе, ответа найти не смог.
    Почему так, мое видение: API Excel общается с сервером в объектах, которые на сервере хранятся – а это английские названия полей и атрибутов, и именно по нему формируются запросы к серверу.
    Translation – это удобный вид на нужном языке, не более того».

  12. Сергей Викторович, огромное спасибо!

    Получилось сделать через PowerPivot.
    Очень приятно, что в PowerPivot пишешь сам запросы к серверу — они оптимальные и читабельные, поля в результирующей таблице переименовываются в читаемый вид и в итоге имеем следующее:

    =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("[Measures].[Кол-во Товар]";$B$3;"[Баланс].[Ресурс]";"[Баланс].[Ресурс].&[Аккумулятор АК-100]";"[Баланс].[Ед.изм.]";"[Баланс].[Ед.изм.].&[шт]";"[Баланс].[Владелец]";"[Баланс].[Владелец].&[Рога и копыта]")

  13. Евгения

    Добрый день, я как раз с большим удовольствием и постоянно использую функцию ПОЛУЧИТЬ.ДАННЫЕ…
    Но сегодня столкнулась со странной ошибкой: в ячейке прописана формула: =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("План ДДС";$B$12;"Неделя платежа / поступления по текущему статусу";22;"Месяц платежа / поступления по текущему статусу";$M$13;"Валюта";$L$7)
    и она дает результат #ссылка, хотя для 23 или для 24 недели (в соседних ячейках) данные подтягиваются из сводной таблицы совершено правильно. Возможно проблема в том, что часть данных по 22 недели относится к маю, а часть к июню….
    Но даже если я не пишу формулу, а вношу ячейку = и щелкаю мышью по ячейке в сводной таблице, из которой мне нужны данные (формула подставляется автоматически), то все равно выводится ошибка #ссылка. Не могу разобраться — что не так, если кто знает — помогите пожалуйста 🙂

  14. Файл весит 30 мб архиве… может можно будет Вам его показать в режиме демонстрации по skype?

  15. Уважаемый Сергей Викторович! Добрый день!
    Помогите пожалуйста решить такую задачку.
    Необходимо вывести максимальную дату из таблицы по нескольким условиям:
    Фамилия; № Упр1; № Упр2; № Упр3; № Упр4
    Пример файла отправил Вам на почту.
    Спасибо!

  16. Прочитал статью, вроде нигде не упомянуто, что если к исходной сводной пользователь применит фильтры, то ваша красивая оболочка потеряет ссылки на скрытые данные. Выход скрыть лист содержащий базовую сводную. В итоге проблема с обновлением сводной. Лучше писать макрос и кнопку, иначе пользоваться кнопкой штатной обновить все. Иногда приходится нажимать дважды если Сводная не обновилась с одного нажатия

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

    Или осилить power query, она вас удивит

  17. здравствуйте, подскажите простым языком как =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ преобразовывать по данным OLAP.

    у меня такая формула
    GETPIVOTDATA(«[Measures].[Sales Volume (tn)]»;’LE (2)’!$B$12;»[CMA_Report_MVP].[Scenario]»;»[CMA_Report_MVP].[Scenario].&[Forecast LE Demand]»;»[CMA_Report_MVP].[Local Channels]»;»[CMA_Report_MVP].[Local Channels].&[Distributors]»;»[CMA_Report_MVP].[Chain]»;»[CMA_Report_MVP].[Chain].&[Produktovaya Mozaika]»)

    как сделать чтобы вместо работали ссылки на ячейки с названиями?
    если я понятно изъясняюсь(((
    что-то такое, это на простой пивотке работает:
    GETPIVOTDATA(T(AC$3);’data cma’!$A$3;»Scenario»;$AC$4;»Local Channels»;$B9

    спасибо

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *