Sumif z več merili, ki temeljijo na logiki ALI in IN
Ko delate na Excelovih delovnih listih, boste morda morali sešteti vrednosti na podlagi več meril. Včasih je več kriterijev iz istega stolpca (logika ALI), včasih pa iz različnih stolpcev (logika IN). Kako bi se v tem primeru lahko spopadli s to nalogo v Excelu?
- Sumif z več kriteriji, ki temeljijo na logiki ALI
- Sumif z več kriteriji, ki temeljijo na logiki IN z uporabo funkcije SUMIFS
Sumif z več kriteriji, ki temeljijo na logiki ALI
Predpostavimo, da imate seznam izdelkov, ki vsebuje dobavitelja in količino, kot je prikazano spodaj, zdaj želite dobiti vse skupne količine, ki jih dobavljata dobavitelja John in David. Tukaj vam bom predstavil dve preprosti formuli.
Z uporabo formule SUMIF + SUMIF +…
Če želite sešteti števila, ki izpolnjujejo enega od kriterijev (ali logiko) iz več kriterijev, lahko v eno formulo seštejete več funkcij SUMIF, generična sintaksa je:
- criteria_range: obseg celic, ki jih želite ujemati s kriteriji;
- criteria1: prvi kriterij, ki se uporablja za določitev celic, ki jih je treba sešteti;
- criteria2: Drugi kriterij, ki se uporablja za določanje, katere celice je treba sešteti;
- sum_range: obseg celic, iz katerih želite sešteti.
Zdaj kopirajte ali vnesite katero koli od spodnjih formul v prazno celico in pritisnite Vnesite ključ do rezultata:
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14) (Use a cell reference)
Pojasnilo formule:
=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)
- 1. SUMIF(B4:B14,"Janez",C4:C14) poišče Johnove vrstice in sešteje skupne količine;
- 2. SUMIF(B4:B14,"David",C4:C14) poišče Davidove vrstice in sešteje skupne količine;
- Nato seštejte ti dve formuli SUMIF, da dobite vse skupne količine, ki sta jih dobavila John in David.
Z uporabo funkcij SUM in SUMIF
Zgornja formula je zelo enostavna za uporabo, če obstaja le nekaj kriterijev, vendar če želite sešteti vrednosti z več pogoji ALI, je zgornja formula lahko odveč. V tem primeru vam lahko naredi uslugo boljša formula, ki je ustvarjena na podlagi funkcij SUM in SUMIF. Splošne sintakse so:
Splošna formula s trdo kodiranim besedilom:
- criteria_range: obseg celic, ki jih želite ujemati s kriteriji;
- criteria1: prvi kriterij, ki se uporablja za določitev celic, ki jih je treba sešteti;
- criteria2: Drugi kriterij, ki se uporablja za določanje, katere celice je treba sešteti;
- sum_range: obseg celic, iz katerih želite sešteti.
Generična formula s referencami celic:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: obseg celic, ki jih želite ujemati s kriteriji;
- criteria_cells: celice, ki vsebujejo merila, ki jih želite uporabiti;
- sum_range: obseg celic, iz katerih želite sešteti.
Prosimo, vnesite ali kopirajte katero koli od spodnjih formul, ki jih potrebujete, v prazno celico in nato dobite rezultat:
=SUM(SUMIF(B4:B14, E4:E5, C4:C14)) (Use cell references, array formula, should press Ctrl + Shift + Enter keys)
Pojasnilo formule:
=SUM(SUMIF(B4:B14, {"Janez","David"}, C4:C14))
> SUMIF(B4:B14, {"Janez","David"}, C4:C14):
- {"Janez","David"}: Konstanta matrike, ki je nabor več kriterijev v kodrastih oklepajih.
- SUMIF(B4:B14,"David",C4:C14) Konstanta matrike, ki uporablja logiko ALI, prisili funkcijo SUMIF, da sešteje števila v C4:C14 na podlagi enega od več kriterijev ("John" in "David") in vrnila bo dva ločena rezultata: {410,1520}.
> SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))=SUM({410,1520}): Nazadnje ta funkcija SUM sešteje te rezultate matrike, da vrne rezultat: 1930.
Sumif z več kriteriji, ki temeljijo na logiki IN z uporabo funkcije SUMIFS
Če želite sešteti vrednosti z več merili v različnih stolpcih, lahko to nalogo hitro rešite s funkcijo SUMIF. Splošna sintaksa je:
- sum_range: obseg celic, iz katerih želite sešteti;
- criteria_range1: Obseg, kjer se uporablja criteira1;
- criteria1: prvi kriterij, ki je preverjen glede na obseg_kriterij1 in določa, katere celice je treba dodati; (vrsta meril je lahko: število, logični izraz, sklic na celico, besedilo, datum ali druga Excelova funkcija.)
- criteria_range2, criteria2…: Drugi dodatni razponi in z njimi povezana merila. (v formuli SUMIFS lahko nastavite 127 parov kriterijev_razpon in kriterijev.)
Recimo, da imam tabelo s stolpci Product, Supplier, Qty in Price, kot je prikazano na spodnjem posnetku zaslona. Zdaj želim ugotoviti vsoto skupne cene izdelka Apple in količine, ki je večja ali enaka 200.
Prosimo, uporabite katero koli od spodnjih formul v prazno celico in pritisnite Vnesite ključ za vrnitev rezultata:
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4) (Use cell references)
Pojasnilo formule:
=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")
- Območje A4:A14 preveri za izdelek "Apple", območje C4:C14 pa izvleče vse količine, večje ali enake 200, nato funkcija SUMIFS sešteje samo ujemajoče se celice iz območja D4:D14.
Uporabljena relativna funkcija:
- SUMIF:
- Funkcija SUMIF lahko pomaga pri seštevanju celic na podlagi enega merila.
- SUMIFS:
- Funkcija SUMIFS v Excelu lahko pomaga pri seštevanju vrednosti celic na podlagi več meril.
Več člankov:
- Vsota, če se začne z ali se konča z določenim besedilom ali znaki
- Če želite sešteti vrednosti, če se ustrezne celice začnejo z ali končajo z določeno vrednostjo, lahko uporabite funkcijo SUMIF z nadomestnim znakom (*), da jo izvlečete. Ta članek bo podrobno predstavil, kako uporabljati formulo.
- Vsota, če celice vsebujejo določeno besedilo v drugem stolpcu
- Ta vadnica vam bo pokazala, kako sešteti vrednosti, če celice vsebujejo specifično ali delno besedilo v drugem stolpcu. Za primer vzemite spodnji obseg podatkov, da dobite skupne količine izdelkov, ki vsebujejo besedilo »T-Shirt«, tako funkcija SUMIF kot funkcija SUMPRODUCT lahko obravnavata to nalogo v Excelu.
- Vsota najmanjših ali spodnjih N vrednosti v Excelu
- V Excelu nam je enostavno sešteti obseg celic s funkcijo SUM. Včasih boste morda morali sešteti najmanjše ali najnižje številke 3, 5 ali n v obsegu podatkov, kot je prikazano na spodnjem posnetku zaslona. V tem primeru vam lahko SUMPRODUCT skupaj s funkcijo SMALL pomaga rešiti to težavo v Excelu.
Najboljša orodja za pisarniško produktivnost
Kutools for Excel - Pomaga vam izstopati iz množice
Kutools for Excel Ponaša se z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Office Tab - Omogočite branje in urejanje z zavihki v Microsoft Officeu (vključno z Excelom)
- 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.
