Ez az oktatóanyag képletekkel és példákkal részletesen bemutatja, hogyan kell használni a SUMIF és SUMIFS függvényeket a Google Táblázatokban.
A SUMIF a Google Táblázatok egyik matematikai függvénye, amely cellák feltételes összegzésére szolgál. Alapvetően a SUMIF függvény egy adott feltételt keres egy cellatartományban, majd összeadja az adott feltételnek megfelelő értékeket.
Például van egy költséglistája a Google-táblázatokban, és csak azokat a kiadásokat szeretné összesíteni, amelyek egy bizonyos maximális értéket meghaladnak. Vagy van egy listája a rendelési cikkekről és a hozzájuk tartozó összegekről, és csak egy adott cikk teljes rendelési összegét szeretné tudni. Itt jön jól a SUMIF funkció.
A SUMIF használható értékek összegzésére számfeltételek, szövegfeltételek, dátumfeltételek, helyettesítő karakterek, valamint üres és nem üres cellák alapján. A Google Táblázatok két funkciója a feltételek alapján összegzi az értékeket: SUMIF és SUMIFS. A SUMIF funkció egy feltétel alapján, míg a SUMIFS több feltétel alapján ad össze számokat.
Ebben az oktatóanyagban elmagyarázzuk, hogyan használhatjuk a SUMIF és SUMIFS függvényeket a Google Táblázatokban bizonyos feltétel(ek)nek megfelelő számok összegzésére.
SUMIF függvény a Google Táblázatokban – Szintaxis és argumentumok
A SUMIF funkció csak a SUM és IF függvény kombinációja. Az IF függvény végigpásztázza egy adott feltétel cellatartományát, majd a SUM függvény összegzi a feltételnek megfelelő celláknak megfelelő számokat.
A SUMIF függvény szintaxisa:
A SUMIF függvény szintaxisa a Google Táblázatokban a következő:
=SUMIF(tartomány; feltételek; [összeg_tartomány])Érvek:
hatótávolság - A cellák tartománya, ahol a kritériumoknak megfelelő cellákat keressük.
kritériumok – A feltételek, amelyek meghatározzák, hogy mely cellákat kell hozzáadni. A feltételt számra, szöveges karakterláncra, dátumra, cellahivatkozásra, kifejezésre, logikai operátorra, helyettesítő karakterre és egyéb függvényekre alapozhatja.
összeg_tartomány – Ez az érv nem kötelező. Ez az összeadandó értékekkel rendelkező adattartomány, ha a megfelelő tartománybejegyzés megfelel a feltételnek. Ha nem adja meg ezt az argumentumot, akkor helyette a „tartomány” összegzése történik.
Most pedig nézzük meg, hogyan használhatjuk a SUMIF függvényt különböző kritériumok szerinti értékek összegzésére.
SUMIF függvény számkritériumokkal
A következő összehasonlító operátorok egyikével összeadhatja azokat a számokat, amelyek megfelelnek bizonyos feltételeknek egy cellatartományban.
- nagyobb mint (>)
- kevesebb, mint (<)
- nagyobb vagy egyenlő, mint (>=)
- kisebb vagy egyenlő (<=)
- egyenlő (=)
- nem egyenlő ()
Tegyük fel, hogy rendelkezik a következő táblázattal, és érdekli az 1000 vagy annál nagyobb összértékesítés.
Így léphet be a SUMIF funkcióba:
Először válassza ki azt a cellát, ahol meg szeretné jeleníteni az összeg kimenetét (D3). A B2:B12-ben 1000-nél nagyobb vagy azzal egyenlő számok összegzéséhez írja be ezt a képletet, és nyomja meg az „Enter” billentyűt:
=SZUMF(B2:B12,">=1000",B2:B12)Ebben a példaképletben a tartomány és a tartomány összege argumentumok (B2:B12) megegyeznek, mivel az értékesítési számok és feltételek ugyanarra a tartományra vonatkoznak. És beírtuk a számot az összehasonlító operátor elé, és idézőjelbe tettük, mert a kritériumokat mindig dupla idézőjelbe kell tenni, kivéve a cellahivatkozást.
A képlet 1000-nél nagyobb vagy azzal egyenlő számokat keresett, majd összeadta az összes egyező értéket, és az eredményt a D3 cellában jelenítette meg.
Mivel a tartomány és a sum_range argumentumok megegyeznek, ugyanazt az eredményt érheti el a képletben szereplő sum_range argumentumok nélkül, így:
=SUMIF(B2:B12,">=1000")
Vagy megadhatja a cellahivatkozást (D2), amely a számot tartalmazza a számfeltételek helyett, és a feltétel argumentumában összekapcsolja az összehasonlító operátort ezzel a cellahivatkozással:
=SUMIF(B2:B12,">="&D2)Amint láthatja, az összehasonlító operátor továbbra is dupla idézőjelben van megadva, és az operátor és a cellahivatkozás egy és-jellel (&) van összefűzve. És nem kell idézőjelbe tenni a cellahivatkozást.
Jegyzet: Amikor a feltételeket tartalmazó cellára hivatkozik, ügyeljen arra, hogy ne hagyjon kezdő vagy záró szóközt a cella értékében. Ha az értékben szükségtelen szóköz van az érték előtt vagy után a hivatkozott cellában, akkor a képlet „0”-t ad vissza.
Hasonló módon más logikai operátorokat is használhat a feltételek argumentumában való feltételek megadásához. Például 500-nál kisebb értékek összegzéséhez:
=SUMIF(B2:B12,"<500")
Összegzés, ha a számok egyenlőek
Ha egy bizonyos számmal egyenlő számokat szeretne hozzáadni, akkor megadhatja csak a számot, vagy megadhatja az egyenlőségjellel ellátott számot a feltétel argumentumában.
Például a megfelelő értékesítési összegek (B oszlop) összegzéséhez olyan mennyiségekhez (C oszlop), amelyek értéke 20, próbálja ki az alábbi képletek bármelyikét:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12;E2;B2:B12)
Ha a B oszlopban olyan számokat szeretne összeadni, amelyek mennyisége nem 20 a C oszlopban, próbálja ki a következő képletet:
=SUMIF(C2:C12,"20",B2:B12)
SUMIF funkció szöveges kritériumokkal
Ha számokat szeretne összeadni egy cellatartományban (oszlopban vagy sorban), amely megfelel azoknak a celláknak, amelyekben egy adott szöveg található, egyszerűen beillesztheti a szöveget vagy a szöveget tartalmazó cellát a SUMIF képlet feltételargumentumába. Kérjük, vegye figyelembe, hogy a szöveget mindig dupla idézőjelbe (" ") kell tenni.
Például, ha a „Nyugati” régió eladásainak teljes összegét szeretné elérni, használhatja az alábbi képletet:
=SUMIF(C2:C13,"Nyugat",B2:B13)Ebben a képletben a SUMIF függvény a „Nyugat” értéket keresi a C2:C13 cellatartományban, és összeadja a megfelelő értékesítési értéket a B oszlopban. Ezután az eredményt az E3 cellában jeleníti meg.
Hivatkozhat a szöveget tartalmazó cellára is a szöveg használata helyett a feltétel argumentumában:
=SUMIF(C2:C12;E2;B2:B12)
Most nézzük meg a „Nyugat” kivételével az összes régió teljes bevételét. Ehhez a nem egyenlő operátorral () használjuk a képletben:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF helyettesítő kártyákkal
A fenti módszerben a SUMIF függvény szöveges feltételekkel ellenőrzi a tartományt a pontosan megadott szöveggel. Ezután összegzi a számokat a pontos szöveghez, és figyelmen kívül hagy minden más számot, beleértve a részben egyező szöveges karakterláncot is. A számok részlegesen egyező szöveges karakterláncokkal való összegzéséhez a következő helyettesítő karakterek egyikét kell személyre szabnia a feltételekben:
?A (kérdőjel) bármely karakterhez illeszkedik, bárhol a szöveges karakterláncban.*(csillag) a megfelelő szavak és karaktersorozatok keresésére szolgál.~A (tilde) kérdőjellel (?) vagy csillaggal (*) rendelkező szövegek párosítására szolgál.
Ezt a példatáblázatot a termékekre és mennyiségeikre vonatkozóan helyettesítő karakterekkel összegzi:
Csillag (*) Helyettesítő karakter
Például, ha össze akarja adni az összes Apple termék mennyiségét, használja ezt a képletet:
=SUMIF(A2:A14,"Alma*",B2:B14)Ez a SUMIF képlet megtalálja az összes olyan terméket, amelynek elején az „Apple” szó, és tetszőleges számú karakter utána (jelezve „*”). Miután megtalálta a találatot, összegzi a Mennyiség a megfelelő szöveges karakterláncoknak megfelelő számokat.
A feltételekben több helyettesítő karakter is használható. És beírhat helyettesítő karaktereket is cellahivatkozással a közvetlen szöveg helyett.
Ehhez a helyettesítő karaktereket dupla idézőjelbe (" ") kell tenni, és össze kell kapcsolni a cellahivatkozás(ok)kal:
=SUMIF(A2:A14"*"&D2&"*",B2:B14)Ez a képlet összeadja az összes olyan termék mennyiségét, amelyekben a „Redmi” szó szerepel, függetlenül attól, hogy a szó hol található a karakterláncban.
Kérdőjel (?) Helyettesítő karakter
Használhatja a kérdőjel (?) helyettesítő karaktert a szöveges karakterláncok és az egyes karakterek párosítására.
Például, ha meg szeretné találni a Xiaomi Redmi 9 összes változatának mennyiségét, használja ezt a képletet:
=SUMIF(A2:A14"Xiaomi Redmi 9?",B2:B14)A fenti képlet olyan szöveges karakterláncokat keres, amelyekben a „Xiaomi Redmi 9” szó, majd tetszőleges karakter követi, és összegzi a megfelelő Mennyiség számok.
Tilde (~) Helyettesítő karakter
Ha tényleges kérdőjelet (?) vagy csillag karaktert (*) szeretne egyeztetni, illessze be a tilde (~) karaktert a helyettesítő karakter elé a képlet feltétel részében.
A B oszlopban lévő mennyiségek hozzáadásához a megfelelő karakterlánccal, amelyek végén csillag jel van, írja be az alábbi képletet:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Ha olyan mennyiségeket szeretne hozzáadni a B oszlophoz, amelyeknek ugyanabban a sorban az A oszlopában kérdőjel (?) van, próbálja ki az alábbi képletet:
=SUMIF(A2:A14,"~?",B2:B14)
SUMIF függvény dátum kritériumokkal
A SUMIF funkció segítségével feltételesen összegezheti az értékeket dátum kritériumok alapján – például egy bizonyos dátumhoz tartozó, dátum előtti vagy dátum utáni számok. A dátumértékkel rendelkező összehasonlító operátorok bármelyikét is használhatja dátumfeltételek létrehozásához a számok összegzéséhez.
A dátumot a Google Táblázatok által támogatott dátumformátumban kell megadni, vagy dátumot tartalmazó cellahivatkozásként, vagy dátumfüggvény használatával, például DÁTUM() vagy TODAY().
Ezt a példatáblázatot fogjuk használni, hogy megmutassuk, hogyan működik a SUMIF függvény dátumfeltételekkel:
Tegyük fel, hogy a fenti adatkészletben összegezni szeretné azokat az értékesítési összegeket, amelyek 2019. november 29-én vagy azt megelőzően történtek, ezeket az értékesítési számokat a SUMIF függvény segítségével a következő módok egyikén adhatja hozzá:
=SUMIF(C2:C13,"<=2019. november 29.",B2:B13)A fenti képlet C2-től C13-ig ellenőrzi az egyes cellákat, és csak azokra a cellákra egyezik, amelyek 2019. november 29-i (2019.11.29.) dátumot tartalmaznak. Ezután összeadja a B2:B13 cellatartomány egyező celláinak megfelelő eladási összeget, és az eredményt az E3 cellákban jeleníti meg.
A dátum bármilyen, a Google Táblázatok által felismert formátumban megadható a képletben, például „2019. november 29.”, „2019. november 29.”, „2019. november 29.” stb. Ne felejtse el a dátumértéket, és az operátornak kell mindig dupla idézőjelbe kell tenni.
Használhatja a DATE() függvényt is a feltételekben a közvetlen dátumérték helyett:
=SUMIF(C2:C13,"<="&DATE(2019;11;29),B2:B13)
Vagy használhatja a cellahivatkozást a dátum helyett a képlet feltételek részében:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Ha az eladási összegeket a mai dátum alapján szeretné összeadni, akkor a TODAY() függvényt használhatja a feltételek argumentumában.
Például a mai dátum összes eladási összegének összegzéséhez használja ezt a képletet:
=SUMIF(C2:C13,MA(),B2:B13)
SUMIF funkció üres vagy nem üres cellákkal
Néha előfordulhat, hogy összegeznie kell egy cellatartományban lévő számokat úgy, hogy ugyanabban a sorban vannak üres vagy nem üres cellák. Ilyen esetekben használhatja a SUMIF függvényt az értékek összegzésére azon feltételek alapján, hogy a cellák üresek-e vagy sem.
Összeg, ha üres
A Google Táblázatok két feltétele van az üres cellák megtalálásának: „” vagy „=”.
Például, ha össze akarja adni az összes olyan eladási összeget, amely nulla hosszúságú karakterláncokat tartalmaz (vizuálisan üresnek tűnik) a C oszlopban, használjon dupla idézőjeleket úgy, hogy ne legyen szóköz a képletben:
=SUMIF(C2:C13,"",B2:B13)
Ha a B oszlopban lévő eladási összeget a C oszlop üres celláival szeretné összeadni, adja meg a „=” feltételt:
=SUMIF(C2:C13,"=",B2:B13)
Összeg, ha nem üres:
Ha olyan cellákat szeretne összegezni, amelyek bármilyen értéket tartalmaznak (nem üresek), használhatja a „” feltételt a képletben:
Például az eladások teljes összegének kiszámításához bármilyen dátummal, használja ezt a képletet:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Több kritérium alapján VAGY logikával
Amint azt eddig láttuk, a SUMIF függvényt úgy tervezték, hogy egyetlen feltétel alapján összegezze a számokat, de a Google Táblázatok SUMIF függvényével több feltétel alapján is lehetséges az értékek összegzése. Megtehető úgy, hogy egynél több SUMIF függvényt egyetlen képletbe kapcsolunk VAGY logikával.
Például, ha a „Nyugati” régióban vagy a „Déli” régióban (VAGY logika) a megadott tartományban (B2:B13) szeretné összegezni az eladási összeget, használja ezt a képletet:
=SUMIF(C2:C13,"Nyugat",B2:B13)+SUMIF(C2:C13,"Dél",B2:B13)Ez a képlet akkor összegzi a cellákat, ha legalább az egyik feltétel IGAZ. Ezért „VAGY logika” néven ismert. Az értékeket is összegzi, ha minden feltétel teljesül.
A képlet első része a C2:C13 tartományban ellenőrzi a „Nyugat” szöveget, és összegzi a B2:B13 tartomány értékeit, ha az egyezés teljesül. A második része a „Dél” szövegérték ellenőrzésének ugyanabban a C2:C13 tartományban, majd összegzi az értékeket a megfelelő szöveggel ugyanabban a B2:B13 összegtartományban. Ezután mindkét összeg összeadódik, és megjelenik az E3 cellában.
Ha csak egy feltétel teljesül, akkor csak azt az összeget adja vissza.
Egy vagy kettő helyett több feltételt is használhat. És ha több feltételt használ, jobb, ha egy cellahivatkozást használ kritériumként, ahelyett, hogy a közvetlen értéket írná be a képletbe.
=SZUMIF(C2:C13,E2,B2:B13)+SZUMF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
A SUMIF a VAGY logikával akkor ad hozzá értékeket, ha a megadott feltételek közül legalább egy teljesül, de ha csak akkor szeretne értékeket összegezni, ha az összes megadott feltétel teljesül, akkor az új testvér SUMIFS() függvényt kell használnia.
SUMIFS függvény a Google Táblázatokban (több feltétel)
Ha a SUMIF függvényt több feltétel alapján összegezi, a képlet túl hosszú és bonyolult lehet, és hajlamos hibázni. Emellett a SUMIF csak egyetlen tartományban teszi lehetővé az értékek összegzését, és ha bármelyik feltétel IGAZ. Itt jön be a SUMIFS funkció.
A SUMIFS funkció segít összegezni az értékeket több egyezési feltétel alapján egy vagy több tartományban. És az ÉS logikán működik, vagyis csak akkor tud értékeket összegezni, ha minden adott feltétel teljesül. Még ha az egyik feltétel hamis is, ennek eredményeként „0”-t ad vissza.
SUMIFS függvény szintaxisa és argumentumai
A SUMIFS függvény szintaxisa a következő:
=SUMIFS(összeg_tartomány, feltétel_tartomány1, feltétel1, [feltétel_tartomány2, ...], [feltétel2, ...])Ahol,
- sum_range – Az összes feltétel teljesülése esetén összegezni kívánt értékeket tartalmazó cellatartomány.
- kritérium_tartomány1 – Ez az a cellatartomány, ahol ellenőrizheti a kritérium1-et.
- kritérium1 - Ez az a feltétel, amelyet ellenőriznie kell a kritérium_tartomány1 függvényében.
- criteria_range2, kritérium2, …– További értékelési tartományok és kritériumok. És további tartományokat és feltételeket adhat hozzá a képlethez.
A következő képernyőképen látható adatkészletet használjuk annak bemutatására, hogyan működik a SUMIFS függvény különböző feltételek mellett.
SUMIFS szöveges feltételekkel
Két különböző szöveges feltétel alapján összegezhet értékeket különböző tartományokban. Tegyük fel például, hogy szeretné megtudni a leszállított sátorcikk teljes eladási összegét. Ehhez használja ezt a képletet:
=SUMIFS(D2:D13,A2:A13"sátor",C2:C13,"Kézbesítve")Ebben a képletben két kritérium van: „Sátor” és „Kézbesítés”. A SUMIFS funkció ellenőrzi a „Tent” (kritérium1) elemet az A2:A13 (feltételtartomány1) tartományban, és ellenőrzi a „Delivered” (criteria2) állapotot a C2:C13 (kritériumtartomány2) tartományban. Ha mindkét feltétel teljesül, akkor összegzi a megfelelő értéket a D2:D13 cellatartományban (összeg_tartomány).
SUMIFS számkritériumokkal és logikai operátorokkal
Feltételes operátorok segítségével számokkal hozhat létre feltételeket a SUMIFS funkcióhoz.
A Kalifornia államban (Kalifornia állam) származó termékek több mint 5 mennyiségének teljes értékesítéséhez használja a következő képletet:
=SUMIFS(E2:E13;D2:D13;">5",B2:B13"CA")Ennek a képletnek két feltétele van: „>5” és „CA”.
Ez a képlet az 5-nél nagyobb mennyiségeket (Mennyiség) ellenőrzi a D2:D13 tartományban, és ellenőrzi a „CA” állapotot a B2:B13 tartományban. És ha mindkét feltétel teljesül (azaz ugyanabban a sorban vannak), akkor az E2:E13-ban összegzi az összeget.
SUMIFS dátumkritériumokkal
A SUMIFS funkció lehetővé teszi több feltétel ellenőrzését is ugyanabban a tartományban, valamint különböző tartományokban.
Tegyük fel, hogy ellenőrizni szeretné a leszállított cikkek teljes eladási összegét 2021.05.31. után és 2021.10.06. előtt, akkor használja a következő képletet:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)A fenti képletnek három feltétele van: 2021.05.31., 2021.10.05. és Kézbesítés. A közvetlen dátum- és szövegértékek használata helyett az ezeket a feltételeket tartalmazó cellákra hivatkoztunk.
A képlet ellenőrzi a 2021.05.31. (G1) és a 2021.10.06. előtti (G2) dátumokat ugyanabban a D2:D13 tartományban, és ellenőrzi a „Kézbesítés” állapotot a két dátum között. Ezután összegezze a kapcsolódó összeget az E2:E13 tartományban.
SUMIFS üres és nem üres cellákkal
Néha érdemes megkeresni az értékek összegét, ha a megfelelő cella üres vagy nem. Ehhez használhatja a korábban tárgyalt három kritérium egyikét: „=”, „” és „”.
Például, ha csak azoknak a „Sátor” tételeknek az összegét szeretné összeadni, amelyek szállítási dátuma még nincs megerősítve (üres cellák), használhatja a „=” kritériumot:
=SUMIFS(D2:D13;A2:A13,"sátor",C2:C13,"=")A képlet megkeresi a „Sátor” elemet (1. kritérium), a megfelelő üres cellákkal (2. kritérium) a C oszlopban, majd összegzi a megfelelő összeget a D oszlopban. A „=” egy teljesen üres cellát jelöl.
A „sátor” tételek összegének meghatározásához, amelyek szállítási dátumát megerősítették (nem üres cellák), használja a „” kritériumot:
=SUMIFS(D2:D13;A2:A13"sátor",C2:C13")Ebben a képletben a „=”-t „”-re cseréltük. Megkeresi a C oszlopban lévő nem üres cellákkal rendelkező sátorelemek összegét.
SUMIFS VAGY logikával
Mivel a SUMIFS függvény ÉS logikán működik, csak akkor összegez, ha minden feltétel teljesül. De mi van akkor, ha több feltétel alapján szeretne összegezni az értéket, ha bármelyik feltétel teljesül. A trükk több SUMIFS funkció használata.
Például, ha össze szeretné adni az eladási összeget a „Bringatartó” VAGY a „Hátizsák” esetében, amikor állapota „Megrendelt”, próbálja ki a következő képletet:
=SUMIFS(D2:D13,A2:A13"Kerékpártartó",C2:C13"Rendezett") +SUMIFS(D2:D13,A2:A13"Hátizsák",C2:C13"Rendezett")Az első SUMIFS funkció ellenőrzi a két feltételt: „Bike rack” és „Ordered”, és összegzi a D oszlopban lévő összegértékeket. Ezután a második SUMIFS ellenőrzi a „Hátizsák” és a „Megrendelt” két feltételt, és összegzi a D oszlopban lévő összegértékeket. , mindkét összeg összeadódik és megjelenik az F3-on. Egyszerűen fogalmazva, ez a képlet összegzi a „Bike rack” vagy a „Hátizsák” rendelését.
Ez minden, amit tudnia kell a Google Táblázatok SUMIF és SUMIFS funkciójáról.