SUMIF függvény Excelben és összegzés több feltétellel

Az Excel egy hihetetlenül funkcionális program. Még a beépített funkciókészlet is elegendő szinte bármilyen feladat elvégzéséhez. És a sokak számára ismerős szabványok mellett vannak olyanok is, amelyekről kevesen hallottak. De ugyanakkor nem szűnnek meg hasznosak lenni. Szűkebb a szakterületük, és nem mindig van rájuk igény. De ha tudsz róluk, akkor egy kritikus pillanatban nagyon hasznosak lehetnek.

Ma az egyik ilyen függvényről fogunk beszélni − SUMMESLIMN.

Ha a felhasználó azzal a feladattal szembesül, hogy több értéket összegezzen, bizonyos kritériumokra összpontosítva, akkor a funkció használata szükséges. SUMMESLIMN. Az ezt a függvényt használó képlet argumentumként veszi ezeket a feltételeket, majd összegzi az ezeknek megfelelő értékeket, majd a talált értéket beírja abba a cellába, amelybe beírta. 

SUMIFS Funkció Részletes leírás

Mielőtt megvizsgálná a funkciót SUMMESLIMN, először meg kell értened, mi ennek az egyszerűbb változata – SUMMESLI, mivel ezen alapul az általunk vizsgált függvény. Valószínűleg mindannyian ismerünk már két gyakran használt funkciót – ÖSSZEG (értékek összegzését végzi) ill HA (tesztel egy értéket egy meghatározott feltételhez képest).

Ha ezeket kombinálja, egy másik funkciót kap – SUMMESLI, amely ellenőrzi az adatokat a felhasználó által megadott feltételekhez képest, és csak azokat a számokat összegzi, amelyek megfelelnek ezeknek a feltételeknek. Ha az Excel angol verziójáról beszélünk, akkor ezt a függvényt SUMIF-nek hívják. Egyszerű szavakkal, a -nyelvű név az angol nyelv közvetlen fordítása. Ez a funkció többféle célra használható. Különösen alternatívaként használható VPR, vagyis írd le

A fő különbség a funkció között SUMMESLIMN  a szokásos funkciótól SUMMESLI az, hogy több kritériumot alkalmaznak. Szintaxisa első ránézésre meglehetősen bonyolult, de közelebbről megvizsgálva kiderül, hogy ennek a függvénynek a logikája nagyon egyszerű. Először ki kell választania azt a tartományt, ahol az adatokat ellenőrizni fogja, majd be kell állítania az elemzés végrehajtásának feltételeit. És egy ilyen művelet meglehetősen nagy számú feltétel esetén elvégezhető.

Maga a szintaxis a következő:

SUMIFS(összeg_tartomány, feltétel_tartomány1, feltétel1, [feltétel_tartomány2, feltétel2], …)

A megfelelő helyekre egy adott esetre megfelelő cellatömböket kell elhelyezni. 

Nézzük részletesebben az érveket:

1. Összeg_tartomány. Ez az argumentum, valamint az 1. és 1. feltétel tartománya kötelező. Ez egy olyan cellakészlet, amelyet összegezni kell.
2. Feltétel_tartomány1. Ez az a tartomány, ahol a feltétel ellenőrzésre kerül. Párosítva van a következő argumentummal – Feltétel1. A kritériumnak megfelelő értékek összegzése az előző argumentumban megadott cellákon belül történik.
3. Feltétel1. Ez az argumentum meghatározza azokat a kritériumokat, amelyek alapján ellenőrizni kell. Beállítható például így: “> 32”.
4. Feltétel tartomány 2, feltétel 2… Itt a következő feltételek azonos módon vannak beállítva. Ha néhány feltételnél több feltételt kell megadni, akkor a rendszer hozzáadja a 3. feltételtartomány és a 3. feltétel argumentumokat. A szintaxis megegyezik a következő argumentumokkal.

A funkció legfeljebb 127 pár feltétel és tartomány maximális feldolgozását teszi lehetővé. 

Egyszerre több területen is használható (csak néhányat adunk meg, a lista valójában még hosszabb):

