Подбор параметра, поиск решения и сценарии

ОБУЧЕНИЕ СВОДНЫМ ТАБЛИЦАМ EXCEL НА ПРИМЕРАХ

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

РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ В EXCEL

Создайте таблицу исходных данных о клиентах фирмы, так как показано на рисунке:

Теперь по исходной базе данных клиентов построим сводную таблицу, в которой можно легко отобразить средний возраст всех клиентов фирмы, которые не имеют дисконтную скидку c распределением на:

• возраст;

• образование;

• пол.

Решение для построения сводного отчета в Excel:

1. Перейдите на любую ячейку исходной клиентской базы и выберите инструмент: «Вставка»-«Таблицы»-«Сводная таблица».

2. В появившемся диалоговом окне переключите на опцию «На существующий лист» и в поле «Диапазон» укажите значение H3:

3. В окне настроек (справа) «Список полей сводной таблицы» переместите значение «Пол» из поля «Выберите поля для добавления в отчет» в поле «Фильтр отчета»:

4. Таким же образом распределите остальные значения по полям так как указано выше на рисунке.

5. Щелкните правой кнопкой мышки по любой ячейке внутри сводной таблицы. Из появившегося контекстного меню выберите опцию: «Итоги по»-«Среднее».

6. Округлите все значения до десятых с помощью формата ячеек.

Обратите внимание! В ячейке I1 мы имеем возможность указать пол (мужской, женский или оба) для сегментирования отчета.

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

ПРИМЕР НАСТРОЙКИ СВОДНЫХ ОТЧЕТОВ

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

1. Если вы случайно закрыли окно «Список полей сводной таблицы», тогда щелкните левой кнопкой мышки по сводной таблице, чтобы сделать ее активной. Затем включите опцию: «Работа со сводными таблицами»-«Параметры»-«Показать»-«Список полей».

2. Переместите значение «Пол» в поле «Название строк» под значение «Образование».

Структура отчета кардинально изменена:

Появились группировки, которые управляются кнопками (+ и -). Эти вспомогательные кнопки и заголовки можно включить/отключить через меню: Работа со сводными таблицами»-«Параметры»-«Показать»-«Кнопки» или «Заголовки полей». Так же у нас больше нет фильтра для сегментирования отчета по полу в ячейке I1.