Загрузите образец книги
В этом руководстве показано, как использовать Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel в Excel для расчета сводной статистики.
ПРОМЕЖУТОЧНЫЙ ИТОГ Обзор функции
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ Вычисляет итоговую статистику для серии данных. Доступная статистика включает, помимо прочего, среднее значение, стандартное отклонение, количество, минимальное и максимальное значение. См. Полный список ниже в разделе функциональных входов:
Чтобы использовать функцию рабочего листа Excel ПРОМЕЖУТОЧНЫЙ ИТОГ, выберите ячейку и введите:
(Обратите внимание, как появляются входные данные формулы)
ПРОМЕЖУТОЧНЫЙ ИТОГО Синтаксис и входные данные функции:
| 1 | = ПРОМЕЖУТОЧНЫЙ ИТОГ (номер_функции; ЗАДАНИЕ1) |
function_num - Число, указывающее, какую операцию выполнить.
REF1 - Диапазоны или ссылки, содержащие данные для расчета.
Что такое функция ПРОМЕЖУТОЧНЫЙ ИТОГ?
ПРОМЕЖУТОЧНЫЙ ИТОГ - одна из уникальных функций электронных таблиц, поскольку он может отличить скрытые от скрытых ячеек. Это может оказаться весьма полезным при работе с отфильтрованными диапазонами или когда вам нужно настроить вычисления на основе различных пользовательских выборов. Поскольку он также знает, как игнорировать другие функции ПРОМЕЖУТОЧНОГО ИТОГА из его вычислений, мы также можем использовать его в больших суммированных данных, не опасаясь двойного счета.
Базовая сводка с ПРОМЕЖУТОЧНЫМ ИТОГОМ
Допустим, у вас есть таблица продаж отсортированных продуктов, и вы хотите создать итоги для каждого продукта, а также создать общую сумму. Вы можете использовать сводную таблицу или вставить несколько формул. Рассмотрим этот макет:
Я поместил несколько функций ПРОМЕЖУТОЧНЫЙ ИТОГ в ячейки B5 и B8, которые выглядят так:
| 1 | = ПРОМЕЖУТОЧНЫЙ ИТОГ (9; B2: B4) |
Исходя из синтаксиса, вы можете использовать различные числа в качестве первого аргумента. В нашем конкретном случае мы используем 9, чтобы указать, что мы хотим вычислить сумму.
Сосредоточимся на ячейке B9. В нем есть эта формула, которая включает весь диапазон данных столбца B, но не включает другие промежуточные итоги.
| 1 | = ПРОМЕЖУТОЧНЫЙ ИТОГ (9; B2: B8) |
ПРИМЕЧАНИЕ. Если вы не хотите писать все формулы итога самостоятельно, вы можете перейти на ленту «Данные» и использовать мастер Outline - Subtotal. Он автоматически вставит строки и разместит формулы за вас.
Разница в первых аргументах
В первом примере мы использовали 9, чтобы указать, что хотим подсчитать. Разница между использованием 9 и 109 будет заключаться в том, как мы хотим, чтобы функция обрабатывала скрытые строки. Если вы используете обозначения 1XX, функция не будет включать строки, которые были вручную скрыты или отфильтрованы.
Вот наша таблица из прошлого. Мы переместили функции, чтобы увидеть разницу между аргументами 9 и 109. При всем видимом результаты такие же.
Если мы применим фильтр, чтобы отфильтровать значение 6 в столбце B, две функции останутся такими же.
Если мы вручную скроем строки, мы увидим разницу. Функция 109 смогла игнорировать скрытую строку, а функция 9 - нет.
Изменить математическую операцию с ПРОМЕЖУТОЧНЫМ ИТОГОМ
Иногда вы можете дать своему пользователю возможность изменить тип выполняемых вычислений. Например, хотят ли они получить сумму или среднее значение. Поскольку SUBTOTAL управляет математической операцией по номеру аргумента, вы можете записать это в одной формуле. Вот наша установка:
Мы создали раскрывающийся список в D2, где пользователь может выбрать «Сумма» или «Среднее». Формула в E2:
| 1 | = ПРОМЕЖУТОЧНЫЙ ИТОГ (ЕСЛИ (D2 = «Среднее»; 1; ЕСЛИ (D2 = «Сумма»; 9)); B2: B4) |
Здесь функция ЕСЛИ будет определять, какой числовой аргумент передать ПРОМЕЖУТОЧНОМУ ИТОГУ. Если A5 - «Среднее», то будет выведено 1, а ПРОМЕЖУТОЧНЫЙ ИТОГО даст среднее значение B2: B4. Или, если A5 равно «Sum», тогда IF выдает 9, и мы получаем другой результат.
Вы можете расширить эту возможность, используя таблицу поиска, чтобы перечислить еще больше типов операций, которые вы хотите выполнить. Ваша таблица поиска может выглядеть так
Затем вы можете изменить формулу в E2 на
| 1 | = ПРОМЕЖУТОЧНЫЙ ИТОГ (ВПР (A5; Таблица поиска; 2; 0); B2: B4) |
Условные формулы с ПРОМЕЖУТОЧНЫМ ИТОГОМ
Хотя SUBTOTAL может выполнять множество операций, он не может проверять критерии самостоятельно. Однако мы можем использовать его во вспомогательном столбце для выполнения этой операции. Когда у вас есть столбец данных, который, как вы знаете, будет всегда если в нем есть часть данных, вы можете использовать способность SUBTOTALs для обнаружения скрытых строк.
Вот таблица, с которой мы будем работать в этом примере. В конце концов, мы хотели бы иметь возможность суммировать значения для «Apple», но также позволить пользователю фильтровать столбец Qty.
Сначала создайте вспомогательный столбец, в котором будет находиться функция ПРОМЕЖУТОЧНЫЙ ИТОГ. В C2 формула:
| 1 | = ПРОМЕЖУТОЧНЫЙ ИТОГ (103; A2) |
Помните, что 103 означает, что мы хотим выполнить COUNTA. Я рекомендую использовать COUNTA, потому что тогда вы можете заполнить ссылочную ячейку A2 или числа или текст. Теперь у вас будет таблица, которая выглядит так:
Сначала это не кажется полезным, потому что все значения равны 1. Однако, если мы скроем строку 3, то «1» в C3 изменится на 0, потому что он указывает на скрытую строку. Хотя невозможно получить изображение, показывающее значение конкретной скрытой ячейки, вы можете проверить его, скрыв строку, а затем написав основную формулу, подобную этой, для проверки.
| 1 | = C3 |
Теперь, когда у нас есть столбец, значение которого будет изменяться в зависимости от того, скрыт он или нет, мы готовы написать окончательное уравнение. Наши СУММЕСЛИМН будут выглядеть так
В этой формуле мы будем суммировать значения из столбца B, только если столбец A равен «Apple», а также значение в столбце C равно 1 (иначе строка не скрыта). Допустим, наш пользователь хочет отфильтровать 600, потому что оно кажется ненормально высоким. Мы видим, что наша формула дает правильный результат.
С помощью этой возможности вы можете применить проверку к СЧЁТЕСЛИМН, СУММЕСЛИМН или даже СУММПРОИЗВ. Вы добавляете возможность позволить вашим пользователям управлять некоторыми срезами таблиц, и вы готовы создать потрясающую панель инструментов.
ПРОМЕЖУТОЧНЫЙ ИТОГ в Google Таблицах
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Google Таблицах работает точно так же, как и в Excel:
ПРОМЕЖУТОЧНЫЙ ИТОГО Примеры в VBA
Вы также можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в VBA. Тип:application.worksheetfunction.subtotal (function_num, reh1)
Выполнение следующих операторов VBA
| 1234567891011121314151617 | Диапазон ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Диапазон ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Диапазон ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Диапазон ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Диапазон ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Диапазон ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Диапазон ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Диапазон ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Диапазон ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Диапазон ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Диапазон ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Диапазон ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Диапазон ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Диапазон ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Диапазон ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
даст следующие результаты
Для аргументов функции (номер_функции и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные, которые будут использоваться вместо них.
Вернуться к списку всех функций в Excel
