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

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

Рис. 6.18Фрагмент сводной таблицы с вычисляемым полем

Сформируйте вычисляемый элемент, отобража­ющий сумму кредита по Московскому региону.

Выполните следующие действия:

1) Поместите курсор на то поле, которое будет содержать новый вычисляемый элемент. В нашем примере таким полем будет поле Филиал.

2) На панели инструментов Сводные таблицы раскройте список кнопки Сводная таблица и выберите команды: Формулы → Вычисляемый объект...

В результате появится диалоговое окно Вставка вычисляемого элемента (рис. 6.19).

Рис. 6.19 Окно формирования формулы вычисляемого элемента сводной таблицы

3) В поле Имя укажите название нового элемента, в поле Фор­мула введите формулу расчета нового элемента. В нашем при­мере имя нового объекта — Итоги по Московскому региону, а формула имеет следующий вид: ='Перово'+Сокол+Фили.

4) Для добавления созданного нового элемента в поле сводной таблицы щелкните по кнопке Добавить.

5) Для закрытия диалогового окна щелкните по кнопке ОК.

Иногда требуется сгруппировать данные в соответствии с определен­ным временным интервалом. Например, на основании базы данных (рис. 6.4) необходимо проанализировать выдачу кредитов по меся­цам (кварталам).

- Для этого первоначально создайте сводную таблицу на отдельном листе, в которой согласно макету (рис. 6.14) в области строк приведены даты выдачи кредитов, в области столбцов, например, филиалы, в кото­рых выданы кредиты, в области данных — суммы кредитов.

Результат выполненных действий должен быть, как на рис. 6.20.

Рис. 6.20Сводная таблица выдачи кредитов в филиалах по датам

Далее для создания группы по месяцам (кварталам) необходимо выпол­нить следующие операции:

1 ПКМ по ячейке в пределах столбца Дата выдачи кредита → Команда Группа и структура → Группировать...¹ → Задать интервал группировки → С шагом: Месяцы (Кварталы, Годы,...) → ОК

¹ Последующие операции выполняются в окне, представленном на рис. 6.21.

Рис. 6.21 Окно задания параметров группировки сводной таблицы

В результате будет сформирована сводная таблица с группировкой дан­ных по месяцам (рис. 6.22.

Рис. 6.22 Сгруппированная сводная таблица выдачи кредитов в филиалах по месяцам

- Разгруппируйте сводную таблицу (исполь­зуя контекстное меню) и выполните новую группировку по кварталам.

Завершив формирование сводной таблицы, представьте ее более красочно, в виде одного из 22 вариантов оформления — 10 отчетов, 10 таблиц, классической сводной таблицы и макета без форматирова­ния (рис. 6.23). Установите курсор в пределах сводной таблицы → Кнопка Формат отчета панели Сводные таблицы → Выбрать макет отчета → Кнопка ОК.

Рис. 6.23Окно автоформатирования сводной таблицы

Консолидация данных

Другим инструментом обобщения и анализа данных является консоли­дация.

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

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

Консолидировать данные можно несколькими способами:

- по расположению данных;

- по категориям;

- с помощью трехмерных ссылок в формулах.

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

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

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

Рассмотрим реализацию метода консолидации.

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

1) Создайте таблицы одинаковой структуры посещаемости стран за январь (на новом листе) и за февраль (на новом листе) (см. рис. 6.24).

Рис. 6.24 Таблица посещаемости стран за январь

2) Выделите на новом листе одну или несколько ячеек.

3) Используя оконное меню, выберите команды: Данные → Кон­солидация. В результате на экране появится диалоговое окно Консолидация (рис. 6.25).

4) В диалоговом окне в поле Функция выберите функцию для обобщения данных (в нашем примере, функцию Сумма).

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

6) Для включения диапазона в группу обрабатываемых данных нажмите Добавить. Выбранный диапазон отобра­зится в поле Список диапазонов.

7) Повторите ввод других диапазонов данных, участвующих в обобщении, используя поле Ссылка и кнопку Добавить.

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

9) Закройте диалоговое окно кнопкой ОК, что приведет к объеди­нению данных в одну или несколько изначально выделенных ячеек. Консолидация будет завершена.

Рис. 6.25 Диалоговое окно консолидации данных

Рис. 6.26 Результат консолидации данных

Контрольные вопросы

1) Какова технология подведения промежуточных итогов?

2) Как выполнить промежуточные итоги по одному или несколь­ким полям списка?

3) Что такое сводная таблица? Каково ее назначение и сферы применения?

4) Какова технология создания сводных таблиц?

5) Как изменить параметры сводной таблицы?

6) Назовите основные значения параметров сводной таблицы.

7) Что такое вычисляемое поле?

8) Для чего используется механизм создания вычисля­емых полей? Как создать вычисляемое поле?

9) Что такое вычисляемый элемент? Как его создать?

10) Как сгруппировать и разгруппировать данные сводной таблицы?

11) Что такое консолидация данных?

12) Какие существуют способы консолидации данных?

13) Можно ли выполнить консолидацию и на основе одной таблицы?