Процедуры «Быстрой сводной» из статьи доступны в !SEMTools Lite и Pro.
Сводная таблица в Excel создаётся так: выделите любую ячейку данных, откройте «Вставка» → «Сводная таблица», нажмите OK и перетащите поля в области «Строки» и «Значения». Сводная это отчёт, который сам группирует строки списка и считает по группам сумму, количество или среднее. Вручную, формулами, то же самое делают СУММЕСЛИ и СЧЁТЕСЛИМН, но по одной группе на формулу, а сводная строит весь отчёт за несколько кликов. Если нужна самая простая свёртка «ключ и сумма» без настройки полей, пригодится процедура «Быстрая сводная».
Что такое сводная таблица и для чего она нужна
Представьте список продаж на тысячу строк: город, товар, сумма. Вопрос «сколько продано по каждому городу» это группировка: все строки одного города собираются вместе, суммы складываются. Сводная таблица делает такую группировку без формул. Исходные данные она не меняет: отчёт живёт отдельно и строится поверх списка.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Город | Товар | Сумма | Город | Сумма | |
| 2 | Москва | Чай | 120 | Казань | 130 | |
| 3 | Казань | Кофе | 80 | Москва | 350 | |
| 4 | Москва | Кофе | 200 | Общий итог | 480 | |
| 5 | Казань | Чай | 50 | |||
| 6 | Москва | Чай | 30 |
Слева исходный список, справа результат: поле «Город» стоит в «Строках», поле «Сумма» в «Значениях» с операцией «Сумма». Города в сводной по умолчанию идут по алфавиту.
Как сделать сводную таблицу по шагам
- Поставьте курсор в любую ячейку списка. Если в нём нет пустых строк и столбцов, Excel сам найдёт границы данных.
- Откройте «Вставка» → «Сводная таблица». В окне проверьте диапазон и выберите, где разместить отчёт: на новом листе или на существующем.
- Нажмите OK. Справа откроется панель «Поля сводной таблицы» со списком столбцов вашего списка.
- Перетащите поле «Город» в область «Строки», а поле «Сумма» в область «Значения». Отчёт появится сразу.
- Чтобы разбить суммы ещё и по товарам, перетащите «Товар» в «Столбцы»: получится таблица «город на товар».
Если поле с числами попало в «Значения» как «Количество по полю…», а нужна сумма, щёлкните по нему в панели → «Параметры полей значений» → выберите «Сумма». Количество вместо суммы Excel ставит, когда в столбце есть текст или пустые ячейки: проверьте, что числа не «числа как текст».
Как подготовить данные для сводной таблицы
- Одна строка заголовков, и у каждого столбца заголовок. Пустой заголовок вызывает ошибку «Имя поля сводной таблицы задано неверно».
- Без пустых строк и столбцов внутри списка, иначе Excel возьмёт только часть данных.
- Без объединённых ячеек: значение должно стоять в каждой строке, а не раз на блок.
- Один столбец это один тип данных. Числа с текстом в одном столбце считаются как текст.
- Лучше превратить список в умную таблицу (Ctrl+T): сводная, построенная на ней, подхватит новые строки при обновлении.
Если данные лежат на нескольких листах, сначала соберите их в один список, как это сделано в статье объединить все листы Excel в один.
Настройка значений: сумма, количество, проценты
Операцию в поле значений меняют так: щёлкните по полю в области «Значения» → «Параметры полей значений». Здесь выбирают сумму, количество, среднее, минимум, максимум. На вкладке «Дополнительные вычисления» есть режимы вроде «% от общей суммы» и «% от суммы по столбцу», они переводят отчёт в доли без дополнительных формул.
| Задача | Операция в сводной |
|---|---|
| Сколько всего продано | Сумма |
| Сколько заказов | Количество |
| Средний чек | Среднее |
| Доля города в общих продажах | Сумма + «% от общей суммы» |
| Сколько разных товаров | Число различных элементов (нужна модель данных) |
«Число различных элементов» появляется, если при создании сводной поставить галочку «Добавить эти данные в модель данных». Подробнее об этом в статье посчитать количество уникальных значений.
Как обновить сводную таблицу
Сводная не пересчитывается сама, когда вы правите исходный список: она хранит снимок данных. Чтобы обновить её, щёлкните по ней правой кнопкой → «Обновить» или нажмите Alt+F5. Кнопка «Обновить всё» на вкладке «Данные» обновляет все сводные книги сразу. Чтобы отчёт обновлялся при открытии файла, откройте «Параметры сводной таблицы» → вкладка «Данные» → галочка «Обновлять при открытии файла».
Если добавили строки под списком, а в отчёте их нет, значит диапазон сводной остался прежним. Измените источник кнопкой «Изменить источник данных» на вкладке «Анализ сводной таблицы» или заранее постройте сводную на умной таблице.
Срезы, вычисляемое поле и ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
- Срез это кнопки-фильтры рядом с отчётом: «Вставка» → «Срез», отметьте поле, и по клику на город отчёт покажет только его.
- Вычисляемое поле позволяет посчитать новый показатель из полей списка, например сумму, умноженную на 0,2. Оно находится на вкладке «Анализ сводной таблицы» в «Поля, элементы и наборы».
- Группировка: щёлкните по дате или числу в строках правой кнопкой → «Группировать», и даты соберутся в месяцы, кварталы и годы.
Чтобы взять число из сводной в обычную формулу, Excel подставляет функцию =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";A3): первый аргумент это имя поля значений, второй любая ячейка внутри сводной. Excel вставляет её сам, если при вводе формулы щёлкнуть мышью по ячейке сводной таблицы.Для ссылки на значение при поиске по ключу проще и надёжнее обычный ВПР.
Быстрая сводная: сумма, мин, макс и сцепка без настройки
Для простых свёрток штатная сводная избыточна. В !SEMTools для этого есть «ИЗМЕНИТЬ» → «Диапазон» → «Быстрая сводная». Выделите два столбца: в первом ключ (город, клиент, номер заказа), во втором значения. Процедура спросит, есть ли в выделении заголовки, построит свёртку и выведет результат на новый лист. Выбирается одна из операций:
- СУММ: сумма значений каждого ключа. Если значений несколько столбцов, они тоже суммируются.
- МИН и МАКС: наименьшее и наибольшее значение ключа. Подходят для выделения ровно двух столбцов.
- Сцепить значения: значения каждого ключа собираются в одну ячейку через выбранный разделитель или построчно, с переносом строки.
- Развернуть по разделителю: обратная операция, ячейка со списком значений разворачивается в строки (см. разделить ячейку на строки).
Ключом считается левый столбец выделения. Перед обработкой пустые ячейки и прочерк «-» в данных заменяются нулями прямо в исходном диапазоне, поэтому для важных данных лучше работать с копией листа. Результат строится на новом листе и не обновляется при правке исходных данных, зато выполняется быстро даже на больших списках. В бесплатной версии доступна часть операций, список смотрите на странице возможности !SEMTools, доступные бесплатно. Все варианты разобраны на странице быстрая сводная таблица.
Когда сводная не нужна: формулы вместо неё
Если нужна одна сумма по условию, быстрее СУММЕСЛИ: =СУММЕСЛИ(A2:A6;"Москва";C2:C6). Для нескольких условий есть СУММЕСЛИМН, для подсчёта строк СЧЁТЕСЛИМН. Формулы обновляются мгновенно, сводную нужно обновлять руками. Зато сводная выигрывает, когда групп много и состав их заранее неизвестен: формулам понадобился бы отдельный список ключей. Если задача только найти повторы, а не свести их, смотрите повторяющиеся значения в Excel.
Почему сводная таблица работает не так, как ждёте
- В отчёте «Количество» вместо суммы. В столбце есть текст или пустые ячейки: исправьте данные и обновите сводную, либо поменяйте операцию в параметрах поля.
- Новые строки не попадают в отчёт. Источник задан жёстко: измените его или постройте сводную на умной таблице.
- Один город записан двумя способами. «Москва» и «Москва » с пробелом считаются разными значениями: уберите лишние пробелы в исходных данных.
- Ошибка «Имя поля сводной таблицы задано неверно». В списке есть столбец без заголовка.
- Даты не группируются. Часть дат записана текстом: преобразуйте в настоящие даты.
Сводные таблицы Excel: частые вопросы
Как создать сводную таблицу в Excel?
Выделите ячейку списка, откройте «Вставка» → «Сводная таблица», нажмите OK и перетащите поля в области «Строки» и «Значения».
Как обновить сводную таблицу?
Правой кнопкой по таблице → «Обновить» или Alt+F5. Чтобы все сводные книги обновились сразу, нажмите «Обновить всё» на вкладке «Данные».
Почему сводная считает количество, а не сумму?
В столбце с числами есть текст или пустые ячейки. Исправьте данные и выберите операцию «Сумма» в параметрах поля значений.
Как быстро посчитать сумму по группам без сводной?
Формулой СУММЕСЛИ по каждой группе или процедурой «Быстрая сводная» из !SEMTools, которая строит свёртку на новом листе.
Как собрать данные с нескольких листов в одну сводную?
Сначала объедините листы в один список, затем стройте сводную по нему.
Свести значения по ключу, собрать их в одну ячейку столбиком или развернуть список обратно в строки: быстрые свёртки !SEMTools делают это без настройки полей и обновления.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: сводная таблица в Excel
Сводная таблица строится через «Вставка» → «Сводная таблица», обновляется вручную по Alt+F5 и нужна, когда групп много и отчёт будут менять. Для разовой суммы хватит СУММЕСЛИ, а для простой свёртки «ключ и значение» подойдёт быстрая сводная. Другие вопросы для начинающих собраны в разделе частые вопросы по Excel.