Лабораторная работа № 3 агрегирование данных в ms excel

Агрегирование данных состоит в формировании промежуточных итогов, создании сводных таблиц и консолидированных таблиц. Агрегирование в MS Excel выполняется для списков, в записях которых имеются поля с повторяющимися значениями.

Задание

Имеются данные о поступлении товаров на склад:

Дата	Поставщик	Наименование товара	Ед. изм.	Цена, руб.	Количество	Сумма, руб.
01.02	ООО «Интра-Ф»	Макароны в/с	кг	10	100
01.02	ООО «Интра-Ф»	Чай Липтон	пач	12	300
01.02	ЧП Иванов С.А.	Макароны в/с	кг	6	20
01.02	ЗАО «Хлеб»	Сдоба тульская	шт	4,3	35
02.02	ООО «Интра-Ф»	Макароны в/с	кг	9,5	60
02.02	ЗАО «Хлеб»	Макароны в/с	кг	8	20
02.02	ЗАО «Хлеб»	Сдоба тульская	шт	4,3	25
02.02	ЧП Иванов С.А.	Сдоба тульская	шт	4	30

Провести анализ поступления товаров от каждого поставщика в количественном и стоимостном выражение по датам.

Порядок выполнения работы

Для выполнения этого задания удобно воспользоваться сводной таблицей.

1   Разработать макет таблицы, занести исходные данные и расчетные формулы в ячейки электронной таблицы.

2   Для агрегирования данных выбрать пункт меню Данные → Сводная таблица…

Рис. 10   Форматирование таблицы и ввод исходных данных

3   На первом шаге работы мастера сводных таблиц необходимо указать источник данных, которым может являться список или база данных MS Excel, внешний источник данных, диапазоны консолидации или другая сводная таблица. Также надо указать вид отчета: сводная таблица или сводная диаграмма. В нашем примере источник данных – список MS Excel, вид отчета – сводная таблица.

4   Для перехода к следующему шагу работы мастера нажать кнопку «Далее >».

5   На втором шаге работы мастера требуется указать диапазон, содержащий исходные данные. Диапазон можно задать в строке ввода с клавиатуры, указав адрес левой верхней ячейки и через двоеточие – адрес правой нижней ячейки диапазона, можно выделить диапазон с помощью мыши непосредственно в таблице. Если окно мастера мешает выделению его можно свернуть, нажав на кнопку «Свернуть окно» рядом с полем ввода, а затем восстановить окно мастера при помощи кнопки «Развернуть окно». Если данные находятся в другом файле нужно предварительно открыть его, используя кнопку «Обзор…», расположенную на окне мастера. В нашем примере надо задать диапазон A1:G9, т. е. всю рабочую область листа. После того, как диапазон задан, перейти к следующему шагу, нажав на кнопку «Далее >».

6   На последнем шаге работы мастера необходимо указать, где будет размещена сводная таблица – на новом листе книги или на существующем листе. Если сводная таблица размещается на существующем листе, то в поле ввод требуется задать ссылку на ячейку, с позиции которой будет вставлена таблица. Для нашего примера разместим таблицу на новом листе.

7   Также на третьем шаге работы мастера можно создать макет сводной таблицы. Для этого надо нажать кнопку «Макет…» в окне мастера.

8   Для формирования макета необходимо перетащить кнопки полей в области сводной таблицы. Для нашего примера: в область страниц перетащим дату, в строки таблицы – наименование товара, в столбцы – поставщика, в область данных – количество и сумму (по их значениям будет проводиться агрегирование, по умолчанию – суммирование).

Рис. 11   Макетирование сводной таблицы

9   Для завершения работы мастера нажать кнопку «Готово». В результате на новом листе рабочей книги исходные данные будут агрегированы в форме сводной таблицы.

10   Для управления сводной таблицей можно воспользоваться кнопками панели инструментов «Сводные таблицы». Если она отсутствует на экране, необходимо выбрать пункт меню Вид → Панели инструментов → Сводные таблицы.

11   Если требуется изменить операцию агрегирования данных в сводной таблице надо установить курсор в поле данных для которого изменяется операция и нажать кнопку «Параметры поля» на панели инструментов сводной таблицы. Будет открыто окно вычисления полей сводной таблицы.

12   В этом окне надо выбрать необходимую операцию агрегирования (например, количество значений, среднее значение и т. д.) и нажать кнопку ОК.

13   Для отображения агрегированных данных по конкретной дате нажать на кнопку списка рядом с полем «Дата» (область страницы сводной таблицы) и выбрать интересующую дату.

14   Можно скрыть отдельные строки и столбцы сводной таблицы. Для этого надо нажать на кнопку списка в заголовочной части столбцов или строк сводной таблицы и снять пометки выделения для скрываемых данных в появившемся списке.

15   Если исходные данные в списке были изменены, то для обновления значений в сводной таблице надо нажать кнопку «Обновить данные».

Рис. 12   Управление сводной таблицей

16   Сохранить результаты в файле, выбрав пункт меню Файл → Сохранить и указав имя файла.