Урок 8. Сводные таблицы
Сводная таблица — отчёт, который Excel собирает из большой таблицы сам: суммы по менеджерам, количество заказов по месяцам, продажи каждого товара в каждом городе. Формул писать не нужно — достаточно перетащить названия столбцов мышью. Исходные данные при этом не меняются.
Исходные данные
Сводная строится из обычного списка, где каждая строка — одна запись (продажа, заказ, платёж):
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Дата | Менеджер | Товар | Сумма |
| 2 | 03.03.2026 | Анна | Чайник | 120 |
| 3 | 05.03.2026 | Борис | Утюг | 90 |
| 4 | 12.03.2026 | Анна | Утюг | 85 |
| 5 | 18.03.2026 | Вера | Чайник | 130 |
| 6 | 02.04.2026 | Борис | Чайник | 115 |
| 7 | 09.04.2026 | Анна | Чайник | 125 |
| 8 | 15.04.2026 | Вера | Утюг | 105 |
| 9 | 21.04.2026 | Борис | Утюг | 90 |
Требования те же, что для фильтра: одна строка заголовков, у каждого столбца своё непустое название, без пустых строк и объединённых ячеек, в столбце — данные одного вида. Если в столбце «Сумма» попадётся текст, Excel начнёт считать в нём не сумму, а количество.
Лучше сначала сделать из данных умную таблицу (Ctrl+T, урок Сортировка, фильтр и умные таблицы): тогда новые строки сами попадут в сводную при обновлении.
Создаём сводную
- Щёлкните любую ячейку данных.
- «Вставка» → «Сводная таблица».
- Проверьте, что диапазон (или имя таблицы) указан верно, выберите размещение на новом листе и нажмите OK.
Появится пустая сводная таблица, а справа — панель «Поля сводной таблицы»: сверху список столбцов исходной таблицы, снизу четыре области:
| Область | Что туда класть |
|---|---|
| Строки | то, что пойдёт по строкам отчёта: менеджеры, товары, города |
| Столбцы | то, что пойдёт по столбцам |
| Значения | что считать: суммы, количества |
| Фильтры | поле, по которому отбирать данные для всего отчёта |
Перетащите «Менеджер» в «Строки», «Товар» в «Столбцы», «Сумма» в «Значения»:
| Названия строк | Утюг | Чайник | Общий итог |
|---|---|---|---|
| Анна | 85 | 245 | 330 |
| Борис | 180 | 115 | 295 |
| Вера | 105 | 130 | 235 |
| Общий итог | 370 | 490 | 860 |
В углу отчёта будет подпись «Сумма по полю Сумма». Excel сам нашёл всех менеджеров и все товары, сложил продажи на пересечениях и посчитал итоги по строкам и столбцам. Уберите поле из области (перетащите обратно в список или снимите флажок) — отчёт перестроится.
Не только сумма
Числа в «Значениях» по умолчанию складываются. Чтобы считать по-другому, щёлкните поле в области «Значения» и выберите «Параметры поля значений». На первой вкладке выбирается операция: сумма, количество, среднее, максимум, минимум. Например, «Количество» покажет, сколько продаж сделал каждый:
| Названия строк | Утюг | Чайник | Общий итог |
|---|---|---|---|
| Анна | 1 | 2 | 3 |
| Борис | 2 | 1 | 3 |
| Вера | 1 | 1 | 2 |
| Общий итог | 4 | 4 | 8 |
На вкладке «Дополнительные вычисления» («Показать значения как») можно показать доли. Уберите «Товар» из «Столбцов» и выберите для суммы «% от общей суммы»:
| Названия строк | Сумма по полю Сумма |
|---|---|
| Анна | 38,37% |
| Борис | 34,30% |
| Вера | 27,33% |
| Общий итог | 100,00% |
Одно поле можно положить в «Значения» дважды: один раз как сумму, второй — как процент.
Даты: по месяцам
Перетащите «Дата» в «Строки». Современные версии Excel сами группируют даты по месяцам (и годам, если данных много лет). Если даты остались по дням, щёлкните любую дату в сводной правой кнопкой → «Группировать» и отметьте «Месяцы». Для данных из примера получится две строки: март — 425, апрель — 435.
Если сгруппировать не удаётся, в столбце дат есть текст или пустые ячейки: Excel группирует только настоящие даты.
Фильтр отчёта
Поле в области «Фильтры» появляется над отчётом списком. Положите туда «Товар» и выберите «Чайник» — весь отчёт пересчитается только по чайникам. Внутри отчёта у «Названий строк» и «Названий столбцов» тоже есть кнопки фильтра.
Подробности за числом
Дважды щёлкните любое число в сводной — Excel создаст новый лист со всеми строками исходной таблицы, из которых это число сложилось. Удобно проверять, «откуда взялось 245».
Обновление
Сводная таблица не пересчитывается сама, когда меняются исходные данные. После правок щёлкните сводную правой кнопкой и выберите «Обновить». Если сводных несколько — вкладка «Анализ сводной таблицы» → стрелка у кнопки «Обновить» → «Обновить все».
Новые строки, дописанные под обычный диапазон, в сводную не попадут даже после обновления: диапазон источника остался прежним. Поэтому источник лучше делать умной таблицей — она расширяется сама.
В LibreOffice Calc
«Вставить» → «Сводная таблица», затем источник «Выбранное». Области называются «Фильтры», «Поля столбцов», «Поля строк» и «Поля данных», а поля в них перетаскиваются так же.
Коротко
- Сводная таблица считает итоги без формул: «Вставка» → «Сводная таблица».
- Четыре области: «Строки», «Столбцы», «Значения» (что считать), «Фильтры» (отбор для всего отчёта).
- Операция и проценты — «Параметры поля значений»: количество, среднее, «% от общей суммы».
- Даты группируются по месяцам и годам; не группируются — значит, среди них есть текст.
- Двойной щелчок по числу показывает строки, из которых оно сложилось.
- После изменения данных — «Обновить»; источник лучше делать умной таблицей.