Занятие 2. Сводные таблицы , консолидация

1. Скопируйте на рабочий диск файл Задание9(учет).xls

2. Откройте таблицу «Задание9 (Учет).xls.

3. Создайте две копии листа Соискатель, назовите листы Фильтры и Итоги.

4. На листе Итоги подведите промежуточные итоги общей суммы отработанного времени и зара­ботной платы по отделам.

5. На листе Фильтры с помощью автофильтра выберите всех сотрудников, имеющих н/з высшее образова­ние.

6. С помощью автофильтра выберите сотрудников моложе 40 лет.

7. На листе Фильтры с помощью расширенного фильтра выведите всех сотрудников 1 отдела, имеющих разряд выше 13-го в таблицу с заголовками: Фамилия, Разряд, Зарплата.

8. С помощью расширенного фильтра выберите экономистов, получивших зарплату больше 12000 в таб­лицу Фамилия, Отдел, Разряд, Отработано часов, Зарплата.

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

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

· Шаг 1. В открывшемся диалоговом окне Мастер сводных таблиц от­метьте опцию в списке или базе данных Mi­crosoft Excel, а также вид отчета.

· Шаг 2. Далее определите диапазон, с которым будет работать Мас­тер сводных таблиц, включая заголовки столбцов. Щелкните на кнопке Далее.

· Шаг 3. Укажите, куда поместить сводную таблицу (при необходимости можно также изменить Пара­метры) и щелкните по кнопке Макет для вызова пустого макета (рис. 7).

Макет содержит 4 зоны.

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

Строка, Столбец – группируют строки и столбцы для вычисления итогов.

Данные – содержит данные, для которых требуется подведение итогов.

Поля в этой области можно переименовать.

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

· Шаг 4. Определяется лист для размещения сводной таблицы, который переименовывается в лист «Свод­ная таблица».

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

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

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

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

11. Создайте сводную таблицу для анализа средней оплаты за час по отделам и образованию.

12. Создать рабочую книгу «Консолидация»

13. На 1 листе создать таблицу «Данные о наличии товаров на складе» по представленному образцу (Рис.8)

14. Произвести консолидацию данных о наличии товаров на двух складах фирмы по всем полям (суммарная стоимость одноименных товаров);

15. Произвести консолидацию данных о наличии товаров на двух складах фирмы по полям «Поставщик», «Дата поставки», «Стоимость» (суммарная стоимость товаров от одного поставщика);

16. Произвести консолидацию данных о наличии товаров на двух складах фирмы по полям «Дата поставки», «Стоимость» (суммарная стоимость товаров, поставленных в одном месяце).

Данные о наличии товаров на складах фирмы

Склад 1		Склад 2
Наименование товара	Поставщик	Дата поставки	Стоимость (тыс. руб)		Наименование товара	Поставщик	Дата поставки	Стоимость (тыс. руб)
Книги	Киев	мар	55, 0		Видео	Киев	мар	781, 0
Видео	С.-П.	янв	425, 0		Книга	С.-П.	апр	59, 0
Книги	Киев	фев	16, 5		Книга	Москва	май	122, 0
Аудио	Москва	мар	148, 0		Видео	Киев	июн	477, 0
Видео	С.-П.	апр	520, 0		Аудио	Москва	мар	356, 0
Аудио	С.-П.	май	623, 0		Аудио	Москва	апр	533, 0
Книги	Москва	июн	58, 0		Видео	С.-П.	мар	699, 0
Аудио	Москва	янв	132, 7
Видео	Москва	фев	455, 0
Видео	Москва	апр	400, 0

Рис. 8. Консолдидация. Исходные данные

17. Создать таблицу «Расход» по образцу Табл. 2.

Таблица 2

	расход	доход
пн
вт
ср
чт
пт
сб
вс