Задание для самостоятельной работы:

1. Перейдите на лист Данные свод.табл.2. При отсутствии исходного файла вставьте новый лист, назовите его Данные свод.табл.2, заполните электронную таблицу так, как указано в таблице 3.

Таблица 3

Сотрудник	Год приема на работу	Пол	Отдел
Андреева Любовь Павловна	2005	ж	Маркетинг
Бершев Федор Григорьевич	2001	м	Управление
Буденков Денис Алексеевич	2001	м	Склад
Васин Петр Семенович	2005	м	Управление
Голубицкая Елена Максимовна	2001	ж	Маркетинг
Горбатов Илья Егорович	2002	м	Бухгалтерия
Грицацуева Сара Абрамовна	2005	ж	Управление
Зайцев Леонид Петрович	2005	м	Склад
Иванкин Иван Иванович	2002	м	Бухгалтерия
Крылова Анастасия Петровна	2004	ж	Склад
Крылова Ирина Валерьевна	2001	ж	Бухгалтерия
Ноткин Михаил Максимович	2000	м	Бухгалтерия
Петрова Лариса Федоровна	2004	ж	Бухгалтерия
Самойлов Артем Денисович	2003	м	Маркетинг
Сирко Карл Петрович	2000	м	Склад

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

3. Сводная таблица должна иметь вид:

4. Назовите лист Сводн.табл.2.

5. Сохраните файл.

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

Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.

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

• в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;

• во внешнем источнике данных - если данные берутся из внешней базы данных;

• в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;

• в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.

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

Рис. 9.13. Первое диалоговое окно Мастер сводных таблиц

В рассматриваемом примере переключатель устанавливается в положение В списке или базе данных MS Excel.

В поле Диапазон второго диалогового окна Мастер сводных таблиц (рис. 9.14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: 'Поставщики товара'!$A$:$I$18.

В третьем диалоговом окне Мастер сводных таблиц (рис. 9.15) необходимо установить переключатель в положение, указывающее, где будет размещена сводная таблица - на новом листе или на уже существующем.

Рис. 9.14. Второе диалоговое окно Мастер сводных таблиц

Рис. 9.15. Третье диалоговое окно Мастер сводных таблиц

Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.

Поля БД, на основании которой строится сводная таблица, представлены в окне создания макета в виде кнопок с названием этих полей (рис. 9.16). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.

В окне имеются четыре области:

• Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;

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

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

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

Рис. 9.16. Диалоговое окно для создания макета сводной таблицы

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

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

Рис. 9.17. Диалоговое окно Вычисление поля сводной таблицы

Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы (рис. 9.18). Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.

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

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

Рис. 9. 19. Сводная таблица подсчета прибыли по каждому товару