Povzetek
Če želite šteti vrstice z logiko ALI, lahko uporabite formulo, ki temelji na funkciji SUMPRODUCT. V prikazanem primeru je formula v G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kjer so skupine (B5: B15), color1 (C5: C15) in color2 (D5: D15) imenovane obsegi.
Pojasnilo
Ena izmed bolj zapletenih težav v Excelu je štetje vrstic v nizu podatkov z "ALI logiko". Obstajata dva osnovna scenarija: (1) želite prešteti vrstice, kjer je vrednost v stolpcu "x" ALI "y" (2) želite prešteti vrstice, kjer vrednost "x" obstaja v enem stolpcu ALI drugem .
V tem primeru je cilj prešteti vrstice, kjer je group = "a" IN Color1 ALI Color2 "rdeča". To pomeni, da delamo s scenarijem 2 zgoraj.
Z COUNTIFS
Najprej boste morda posegli po funkciji COUNTIFS, ki izvorno obravnava več meril. Vendar pa funkcija COUNTIFS združi pogoje z logiko AND, zato morajo biti vsa merila TRUE, da bodo vključena v štetje:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Zaradi tega COUNTIFS ni izvedljiv, razen če uporabimo več primerkov COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Prevod: štetje vrstic, kjer je skupina "a" in color1 "rdeča" + štetje vrstic, kjer je skupina "a" in color2 "rdeča" - štetje vrstic, kjer je skupina "a" in color1 "rdeča" in barva2 " rdeča "(v izogib dvojnemu štetju).
To deluje, vendar vidite, da je to nekoliko zapletena in odvečna formula.
Z logično logiko
Boljša rešitev je uporaba logične logike in rezultat obdelati s funkcijo SUMPRODUCT. (Če potrebujete temeljni primer o logični algebri, ta video nudi uvod.) V prikazanem primeru je formula v G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kjer so skupine (B5: B15), color1 (C5: C15) in color2 (D5: D15) imenovane obsegi.
Prvi del težave je preizkusiti skupino = "a", ki jo naredimo takole:
(group="a")
Ker obseg B5: B15 vsebuje 11 celic, ta izraz vrne polje 11 TRUE in FALSE vrednosti, kot je ta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Vsaka TRUE predstavlja vrstico, v kateri je skupina "A".
Nato moramo v stolpcu1 ali stolpcu2 preveriti vrednost "rdeča". To naredimo z dvema izrazoma, ki ju povezuje dodatek (+), saj dodajanje ustreza logiki ALI v logični algebri:
(color1="red")+(color2="red")
Excel med katero koli matematično operacijo samodejno oceni vrednosti TRUE in FALSE kot 1s in 0s, zato je rezultat iz zgornjega izraza matrika, kot je ta:
(2;0;0;1;1;0;1;0;0;0;1)
Prva številka v matriki je 2, ker sta Color1 in Color2 "rdeča" v prvi vrstici. Iz spodaj pojasnjenih razlogov se moramo tega stanja zaščititi s preverjanjem vrednosti, večjih od nič:
((2;0;0;1;1;0;1;0;0;0;1))>0
Zdaj imamo spet vrsto TRUE in FALSE vrednosti:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Spodnja tabela povzema, kako Excel ocenjuje zgoraj razloženo barvno logiko:
Na tej točki imamo rezultate testiranja Group = "a" v enem polju:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
In rezultati testiranja "rdeče" v Color1 ali Color2 v drugem polju:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Naslednji korak je združitev teh dveh nizov z "AND logiko". Za to uporabimo množenje (*), saj množenje ustreza logiki AND v logični algebri.
Po pomnožitvi obeh nizov imamo eno matriko 1s in 0s, ki je dostavljena neposredno v funkcijo SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkcija SUMPRODUCT vrne vsoto števil 2 kot končni rezultat. To je število vrstic, kjer je group = "a" IN Color1 ALI Color2 "rdeč".
Da bi se izognili dvojnemu štetju
Ne želimo dvojno šteti vrstic, kjer sta Color1 in Color2 "rdeča". Zato rezultate iz (color1 = "red") + (color2 = "red") preverimo za vrednosti, večje od nič v spodnji kodi:
((color1="red")+(color2="red"))>0
Brez tega preverjanja se bo 2 iz prve vrstice v podatkih prikazalo v končni matriki in povzročilo, da bo formula napačno vrnila 3 kot končno štetje.
Možnost FILTER
Ena lepa stvar logike logike je, da odlično deluje z najnovejšimi funkcijami Excela, kot sta XLOOKUP in FILTER. Na primer, funkcija FILTER lahko za izločanje ujemajočih se vrstic uporablja popolnoma enako logiko, kot je pojasnjena zgoraj:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Rezultat FILTER je dve vrstici, ki izpolnjujeta merila, kot je prikazano spodaj:
Če želite izvedeti več o teh novih funkcijah, imamo pregled in video trening.
