Пример 4.7

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

1. Выделите любую ячейку списка и выполните команду Вставка – Сводная таблица.

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

3. Установите переключатель На существующий лист и введите в поле Диапазон адрес ячейки (верхней левой) будущего диапазона сводной таблицы.

4. Нажмите кнопку ОК.

5. Создайте макет сводной таблицы, перетаскивая поля из области Список полей сводной таблицы и распределяя их по областям (рис. 4.9, 4.10):

• в область Фильтр отчета или в область с текстом Перетащите сюда поля фильтра отчета поместите поле Группа;

• в область Названия строк или в область с текстом Перетащите сюда поля строк – поле ФИО;

• в область Σ Значения или в область с текстом Перетащите сюда поля значений – поля Сумма МЛ и Сумма ИНФ (по умолчанию оба поля будут обработаны функцией Сумма).

Рис. 4.9. Макет будущей сводной таблицы

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

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

Примечание. Если в диалогеСоздание сводной таблицыустановить переключательНа новый лист, то сводная таблица будет помещена на новый рабочий лист, начиная с ячейкиА1 (рис. 4.11).

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

4.2. Варианты заданий для самостоятельной работы

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

Вариант 1

Откройте файл Списки.xls и скопируйте в новую книгу список Филиалы.

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

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

3. С помощью команды Дополнительно выберите из списка записи со следующими данными:

• название филиала начинается на букву «А»;

• отдел – Экономический;

• в поле Оклад значения находятся в диапазоне от 3000 до 4000 рублей.

Результат фильтрации должен содержать все поля списка.

4. Используйте команду Промежуточные итоги для определения суммы окладов по каждой должности.

5. Постройте многостраничную сводную таблицу на отдельном листе (рис. 4.11).

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

Вариант 2

Откройте файл Списки.xls и скопируйте в новую книгу список Продажи.

1. Отсортируйте список по категориям товаров, внутри категорий по наименованию товара, внутри наименований по стоимости товара. Для перечисленных полей задайте возрастающий порядок сортировки.

2. С помощью команды Фильтр выберите из списка записи, которые относятся к краскам фирмы Feidal с ценой не более 650 рублей.

3. С помощью команды Расширенный фильтр выберите из списка записи со следующими данными:

• категория товара – сухие смеси;

• дата продажи – январь 2011 года.

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

4. Используйте команду Промежуточные итоги для определения количества продаж и суммарной стоимости продаж по каждой фирме.

5. Постройте сводную таблицу на отдельном листе (рис. 4.12).

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