Note: The other languages of the website are Google-translated. Back to English

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:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • 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:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


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:

=SUM(COUNTIF(B2:B13,D2:D3))


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:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • 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:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


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:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

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:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))


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 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

Kunne du tænke dig at udføre dit daglige arbejde hurtigt og perfekt? Kutools til Excel bringer 300 kraftfulde avancerede funktioner (Kombiner projektmapper, sum efter farve, delt celleindhold, konverter dato, og så videre...) og spar 80% tid for dig.

  • Designet til 1500 arbejdsscenarier, hjælper dig med at løse 80 % Excel-problemer.
  • Reducer tusindvis af tastatur- og museklik hver dag, lindre dine trætte øjne og hænder.
  • Bliv en Excel-ekspert på 3 minutter. Behøver ikke længere huske smertefulde formler og VBA-koder.
  • 30-dages ubegrænset gratis prøveperiode. 60-dages pengene tilbage garanti. Gratis opgradering og support i 2 år.
Ribbon of Excel (med Kutools til Excel installeret)

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.
  • Bringer effektive faner til Office (inkluderer Excel), Ligesom Chrome, Firefox og ny Internet Explorer.
Skærmbillede af Excel (med Office-fanen installeret)
Sorter kommentarer efter
Kommentarer (0)
Ingen vurderinger endnu. Vær den første til at bedømme!
Der er endnu ingen kommentarer her
Efterlad dine kommentarer
Sender som gæst
×
Bedøm dette indlæg:
0   Tegn
Foreslåede steder

Følg os

Copyright © 2009 - www.extendoffice.com. | Alle rettigheder forbeholdes. Drevet af ExtendOffice. | | Sitemap
Microsoft og Office-logoet er varemærker eller registrerede varemærker tilhørende Microsoft Corporation i USA og / eller andre lande.
Beskyttet af Sectigo SSL