Resumé
For at tælle rækker med ELLER logik kan du bruge en formel baseret på SUMPRODUCT-funktionen. I det viste eksempel er formlen i G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
hvor gruppe (B5: B15), farve1 (C5: C15) og farve2 (D5: D15) er navngivet områder.
Forklaring
Et af de sværere problemer i Excel er at tælle rækker i et datasæt med "ELLER logik". Der er to grundlæggende scenarier: (1) du vil tælle rækker, hvor en værdi i en kolonne er "x" ELLER "y" (2) du vil tælle rækker, hvor en værdi, "x", findes i en kolonne ELLER en anden .
I dette eksempel er målet at tælle rækker, hvor group = "a" OG Color1 ELLER Color2 er "rød". Dette betyder, at vi arbejder med scenarie 2 ovenfor.
Med COUNTIFS
Du kan først nå ud til COUNTIFS-funktionen, der håndterer flere kriterier indbygget. COUNTIFS-funktionen forbinder dog betingelser med AND-logik, så alle kriterier skal være SAND for at være inkluderet i optællingen:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Dette gør COUNTIFS ubrugelig, medmindre vi bruger flere forekomster af COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Oversættelse: tæl rækker hvor gruppe er "a" og farve1 er "rød" + tæl rækker hvor gruppe er "a" og farve2 er "rød" - tæl rækker hvor gruppe er "a" og farve1 er "rød" og farve2 er " rød "(for at undgå dobbelt optælling).
Dette fungerer, men du kan se, at dette er en noget kompliceret og overflødig formel.
Med boolsk logik
En bedre løsning er at bruge boolsk logik og behandle resultatet med SUMPRODUCT-funktionen. (Hvis du har brug for en primer på boolsk algebra, giver denne video en introduktion.) I det viste eksempel er formlen i G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
hvor gruppe (B5: B15), farve1 (C5: C15) og farve2 (D5: D15) er navngivet områder.
Den første del af problemet er at teste for gruppe = "a", som vi gør sådan her:
(group="a")
Da området B5: B15 indeholder 11 celler, returnerer dette udtryk en matrix med 11 SANDE og FALSKE værdier som denne:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Hver SAND repræsenterer en række, hvor gruppen er "A".
Dernæst skal vi kontrollere værdien "rød" i enten kolonne1 eller kolonne2. Vi gør dette med to udtryk sammenføjet med tilføjelse (+), da tilføjelse svarer til ELLER logik i boolsk algebra:
(color1="red")+(color2="red")
Excel evaluerer automatisk SANDE og FALSKE værdier som 1s og 0s under enhver matematikhandling, så resultatet af ovenstående udtryk er en matrix som denne:
(2;0;0;1;1;0;1;0;0;0;1)
Det første tal i arrayet er 2, fordi både Color1 og Color2 er "røde" i første række. Af grunde forklaret nedenfor er vi nødt til at beskytte mod denne situation ved at kontrollere værdier større end nul:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nu har vi igen en række TRUE og FALSE værdier:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tabellen nedenfor opsummerer, hvordan Excel evaluerer farvelogikken forklaret ovenfor:
På dette tidspunkt har vi resultater fra at teste Group = "a" i en matrix:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Og resultater fra testning af "rødt" i Color1 eller Color2 i et andet array:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Det næste trin er at bringe disse to arrays sammen med "AND logic". For at gøre dette bruger vi multiplikation (*), da multiplikation svarer til OG-logik i boolsk algebra.
Efter at have multipliceret de to arrays sammen har vi et enkelt array på 1s og 0s, som leveres direkte til SUMPRODUCT-funktionen:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
SUMPRODUCT-funktionen returnerer summen af tal, 2, som et endeligt resultat. Dette er antallet af rækker, hvor group = "a" OG Color1 ELLER Color2 er "rød".
For at undgå dobbelt optælling
Vi ønsker ikke at fordoble antallet af rækker, hvor både Color1 og Color2 er "røde". Det er derfor, vi kontrollerer resultaterne fra (color1 = "rød") + (color2 = "rød") for værdier større end nul i nedenstående kode:
((color1="red")+(color2="red"))>0
Uden denne kontrol ville de 2 fra første række i dataene dukke op i den endelige matrix og få formlen til at forkert returnere 3 som det endelige antal.
FILTER-indstilling
En dejlig ting ved boolsk logik er, at den fungerer perfekt med Excels nyeste funktioner, som XLOOKUP og FILTER. For eksempel kan FILTER-funktionen bruge nøjagtig den samme logik, der er forklaret ovenfor, til at udtrække matchende rækker:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Resultatet fra FILTER er de to rækker, der opfylder kriterierne, som vist nedenfor:
Hvis du gerne vil vide mere om disse nye funktioner, har vi et overblik og videotræning.
