Загрузите образец книги
В этом руководстве будет показано, как вычислить «промежуточный итог, если», подсчитывая только видимые строки с критериями..
ПРОМЕЖУТОЧНЫЙ ИТОГ Функция
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ может выполнять различные вычисления с диапазоном данных (количество, сумма, среднее значение и т. Д.). Что наиболее важно, его можно использовать для вычисления только видимых (отфильтрованных) строк. В этом примере мы будем использовать функцию для подсчета (COUNTA) видимых строк, установив для аргумента SUBTOTAL function_num значение 3 (полный список возможных функций можно найти здесь).
= ПРОМЕЖУТОЧНЫЙ ИТОГ (3; $ D $ 2: $ D $ 14)
Обратите внимание, как меняются результаты, когда мы вручную фильтруем строки.
ПРОМЕЖУТОЧНЫЙ ИТОГ, ЕСЛИ
Чтобы создать «Промежуточный итог, если», мы будем использовать комбинацию SUMPRODUCT, SUBTOTAL, OFFSET, ROW и MIN в формуле массива. Используя эту комбинацию, мы можем создать общую функцию «ПРОМЕЖУТОЧНЫЙ ИТОГ, ЕСЛИ». Давайте рассмотрим пример.
У нас есть список участников и их статус посещаемости для каждого мероприятия:
Предположим, нас просят подсчитать количество участников, которые посетили мероприятие динамически, поскольку мы вручную фильтруем список следующим образом:
Для этого мы можем использовать эту формулу:
= СУММПРОИЗВ ((=) * (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (; СТРОКА () - МИН (СТРОКА ()); 0))))
= СУММПРОИЗВ ((D2: D14 = "Посещено") * (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (D2; СТРОКА (D2: D14) -МИН (СТРОКА (D2: D14)), 0))))
При использовании Excel 2022 и более ранних версий необходимо ввести формулу массива, нажав CTRL + SHIFT + ВВОД чтобы сообщить Excel, что вы вводите формулу массива. Вы узнаете, что формула была введена правильно как формула массива, когда вокруг формулы появятся фигурные скобки (см. Изображение выше).
Как работает формула?
Формула работает путем умножения двух массивов внутри SUMPRODUCT, где первый массив имеет дело с нашими критериями, а второй массив фильтрует только видимые строки:
= СУММПРОИЗВ (*)
Массив критериев
Массив критериев оценивает каждую строку в нашем диапазоне значений (статус «Присутствует» в этом примере) и генерирует такой массив:
=(=)
= (D2: D14 = "Присутствовали")
Выход:
{ПРАВДА; ЛОЖНЫЙ; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ТЮРЕ; ТЮРЕ; ТЮРЕ; ЛОЖНЫЙ; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА}
Обратите внимание, что вывод в первом массиве в нашей формуле игнорирует, видна ли строка или нет, и именно здесь на помощь приходит наш второй массив.
Массив видимости
Используя SUBTOTAL для исключения невидимых строк из нашего диапазона, мы можем сгенерировать наш массив видимости. Однако только SUBTOTAL вернет одно значение, а SUMPRODUCT ожидает массив значений. Чтобы обойти это, мы используем OFFSET для передачи по одной строке за раз. Этот метод требует подачи OFFSET массива, который содержит одно число за раз. Второй массив выглядит так:
= ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (; СТРОКА () - МИН (СТРОКА ()); 0))
= ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (D2; СТРОКА (D2: D14) -МИН (СТРОКА (D2: D14)); 0))
Выход:
{1;1;0;0;1;1}
Сшивание двух вместе:
= СУММПРОИЗВ ({ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА} * {1; 1; 0; 0; 1; 1})
= 4
ПРОМЕЖУТОЧНЫЙ ИТОГ, ЕСЛИ с несколькими критериями
Чтобы добавить несколько критериев, просто добавьте еще несколько критериев в СУММПРОИЗВ, например:
= СУММПРОИЗВ ((=) * (=) * (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (; СТРОКА () - МИН (СТРОКА ()), 0))))
= СУММПРОИЗВ ((E2: E14 = "Посещено") * (B2: B14 = 2019) * (ПРОМЕЖУТОЧНЫЙ ИТОГ (3, СМЕЩЕНИЕ (E2; СТРОКА (E2: E14) -МИН (СТРОКА (E2: E14)), 0)) ))
ПРОМЕЖУТОЧНЫЙ ИТОГ, ЕСЛИ в Google Таблицах
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Google Таблицах работает точно так же, как и в Excel:
