Первая сводная таблица
Каждую пятницу Марина из отдела продаж считает, сколько выручки за неделю принёс каждый менеджер. Заказов за неделю набирается сорок:
Марина фильтрует таблицу по Анне, складывает её заказы и переписывает итог в отдельную табличку. Потом то же для Бориса, Веры и Глеба. Полчаса на четыре числа, и всё заново, если в каком-то заказе нашлась ошибка.
Excel умеет собирать такой отчёт сам. Он называется сводной таблицей: это короткая таблица с итогами, которую Excel считает по длинной.
Тот же отчёт можно собрать формулами СУММЕСЛИ, по одной на менеджера. Но список менеджеров тогда придётся составить самому, а под каждый новый вопрос писать новые формулы. Сводная таблица сама находит всех менеджеров в таблице, а вопрос в ней меняется одной галочкой.
Вопрос превращается в поля
Сводная таблица строится из полей. Поле — это столбец исходной таблицы, а его имя берётся из заголовка: «Менеджер», «Город», «Выручка, ₽».
Поля расставляют в панели «Поля сводной таблицы». В ней список полей с галочками и четыре области: «Фильтры», «Строки», «Столбцы» и «Значения». Для первого отчёта хватит «Строк» и «Значений».
Чтобы собрать отчёт, вопрос делят на две части:
- по чему группировать: «каждый менеджер». Это поле ставят в область «Строки», и у каждого менеджера в отчёте будет своя строка;
- что считать: «выручку». Это поле ставят в «Значения», и Excel сложит выручку внутри каждой строки.
| Менеджер | Город | Категория | Выручка, ₽ |
|---|---|---|---|
| Анна | Москва | Техника | 12 400 |
| Борис | Казань | Одежда | 3 200 |
| Анна | Омск | Техника | 8 700 |
| Вера | Москва | Одежда | 5 100 |
| Вера | Казань | Техника | 4 800 |
| Борис | Москва | Одежда | 2 900 |
| Глеб | Омск | Техника | 15 300 |
| Анна | Казань | Одежда | 6 100 |
| Глеб | Москва | Техника | 7 400 |
| Борис | Омск | Одежда | 3 900 |
| Анна | Москва | Одежда | 4 400 |
| Глеб | Казань | Техника | 9 800 |
| Названия строк | Сумма по полю Выручка, ₽ |
|---|---|
| Анна | 31 600 |
| Борис | 10 000 |
| Вера | 9 900 |
| Глеб | 32 500 |
| Общий итог | 84 000 |
| Названия строк | Сумма по полю Выручка, ₽ |
|---|---|
| Казань | 23 900 |
| Москва | 32 200 |
| Омск | 27 900 |
| Общий итог | 84 000 |
| Названия строк | Сумма по полю Выручка, ₽ |
|---|---|
| Одежда | 25 600 |
| Техника | 58 400 |
| Общий итог | 84 000 |
На схеме одни и те же двенадцать заказов дают три разных отчёта, и меняется только поле в «Строках».
Две галочки вместо формул
Соберите отчёт Марины прямо на её таблице. Выделять её целиком не нужно: щёлкните по любой её ячейке, и Excel сам найдёт, где таблица начинается и кончается. После кнопки «Сводная таблица» он откроет окно «Создание сводной таблицы», где диапазон таблицы и место для отчёта уже подставлены, и останется нажать «ОК». На листе появится пустая рамка будущего отчёта, а рядом панель полей. В ней Excel сам отправит текстовое поле в «Строки», а числовое в «Значения».
Отчёт не следит за таблицей
Сводная таблица запоминает числа в момент построения и сама за таблицей не следит. Если исправить заказ в таблице, отчёт продолжит показывать старый итог. Чтобы он пересчитался, щёлкните по отчёту и на вкладке «Анализ сводной таблицы» нажмите «Обновить».
Сами ячейки отчёта руками не меняют. Неверное число исправляют в исходной таблице, а потом обновляют отчёт. С грязными названиями так же: «Москва» и «Москва » с пробелом в конце сводная считает разными городами и даёт им две строки. Такие названия чистят функцией СЖПРОБЕЛЫ и нажимают «Обновить».
«Обновить» пересчитывает только те строки, по которым отчёт построен. Заказ, дописанный под таблицей, в отчёт не попадёт: чтобы его учесть, сводную строят заново.
Пятничные полчаса Марины сжались до двух галочек. А ошибка в заказе больше не означает «считать всё заново»: хватит исправить число в таблице и нажать «Обновить».