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

Številke z logiko OR za več meril v Excelu

Običajno lahko uporabite funkcijo COUNTIFS za štetje celic na podlagi enega ali več pogojev z logiko AND v Excelu. Ste kdaj doživeli situacijo, ko morate iz enega stolpca ali obsega celic prešteti več vrednosti? To pomeni štetje z več pogoji in logiko OR. V tem primeru lahko funkcije SUM in COUNTIFS uporabite skupaj ali uporabite funkcijo SUMPRODUCT.


Štejte celice s pogoji OR v Excelu

Na primer, imam na voljo vrsto podatkov, kot je prikazano na spodnjem posnetku zaslona, ​​zdaj pa želim prešteti številko izdelka, ki je »Svinčnik« ali »Ravnilo«, tukaj bom razpravljal o dveh formulah za reševanje te naloge v Excelu.

Štejte celice s pogoji OR z uporabo funkcij SUM in COUNTIFS

Če želite v Excelu šteti z več pogoji OR, lahko uporabite funkcije SUM in COUNTIFS s konstanto matrike, splošna sintaksa je:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: Področje podatkov vsebuje merila, iz katerih štejete celice;
  • criterion1, criterion2, criterion3…: Pogoji, na podlagi katerih želite šteti celice.

Če želite prešteti število izdelkov, ki so "svinčnik" ali "ravnilo", kopirajte ali vnesite spodnjo formulo v prazno celico, nato pritisnite Vnesite ključ do rezultata:

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


Pojasnilo formule:

= SUM (COUNTIFS (B2: B13, {"svinčnik", "ravnilo"}))

  • {"Svinčnik", "ravnilo"}: Najprej morate vse pogoje zapakirati v konstanto matrike, kot je ta: {"Pencil", "Ruler"}, ločiti elemente z vejicami.
  • COUNTIFS (B2: B13, {"svinčnik", "ravnilo"}): Ta funkcija COUNTIFS bo prejela posamezno štetje za "svinčnik" in "ravnilo", rezultat pa boste dobili tako: {2,3}.
  • SUM (COUNTIFS (B2: B13, {"svinčnik", "ravnilo"})) = SUM ({2,3}): Na koncu ta funkcija SUM povzame vse elemente v matriki in vrne rezultat: 5.

nasveti: Za merila lahko uporabite tudi sklice na celice, uporabite spodnjo formulo matrike in pritisnite Ctrl + Shift + Enter skupaj, da dobite pravilen rezultat:

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


Štejte celice s pogoji OR z uporabo funkcije SUMPRODUCT

Tu je še ena formula, ki jo ustvari funkcija SUMPRODUCT in lahko pomaga tudi pri štetju celic z logiko OR. Splošna skladnja je:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: Področje podatkov vsebuje merila, iz katerih štejete celice;
  • criterion1, criterion2, criterion3…: Pogoji, na podlagi katerih želite šteti celice.

Kopirajte ali vnesite naslednjo formulo v prazno celico in pritisnite Vnesite ključ za vrnitev rezultata:

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


Pojasnilo formule:

= SUMPRODUCT (1*(B2: B13 = {"Svinčnik", "Ravnilo"}))

  • B2: B13 = {"Svinčnik", "Ravnilo"}: Ta izraz primerja vsako merilo "svinčnik", "ravnilo" s celico obsega B2: B13. Če je merilo izpolnjeno, vrne TRUE, v nasprotnem primeru se prikaže vrednost FALSE, rezultat pa bo naslednji: {TRUE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; TRUE, FALSE ; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}.
  • 1*(B2: B13 = {"Svinčnik", "Ravnilo"}): Množenje pretvori logične vrednosti - TRUE in FALSE v 1 in 0, zato bo rezultat naslednji: {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}): Na koncu funkcija SUMPRODUCT sešteje vse številke v matriki, da dobite rezultat: 5.

Štejte celice z več nizi pogojev OR v Excelu

Včasih boste morda morali prešteti celice z dvema ali več nizi pogojev OR, v tem primeru lahko uporabite SUM in COUNTIFS s konstanto matrike ali SUMPRODUCT s funkcijami ISNUMBER MATCH.

Štejte celice z dvema nizoma pogojev OR z uporabo funkcij SUM in COUNTIFS

Če želite obravnavati le dva niza kriterijev OR, morate formuli COUNTIFS dodati še eno konstanto matrike.

Na primer, imam obseg podatkov, kot je prikazano na spodnjem posnetku zaslona, ​​zdaj želim šteti ljudi, ki so naročili »Svinčnik« ali »Ravnilo«, znesek pa je <100 ali> 200.

Vnesite ali kopirajte naslednjo formulo v celico in pritisnite Vnesite tipko, da dobite rezultat, glejte sliko zaslona:

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

Opombe: V formuli za drugo konstanto matrike uporabite podpičje, ki ustvari navpično matriko.


S funkcijo SUMPRODUCT preštejte celice z več nizi pogojev OR

