Лабораторная работа №24 «Сводные таблицы Excel»

1. Цель и задачи лабораторной работы.

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

2. Теоретические сведения.

Основные понятия

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

Исходными понятиями при создании сводной таблицы являются список Excel, включающий произвольное  количество полей (N) и строк (M):

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

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

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

Предположим, что путем запроса информации из демонстрационной базы данных MS Access Борей.mdb на листе Excel Данные 2 сформирован список:

Формирование сводной таблицы

Формирование выполняется с помощью Мастера сводных таблиц и диаграмм, который запускается командой Данные \ Сводные таблицы:

На первом шаге нажимаем кнопку Далее и переходим ко второму шагу:

На втором шаге нажимаем кнопку Далее и переходим к третьему шагу:

Нажимаем кнопку Макет  и видим пока еще не заполненные области (Строка, Столбец, Данные, Страница) будущей сводной таблицы:

Заполним их, отбуксировав справа кнопку поля Фамилия в область Столбец, кнопку поля Категория в область Строка, кнопку поля Общая Сумма в область Данные, кнопку поля Город Получателя в область Страница. В результате получим:

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

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

Завершаем макетирование сводной таблицы щелчком по кнопке ОК, а затем по кнопке Готово для завершения создания сводной таблицы:

Обратите внимание на наличие в сводной таблице итоговых строки (строк) и столбца (столбцов).

При появлении сводной таблицы появилась панель инструментов Сводные таблицы и Список полей сводной таблицы:

Буксировка полей в различные области сводной таблицы позволяет легко трансформировать таблицу. Например, после буксировки поля Дата исполнения в область Строка слева от поля Категория получим следующий вид сводной таблицы:

А путем перебуксировки того же поля (Дата исполнения), но справа от поля Категория и выбора из раскрывающегося списка поля Дата исполнения лишь двух дат (07.07.93 и 08.07.93) получаем уже другую компоновку сводной таблицы:

Отбуксируем из области Строка поле Категория в Список полей сводной таблицы:

Сгруппируем информацию по годам. Для этого:

1. Правый щелчок по ячейке А4,
2. Из контекстного меню выполним команду Группа и структура \ Группировать,
3. Появилось диалоговое окно

 ,

После группировки имеем:

Для разгруппирования надо:

1. Правый щелчок по ячейке А4,
2. Из контекстного меню выполнить команду Группа и структура \ Разгруппировать.

Построение диаграмм и графиков

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

Щелкнем по кнопке Мастер диаграмм панели инструментов Сводные таблицы.

Появился лист Диаграмма 1 для текущего вида сводной таблицы:

Меняя на листе Сводная таблица 1 вид сводной таблицы и переходя на лист Диаграмма 1, Вы всегда будете видеть диаграмму, соответствующую текущему виду сводной таблицы.

Изменим вид диаграммы. Для этого:

1. Перейдем на лист Диаграмма 1 и щелкнем по кнопке Мастер диаграмм на панели инструментов Сводные таблицы. В результате запустится Мастер диаграмм:

Выберем на вкладке Стандартные: Тип: Гистограмма, Вид: [1,1]. Щелкнем кнопку Далее.

2. На третьем шаге Мастера диаграмм на вкладке Заголовки введем Название диаграммы: Объемы продаж (руб.) и название Ось Х: Категории:

Щелкнем кнопку Далее.

3. На четвертом шаге Мастера диаграмм

4. Дважды щелкнем по заголовку диаграммы и в появившемся окне перейдем на вкладку Шрифт:

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

В результате получим следующую диаграмму:

3.Оборудование для лабораторной работы.

Персональный IBM PC — совместимый компьютер, подключенный в одноранговую локальную вычислительную сеть под управлением Windows XP.

4. Порядок выполнения работы.

1. Прочитать п.2 настоящего руководства и выполнить предписанные в нем действия.
2. Закрепить полученные знания, ответив на вопросы для самотестирования.

©2008-2020, Интернет-институт ТулГУ