Лабораторная работа № 7.
Подведение итогов. Сводные таблицы.
Перед выполнением работы изучите материалы соответствующего раздела лекций: подведение промежуточных итогов, сводные таблицы, мастер сводных таблиц, структура сводной таблицы.
Запустите Excel и откройте Ваш файлФамилия6-группировка, консолидация.
2. Вставьте новый лист Динамика продаж по филиалу 1 на котором будет отображена информация о продажах товара в течение недели каждым из продавцов.
Т.е. созданный лист должен иметь вид:
На основе введенных данных получите ответ на запрос: Какова общая сумма выручки в течении каждого дня недели?
Для этого необходимо провести сортировку по полю Дата в порядке возрастания с подведением промежуточным Итогов по полю Сумма.
Т.е. выполнить команды:
Данные®Сортировка® Сортировать по-Дата® по возрастанию®ОК.
Данные®Итоги®При каждом изменении в- Дата® Операция - Сумма® Добавить итоги по - Сумма®ОК.
В результате этих действий данные на листе примут вид:
Скройте третий уровень детализации, щелкнув на кнопке 2 левом верхнем углу окна документа. Сравните полученные результаты.
Таким образом, общая выручка за 10.03.2007 составила 6256,3 грн., за 11.03.2007 - 11778 грн. и т.д.
Выполните после этого сортировку по полю Сумма в порядке убывания. Выбрав данные в первой строке, вы получите ответ на запрос: В какой день выручка была наибольшая?
Наибольшая выручка была получена 14.03.2007, она составила 17597,1 грн.
5. Восстановите исходное состояние данных на листе, выполнив команды:
Данные®Итоги®Убрать все.
Получите ответ на запрос: Какова общая сумма выручки в течении недели каждого из продавцов?
Для этого необходимо произвести сортировку по полю Продавец в порядке возрастания с подведением промежуточным Итогов по полю Сумма.
Т.е. выполнить команды:
Данные®Сортировка®Сортировать по-Продавец® по возрастанию®ОК.
Данные®Итоги®При каждом изменении в- Продавец® Операция - Сумма® До бавить итоги по - Сумма®ОК.
В результате данные примут вид:
Таким образом, общая сумма выручки в течении недели для Иванова составила 26070,6 грн., для Петрова – 14515,42 грн. и т.д. Общая сумма выручки по всем продавцам в течении недели – 50273,44 грн.
Товара на большую сумму в течении недели реализовал продавец Иванов.
Выполнив аналогичные действия, получите ответ на запрос: В какой день было продано меньшее количество товаров?
8. Придумайте самостоятельно, какой либо запрос и получите на него ответ.
9. Восстановите исходное состояние листа, разгруппировав строки и удалив промежуточные итоги.
10. Скопируйте содержимое листа Динамика продаж по филиалу 1 на новый лист и озаглавьте его Динамика продаж по филиалу 2. Измените данные о продавцах и некоторые данные о количествах проданных товаров.
11. Ответьте на вопрос : В каком из филиалов за неделю выручка была наибольшая? Т.е. выполните действия пункта 3 для Филиала 2 и сравните полученные Общие итоги.
12. Создайте сводную таблицу, в которой будет отображена информация о товарах, проданных за каждый день в течение недели по каждому из филиалов и о полученной при этом выручке.
Для этого выполните действия:
- на листе Динамика продаж по филиалу 1, удалите итоговые строки;
- отсортируйте Ваш список по возрастанию по полю Дата, внутри одинаковых дат по полю Товар, а затем по Модели;
- активизируйте Мастер сводных таблиц и диаграмм по команде меню Данные ® Сводная таблица;
- в открывшемся диалоговом окне Мастера сводных таблиц и диаграмм - шаг 1 из 3 укажите: тип источника данных – списки в Microsoft Excel, вид создаваемого отчета – сводная таблица;
- щелкните на кнопке Далее;
- в окне Мастера сводных таблиц и диаграмм - шаг 2 из 3 укажите диапазон, содержащий исходные данные, т.е. диапазон ячеек A7:H26;
- щелкните на кнопке Далее;
- в окне Мастера сводных таблиц и диаграмм - шаг 3 из 3 укажите область, куда необходимо поместить сводную таблицу. Установите флажок возле опции Поместить таблицу на новый лист;
- щелкните на кнопке Макет. Откроется диалоговое окно Мастера сводных таблиц и диаграмм – макет;
- сформируйте макет сводной таблицы, перетаскивая кнопки с названиями полей исходной таблицы, в области Строка, Столбец и Данные, т.е. перетащите поля: Дата, Товар, Модель в область строк, поле Сумма в область данных;
- нажать кнопку ОК;
- щелкните на кнопке Готово.
На новом листе появится сводная таблица следующего вида:
Увеличьте ширину столбцов сводной таблицы так, чтобы в них отображалась вся информация.
13. Отобразите в сводной таблице данные только за 10.03.2007.
Для этого выполните действия:
- щелкните на стрелке рядом с названием столбца Дата;
- в открывшемся диалоговом окне уберите флажки возле всех дат, кроме 10.03.2007;
- щелкните OK, в сводной таблице останутся только данные за 10.03.2007.
14.: Аналогичным образом, щелкнув на стрелке рядом с Товаром и сняв соответствующие флажки, оставьте в таблице лишь данные о проданных мониторах.
15. Восстановите сводную таблицу в полном объеме.
16. Измените вид сводной таблицы, изменив расположение кнопок в целевых зонах рабочего листа.
Для этого поле Дата перетащите в область столбцов.
Сводная таблица примет вид:
Оставьте сводную таблицу в понравившемся Вам виде.
17. Переименуйте лист на Сводная 1.
18. Создайте еще одну сводную таблицу, которая будет отображать информацию о выручке каждого продавца в течении недели по каждому из филиалов. Используйте второй способ создания макета таблицы.
Для этого выполните действия:
- скопируйте лист Динамика продаж по филиалу 1 и переименуйте его на Динамика продаж общая;
- перед столбцом Дата вставьте новый столбец Филиал, заполните данные столбца текстом Филиал 1;
- скопируйте на этот же лист данные с листа Динамика продаж по филиалу 2 (без заголовков полей), в столбец Филиал введите соответственно значение Филиал 2;
- выполните команды
Данные®Сводная таблица (выставьте нужные опции) ®Далее (укажите диапазон) ® Далее® на Шаге 3 Мастера сводных таблиц и диаграмм щелкните на кнопке Готово;
- на рабочем листе появится разметка для будущей таблицы и плавающая панель инструментов Сводные таблицы;
- перетащите кнопки полей с плавающей панели инструментов Сводные таблицы: поле Филиал в поле страниц, поле Продавец в поле строк, поле Дата в поле столбцов, поле Сумма в поле данных.
Сводная таблица примет вид:
Щелкнув на кнопке в строке Филиал, можно просмотреть данные по каждому из филиалов.
19. Переименуйте лист на Сводная общая.
20. Перейдите на лист С водная1 и отформатируйте таблицу:
Для этого выполните действия:
- выделите всю таблицу;
- выберите команду Формат ® Автоформат;
- в открывшемся диалоговом окне выберите подходящий формат;
- щелкните OK.
