Лабораторная работа № 6 по ОИТ. Отчёт лаб6. Лабораторная работа 6 студента группы ит11 Светличного Егора Алексеевича Выполнение Защита
 Скачать 2.76 Mb.
|
|
Лабораторная работа №6 студента группы ИТ-11 Светличного Егора Алексеевича Выполнение: _________ Защита: ___________ Консолидация и анализ данных Цель работы: приобрести практические навыки при обработке данных, расположенных в различных таблицах. Содержание работы Запустить приложение Microsoft Excel и создать с его помощью файл с именем Lab6.xlsx с шестью листами. Первые четыре листа переименовать в Квартал 1, Квартал 2, Квартал 3, Квартал 4 и разместить на них квартальные отчеты о продажах коммерческого предприятия (конкретный вариант 93 см. ниже в таблице). Структура отчета: первый столбец содержит наименования товара (7-8 названий, придумать самостоятельно в зависимости от профиля предприятия), второй, третий, четвертый содержат объемы продаж каждого товара в филиалах предприятия (названия филиалов и конкретные значения придумать самостоятельно), пятый – общий объем продаж каждого товара по всем филиалам. В конце каждого отчета вычисляются итоговые значения по каждому филиалу и по предприятию в целом. Пятый лист назвать Консолидация 1 и разместить на нем годовой отчет фирмы, в котором будут суммироваться объемы продаж товаров по каждому филиалу и по предприятию в целом. Для этого использовать консолидацию по категориям без установления связей с исходными данными. На шестом листе, который назвать Консолидация 2 разместить аналогичный годовой отчет, но с использованием консолидации с установлением связи с исходными данными. Изменить данные в одном из квартальных отчетов и убедиться в изменении данных годового отчета. В отчет по лабораторной работе внести структуру квартального и годового отчетов. Создать файл с именем Lab6а.xlsx. Первый лист переименовать в Исходные данные и разместить на нем данные о продажах товаров (названия взять из предыдущего задания) конкретными продавцами (4-5 фамилий придумать самостоятельно). Исходный список должен иметь следующие поля: ФИО продавца, дата продажи, наименование товара, цена товара, количество, стоимость. В списке должно быть 10-12 записей. На отдельном листе создать сводную таблицы с возможностью фильтрации по дате продажи и вычислению промежуточных итогов по стоимости проданных товаров для каждого продавца и каждого наименования. Изучить самостоятельно и занести в отчет информацию о построении сводных диаграмм. На отдельном листе построить сводную диаграмму на базе созданной таблицы, вид которой определяется вариантом. Схематичный рисунок диаграммы занести в отчет. Используя созданную таблицу по 2-3 датам (выбрать самостоятельно) определить общую стоимость проданных товаров. Также определить общую стоимость товаров, 94 проданных каждым продавцом и товар с наибольшей стоимостью. Внести в отчет полученные значения. В отчет, кроме указанных выше данных внести порядок построения консолидированных отчетов и сводных таблиц, а также примеры использованных формул, вывод. Вариант задания:  Ход работы: Запустил приложение Microsoft Excel и создал с его помощью файл с именем Lab6.xlsx с шестью листами. Первые четыре листа переименовал в Квартал 1, Квартал 2, Квартал 3, Квартал 4. Далее разместил на данных листах квартальные отчёты в соответствии со структурой, указанной в задании (Рис.4). В конце каждого отчета вычислил итоговые значения по каждому филиалу и по предприятию в целом. При создании квартальных отчётов использовал следующие формулы: =СУММ(B2:D2) – формула для вычисления общего объёма продаж в первой ячейке столбца первого квартального отчёта (далее применил автозаполнение до конца столбца). =СУММ(B2:B8) – формула для вычисления итоговых значений объёма продаж первого филиала первого квартального отчёта (далее распространил данную формулу до конца строки с помощью автозаполнения). Пятый лист назвал Консолидация 1 и разместил на нем годовой отчет фирмы, в котором суммируются объемы продаж товаров по каждому филиалу и по предприятию в целом (Рис.2). Использовал консолидацию по категориям без установления связей с исходными данными. Для этого сделал активной левую верхнюю ячейку будущего годового отчёта и во вкладке «Данные», в группе «Работа с данными» нажал кнопку «Консолидация». Появилось следующее диалоговое окно (Рис.1):  Диалоговое окно «Консолидация» В поле со списком «Функция» оставил предлагаемый вариант «Сумма». Далее установил курсор в поле «Ссылка» и указал все области-первоисточники, нажимая при этом каждый раз кнопку «Добавить». Также установил оба переключателя (флажка) в группе «Использовать в качестве имен» и нажал кнопку «ОК». В итоге получил консолидированный отчёт. Шестой лист назвал Консолидация 2 и разместил аналогичный годовой отчет, но с использованием консолидации с установлением связи с исходными данными (Рис.3). Изменил данные в первом квартальном отчёте и убедился в изменении данных годового отчета.  Первый годовой отчёт  Второй годовой отчёт  Структура квартального отчёта (первый отчёт) Создал файл с именем Lab6а.xlsx. Первый лист переименовал в «Исходные данные» и разместил на нем данные о продажах товаров (Рис.5).  Таблица с исходными данными На отдельном листе создал сводную таблицу с возможностью фильтрации по дате продажи и вычислению промежуточных итогов по стоимости проданных товаров для каждого продавца и каждого наименования. Для создания таблицы сделал активной ячейку А1, перешёл ко вкладке «Вставка» и в группе «Таблицы» выбрал «Сводная таблица». Появилось следующее диалоговое окно (Рис.6):  Диалоговое окно «Создание сводной таблицы» Все поля данного диалогового окна оставил неизменными и нажал «ОК». Отобразился макет сводной таблицы и список полей сводной таблицы. Далее из списка полей сводной таблицы перетащил поле «Дата продажи» в область «Фильтр отчета», поле «ФИО продавца» – в область «Названия столбцов», «Наименование товара» – в область «Названия строк» и поле «Стоимость» – в область «Значения». В итоге получилась сводная таблица, соответствующая критериям, указанным в задании (Рис.7).  Сводная таблица Изучил самостоятельно информацию о построении сводных диаграмм. На отдельном листе построил сводную диаграмму на базе созданной таблицы (Рис.9). Для построения диаграммы выделил одну из ячеек нашей сводной таблицы и в контекстной вкладке «Анализ», в группе «Сервис» выбрал пункт «Сводная диаграмма». Появилось следующее диалоговое окно (Рис.8):  Диалоговое окно «Вставка диаграммы» Далее выбрал пункт «Круговая» и вид диаграммы «Кольцевая». После нажатия кнопки «ОК». Появилась сводная диаграмма. Перенёс её на новый лист, увеличил размер и задал название – «Сведения о продаже товаров» (кнопка «Элементы диаграммы», пункт «Название диаграммы»).  Сводная диаграмма «Сведения о продаже товаров» Вернулся к листу со сводной таблицей. В поле с раскрывающимся списком «Дата продажи» установил флажки только на трёх датах (08.11.2018; 16.11.2018; 21.11.2018). Общая стоимость проданных товаров по этим датам составляет 61180 рублей. Общая стоимость товаров, проданных каждым продавцом: Иванов И.П. – 3200 рублей, Петров С.С. – 24990 рублей, Путятин В.В. – 24990 рублей и Сидоров В.А. – 8000 рублей. Товар с наибольшей стоимостью – холодильник, его стоимость составляет 24990 рублей. Вывод: MS Excel продоставляет пользователям широкие возможности обработки данных, расположенных в различных таблицах. Для этого можно использовать консолидацию данных, создание сводных таблиц и построение сводных диаграмм. Приобретённые навыки обработки данных в MS Excel потом пригодятся во многих сферах организации работы, например, при составлении годовых отчётов. |