1. Könyvelés. Például jó a függvényt használni SUMMESLIMN összefoglaló jelentések készítéséhez negyedévenként, például egy bizonyos összeg feletti kiadásokról. Vagy készítsen jelentést egy bizonyos árkategóriából származó termékről.
2. Értékesítési menedzsment. Itt is nagyon hasznos lehet a funkció. Például azzal a feladattal állunk szemben, hogy csak azoknak az áruknak a költségeit összegezzük, amelyeket egy bizonyos vevőnek adott időpontban értékesítettek. És ilyen helyzetben a funkció SUMMESLIMN nagyon hasznos lehet.
3. Oktatás. Erről a területről ma több gyakorlati példát adunk. Különösen arra használhatja, hogy összefoglalót kapjon a tanulói osztályzatokról. Választhat egy tantárgyból vagy egyes évfolyamokból. Egy személy azonnal beállíthat több kritériumot, amelyek alapján az értékelést kiválasztják, ami nagyon kényelmes és sok időt takaríthat meg.

Amint láthatja, ennek a funkciónak az alkalmazási köre nagyon széles. De nem ez az egyetlen érdeme. Nézzünk meg néhány további előnyt, amelyekkel ez a funkció rendelkezik:

1. Több kritérium beállításának lehetősége. Miért előny ez? Használhatja a normál funkciót SUMMESLI! És mindezt azért, mert kényelmes. Nem szükséges külön számításokat végezni az egyes kritériumok esetében. Minden művelet előre programozható, akár korábban is. hogyan fog létrejönni az adattábla. Ez egy nagyszerű időmegtakarítás.
2. Automatizálás. A modern kor az automatizálás kora. Csak az tud sokat keresni, aki tudja, hogyan kell megfelelően automatizálni a munkáját. Ezért az Excel és a funkció elsajátításának képessége SUMMESLIMN különösen fontos minden olyan személy számára, aki karriert akar építeni. Egy funkció ismerete lehetővé teszi több művelet egyidejű végrehajtását. És itt áttérünk a funkció következő előnyére.
3. Időt megtakarítani. Már csak azért is, mert egy funkció több feladatot lát el egyszerre.
4. Egyszerűség. Annak ellenére, hogy a szintaxis első pillantásra meglehetősen nehéz a terjedelmessége miatt, valójában ennek a függvénynek a logikája nagyon egyszerű. Először egy adattartomány kerül kiválasztásra, majd egy értéktartomány, amely ellenőrzi, hogy megfelel-e egy bizonyos feltételnek. És persze magát a feltételt is meg kell adni. És így többször is. Valójában ez a függvény egyetlen logikai konstrukción alapul, ami egyszerűbbé teszi a jól ismertnél VPR annak ellenére, hogy több szempontot is figyelembe véve ugyanazokra a célokra használható. 

A SUMIFS funkció használatának jellemzői

A funkció használatának számos olyan funkciója van, amelyekre figyelnie kell. Először is, ez a függvény figyelmen kívül hagyja a szöveges karakterláncokat vagy nullákat tartalmazó tartományokat, mivel ezek az adattípusok nem adhatók össze egy aritmetikai mintában, csak összefűzhetők, mint a karakterláncok. Ez a funkció nem tudja ezt megtenni. A következő feltételekre is figyelni kell:

1. Az ilyen típusú értékeket feltételekként használhatja a cellák kiválasztásához a bennük lévő értékek további hozzáadásához: numerikus értékek, logikai kifejezések, cellahivatkozások stb. 
2. Ha szöveget, logikai kifejezéseket vagy matematikai jeleket ellenőrzünk, akkor ezeket a feltételeket idézőjelek határozzák meg.
3. Nem használhatók 255 karakternél hosszabb kifejezések.
4. Hozzávetőleges kritériumok használhatók az értékek helyettesítő karakterek használatával történő kiválasztásához. A kérdőjel egyetlen karakter, a szorzójel (csillag) pedig több karakter helyettesítésére szolgál. 
5. Az összegzési tartományban lévő logikai értékek típusuk szerint automatikusan számértékekké alakulnak. Így az „IGAZ” érték eggyel, a „FALSE” pedig nullává változik. 
6. Ha egy #ÉRTÉK! hiba jelenik meg egy cellában, ez azt jelenti, hogy a cellák száma a feltétel- és összegzési tartományban eltérő. Győződjön meg arról, hogy ezeknek az argumentumoknak a mérete megegyezik. 

Példák a SUMIFS funkció használatára

Funkció SUMMESLIMN nem olyan bonyolult, mint amilyennek első pillantásra tűnik, kiderül. De a nagyobb érthetőség kedvéért nézzünk meg néhány gyakorlati példát a funkció használatára SUMMESLIMN. Így sokkal könnyebb lesz elmélyedni a témában.

