Ez az oktatóanyag elmagyarázza a SUMIF és a SUMIFS függvények közötti különbséget a szintaxisuk és használatát, és számos képletpéldát ad az összes AND / OR kritériummal rendelkező értékek összegzéséhez az Excel 2019, 2016, 2013, 2010, 2007, 2003 és alacsonyabb verziókban.
Mint mindenki tudja, a Microsoft Excel egy tömböt kínál függvények különböző számítások elvégzéséhez adatokkal. Néhány cikkel ezelőtt feltártuk a COUNTIF és a COUNTIFS elemeket, amelyeket cellák számlálására terveztek egyetlen feltétel, illetve több feltétel alapján. A múlt héten kitértünk az Excel SUMIF-re, amely a megadott kritériumoknak megfelelő értékeket ad hozzá. Itt az ideje átnézni a SUMIF – Excel SUMIFS többes számú változatát, amely lehetővé teszi az értékek több kritériummal történő összegzését.
Akik ismerik a SUMIF függvényt, azt gondolhatják, hogy a SUMIFS formátumba konvertálása csak egy extra feladatot jelent. “S” és néhány további kritérium. Ez elég logikusnak tűnik … de “logikus” ez nem mindig így van a Microsoft kezelésében:)
- SUMIF függvény – szintaxis & használat
- SUMIFS függvény – szintaxis & használat
- A SUMIFS használata Excelben (ÉS logika)
- Excel SUMIF több kritériummal (VAGY logika)
- Excel SUMIFS több VAGY feltétellel
- Excel SUM használata tömbképletekben
Excel SUMIF függvény – szintaxis & használat
A SUMIF függvény arra szolgál, hogy feltételeket összegezzen egyetlen feltétel alapján. Az előző cikkben részletesen megvitattuk a SUMIF szintaxist, ezért hadd adjak most egy rövid összefoglalót.
-
range– a kritériumok alapján értékelendő cellák tartománya szükséges. -
criteria– a teljesítendő feltétel, kötelező. -
sum_range– az összesítendő cellák, ha a feltétel teljesül, nem kötelező.
Amint látja, a Az Excel SUMIF függvény szintaxisa csak egy feltételt engedélyez. És még mindig azt mondjuk, hogy az Excel SUMIF használható több kritériummal rendelkező értékek összegzésére. Hogy lehetséges? Több SUMIF függvény eredményének hozzáadásával és tömbfeltételekkel rendelkező SUMIF képletek használatával, amint azt a következő példák mutatják.
Excel SUMIFS függvény – szintaxis & használat
A SUMIFS használatával az Excelben több feltétel alapján feltételes értékösszeget találhat. A SUMIFS függvényt az Excel 2007-ben vezették be, így az Excel 2019, 2016, 2013, 2010 és 2007 összes modern verziójában használható.
A SUMIF-hez képest a SUMIFS-szintaxis egy kicsit összetettebb :
Az első 3 argumentum kötelező, további tartományok és a hozzájuk tartozó feltételek opcionálisak.
-
sum_range– az összegzéshez egy vagy több cella szükséges. Ez lehet egyetlen cella, cellatartomány vagy megnevezett tartomány. Csak a számokkal rendelkező cellákat összesítik; üres és szöveges értékeket figyelmen kívül hagyunk. -
criteria_range1– az első tartomány, amelyet a társított kritériumok alapján értékelni kell, szükséges. -
criteria1– az első feltétel, amelynek teljesülnie kell, kötelező. Megadhatja a feltételeket szám, logikai kifejezés, cellahivatkozás, szöveg vagy más Excel függvény formájában. Használhat például olyan kritériumokat, mint 10, “> = 10”, A1, “cseresznye” vagy TODAY (). -
criteria_range2, criteria2, …– ezek további tartományok és feltételek társulnak hozzájuk, nem kötelező. A SUMIFS képletekben legfeljebb 127 tartomány / feltétel pár használható.
És most hagyjuk ” s nézzük meg az Excel SUMIFS képletet két feltétellel. Tegyük fel, hogy van egy táblázata, amely felsorolja a különböző beszállítók gyümölcsszállítmányait. Az A oszlopban a gyümölcsnevek, a B oszlopban a szállítónevek, a C oszlopban pedig a mennyiség szerepel. Azt szeretné, hogy megtudja az adott gyümölcsre és szállítóra vonatkozó összegek összegét, pl az összes almát Pete szállította.
Amikor valami újat tanulsz, mindig egyszerű dolog egyszerű dolgokkal kezdeni. Tehát először is adjuk meg a SUMIFS képletünk összes argumentumát:
- sum_range – C2: C9
- feltételek_tartomány1 – A2: A9
- szempont1 – “alma”
- kritérium_tartomány2 – B2: B9
- kritérium2 – “Pete”
Most állítsa össze a fenti paramétereket, és Ön a következő SUMIFS képletet kapja:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Finomítás a képletet tovább cserélheti az “alma” és “Pete” szövegkritériumokat cellahivatkozásokkal.Ebben az esetben nem kell megváltoztatnia a képletet egy másik szállító egyéb gyümölcsének mennyiségének kiszámításához:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
A SUMIFS és a SUMIF használata az Excel programban – emlékezetes dolgok
Mivel ennek az oktatóanyagnak az a célja, hogy az értékek összesítésének minden lehetséges módját lefedje több feltétellel, mindkét képlettel – Excel SUMIFS és SUMIF – megvitatjuk a képleteket több kritériummal. A helyes használatukhoz világosan meg kell értened, mi a közös e két funkcióban, és milyen módon különböznek egymástól.
Bár a közös rész világos – hasonló cél és paraméterek – a különbségek nem annyira nyilvánvalóak, bár nagyon lényegesek.
1. Az argumentumok sorrendje
Az Excel SUMIF és SUMIFS függvényekben az argumentumok sorrendje eltérő. Különösen a sum_range az első paraméter a SUMIFS-ban, de a 3. a SUMIF-ben.
Első látásra úgy tűnhet, hogy a Microsoft szándékosan bonyolítja felhasználói számára a tanulási görbét. Ha jobban megnézed, meglátod a mögöttes érvelést. A lényeg az, hogy a sum_range opcionális a SUMIF-ben. Ha ezt elmulasztja, semmi gond, a SUMIF képlete összeadja a tartomány értékeit (első paraméter).
A SUMIFS-ban az összeg_tartomány nagyon fontos és kötelező, és ezért ez az első. Valószínűleg a Microsoft srácai úgy gondolták, hogy a 10. vagy a 100. tartomány / kritérium pár hozzáadása után valaki elfelejtheti megadni az összegzett tartományt:)
2. Az összegtartománynak és a kritériumtartománynak egyformán kell lennie
A SUMIF függvényben az összeg_tartomány argumentumnak nem feltétlenül kell ugyanolyan méretűnek lennie, mint a tartomány argumentumnak, mindaddig, amíg a bal felső sarokban jobbra van. Az Excel SUMIFS programban minden feltétel_tartománynak ugyanannyi sort és oszlopot kell tartalmaznia, mint az összeg_tartomány paraméterét.
Például a =SUMIF(A2:A9,F1,C2:C18) a helyes eredményt adja vissza, mert az Excel úgy ítéli meg csak az összeg_tartomány argumentum bal felső sarkában található cellát (ebben a példában C2, ami helyes), majd annyi oszlopot és sort tartalmaz, amennyit a tartomány argumentum tartalmaz.
Rendben, elég stratégia (azaz elmélet) , térjünk át a taktikára (pl. képletpéldák:)
A SUMIFS használata Excelben – képletpéldák
Egy pillanattal ezelőtt egy egyszerű SUMIFS képletet tárgyaltunk két szöveggel feltételek. Ugyanígy használhatja az Excel SUMIFS-t számos feltételekkel, számokkal, dátumokkal, logikai kifejezésekkel és más Excel-funkciókkal kifejezve.
1. példa 1. Excel SUMIFS összehasonlító operátorokkal
Tegyük fel, hogy a gyümölcsszállítók táblázatában összegezni kívánja Mike összes szállítását 200 vagy annál több mennyiséggel. Ehhez használja a “nagyobb, mint o r egyenlő a (z) “(> =) feltételekkel2, és kapja meg a következő SUMIFS képletet:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Az Excel SUMIF függvény megvitatásakor az összes lehetséges összehasonlító operátort részletesen bemutattuk, ugyanazok az operátorok használható a SUMIFS kritériumokban. Például a következő képlet a C2: C9 cellák összes értékének összegét adja vissza, amely 200-nál nagyobb vagy egyenlő, és kisebb vagy egyenlő 300-tal.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
2. példa: Excel SUMIFS használata dátumokkal
Ha az aktuális dátum alapján több kritériumot szeretne összegezni, használja a SUMIFS TODAY () függvényét kritériumok, amint azt az alábbiakban bemutatjuk. A következő képlet összegzi a D oszlop értékeit, ha a C oszlop megfelelő dátuma az elmúlt 7 napra esik, beleértve a mai napot is:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
Hasonló módon az Excel SUMIF függvény segítségével összegezheti az értékeket egy adott dátumtartományban. Például a következő SUMIFS képlet hozzáadja az értékeket a C2: C9 cellákhoz, ha a B oszlop dátuma 2014. október 1. és 2014. október 31. közé esik.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Ugyanez az eredmény érhető el két SUMIF függvény különbségének kiszámításával, amint ezt a példa bemutatja – Hogyan használjuk a SUMIF-ot egy adott dátumtartomány értékeinek összegzéséhez. Az Excel SUMIFS azonban sokkal könnyebb és érthetőbb, nem igaz?
3. példa. Excel SUMIFS üres és nem üres cellákkal
A jelentések és egyéb adatok elemzésekor gyakran szükség lehet az üres vagy a nem üres celláknak megfelelő értékek összegzésére.
És most nézzük meg, hogyan használhatjuk a SUMIFS képletet “üres” és “nem üres” feltételekkel a valós adatok.
Tegyük fel, hogy a B oszlopban van rendelési dátum, a C oszlopban és a Mennyiségben a szállítási dátum. a D. oszlopban. Hogyan találja meg az összes olyan terméket, amelyet még nem szállítottak le? Vagyis tudni szeretné a B oszlop nem üres celláinak és a C oszlop üres celláinak megfelelő értékek összegét.
A megoldás az, hogy a SUMIFS képletet két feltétellel használjuk:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Az Excel SUMIF használata több VAGY kritériummal
Amint az oktatóanyag elején megjegyeztük, a SUMIFS függvény ÉS logikával készült. De mi van akkor, ha több VAGY kritériummal kell összegeznie az értékeket, azaz ha a feltételek legalább egyike teljesül?
1. példa SUMIF + SUMIF
A legegyszerűbb megoldás az összeg az eredményeket számos SUMIF függvény adta vissza. Például a következő képlet bemutatja, hogyan lehet megtalálni a Mike és John által szállított termékek teljes számát:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Amint látja, az első SUMIF függvény összeadja a “Mike” -nek megfelelő mennyiségeket, a másik SUMIF függvény visszaadja a “John” -ra vonatkozó összegeket, majd hozzáadja ezt a 2 számot.
2. példa. SUM & SUMIF tömb argumentummal
A fenti megoldás nagyon egyszerű, és gyorsan elvégezheti a munkát, ha csak pár kritérium. De egy SUMIF + SUMIF képlet óriási mértékben megnőhet, ha több OR feltételes értékeket szeretne összegezni. Ebben az esetben jobb megközelítés egy tömbfeltétel argumentum használata a SUMIF függvényben. Vizsgáljuk meg most ezt a megközelítést.
Kezdheti úgy, hogy az összes feltételt vesszővel elválasztva sorolja fel, majd a kapott vesszővel elválasztott listát csatolja a {curly brackets} közé, amelyet technikailag tömbnek neveznek.
A 3 értékből álló tömb argumentum arra kényszeríti a SUMIF képletet, hogy három külön eredményt adjon vissza, de mivel a képletet egyetlen cellába írjuk, csak az első eredményt adná vissza – azaz a John által leadott összes terméket . Ahhoz, hogy ez a tömbfeltételes megközelítés működőképes legyen, még egy apró trükköt kell használnia – csatolja a SUMIF képletét egy SUM függvénybe, így:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Mint látható, egy tömbfeltétel sokkal kompaktabbá teszi a képletet a SUMIF + SUMIF-hez képest, és annyi értéket adhat hozzá, amennyit csak lehet tetszik a tömbben.
Ez a megközelítés számokkal és szöveges értékekkel is működik. Például, ha a C oszlopban a szállító neve helyett a szállító azonosítói voltak, például 1, 2, 3 stb. ., akkor a SUMIF képlete ehhez hasonlóan nézne ki:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
A szöveges értékekkel ellentétben a számokat nem kell dupla idézőjelek közé tenni. tömb argumentumok.
3. példa SUMPRODUCT & SUMIF
Abban az esetben, ha a kívánt módszer az, hogy néhány cellában feltünteti a feltételeket, adja meg őket közvetlenül a képletben, használhatja a SUMIF-et a SUMPRODUCT függvénnyel együtt, amely megsokszorozza az adott tömbök összetevőit, és visszaadja az adott termékek összegét.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Ahol a G2: G4 az Ön kritériumait tartalmazó cellák, esetünkben a szállítók neve, amint az az alábbi képernyőképen látható.
De természetesen semmi sem akadályozza meg az értékek felsorolása a SUMIF függvény tömbfeltételeiben, ha szeretné:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
A két képlet által adott eredmény megegyezik a képernyőképen láthatóval:
Excel SUMIFS with több VAGY kritérium
Ha feltételesen szeretné összegezni az értékeket az Excel-ben, nem csak több VAGY feltétellel, hanem több feltételkészlettel, akkor SUMIFS-ot kell használnia SUMIF helyett. A képletek nagyon hasonlítanak az imént tárgyaltakra.
A szokásos módon egy példa segíthet jobban szemléltetni a lényeget. A gyümölcsszállítók táblázatában adjuk meg a szállítási dátumot (oszlop) E) és keresse meg Mike, John és Pete által októberben szállított teljes mennyiséget.
1. példa SUMIFS + SUMIFS
Az ilyen megközelítés által létrehozott képlet sok ismétlést és megjelenést tartalmaz nehézkes, de könnyen érthető, és ami a legfontosabb, működik:)
2. példa. SUM & SUMIFS tömb argumentummal
Megpróbáltam elmagyarázni ennek a megközelítésnek a lényegét a SUMIF példában, így most egyszerűen átmásolhatjuk ezt a képletet, megváltoztathatjuk az argumentumok sorrendjét (amint emlékszel, a SUMIF és a SUMIFS más és más), és további feltételeket adhatunk hozzá. az eredményül kapott képlet kompaktabb, mint a SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
A képlettel kapott eredmény pontosan megegyezik a a fenti képernyőkép.
Exampl e 3. SUMPRODUCT & SUMIFS
Amint emlékszel, a SUMPRODUCT megközelítés abban különbözik az előző kettőtől, hogy mindegyik kritériumot külön cellába írja be hanem közvetlenül a képletben határozzák meg őket. Több kritériumkészlet esetén a SUMPRODUCT függvény nem lesz elegendő, és az ISNUMBER és a MATCH alkalmazást is alkalmazni kell.
Tehát feltételezve, hogy a kellékanyagok neve a H1: H3, a kezdési dátum a H4 és a befejezés dátuma a H5 cellában van, a SUMPRODUCT képletünk a következő formát ölti:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Sokan csodálkoznak azon, hogy miért kell kettős kötőjelet (-) használni a SUMPRODUCT képletekben. A lényeg az, hogy az Excel SUMPRODUCT figyelmen kívül hagy minden, a numerikus értéket kivéve, míg a képletünkben szereplő összehasonlító operátorok logikai értékeket (TRUE / FALSE) adnak vissza, amelyek nem numerikusak. Ha ezeket a logikai értékeket 1 “és 0” s értékre kívánja konvertálni, használja a dupla mínusz előjelet, amelyet technikailag kettős unáris operátornak hívnak. Az első unár a TRUE / FALSE értéket -1 / 0-ra kényszeríti. A második unária tagadja az értékeket, vagyis megfordítja a jelet, +1 és 0-ra változtatva, amit a SUMPRODUCT függvény megérthet.
Remélem, hogy a fenti magyarázatnak van értelme. És még akkor is, ha nem így van, ne feledje ezt az ökölszabályt – használja a kettős unáris operátort (-), amikor összehasonlító operátorokat használ a SUMPRODUCT képleteiben.
Excel SUM használata tömbképletekben
Amint emlékszik, a Microsoft az SUMIFS függvényt implementálta az Excel 2007 programban. Ha valaki továbbra is az Excel 2003, 2000 vagy korábbi verziókat használja, akkor SUM tömbképletet kell használnia több ÉS feltételes értékek hozzáadásához. Természetesen ez a megközelítés az Excel 2013 – 2007 modern verzióiban is működik, és a SUMIFS függvény régimódi párjának tekinthető.
A fent tárgyalt SUMIF képletekben már használt tömb argumentumokat, de tömb képletet valami más.
1. példa Összegzés több AND kritériummal az Excel 2003 és korábbi verzióiban
Térjünk vissza a legelső példához, ahol megtudtuk az összegek összegét egy adott gyümölcsnek és a beszállítónak:
Mint már tudják, ez a feladat egyszerűen elvégezhető egy hétköznapi SUMIFS képlet:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Most pedig nézzük meg, hogyan lehet ugyanaz a feladat teljesíteni az Excel korai “SUMIFS-mentes” verzióiban . Először írja le az összes feltételt, amelynek teljesülnie kell tartomány = “feltétel” formájában. Ebben a példában két tartomány / feltétel pár van:
1. feltétel: A2: A9 = “alma”
2. feltétel: B2: B9 = “Pete”
Ezután ír egy SUM képletet, amely “megszorozza” az összes tartomány / feltétel párját, zárójelben zárva. Az utolsó szorzó az összegtartomány, esetünkben C2: C9:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Amint az alábbi képernyőképen látható, a képlet tökéletesen működik a legújabb Excel 2013 verzióban.
2. példa. SUM tömbképletek modern Excel verziókban
Even az Excel modern verzióiban a SUM függvény erejét nem szabad lebecsülni. A SUM tömbképlet nem egyszerűen az elme torna, hanem gyakorlati értéke is van, amint azt a következő példa bemutatja.
Tegyük fel, hogy , két oszlopod van, B és C, és meg kell számolnod, hogy a C oszlop hányszor nagyobb, mint a B oszlop, amikor a C oszlop értéke nagyobb vagy egyenlő 10-vel. Az azonnali megoldás jut eszedbe, ha a SUM-ot használod. tömbképlet:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Ne lásson praktikusat alkalmazás a fenti képletre? Gondoljon bele más módon:)
Tegyük fel, hogy rendelkezik az alábbi képernyőképen látható megrendelések listájával, és szeretné tudni, hány terméket nem szállítottak le teljes egészében egy adott dátumig. Az Excel nyelvére lefordítva a következő feltételekkel rendelkezünk:
1. feltétel: A B oszlop (Rendezett elemek) értéke nagyobb, mint 0
2. feltétel: Az oszlop értéke C (kézbesítve) kevesebb, mint a B oszlopban
3. feltétel: A D oszlopban (esedékesség) egy dátum kevesebb, mint 2014.11.01.
A három tartomány megadása / a feltételpárokat együtt, a következő képletet kapja:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Nos, az ebben az oktatóanyagban tárgyalt képlet-példák csak megkarcolták az Excel SUMIFS és SUMIF függvények felületét, de remélhetőleg segítettek a helyes irányba mutatásban, és mostantól függetlenül összegezheti az értékeket az Excel munkafüzeteiben hány bonyolult állapotot kell figyelembe venned.
- A SUMIF használata az Excelben – képletpéldák a cellák feltételes összegzéséhez
- Az Excel SUMIFS és a SUMIF használata több kritériummal – képletpéldák
- A VLOOKUP & SUM vagy SUMIF függvények használata az Excelben
- SUMIF a Google Táblázatokban képletpéldákkal
- COUNTIF az Excelben – számolja meg, ha nem üres, nagyobb, duplikált vagy egyedi
- Hogyan számoljuk és összegezzük a cellákat szín szerint az Excelben?