ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel (эксель): сумма видимых ячеек

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C8) складывает числа в C2:C8, как СУММ, но с двумя отличиями: пропускает другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ внутри диапазона и пропускает строки, скрытые фильтром. Первый аргумент, 9, говорит, какое вычисление выполнить. По-английски эта функция называется SUBTOTAL; в таблице ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.

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

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C7)

Общий итог в C8 охватывает весь столбец вместе со строками промежуточных итогов и всё равно показывает 445: ПРОМЕЖУТОЧНЫЕ.ИТОГИ не учитывает C4 и C7, потому что в них формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ. F2 делает то же через СУММ и показывает 890, каждая продажа посчитана дважды. Если в каждой итоговой строке стоит ПРОМЕЖУТОЧНЫЕ.ИТОГИ, группы можно добавлять и переносить, не переписывая общий итог.

Номера функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ

=SUBTOTAL(function_num, ref1, [ref2], ...)

Когда вы вводите =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(, Excel показывает этот список, поэтому запоминать его не нужно. Чаще всего используются 9 и 109 (СУММ), 1 (СРЗНАЧ) и 103 (подсчёт видимых строк).

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

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;C2:C7)

Здесь ничего не скрыто, поэтому каждая строка равна обычной функции: среднее 88,33, 6 строк, МАКС 200, МИН 30 и СУММ 530. Разница видна только тогда, когда строки скрыты, и об этом следующий раздел.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ 9 и 109, отфильтрованные строки

Включите фильтр через Данные > Фильтр (Ctrl+Shift+L, на Mac Cmd+Shift+F) и выберите North в раскрывающемся списке Region. Строки других регионов скроются:

• =СУММ(C2:C7) по-прежнему складывает все шесть строк.
• =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C7) и =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;C2:C7) складывают только видимые строки North.
• =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A2:A7) считает строки, оставшиеся на экране: 2, то же число, что в строке состояния «Найдено записей: 2 из 6».

Два семейства различаются только для строк, скрытых вручную (выделите строки, правый щелчок > Скрыть). 9 их по-прежнему складывает, 109 нет. Если итог всегда должен совпадать с тем, что на экране, используйте 109. Если вы скрываете строки только ради порядка и хотите оставить их в итоге, используйте 9.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только со строками. Скрытые столбцы учитываются всегда, поэтому =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2) вдоль строки складывает и скрытые столбцы.

Быстрее всего получить ПРОМЕЖУТОЧНЫЕ.ИТОГИ кнопкой «Автосумма» при включённом фильтре: Excel напишет =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;...) вместо СУММ. Данные > Промежуточный итог идёт дальше: в списке, отсортированном по столбцу, команда вставляет итоговую строку под каждой группой и общий итог, все через ПРОМЕЖУТОЧНЫЕ.ИТОГИ, плюс кнопки структуры, чтобы сворачивать группы.

АГРЕГАТ: ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которые умеют пропускать ошибки

Если в одной ячейке диапазона ошибка, СУММ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращают эту ошибку. АГРЕГАТ (по-английски AGGREGATE, Excel 2010 и новее) это ПРОМЕЖУТОЧНЫЕ.ИТОГИ с дополнительным аргументом параметров; параметр 6 пропускает значения ошибок.

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

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =АГРЕГАТ(9;6;C2:C7)

В C3 стоит #N/A (в русском Excel #Н/Д; таблицы на этой странице показывают ошибки под английскими именами), поэтому F2 тоже показывает #N/A. F3 её пропускает и складывает остальные пять: 450. Первый аргумент у неё использует те же номера, что и ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9 это СУММ, 4 это МАКС). Другие параметры: 5 пропускает скрытые строки, 7 пропускает скрытые строки и ошибки, 3 пропускает скрытые строки, ошибки и вложенные формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ. Замените C3 числом, и F2 покажет тот же итог, что и F3.

Практика: общий итог поверх промежуточных

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

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

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

Ваша очередь: Под каждым регионом в списке стоит промежуточный итог. Поставьте в C9 общий итог по C2:C8, который не считает строки промежуточных итогов дважды.

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

Почему итог с ПРОМЕЖУТОЧНЫЕ.ИТОГИ всё равно неверный

• Итоги групп сделаны через СУММ. ПРОМЕЖУТОЧНЫЕ.ИТОГИ пропускает внутри своего диапазона другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но не формулы СУММ. Итог группы, записанный как =СУММ(C2:C3), посчитается ещё раз. Замените формулу в каждой итоговой строке на ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
• Строки скрыты вручную, а номер функции 9. Используйте 109.
• Данные расположены по столбцам, а не по строкам. Скрытые столбцы никогда не пропускаются.
• Нужно условие, а не фильтр. ПРОМЕЖУТОЧНЫЕ.ИТОГИ следует за тем, что скрывает фильтр. Чтобы получить итог по North без фильтра, используйте СУММЕСЛИ. Для сводки по всем группам сразу сводная таблица обходится без итоговых строк в данных.