Sažetak
Za brojanje redaka s logikom ILI možete upotrijebiti formulu koja se temelji na funkciji SUMPRODUCT. U prikazanom primjeru formula u G6 je:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
gdje su skupine (B5: B15), boja1 (C5: C15) i boja2 (D5: D15) imenovani rasponi.
Obrazloženje
Jedan od problematičnijih problema u Excelu je brojanje redaka u skupu podataka s "ILI logikom". Dva su osnovna scenarija: (1) želite brojati retke gdje je vrijednost u stupcu "x" ILI "y" (2) želite brojati retke u kojima vrijednost "x" postoji u jednom stupcu ILI drugom .
U ovom primjeru cilj je brojati redove u kojima je group = "a" I Boja1 ILI Boja2 "crvena". To znači da radimo sa scenarijem 2 gore.
S COUNTIFS
U početku biste mogli posegnuti za funkcijom COUNTIFS koja izvorno obrađuje više kriterija. Međutim, funkcija COUNTIFS pridružuje uvjete logikom AND, pa svi kriteriji moraju biti TRUE da bi se uključili u count:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
To čini COUNTIFS neizvodljivim, osim ako ne koristimo više instanci COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Prijevod: broji redove gdje je grupa "a", a color1 je "crvena" + broji redove gdje je grupa "a", a color2 je "crvena" - broji redove gdje je grupa "a", a color1 je "crvena" i color2 je " crvena "(kako bi se izbjeglo dvostruko brojanje).
To djeluje, ali vidite da je ovo pomalo komplicirana i suvišna formula.
S logičkom logikom
Bolje rješenje je uporaba logičke logike i obrađivanje rezultata pomoću funkcije SUMPRODUCT. (Ako vam treba početni kurs o logičkoj algebri, ovaj video daje uvod.) U prikazanom primjeru formula u G6 je:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
gdje su skupine (B5: B15), boja1 (C5: C15) i boja2 (D5: D15) imenovani rasponi.
Prvi dio problema je testiranje za grupu = "a" što radimo ovako:
(group="a")
Budući da raspon B5: B15 sadrži 11 ćelija, ovaj izraz vraća niz od 11 TRUE i FALSE vrijednosti poput ove:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Svaka ISTINA predstavlja red u kojem je grupa "A".
Dalje, moramo provjeriti vrijednost "crvena" bilo u stupcu1 ili u stupcu2. To radimo s dva izraza spojena sabiranjem (+), budući da zbrajanje odgovara logici OR u logičkoj algebri:
(color1="red")+(color2="red")
Excel automatski procjenjuje vrijednosti TRUE i FALSE kao 1 i 0 tijekom bilo koje matematičke operacije, tako da je rezultat iz gornjeg izraza niz poput ovog:
(2;0;0;1;1;0;1;0;0;0;1)
Prvi broj u polju je 2, jer su i Color1 i Color2 "crveni" u prvom redu. Iz dolje objašnjenih razloga moramo se zaštititi ove situacije provjerom vrijednosti veće od nule:
((2;0;0;1;1;0;1;0;0;0;1))>0
Sada opet imamo niz TRUE i FALSE vrijednosti:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tablica u nastavku sažima kako Excel procjenjuje gore objašnjenu logiku boja:
U ovom trenutku imamo rezultate testiranja Group = "a" u jednom nizu:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
I rezultati testiranja "crvene" boje Color1 ili Color2 u drugom nizu:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Sljedeći je korak spojiti ova dva niza s "I logikom". Da bismo to učinili, koristimo množenje (*), jer množenje odgovara logici AND u logičkoj algebri.
Nakon množenja dva polja zajedno, imamo jedan niz od 1 i 0, koji se isporučuje izravno u funkciju SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkcija SUMPRODUCT vraća zbroj brojeva 2 kao konačni rezultat. Ovo je broj redaka u kojima je group = "a" I Boja1 ILI Boja2 "crvena".
Da bi se izbjeglo dvostruko brojanje
Ne želimo dvostruko brojati retke u kojima su i Color1 i Color2 "crveni". Zbog toga provjeravamo rezultate iz (color1 = "red") + (color2 = "red") za vrijednosti veće od nule u donjem kodu:
((color1="red")+(color2="red"))>0
Bez ove provjere, 2 iz prvog retka podataka prikazalo bi se u konačnom nizu i uzrokovalo da formula pogrešno vrati 3 kao konačni broj.
Opcija FILTER
Jedna lijepa stvar kod logičke logike je ta što ona savršeno funkcionira s najnovijim Excelovim funkcijama, kao što su XLOOKUP i FILTER. Na primjer, funkcija FILTER može koristiti potpuno istu logiku gore objašnjenu za izdvajanje odgovarajućih redaka:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Rezultat iz FILTER-a su dva retka koja zadovoljavaju kriterije, kao što se vidi u nastavku:
Ako želite saznati više o tim novim funkcijama, imamo pregled i video trening.
