Sammanfattning
För att räkna rader med ELLER-logik kan du använda en formel baserad på SUMPRODUCT-funktionen. I exemplet som visas är formeln i G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
där grupp (B5: B15), färg1 (C5: C15) och färg2 (D5: D15) heter områden.
Förklaring
Ett av de knepigare problemen i Excel är att räkna rader i en uppsättning data med "ELLER logik". Det finns två grundläggande scenarier: (1) du vill räkna rader där ett värde i en kolumn är "x" ELLER "y" (2) du vill räkna rader där ett värde, "x", finns i en kolumn ELLER en annan .
I det här exemplet är målet att räkna rader där group = "a" OCH Color1 ELLER Color2 är "röda". Det betyder att vi arbetar med scenario 2 ovan.
Med COUNTIFS
Du kan först nå COUNTIFS-funktionen, som hanterar flera kriterier inbyggt. COUNTIFS-funktionen sammanfogar dock villkor med AND-logiken, så alla kriterier måste vara SANT för att inkluderas i räkningen:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Detta gör COUNTIFS omöjligt, såvida vi inte använder flera instanser av COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Översättning: räkna rader där gruppen är "a" och färg1 är "röd" + räkna rader där gruppen är "a" och färg2 är "röd" - räkna rader där gruppen är "a" och färg1 är "röd" och färg2 är " röd "(för att undvika dubbelräkning).
Detta fungerar, men du kan se att detta är en något komplicerad och överflödig formel.
Med boolesk logik
En bättre lösning är att använda boolesk logik och bearbeta resultatet med SUMPRODUCT-funktionen. (Om du behöver en primer på boolesk algebra ger den här videon en introduktion.) I exemplet som visas är formeln i G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
där grupp (B5: B15), färg1 (C5: C15) och färg2 (D5: D15) heter områden.
Den första delen av problemet är att testa för grupp = "a" som vi gör så här:
(group="a")
Eftersom intervallet B5: B15 innehåller 11 celler returnerar detta uttryck en matris med 11 SANT och FALSKA värden så här:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Varje SANT representerar en rad där gruppen är "A".
Därefter måste vi söka efter värdet "röd" i antingen kolumn1 eller kolumn2. Vi gör detta med två uttryck förenade med addition (+), eftersom addition motsvarar ELLER-logik i boolesk algebra:
(color1="red")+(color2="red")
Excel utvärderar automatiskt SANT och FALSKA värden som 1s och 0s under alla matematiska operationer, så resultatet från ovanstående uttryck är en matris så här:
(2;0;0;1;1;0;1;0;0;0;1)
Det första numret i matrisen är 2, eftersom både Color1 och Color2 är "röda" i första raden. Av skäl som förklaras nedan måste vi skydda oss mot denna situation genom att kontrollera värden större än noll:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nu har vi återigen en rad sanna och falska värden:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tabellen nedan sammanfattar hur Excel utvärderar den färglogik som förklarats ovan:
Vid den här tiden har vi resultat från att testa Group = "a" i en matris:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Och resultat från att testa "rött" i Color1 eller Color2 i en annan array:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nästa steg är att föra samman dessa två matriser med "OCH logik". För att göra detta använder vi multiplikation (*), eftersom multiplikation motsvarar OCH-logik i boolesk algebra.
Efter att ha multiplicerat de två matriserna tillsammans har vi en enda grupp av 1s och 0s, som levereras direkt till SUMPRODUCT-funktionen:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funktionen SUMPRODUCT returnerar summan av siffror, 2, som ett slutresultat. Detta är antalet rader där group = "a" OCH Color1 ELLER Color2 är "röda".
För att undvika dubbelräkning
Vi vill inte dubbelt räkna rader där både Color1 och Color2 är "röda". Det är därför vi kontrollerar resultaten från (color1 = "red") + (color2 = "red") för värden större än noll i koden nedan:
((color1="red")+(color2="red"))>0
Utan denna kontroll skulle 2 från den första raden i data visas i den slutliga matrisen och orsaka att formeln felaktigt returnerar 3 som det slutliga antalet.
FILTER-alternativ
En trevlig sak med boolesk logik är att den fungerar perfekt med Excels nyaste funktioner, som XLOOKUP och FILTER. Till exempel kan FILTER-funktionen använda exakt samma logik som förklarats ovan för att extrahera matchande rader:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Resultatet från FILTER är de två raderna som uppfyller kriterierna, se nedan:
Om du vill lära dig mer om dessa nya funktioner har vi en översikt och videoutbildning.
