Формула промежуточные итоги в excel примеры: Промежуточные итоги в Excel с примерами функций

Excel функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) — Info Tech Steps

В этом учебном материале вы узнаете, как использовать Excel функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ с синтаксисом и примерами.

Описание

Microsoft Excel функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает промежуточный итог чисел в столбце в списке или базе данных. ПРОМЕЖУТОЧНЫЕ.ИТОГИ — это встроенная в Excel функция, которая относится к категории математических / тригонометрических функций.
Её можно использовать как функцию рабочего листа (WS) в Excel.
В качестве функции рабочего листа функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…])

Аргументы или параметры

номер_функции

Тип создаваемого промежуточного итога, но будьте осторожны при выборе номер_функции. номер_функции может быть значением от 1 до 11, которое включает скрытые значения, или значением от 101 до 111, которое игнорирует скрытые значения при вычислении.

Значение	Пояснение (включает скрытые значения)
1	СРЗНАЧ
2	СЧЁТ
3	СЧЁТЗ
4	МАКС
5	МИН
6	ПРОИЗВЕД
7	СТАНДОТКЛОН
8	СТАНДОТКЛОНП
9	СУММ
10	ДИСП
11	ДИСПР

Значение	Пояснение (игнорирует скрытые значения)
101	СРЗНАЧ
102	СЧЁТ
103	СЧЁТЗ
104	МАКС
105	МИН
106	ПРОИЗВЕД
107	СТАНДОТКЛОН
108	СТАНДОТКЛОНП
109	СУММ
110	ДИСП
111	ДИСПР

ссылка1;[ссылка2];…

Диапазоны ячеек, для которых вы хотите подвести промежуточный итог.

Возвращаемое значение

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает числовое значение.

Применение

• Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

• Функция рабочего листа (WS)

Пример (как функция рабочего листа)

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; D2:D5) Результат: 20.2575 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2; D2:D5) Результат: 4 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3; D2:D5) Результат: 4 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4; D2:D5) Результат: 35.88 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(5; D2:D5) Результат: 6. 4 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(6; D2:D5) Результат: 86112 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(7; D2:D5) Результат: 12.08899879 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(8; D2:D5) Результат: 10.46938006 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; D2:D5) Результат: 81.03 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(10; D2:D5) Результат: 146.1438917 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(11; D2:D5) Результат: 109.6079188

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ Excel с примерами формул

В учебном пособии объясняются особенности функции ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel и показано, как использовать формулы промежуточного итога для суммирования данных в видимых ячейках.

В предыдущей статье мы обсуждали автоматический способ вставки промежуточных итогов в Excel с помощью функции «Промежуточные итоги». Сегодня вы узнаете, как самостоятельно писать формулы промежуточных итогов и какие преимущества это вам дает.

• Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel — синтаксис и использование
• 3 основные причины использовать функцию ПРОМЕЖУТОЧНЫЕ ИТОГО в Excel
• Примеры формулы промежуточного итога
• Excel ПРОМЕЖУТОЧНЫЙ ИТОГ не работает

Функция промежуточного итога Excel — синтаксис и использование

Microsoft определяет ПРОМЕЖУТОЧНЫЙ ИТОГ Excel как функцию, которая возвращает промежуточный итог в списке или базе данных. В этом контексте «промежуточный итог» — это не просто суммирование чисел в определенном диапазоне ячеек. В отличие от других функций Excel, предназначенных для выполнения только одной конкретной задачи, ПРОМЕЖУТОЧНЫЕ.ИТОГИ удивительно универсальны — они могут выполнять различные арифметические и логические операции, такие как подсчет ячеек, вычисление среднего значения, нахождение минимального или максимального значения и многое другое.

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

Синтаксис функции ПРОМЕЖУТОЧНЫЙ ИТОГ Excel следующий:

ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции, ссылка1, [ссылка2],…)

Где:

