Шаг 3. Формирование таблицы

На этом шаге определяется местоположение сводной таблицы, ее имя, структура и внешний вид.

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

Рис. 6.12. Окно шага 3 Мастера сводных таблиц и диаграмм

- Присвойте имя сводной таблице можно, нажав кноп­ку Параметры (рис. 6.13).

- Изучите все параметры сводной таблицы.

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

- Структура сводной таблицы определяется после нажатия кнопки Ма­кет и открытия диалогового окна настройки макета сводной таблицы (рис. 6.14).

Рис. 6.14. Диалоговое окно формирования структуры сводной таблицы

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

Макет сводной таблицы состоит из следующих фиксированных облас­тей:

- Страница. Значения в данной области выступают в качестве эле­ментов страницы сводной таблицы;

- Строка. Значения в данной области выступают в качестве эле­ментов строки в сводной таблице, т.е. в качестве заголовков строк таблицы;

- Столбец. Значения в данной области выступают в качестве эле­ментов столбца в сводной таблице, т.е. в качестве заголовков столбцов таблицы;

- Данные. В данной области отображаются детальные и итого­вые для сводной таблицы поля, которые составляют содержи­мое таблицы.

Таблица 6.2 Значения параметров сводной таблицы

Параметр	Назначение (действие)
Имя	Присвоение имени. Excel автоматически задает имена в форме СводнаяТаблица1 и т.д.
Общая сумма по столбцам	Вычисление общей суммы для элементов, отображаемых в столбцах
Общая сумма по строкам	Вычисление общей суммы для элементов, отображаемых в строках
Автоформат	Установка одного из типов автоформатирования
Включать скрытые значения	Включение скрытых элементов, находящихся на странице, в промежуточные суммы
Объединять ячейки заголовков	Объединение ячеек внешней строки и столбца заголовков
Сохранять форматирование	Сохранение форматирования после обновления сводной таблицы
Повторять подписи на каждой странице печати	Расположение подписи строк на каждой стра­нице распечатанного отчета сводной таблицы
Макет страницы	Установка порядка, в котором будут отобра­жаться поля страницы
Число полей в столбце	Задание числа полей страницы, отображаемых в одной строке страницы
Для ошибок отображать	Установка значения, которое будет отобра­жаться для ячеек сводной таблицы, содержащих ошибку
Для пустых ячеек отображать	Установка значения, которое будет показано для пустых ячеек сводной таблицы
Печать заголовков	Расположение подписи столбцов на каждой странице распечатанного отчета сводной таблицы
Помечать итоги	Отображение звездочкой индикации того, что промежуточные и итоговые суммы включают и отображаемые, и скрытые элементы. Данный параметр активизирован только в том случае, когда сводная таблица создается из источника данных OLAP
Сохранять данные вместе с таблицей	Сохранение дополнительной копии данных
Развертывание разрешено	Просмотр элементов, составляющих итоговые значения
Обновить при открытии	Обновление сводной таблицы при каждом открытии рабочей книги
Обновлять каждые... минуты1	Задание интервала времени, через который происходит обновление сводной таблицы в открытой рабочей книге
Сохранить пароль1	Сохранение пароля для открытия внешней базы данных
Фоновый запрос1	Выполнение запроса базы данных в фоновом режиме, что позволяет не прерывать работу
Оптимизировать память1	Уменьшение объема памяти, используемой при обновлении запроса внешней базы данных

¹ Параметры Обновлять каждые... минуты, Сохранить пароль, Фоновый запрос, Оптимизировать память активизированы в слу­чае, когда сводная таблица создается из внешней базы данных.

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

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

Одно и то же поле можно многократно разместить в области данных, задав для него разные виды итогов.

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

- По завершению формирования макета нажмите ОК, а затем и по кнопке Готово. На рабочем листе появится сводная таблица. Вид ее применительно к рассматрива­емому примеру показан на рис. 6.16.

Рис. 6.16 Фрагмент окна сформированной сводной таблицы

Как видно, поля, которые были помещены в области макета Страница, Строка и Столбец, отображаются в виде раскрывающихся списков. Так, для элемента области Страница «Отрасль» можно выбрать в списке параметр с именем «Все», что обеспечит вывод сведений по всем отрас­лям базы данных (в нашем примере: для юридических лиц — меди­цина, промышленность, торговля, для физических лиц — предприни­матель), а можно указать только определенные параметры, что обеспе­чит вывод соответствующих данных.

- Выведите информацию только о клиентах — юридических лицах.

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

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

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

В качестве примера приведем механизм формирования вычисляемого поля для расчета платы банку за издержки при оформлении кредита. Пусть данная величина составляет 0,5% от суммы кредита.

Схема создания вычисляемого поля может быть представлена следу­ющим образом.

1) Установите курсор в любую ячейку сводной таблицы.

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

3) В поле окна Имя введите название вычисляемого поля (в при­мере — Плата банку за издержки).

4) В поле Формула введите формулу расчета нового поля

(в при­мере: ='Сумма кредита'*0,5%).

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

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

5) Для добавления созданного поля в сводную таблицу нажмите Добавить.

6) Для закрытия диалогового окна нажмите ОК.

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

Сводная таблица после добавления в нее вычисляемого поля приобре­тет следующий вид (рис. 6.18).