Feltétel összegző dinamika tartomány

Kezdjük tehát az első példával. Tegyük fel, hogy van egy táblázatunk, amely információkat tartalmaz arról, hogy a tanulók hogyan birkóznak meg a tananyaggal egy adott tantárgyból. Van egy osztályzat, a teljesítményt 10-es skálán értékelik. A feladat azoknak a tanulóknak az osztályzatát keresni a vizsgára, akiknek vezetéknevük A betűvel kezdődik, és minimum pontszámuk 5.

A táblázat így néz ki.

Ahhoz, hogy a fent leírt szempontok alapján kiszámíthassuk az összpontszámot, a következő képletet kell alkalmaznunk.

Írjuk le részletesebben az érveket:

1. C3:C14 az összesítési tartományunk. Esetünkben egybeesik a feltételtartománnyal. Ebből az összeg kiszámításához használt pontok kerülnek kiválasztásra, de csak azokat, amelyek megfelelnek a kritériumainknak.
2. A „>5” az első feltételünk.
3. A B3:B14 a második összegzési tartomány, amely a második feltételnek való megfelelés érdekében kerül feldolgozásra. Látjuk, hogy nincs egybeesés az összegzési tartománnyal. Ebből arra következtetünk, hogy az összegzés tartománya és a feltétel tartománya lehet azonos, de lehet, hogy nem. 
4. Az „A*” a második tartomány, amely csak azoknak a tanulóknak adja meg a jegyek kiválasztását, akiknek vezetékneve A-val kezdődik. Esetünkben a csillag tetszőleges számú karaktert jelent. 

A számítások után a következő táblázatot kapjuk.

Mint látható, a képlet a dinamikatartomány és a felhasználó által megadott feltételek alapján összegezte az értékeket.

Szelektív összegzés feltétel szerint Excelben

Most tegyük fel, hogy információt szeretnénk kapni arról, hogy mely országokba milyen árukat szállítottak ki az elmúlt negyedévben. Ezt követően keresse meg a júliusi és augusztusi szállításokból származó teljes bevételt.

Maga a táblázat így néz ki. 

A végeredmény meghatározásához szükségünk van egy ilyen képletre.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Az ezzel a képlettel végzett számítások eredményeként a következő eredményt kapjuk.

Figyelem! Ez a képlet elég nagynak tűnik, annak ellenére, hogy csak két kritériumot használtunk. Ha az adattartomány megegyezik, akkor jelentősen csökkentheti a képlet hosszát az alábbiak szerint.

SUMIFS funkció több feltétel értékének összegzésére

Most adjunk egy másik példát szemléltetésül. Ebben az esetben a táblázat ugyanaz marad, mint az előző esetben. 

A következő képletet használjuk (de tömbképletként írjuk le, vagyis a CTRL + SHIFT + ENTER billentyűkombináción keresztül írjuk be).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

A funkció után SUMMESLIMN összegzi az értékek tömbjét a képletben megadott kritériumok alapján (vagyis Kína és Grúzia országai), az eredményül kapott tömböt a szokásos függvény összegzi ÖSSZEG, amely tömbképletként van felírva.

Ha a feltételeket egynél több párhoz tömbállandóként adtuk át, akkor a képlet helytelen eredményt ad.

Most pedig nézzük meg az összegeket tartalmazó táblázatot.

Amint látja, sikerült. Neked is biztosan sikerülni fog. Nagy sikerek ezen a téren. Ez egy nagyon egyszerű funkció, amelyet az a személy is megérthet, aki most lépett az Excel tanulásának útjára. És már tudjuk, hogy a függvény SUMMESLIMN lehetővé teszi, hogy bármilyen tevékenységi területen hatékony legyen, a könyveléstől az egyenletes oktatásig. Ez a funkció akkor is segít pénzt keresni, ha bármilyen más területen épít karriert, amelyet fent nem ismertettünk. Ezért is értékes.

A legfontosabb, hogy időt takaríthat meg, ami sajnos korlátozott erőforrás. Úgy tűnik, hogy két funkció alkalmazásához néhány másodperc van, de ha rengeteg ismétlődő műveletet kell végrehajtania, akkor ezek a másodpercek olyan órákat adnak, amelyeket másra is lehetne fordítani. Ezért javasoljuk, hogy gyakorolja ennek a funkciónak a használatát. Ráadásul hihetetlenül egyszerű.