• Номер_функции — число, указывающее, какую функцию использовать для промежуточного итога.
• Ref1, Ref2, … — одна или несколько ячеек или диапазонов для промежуточного итога. Первый аргумент ref является обязательным, остальные (до 254) необязательны.

Аргумент номер_функции может принадлежать к одному из следующих наборов:

• 1 — 11 игнорировать отфильтрованные ячейки, но включать скрытые вручную строки.
• 101 — 111 игнорировать все скрытые ячейки — отфильтровано и скрыто вручную.

Function_num		Функция	Описание
1	101	СРЕДНЕЕ	Возвращает среднее число.
2	102	СЧЕТ	Подсчитывает ячейки, содержащие числовые значения.
3	103	СЧЁТ	Подсчитывает непустые ячейки.
4	104	МАКС	Возвращает наибольшее значение.
5	105	МИН	Возвращает наименьшее значение.
6	106	ПРОДУКТ	Вычисляет произведение ячеек.
7	107	СТАНДОТКЛОН	Возвращает стандартное отклонение совокупности на основе выборки чисел.
8	108	СТДЕВП	Возвращает стандартное отклонение на основе всей совокупности чисел.
9	109	СУММА	Складывает числа.
10	110	ВАР	Оценивает дисперсию генеральной совокупности на основе выборки чисел.
11	111	ВАРП	Оценивает дисперсию совокупности на основе всей совокупности чисел.

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

Например, вот как можно создать формулу «Промежуточный итог 9» для суммирования значений в ячейках от C2 до C8:

Чтобы добавить в формулу номер функции, дважды щелкните по ней, затем введите запятую, укажите диапазон, введите закрывающую скобку и нажмите Enter. Завершенная формула будет выглядеть следующим образом:

=ПРОМЕЖУТОЧНЫЙ ИТОГ(9,C2:C8)

Аналогичным образом вы можете написать формулу Промежуточный итог 1 для получения среднего значения, Промежуточный итог 2 для подсчета ячеек с числами, Промежуточный итог 3 для подсчета непустых и так далее. На следующем снимке экрана показано несколько других формул в действии:

Примечание. Когда вы используете формулу промежуточного итога с функцией суммирования, такой как СУММ или СРЗНАЧ, она вычисляет только ячейки с числами, игнорируя пробелы, и ячейки, содержащие нечисловые значения.

Теперь, когда вы знаете, как создать формулу промежуточного итога в Excel, возникает главный вопрос: зачем утруждать себя ее изучением? Почему бы просто не использовать обычные функции, такие как SUM, COUNT, MAX и т. д.? Ответ вы найдете прямо ниже.

3 главные причины использовать ПРОМЕЖУТОЧНЫЕ ИТОГО в Excel

По сравнению с традиционными функциями Excel, ПРОМЕЖУТОЧНЫЙ ИТОГ дает вам следующие важные преимущества.

1. Вычисление значений в отфильтрованных строках

Поскольку функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ Excel игнорирует значения в отфильтрованных строках, ее можно использовать для создания динамической сводки данных, в которой промежуточные итоги автоматически пересчитываются в соответствии с фильтром.

Например, если мы отфильтруем таблицу, чтобы показать продажи только для Восточного региона, формула промежуточного итога будет автоматически скорректирована таким образом, что все другие регионы будут удалены из общего количества:

Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу ether Subtotal 9 или Subtotal 109.

2. Вычислять только видимые ячейки

Как вы помните, формулы промежуточных итогов с номером_функции от 101 до 111 игнорируют все скрытые ячейки — отфильтровываются и скрываются вручную. Итак, когда вы используете функцию «Скрыть» Excel для удаления ненужных данных из представления, используйте номер функции 101-111, чтобы исключить значения в скрытых строках из промежуточных итогов.

Следующий пример поможет вам лучше понять, как это работает: промежуточный итог 9 против промежуточного итога 109.

3. Игнорировать значения во вложенных формулах промежуточных итогов

Если диапазон, указанный в вашей формуле промежуточных итогов Excel, содержит любые другие формулы промежуточных итогов, эти вложенные промежуточные итоги будут игнорироваться, поэтому одни и те же числа не будут рассчитываться дважды. Потрясающе, не так ли?

На снимке экрана ниже формула общего среднего ПРОМЕЖУТОЧНЫЙ ИТОГ(1, C2:C10) игнорирует результаты формул промежуточного итога в ячейках C3 и C10, как если бы вы использовали формулу среднего с двумя отдельными диапазонами СРЕДНЕЕ(C2:C5, C7:C9) .

Использование промежуточного итога в Excel — примеры формул

Когда вы впервые сталкиваетесь с промежуточным итогом, он может показаться сложным, запутанным и даже бессмысленным. Но как только вы приступите к делу, вы поймете, что освоить его не так уж и сложно. Следующие примеры покажут вам несколько полезных советов и вдохновляющих идей.

Пример 1. Промежуточный итог 9 против промежуточного итога 109

Как вы уже знаете, Excel ПРОМЕЖУТОЧНЫЕ ИТОГО принимает 2 набора номеров функций: 1-11 и 101-111. Оба набора игнорируют отфильтрованные строки, но номера 1-11 включают скрытые вручную строки, тогда как 101-111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.

Чтобы получить отфильтрованных строки , вы можете использовать формулу Subtotal 9 или Subtotal 109, как показано на снимке экрана ниже:

вкладка > Cells group > Format > Hide & Unhide , или щелкнув правой кнопкой мыши строки, а затем щелкнув Hide , и теперь вы хотите суммировать значения только в видимых строках, промежуточный итог 109единственный вариант:

Другие номера функций работают таким же образом. Например, для подсчета непустых отфильтрованных ячеек подойдет формула «Итог 3» или «Итог 103». Но только Промежуточный итог 103 может правильно подсчитать видимые непустые строки, если в диапазоне есть скрытые строки:

Примечание. Функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ Excel с номером_функции 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах . Например, если вы используете такую ​​формулу, как SUBTOTAL(109, A1:E1) для суммирования чисел в горизонтальном диапазоне, скрытие столбца не повлияет на промежуточный итог.

Пример 2. ЕСЛИ + ПРОМЕЖУТОЧНЫЙ ИТОГ для динамического суммирования данных

Если вы создаете сводный отчет или информационную панель, где вам нужно отобразить различные сводки данных, но у вас нет места для всего, следующий подход может быть решением:

• В одной ячейке создайте раскрывающийся список, содержащий имена функций, такие как Total, Max, Min и т. д.
• В ячейку рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточного итога, соответствующими именам функций в раскрывающемся списке.

Например, предположим, что значения для промежуточного итога находятся в ячейках C2:C16, а раскрывающийся список в A17 содержит Итого , Среднее , Макс. и Мин. выглядит следующим образом:

=ЕСЛИ(A17="всего", ПРОМЕЖУТОЧНЫЙ ИТОГ(9,C2:C16), ЕСЛИ(A17="среднее", ПРОМЕЖУТОЧНЫЙ ИТОГ(1,C2:C16), ЕСЛИ(A17="минимум", ПРОМЕЖУТОЧНЫЙ ИТОГ(5,C2:C16), ЕСЛИ(A17="max", ПРОМЕЖУТОЧНЫЙ ИТОГ(4,C2:C16),""))))

И теперь, в зависимости от того, какую функцию пользователь выберет из выпадающего списка, соответствующая функция Subtotal будет вычислять значения в отфильтрованных строках:

Подсказка. Если вдруг выпадающий список и ячейка с формулой исчезнут с вашего листа, обязательно выберите их в списке фильтров.

Промежуточный итог Excel не работает — распространенные ошибки

Если формула промежуточного итога возвращает ошибку, это может быть вызвано одной из следующих причин:

#ЗНАЧ! — аргумент номер_функции отличен от целого числа от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку.

