Cводные таблицы — мощный и очень удобный инструмент Excel для анализа больших объёмов данных. С помощью таблиц можно легко получать сводные отчёты, видоизменяя и настраивая их несколькими щелчками мыши. Рассмотрим сразу на практическом примере.
Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:
- Дата совершения продажи;
- Наименование товара;
- Наименование покупателя товара;
- Сумма сделки.
Относительно этих данных может возникнуть множество вопросов:
- Какая общая сумма продаж?
- Кто самый активный покупатель?
- Какой самый популярный товар по общей сумме сделки?
- Как распределены продажи в течение года, есть ли сезонность у товаров?
- Растут или падают продажи в течение нескольких лет?
На все эти вопросы помогают ответить сводные таблицы.
Обратите внимание на вспомогательные столбцы Год, Месяц, День листа данные в файле с примером. В этих столбцах хранятся соответствующие значения даты, которые будут нужны для некоторых видов отчётов.
Создание сводной таблицы
Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае — вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.
Поля сводной таблицы
На новом листе слева появилась заготовка сводной таблицы, справа окно со списком полей и четырьмя окошками: фильтр отчёта, названия столбцов, названия строк, суммарные значения.
Напомним, сводная таблица должна давать ответы на поставленные вопросы. Например, ответим на три первых вопроса: о сумме продаж, о самом активном покупателе и самом популярном товаре. Для этого нужно отметить в окне справа поля Наименование товара, Покупатель, Сумма. Программа разместит поле Сумма в окошко Суммарные значения (в самом низу справа), а остальные два поля — в окошко Названия строк. Перетащите одно из полей в окошко Названия столбцов. Получится примерно так:
Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог — покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка — большую кнопку Сортировка, в открывшемся окне — Параметры сортировки — По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.
Как правило, данные анализируются за определённый период или несколько периодов. Структурируем данные по временным периодам. Например, можно узнать динамику продаж продуктов по годам. Для этого нужно отметить поле Год и перенести это поле в окошко Названия строк, убрав оттуда поле Покупатель обратно в окно списка полей. Получится наглядная таблица, данные которой удобно использовать для построения графика.
Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.
Скачать пример сводных таблиц Excel: svodnie-tablici
Практический пример со сводными таблицами: Экспорт данных из 1С в Excel на примере отчёта о движении денежных средств
Предыдущая глава: Глава 6. Основные функции Excel
Содержание учебника: Содержание
