Сводные таблицы и диаграммы: отчёт за пару минут

Список превращается в отчёт

Руководитель магазина спрашивает: сколько продал каждый менеджер, и как менялись продажи от марта к апрелю? Можно написать десяток формул СУММЕСЛИ, а можно собрать сводную таблицу — отчёт, который группирует данные перетаскиванием полей.

Сводная работает только с правильным списком — тем самым, о котором шла речь в первом уроке: шапка в первой строке, одна строка на один заказ, без пустых строк и объединённых ячеек. Наша учебная таблица на листе «Заказы» как раз такая.

Вставляется сводная одинаково в обеих программах: выделите любую ячейку списка и выберите в меню «Вставка» пункт «Сводная таблица». Программа предложит диапазон данных и место для отчёта — выберите новый лист.

Четыре зоны

Справа появится панель с полями — это заголовки столбцов списка — и четырьмя зонами:

• Строки — по какому признаку разложить отчёт сверху вниз;
• Столбцы — вторая разбивка, слева направо;
• Значения — что посчитать;
• Фильтры — что отобрать для всего отчёта.

Поставим «Менеджер» в строки, а «Сумма» — в значения:

Менеджер	Сумма по полю Сумма
Иван	3700
Ольга	4170
Пётр	2840
Общий итог	10710

Два перетаскивания — и отчёт готов. Числа совпадают с СУММЕСЛИ из урока 5, только формул писать не пришлось. Если в столбцы поставить «Категория», у каждого менеджера появятся отдельные суммы по бумажным и письменным товарам.

Способ подсчёта

Числовое поле в значениях по умолчанию суммируется, текстовое — считается количеством. Способ меняют в настройках поля значений:

Вопрос	Способ подсчёта
Сколько продали?	Сумма
Сколько было заказов?	Количество
Какой средний заказ?	Среднее
Какой самый крупный заказ?	Максимум

С количеством отчёт отвечает на другой вопрос: у Ольги 4 заказа, у Ивана и Петра — по 3. Поэтому перед тем как показывать отчёт, проверьте, какой способ подсчёта стоит в значениях.

Там же можно показать значения как долю от общей суммы — тогда вместо рублей будут проценты: у Ольги около 39% всех продаж.

Группировка дат

Поставим «Дата» в столбцы — по датам получится по столбцу на каждый день, а нужны месяцы. Даты в сводной группируют:

• Excel обычно сам собирает даты в месяцы, кварталы и годы. Если нет — щёлкните правой кнопкой по любой дате в отчёте и выберите группировку по месяцам.
• Google Таблицы: правой кнопкой по дате в отчёте → создать группу дат сводной таблицы → месяц.

Менеджер	Март	Апрель	Общий итог
Иван	1700	2000	3700
Ольга	3030	1140	4170
Пётр	750	2090	2840
Общий итог	5480	5230	10710

Подписи месяцев в программах могут выглядеть по-разному — «мар», «Март» или «3», — смысл один. Теперь видно то, чего не было в общих суммах: Ольга лидировала в марте, а в апреле её продажи упали почти втрое.

Обновление: здесь программы различаются

• Excel держит в сводной снимок данных. Исправили заказ — отчёт покажет прежние числа, пока вы не выполните для сводной команду «Обновить», например через правую кнопку мыши. Это самая частая причина расхождений между списком и отчётом.
• Google Таблицы пересчитывают сводную сами, как только меняются данные в исходном диапазоне.

В обеих программах есть общая ловушка: строки, добавленные ниже исходного диапазона, в отчёт не попадут. Диапазон источника тогда расширяют в настройках сводной. В Excel от этой проблемы спасает «умная таблица»: выделите список и нажмите Ctrl+T — новые строки будут попадать в неё сами.

Диаграмма по итогам

Числа показывают точные значения, а диаграмма — соотношения с первого взгляда. Выделите отчёт и выберите в меню «Вставка» диаграмму. Тип выбирают под вопрос:

• Столбчатая — сравнить группы: продажи менеджеров.
• Линейная — показать изменение во времени: продажи по месяцам за год.
• Круговая — показать доли одного целого, если частей немного, до пяти: бумажные и письменные товары.

Перед тем как показывать диаграмму людям, проверьте три вещи: у неё есть название, у осей подписаны единицы — «рубли», «штуки», — а столбцы начинаются с нуля. Иначе разница в 10% может выглядеть как разница в разы.