Изтеглете примерната работна книга
Този урок ще покаже как да се изчисли „междинна сума ако“, като се броят само видимите редове с критерии.
SUBTOTAL функция
Функцията SUBTOTAL може да извършва различни изчисления за диапазон от данни (брой, сума, средна стойност и т.н.). Най -важното е, че може да се използва за изчисляване само на видими (филтрирани) редове. В този пример ще използваме функцията за преброяване на (COUNTA) видими редове, като зададем аргумента SUBTOTAL function_num на 3 (Пълен списък с възможни функции можете да намерите тук.)
= СУБТОТАЛ (3, $ D $ 2: $ D $ 14)
Забележете как се променят резултатите, докато ръчно филтрираме редове.
ПОДБОТА АКО
За да създадем „междинна сума ако“, ще използваме комбинация от SUMPRODUCT, SUBTOTAL, OFFSET, ROW и MIN във формула на масив. Използвайки тази комбинация, можем по същество да създадем обща функция „SUBTOTAL IF“. Нека преминем през пример.
Имаме списък на членовете и статуса им на присъствие за всяко събитие:
Да предположим, че сме помолени да преброим броя на членовете, които са посещавали динамично събитие, докато ръчно филтрираме списъка така:
За да постигнем това, можем да използваме тази формула:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Присъстващ")*(ПОДБОТА (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Когато използвате Excel 2022 и по -ранни версии, трябва да въведете формулата на масива, като натиснете CTRL + SHIFT + ENTER за да кажете на Excel, че въвеждате формула за масив. Ще знаете, че формулата е въведена правилно като формула на масив, когато около формулата се появят къдрави скоби (вижте изображението по -горе).
Как действа формулата?
Формулата работи чрез умножаване на два масива вътре в SUMPRODUCT, където първият масив се занимава с нашите критерии, а вторият масив филтрира само до видими редове:
= СУМПРОДУКТ (*)
Масив от критерии
Масивът с критерии оценява всеки ред в нашия диапазон от стойности („Присъстващ“ статус в този пример) и генерира масив по следния начин:
=(=)
= (D2: D14 = "Присъства")
Изход:
{ВЯРНО; FALSE; FALSE; ВЯРНО; FALSE; TURE; TURE; TURE; FALSE; FALSE; ВЯРНО; FALSE; ВЯРНО}
Имайте предвид, че изходът в първия масив във формулата ни игнорира дали редът е видим или не, което е мястото, където вторият ни масив идва на помощ.
Масив от видимост
Използвайки SUBTOTAL за изключване на невидими редове в нашия диапазон, можем да генерираме нашия масив за видимост. Само SUBTOTAL обаче ще върне единична стойност, докато SUMPRODUCT очаква масив от стойности. За да заобиколим това, използваме OFFSET за предаване на един ред наведнъж. Тази техника изисква подаване на OFFSET на масив, който съдържа едно число наведнъж. Вторият масив изглежда така:
= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (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, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
ПОДБОТА АКО в Google Таблици
Функцията SUBTOTAL IF работи абсолютно същото в Google Sheets, както и в Excel:
