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

Что такое функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel ? Что такое формула ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel ? Давайте выясним это вместе с LuckyTemplates.com!

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

Основное содержание функции ПРОМЕЖУТОЧНЫЙ ИТОГ

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

Что такое функция ПРОМЕЖУТОЧНЫЙ ИТОГ и для чего она используется?

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

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

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

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

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

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ работает путем расчета общего значения диапазона ячеек на основе другой математической функции. ПРОМЕЖУТОЧНЫЙ ИТОГ использует до 11 различных математических функций (от СРЗНАЧА до ДИСП.П) для нахождения соответствующего итогового значения, работая при этом в пределах параметров функции (т. е. игнорируя другие формулы ПРОМЕЖУТОЧНОГО ИТОГА).

Функция ПРОМЕЖУТОЧНЫЙИТОГ имеет синтаксис =SUBTOTAL(function_num, ref1, [ref2],…) . Там:

• Function_num: Числа от 1 до 11 и от 101 до 111 определяют, какая функция будет использоваться для расчета ПРОМЕЖУТОЧНОГО ИТОГО.
• Ref1, Ref2, ...: 1 или несколько ячеек или диапазон ячеек для расчета промежуточных итогов, максимум 254.

Примечание:

• Функция ПРОМЕЖУТОЧНЫЙ ИТОГ предназначена для расчета вертикальных столбцов данных.
• Если аргументы ref1, ref2,... содержат функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, они будут игнорироваться во избежание двойного счета.
• Если function_num имеет значение от 1 до 11, функция ПРОМЕЖУТОЧНЫЙ ИТОГ вычисляет скрытые значения в наборе данных, поскольку строка, содержащая этот диапазон, скрыта. Если function_num от 101 до 111, функция ПРОМЕЖУТОЧНЫЙ ИТОГ вычисляет только нескрытые значения в наборе данных (не считая значений в скрытых строках).
• Для областей данных, скрытых фильтром, ПРОМЕЖУТОЧНЫЙ ИТОГ будет игнорироваться.

Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel

1. Подсчитайте общее количество отфильтрованных строк. 

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

= ПРОМЕЖУТОЧНЫЙ ИТОГО (9,_диапазон)

pham_range — это область, которую вы хотите суммировать после фильтрации данных.

2. Подсчитайте отфильтрованные ячейки, которые не пусты.

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

Например, в приведенной выше таблице данных будут скрыты 2 строки 4 и 5. При использовании ПРОМЕЖУТОЧНОГО ИТОГО 3 или ПРОМЕЖУТОЧНОГО ИТОГО 103 будут получены 2 разных результата.

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

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

Что касается ПРОМЕЖУТОЧНОГО ИТОГА 103, то он будет отображать только те непустые ячейки, которые мы видим, игнорируя скрытые строки.

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

Для примера посчитаем среднее общее количество килограммов ткани на складах А1 и А2.

Формула среднего значения для склада A2= ПРОМЕЖУТОЧНЫЙ(1,C2:C4) дает результат 19.

Формула среднего значения для склада A1 = ПРОМЕЖУТОЧНЫЙ(1,C5:C7) имеет результат 38.

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

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

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

• Как уже упоминалось, использование значений 1–11 для аргумента function_num гарантирует, что ПРОМЕЖУТОЧНЫЙ ИТОГ включает в себя скрытые значения, а значения 101–111 их игнорируют.
• Использование значения, отличного от 1–11 или 101–111, приведет к тому, что Excel вернет ошибку #ЗНАЧ!. Это также произойдет для ссылок на трехмерные ячейки (когда одна и та же ячейка на нескольких листах ссылается на диапазон).
• При использовании горизонтальных диапазонов ячеек, таких как A1:D1, скрытые значения автоматически включаются (независимо от значения аргумента function_num). Это ограничение функции ПРОМЕЖУТОЧНЫЙ ИТОГ, и его невозможно преодолеть.
• Использование ПРОМЕЖУТОЧНОГО ИТОГА для отфильтрованных данных гарантирует, что скрытые значения всегда будут игнорироваться, независимо от используемого аргумента function_num.
• ПРОМЕЖУТОЧНЫЙ ИТОГО можно использовать как вложенную функцию в составе других функций, включая формулы, содержащие функцию ЕСЛИ.
• Если другая формула ПРОМЕЖУТОЧНОГО ИТОГА находится в диапазоне данных ref1, ПРОМЕЖУТОЧНЫЙ ИТОГ игнорирует ее и исключает из общего расчета.
• Excel позволяет использовать до 254 диапазонов ячеек в формуле ПРОМЕЖУТОЧНЫЙ ИТОГО, хотя для работы формулы необходим только 1 диапазон ячеек.

Как допустить ошибку при выполнении функции ПРОМЕЖУТОЧНЫЙ ИТОГО в Excel

При расчете функции ПРОМЕЖУТОЧНЫЙ ИТОГО в Excel вы часто сталкиваетесь с некоторыми основными ошибками:

• #ЗНАЧ!: Идентификатор функции не находится в диапазоне 1–11 или 101–111 или имеет трехмерную ссылку.
• #ДЕЛ/0!: возникает, когда определенную сумму необходимо разделить на 0 (например, при вычислении среднего или стандартного отклонения диапазона ячеек, не содержащего числовых значений).
• #ИМЯ? Имя функции ПРОМЕЖУТОЧНЫЙ ИТОГ написано с ошибкой.

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

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

Шаг 1. Нажмите «Промежуточный итог» . Помните, что этот шаг заключается в добавлении дополнительных критериев к существующим промежуточным данным.

Шаг 2. Выберите COUNT в раскрывающемся меню и Size в поле « Добавить поле промежуточного итога ». Затем снимите флажок Заменить текущие промежуточные итоги . Когда вы нажмете ОК , вы получите следующую таблицу данных:

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

Совет 2. Всегда полезно сортировать данные по столбцам.

Совет 3. Помогает включить метку в первую строку.

Совет 4. Если вы хотите суммировать данные, снимите флажок « Сводка под данными при вставке промежуточного итога ». - Сводные данные ниже при вставке функции ПРОМЕЖУТОЧНЫЙ ИТОГО.

Короче говоря, что такое функция «Промежуточный итог» в Excel?

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

• Цель: Вычислить промежуточный итог в списке или базе данных.
• Возвращаемое значение: число, представляющее тип промежуточного итога.
• Аргумент:
  • function_num– Число, указывающее, какую функцию использовать для расчета промежуточных итогов в списке.
  • ref1– Именованный диапазон или ссылка на промежуточный итог.
  • ref2- [необязательно] Именованный диапазон или ссылка на промежуточный итог.

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

Узнать больше:

• Функция СУММПРОИЗВ в Excel: вычислить общий продукт соответствующих значений.
• Как объединить два столбца «Полное имя» и «Имя» в Excel без потери содержимого