Countifs med OR -logik for flere kriterier i Excel
Normalt kan du bruge COUNTIFS -funktionen til at tælle celler baseret på en eller flere betingelser med AND -logik i Excel. Har du nogensinde oplevet en situation, hvor du har brug for at tælle mere end én værdi fra en enkelt kolonne eller et område af celler? Dette betyder at tælle med flere betingelser og ELLER logik. I dette tilfælde kan du anvende SUM og COUNTIFS -funktionerne sammen eller bruge SUMPRODUCT -funktionen.
Tæl celler med OR -betingelser i Excel
For eksempel har jeg en række data som vist under skærmbillede, nu vil jeg tælle antallet af produktet, der er "blyant" eller "lineal", her vil jeg diskutere to formler til løsning af denne opgave i Excel.
Tæl celler med OR -betingelser ved hjælp af SUM og COUNTIFS -funktioner
I Excel, for at tælle med flere OR -betingelser, kan du bruge SUM og COUNTIFS -funktionerne med en matrixkonstant, den generiske syntaks er:
- range: Dataområdet indeholder de kriterier, hvorfra du tæller celler fra;
- criterion1, criterion2, criterion3…: De betingelser, du vil tælle celler baseret på.
For at tælle antallet af produkter, der er "blyant" eller "lineal", skal du kopiere eller indtaste nedenstående formel i en tom celle og derefter trykke på Indtast nøgle for at få resultatet:
Forklaring til formlen:
= SUM (TÆLLER (B2: B13, {"blyant", "lineal"}))
- {"Blyant", "lineal"}: For det første skal du pakke alle betingelser i en matrixkonstant som denne: {"Blyant", "Lineal"}, adskil elementerne med kommaer.
- TÆLLE (B2: B13, {"blyant", "lineal"}): Denne COUNTIFS -funktion får individuelle tællinger for "blyant" og "lineal", og du får resultatet således: {2,3}.
- SUM (COUNTIFS (B2: B13, {"Pencil", "Lineal"})) = SUM ({2,3}): Endelig summerer denne SUM -funktion alle elementer i arrayet og returnerer resultatet: 5.
Tips: Du kan også bruge cellereferencer til kriterier, anvend venligst nedenstående matrixformel, og tryk derefter på Ctrl + Skift + Enter tasterne sammen for at få det korrekte resultat:
Tæl celler med OR -betingelser ved hjælp af SUMPRODUCT -funktionen
Her er en anden formel, der er skabt af SUMPRODUCT -funktionen, der også kan hjælpe med at tælle celler med OR -logik. Den generiske syntaks er:
- range: Dataområdet indeholder de kriterier, hvorfra du tæller celler fra;
- criterion1, criterion2, criterion3…: De betingelser, du vil tælle celler baseret på.
Kopier eller indtast følgende formel i en tom celle, og tryk på Indtast nøgle til at returnere resultatet:
Forklaring til formlen:
= SUMPRODUCT (1*(B2: B13 = {"blyant", "lineal"}))
- B2: B13 = {"blyant", "lineal"}: Dette udtryk sammenligner hvert kriterium "blyant", "lineal" med områdecelle B2: B13. Hvis kriteriet er opfyldt, returnerer det et SANDT, ellers vises et FALSKT, du får resultatet således: {SAND, FALSK; FALSK, FALSK; FALSK, FALSK; FALSK, SAND; FALSK, FALSK; SAND, FALSK ; FALSK, FALSK; FALSK, SAND; FALSK, FALSK; FALSK, FALSK; FALSK, SAND; FALSK, FALSK}.
- 1*(B2: B13 = {"blyant", "lineal"}): Multiplikation konverterer de logiske værdier - SAND og FALSK til 1 og 0, så det får resultatet således: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Endelig tilføjer SUMPRODUCT -funktionen alle tal i arrayet for at få resultatet: 5.
Tæl celler med flere sæt OR -betingelser i Excel
Nogle gange skal du muligvis tælle cellerne med to eller flere sæt OR -betingelser, i dette tilfælde kan du bruge enten SUM og COUNTIFS med en matrixkonstant eller SUMPRODUCT med ISNUMBER MATCH -funktioner.
Tæl celler med to sæt OR -betingelser ved hjælp af SUM og COUNTIFS -funktioner
For kun at håndtere to sæt OR -kriterier skal du blot tilføje endnu en matrixkonstant til COUNTIFS -formlen.
For eksempel har jeg dataintervallet som vist nedenfor på skærmbilledet, nu vil jeg tælle de personer, der bestilte "blyant" eller "lineal", og beløbet er enten <100 eller> 200.
Indtast eller kopier følgende formel til en celle, og tryk derefter på Indtast nøgle for at få resultatet, se skærmbillede:
Bemærk: I formlen skal du bruge et semikolon til den anden matrixkonstant, hvilket skaber et lodret array.
Tæl celler med flere sæt OR -betingelser ved hjælp af SUMPRODUCT -funktionen
Ovenstående formel fungerer kun for to sæt OR -kriterier. Hvis du skal tælle med flere kriterier, kan en kompleks SUMPRODUCT -formel sammen med ISNUMBER MATCH -funktioner hjælpe dig.
Tag f.eks. Nedenstående data for at tælle de personer, der bestilte "blyant" eller "lineal", og status er enten "Leveret" eller "I transit" og underskrevet af "Bob" eller "Eko", skal du anvende en kompleks formel .
Kopier eller indtast nedenstående formel i en tom celle, og tryk derefter på Indtast nøgle for at få beregningen, se skærmbillede:
Forklaring til formlen:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER (MATCH (B2: B13, {"blyant", "lineal"}, 0)):
- MATCH (B2: B13, {"blyant", "lineal"}, 0): Denne MATCH -funktion bruges til at sammenligne hver celle i område B2: B13 med den tilsvarende array -konstant. Hvis matchet findes, returnerer det en relativ position for værdien i arrayet, ellers vises en fejlværdi. Så får du arraylisten som denne: {1;#N/A;#N/A; 2;#N/A; 1;#N/A; 2; 1;#N/A; 2;# Ikke relevant}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): ISNUMBER -funktionen konverterer tallene til SAND og fejlværdier til FALSK som dette: {SAND; FALSK; FALSK; SAND; FALSK; SAND; FALSK; SAND; SAND; FALSK; FALSK}.
Ovenstående logik kan også anvendes på det andet og tredje ISNUMBER udtryk.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Derefter multipliceres disse tre arrays sammen i SUMPRODUCT, som automatisk konverterer SANDE og FALSKE værdier til 1s og 0s som en del af matematikoperationen sådan: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} *{1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} *{1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}) = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}).
- Endelig summerer SUMPRODUCT -funktionen alle tal i arrayet for at få resultatet: 3.
Relativ funktion anvendt:
- SUM:
- Funktionen Excel SUM returnerer summen af de leverede værdier.
- TÆLLE:
- COUNTIF-funktionen er en statistisk funktion i Excel, der bruges til at tælle antallet af celler, der opfylder et kriterium.
- SUMPRODUKT:
- SUMPRODUCT-funktionen kan bruges til at gange to eller flere kolonner eller arrays sammen og derefter få summen af produkter.
- ISNUMBER:
- Excel ISNUMBER -funktionen returnerer SAND, når en celle indeholder et tal, og FALSK hvis ikke.
- MATCH:
- Microsoft Excel MATCH-funktionen søger efter en bestemt værdi i et celleområde og returnerer den relative position for denne værdi.
Flere artikler:
- Tæl unikke numeriske værdier baseret på kriterier
- I Excel -regneark lider du muligvis af et problem, der tæller antallet af unikke numeriske værdier baseret på en bestemt tilstand. For eksempel, hvordan kan jeg tælle de unikke mængdeværdier af produktet "T-shirt" fra rapporten som vist nedenfor? I denne artikel vil jeg vise nogle formler for at opnå denne opgave i Excel.
- Tæl celler, der matcher to eller flere kriterier
- I Excel, for at tælle antallet af celler baseret på to eller flere kriterier, kan funktionen TÆLLER hjælpe dig med at løse denne opgave hurtigt og nemt.
- Tæl antal rækker med flere eller kriterier
- For at tælle antallet af rækker med flere kriterier i forskellige kolonner med OR -logik kan funktionen SUMPRODUCT hjælpe dig. For eksempel har jeg en produktrapport som vist under skærmbillede, nu vil jeg tælle de rækker, hvor produktet er "T-shirt" eller farven er "Sort". Hvordan håndteres denne opgave i Excel?
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.
