Инструмент Сводная таблица является чрезвычайно мощным и удобным средством анализа. С его помощью можно сделать практически любой “разрез” таблицы, получить итоги по любым данным.

u СВОДНАЯ ТАБЛИЦА

ось столбцов

фильтры

Рис. 1

Инструмент Сводная таблица является чрезвычайно мощным и удобным средством анализа. С его помощью можно сделать практически любой “разрез” таблицы, получить итоги по любым данным.

Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как Итоги, Фильтр, Сортировка. В сводной таблицеможно произвольным образом изменить структуру исходных данных, располагая заголовки строк и столбцов удобным образом, а также получить все необходимые промежуточные итоги и сортировки.

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

Пусть исходные данные для анализа находятся в таблице работы продавцов магазина (рис. 2.). Инструмент Сводная таблица находится во вкладке Вставка > группа Таблицы, но лучше его иконку поместить на панель быстрого доступа.

Создание сводной таблицы. После этой команды открывается окно Создание сводной таблицы (рис. 3). Здесь указывается источник данных А1:D14 и место вывода сводной таблицы. Мы поместим ее на тот же лист с координатой F1 левого верхнего угла. Далее появится важное окно настройки структуры таблицы (рис. 4). Для улучшения обзора, целесообразно мышью растянуть его по вертикали вниз. Это окно имеет несколько разновидностей. Удобное для себя, можно выбрать кнопкой.

	А	В	C	D		Создание сводной таблицы Выберите данные для анализа ––––––––––––––––––––––– ž Выбрать таблицу или диапазон Таблица или диапазон: Лист1!$A$1:$D$14 š Использовать внешний источник данных Выбрать подключение... Имя подключения: Укажите, куда следует поместить отчет сводной таблицы:– š На новый лист ž На существующий лист Диапазон: Лист1!$F1 ОК Отмена Рис. 3
	Имя	Товар	Выручка	Дата
	Петр	стул		01.янв
	Петр	стул		01.янв
	Иван	стол		25.янв
	Олег	шкаф		10.фев
	Петр	стул		12.фев
	Петр	стул		12.фев
	Олег	стол		25.фев
	Олег	шкаф		25.фев
	Петр	стол		02.мар
	Петр	стол		02.мар
	Олег	стул		12.апр
	Иван	шкаф		25.мар
	Иван	стул		12.апр
				Рис. 2

	Список полей сводной таблицы €	Ñ
	Выберите поля для добавления в отчет:
	þ Имя þ Товар þ Выручка þ Дата
	Перетащите поля между указанными ниже областями:
	Фильтр отчета		Название столбцов
			Товар €
	Названия строк		S Значения
	Имя €		Сумма по полю Выр…€
	Дата €
	Отложить обновление макета
	Рис. 4
	Фильтр отчета		Названия столбцов
			Имя €
	Названия строк		S Значения
	Товар €		Число продаж €
	Дата €
	Рис. 5

Определим структуру сводной таблицы. Положим, нам надо создать сводку, где по вертикали будут представлены даты продаж, сгруппированные по продавцам, а по горизонтали суммы выручки для каждого товара. Из списка полей выберем и перетащим поля для их включения в отчет. В столбцах покажем номенклатуру товаров (перетащим сюда поле Товар). В строках – имена продавцов и даты их продаж (поля Имя и Дата). Поле, находящееся в списке выше, является старшим по отношению к следующему. Это означает, что одному значению первого поля (Имя) в сводной таблице будут соответствовать все значения второго (Дата). В самом теле таблицы (S Значения) покажем суммы по полю Выручка. Результат видим на рис. 6. Кнопки

-

/

+

в таблицеоткрывают возможность скрытия/раскрытия детальных данных в группе. Например, щелчок на кнопке для Ивана скроет все его даты. К аналогичному результату ведет двойной клик по имени.

Теперь изменим вид таблицы. Поменяем местами поля (рис. 5) поля Товар и Имя. Кроме того, в качестве значений зададим не сумму выручки, а количество продаж. Для этого щелкнем по кнопке Сумма по полю Выручка (на рис. 4 внизу справа). В возникшем меню выберем пункт Параметры поля значений. Появится окно (рис. 8), где выберем Количество. Кроме этого, дадим собственные, более короткие и удобные имена элементам таблицы. Вместо предлагаемого по умолчанию названия Количество по полю Выручка (см. рис. 8) дадим пользовательское имя Число продаж. Это же можно сделать и непосредственно в таблице, где мы заменим заголовок Общий итог на просто Итог. В результате таблица будет выглядеть, как на рис. 7.

Сводная таблица является не просто отображением данных, но “живым”, управляемым объектом. Здесь действует свое контекстное меню для каждого элемента данных.

Сумма по полю				Общий		Число продаж
Выручка	стол	стул	шкаф	итог			Иван	Олег	Петр	Итог
- Иван						- стол
25.янв						25.янв
25.мар						25.фев
12.апр						02.мар
- Олег						- стул
10.фев						01.янв
25.фев						12.фев
12.апр						12.апр
- Петр						- шкаф
01.янв						10.фев
12.фев						25.фев
02.мар						25.мар
Общий итог						Итог
				Рис. 6					Ри	с. 7	Рис. 8

	Список полей сводной таблицы €	Ñ
	...
	Фильтр отчета		Названия столбцов
	Имя €
	Названия строк		S Значения
	Товар €		Число продаж €
	Дата €
	Рис. 9

			(Все) Иван Олег Петр Выделить несколько элементов ОК Отмена Рис.11
