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

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

Исходными понятиями при создании сводной таблицы являются список Excel, включающий произвольное количество полей (N) и строк (M):

Поле 1	Поле 2	Поле 3	…	Поле N
Значение 11	Значение 12	Значение 13	…	Значение 1N
Значение 21	Значение 22	Значение 23	…	Значение 2N
…	…	…	…	…
Значение M1	Значение M2	Значение M3	…	Значение MN

и макет сводной таблицы:

Фильтр отчета			Столбец
		Строка	Значения

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

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

Дата поставки	Поставщик	Город поставки	Товар	Объем поставки
01.01.03	Иванов	Тула	тушенка
…
02.02.03	Иванов	Тула	тушенка
…
03.03.03	Иванов	Тула	тушенка
…

при макете:

Город поставки			Товар
		Поставщик	Объем поставки

для города поставки Тула, в ячейке области Значений на пересечении строки Иванов и столбца Тушенка будет расположена общая сумма поставок тушенки Ивановым в Тулу по всем датам поставок, т.е. 60.

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

Рассмотрим пример построения сводной таблицы.

Предположим, что путем запроса информации из демонстрационной базы данных MS Access Борей.mdb на листе Excel Данные 2 сформирован список:

включающий 2094 строки. Этот список содержит перечень поставок (с Датой Исполнения) пищевых товаров (Код товара) нескольких Категорий (кондитерские изделия, напитки, рыбопродукты и т.д.) по Цене (за единицу измерения), в Количестве и на Общую сумму (Цена × Количество) в различные Города Получателя, осуществленные сотрудниками (Фамилия) фирмы Борей.

Формирование сводной таблицы

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

Нажимаем кнопку ОК.

На новом листе мы видим пока еще не заполненные области (Строка, Столбец, Значения, Фильтр отчета) будущей сводной таблицы.

Заполним их, отбуксировав справа кнопку поля Фамилия в область Столбец, кнопку поля Категория в область Строка, кнопку поля Общая Сумма в область Значений, кнопку поля Город Получателя в область Фильтр отчета. В результате получим:

Заполнить сводную таблицу также можно, отбуксировав кнопки полей (в данном примере Фамилия, Категория, Общая сумма и др.) из поля 1 в поле 2.

Обратите внимание, что надпись на кнопке, помещенной в область Значения, содержит указание на выбираемую по умолчанию функцию сведения: для числовых полей в качестве такой функции используется функция Сумма, а для прочих типов полей – функция Счет (в русской версии ей соответствует надпись на кнопке «Количество значений»). Двойной щелчок по кнопке поля в одной из областей открывает диалоговое окно, с помощью которого можно настроить параметры этого поля:

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

Ознакомьтесь с готовой сводной таблицей:

Обратите внимание на наличие в сводной таблице итоговых строки (строк) и столбца (столбцов).

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

А путем перебуксировки того же поля (Дата исполнения), но ниже поля Категория и выбора из раскрывающегося списка поля Дата исполнения лишь двух дат (07.07.93 и 08.07.93) получаем уже другую компоновку сводной таблицы:

Отбуксируем из области Строка поле Категория в Список полей сводной таблицы:

Сгруппируем информацию по годам. Для этого:

1. Правый щелчок по ячейке А4;

2. Из контекстного меню выполним команду Группировать;

3. Появилось диалоговое окно:

,

в котором укажем шаг группировки Годы и щелкнем ОК.

После группировки имеем:

Для разгруппирования надо:

1. Правый щелчок по ячейке А4;

2. Из контекстного меню выполнить команду Разгруппировать.