Итоговая таблица в Excel

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

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

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

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

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

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

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

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

Рис. 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 с помощью мыши и клавиш управления курсором

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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