36

Рисунок 52 – Составленный отчет

Создайте еще один сценарий – увеличение на 50 % и выполните все дей-

ствия. Создайте отчет по всем трем сценариям (исходный, увеличение на 25 %

и увеличение на 50 %).

Сделайте вывод.

Лабораторная работа 5

Сводная таблица и диаграмма

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

ми отображались наилучшим образом. Сводные таблицы впервые появились в пятой версии программы Microsoft Excel.

Изучать возможности использования сводных таблиц будем на примере таб-

лицы, содержащей сведения о продаже путёвок турфирмы (таблица 3).

37

Таблица 3 – Продажи путёвок турфирмы

Создание сводной таблицы в MS Excel 2003

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

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

Создание и обработка сводных таблиц осуществляются с помощью специального мастера, для запуска которого предназначена команда Сводная таблица из меню Данные (MS Excel 2003) (рисунок 53).

Рисунок 53 – Вызов команды Сводная таблица в MS Excel 2003

38

После её вызова MS Excel 2003 – первое диалоговое окно мастера сводных таблиц и диаграмм – Мастер сводных таблиц и диаграмм – шаг 1 из 3 (рисунок 54).

Рисунок 54 – Диалоговое окно Мастер сводных таблиц и диаграмм – шаг 1 из 3

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

рим далее. Нажмите кнопку Далее, чтобы перейти в следующее диалоговое окно мастера (рисунок 55).

Рисунок 55 – Диалоговое окно Мастер сводных таблиц и диаграмм – шаг 2 из 3

39

Во втором окне мастера сводных таблиц определяется диапазон ячеек, дан-

ные из которого будут включены в сводную таблицу. Если перед запуском ма-

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

говое окно выбора файла, и загрузить нужную книгу. Укажите диапазон ячеек,

на основе которого должна быть составлена сводная таблица, и перейдите в сле-

дующее окно мастера, нажав кнопку Далее.

Третье диалоговое окно мастера сводных таблиц служит для выбора местопо-

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

цы на текущем рабочем листе необходимо указать позицию её левого верхнего угла (рисунок 56). После щелчка по кнопке Макет на экране откроется диалого-

вое окно Мастер сводных таблиц и диаграмм – макет (рисунок 57).

Рисунок 56 – Диалоговое окно Мастер сводных таблиц и диаграмм – шаг 3 из 3

40

Рисунок 57 – Диалоговое окно Мастер сводных таблиц и диаграмм – макет

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

логового окна расположены области строк, столбцов, страниц и данных. Все заго-

ловки (метки) полей таблицы отображаются справа от перечисленных областей.

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

Переместите поле Менеджер в область полей страниц (перетащите при нажа-

той левой кнопке мыши). Таким образом вы зададите размещение данных о каж-

дом менеджере на «отдельной странице». Имена менеджеров будут находиться в поле списка. Для отображения на экране данных о каком-либо менеджере нужно будет выбрать его имя в списке.

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

мостные показатели. В этом случае их следует разместить в поле Данные. При этом отдельные значения будут автоматически просуммированы. В дальнейшем вы сможете изменить вид выполняемой в сводной таблице операции. Поместите поля Дата и Страна в область полей строк, а поле Туроператор в область полей столбцов. Нажав на кнопку ОК, вы завершите создание макета (рисунок 58).

41

Рисунок 58 – Готовый макет

Для изменения параметров сводной таблицы или диаграммы необходимо нажать на кнопку Параметры. Параметры сводной таблицы устанавливаются в окне Параметры сводной таблицы (рисунок 59). Это окно открывается в ре-

зультате активизации команды Параметры из меню Сводная таблица одно-

имённой панели инструментов или при нажатии кнопки Параметры в третьем диалоговом окне мастера сводных таблиц.

Окно Параметры сводной таблицы состоит из двух областей – Формат и Данные. В первой области устанавливаются параметры форматирования и вид сводной таблицы, а во второй – параметры источника данных сводной таблицы.

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

мат – использование параметров автоформата при форматировании таблицы.

Если установлена опция сохранять форматирование, выполненное вами фор-

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

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

42

Рисунок 59 – Диалоговое окно Параметры сводной таблицы

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

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

С помощью опций для ошибок отображать и для пустых ячеек отобра-

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

Опция сохранять данные вместе с таблицей в области Данные предостав-

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