Если ваш рабочий лист Excel содержит скрытые строки, отфильтрованные данные или сгруппированные данные, используйте функцию ПРОМЕЖУТОЧНЫЕ ИТОГОВ Excel. Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ может включать или исключать скрытые значения в расчетах. В дополнение к нахождению суммы группы данных Excel может вычислять среднее, максимальное, минимальное значение, стандартное отклонение и дисперсию ваших данных. Вот как вставить промежуточные итоги в Excel.
Инструкции в этой статье относятся к Excel для Microsoft 365, Excel 2019 и Excel 2016.
Синтаксис функции SUBTOTAL
Используйте функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ в Excel, чтобы суммировать значения на листе различными способами. Это особенно полезно, когда ваш рабочий лист содержит скрытые строки, которые вы хотите включить в расчет.
Синтаксис функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ:
Аргумент function_num является обязательным и указывает тип математической операции, используемой для промежуточного итога. Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ может добавлять числа, вычислять среднее значение выбранных чисел, находить максимальные и минимальные значения в диапазоне, подсчитывать количество значений в выбранном диапазоне и многое другое.
Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ игнорирует ячейки, которые не содержат данных, и ячейки с нечисловыми значениями.
Этот аргумент является числом и зависит от того, хотите ли вы включить скрытые строки в результат или исключить скрытые строки из результата. Эти строки можно скрыть вручную или с помощью фильтра.
Аргументы function_num включают:
| Функциональная задача | номер_функции | номер_функции |
|---|---|---|
| (включая скрытые значения) | (исключая скрытые значения) | |
| СРЕДНЯЯ | 1 | 101 |
| СЧЕТ | 2 | 102 |
| СЧЁТ | 3 | 103 |
| МАКС | 4 | 104 |
| МИН | 5 | 105 |
| ПРОДУКТ | 6 | 106 |
| СТАНДОТКЛОН | 7 | 107 |
| STDEVP | 8 | 108 |
| СУММ | 9 | 109 |
| ВАР | 10 | 110 |
| ВАРП | 11 | 111 |
Аргументы ссылки function_num с 1 по 11 включают только значения в скрытых строках при использовании команды Hide для скрытия строк. При использовании команды «Фильтр» расчет ПРОМЕЖУТОЧНЫХ ИТОГОВ не включает скрытые результаты фильтрации.
Аргумент ref1 обязателен. Это ячейки, используемые для вычисления результатов выбранного аргумента function_num. Этот аргумент может быть значением, отдельной ячейкой или диапазоном ячеек.
Аргументы ref2, … являются необязательными. Это дополнительные ячейки, которые включаются в расчет.
Используйте функцию ПРОМЕЖУТОЧНЫЕ ИТОГО со скрытыми строками
Функции Excel можно вводить вручную или с помощью диалогового окна «Аргументы функции». Чтобы проиллюстрировать, как ввести функцию вручную с помощью строки формул, в следующем примере аргумент COUNT номер_функции используется для подсчета количества значений в видимых строках, а также в видимых и скрытых строках.
Чтобы использовать функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ для подсчета количества строк на листе:
- Начните с рабочего листа, содержащего несколько строк данных.
- Выберите ячейку, которая будет содержать количество видимых строк.
-
В строке функций введите =ПРОМЕЖУТОЧНЫЙ ИТОГ. По мере ввода Excel предлагает функцию. Дважды щелкните функцию ПРОМЕЖУТОЧНЫЙ ИТОГ..
Чтобы использовать диалоговое окно «Аргументы функции» для ввода функции ПРОМЕЖУТОЧНЫЙ ИТОГ, перейдите к Формулы и выберите Математика и триггеры >ПРОМЕЖУТОЧНЫЙ ИТОГ.
Image -
В появившемся раскрывающемся меню дважды щелкните аргумент 102 – COUNT номер_функции.
Image -
Введите запятую (,).
Image -
На рабочем листе выберите ячейки для включения в формулу.
Image -
Нажмите Enter, чтобы увидеть результат в ячейке, выбранной на шаге 2.
Image - Выберите ячейку, которая будет содержать количество видимых и скрытых строк.
-
В строке функций введите =ПРОМЕЖУТОЧНЫЙ ИТОГ. По мере ввода Excel предлагает функцию. Дважды щелкните функцию ПРОМЕЖУТОЧНЫЙ ИТОГ..
- В появившемся раскрывающемся меню дважды щелкните аргумент 2 – COUNT номер_функции, затем введите запятую (,).
-
На рабочем листе выберите ячейки для включения в формулу, затем нажмите Enter.
Image -
Скрыть несколько строк данных. В этом примере были скрыты строки только с продажами ниже 100 000 долларов США.
Image
Использование функции ПРОМЕЖУТОЧНЫЙ ИТОГ с отфильтрованными данными
Использование функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ для отфильтрованных данных игнорирует данные в строках, которые были удалены фильтром. Каждый раз, когда критерии фильтра меняются, функция пересчитывает, чтобы показать промежуточный итог для видимых строк.
Чтобы использовать функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ для просмотра различий в результатах вычислений при фильтрации данных:
-
Создавайте формулы ПРОМЕЖУТОЧНЫХ ИТОГОВ. Например, создайте формулы для определения промежуточных и средних значений отфильтрованных данных.
Не имеет значения, используете ли вы аргумент номер_функции для видимых или скрытых строк. Оба аргумента дают одинаковый результат в отфильтрованных данных.
Image - Выберите любую ячейку в наборе данных.
-
Перейдите на Главная, затем выберите Сортировка и фильтр > Фильтр.
Image -
Используйте стрелки раскрывающегося списка для фильтрации данных рабочего листа.
Image -
Обратите внимание, как значения меняются каждый раз, когда вы выбираете разные критерии фильтрации.
Image
Используйте функцию ПРОМЕЖУТОЧНЫЕ ИТОГО со сгруппированными данными
Когда данные сгруппированы, есть способ применить функцию ПРОМЕЖУТОЧНЫЙ ИТОГ к каждой отдельной группе, а затем вычислить общую сумму для всего набора данных.
- Выберите любую ячейку в наборе данных.
-
Выберите Данные > Промежуточный итог, чтобы открыть диалоговое окно Промежуточный итог.
Image - Выберите стрелку раскрывающегося списка При каждом изменении в и выберите группу, по которой будет рассчитываться каждый промежуточный итог.
- Выберите стрелку раскрывающегося списка Использовать функцию и выберите номер_функции.
- В списке Добавить промежуточный итог к выберите столбец, к которому будет применяться формула.
-
Выберите OK.
Image -
Промежуточные итоги вставляются для каждой группы данных, а общий итог вставляется внизу набора данных.
Image - Чтобы изменить номер_функции, выделите любую ячейку в наборе данных и выберите Данные > Промежуточный итог. Затем сделайте свой выбор в диалоговом окне Промежуточный итог..
