Решение задач бизнес-анализа средствами аппарата сводных таблиц ms Excel

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

Рассмотрим построение сводной таблицы на примере формирования Ведомости зарплаты за полугодие на основе Ведомостей зарплаты сотрудников за 1-й и 2-й кварталы. Прежде всего необходимо разместить исходные ведомости на одном рабочем листе EXCEL, как этo представлено в таблице 18.⁵

Таблица 18 – Подготовка списка для создания сводной ведомости за полугодие

Ф.И.О.	Всего начислено, руб.	Всего удержано, руб.	Сумма к выдач, руб.е	Отдел	Дата
Иванов И.А.	4 500	487,80	4 012,20	1	1 кв 1998
Малаев В.П.	3 900	441,12	3 458,88	2	1 кв 1998
Климов Ф.Ф. Ф.	4 000	484,60	3 515,40	1	1 кв 1998
Иванов И.А.	7 700	899,96	6 800,04	1	2 кв 1998
Малаев В.П.	6 100	724,48	5 375,52	2	2 кв 1998
Климов Ф.Ф.	4 800	587,64	4 212,36	1	2 кв 1998

В меню Данные следует выбрать команду Сводная таблица, по которой на экран выводится окно Мастер сводных таблиц для задания области обрабатываемых данных, в нашем примере - это весь список.

Н а следующем шаге формируется макет итогового документа из полей исходного списка - макет сводной таблицы, диалоговое окно которого применительно к описанному выше примера приведено на рисунке 35.

Рисунок 35 – Образец подготовки списка для создания сводной ведомости за полугодие в MS Excel

Макет сводной таблицы содержит 4 области (рисунок 7):

• Страница - обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать О-N полей списка;

• Строка, Столбец - обеспечивают группирование строк и столбцов списка для вычисления итогов (О-N полей списка);

• Данные - обязательная область макета; содержит произвольное число полей, не включенных в другие области. Одно и то же поле списка может быть многократно размещено в области данных, если для него нужны разные виды итогов (сумма, среднее значение и т.д.).

Порядок следования полей в областях Страница, Строка, Столбец определяет иерархию группирования данных и формируемых итогов.

На последнем шаге определяется место размещения результатов построения сводной таблицы. Конечный результат формирования Ведомости зарплаты за полугодие приведен в таблице 19.

Таблица 19 – Сводная ведомость зарплаты за полугодие

Ф.И.О.	Данные	Дата
		1 кв 1998	2 кв 1998	Общий итог, руб.
Иванов И.А.	Сумма по полю Всего начислено	4 500,00	7 700,00	12 200,00
	Сумма по полю Всего удержано	487,80	899,96	1 387,76
	Сумма по полю Сумма к выдаче	4 012,20	6 800,04	10 812,24
Климов Ф.Ф.	Сумма по полю Всего начислено	4 000,00	4 800,00	8 800,00
	Сумма по полю Всего удержано	484,60	587,64	1 072,24
	Сумма по полю Сумма к выдаче	3 515,40	4 212,36	7 727,76
Малаев В.П.	Сумма по полю Всего начислено	3 900,00	6 100,00	10 000,00
	Сумма по полю Всего удержано	441,12	724,48	1 165,60
	Сумма по полю Сумма к выдаче	3 458,88	5 375,52	8 834,40
Итог Сумма по полю Всего начислено		12 400,00	18 600,00	31000
Итог Сумма по полю Всего удержано		1413,52	2212,08	3625,6
Итог Сумма по полю Сумма к выдаче		10986,48	16387,92	27374,4

При подведении итогов можно объединять данные в группы в зависимости от типа данных разными способами:

• группировать выделенные элементы по выбранным категориям;

• автоматически группировать числовые элементы;

• автоматически группировать даты и время в более крупные единицы времени, например, дни, месяцы, кварталы, годы.

Можно создавать подгруппы в группах, например, сгруппировать даты по месяцам, а затем месяцы в кварталы.

Для группировки данных следует выделить элементы, которые группируются, и воспользоваться кнопкой Группировка на панели инструментов Сводные таблицы. Например, для объединения сотрудников по отделам и получения дополнительных итогов для предыдущего примера (таблица 18) следует в построенной сводной таблице выделить требуемые фамилии, а затем выполнить группировку описанным способом.

С помощью мастера сводных таблиц можно также консолидировать данные из таблиц, расположенных на разных рабочих листах EXCEL. Непременным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного (или другого типа) диапазона. Например, ведомости зарплаты за каждый квартал 1998 года сформированы на разных рабочих листах EXCEL с именами 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98. Для построения сводной ведомости за весь 1998 год не обязательно переносить их на один рабочий лист EXCEL, а можно воспользоваться средством консолидации мастера сводных таблиц, для чего выполнить следующие действия:

- в команде Сводная таблица установить флажок в нескольких диапазонах консолидации;

- в режиме переключателя Создать одно поле страницы указать диапазон каждой консолидируемой таблицы, включая в него заголовки, кроме итоговых строк и столбцов;

• сформировать макет итогового документа,⁶ выбирая для расчетов требуемые функции.

Таблица 20 - Образец сводной ведомости зарплаты за 1998 год в MS Excel

(Все)
Сумма по полю Значение	Столбец
Ф.И.0.	Начислено	Удержано	К выдаче
Иванов И.А	24310	2763,93	21546,07
КлимовФ.Ф.	20640	2505,43	18134,57
Малаев В.П.	21990	2587,51	19402,49
Общий итог	31000	3625,6	27374,4

Результат формирования Ведомости зарплаты за 1998 год методом консолидации диапазонов, расположенных на листах 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98, приведен на рисунке 38.

По списку (Все) можно получить сведения по каждому кварталу.