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

1. По каким показателям можно осуществлять подведение промежуточных итогов?

2. Как удалить промежуточные итоги?

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

4. Можно ли расположить итоговый отчет на одном листе с исходными данными?

5.Как добавить вложенные итоги?

Заполните таблицу (заполненная таблица помещается в отчет по вычислительной практике)

Краткое изложение задания 3

Чтобы	Необходимо
Добавить промежуточные итоги
Убрать промежуточные итоги
Добавить вложенные итоги
Скрыть детали структуры
Показать детали структуры
Структурировать рабочий лист, содержащий формулы
Отменить структурирование
Провести консолидацию данных нескольких рабочих листов

Задание 4. Анализ данных с помощью сводных таблиц

Вы научитесь:

• подводить итоги с помощью сводной таблицы;

• изменять структуру данных без перестройки рабочего листа;

• придавать документам профессиональный вид с помощью форматирования;

• строить диаграммы сводных таблиц.

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

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

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

Начало работы

Откройте в папке Мои документы свою папку и выберите файл с именем Задание3.xls, а затем сохраните его под новым именем Задание4.xls.

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

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

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

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

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

Упражнение 1. Составьте сводную таблицу полного ассортимента оргтехники

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

1. На листе Полный ассортимент выделите ячейку А6 и щелкните на кнопке Сводная таблица группы Таблицы вкладки Вставка.Можно выделить и любую другую ячейку базы данных.

Откроется диалоговое окно Создание сводных таблиц (рис. 4.1). Так как сводная таблица создается на основе базы данных Microsoft Excel, то оставьте включенной первую опцию диалога.

Рис. 4.1.

2. Щелкните на кнопке ОК.

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

Рис. 4.2.

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

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

5. Перетащите кнопку поля Товар в область Перетащите сюда поля столбцов макета сводной таблицы. Сведения о ксероксах и факсах будут находиться в разных столбцах сводной таблицы.

6. Перетащите поле Сумма из области задач Список полей сводной таблицы в область Перетащите сюда элементы данных макета сводной таблицы. Внутри каждого вида оргтехники с помощью функции СУММ будет просуммирована выручка от продажи единиц с одинаковым названием (рис. 4.3).

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

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

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

Рис. 4.4 Сводная таблица