rezumat
Pentru a număra rânduri cu logică SAU, puteți utiliza o formulă bazată pe funcția SUMPRODUCT. În exemplul prezentat, formula din G6 este:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
unde grupul (B5: B15), color1 (C5: C15) și color2 (D5: D15) sunt denumite intervale.
Explicaţie
Una dintre problemele mai dificile din Excel este de a număra rânduri într-un set de date cu „logică SAU”. Există două scenarii de bază: (1) doriți să numărați rânduri în care o valoare dintr-o coloană este „x” SAU „y” (2) doriți să numărați rânduri în care o valoare, „x”, există într-o coloană SAU alta .
În acest exemplu, obiectivul este de a număra rânduri în care grupul = "a" ȘI Color1 SAU Color2 sunt "roșii". Aceasta înseamnă că lucrăm cu scenariul 2 de mai sus.
Cu COUNTIFS
La început ați putea ajunge la funcția COUNTIFS, care gestionează mai multe criterii în mod nativ. Cu toate acestea, funcția COUNTIFS unește condiții cu logica AND, deci toate criteriile trebuie să fie ADEVĂRATE pentru a fi incluse în numărare:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Acest lucru face ca COUNTIFS să nu poată fi realizat, cu excepția cazului în care folosim mai multe instanțe de COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Traducere: numărați rândurile în care grupul este „a” și culoarea1 este „roșie” + numărați rândurile în care grupul este „a” și culoarea2 este „roșu” - numărați rândurile în care grupul este „a” și culoarea1 este „roșu” și culoarea2 este „ roșu "(pentru a evita dubla numărare).
Acest lucru funcționează, dar puteți vedea că aceasta este o formulă oarecum complicată și redundantă.
Cu logică booleană
O soluție mai bună este să utilizați logica booleană și să procesați rezultatul cu funcția SUMPRODUCT. (Dacă aveți nevoie de un manual despre algebra booleană, acest videoclip oferă o introducere.) În exemplul prezentat, formula din G6 este:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
unde grupul (B5: B15), color1 (C5: C15) și color2 (D5: D15) sunt denumite intervale.
Prima parte a problemei constă în testarea pentru grupul = "a", care ne place astfel:
(group="a")
Deoarece intervalul B5: B15 conține 11 celule, această expresie returnează o matrice de 11 valori ADEVĂRATE și FALSE ca aceasta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Fiecare ADEVĂRAT reprezintă un rând în care grupul este „A”.
În continuare, trebuie să verificăm valoarea „roșu” fie în coloana1, fie în coloana2. Facem acest lucru cu două expresii unite prin adunare (+), deoarece adunarea corespunde cu logica OR din algebra booleană:
(color1="red")+(color2="red")
Excel evaluează automat valorile ADEVĂRATE și FALSE ca 1s și 0s în timpul oricărei operații matematice, astfel încât rezultatul din expresia de mai sus este o matrice ca aceasta:
(2;0;0;1;1;0;1;0;0;0;1)
Primul număr din matrice este 2, deoarece atât Color1, cât și Color2 sunt „roșii” în primul rând. Din motivele explicate mai jos, trebuie să ne ferim de această situație verificând valori mai mari decât zero:
((2;0;0;1;1;0;1;0;0;0;1))>0
Acum avem din nou o serie de valori ADEVĂRATE și FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tabelul de mai jos rezumă modul în care Excel evaluează logica culorilor explicată mai sus:
În acest moment, avem rezultate din testarea grupului = "a" într-o matrice:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Și rezultatele testării „roșu” în Color1 sau Color2 într-o altă matrice:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Următorul pas este de a aduce aceste două matrice împreună cu „ȘI logica”. Pentru a face acest lucru, folosim înmulțirea (*), deoarece înmulțirea corespunde logicii ȘI din algebra booleană.
După înmulțirea celor două matrice împreună, avem o singură matrice de 1s și 0s, care este livrată direct la funcția SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funcția SUMPRODUCT returnează suma numerelor, 2, ca rezultat final. Acesta este numărul de rânduri în care grupul = "a" ȘI Color1 SAU Color2 sunt "roșii".
Pentru a evita dubla numărare
Nu vrem să numărăm dublu rândurile în care atât Color1 cât și Color2 sunt „roșii”. Acesta este motivul pentru care verificăm rezultatele din (color1 = "roșu") + (color2 = "roșu") pentru valori mai mari decât zero în codul de mai jos:
((color1="red")+(color2="red"))>0
Fără această verificare, 2 din primul rând din date ar apărea în matricea finală și ar determina formula să returneze incorect 3 ca număr final.
Opțiunea FILTRU
Un lucru frumos despre logica booleană este că funcționează perfect cu cele mai noi funcții Excel, cum ar fi XLOOKUP și FILTER. De exemplu, funcția FILTER poate utiliza exact aceeași logică explicată mai sus pentru a extrage rândurile potrivite:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Rezultatul din FILTER sunt cele două rânduri care îndeplinesc criteriile, așa cum se vede mai jos:
Dacă doriți să aflați mai multe despre aceste noi funcții, avem o prezentare generală și un training video.
