Laadige alla töövihiku näide
See õpetus näitab, kuidas arvutada “vahekokku, kui”, lugedes ainult nähtavaid ridu koos kriteeriumidega.
Funktsioon SUBTOTAL
Funktsioon SUBTOTAL võib erinevate andmete (arv, summa, keskmine jne) põhjal teha erinevaid arvutusi. Kõige tähtsam on see, et seda saab kasutada ainult nähtavate (filtreeritud) ridade arvutamiseks. Selles näites kasutame funktsiooni (COUNTA) nähtavate ridade loendamiseks, seades argumendi SUBTOTAL function_num väärtuseks 3 (võimalike funktsioonide täieliku loendi leiate siit.)
= VAHESUMMA (3, $ D $ 2: $ D $ 14)
Pange tähele, kuidas tulemused muutuvad, kui ridu käsitsi filtreerime.
KUI VAHESUMMA
„Vahesumma If” loomiseks kasutame massiivivalemis kombinatsiooni SUMPRODUCT, SUBTOTAL, OFFSET, ROW ja MIN. Seda kombinatsiooni kasutades saame sisuliselt luua üldise funktsiooni „SUBTOTAL IF”. Lähme läbi näite.
Meil on iga ürituse jaoks liikmete nimekiri ja nende kohalolu:
Oletame, et meil palutakse loendada liikmete arv, kes on üritusel dünaamiliselt osalenud, kui filtreerime loendi käsitsi järgmiselt.
Selle saavutamiseks saame kasutada järgmist valemit:
= SUMPRODUCT ((=)*(VAHESUMMA (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((D2: D14 = "Attended")*(VAHESUMMA (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Kui kasutate Excel 2022 ja varasemaid versioone, peate massiivi valemi sisestama, vajutades CTRL + SHIFT + ENTER öelda Excelile, et sisestate massiivivalemi. Teate, et valem on massiivivalemina õigesti sisestatud, kui valemi ümber on lokkisulud (vt ülaltoodud pilti).
Kuidas valem töötab?
Valem töötab korrutades kaks massiivi SUMPRODUCTi sees, kus esimene massiiv käsitleb meie kriteeriume ja teine massiivi filtrid ainult nähtavatele ridadele:
= SUMPRODUCT (*)
Kriteeriumide massiiv
Kriteeriumide massiiv hindab meie väärtuste vahemiku iga rida (selles näites olek „Käidud”) ja genereerib järgmise massiivi:
=(=)
= (D2: D14 = "Osalenud")
Väljund:
{TÕSI; VÄÄR; VÄÄR; TÕSI; VÄÄR; TURE; TURE; TURE; VÄÄR; VÄÄR; TÕSI; VÄÄR; TÕSI}
Pange tähele, et meie valemi esimese massiivi väljund ignoreerib seda, kas rida on nähtav või mitte, kus on abiks meie teine massiiv.
Nähtavuse massiiv
Kasutades SUBTOTAL, et välistada meie vahemikust nähtamatud read, saame luua oma nähtavuse massiivi. Ainuüksi SUBTOTAL tagastab aga ühe väärtuse, samas kui SUMPRODUCT ootab väärtuste massiivi. Selle lahendamiseks kasutame OFFSET -i, et läbida üks rida korraga. See tehnika nõuab OFFSET -i toitmist massiivi, mis sisaldab ühte numbrit korraga. Teine massiiv näeb välja selline:
= VAHESUMMA (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= VAHESUMMA (3, NIIRE (D2, RIDA (D2: D14) -MIN (RIDA (D2: D14)), 0))
Väljund:
{1;1;0;0;1;1}
Nende kahe ühendamine:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
VAHESUMMA KUI mitme kriteeriumiga
Mitme kriteeriumi lisamiseks lihtsalt lisage SUMPRODUCTis veel mitu kriteeriumit järgmiselt.
= SUMPRODUCT ((=)*(=)*(VAHESUMMA (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))))
= SUMPRODUCT ((E2: E14 = "Attended")*(B2: B14 = 2019)*(VAHESUMMA (3, OFFSET (E2, ROW (E2: E14)) -MIN (ROW (E2: E14)), 0)) ))
KUI VAHESUMMA Google'i arvutustabelites
Funktsioon SUBTOTAL IF toimib Google'i arvutustabelites täpselt samamoodi nagu Excelis:
