Sammendrag
For å telle rader med ELLER logikk, kan du bruke en formel basert på SUMPRODUCT-funksjonen. I eksemplet vist er formelen i G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
hvor gruppe (B5: B15), farge1 (C5: C15) og farge2 (D5: D15) er kalt områder.
Forklaring
Et av de vanskeligste problemene i Excel er å telle rader i et datasett med "ELLER logikk". Det er to grunnleggende scenarier: (1) du vil telle rader hvor en verdi i en kolonne er "x" ELLER "y" (2) du vil telle rader der en verdi, "x", finnes i en kolonne ELLER en annen .
I dette eksemplet er målet å telle rader der group = "a" OG Color1 ELLER Color2 er "rød". Dette betyr at vi jobber med scenario 2 ovenfor.
Med COUNTIFS
Først kan du nå COUNTIFS-funksjonen, som håndterer flere kriterier naturlig. COUNTIFS-funksjonen føyer seg imidlertid sammen med AND-logikken, så alle kriteriene må være SANNE for å være inkludert i tellingen:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Dette gjør COUNTIFS ubrukelig, med mindre vi bruker flere forekomster av COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Oversettelse: telle rader hvor gruppen er "a" og farge1 er "rød" + telle rader der gruppen er "a" og farge2 er "rød" - telle rader der gruppen er "a" og farge1 er "rød" og farge2 er " rød "(for å unngå dobbel telling).
Dette fungerer, men du kan se at dette er en litt komplisert og overflødig formel.
Med boolsk logikk
En bedre løsning er å bruke boolsk logikk, og behandle resultatet med SUMPRODUCT-funksjonen. (Hvis du trenger en primer på boolsk algebra, gir denne videoen en introduksjon.) I eksemplet vist er formelen i G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
hvor gruppe (B5: B15), farge1 (C5: C15) og farge2 (D5: D15) er kalt områder.
Den første delen av problemet er å teste for gruppe = "a" som vi gjør slik:
(group="a")
Fordi området B5: B15 inneholder 11 celler, returnerer dette uttrykket en matrise med 11 SANNE og FALSE verdier slik:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Hver SANT representerer en rad der gruppen er "A".
Deretter må vi se etter verdien "rød" i enten kolonne1 eller kolonne2. Vi gjør dette med to uttrykk sammenføyd med tillegg (+), siden tillegg tilsvarer ELLER logikk i boolsk algebra:
(color1="red")+(color2="red")
Excel evaluerer automatisk SANTE og FALSE verdier som 1s og 0s under en hvilken som helst matematikkoperasjon, så resultatet fra ovennevnte uttrykk er en matrise som dette:
(2;0;0;1;1;0;1;0;0;0;1)
Det første tallet i matrisen er 2, fordi både Color1 og Color2 er "rød" på første rad. Av grunner som er forklart nedenfor, må vi beskytte oss mot denne situasjonen ved å se etter verdier større enn null:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nå har vi igjen en rekke sanne og falske verdier:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tabellen nedenfor oppsummerer hvordan Excel evaluerer fargelogikken forklart ovenfor:
På dette punktet har vi resultater fra å teste Group = "a" i en matrise:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Og resultater fra å teste "rødt" i Color1 eller Color2 i en annen matrise:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Det neste trinnet er å bringe disse to matriser sammen med "OG logikk". For å gjøre dette bruker vi multiplikasjon (*), siden multiplikasjon tilsvarer OG-logikk i boolsk algebra.
Etter å ha multiplisert de to gruppene sammen, har vi en enkelt matrise på 1s og 0s, som leveres direkte til SUMPRODUCT-funksjonen:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
SUMPRODUCT-funksjonen returnerer summen av tall, 2, som et endelig resultat. Dette er antall rader der group = "a" OG Color1 ELLER Color2 er "rød".
For å unngå dobbelttelling
Vi ønsker ikke å doble telle rader der både Color1 og Color2 er "røde". Dette er grunnen til at vi sjekker resultatene fra (color1 = "red") + (color2 = "red") for verdier større enn null i koden nedenfor:
((color1="red")+(color2="red"))>0
Uten denne kontrollen vil 2 fra første rad i dataene dukke opp i den endelige matrisen, og føre til at formelen feilaktig returnerer 3 som endelig telling.
FILTER-alternativ
En fin ting med boolsk logikk er at den fungerer perfekt med Excels nyeste funksjoner, som XLOOKUP og FILTER. For eksempel kan FILTER-funksjonen bruke nøyaktig samme logikk som forklart ovenfor for å trekke ut samsvarende rader:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Resultatet fra FILTER er de to radene som oppfyller kriteriene, som vist nedenfor:
Hvis du vil lære mer om disse nye funksjonene, har vi oversikt og videoopplæring.
