Сводные таблицы в Excel
Сводная сворачивает длинный список операций в короткий отчёт по строкам и столбцам. Как построить её за пять кликов и чем заменить формулой, когда сводная не нужна или отчёт уйдёт в Google Таблицы.
Коротко
Сводная таблица берёт длинный список операций — строка на каждую продажу — и сворачивает его в компактный отчёт: что положить в строки, что в столбцы, какое число считать в пересечениях. Регион в строки, товар в столбцы, сумму в значения — и на экране матрица «сколько каждого товара продано в каждом регионе», без единой формулы.
В файле-песочнице лежит чистый набор на 180 строк: столбцы Дата, Регион, Менеджер, Товар, Количество, Цена, Сумма (Сумма = Количество × Цена). Четыре региона — Москва, Санкт-Петербург, Екатеринбург, Новосибирск; пять товаров — «Стол Loft», «Лампа Lumo», «Кресло Ergo», «Полка Nord», «Шкаф Kvadrat»; четыре менеджера. Данные занимают строки 7–186, столбцы B–H: на них и собирают сводную.
Сводная нужна, когда разрезов много и они меняются: перетащил поле — и отчёт перестроился. Но ради одного числа сводную не строят, и в Google Таблицы сводная из Excel не переносится. На такой случай тот же итог даёт формула СУММЕСЛИМН — она ниже.
То же самое формулой в Excel
=СУММЕСЛИМН($H$7:$H$186;$C$7:$C$186;"Москва";$E$7:$E$186;"Стол Loft") — сумма по региону и товару — это ровно одна ячейка сводной, но формулой=СУММЕСЛИМН($H$7:$H$186;$C$7:$C$186;"Москва") — итог по одному региону: условие оставили только одно=СЧЁТЕСЛИ($E$7:$E$186;"Стол Loft") — сколько строк по товару — счёт продаж, а не их сумма=СУММ($H$7:$H$186) — общая сумма по всему набору — контрольная цифра, с ней сверяют своднуюЧистый набор на 180 строк для тренировки сводных
Готовые данные без единого изъяна: 180 продаж по четырём регионам, пяти товарам и четырём менеджерам, столбцы Дата, Регион, Менеджер, Товар, Количество, Цена, Сумма. Ни пустых строк, ни объединённых ячеек, ни подытогов внутри — то, с чего сводная строится с первого раза. Стройте свою сводную поверх или разбирайте формулы СУММЕСЛИМН рядом.
Файл
svodnaya-tablitsa-dannye.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Встаньте в любую ячейку внутри данных — Excel сам возьмёт весь диапазон до пустой строки и пустого столбца. Выделять таблицу руками не нужно.
- Вкладка Вставка → Сводная таблица → OK. Excel создаст новый лист с пустой сеткой и панелью полей справа.
- В панели полей перетащите Регион в область Строки, Товар — в Столбцы, Сумма — в Значения. В сетке появится матрица: регионы по строкам, товары по столбцам, суммы в пересечениях.
- Если разрез нужен по одному менеджеру, перетащите Менеджер в область Фильтры — над сводной появится выпадающий список, и отчёт покажет только выбранного.
- Данные изменились или добавились строки — сводная сама не пересчитается. Щёлкните по ней правой кнопкой → Обновить. Это и есть главное отличие сводной от формулы.
Где обычно ломается
Три вещи, на которых сводная подводит чаще всего.
- Сводной нужен чистый набор. Пустая строка, объединённая ячейка или строка подытога внутри данных — и сводная либо обрывает диапазон на пустоте, либо считает подытог как ещё одну продажу. Данные должны быть простым списком: одна строка — одна операция, шапка в один ряд, никаких «Итого» между строками. Сначала чистый список, потом сводная поверх него.
- Сводная не пересчитывается сама. Поправили цену в исходных данных — цифры в сводной остались прежними. Каждый раз после правки: правая кнопка → Обновить. А если строк добавилось за пределы прежнего диапазона, обновления мало — надо расширить источник в «Анализ сводной таблицы → Источник данных». Формула
СУММЕСЛИМНэтого не требует: пересчитывается сразу. - В Google Таблицы сводная из Excel не переносится. При импорте
.xlsxсама таблица данных придёт, а построенная сводная — нет, её собирают заново: Данные → Сводная таблица. Логика та же (строки, столбцы, значения), но кнопки другие. Если отчёт заведомо поедет в Google Таблицы, надёжнее считать формулами — они переживают импорт без потерь.