Преузмите пример радне свеске
Овај водич ће показати како израчунати „међузбир ако“, рачунајући само видљиве редове са критеријумима.
СУБТОТАЛ функција
Функција СУБТОТАЛ може извршити различите прорачуне за низ података (број, збир, просек итд.). Што је најважније, може се користити за израчунавање само на видљивим (филтрираним) редовима. У овом примеру ћемо користити функцију за пребројавање (ЦОУНТА) видљивих редова постављањем аргумента СУБТОТАЛ фунцтион_нум на 3 (Потпуна листа могућих функција може се пронаћи овде.)
= СУБТОТАЛ (3, $ Д $ 2: $ Д $ 14)
Обратите пажњу на то како се резултати мењају док ручно филтрирамо редове.
СУБТОТАЛ ИФ
Да бисмо створили „међузбир Иф“, користићемо комбинацију СУМПРОДУЦТ, СУБТОТАЛ, ОФФСЕТ, РОВ и МИН у формули низа. Користећи ову комбинацију, у суштини можемо створити генеричку функцију „СУБТОТАЛ ИФ“. Прођимо кроз пример.
Имамо списак чланова и њихов статус присутности за сваки догађај:
Претпоставимо да се од нас тражи да бројимо чланове који су динамично присуствовали догађају док ручно филтрирамо листу на следећи начин:
Да бисмо то постигли, можемо користити ову формулу:
= СУМПРОДУЦТ ((=)*(СУБТОТАЛ (3, ОФФСЕТ (, РОВ ()-МИН (РОВ ()), 0)))))
= СУМПРОДУЦТ ((Д2: Д14 = "Присуствовао")*(СУБТОТАЛ (3, ОФФСЕТ (Д2, РОВ (Д2: Д14) -МИН (РОВ (Д2: Д14)), 0))))
Када користите Екцел 2022 и старије верзије, морате унети формулу низа притиском на ЦТРЛ + СХИФТ + ЕНТЕР да бисте рекли Екцелу да уносите формулу низа. Знаћете да је формула правилно унета као формула низа када се око формуле појављују витичасте заграде (погледајте слику изнад).
Како функционише формула?
Формула функционише тако што се множе два низа унутар СУМПРОДУЦТ -а, при чему се први низ бави нашим критеријумима, а други филтрира само у видљиве редове:
= СУМПРОДУЦТ (*)
Низ критеријума
Низ критеријума процењује сваки ред у нашем опсегу вредности (статус „Присутан“ у овом примеру) и генерише низ овако:
=(=)
= (Д2: Д14 = "Присуствовао")
Излаз:
{ИСТИНА; ФАЛСЕ; ФАЛСЕ; ИСТИНА; ФАЛСЕ; ТУРЕ; ТУРЕ; ТУРЕ; ФАЛСЕ; ФАЛСЕ; ИСТИНА; ФАЛСЕ; ИСТИНА}
Имајте на уму да излаз у првом низу у нашој формули занемарује да ли је ред видљив или не, и ту нам помаже други низ.
Низ видљивости
Користећи СУБТОТАЛ за искључивање невидљивих редова у нашем опсегу, можемо генерисати наш низ видљивости. Међутим, само СУБТОТАЛ ће вратити једну вредност, док СУМПРОДУЦТ очекује низ вредности. Да бисмо заобишли ово, користимо ОФФСЕТ да проследимо један по један ред. Ова техника захтева уношење ОФФСЕТ -а низа који садржи један број у исто време. Други низ изгледа овако:
= СУБТОТАЛ (3, ОФФСЕТ (, РОВ ()-МИН (РОВ ()), 0))
= СУБТОТАЛ (3, ОФФСЕТ (Д2, РОВ (Д2: Д14) -МИН (РОВ (Д2: Д14)), 0))
Излаз:
{1;1;0;0;1;1}
Спајање њих двоје заједно:
= СУМПРОДУЦТ ({ТРУЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ТРУЕ} * {1; 1; 0; 0; 1; 1})
= 4
СУБТОТАЛ АКО са више критеријума
Да бисте додали више критеријума, једноставно још више критеријума заједно у оквиру СУМПРОДУЦТ -а, на следећи начин:
= СУМПРОДУЦТ ((=)*(=)*(СУБТОТАЛ (3, ОФФСЕТ (, РОВ ()-МИН (РОВ ()), 0)))))
= СУМПРОДУЦТ ((Е2: Е14 = "Присуствовао")*(Б2: Б14 = 2019)*(СУБТОТАЛ (3, ОФФСЕТ (Е2, РОВ (Е2: Е14) -МИН (РОВ (Е2: Е14)), 0)) ))
СУБТОТАЛ ИФ у Гоогле табелама
СУБТОТАЛ ИФ функција ради потпуно исто у Гоогле табелама као и у Екцелу:
