Ta vadnica ponuja podroben prikaz uporabe funkcij SUMIF in SUMIFS v Google Preglednicah s formulami in primeri.
SUMIF je ena od matematičnih funkcij v Google Preglednicah, ki se uporablja za pogojno seštevanje celic. V bistvu funkcija SUMIF išče določen pogoj v obsegu celic in nato sešteje vrednosti, ki izpolnjujejo dani pogoj.
Na primer, v Googlovih preglednicah imate seznam stroškov in želite povzeti samo stroške, ki so nad določeno največjo vrednostjo. Ali pa imate seznam artiklov naročila in njihovih ustreznih zneskov in želite vedeti samo skupni znesek naročila določenega artikla. Tu pride funkcija SUMIF prav.
SUMIF se lahko uporablja za seštevanje vrednosti na podlagi številčnega stanja, besedilnega stanja, datumskega pogoja, nadomestnih znakov ter na podlagi praznih in nepraznih celic. Google Preglednice ima dve funkciji za seštevanje vrednosti na podlagi meril: SUMIF in SUMIFS. Funkcija SUMIF sešteje števila na podlagi enega pogoja, medtem ko SUMIFS sešteje števila na podlagi več pogojev.
V tej vadnici bomo razložili, kako uporabiti funkcije SUMIF in SUMIFS v Google Preglednicah za seštevanje številk, ki izpolnjujejo določene pogoje.
Funkcija SUMIF v Google Preglednicah – sintaksa in argumenti
Funkcija SUMIF je le kombinacija funkcij SUM in IF. Funkcija IF pregleda obseg celic za dani pogoj, nato pa funkcija SUM sešteje števila, ki ustrezajo celicam, ki izpolnjujejo pogoj.
Sintaksa funkcije SUMIF:
Sintaksa funkcije SUMIF v Google Preglednicah je naslednja:
=SUMIF(obseg, merila, [obseg_vsote])Argumenti:
razpon - Obseg celic, kjer iščemo celice, ki ustrezajo kriterijem.
meril – Merila, ki določajo, katere celice je treba dodati. Kriterij lahko utemeljite na številki, besedilnem nizu, datumu, sklicu celice, izrazu, logičnem operaterju, nadomestnem znaku in drugih funkcijah.
vsota_razpon – Ta argument ni obvezen. To je obseg podatkov z vrednostmi, ki se seštejejo, če se ustrezen vnos obsega ujema s pogojem. Če tega argumenta ne vključite, se namesto tega sešteje 'razpon'.
Zdaj pa poglejmo, kako uporabiti funkcijo SUMIF za seštevanje vrednosti z različnimi kriteriji.
Funkcija SUMIF s številskimi merili
Številke, ki izpolnjujejo določena merila, lahko seštejete v obsegu celic, tako da uporabite enega od naslednjih primerjalnih operatorjev za izdelavo meril.
- večje od (>)
- manj kot (<)
- večje ali enako (>=)
- manj kot ali enako (<=)
- enako (=)
- ni enako ()
Recimo, da imate naslednjo preglednico in vas zanima skupna prodaja, ki je 1000 ali več.
Funkcijo SUMIF lahko vnesete tako:
Najprej izberite celico, v kateri želite, da se prikaže rezultat vsote (D3). Če želite sešteti števila v B2:B12, ki so večja ali enaka 1000, vnesite to formulo in pritisnite »Enter«:
=SUMIF(B2:B12,">=1000",B2:B12)V tem primeru formule sta argumenta obseg in vsota_razpon (B2:B12) enaka, ker se prodajne številke in merila uporabljajo za isti obseg. Število smo vnesli pred operatorjem primerjave in jo zajeli v narekovaje, ker morajo biti merila vedno zaprta v dvojnih narekovajih, razen za sklic na celico.
Formula je iskala števila, ki so večja ali enaka 1000, nato pa seštela vse ujemajoče se vrednosti in pokazala rezultat v celici D3.
Ker sta argumenta obseg in razpon vsote enaka, lahko dosežete enak rezultat brez argumentov obsega vsote v formuli, kot je ta:
=SUMIF(B2:B12,">=1000")
Lahko pa navedete sklic na celico (D2), ki vsebuje številko namesto merila števila, in pridružite primerjalnemu operatorju s to referenco celice v argumentu kriterijev:
=SUMIF(B2:B12,">="&D2)Kot lahko vidite, je primerjalni operator še vedno vpisan v dvojnih narekovajih, operater in sklic na celico pa sta povezana z znakom &. In ni vam treba vključiti sklicevanja na celico v narekovaje.
Opomba: Ko se sklicujete na celico, ki vsebuje merila, pazite, da v vrednosti v celici ne pustite nobenega presledka na začetku ali na koncu. Če ima vaša vrednost nepotreben prostor pred ali za vrednostjo v navedeni celici, bo formula posledično vrnila '0'.
Na enak način lahko uporabite tudi druge logične operatorje, da določite pogoje v argumentu kriterijev. Če želite na primer sešteti vrednosti, manjše od 500:
=SUMIF(B2:B12,"<500")
Vsota, če so števila enaka
Če želite sešteti števila, ki so enaka določenemu številu, lahko vnesete samo število ali v argument kriterija vnesete število z znakom enakosti.
Če želite na primer sešteti ustrezne prodajne zneske (stolpec B) za količine (stolpec C), katerih vrednosti so enake 20, poskusite s katero koli od teh formul:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
Če želite sešteti števila v stolpcu B s količino, ki ni enaka 20 v stolpcu C, poskusite s to formulo:
=SUMIF(C2:C12,"20",B2:B12)
Funkcija SUMIF z besedilnimi merili
Če želite sešteti številke v obsegu celic (stolpec ali vrstica), ki ustreza celicam, ki imajo določeno besedilo, lahko preprosto vključite to besedilo ali celico, ki vsebuje besedilo, v argument kriterijev vaše formule SUMIF. Upoštevajte, da mora biti besedilni niz vedno zajet v dvojnih narekovajih (" ").
Na primer, če želite skupni znesek prodaje v regiji „Zahod“, lahko uporabite spodnjo formulo:
=SUMIF(C2:C13,"Zahod",B2:B13)V tej formuli funkcija SUMIF išče vrednost 'West' v obsegu celic C2:C13 in sešteje ustrezno prodajno vrednost v stolpcu B. Nato prikaže rezultat v celici E3.
Lahko se sklicujete tudi na celico, ki vsebuje besedilo, namesto da uporabite besedilo v argumentu kriterijev:
=SUMIF(C2:C12,E2,B2:B12)
Zdaj pa dobimo skupni prihodek vseh regij razen "Zahoda". Če želite to narediti, bomo v formuli uporabili ne enako operatorju ():
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF z nadomestnimi karticami
V zgornji metodi funkcija SUMIF z besedilnimi merili preveri obseg glede na natančno določeno besedilo. Nato sešteje parrelne številke v točno besedilo in prezre vse druge številke, vključno z delno ujemajočim se besedilnim nizom. Če želite sešteti številke z delno ujemajočimi se besedilnimi nizi, morate v svojih merilih prilagoditi enega od naslednjih nadomestnih znakov:
?(vprašaj) se uporablja za ujemanje s katerim koli posameznim znakom, kjer koli v besedilnem nizu.*(zvezdica) se uporablja za iskanje ujemajočih se besed skupaj s katerim koli zaporedjem znakov.~(tilda) se uporablja za ujemanje besedil z vprašajem (?) ali znakom zvezdice (*).
Ta primer preglednice za izdelke in njihove količine bomo sešteli z nadomestnimi znaki:
Zvezdica (*) Nadomestni znak
Na primer, če želite sešteti količine vseh izdelkov Apple, uporabite to formulo:
=SUMIF(A2:A14,"Apple*",B2:B14)Ta formula SUMIF najde vse izdelke z besedo "Apple" na začetku in poljubnim številom znakov za njo (označeno z "*"). Ko je ujemanje najdeno, povzame Količina številke, ki ustrezajo ujemajočim se besedilnim nizom.
V merilih je mogoče uporabiti tudi več nadomestnih znakov. Namesto neposrednega besedila lahko vnesete tudi nadomestne znake s sklicevanjem na celice.
Če želite to narediti, morajo biti nadomestni znaki zaprti v dvojnih narekovajih (“ “) in povezani s sklicevanjem na celico:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Ta formula sešteje količine vseh izdelkov, ki imajo v sebi besedo „Redmi“, ne glede na to, kje se beseda nahaja v nizu.
Vprašaj (?) Nadomestni znak
Z vprašajem (?) lahko uporabite nadomestni znak za ujemanje besedilnih nizov s poljubnimi posameznimi znaki.
Na primer, če želite najti količine vseh različic Xiaomi Redmi 9, lahko uporabite to formulo:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Zgornja formula išče besedilne nize z besedo »Xiaomi Redmi 9«, ki ji sledijo posamezni znaki in sešteje ustrezne Količina številke.
Tilda (~) Nadomestni znak
Če se želite ujemati z dejanskim vprašajem (?) ali znakom zvezdice (*), vstavite tildo (~) pred nadomestni znak v pogojnem delu formule.
Če želite dodati količine v stolpcu B z ustreznim nizom, ki imajo na koncu znak zvezdice, vnesite spodnjo formulo:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Če želite dodati količine v stolpec B, ki imajo vprašaj (?) v stolpcu A v isti vrstici, poskusite s spodnjo formulo:
=SUMIF(A2:A14,"~?",B2:B14)
Funkcija SUMIF z datumskimi merili
Funkcija SUMIF vam lahko pomaga tudi pri pogojnem seštevanju vrednosti na podlagi datumskih meril – na primer številke, ki ustrezajo določenemu datumu, pred datumom ali po datumu. Uporabite lahko tudi katerega koli od primerjalnih operatorjev z vrednostjo datuma, da ustvarite merila datuma za seštevanje števil.
Datum je treba vnesti v format datuma, ki ga podpirajo Google Preglednice, ali kot referenco na celico, ki vsebuje datum, ali z uporabo datumske funkcije, kot je DATE() ali DANES().
Uporabili bomo ta primer preglednice, da vam pokažemo, kako deluje funkcija SUMIF z merili datuma:
Recimo, da želite v zgornjem naboru podatkov sešteti zneske prodaje, ki so se zgodili pred (<=) 29. novembrom 2019, lahko te prodajne številke dodate s funkcijo SUMIF na enega od teh načinov:
=SUMIF(C2:C13,"<=29. november 2019",B2:B13)Zgornja formula preveri vsako celico od C2 do C13 in se ujema samo s tistimi celicami, ki vsebujejo datume 29. novembra 2019 ali pred njim (29. 11. 2019). Nato sešteje znesek prodaje, ki ustreza tistim ujemajočim se celicam iz obsega celic B2:B13, in prikaže rezultat v celicah E3.
Datum je mogoče v formulo vnesti v kateri koli obliki, ki jo prepoznajo Google Preglednice, na primer »29. november 2019«, »29. november 2019« ali »29/11/2019« itd. Zapomnite si vrednost datuma in operater mora vedno v dvojnih narekovajih.
V kriterijih lahko uporabite tudi funkcijo DATE() namesto neposredne vrednosti datuma:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Lahko pa uporabite sklic na celico namesto datuma v delu formule s kriteriji:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Če želite zneske prodaje sešteti na podlagi današnjega datuma, lahko uporabite funkcijo TODAY() v argumentu kriterijev.
Če želite na primer sešteti vse prodajne zneske za današnji datum, uporabite to formulo:
=SUMIF(C2:C13,DANES(),B2:B13)
Funkcija SUMIF s praznimi ali nepraznimi celicami
Včasih boste morda morali sešteti številke v obsegu celic s praznimi ali nepraznimi celicami v isti vrstici. V takih primerih lahko uporabite funkcijo SUMIF za seštevanje vrednosti na podlagi meril, kjer so celice prazne ali ne.
Vsota, če je prazno
V Google Preglednicah obstajata dva merila za iskanje praznih celic: »« ali »=«.
Na primer, če želite v stolpcu C sešteti ves prodajni znesek, ki vsebuje nize brez dolžine (vizualno je videti prazno), v formuli uporabite dvojne narekovaje brez presledka:
=SUMIF(C2:C13,"",B2:B13)
Če želite sešteti ves znesek prodaje v stolpcu B s popolnimi praznimi celicami v stolpcu C, vključite »=« kot merilo:
=SUMIF(C2:C13,"=",B2:B13)
Vsota, če ni prazno:
Če želite sešteti celice, ki vsebujejo katero koli vrednost (ne prazne), lahko uporabite "" kot merilo v formuli:
Če želite na primer dobiti skupni znesek prodaje s katerim koli datumom, uporabite to formulo:
=SUMIF(C2:C13,"",B2:B13)
SUMIF temelji na več kriterijih z logiko ALI
Kot smo videli do sedaj, je funkcija SUMIF zasnovana za seštevanje števil na podlagi enega samega kriterija, vendar je mogoče sešteti vrednosti na podlagi več kriterijev s funkcijo SUMIF v Google Preglednicah. To je mogoče storiti tako, da združite več kot eno funkcijo SUMIF v eno samo formulo z logiko ALI.
Na primer, če želite sešteti znesek prodaje v regiji »Zahod« ali »Jug« (logika ALI) v določenem obsegu (B2:B13), uporabite to formulo:
=SUMIF(C2:C13,"Zahod",B2:B13)+SUMIF(C2:C13,"Jug",B2:B13)Ta formula sešteje celice, če je vsaj eden od pogojev TRUE. Zato je znano kot "ali logika". Prav tako bo seštel vrednosti, ko bodo izpolnjeni vsi pogoji.
Prvi del formule preveri obseg C2:C13 za besedilo »Zahod« in sešteje vrednosti v obsegu B2:B13, ko je ujemanje doseženo. Sekundni del preveri vrednost besedila 'Jug' v istem obsegu C2:C13 in nato sešteje vrednosti z ujemajočim se besedilom v istem obsegu vsote B2:B13. Nato se obe vsoti seštejeta in prikažeta v celici E3.
V primerih, ko je izpolnjen samo en kriterij, vrne samo to vrednost vsote.
Uporabite lahko tudi več meril namesto enega ali dveh. In če uporabljate več meril, je bolje, da kot merilo uporabite sklic na celico, namesto da zapišete neposredno vrednost v formulo.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF z logiko ALI doda vrednosti, ko je izpolnjen vsaj eden od podanih kriterijev. Če pa želite vrednosti sešteti samo, ko so izpolnjeni vsi podani pogoji, morate uporabiti njeno novo sorodno funkcijo SUMIFS().
Funkcija SUMIFS v Google Preglednicah (več meril)
Ko uporabite funkcijo SUMIF za seštevanje vrednosti na podlagi več meril, je formula morda predolga in zapletena, vi pa ste nagnjeni k napakam. Poleg tega vam SUMIF omogoča seštevanje vrednosti samo za en obseg in kadar je kateri koli od pogojev TRUE. Tu pride na vrsto funkcija SUMIFS.
Funkcija SUMIFS vam pomaga sešteti vrednosti na podlagi več meril ujemanja v enem ali več obsegih. In deluje na podlagi logike IN, kar pomeni, da lahko sešteje vrednosti le, če so izpolnjeni vsi dani pogoji. Tudi če je en pogoj napačen, bo kot rezultat vrnil '0'.
Sintaksa in argumenti funkcije SUMIFS
Sintaksa funkcije SUMIFS je naslednja:
=SUMIFS(obseg_vsote, obseg_pogojev1, merilo1, [obseg_meril2, ...], [merilo2, ...])Kje,
- obseg_vsote – Obseg celic, ki vsebuje vrednosti, ki jih želite sešteti, ko so izpolnjeni vsi pogoji.
- merilni_razpon1 – To je obseg celic, kjer preverite kriterije1.
- merila 1 – To je pogoj, ki ga morate preveriti glede na kriterij_razpon1.
- kriteria_range2, kriterij2, …– Dodatni razponi in merila za ocenjevanje. Formuli lahko dodate več obsegov in pogojev.
Za prikaz, kako funkcija SUMIFS deluje z različnimi merili, bomo uporabili nabor podatkov na naslednjem posnetku zaslona.
SUMIFS z besedilnimi pogoji
Vrednosti lahko seštejete na podlagi dveh različnih besedilnih meril v različnih obsegih. Recimo, da želite izvedeti skupni prodajni znesek dostavljenega artikla Šotor. Za to uporabite to formulo:
=SUMIFS(D2:D13,A2:A13,"šotor",C2:C13,"dostavljeno")V tej formuli imamo dva kriterija: »Šotor« in »Dostavljeno«. Funkcija SUMIFS preveri za postavko 'Šotor' (merila1) v obsegu A2:A13 (razpon_kriterij1) in preveri status 'Dostavljeno' (merila2) v obsegu C2:C13 (razpon_kriterij2). Ko sta oba pogoja izpolnjena, potem sešteje ustrezno vrednost v obsegu celic D2:D13 (obseg_vsote).
SUMIFS s številskimi merili in logičnimi operaterji
S pogojnimi operatorji lahko ustvarite pogoje s številkami za funkcijo SUMIFS.
Če želite najti skupno prodajo več kot 5 količin katerega koli artikla v zvezni državi Kalifornija (CA), uporabite to formulo:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Ta formula ima dva pogoja: »>5« in »CA«.
Ta formula preveri količine (Qty) večje od 5 v območju D2:D13 in preveri stanje 'CA' v območju B2:B13. In ko sta izpolnjena oba pogoja (kar pomeni, da sta v isti vrstici), sešteje znesek v E2:E13.
SUMIFS z datumskimi merili
Funkcija SUMIFS vam omogoča tudi preverjanje več pogojev v istem in različnih razponih.
Recimo, da želite preveriti skupni znesek prodaje dostavljenih artiklov po 31. 5. 2021 in pred datumom 6. 10. 2021, nato uporabite to formulo:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Zgornja formula ima tri pogoje: 31/5/2021, 10/5/2021 in Dostavljeno. Namesto da bi uporabili neposredne datumske in besedilne vrednosti, smo se sklicevali na celice, ki vsebujejo ta merila.
Formula preveri datume po 31. 5. 2021 (G1) in datume pred 6. 10. 2021 (G2) v istem obsegu D2:D13 ter preveri status »Dostavljeno« med tema dvema datumoma. Nato sešteje ustrezni znesek v območju E2:E13.
SUMIFS s praznimi in nepraznimi celicami
Včasih boste morda želeli poiskati vsoto vrednosti, ko je ustrezna celica prazna ali ne. Če želite to narediti, lahko uporabite enega od treh kriterijev, o katerih smo razpravljali prej: "=", "" in "".
Če želite na primer sešteti samo količino artiklov »šotor«, za katere datum dostave še ni potrjen (prazne celice), lahko uporabite merila »=«:
=SUMIFS(D2:D13,A2:A13,"šotor",C2:C13,"=")Formula išče element 'Šotor' (merila1) v stolpcu A z ustreznimi praznimi celicami (merila2) v stolpcu C in nato sešteje ustrezno količino v stolpcu D. "=" predstavlja popolnoma prazno celico.
Če želite poiskati vsoto artiklov 'šotor', za katere je bil potrjen datum dostave (ne prazne celice), uporabite "" kot merilo:
=SUMIFS(D2:D13,A2:A13,"šotor",C2:C13,"")Pravkar smo zamenjali »=« za »« v tej formuli. V stolpcu C najde vsoto šotorskih elementov z nepraznimi celicami.
SUMIFS z ALI logiko
Ker funkcija SUMIFS deluje na podlagi logike IN, sešteje le, če so izpolnjeni vsi pogoji. Kaj pa, če želite sešteti vrednost na podlagi več meril, ko je kateri koli od meril izpolnjen. Trik je v uporabi več funkcij SUMIFS.
Če želite na primer sešteti prodajni znesek za "stojalo za kolesa" ALI "nahrbtnik", ko je njihov status "Naročeno", poskusite s to formulo:
=SUMIFS(D2:D13,A2:A13,"Stelo za kolesa",C2:C13,"Naročeno") +SUMIFS(D2:D13,A2:A13,"Nahrbtnik",C2:C13,"Naročeno")Prva funkcija SUMIFS preveri dva kriterija »Ograja za kolesa« in »Naročeno« ter sešteje vrednosti zneska v stolpcu D. Nato drugi SUMIFS preveri dva kriterija »Nahrbtnik« in »Naročeno« ter sešteje vrednosti zneska v stolpcu D. , se obe vsoti seštejeta in prikažeta na F3. Preprosto povedano, ta formula se sešteje, ko je naročen bodisi 'stolo za kolo' ali 'nahrbtnik'.
To je vse, kar morate vedeti o funkcijah SUMIF in SUMIFS v Google Preglednicah.