Задание на работу
Лабораторная работа СИСТЕМА УЧЕТА ПРОДАЖ
Цель работы: Изучение методов анализа, систематизации и форматирования данных в Excel, получение навыков формирования и использования сводных таблиц.
Задание на работу
Создайте список, содержащий следующие столбцы:
| Месяц | Продавец | Продукт | Продано, шт. | Цена, руб. | Сумма, руб. |
Столбцы "Месяц" и "Продавец" должны содержать не более трех значений. Например, для "Месяц" – январь, февраль, март. Столбец «Продукт» должен содержать 4 значения. Например, яблоки, бананы, груши, апельсины. Столбец "Сумма" вычисляется по формуле как произведение "Продано" на "Цену".
Список должен содержать 20 строк, не считая метки столбцов.
1. На этом же рабочем листе отсортировать список простой сортировкой сначала по "Месяцу", затем по "Продукту", затем по "Цене". Далее примените к "Месяцу" пользовательский порядок сортировки: март, февраль, январь.
2. Скопируйте список на новый рабочий лист, отфильтруйте его автофильтром так, чтобы отображались данные только для одного продукта при ограничении на цену (ограничение задается в виде неравенства).
3. На следующем рабочем листе выполните расширенный фильтр, наложив условия на столбцы "Месяц" и "Продано", при этом результат скопируйте на другое место. На столбец "Продано" должно быть наложено несколько условий.
4. На следующем рабочем листе подведите итоги по "Месяцам" – выведите сумму по столбцу "Сумма".
5. На следующем рабочем листе подведите итоги по "Продавцам" – выведите сумму по столбцу "Сумма".
6. На следующем рабочем листе подведите итоги по суммарным продажам "Продуктов".
7. По исходному списку постройте на рабочем листе рядом со списком сводную таблицу с макетом, приведенном на рисунке:
8. Поле "Продукт" переместите в строки, поле "Цена" добавьте в столбец.
9. Сгруппируйте Продукты в две группы и переименуйте их.
10. Сгруппируйте цены по интервалам.
11. Из поля "Продавец" скройте одного из Продавцов.
12. Скройте детализирующие данные по одной из групп Продуктов.
13. В область данных добавьте поле "Продано" и измените операцию "Сумма по полю Продано" на "Максимум по полю Продано".
14. Для данных "Сумма по полю Сумма" представьте числа в денежном формате.
15. На основе исходного списка с использованием функций базы данных ДМАКС(), и БИЗВЛЕЧЬ() рассчитайте и сформируйте следующую таблицу:
| январь | февраль | март | ||||
| Сумма, руб. | Продавец | Сумма, руб. | Продавец | Сумма, руб. | Сумма, руб. | |
| Максимальная сумма |
Пример выполнения задания
Рассмотрим выполнение типового варианта задания.
Запустить программу MS Excel. Создайте файл "Система учета продаж.xls"). Переименовать «лист1» рабочей книги в «Исходный список». Создайте список согласно условию задания как показано ниже (рис. 13).
Рис. 13. Таблица с исходными данными
Простую сортировку осуществите через команды меню Данные/ Сортировка / ОК (рис. 14):
Рис. 14. Сортировка таблицы
Установите пользовательский порядок сортировки: март, февраль, январь. Сервис / Параметры / вкладка Списки. Затем установите параметры как на рис. 15.
Рис. 15. Установка пользовательских параметров сортировки
Для выполнения сортировки выделить команду Данные / Сортировка / Параметры / Сортировка по первому ключу / выбрать последовательность в соответствии с рис. 16.
Рис. 16. Задание пользовательских параметров сортировки
Создайте копию листа "Типовой вариант" и назовите его "Автофильтр". Для добавления нового листа выделить команду Вставка / Лист. Выделить лист целиком, щелчком на нулевую ячейку. Затем в листе "Типовой вариант" выделить команду Копировать. Вставьте выделенную таблицу в ячейку А1 листа Автофильтр командой Вставить. Щелкните правой кнопкой мыши на ярлыке нового листа, выберите команду Переименовать и присвойте листу название "Автофильтр".
Для создания автофильтра выделить команды Данные / Фильтр / Автофильтр. Ограничение на цену выбирается в строке условие, находясь в автофильтре (рис. 17).
Рис. 17. Задание пользовательского автофильтра
Создайте копию листа "Типовой вариант" и назовите его "Пользовательский фильтр". Для запуска расширенного фильтра сначала необходимо сформировать диапазон условий (рис. 18) и диапазон вывода.
Рис. 18. Диапазоны условий (критерии выборки) пользовательского фильтра
Названия столбцов должны быть идентичны названиям столбцов в исходной таблице. Выделите команды Данные / Фильтр / Расширенный фильтр и заполняете параметры в соответствии с рис. 19.
Рис. 19. Выполнение пользовательского фильтра
Создать копии листа "Типовой вариант" и назовите их "Итоги - месяцы", "Итоги - продавцы", "Итоги - продукты".
На листе "Итоги - месяцы" выделить команды меню Данные / Итоги. Заполните параметры в соответствии с рис. 20.
Рис. 20. Диалоговое окно Промежуточные итоги
Результат выполнения операции может выглядеть таким, как он показан на рис. 21.
Рис. 21. Результат вычисления промежуточных итогов
На листе "Итоги - продавцы" отсортировать таблицу по полю "Продавец". Аналогично подвести промежуточные итоги на листах "Итоги - продавцы", "Итоги - продукты". Результаты выполнения операции показаны на рис. 22.
Рис. 22. Результаты вычисления промежуточных итогов
Для выполнения шестого задания в листе "Типовой вариант" вызовите меню Данные / Сводная таблица (рис. 23).
Рис. 23. Работа с мастером сводных таблиц
Макет сводной таблицы оформить следующим образом (рис. 24).
Рис. 24. Макет сводной таблицы
В результате вы должны получить сводную таблицу следующего вида (рис. 25):
Рис. 25. Сводная таблица
Полученный лист назовите "Свод-1".
Аналогично создать сводную таблицу «Свод-2» по следующему макету (рис. 26).
Рис. 26. Макет сводной таблицы
Далее требуется создать группы по "Продуктам", а именно: группа импортных фруктов (апельсины, бананы) и группа отечественных фруктов (груши, яблоки). Для этого выделите в сводной таблице "Свод -2", удерживая нажатой клавишу Ctrl, те продукты, которые должны входить в одну группу. Затем нажмите правую кнопку мыши и выберите Группа и структура / Группировать (рис. 26).
Появившуюся структуру "Группа 1" нужно переименовать. Для этого щелкните мышью в ячейку "Группа 1" а затем введите новый текст в строке формул рабочего листа электронной таблицы.
Попробуйте сформировать «Группа 2». В результате выполнения операции группировки будет сформирована таблица, показанная на рис. 27.
Рис. 27. Создание группы в сводной таблице и переименование групп
Цены по интервалам группируются по аналогии с заданием 8. Можно сформировать две и более группы. Вначале создайте новую сводную таблицу "Свод 3" по макету (рис. 28).
Рис. 28. Макет сводной таблицы
Выполните группировку по полю Цена как показано на рис. 29. Результат группировки приведен на рис. 30.
Рис. 29. Группировка поля Цена сводной таблицы
Рис. 30. Сводная таблица «Свод 3»
Чтобы выполнить скрытие детализирующих данных по одной из групп продуктов (например, хлебобулочным) скопируйте лист "Свод-2" на лист "Свод-4". Поместите указатель мыши в ячейку с названием этой группы, нажмите правую кнопку мыши, выберите меню Группа и структура / Скрыть детали. После выполнения задания примерный вид макета и сводной таблицы может быть таким как на рис. 31 и рис. 32.
Рис. 31. Макет сводной таблицы «Свод 4»
Рис. 32. Сводная таблица «Свод 4»
Для выполнения текущего задания сформируйте макет сводной таблицы "Свод-5" следующим образом (рис. 33).
Рис. 33. Макет сводной таблицы «Свод 5»
Измените операцию Сумма по полю "Продано" на операцию Максимум по этому же полю дважды щелкнув левой кнопкой мыши по кнопке поля "Сумма по полю Продано" (рис. 34).
Рис. 34. Изменение операции с полем сводной таблицы
Для выполнения данного пункта задания выделите "Сумма" во всей сводной таблице "Свод-5", затем выделите команды меню Формат / "Ячейки" / закладка Число / Денежный. В результате формируется сводная таблица следующего вида (рис. 35).
Рис. 35. Сводная таблица «Свод 5»
Создайте новый рабочий лист "Функции баз данных" и туда скопируйте лист "Типовой вариант". В ячейках A23:D25 организуйте область критериев (рис. 36). В ячейки А24, С24, А25 и С25 скопируйте содержимое соответствующих ячеек списка, а в ячейки В25 и D25 впишите формулы поиска максимального значения по столбцу "Сумма".
а
б
Рис. 36. Область критериев в режиме представления данных (а) и в режиме задания формул (б)
В ячейках A29:Е31 организуйте область вывода данных. В ячейках, не выделенных цветом, скопируйте соответствующие ячейки списка (рис. 37).
Рис. 37. Область вывода результатов расчетов
В ячейку В31 напишите формулу определения наибольшего числа в столбце списка "Сумма" для января (рис. 38).
Рис. 38. Диалоговое окно функции ДМАКС
В ячейку С31 напишите формулу извлечения фамилии продавца, который совершил максимальную по стоимости сделку в январе месяце (рис. 39).
Рис. 39. Диалоговое окно функции БИЗВЛЕЧЬ
Результат выполнения задания имеет следующий вид (рис. 40).
Рис. 40. Результат выполнения задания
Контрольные вопросы
1. Как выбрать данные с помощью автофильтра?
2. Что такое таблица Критерий в функции ДМАКС?
3. Как получить промежуточные итоги?
4. Что такое Макет при создании Сводной таблицы?
5. Группировка в Сводной таблице.