#ДЕЛ/0! — возникает, если указанная функция суммирования должна выполнять деление на ноль (например, вычисление среднего значения или стандартного отклонения для диапазона ячеек, не содержащих ни одного числового значения).

#ИМЯ? — неправильно написано название функции Subtotal — проще ошибку исправить 🙂

Совет. Если вы еще не чувствуете себя комфортно с функцией ПРОМЕЖУТОЧНЫЕИТОГИ, вы можете использовать встроенную функцию ПРОМЕЖУТОЧНЫЕИТОГИ и автоматически вставить формулы для вас.

Вот как можно использовать формулы ПРОМЕЖУТОЧНЫХ ИТОГОВ в Excel для вычисления данных в видимых ячейках. Чтобы вам было легче следовать примерам, вы можете скачать нашу рабочую тетрадь с примерами ниже. Спасибо за чтение!

Практическая рабочая тетрадь

Примеры формул промежуточных итогов Excel (файл .xlsx)

Вас также может заинтересовать

ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel (формула, примеры)

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel (оглавление)

• ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel
• Формула промежуточного итога в Excel
• Как использовать функцию ПРОМЕЖУТОЧНЫЕ ИТОГО в Excel?

• Функция промежуточного итога — это встроенная функция Excel, относящаяся к категории математических/триггерных функций.
• Промежуточный итог не просто суммирует числа в определенном диапазоне ячеек; это поможет, выполняя различные арифметические операции.
• ПРОМЕЖУТОЧНЫЙ ИТОГ универсален. Вы можете использовать его для усреднения, суммирования, подсчета множества ячеек и других вычислений.

Определение

Возвращает промежуточный итог в базе данных или списке, т. е. находит промежуточный итог заданного диапазона ячеек. Он выполняет указанный расчет для заданного набора значений.

ПРОМЕЖУТОЧНЫЙ ИТОГ Формула в Excel

Формула для функции ПРОМЕЖУТОЧНЫЙ ИТОГ выглядит следующим образом:

ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции, ссылка1, [ссылка2],…) Где

Номер_функции или код операции: Указывает, какую функцию следует использовать для расчета промежуточных итогов в заданном списке. Это тип математической операции, которую вам нравится выполнять.

Аргумент номер_функции применяется на основе двух указанных ниже критериев или методов.

1. Включить скрытые значения Когда аргумент номер_функции находится в диапазоне от 1 до 11, функция ПРОМЕЖУТОЧНЫЕ ИТОГ будет включать скрытые значения.
2. Игнорировать скрытые значения Если аргумент номер_функции находится в диапазоне от 101 до 111, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет исключать или игнорировать скрытые значения. Он найдет значения только в невидимых строках, которые игнорируют скрытые значения при расчете.

Fun_num Включает скрытые значения		Fun_num Исключает скрытые значения		Функция		Описание
1		101		СРЕДНЕЕ		Вычисляет среднее значение указанного диапазона.
2		102		СЧЕТ		Будет подсчитано количество ячеек, содержащих числовые значения.
3		103		СЧЁТ		Будет подсчитаны непустые ячейки в указанном диапазоне.
4		104		МАКС		Обнаружит наибольшее значение в указанном диапазоне.
5		105		МИН		Найдет наименьшее значение в указанном диапазоне.
6		106		ПРОДУКТ		Вычисляет произведение ячеек в указанном диапазоне.
7		107		СТАНДОТКЛОН		Оценивает стандартное отклонение в указанном диапазоне.
8		108		СТДЕВП		Он вычисляет стандартное отклонение на основе всей совокупности.
9		109		СУММА		Вычисляет сумму указанного диапазона.
10		110		ВАР		Оценивает дисперсию в указанном диапазоне.
11		111		ВАРП		Оценивает дисперсию на основе всего населения.

ref1, [ref2]: Одна или несколько ссылок на ячейки, содержащие значения, для которых должен быть выполнен расчет промежуточного итога.