Сводная таблица в Excel (эксель): как сделать по шагам

Сводная таблица группирует строки таблицы по категории, например Region, и подводит итог по числу, например Sales, для каждой группы, без формул. Чтобы её создать, щёлкните ячейку в данных, выберите Вставка > Сводная таблица, нажмите «ОК» и перетащите Region в Строки, а Sales в Значения. Таблица ниже не сводная: она строит ту же сводку формулами, чтобы вы видели, как меняются итоги. Формулы в ней можно вводить и по-русски, с точкой с запятой: =СУММЕСЛИ($A$2:$A$9;E2;$C$2:$C$9).

ФормулыСбросить

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММЕСЛИ($A$2:$A$9;E2;$C$2:$C$9)

УНИК перечисляет каждый регион один раз, а СУММЕСЛИ подводит по нему итог: North 455, South 305 и East 170, это 49%, 33% и 18% от общих 930. Замените C3 на 185, и итог South и все три процента сразу изменятся. Сводная таблица показала бы те же числа, но только после обновления.

Как сделать сводную таблицу

Перед началом проверьте исходные данные: одна строка заголовков с названием в каждом столбце, одна запись на строку, без пустых строк и столбцов посередине и без строк промежуточных итогов.

1. Щёлкните любую ячейку в данных.
2. Выберите Вставка > Сводная таблица (в некоторых версиях Вставка > Сводная таблица > Из таблицы или диапазона).
3. Excel подставит диапазон. Выберите На новый лист и нажмите ОК.
4. Появится пустая сводная таблица, а справа панель Поля сводной таблицы со списком заголовков ваших столбцов.
5. Перетащите Region в поле Строки, а Sales в поле Значения. Сводная таблица покажет каждый регион один раз с суммой продаж рядом и строку общего итога.
6. Чтобы изменить то, что показано, перетаскивайте поля между областями или убирайте их из панели.

Если вы не знаете, с чего начать, Вставка > Рекомендуемые сводные таблицы покажет несколько готовых макетов для ваших данных. На Mac меню то же самое: Вставка > Сводная таблица.

Строки, Столбцы, Значения и Фильтры

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

• Строки: категории по левому краю, по строке на каждое различное значение (Region).
• Столбцы: категории по верху, по столбцу на каждое различное значение (Product).
• Значения: числа, которые нужно посчитать для каждого сочетания. Для числового столбца по умолчанию сумма; количество, среднее, максимум, минимум и другое есть в «Параметрах полей значений».
• Фильтры: поле, по которому фильтруется вся сводная таблица; оно показывается как выпадающий список над ней.

С Region в Строках, Product в Столбцах и Sales в Значениях сводная таблица для данных выше выглядит так (в русском Excel подписи будут русскими: «Сумма по полю Sales», «Названия строк», «Общий итог»):

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

Вариант этого макета с формулами перечисляет регионы вниз через УНИК, товары поперёк через ТРАНСП(УНИК()) и считает каждую ячейку сетки одной СУММЕСЛИМН, которая принимает оба списка как условия:

ФормулыСбросить

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММЕСЛИМН(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 разливает North, South и East вниз, F1 разливает Apple и Pear поперёк, а СУММЕСЛИМН в F2 заполняет сетку 3 на 2 между ними: по итогу на каждую пару региона и товара. Замените B5 с Apple на Pear, и изменятся обе ячейки East. Порядок здесь такой, в каком значения впервые встречаются; сводная таблица сортирует подписи по алфавиту.

Количество, среднее или процент вместо суммы

В сводной таблице щёлкните поле в области «Значения» и выберите Параметры полей значений. Вкладка Операция переключает сумму, количество, среднее, максимум и минимум; вкладка Дополнительные вычисления превращает числа в % от общей суммы, % от суммы по столбцу, нарастающий итог и другое. У каждой формулы есть прямой аналог:

ФормулыСбросить

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =УНИК(A2:A9)

У North 3 заказа со средним 151.7, у South 3 со средним 101.7, у East 2 со средним 85.0. Столбец с процентом от общей суммы есть в первой таблице на этой странице.

Отфильтровать сводку по одному товару

Область «Фильтры» ставит над сводной таблицей выпадающий список. Вариант с формулами это ячейка с выпадающим списком и СУММЕСЛИМН, которая добавляет к СУММЕСЛИ ещё одно условие. Выберите товар в F1:

ФормулыСбросить

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

При выбранном Apple North показывает 215, South 150 и East 60. Выберите Pear, и они изменятся на 240, 155 и 110. Другие условия описаны на странице СУММЕСЛИМН, а как добавить список в Excel, на странице выпадающий список.

Обновить сводную таблицу

Сводная таблица хранит копию исходных данных (кэш сводной таблицы) и не пересчитывается, когда меняется ячейка в источнике. После правки данных:

• Щёлкните правой кнопкой в любом месте сводной таблицы и выберите Обновить или нажмите Alt+F5 в Windows.
• Данные > Обновить все (Ctrl+Alt+F5) обновляет все сводные таблицы книги.
• Чтобы обновлять при каждом открытии файла, щёлкните сводную таблицу правой кнопкой, выберите Параметры сводной таблицы и на вкладке Данные отметьте Обновить при открытии файла.

Новые строки, добавленные под исходным диапазоном, не попадают в сводную таблицу даже после обновления. Измените диапазон в Анализ сводной таблицы > Изменить источник данных или, что лучше, превратите источник в таблицу до создания сводной: выделите данные и нажмите Ctrl+T или выберите Вставка > Таблица. Таблица растёт, когда вы добавляете строки, и сводная таблица подхватит их при следующем обновлении.

Итог по всем регионам одной формулой

ФормулыСбросить

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: В F2 одной формулой подведите итог продаж по каждому региону из E2:E4.

ПроверитьПоказать ответ

Ответ разливает 455, 305 и 170. Если передать СУММЕСЛИ весь список E2:E4 как условие, она вернёт по итогу на регион, и протягивать ничего не нужно. В Excel 2019 и старше нет ни УНИК, ни разлива: введите регионы в E2:E4 и протяните вниз =СУММЕСЛИ($A$2:$A$9;E2;$C$2:$C$9). Без знаков $ диапазоны сдвигаются вниз с каждой строкой, и итоги получаются неверными.

ГРУПППО и СВОДПО: сводная таблица одной формулой

В Excel для Microsoft 365 есть две функции, которые строят всю сводку одной формулой и пересчитываются, как любая формула, без обновления. Для них нужна актуальная подписка Microsoft 365. Для данных выше:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

В русском Excel эти формулы записываются как =ГРУПППО(A2:A9;C2:C9;СУММ) и =СВОДПО(A2:A9;B2:B9;C2:C9;СУММ). ГРУПППО (по-английски GROUPBY) принимает поле строк, значения и функцию (СУММ, СЧЁТЗ, СРЗНАЧ, МАКС, PERCENTOF). СВОДПО (по-английски PIVOTBY) добавляет между ними поле столбцов. Обе сортируют подписи и добавляют строки итогов, как сводная таблица.

Сводная таблица или формулы: что выбрать

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