Несмотря на появление новых функций вычисления итогов в Excel 2010, иногда просто невозможно получить данные, необходимые для формирования того или иного отчета. В подобных случаях вам понадобится промежуточная сводная таблица в качестве источника данных для формируемого отчета. Предположим, что нужно показать продажи за четыре месяца, завершая февралем 2012, и сравнить их с продажами за предыдущий период. В этом случае потребуются данные за январь и февраль 2012 года; за январь, февраль, ноябрь и декабрь 2011 года; за ноябрь и декабрь 2010 года. Решение этой задачи лежит за границами возможностей обычных сводных отчетов.
Для решения этой задачи выполните следующие действия.
- Создайте сводную таблицу, в которой отображаются сведения о продаже за все месяцы и годы (рис. 3.42).
Рис. 3.42. В этой сводной таблице выполняются всевозможные вычисления
- Щелкните в области сводной таблицы и выберите контекстную вкладку Параметры (Options). В левой части ленты находится кнопка Параметры (Options), после щелчка на которой открывается раскрывающийся список. Просмотрите этот список и убедитесь в том, что установлен флажок Создать GetPivotData (Generate Get Pivot Data).
- На другом листе создайте форматированный отчет. Проверьте наличие заголовков, позволяющих идентифицировать год, месяц и название оборудования. Добавьте формулы, используемые для вычисления итогов и относительного роста. Не заполняйте остальные ячейки данными.
- Выберите первую ячейку с данными на рабочем листе отчета. В рассматриваемом случае выбирается ячейка С4, в которой отображаются данные о продажах промышленного оборудования за ноябрь 2011 года (рис. 3.43).
- Введите знак равенства (=). Щелкните на ярлычке листа сводной таблицы для его выбора. Прокручивайте лист до тех пор, пока не найдете сведения о продажах промышленного оборудования за ноябрь 2011 года. Щелкните на этой ячейке и нажмите клавишу Enter. Вполне логично ожидать появления формулы типа =CaseStudyPivot!Е28. Но вместо нее Excel вставляет довольно замысловатую формулу вида ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (…), как показано на рис. 3.43.
Рис. 3.43. Для создания формулы введите знак равенства и щелкните на нужной ячейке сводной таблицы
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, созданная на шаге 5, жестко запрограммирована на возвращение значений из одной заданной ячейки сводной таблицы. Эта функция знакома многим пользователям, но далеко не все нашли время, чтобы детально разобраться в ее синтаксисе.
- =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, «Доход» — получение значения поля Доход.
- CaseSCudyPivot$A$3 — идентификация сводной таблицы путем выбора любой ячейки. Обратите внимание: по умолчанию Excel выбирает левую верхнюю ячейку сводной таблицы.
- «Год», 2011 — последняя пара аргументов представляет собой имя поля и возвращаемое значение. Второй аргумент из этой пары жестко закодирован. Ключ к успешному применению функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — параметризация аргументов, которая позволит изменять их в дальнейшем.
- Отредактируйте формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Измените значение параметра 2011 на указатель ячейки С$3. Вместо значения параметра Промышленное оборудование используйте указатель на ячейку $В4. Выберите пользовательский формат для данных в ячейках в виде #, ##0, К. Как показано на рис. 3.44, результат не изменился, просто была создана формула, которая может быть скопирована.
Рис. 3.44. Измените функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ таким образом, чтобы использовать параметры отчета
- Скопируйте формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ во все ключевые ячейки сводной таблицы. В результате получим красиво отформатированный отчет, отображающий данные из промежуточной сводной таблицы (рис. 3.45). Несмотря на то что для построения этого отчета потребуется довольно много времени и усилий, результаты его выполнения будут весьма впечатляющими. Ниже приведен перечень преимуществ, связанных с применением подобной формы отчета.
Рис. 3.45. Этот отчет выводит данные из промежуточной сводной таблицы
- При изменении исходных данных для коррекции данных в полученном отчете достаточно обновить сводную таблицу, на основе которой он построен.
- При обновлении сводных таблиц часто исчезает исходное форматирование. Но поскольку полученный форматированный отчет не является сводной таблицей, его форматирование сохраняется.
- Если, например, между столбцами F и G нужно вставить пустой столбец, эту операцию можно выполнить несколькими щелчками мыши. В обычной сводной таблице эту операцию выполнить невозможно.
- И наверное, самый главный аргумент в пользу функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ заключается в том, что ее активно используют сотрудники Microsoft.
Учтите, что использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ может привести к появлению определенных проблем. Если кто-либо изменил название категории Промышленное оборудование в отчете сводной таблицы, придется соответствующим образом изменить подпись в ячейке В4. Если этого не сделать, будет невозможно выбрать данные из промежуточной сводной таблицы. Функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ невозможно использовать для выборки данных из сводных таблиц OLAP и PowerPivot. В подобных случаях воспользуйтесь функциями кубов данных для создания совместимой сводной таблицы.
