Лабораторная робота № 5. Тема. Анализ данных с помощью сводных таблиц.
Цель работы: научиться проводить анализ данных с помощью сводных таблиц в табличном процессоре MS Excel.
Электронные таблицы могут содержать большие группы данных, которые требуют некоторого обобщения и анализа. Для этих целей применяются сводные таблицы, которые позволяют обобщить данные и просмотреть срезы данных по некоторым категориям: по товарным группам, по наименованиям товаров, по поставщикам, по датам, по потребителям, по Ф. И. О., по производителям и т. п.
Большие группы данных, обобщенные по некоторым критериям и преобразованные в удобный для просмотра и анализа табличный вид, называют сводной таблицей. Начальными данными для сводных таблиц могут быть как группы ячеек Excel, так и другие сводные таблицы и базы данных из других файлов. На основе сводных таблиц легко строятся диаграммы. При смене начальных данных, сводные таблицы и соответствующие диаграммы не обновляются. В самой сводной таблице нельзя менять (вводить) данные.
Методические указания по выполнению задания по теме № 5
Пример 5.1.
Подготовьте начальные данные для построения сводной таблицы, или загрузите готовый файл Пример_сводные.xls.
Создайте сводную таблицу общей стоимости поступивших товаров (рис.5.1).
В таблице приведены данные о поступлении товаров на склад в течение сентября – ноября 2016 года.
Внимание!!! Все столбцы имеют заголовки - наименования, которые активно будут использоваться в сводной таблице. Заголовки таблицы не должны содержать объединенных ячеек.
|
|
|
Рисунок 5.1. Ведомость поступления товаров.
1. Перейти на вкладку Вставка.В группе Таблицы нажать на кнопку Сводная таблица (рис.5.2).
Рисунок 5.2. Группа Таблицы на Вкладке Вставка.
2. В открывшемся диалоговом окне Создание сводной таблицы (рис.5.3) в поле Таблица или диапазон указать диапазон ячеек для создание сводной таблицы. В нашем примере это вся область таблицы без нижней строчки ИТОГО, т.е. диапазон ячеек $A$2:$F$43. А также указать, куда следует поместить отчет сводной таблицы. В нашем примере отчет поместим на существующий лист в ячейку B47. Нажимаем на кнопку OK.
Внимание!!! При выделении диапазона, захватываем заголовок таблицы, а нижнюю строку с итогами не берем.
Рисунок 5.3. Диалоговое окно Создание сводной таблицы
3. В правой части окна появится панель Поля сводной таблицы (рис.5.4). Используя технологию перетаскивания перетащить необходимые поля в макет, который расположен внизу под таблицей или в нижней части панели Поля сводной таблицы.
Макет состоит из четырех областей: фильтры, колонны, строки и значения.
Внимание!!! В область значения можно размещать только числовые данные. В остальные области – только текстовые или данные дата/время.
|
|
|
Рисунок 5.4. Панель Поля сводной таблицы
В соответствии с заданием нам необходимо поле Наименование товара и Стоимость (рис. 5.5).
Рисунок 5.5. Панель Поля сводной таблицы (с отмеченными полями)
Сводный отчет будет иметь следующий вид (рис.5.6)
Рисунок 5.6. Итоговый сводный отчет
Пример 5.2.
Создайте сводную таблицу общей стоимости поступивших товаров по поставщикам в разрезе дат.
1. Перейти на вкладку Вставка.В группе Таблицы нажать на кнопку Сводная таблица (рис.5.2).
2. В открывшемся диалоговом окне Создание сводной таблицы (рис.3) в поле Таблица или диапазон указать диапазон ячеек для создание сводной таблицы. В нашем примере это вся область таблицы без нижней строчки ИТОГО, т.е. диапазон ячеек $A$2:$F$43. А также указать, куда следует поместить отчет сводной таблицы.
При выполнении второго задания выберите опцию На новый лист. Нажимаем на кнопку OK.
3. Для конструирования сводной таблицы используем макет, расположенный в нижней части панели Поля сводной таблицы. Перетащим наименования столбцов в соответствии с рис. 5.7
Рисунок 5.7. Макет панели Поля сводной таблицы (с отмеченными полями)
|
|
|
Итоговый отчет будет иметь следующий вид (рис. 5.8)
Рисунок 5.8. Итоговый сводный отчет (задание № 2)
Внимание!!!Если необходимо выбрать операцию для вычисляемого поля, щелкните на нем левой клавишей мыши (рис.5.9). В появившемся списке выбрать команду Параметры полей значений…
Рисунок 5.9. Список команд для поля Значение
Откроется диалоговое окно Параметры поля значений. Выберите нужную операцию (рис.5.10).
Рисунок 5.10. Диалоговое окно Параметры поля значений
Готовая сводная таблица легко реорганизуется простым перетягиванием названий столбцов. Например, поля Наименование товаров и Поставщик можно поменять местами и т.п.
Пример 5.3.
Проанализируйте срезы данных по месяцам, раскрыв поле со списком Дата. Рассмотрите основные кнопки Работы с данными сводной таблицы.
Пример 5.4.
Проанализируйте данные с помощью фильтра.
Выделит любую ячейку исходной таблицы на листе 1 и нажмите на кнопку Сортировка и фильтр на вкладке Главная в группе Редактирование
Дата добавления: 2018-05-12; просмотров: 422; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!