See õpetus sisaldab üksikasjalikku tutvustust, kuidas kasutada SUMIF- ja SUMIFS-i funktsioone Google'i arvutustabelites koos valemite ja näidetega.
SUMIF on üks Google'i arvutustabelite matemaatilistest funktsioonidest, mida kasutatakse lahtrite tingimuslikuks liitmiseks. Põhimõtteliselt otsib funktsioon SUMIF kindlat tingimust lahtrivahemikus ja liidab seejärel antud tingimusele vastavad väärtused.
Näiteks on teil Google'i lehtedel kulude loend ja soovite kokku võtta ainult need kulud, mis ületavad teatud maksimumväärtust. Või on teil tellitavate kaupade loend ja nende vastavad summad ning soovite teada ainult konkreetse kauba tellimuse kogusummat. Siin tuleb appi SUMIF-funktsioon.
SUMIF-i saab kasutada väärtuste summeerimiseks nii numbritingimusel, tekstitingimusel, kuupäevatingimusel, metamärkidel kui ka tühjade ja mittetühjade lahtrite põhjal. Google'i arvutustabelitel on kriteeriumide alusel väärtuste summeerimiseks kaks funktsiooni: SUMIF ja SUMIFS. Funktsioon SUMIF liidab arvud ühe tingimuse alusel, SUMIFS aga mitme tingimuse alusel.
Selles õpetuses selgitame, kuidas kasutada Google'i arvutustabelite funktsioone SUMIF ja SUMIFS teatud tingimustele vastavate arvude liitmiseks.
SUMIF-funktsioon Google'i arvutustabelites – süntaks ja argumendid
Funktsioon SUMIF on lihtsalt funktsioonide SUM ja IF kombinatsioon. Funktsioon IF skannib läbi antud tingimuse lahtrite vahemiku ja seejärel summeerib funktsioon SUM tingimusele vastavatele lahtritele vastavad numbrid.
Funktsiooni SUMIF süntaks:
Funktsiooni SUMIF süntaks Google'i arvutustabelites on järgmine:
=SUMMA(vahemik, kriteeriumid, [summa_vahemik])Argumendid:
ulatus – Lahtrite vahemik, kust otsime kriteeriumidele vastavaid lahtreid.
kriteeriumid – kriteeriumid, mis määravad, millised lahtrid tuleb lisada. Kriteeriumi aluseks võib olla nii arv, tekstistring, kuupäev, lahtri viide, avaldis, loogiline operaator, metamärk kui ka muud funktsioonid.
summa_vahemik – See argument on vabatahtlik. See on andmevahemik, mille väärtused tuleb summeerida, kui vastav vahemiku kirje vastab tingimusele. Kui te seda argumenti ei lisa, summeeritakse selle asemel "vahemik".
Nüüd vaatame, kuidas kasutada funktsiooni SUMIF erinevate kriteeriumidega väärtuste summeerimiseks.
SUMIF-funktsioon numbrikriteeriumitega
Saate lahtrivahemikus teatud kriteeriumidele vastavaid arve summeerida, kasutades kriteeriumide koostamiseks ühte järgmistest võrdlustehtetest.
- suurem kui (>)
- vähem kui (<)
- suurem või võrdne (>=)
- väiksem või võrdne (<=)
- võrdne (=)
- ei ole võrdne ()
Oletame, et teil on järgmine arvutustabel ja teid huvitab 1000 või rohkem müüki.
Funktsiooni SUMIF saate sisestada järgmiselt.
Kõigepealt valige lahter, kuhu soovite summa väljundit kuvada (D3). B2:B12 arvude summeerimiseks, mis on suuremad või võrdsed 1000-ga, tippige see valem ja vajutage sisestusklahvi:
=SUMMA(B2:B12,">=1000",B2:B12)Selles näitevalemis on vahemiku ja summa_vahemiku argumendid (B2:B12) samad, kuna müüginumbrid ja kriteeriumid rakendatakse samale vahemikule. Ja me sisestasime numbri enne võrdlusoperaatorit ja panime selle jutumärkidesse, sest kriteeriumid tuleks alati panna topeltjutumärkidesse, välja arvatud lahtri viide.
Valem otsis numbreid, mis on 1000-st suuremad või sellega võrdsed, ja seejärel liideti kõik sobitatud väärtused ja näitas tulemust lahtris D3.
Kuna vahemiku ja summa_vahemiku argumendid on samad, saate sama tulemuse saavutada ilma valemis argumentide summa_vahemikuta, näiteks järgmiselt:
=SUMMA(B2:B12,">=1000")
Või võite numbrikriteeriumide asemel sisestada lahtri viite (D2), mis sisaldab numbrit, ja ühendada võrdlusoperaatori selle lahtri viitega kriteeriumi argumendis:
=SUMMA(B2:B12,">="&D2)Nagu näete, on võrdlusoperaator ikka veel jutumärkides ning operaator ja lahtriviide on ühendatud ampersandiga (&). Ja te ei pea lahtriviiteid jutumärkidesse lisama.
Märge: Kui viitate kriteeriume sisaldavale lahtrile, ärge jätke lahtri väärtusesse algus- ega lõppu tühikut. Kui teie väärtusel on viidatud lahtri väärtuse ees või järel tarbetut tühikut, tagastab valem tulemuseks 0.
Kriteeriumide argumendis tingimuste loomiseks saate samal viisil kasutada ka muid loogilisi operaatoreid. Näiteks väärtuste, mis on väiksemad kui 500, liitmiseks:
=SUMMA(B2:B12,"<500")
Summa, kui arvud on võrdsed
Kui soovite lisada numbreid, mis võrduvad teatud arvuga, võite kriteeriumi argumenti kas sisestada ainult arvu või võrdusmärgiga arvu.
Näiteks, et summeerida vastavad müügisummad (veerg B) koguste jaoks (veerg C), mille väärtused on 20, proovige mõnda järgmistest valemitest:
=SUMMA(C2:C12,"=20",B2:B12)
=SUMMA(C2:C12,"20",B2:B12)
=SUMMA(C2:C12,E2,B2:B12)
Veerus B olevate arvude liitmiseks, mille kogus veerus C ei ole 20, proovige järgmist valemit:
=SUMMA(C2:C12,"20",B2:B12)
SUMIF-funktsioon tekstikriteeriumitega
Kui soovite liita numbreid lahtrivahemikus (veerg või rida), mis vastavad konkreetse tekstiga lahtritele, saate selle teksti või teksti sisaldava lahtri lihtsalt lisada SUMIF-valemi kriteeriumi argumenti. Pange tähele, et tekstistring tuleks alati lisada jutumärkidesse (" ").
Näiteks kui soovite läänepiirkonna müügi kogusummat, võite kasutada järgmist valemit:
=SUMF(C2:C13,"Lääs",B2:B13)Selles valemis otsib funktsioon SUMIF lahtrivahemikus C2:C13 väärtust 'Lääs' ja liidab vastava müügiväärtuse veerus B. Seejärel kuvab tulemuse lahtris E3.
Kriteeriumide argumendis oleva teksti kasutamise asemel võite viidata ka lahtrile, mis sisaldab teksti:
=SUMMA(C2:C12,E2,B2:B12)
Vaatame nüüd kõigi piirkondade kogutulu, välja arvatud "lääs". Selleks kasutame valemis operaatorit () ei võrdu:
=SUMMA(C2:C12,""&E2,B2:B12)
SUMIF koos metakaartidega
Ülaltoodud meetodi puhul kontrollib tekstikriteeriumitega funktsioon SUMIF vahemikku täpselt määratud tekstiga. Seejärel summeerib see numbrid täpse tekstiga ja ignoreerib kõiki muid numbreid, sealhulgas osaliselt sobitatud tekstistringi. Osaliselt sobivate tekstistringidega numbrite summeerimiseks peate oma kriteeriumides kohandama ühte järgmistest metamärkidest:
?(küsimärk) kasutatakse mis tahes üksiku märgi sobitamiseks tekstistringi mis tahes kohas.*(tärni) kasutatakse sobivate sõnade leidmiseks koos mis tahes märgijadaga.~(Tilde) kasutatakse tekstide sobitamiseks küsimärgiga (?) või tärniga (*).
Selle toodete ja nende koguste arvutustabeli näited liidame metamärkidega:
Tärn (*) Metamärk
Näiteks kui soovite kõigi Apple'i toodete kogused liita, kasutage järgmist valemit:
=SUMIF(A2:A14,"Õun*",B2:B14)See SUMIF-valem leiab kõik tooted, mille alguses on sõna "Apple" ja pärast seda on suvaline arv märke (tähistatud tähega "*". Kui vaste on leitud, teeb see kokkuvõtte Kogus vastavatele tekstistringidele vastavad numbrid.
Kriteeriumides on võimalik kasutada ka mitut metamärki. Samuti saate otseteksti asemel sisestada metamärke koos lahtriviidetega.
Selleks tuleb metamärgid panna jutumärkidesse (“”) ja ühendada lahtriviidetega:
=SUMMA(A2:A14,"*"&D2&"*",B2:B14)See valem liidab kõigi toodete kogused, millel on sõna "Redmi", olenemata sellest, kus see sõna stringis asub.
Küsimärk (?) Metamärk
Saate kasutada küsimärki (?), et sobitada tekstistringid mis tahes üksikute tähemärkidega.
Näiteks kui soovite leida kõigi Xiaomi Redmi 9 variantide koguseid, saate kasutada järgmist valemit:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Ülaltoodud valem otsib tekstistringe sõnaga "Xiaomi Redmi 9", millele järgneb mis tahes üksikud märgid ja summeerib vastavad Kogus numbrid.
Tilde (~) Metamärk
Kui soovite vastendada tegelikku küsimärki (?) või tärni (*), sisestage valemi tingimusosas metamärki ette tildemärk (~).
Veerus B olevate koguste lisamiseks vastava stringiga, mille lõpus on tärn, sisestage järgmine valem:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Kui soovite lisada veergu B koguseid, millel on sama rea veerus A küsimärk (?), proovige järgmist valemit:
=SUMF(A2:A14,"~?",B2:B14)
SUMIF-funktsioon koos kuupäevakriteeriumitega
Funktsioon SUMIF võib samuti aidata teil tinglikult liita väärtusi kuupäevakriteeriumide alusel – näiteks numbrid, mis vastavad teatud kuupäevale, enne kuupäeva või pärast kuupäeva. Samuti saate arvude summeerimiseks kuupäevakriteeriumide loomiseks kasutada mis tahes kuupäeva väärtusega võrdlustehteid.
Kuupäev tuleb sisestada Google'i lehtede toetatud kuupäevavormingus või lahtriviitena, mis sisaldab kuupäeva, või kasutades kuupäevafunktsiooni, nagu KUUPÄEV() või TÄNA().
Kasutame seda arvutustabeli näidet, et näidata teile, kuidas kuupäevakriteeriumitega funktsioon SUMIF töötab:
Oletame, et soovite ülalolevas andmekogumis liita müügisummad, mis toimusid (<=) 29. novembril 2019 või enne seda, saate need müüginumbrid lisada funktsiooni SUMIF abil ühel järgmistest viisidest:
=SUMIF(C2:C13,"<=29. november 2019",B2:B13)Ülaltoodud valem kontrollib iga lahtrit vahemikus C2 kuni C13 ja vastab ainult nendele lahtritele, mis sisaldavad 29. novembril 2019 (29.11.2019) või varasemaid kuupäevi. Seejärel summeerib müügisumma, mis vastab lahtrivahemiku B2:B13 vastavatele lahtritele ja kuvab tulemuse lahtrites E3.
Kuupäeva saab valemisse sisestada mis tahes vormingus, mida Google'i arvutustabelid tunnevad, nt „29. november 2019”, „29. november 2019” või „29/11/2019” jne. Pidage meeles kuupäeva väärtus ja operaator peab tuleb alati panna jutumärkidesse.
Otsese kuupäeva väärtuse asemel saate kriteeriumides kasutada ka funktsiooni DATE().
=SUMMA(C2:C13,"<="&KUUPÄEV(2019,11,29),B2:B13)
Või võite valemi kriteeriumiosas kuupäeva asemel kasutada lahtriviidet:
=SUMMA(C2:C13,"<="&E2,B2:B13)
Kui soovite müügisummad liita tänase kuupäeva alusel, saate kriteeriumide argumendis kasutada funktsiooni TODAY().
Näiteks tänase kuupäeva müügisummade summeerimiseks kasutage järgmist valemit:
=SUMMA(C2:C13,TÄNA(),B2:B13)
SUMIF funktsioon tühjade või mittetühjade lahtritega
Mõnikord peate võib-olla liitma lahtrivahemiku arvud tühjade või mittetühjade lahtritega samas reas. Sellistel juhtudel saate funktsiooni SUMIF kasutada väärtuste summeerimiseks kriteeriumide alusel, kas lahtrid on tühjad või mitte.
Summa, kui tühi
Google'i arvutustabelites on tühjade lahtrite leidmiseks kaks kriteeriumi: "" või "=".
Näiteks kui soovite veerus C summeerida kõik müügisummad, mis sisaldavad nullpikkusega stringe (näeb visuaalselt tühi), kasutage valemis kahekordseid jutumärke ilma tühikuta:
=SUMMA(C2:C13,"",B2:B13)
Kogu müügisumma veerus B koos täielike tühjade lahtritega veerus C lisage kriteeriumina "=".
=SUMF(C2:C13,"=",B2:B13)
Summa, kui see pole tühi:
Kui soovite summeerida lahtrid, mis sisaldavad mis tahes väärtust (mitte tühjad), võite valemis kasutada kriteeriumina "".
Näiteks mis tahes kuupäevaga müügi kogusumma saamiseks kasutage järgmist valemit:
=SUMMA(C2:C13,"",B2:B13)
SUMIF Põhineb mitmel VÕI-loogikaga kriteeriumil
Nagu oleme seni näinud, on funktsioon SUMIF loodud arvude summeerimiseks ainult ühe kriteeriumi alusel, kuid Google'i arvutustabelite funktsiooniga SUMIF on võimalik väärtusi liita mitme kriteeriumi alusel. Seda saab teha, ühendades VÕI-loogikaga ühte valemisse rohkem kui ühe SUMIF-funktsiooni.
Näiteks kui soovite summeerida müügisummad piirkonnas "Lääne" või "Lõuna" piirkonnas (OR loogika) määratud vahemikus (B2:B13), kasutage järgmist valemit:
=SUMF(C2:C13,"Lääne",B2:B13)+SUMF(C2:C13,"Lõuna",B2:B13)See valem liidab lahtrid, kui vähemalt üks tingimustest on TRUE. Seetõttu nimetatakse seda "OR-loogikaks". See summeerib ka väärtused, kui kõik tingimused on täidetud.
Valemi esimene osa kontrollib teksti 'Lääne' jaoks vahemikku C2:C13 ja summeerib vahemiku B2:B13 väärtused, kui vaste on täidetud. Teises osas kontrollitakse tekstiväärtust „Lõuna” samas vahemikus C2:C13 ja seejärel summeeritakse väärtused vastava tekstiga samas summa_vahemikus B2:B13. Seejärel liidetakse mõlemad summad kokku ja kuvatakse lahtris E3.
Kui täidetud on ainult üks kriteerium, tagastab see ainult selle summa väärtuse.
Ühe või kahe kriteeriumi asemel võite kasutada ka mitut. Ja kui kasutate mitut kriteeriumi, on parem kasutada valemis otsese väärtuse kirjutamise asemel kriteeriumina lahtri viidet.
=SUMF(C2:C13,E2,B2:B13)+SUMF(C2:C13,E3,B2:B13)+SUMF(C2:C13,E4,B2:B13)
SUMIF koos VÕI loogikaga lisab väärtused, kui vähemalt üks määratud kriteeriumidest on täidetud, kuid kui soovite väärtusi liita ainult siis, kui kõik määratud tingimused on täidetud, peate kasutama selle uut funktsiooni SUMIFS().
SUMIFS-i funktsioon Google'i arvutustabelites (mitme kriteeriumiga)
Kui kasutate funktsiooni SUMIF väärtuste summeerimiseks mitme kriteeriumi alusel, võib valem muutuda liiga pikaks ja keeruliseks ning teil on kalduvus teha vigu. Lisaks võimaldab SUMIF teil väärtusi liita ainult ühes vahemikus ja kui mõni tingimus on TRUE. Siin tulebki sisse funktsioon SUMIFS.
Funktsioon SUMIFS aitab teil väärtusi liita mitme sobituskriteeriumi alusel ühes või mitmes vahemikus. Ja see töötab JA-loogikal, mis tähendab, et see saab väärtusi summeerida ainult siis, kui kõik antud tingimused on täidetud. Isegi kui üks tingimus on vale, tagastab see tulemuseks 0.
SUMIFS Funktsiooni süntaks ja argumendid
Funktsiooni SUMIFS süntaks on järgmine:
=SUMIFS(summa_vahemik, kriteeriumide_vahemik1, kriteerium1, [kriteeriumide_vahemik2, ...], [kriteerium2, ...])kus,
- summa_vahemik – Lahtrite vahemik, mis sisaldab väärtusi, mille soovite summeerida, kui kõik tingimused on täidetud.
- kriteeriumivahemik1 – See on lahtrite vahemik, kus kontrollite kriteeriume1.
- kriteeriumid 1 - See on tingimus, mida peate kontrollima kriteeriumide_vahemiku1 suhtes.
- kriteria_range2, kriteerium2, …– täiendavad hindamisvahemikud ja kriteeriumid. Ja saate lisada valemile rohkem vahemikke ja tingimusi.
Kasutame järgmisel ekraanipildil olevat andmestikku, et näidata, kuidas funktsioon SUMIFS töötab erinevate kriteeriumidega.
SUMIFS tekstitingimustega
Saate väärtusi liita kahe erineva tekstikriteeriumi alusel erinevates vahemikes. Oletame näiteks, et soovite teada tarnitud Telgi kauba müügi kogusummat. Selleks kasutage järgmist valemit:
=SUMIFS(D2:D13,A2:A13,"Telk",C2:C13,"Tarnitud")Selles valemis on meil kaks kriteeriumi: "Telk" ja "Tarnitud". Funktsioon SUMIFS kontrollib üksust "Telk" (kriteeriumid1) vahemikus A2:A13 (kriteeriumide_vahemik1) ja olekut "Tarnitud" (kriteeriumid2) vahemikus C2:C13 (kriteeriumide_vahemik2). Kui mõlemad tingimused on täidetud, summeerib see vastava väärtuse lahtrivahemikus D2:D13 (summa_vahemik).
SUMIFS numbrikriteeriumide ja loogiliste operaatoritega
Funktsiooni SUMIFS jaoks numbritega tingimuste loomiseks saate kasutada tingimuslikke operaatoreid.
California osariigis (CA) mis tahes kauba enam kui 5 müügikoguse leidmiseks kasutage järgmist valemit:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13"CA")Sellel valemil on kaks tingimust: ">5" ja "CA".
See valem kontrollib koguseid (Qty), mis on suuremad kui 5 vahemikus D2:D13, ja kontrollib olekut „CA” vahemikus B2:B13. Ja kui mõlemad tingimused on täidetud (see tähendab, et need on samas reas), summeerib see summa E2:E13.
SUMIFS koos kuupäeva kriteeriumidega
SUMIFS-funktsioon võimaldab teil kontrollida ka mitut tingimust samas vahemikus ja ka erinevates vahemikes.
Oletame, et soovite kontrollida tarnitud kauba kogusummat pärast 31.05.2021 ja enne 10.06.2021 kuupäeva, siis kasutage järgmist valemit:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Ülaltoodud valemil on kolm tingimust: 31/5/2021, 10/5/2021 ja Tarnitud. Otsese kuupäeva ja teksti väärtuste kasutamise asemel viitasime neid kriteeriume sisaldavatele lahtritele.
Valem kontrollib kuupäevi pärast 31.05.2021 (G1) ja kuupäevi enne 10.06.2021 (G2) samas vahemikus D2:D13 ning kontrollib nende kahe kuupäeva vahel olekut „Tarnitud”. Seejärel liidab seotud summa vahemikus E2:E13.
SUMIFS tühjade ja mittetühjade lahtritega
Mõnikord võite soovida leida väärtuste summa, kui vastav lahter on tühi või mitte. Selleks võite kasutada ühte kolmest kriteeriumist, mida me varem käsitlesime: "=", "" ja "".
Näiteks kui soovite liita ainult nende „Telgi“ kaupade koguse, mille tarnekuupäeva pole veel kinnitatud (tühjad lahtrid), võite kasutada kriteeriume "=":
=SUMIFS(D2:D13,A2:A13,"telk",C2:C13,"=")Valem otsib veerus A kirje „Telk” (kriteerium1) koos vastavate tühjade lahtritega (kriteeriumid2) veerus C ja seejärel summeerib vastava summa veerus D. „=” tähistab täiesti tühja lahtrit.
Kinnitatud tarnekuupäeva (mitte tühjad lahtrid) 'Telgi' kaupade summa leidmiseks kasutage kriteeriumina "".
=SUMIFS(D2:D13,A2:A13"Telk",C2:C13"")Vahetasime selles valemis lihtsalt "=" vastu "". See leiab veerus C mittetühjade lahtritega Telgi üksuste summa.
SUMIFS koos VÕI loogikaga
Kuna funktsioon SUMIFS töötab JA-loogikal, teeb see summa ainult siis, kui kõik tingimused on täidetud. Aga mis siis, kui soovite väärtust summeerida mitme kriteeriumi alusel, kui mõni kriteerium on täidetud. Trikk on kasutada mitut SUMIFS-i funktsiooni.
Näiteks kui soovite liita müügisumma kas „Jalgrattahoidja” VÕI „Seljakoti” puhul, kui nende olek on „Tellitud”, proovige järgmist valemit:
=SUMIFS(D2:D13,A2:A13"Jalgrattahoidja",C2:C13"tellitud") +SUMIFS(D2:D13,A2:A13"seljakott",C2:C13"tellitud")Esimene SUMIFS-funktsioon kontrollib kahte kriteeriumi „Jalgrattahoidja” ja „Tellitud” ning summeerib veerus D olevad summad. Seejärel kontrollib teine SUMIFS kahte kriteeriumi „Tagakott” ja „Tellitud” ning liidab veerus D olevad summad. , liidetakse mõlemad summad kokku ja kuvatakse klahvil F3. Lihtsamalt öeldes võtab see valem kokku siis, kui tellitakse kas „Jalgrattaraam” või „Seljakott”.
See on kõik, mida peate Google'i arvutustabelite funktsioonide SUMIF ja SUMIFS kohta teadma.