Завантажте зразок робочої книги
Цей підручник продемонструє, як обчислити "проміжну підсумок, якщо", підрахувавши лише видимі рядки з критеріями.
Функція SUBTOTAL
Функція SUBTOTAL може виконувати різні обчислення за діапазоном даних (кількість, сума, середнє значення тощо). Найголовніше, що його можна використовувати для обчислення лише у видимих (відфільтрованих) рядках. У цьому прикладі ми будемо використовувати функцію для підрахунку (COUNTA) видимих рядків, встановивши аргумент SUBTOTAL function_num на 3 (Повний список можливих функцій можна знайти тут.)
= ПІДСУМКИ (3, $ D $ 2: $ D $ 14)
Зверніть увагу, як змінюються результати, коли ми вручну фільтруємо рядки.
ПІДСУМКОВИЙ IF
Для створення “Проміжного підсумку If” ми будемо використовувати комбінацію SUMPRODUCT, SUBTOTAL, OFFSET, ROW та MIN у формулі масиву. Використовуючи цю комбінацію, ми можемо по суті створити загальну функцію "ПІДСУМКОВИЙ ІФ". Пройдемося по прикладу.
У нас є список учасників та їх статус відвідуваності для кожного заходу:
Припустимо, нас попросять підрахувати кількість учасників, які динамічно відвідували подію, коли ми вручну фільтруємо список так:
Для цього ми можемо скористатися такою формулою:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN) (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Участь")*(ПІДСУМКИЙ (3, ЗМІСТ (D2, РЯД (D2: D14) -МІН (РЯД (D2: D14)), 0))))
Під час використання Excel 2022 та попередніх версій необхідно ввести формулу масиву, натиснувши CTRL + SHIFT + ENTER повідомити Excel, що ви вводите формулу масиву. Ви будете знати, що формула була правильно введена як формула масиву, коли навколо формули з’являються фігурні дужки (див. Зображення вище).
Як працює формула?
Формула працює шляхом множення двох масивів усередині SUMPRODUCT, де перший масив відповідає нашим критеріям, а другий - фільтрує лише видимі рядки:
= ПІДПРИЄМСТВО (*)
Масив критеріїв
Масив критеріїв обчислює кожен рядок у нашому діапазоні значень (у цьому прикладі статус "Відвідуваний") і генерує такий масив:
=(=)
= (D2: D14 = "Присутні")
Вихід:
{TRUE; ПОМИЛКОВИЙ; ПОМИЛКОВИЙ; ІСТИНА; ПОМИЛКОВИЙ; TURE; TURE; TURE; ПОМИЛКОВИЙ; ПОМИЛКОВИЙ; ІСТИНА; ПОМИЛКОВИЙ; ІСТИНА}
Зауважте, що вихідні дані у першому масиві нашої формули ігнорують, чи рядок видимий, чи ні, і тут нам допомагає другий масив.
Масив видимості
Використовуючи SUBTOTAL для виключення невидимих рядків у нашому діапазоні, ми можемо створити наш масив видимості. Однак лише SUBTOTAL поверне єдине значення, тоді як SUMPRODUCT очікує масив значень. Щоб обійти це, ми використовуємо OFFSET для передачі одного рядка за раз. Цей метод вимагає подачі OFFSET масиву, який містить одне число одночасно. Другий масив виглядає так:
= ПІДСУМКИ (3, ЗМІСТ (, РЯД ()-МІН (РЯД ()), 0))
= ПІДСУМКОВИЙ (3, ЗМІСТ (D2, РЯД (D2: D14) -МІН (РЯД (D2: D14)), 0))
Вихід:
{1;1;0;0;1;1}
Зшивання двох разом:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
ПІДСУМКОВИЙ IF якщо з кількома критеріями
Щоб додати декілька критеріїв, просто додайте ще декілька критеріїв разом у SUMPRODUCT так:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= ПІДСУМКОВИЙ ((E2: E14 = "Відвідуваний")*(B2: B14 = 2019)*(ПІДСУМКИЙ (3, ЗМІСТ (E2, РЯД (E2: E14) -МІН (РЯД (E2: E14)), 0)) ))
ПІДСУМКОВИЙ IF у Google Таблицях
Функція SUBTOTAL IF працює точно так само в Google Таблицях, як і в Excel:
