Š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:
- 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:
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:
Š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:
- 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:
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:
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:
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.
- Preštejte celice, ki ustrezajo dvema ali več merilom
- Če želite v Excelu šteti število celic na podlagi dveh ali več meril, vam lahko funkcija COUNTIFS pomaga hitro in enostavno rešiti to nalogo.
- Š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
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
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, Edge in Firefox.