Имя	(Все) €
	Число продаж
стол
25.янв
25.фев
02.мар
стул			Имя	Петр €
01.янв
12.фев				Число продаж
12.апр			стол
шкаф			02.мар
10.фев			стул
25.фев			01.янв
25.мар			12.фев
Итог			Итог
	Рис. 10			Рис. 12

Фильтрация данных. Раздел Фильтр отчета в окне Список полей сводной таблицы (рис. 9) предназначен для выборочного отображаемых содержимого полей. Перенесем сюда поле Имя. Тогда сводная таблица (рис. 7) перестроится (рис. 10). Исчезнут столбцы с именами, но появится строка Имя над ней. Здесь, используя кнопку

€

, можно вызвать меню выбора отображаемых имен (рис. 11) и все сведения в таблице будут относиться только к нему. Если нужно выбрать несколько имен, ставится галочка у пункта Выделить несколько элементов. Тогда у всех имен (на рис. 11) появятся флажки, с помощью которых можно осуществлять множественный выбор. Сейчас мы выберем только Петра. Результат на рис. 12.

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

Группирование данных. Часто бывает нужно видеть сводные данные, не расписанные по конкретным датам, а сгруппированные по месяцам, неделям и т.д. Это легко осуществить, если нажать правую кнопку мыши на любой клетке, содержащей дату. В появившемся меню следует выбрать пункт Группировать. Появится окно Группирование (рис. 14).

В разделе Авто в исходном состоянии оба флага будут установлены и справа от них указаны минимальное и максимальное значения дат, найденных в таблице. Если вы хотите ограничить диапазон сводимых дат, следует ввести новые начальную и конечную даты.

	Список полей сводной таблицы €	Ñ
	Выберите поля для добавления в отчет:
доступ к фильтрам и сортировкам	þ Имя þ Товар þ Выручка
	þ Дата €
	Рис. 13

Список выбора (раздел с шагом) допускает отбор сразу нескольких значений. Если, например, выбрать пункты Годы, Кварталы и Месяцы, то Excel построит иерархическую структуру, где все продажи будут последовательно сгруппированы по этим категориям. Мы выберем Кварталы и Месяцы. Результат на рис. 15.

Переключатель Количество дней позволяет установить группировку по желаемому числу дней, например, по неделям (установить Количество дней: 7). Эта возможность открывается только в случае выбора пункта Дни. Результат применения группировки по неделям показан на рис. 16.

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

Число						Число
продаж	стол	стул	шкаф	Итог		продаж	стол	стул	шкаф	Итог
Иван						Иван
Кв-л1						22.01.2012 - 28.01.2012
янв						25.03.2012 - 31.03.2012
мар						08.04.2012 - 14.04.2012
Кв-л2						Олег
апр						05.02.2012 - 11.02.2012
Олег						19.02.2012 - 25.02.2012
Кв-л1						08.04.2012 - 14.04.2012
фев						Петр
Кв-л2						01.01.2012 - 07.01.2012
апр						12.02.2012 - 18.02.2012
Петр						26.02.2012 - 03.03.2012
Кв-л1						Итог
янв									Ри	с.16
фев
мар
Итог
			Ри	с.15

Рис. 14

17.04.2012

01.01.2012

Секунды

Минуты

Часы

Дни

Месяцы

Кварталы

Годы

с шагом:

выбираем

Месяцы и

Кварталы

количество дней:

начиная с:

по:

ОК

Отмена

Авто

Ñ

Ñ

Группирование

Данный материал только знакомит с возможностями сводных таблиц. Средства управления сводными таблицами весьма разнообразны и, чтобы их освоить, необходимо самостоятельно исследовать все возможности и инструменты, в том числе и содержащиеся на ленте.

Задание. Ниже представлена таблица платежей по трем странам. Указаны: суммы оплат (в тыс. руб.), виды оплаты (наличный/безналичный), ожидаемые даты получения, фактические даты поступления денежных средств.

Произвольным образом дополните таблицу до 20 строк (должны быть три страны и в каждой по два города). Создайте сводный отчет. Выполните все возможные группировки. Создайте вычисляемые поля КАКОЙ - пока не доделано

	A	B	C	D	E	F
	Страна	Город	Сумма	Форма	Дата договора	Дата факт.
	РФ	Курск		нал	05.янв	7.янв
	Украина	Киев		б/нал	18.мар
	Беларусь	Могилев		б/нал	20.апр	19.апр

· Отобрать платежи только для городов Москва и Киев.

· Показать наличные/безналичные платежи по странам.

· Показать суммы платежей по странам, городам и датам.

· Показать суммы помесячных платежей по странам.

· Показать суммы поквартальных платежей по городам.

· Найти максимальные выплаты по кварталам.

Дата добавления: 2015-11-04