Zgornja formula deluje samo za dva niza meril OR, če pa morate šteti z več merili, vam lahko pomaga kompleksna formula SUMPRODUCT skupaj s funkcijami ISNUMBER MATCH.

Vzemite na primer spodnje podatke, če želite šteti ljudi, ki so naročili »Svinčnik« ali »Ravnilo«, stanje pa je »Dostavljeno« ali »V tranzitu« in podpisano z »Bob« ali »Eko«, uporabite kompleksno formulo .

Kopirajte ali vnesite spodnjo formulo v prazno celico in pritisnite Vnesite ključ za izračun, si oglejte posnetek zaslona:

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


Pojasnilo formule:

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

ISNUMBER (UJEMA (B2: B13, {"Svinčnik", "Ravnilo"}, 0)):

  • UJEMA (B2: B13, {"svinčnik", "ravnilo"}, 0): Ta funkcija MATCH se uporablja za primerjavo vsake celice v območju B2: B13 z ustrezno konstanto matrike. Če je ujemanje najdeno, vrne relativni položaj vrednosti v matriki, sicer se prikaže vrednost napake. Seznam nizov boste dobili tako: {1;#N/A;#N/A; 2;#N/A; 1;#N/A; 2; 1;#N/A; 2;# N/A}.
  • 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}): Funkcija ISNUMBER pretvori številke v TRUE in vrednosti napak v FALSE takole: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.

Zgornjo logiko lahko uporabimo tudi za drugi in tretji izraz ISNUMBER.

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

  • Nato se ti tri matrike skupaj pomnožijo v SUMPRODUCT, ki samodejno pretvori vrednosti TRUE in FALSE v 1s in 0s kot del matematične operacije, kot je ta: 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}) = PODPROIZVOD ({1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}).
  • Funkcija SUMPRODUCT končno sešteje vse številke v matriki, da dobimo rezultat: 3.

Uporabljena relativna funkcija:

  • VSEM:
  • Funkcija Excel SUM vrne vsoto predloženih vrednosti.
  • COUNTIF:
  • Funkcija COUNTIF je statistična funkcija v Excelu, ki se uporablja za štetje števila celic, ki izpolnjujejo merilo.
  • POVEZAVA:
  • S funkcijo SUMPRODUCT lahko pomnožite dva ali več stolpcev ali nizov in nato dobite vsoto izdelkov.
  • ŠTEVILKA:
  • Excel ISNUMBER funkcija vrne TRUE, če celica vsebuje številko, in FALSE, če ne.
  • UJEMA:
  • Funkcija Microsoft Excel MATCH išče določeno vrednost v območju celic in vrne relativni položaj te vrednosti.

Več člankov:

  • Štejte edinstvene številske vrednosti na podlagi meril
  • V Excelovem delovnem listu lahko pride do težave, ki šteje število edinstvenih številskih vrednosti glede na določeno stanje. Na primer, kako lahko iz poročila preštejem edinstvene vrednosti količine »majice« izdelka, kot je prikazano na spodnjem posnetku zaslona? V tem članku bom prikazal nekaj formul za dosego te naloge v Excelu.
  • Štejte število vrstic z več ALI merili
  • Za štetje števila vrstic z več merili v različnih stolpcih z logiko OR vam lahko pomaga funkcija SUMPRODUCT. Na primer, imam poročilo o izdelku, kot je prikazano na spodnjem posnetku zaslona, ​​zdaj želim prešteti vrstice, kjer je izdelek »majica« ali je barva »črna«. Kako ravnati s to nalogo v Excelu?

Najboljša orodja za pisarniško produktivnost

Kutools za Excel - vam pomaga izstopati iz množice

Bi radi svoje vsakodnevno delo opravili hitro in popolno? Kutools za Excel prinaša 300 zmogljivih naprednih funkcij (združevanje delovnih zvezkov, seštevanje po barvi, razdelitev vsebine celice, pretvorba datuma itd.) in vam prihrani 80 % časa.

  • Zasnovan za 1500 delovnih scenarijev vam pomaga rešiti 80 % Excelovih težav.
  • Vsak dan zmanjšajte na tisoče klikov na tipkovnici in miški, razbremenite utrujene oči in roke.
  • V 3 minutah postanite strokovnjak za Excel. Ni vam več treba zapomniti nobenih bolečih formul in kod VBA.
  • 30-dnevno neomejeno brezplačno preskusno obdobje. 60-dnevno jamstvo za vračilo denarja. Brezplačna nadgradnja in podpora za 2 leti.
Trak Excela (z nameščenim Kutools za Excel)

Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)

  • Eno sekundo za preklop med desetinami odprtih dokumentov!
  • Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
  • Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
  • Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Firefox in New Internet Explorer.
Posnetek zaslona programa Excel (z nameščenim zavihkom Office)
Komentarji (0)
Ocene še ni. Bodite prvi in ​​ocenite!
Tu še ni objavljenih komentarjev
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL