Построение отчета сводной таблицы по данным одной таблицы, как правило начинается с команды Вставка [Insert] – Сводная таблица [PivotTable]. Для построения сводной таблицы по нескольким диапазонам консолидации необходимо воспользоваться Мастером сводных таблиц.

Начиная с Excel 2007 версии, команда Мастер сводных таблиц не располагается на ленте. Поэтому команду можно вывести, для этого выбрать Файл [File], Параметры [Options], в разделе Панель быстрого доступа [Quick Access Toolbar] в списке Выбрать команды из [Choose commands from] выбрать Команды не на ленте [Commands Not in the Ribbon]. Выделить Мастер сводных таблиц и диаграмм [PivotTable and PivotChart Wizard], нажать кнопку Добавить >> [Add >>], ОК.

ПОСЛЕДОВАТЕЛЬНОСТЬ ДЕЙСТВИЙ

Нажать кнопку Мастер сводных таблиц и диаграмм [PivotTable and PivotChart Wizard)]

В появившемся окне Мастер сводных таблиц и диаграмм – шаг 1 из 3 [PivotTable and PivotChart Wizard – Step 1 of 3] выбрать в нескольких диапазонах консолидации [Muitiple Consolidation ranges], нажать Далее [Next].

В окне Мастер сводных таблиц и диаграмм – шаг 2а из 3 [PivotTable and PivotChart Wizard – Step 2a of 3] выбрать Создать поля страницы [I will create the page fields], нажать Далее [Next]

В окне Мастер сводных таблиц и диаграмм – шаг 2б из 3 [PivotTable and PivotChart Wizard – Step 2b of 3] выбрать диапазоны для объединения и создать поля:

• В поле Диапазон [Range] выделить первый исходный диапазон вместе с заголовками, нажать Добавить [Add]. Повторить для всех исходных дипазонов
• В поле Во-первых, укажите количество полей страницы сводной таблицы [How many page fields do you want?] выбрать нужное количество полей (фильтры отчета сводной таблицы). Например, объединяя данные продаж компаний по разным городам можно создать 2 поля – компания и город.
• Каждый диапазон из списка диапзонов нужно описать по каждому из заданных полей (максимум 4). Выделить диапазон в списке диапазонов, в Первое поле [Field one] ввести описание первого поля и т.д. Например, Компания – это первое поле, Город – второе поле.

Нажать Далее [Next].
В окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 [PivotTable and PivotChart Wizard – Step 3 of 3] выбрать расположение сводной таблицы.

Нажать Готово [Finish].

Поля Страница1 и Страница2, расположенные в области ФИЛЬТРЫ можно переименовать, переместить в область макета СТОЛБЦЫ и СТРОКИ, чтобы придать отчету большей наглядности:

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

Выбрать Файл [File], Параметры [Options] и в разделе Формулы [Formulas]в группе Работа с формулами [Working with formulas] убрать флажок Использовать функции GetPivotData для ссылок в сводной таблице [Use GetPivotData functions for PivotTable references].

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

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

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

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

На вкладке Анализ [Options] (в 2010 версии вкладка Параметры [Options]), в группе Вычисления [Calculations], раскрыть список Поля, элементы и наборы [Fields, Items, &Sort] и выбрать Вычисляемое поле [Calculated Field]:

• В поле Имя [Name] ввести имя нового поля.
• В поле Формула [Formula] составить формулу, начиная со знака = и выбирая доступные поля в списке Поля [Fields] (для вставки можно использовать двойной щелчок по полю или выделить поле и нажать кнопку Добавить поле [Insert Field]).
• Нажать Добавить [Add], ОК.

Настройка внешнего вида вычисляемого поля, как и любого другого поля, происходит в окне Параметры полей значений [Value Field Settings], где нужно установить процентный формат.

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