2018-03-09
191Лабораторная работа №3. Агрегирование данных в MS Excel (2 часа)
Цель работы: освоение работы с автофильтром, расширенным фильтром; знакомство с опцией «Промежуточные итоги»; обучение операциям со сводными таблицами.
Задачи:
- отфильтровать исходные данные с помощью Автофильтра;
- произвести необходимые операции для использования Расширенного фильтра;
- сконструировать сводную таблицу;
- сгруппировать данные в сводной таблице.
Основные сведения
Агрегирование данных состоит в формировании промежуточных итогов, создании сводных таблиц и консолидированных таблиц. Агрегирование в MS Excel выполняется для списков, в записях которых имеются поля с повторяющимися значениями.
При анализе данных иногда полезно использовать вспомогательные таблицы, позволяющие представить информацию выборочно – в виде, наилучшим образом отображающим зависимости между данными. Такие сводные таблицы можно создавать в MicrosoftExcel, который располагает специальным инструментарием для работы с ними.
Пользуясь сводной таблицей, можно анализировать различные группы данных. Скрывая или отображая отдельные группы, изменяя ориентацию строк и столбцов, можно достичь большей прозрачности при анализе данных. Гистограмма или график, построенные на основе сводной таблицы, сделают информацию более наглядной.
Сводная таблица – это таблица, обобщающая и анализирующая данные из одной или нескольких таблиц. Исходные данные для сводной таблицы могут находиться в списке на одном листе рабочей книги, на нескольких листах, во внешней базе данных или в другой сводной таблице. Меняя структуру таблицы, можно получать различные сводные ведомости одних и тех же исходных таблиц.
Поле строки – это поле исходного списка, помещенное в область строчной ориентации сводной таблицы. Значениями поля строки являются значения соответствующего поля в исходном списке.
Поле столбца – это поле исходного списка, помещенное в область столбцов сводной таблицы. Значения для этого поля берутся из соответствующего столбца исходной таблицы.
Поле данных – это поле исходного списка, значения которого являются источником данных для вычислений в сводной таблице. Если поле данных одно, оно находится в области выделения таблицы. Если таких полей несколько, для них отводится отдельная колонка, которая называется Данные.
Область данных – это часть сводной таблицы, содержащая итоговые данные. В ячейках области данных отображаются итоги по полю данных для элементов полей строки и столбца, соответствующих значению поля страницы. Все отображаемые значения соответствуют исходным данным.
Сводные таблицы являются весьма мощным и гибким инструментом анализа и трансформации данных в среде MicrosoftExcel. С его помощью можно сделать практически любой «разрез» таблицы, получить итоги по любым данным. Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как Итоги, Фильтр, Консолидация. С помощью Сводной таблицы можно произвольным образом изменить структуру исходных данных, располагая заголовки строк и столбцов удобным образом, а также получить все необходимые промежуточные итоги и сортировки. Построение сводной таблицы осуществляется с помощью Мастера сводных таблиц, вызываемого командой Вставка / Таблицы / Сводная таблица.
В результате ее выполнения создается настраиваемая таблица для организации полей на листе в новых сочетаниях. Положение всех элементов сводной таблицы пользователь в процессе анализа может изменять, располагая их в желаемом порядке.
Порядок выполнения лабораторной работы
Создайте книгу MS Excel и на листе новой рабочей книги разместите таблицу с рисунка 4.3.
1 Простая сортировка осуществляется через меню «Данные» - «Сортировка» (рисунок 4.1).
Рисунок 4.1 – Диалоговое окно «Сортировка диапазона»
Обязательно присваивайте какие-либо имена всем создаваемым Вами листам, чтобы потом было проще отыскать сделанное Вами задание (для этого щелкните правой кнопкой мыши на название листа, выберите «Переименовать» и присвойте какое-либо название).
2 Копирование данных осуществляется следующим образом: выделяете необходимый диапазон данных, нажимаете правую кнопку мыши находясь на выделенном диапазоне, выбираете пункт «Копировать», перемещаете указатель на нужное место, правая кнопка мыши – «Вставить».
Автофильтр вызывается меню Данные / Сортировка и фильтр / Фильтр. Ограничение на цену выбираете в строке «условие», находясь в автофильтре (рисунок 4.2).
Рисунок 4.2 – Диалоговое окно «Пользовательский автофильтр»
3Для запуска расширенного фильтра сначала необходимо сформировать диапазон условий. Здесь, названия столбцов идентичны названиям столбцов в исходной таблице. Далее вызываете меню Данные / Сортировка и фильтр / Дополнительно и заполняете соответствующие параметры (рисунок 4.3).
4 Далее необходимо отсортировать скопированный диапазон данных простой сортировкой по столбцу «Продавец». Далее, меню Данные / Промежуточные итоги. Заполнение соответствующих параметров представлено на рисунке 4.4.
Рисунок 4.3 – Использование расширенного фильтра
Рисунок 4.4 – Диалоговое окно «Итоги»
5 Далее необходимо отсортировать скопированный диапазон данных простой сортировкой по столбцу «Продукт». Далее меню Данные / Промежуточные итоги. Заполнение соответствующих параметров представлено на рисунке 4.5.
Рисунок 4.5 – Диалоговое окно «Итоги»
6 Далее нужно скопировать исходную таблицу на новый рабочий лист. Выделите весь список и вызовите меню Вставка / Таблицы / Сводная таблица (рисунок 4.6). Макет сводной таблицы оформить согласно рисунку 4.7.
Рисунок 4.6 – Создание сводной таблицы
Рисунок 4.7 – Макет сводной таблицы
В результате должны получить сводную таблицу следующего вида:
Рисунок 4.8 – Первоначальный вариант сводной таблицы
7 Затем нужно внести в макет следующие изменения: поле «Продукт» переместить в область строк, а поле «Цена» добавить в область столбцов (рисунок 4.9).
Рисунок 4.9 – Изменение макета сводной таблицы
8 В этом пункте требуется создать группы по «Продуктам», а именно: группа хлебобулочных изделий (хлеб, булка) и группа молочных продуктов (молоко, кефир). Для этого необходимо выделить в сводной таблице, удерживая нажатой клавишу Ctrl, те продукты, которые должны входить в одну группу. Затем нажать правую кнопку мыши и выбрать «Группа и структура» - «Группировать» (рисунок 4.10).
Появившуюся структуру "Группа 1" нужно переименовать. Аналогично формируется «Группа 2».
9 Цены по интервалам группируются в соответствии с рисунком 4.11. Можно сформировать две и более групп.
Рисунок 4.10 – Использование опции «Группа и структура»
Рисунок 4.11 – Группировка цен по интервалам
10 Далее необходимо скрыть одного из продавцов. Для этого нужно кликнуть по вкладке «Названия строк» и убрать галочку около нужной фамилии (рисунок 4.11).
11 Чтобы выполнить скрытие детализирующих данных по одной из групп продуктов (например, хлебобулочным) достаточно дважды нажать мышкой по названию группы (рисунок 4.12).
Рисунок 4.11 – Скрытие одного из продавцов
Рисунок 4.12 – Сводная таблица после скрытия детализирующих данных по группам
12 Далее снова нужно изменить макет сводной таблицы: в область «Значения» необходимо добавить поле «Продано» и изменить операцию «Сумма по полю Продано» на максимум (рисунок 4.13).
Рисунок 4.13 – Изменение макета сводной таблицы
Изменить операцию «Сумма» по полю «Продано» на операцию «Максимум» по этому же полю можно кликнув левой кнопкой мыши по кнопке поля «Сумма по полю Продано» (рисунок 4.14).
Рисунок 4.14 – Использование опции «Вычисление поля сводной таблицы»
13 Для выполнения последнего пункта задания выделить «Сумма по полю сумма» во всей сводной таблице, затем меню «Формат» - «Ячейки», закладка «Число» - «Денежный». В результате должна получиться таблица, указанная на рисунке 4.15.
Рисунок 4.15 – Конечный вариант сводной таблицы
Контрольные вопросы
1 Для каких целей можно применять Промежуточные итоги в области обработки бухгалтерской информации?
2 Какие операции можно совершать с помощью Расширенного фильтра?
3 Что такое сводная таблица?
4 Как изменить параметры таблицы?
5 Как построить макет таблицы?
