Šioje mokymo programoje išsamiai parodoma, kaip naudoti SUMIF ir SUMIFS funkcijas „Google“ skaičiuoklėse su formulėmis ir pavyzdžiais.
SUMIF yra viena iš matematinių „Google“ skaičiuoklių funkcijų, kuri naudojama sąlyginiam langelių sumavimui. Iš esmės funkcija SUMIF ieško konkrečios sąlygos langelių diapazone ir tada sudeda reikšmes, kurios atitinka nurodytą sąlygą.
Pavyzdžiui, „Google“ lapuose turite išlaidų sąrašą ir norite susumuoti tik tas išlaidas, kurios viršija tam tikrą maksimalią vertę. Arba turite užsakymo prekių sąrašą ir jas atitinkančias sumas ir norite sužinoti tik bendrą konkrečios prekės užsakymo sumą. Čia praverčia SUMIF funkcija.
SUMIF gali būti naudojamas susumuoti reikšmes pagal skaičių sąlygą, teksto sąlygą, datos sąlygą, pakaitos simbolius, taip pat pagal tuščius ir netuščius langelius. „Google“ skaičiuoklėse yra dvi funkcijos, kurios apibendrina reikšmes pagal kriterijus: SUMIF ir SUMIFS. Funkcija SUMIF sudeda skaičius pagal vieną sąlygą, o SUMIFS – pagal kelias sąlygas.
Šioje pamokoje paaiškinsime, kaip naudoti SUMIF ir SUMIFS funkcijas „Google“ skaičiuoklėse, kad susumuotų skaičius, atitinkantis tam tikrą (-as) sąlygą (-es).
SUMIF funkcija „Google“ skaičiuoklėse – sintaksė ir argumentai
Funkcija SUMIF yra tik funkcijų SUM ir IF derinys. Funkcija IF nuskaito tam tikros sąlygos langelių diapazoną, o tada funkcija SUM sumuoja skaičius, atitinkančius sąlygą atitinkančius langelius.
Funkcijos SUMIF sintaksė:
Funkcijos SUMIF sintaksė „Google“ skaičiuoklėse yra tokia:
=SUMF(diapazonas, kriterijai, [sumos_diapazonas])Argumentai:
diapazonas - Langelių diapazonas, kuriame ieškome kriterijus atitinkančių langelių.
kriterijai – Kriterijus, pagal kuriuos nustatoma, kurias ląsteles reikia pridėti. Kriterijus galite pagrįsti skaičiumi, teksto eilute, data, langelio nuoroda, išraiška, loginiu operatoriumi, pakaitos simboliu ir kitomis funkcijomis.
suma_diapazonas – Šis argumentas yra neprivalomas. Tai yra duomenų diapazonas su reikšmėmis, kurias reikia susumuoti, jei atitinkamas diapazono įrašas atitinka sąlygą. Jei neįtrauksite šio argumento, vietoj to sumuojamas „diapazonas“.
Dabar pažiūrėkime, kaip naudoti funkciją SUMIF susumuoti reikšmes pagal skirtingus kriterijus.
SUMIF funkcija su skaičių kriterijais
Galite susumuoti skaičius, atitinkančius tam tikrus kriterijus langelių diapazone, naudodami vieną iš šių palyginimo operatorių, kad nustatytumėte kriterijus.
- didesnis nei (>)
- mažiau nei (<)
- didesnis arba lygus (>=)
- mažesnis arba lygus (<=)
- lygus (=)
- nelygu ()
Tarkime, kad turite šią skaičiuoklę ir jus domina bendras pardavimas, kuris yra 1000 ar daugiau.
Štai kaip galite įvesti funkciją SUMIF:
Pirmiausia pasirinkite langelį, kuriame norite matyti sumos išvestį (D3). Norėdami susumuoti skaičius B2:B12, kurie yra didesni arba lygūs 1000, įveskite šią formulę ir paspauskite „Enter“:
=SUMF(B2:B12,">=1000",B2:B12)Šioje pavyzdinėje formulėje diapazono ir sumos_diapazono argumentai (B2:B12) yra vienodi, nes pardavimo skaičiai ir kriterijai taikomi tam pačiam diapazonui. Ir mes įvedėme skaičių prieš palyginimo operatorių ir įdėjome jį į kabutes, nes kriterijai visada turi būti pateikiami dvigubose kabutėse, išskyrus langelio nuorodą.
Formulė ieškojo skaičių, kurie yra didesni arba lygūs 1000, tada sudėjo visas suderintas reikšmes ir parodė rezultatą langelyje D3.
Kadangi diapazono ir sumos_diapazono argumentai yra vienodi, galite pasiekti tą patį rezultatą be sum_range argumentų formulėje, pavyzdžiui:
=SUMIF(B2:B12,">=1000")
Arba galite pateikti langelio nuorodą (D2), kurioje yra skaičius, o ne skaičiaus kriterijai, ir sujungti palyginimo operatorių su ta langelio nuoroda kriterijų argumente:
=SUMIF(B2:B12,">="&D2)Kaip matote, palyginimo operatorius vis dar įrašomas dvigubose kabutėse, o operatorius ir langelio nuoroda yra sujungti ampersandu (&). Ir jums nereikia dėti langelio nuorodos į kabutes.
Pastaba: Kai nurodote langelį, kuriame yra kriterijų, nepalikkite tarpo priekinėje ar pabaigoje esančioje reikšmėje. Jei jūsų reikšmėje yra nereikalingos vietos prieš arba po nurodytos ląstelės reikšmės, formulė grąžins „0“.
Taip pat galite naudoti kitus loginius operatorius, kad nustatytumėte sąlygas kriterijų argumente. Pavyzdžiui, norint susumuoti reikšmes, mažesnes nei 500:
=SUMF(B2:B12,"<500")
Suma, jei skaičiai lygūs
Jei norite pridėti skaičių, lygių tam tikram skaičiui, kriterijaus argumente galite įvesti tik skaičių arba skaičių su lygybės ženklu.
Pavyzdžiui, norėdami susumuoti atitinkamas pardavimo sumas (B stulpelis) kiekiams (C stulpelis), kurių reikšmės yra 20, išbandykite bet kurią iš šių formulių:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMF(C2:C12,E2,B2:B12)
Norėdami susumuoti skaičius B stulpelyje, kurių kiekis nėra lygus 20 C stulpelyje, išbandykite šią formulę:
=SUMIF(C2:C12,"20",B2:B12)
SUMIF funkcija su teksto kriterijais
Jei norite pridėti skaičius langelių diapazone (stulpelyje arba eilutėje), atitinkančiame langelius, kuriuose yra konkretus tekstas, galite tiesiog įtraukti tą tekstą arba langelį, kuriame yra tekstas, į SUMIF formulės kriterijų argumentą. Atkreipkite dėmesį, kad teksto eilutė visada turi būti dviguba kabutėse (“ “).
Pavyzdžiui, jei norite, kad bendras pardavimas būtų parduotas „Vakarų“ regione, galite naudoti toliau pateiktą formulę:
=SUMIF(C2:C13,"Vakarai",B2:B13)Šioje formulėje funkcija SUMIF ieško reikšmės „Vakarai“ langelių diapazone C2:C13 ir sudeda atitinkamą pardavimo vertę B stulpelyje. Tada E3 langelyje rodomas rezultatas.
Taip pat galite nurodyti langelį, kuriame yra tekstas, o ne naudoti tekstą kriterijų argumente:
=SUMF(C2:C12,E2,B2:B12)
Dabar gaukime visas visų regionų, išskyrus „Vakarus“, pajamas. Norėdami tai padaryti, formulėje naudosime operatoriui nelygu ():
=SUMIF(C2:C12,"&E2,B2:B12)
SUMIF su pakaitos kortomis
Taikant aukščiau pateiktą metodą, funkcija SUMIF su teksto kriterijais tikrina diapazoną su tiksliai nurodytu tekstu. Tada jis susumuoja skaičius su tiksliu tekstu ir nepaiso visų kitų skaičių, įskaitant iš dalies atitinkančią teksto eilutę. Norėdami susumuoti skaičius su iš dalies atitinkančiomis teksto eilutėmis, savo kriterijais turite pritaikyti vieną iš šių pakaitos simbolių:
?(klaustukas) naudojamas norint atitikti bet kurį vieną simbolį bet kurioje teksto eilutės vietoje.*(žvaigždutė) naudojamas norint rasti atitinkančius žodžius kartu su bet kokia simbolių seka.~(tildė) naudojama norint suderinti tekstus su klaustuku (?) arba žvaigždute (*).
Šią produktų ir jų kiekių skaičiuoklės pavyzdį susumuosime su pakaitos simboliais:
Žvaigždutė (*) Pakaitos simbolis
Pavyzdžiui, jei norite susumuoti visų Apple produktų kiekius, naudokite šią formulę:
=SUMIF(A2:A14,"Apple*",B2:B14)Ši SUMIF formulė randa visus produktus, kurių pradžioje yra žodis „Apple“ ir bet koks simbolių skaičius po jo (žymimas „*“). Radus atitiktį, ji apibendrina Kiekis atitinkančias teksto eilutes atitinkančius skaičius.
Taip pat kriterijais galima naudoti kelis pakaitos simbolius. Be to, vietoj tiesioginio teksto galite įvesti pakaitos simbolius su ląstelių nuorodomis.
Norėdami tai padaryti, pakaitos simboliai turi būti įterpti į dvigubas kabutes (“ “) ir sujungti su langelio nuoroda (-omis):
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Ši formulė susumuoja visų produktų, kuriuose yra žodis „Redmi“, kiekius, nesvarbu, kurioje eilutės vietoje yra žodis.
Klaustukas (?) Pakaitos simbolis
Norėdami suderinti teksto eilutes su bet kuriais atskirais simboliais, galite naudoti klaustuko (?) pakaitos simbolį.
Pavyzdžiui, jei norite rasti visų „Xiaomi Redmi 9“ variantų kiekius, galite naudoti šią formulę:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Aukščiau pateikta formulė ieško teksto eilučių su žodžiu „Xiaomi Redmi 9“, po kurio eina bet kokie atskiri simboliai, ir sumuoja atitinkamas Kiekis numeriai.
Tildė (~) Pakaitos simbolis
Jei norite atitikti tikrąjį klaustuką (?) arba žvaigždutę (*), formulės sąlygos dalyje prieš pakaitos simbolį įterpkite tildės (~) simbolį.
Norėdami pridėti kiekius B stulpelyje su atitinkama eilute, kurios pabaigoje yra žvaigždutė, įveskite toliau pateiktą formulę:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Norėdami į B stulpelį įtraukti kiekius, kurių toje pačioje eilutėje A stulpelyje yra klaustukas (?), išbandykite toliau pateiktą formulę:
=SUMIF(A2:A14,"~?",B2:B14)
SUMIF funkcija su datos kriterijais
Funkcija SUMIF taip pat gali padėti sąlygiškai susumuoti reikšmes pagal datos kriterijus – pavyzdžiui, skaičius, atitinkantis tam tikrą datą, prieš datą arba po datos. Taip pat galite naudoti bet kurį palyginimo operatorių su datos reikšme, kad sukurtumėte datos kriterijus skaičiams sumuoti.
Data turi būti įvesta „Google“ lapų palaikomu datos formatu arba kaip langelio nuoroda, kurioje yra data, arba naudojant datos funkciją, pvz., DATE() arba TODAY().
Naudosime šią skaičiuoklės pavyzdį norėdami parodyti, kaip veikia funkcija SUMIF su datos kriterijais:
Tarkime, kad pirmiau pateiktame duomenų rinkinyje norite susumuoti pardavimo sumas, įvykusias 2019 m. lapkričio 29 d. arba anksčiau, tuos pardavimo skaičius galite pridėti naudodami funkciją SUMIF vienu iš šių būdų:
=SUMIF(C2:C13,"<=2019 m. lapkričio 29 d.",B2:B13)Aukščiau pateikta formulė tikrina kiekvieną langelį nuo C2 iki C13 ir atitinka tik tuos langelius, kuriuose yra 2019 m. lapkričio 29 d. arba ankstesnės datos (2019-11-29). Tada susumuoja pardavimo sumą, atitinkančią tuos atitinkančius langelius iš langelių diapazono B2:B13, ir rodo rezultatą langeliuose E3.
Data formulėje gali būti pateikta bet kokiu formatu, kurį atpažįsta „Google“ skaičiuoklės, pvz., „2019 m. lapkričio 29 d.“, „2019 m. lapkričio 29 d.“ arba „2019 m. lapkričio 29 d.“ ir t. t. Atsiminkite datos reikšmę ir operatorius turi visada rašomi dvigubose kabutėse.
Taip pat kriterijuose galite naudoti funkciją DATE() vietoj tiesioginės datos vertės:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Arba galite naudoti langelio nuorodą vietoj datos formulės kriterijų dalyje:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Jei norite pridėti pardavimo sumas pagal šiandienos datą, kriterijų argumente galite naudoti funkciją TODAY().
Pavyzdžiui, norėdami susumuoti visas šios dienos pardavimo sumas, naudokite šią formulę:
=SUMF(C2:C13,ŠIANDIEN(),B2:B13)
SUMIF funkcija su tuščiomis arba netuščiomis ląstelėmis
Kartais gali tekti susumuoti skaičius langelių diapazone su tuščiais arba netuščiais langeliais toje pačioje eilutėje. Tokiais atvejais galite naudoti funkciją SUMIF, kad susumuoti reikšmes pagal kriterijus, ar langeliai tušti, ar ne.
Suma, jei tuščia
„Google“ skaičiuoklėse yra du kriterijai, norint rasti tuščius langelius: „“ arba „=“.
Pavyzdžiui, jei norite susumuoti visą pardavimo sumą, kurioje C stulpelyje yra nulinio ilgio eilutės (vizualiai atrodo tuščios), formulėje naudokite dvigubas kabutes be tarpų:
=SUMIF(C2:C13,"",B2:B13)
Norėdami susumuoti visą pardavimo sumą B stulpelyje su visiškai tuščiais langeliais C stulpelyje, kaip kriterijus įtraukite „=“:
=SUMIF(C2:C13,"=",B2:B13)
Suma, jei ne tuščia:
Jei norite susumuoti langelius, kuriuose yra bet kokia reikšmė (ne tuščia), formulėje galite naudoti „“ kaip kriterijus:
Pavyzdžiui, norėdami gauti bendrą pardavimo sumą su bet kokiomis datomis, naudokite šią formulę:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Remiantis keliais kriterijais su OR logika
Kaip matėme iki šiol, funkcija SUMIF skirta sumuoti skaičius pagal vieną kriterijų, tačiau naudojant SUMIF funkciją Google skaičiuoklėse galima susumuoti reikšmes pagal kelis kriterijus. Tai galima padaryti sujungiant daugiau nei vieną SUMIF funkciją vienoje formulėje su ARBA logika.
Pavyzdžiui, jei norite susumuoti pardavimo sumą „Vakarų“ regione arba „Pietų“ regione (ARBA logika) nurodytame diapazone (B2:B13), naudokite šią formulę:
=SUMIF(C2:C13,"Vakarai",B2:B13)+SUMIF(C2:C13,"Pietai",B2:B13)Ši formulė susumuoja langelius, kai bent viena iš sąlygų yra TRUE. Todėl tai žinoma kaip „ARBA logika“. Ji taip pat susumuoja vertes, kai bus įvykdytos visos sąlygos.
Pirmoje formulės dalyje tikrinamas teksto „Vakarai“ diapazonas C2:C13 ir sumuojamos diapazono B2:B13 reikšmės, kai atitinka atitiktį. Antroji tikrinimo dalis tikrina, ar teksto reikšmė „Pietų“ yra tame pačiame diapazone C2:C13, o tada sumuojamos reikšmės su atitinkamu tekstu tame pačiame sum_range B2:B13. Tada abi sumos sudedamos ir rodomos langelyje E3.
Tais atvejais, kai įvykdomas tik vienas kriterijus, jis grąžins tik tą sumos reikšmę.
Taip pat galite naudoti kelis kriterijus, o ne vieną ar du. Ir jei naudojate kelis kriterijus, geriau naudoti langelio nuorodą kaip kriterijų, o ne rašyti tiesioginę reikšmę formulėje.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF su OR logika prideda reikšmes, kai įvykdomas bent vienas iš nurodytų kriterijų, tačiau jei norite susumuoti reikšmes tik tada, kai įvykdomos visos nurodytos sąlygos, turite naudoti naują funkciją SUMIFS().
SUMIFS funkcija „Google“ skaičiuoklėse (keli kriterijai)
Kai naudojate funkciją SUMIF, norėdami susumuoti reikšmes pagal kelis kriterijus, formulė gali tapti per ilga ir sudėtinga, todėl galite padaryti klaidų. Be to, SUMIF leis susumuoti reikšmes tik viename diapazone ir kai kuri nors iš sąlygų yra TRUE. Čia atsiranda SUMIFS funkcija.
Funkcija SUMIFS padeda susumuoti reikšmes pagal kelis atitikimo kriterijus viename ar keliuose diapazonuose. Ir jis veikia pagal IR logiką, tai reiškia, kad jis gali susumuoti reikšmes tik tada, kai įvykdomos visos nurodytos sąlygos. Net jei viena sąlyga yra klaidinga, ji grąžins „0“.
SUMIFS funkcijos sintaksė ir argumentai
Funkcijos SUMIFS sintaksė yra tokia:
=SUMIFS(sumos_diapazonas, kriterijų_diapazonas1, kriterijus1, [kriterijų_diapazonas2, ...], [2 kriterijus, ...])kur,
- suma_diapazonas – Langelių diapazonas, kuriame yra reikšmės, kurias norite susumuoti, kai įvykdomos visos sąlygos.
- kriterijų_diapazonas1 – Tai langelių diapazonas, kuriame tikrinate kriterijus1.
- 1 kriterijus – Tai sąlyga, kurią turite patikrinti pagal kriterijų_diapazoną1.
- kriteria_range2, 2 kriterijus,…– Papildomi vertinimo diapazonai ir kriterijai. Be to, prie formulės galite pridėti daugiau diapazonų ir sąlygų.
Naudosime toliau pateiktoje ekrano kopijoje pateiktą duomenų rinkinį, kad parodytume, kaip SUMIFS funkcija veikia pagal skirtingus kriterijus.
SUMIFS su teksto sąlygomis
Galite susumuoti reikšmes pagal du skirtingus teksto kriterijus skirtinguose diapazonuose. Pavyzdžiui, tarkime, kad norite sužinoti bendrą pristatytos Palapinės prekės pardavimo sumą. Tam naudokite šią formulę:
=SUMIFS(D2:D13,A2:A13,"Palapinė",C2:C13,"Pristatyta")Šioje formulėje turime du kriterijus: „Palapinė“ ir „Pristatyta“. Funkcija SUMIFS tikrina elementą „Palapinė“ (1 kriterijus) diapazone A2:A13 (kriterijų_diapazonas1), o būseną „Pristatyta“ (2 kriterijus) diapazone C2:C13 (kriterijų_diapazonas2). Kai tenkinamos abi sąlygos, ji susumuoja atitinkamą reikšmę langelių diapazone D2:D13 (sum_range).
SUMIFS su skaičių kriterijais ir loginiais operatoriais
Galite naudoti sąlyginius operatorius, kad sukurtumėte sąlygas su skaičiais funkcijai SUMIFS.
Norėdami sužinoti daugiau nei 5 bet kurios prekės parduotų kiekių Kalifornijos valstijoje (CA), naudokite šią formulę:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Ši formulė turi dvi sąlygas: „>5“ ir „CA“.
Ši formulė tikrina, ar kiekiai (Qty) yra didesni nei 5 diapazone D2:D13, ir tikrinama, ar diapazone B2:B13 yra būsena „CA“. Ir kai tenkinamos abi sąlygos (tai reiškia, kad yra toje pačioje eilutėje), suma E2:E13.
SUMIFS su datos kriterijais
Funkcija SUMIFS taip pat leidžia patikrinti kelias sąlygas tame pačiame diapazone ir skirtinguose diapazonuose.
Tarkime, kad norite patikrinti bendrą pristatytų prekių pardavimo sumą po 2021-05-31 ir iki 2021-10-06 datos, tada naudokite šią formulę:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Aukščiau pateikta formulė turi tris sąlygas: 2021-05-31, 2021-10-05 ir Pristatyta. Užuot naudoję tiesiogines datos ir teksto reikšmes, nurodėme langelius, kuriuose yra šie kriterijai.
Formulė tikrina datas po 2021-05-31 (G1) ir datas iki 2021-10-06 (G2) tame pačiame diapazone D2:D13 ir tikrina, ar tarp šių dviejų datų yra būsena „Pristatyta“. Tada susumuokite susijusią sumą diapazone E2:E13.
SUMIFS su tuščiomis ir netuščiomis ląstelėmis
Kartais galbūt norėsite rasti reikšmių sumą, kai atitinkamas langelis tuščias arba ne. Norėdami tai padaryti, galite naudoti vieną iš trijų kriterijų, kuriuos aptarėme anksčiau: „=“, „“ ir „“.
Pavyzdžiui, jei norite susumuoti tik „Palapinės“ prekių, kurių pristatymo data dar nepatvirtinta, kiekį (tušti langeliai), galite naudoti „=“ kriterijus:
=SUMIFS(D2:D13,A2:A13,"Palapinė",C2:C13,"=")Formulė ieško elemento „Palapinė“ (1 kriterijus) A stulpelyje su atitinkamais tuščiais langeliais (2 kriterijai) C stulpelyje, tada susumuoja atitinkamą sumą D stulpelyje. „=“ reiškia visiškai tuščią langelį.
Norėdami rasti „Palapinės“ prekių, kurių pristatymo data patvirtinta, sumą (ne tuščius langelius), naudokite „“ kaip kriterijų:
=SUMIFS(D2:D13,A2:A13,"Palapinė",C2:C13"")Šioje formulėje „=“ tiesiog pakeitėme į „“. Ji randa palapinės elementų su netuščiais langeliais C stulpelyje sumą.
SUMIFS su OR Logic
Kadangi funkcija SUMIFS veikia pagal IR logiką, ji sumuoja tik tada, kai tenkinamos visos sąlygos. Bet ką daryti, jei norite susumuoti vertę pagal kelis kriterijus, kai atitinka bet kurį iš kriterijų. Triukas yra naudoti kelias SUMIFS funkcijas.
Pavyzdžiui, jei norite pridėti „Dviračio stovo“ ARBA „Kuprinės“ pardavimo sumą, kai jų būsena yra „Užsakyta“, išbandykite šią formulę:
=SUMIFS(D2:D13,A2:A13"Dviračio stovas",C2:C13"Užsakytas") +SUMIFS(D2:D13,A2:A13"Kuprinė",C2:C13"Užsakyta")Pirmoji SUMIFS funkcija patikrina du kriterijus „Dviračio stovas“ ir „Užsakytas“ ir susumuoja sumos reikšmes D stulpelyje. Tada antroji SUMIFS patikrina du kriterijus „Kuprinė“ ir „Užsakyta“ ir susumuoja sumos reikšmes D stulpelyje. , abi sumos sudedamos ir rodomos F3. Paprastais žodžiais tariant, ši formulė susumuoja, kai užsakoma „Dviračio stovas“ arba „Kuprinė“.
Tai viskas, ką reikia žinoti apie SUMIF ir SUMIFS funkcijas „Google“ skaičiuoklėse.