Excel SUMIFS: Egyszeri összegű értékek többszörös kritériumok teljesítésével

A SUMIFS funkció kiterjeszti a SUMIF függvény hasznosságát úgy, hogy 2-127 kritériumot adjon meg, nem csak egy, mint a SUMIF.

Normális esetben a SUMIFS az adatrészletekkel ellátott adatrészletekkel működik. A rekordban a sor minden egyes cellájában vagy mezőjében lévő adatok kapcsolódnak - például a vállalat neve, címe és telefonszáma.

A SUMIFS a rekord két vagy több mezőjében keres bizonyos kritériumokat, és csak akkor találja meg, ha a megadott mező minden egyes mezőben megtalálható.

01. oldal, 10

A SUMIFS funkció működése

A SUMIF lépésenkénti bemutatójában megfeleltünk az értékesítési ügynökök egyetlen kritériumának, akik egy év alatt több mint 250 megrendelést értékesítettek.

Ebben a bemutatóban két feltételt fogunk megfogalmazni a SUMIFS segítségével - a keleti értékesítési régió értékesítési ügynökeivel, akiknek az elmúlt évben 275 eladása kevesebb volt.

Több mint két feltétel beállítását a SUMIFS további Criteria_range és Criteria arguments megadásával lehet elvégezni.

Az alábbi bemutató témakörök lépéseit követve a fenti képen látható SUMIFS funkció létrehozása és használata történik.

Oktató témák

• A bemutatóadatok beírása
• A SUMIFS függvény szintaxisa
• A SUMIFS funkció elindítása
• A Sum_range argumentum
• A Criteria_range1 argumentum
• A kritériumok1 érv
• A Criteria_range2 argumentum
• A Criteria2 argumentum

02. oldal, 10

A bemutatóadatok beírása

Az első lépés az SUMIFS függvény Excel használatához az adatok beírása.

Adja meg az adatokat egy Excel munkalap D1-F11 celláiba a fenti képen látható módon.

A SUMIFS függvény és a keresési kritériumok (kevesebb, mint 275 megrendelés és a keleti értékesítési régió értékesítési ügynökei) kerülnek hozzá az adatok alatti 12. sorhoz.

A bemutató utasításai nem tartalmazzák a munkalap formázási lépéseit.

Ez nem akadályozza a tutorial kitöltését. A munkalap a megjelenített példától eltérőnek tűnik, de a SUMIFS funkció ugyanazokat az eredményeket adja.

03. oldal, 10

A SUMIFS függvény szintaxisa

Az Excel-ben a függvény szintaxisa a függvény elrendezésére utal, és tartalmazza a függvény nevét, zárójelzéseket és argumentumokat .

A SUMIFS függvény szintaxisa:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Megjegyzés: A függvényben legfeljebb 127 Criteria_range / Criteria párok adhatók meg.

A SUMIFS függvény érvei

A függvény argumentumai megmutatják a függvényt, hogy milyen feltételekkel próbálják tesztelni, és milyen adatösszeget kell összegezni, ha ezek a feltételek teljesülnek.

Ehhez a funkcióhoz minden argumentum szükséges.

Sum_range - az ebben a cellatartományban szereplő adatok akkor összegezhetők, ha egyezik az összes megadott kritérium és a megfelelő Criteria_range argumentum között.

Criteria_range - a cellák csoportja a függvény a megfelelő kritériumok argumentumának keresése.

Kritériumok - ez az érték összehasonlításra kerül a megfelelő Criteria_range adatokkal. Ehhez az argumentumhoz a tényleges adatokat vagy az adatokhoz tartozó cellahivatkozást be lehet írni.

04. oldal, 10

A SUMIFS funkció elindítása

Bár lehetséges, hogy csak a SUMIFS függvényt írja be a munkalap egyik cellájába, sokan könnyebben használják a függvény párbeszédablakát a funkció beírásához.

Bemutató lépések

1. Kattintson az F12 cellára, hogy az aktív cella legyen . Itt adjuk meg a SUMIFS funkciót.
2. Kattintson a Formulák lapra.
3. Kattintson a szalagon lévő Math & Trig ikonra a funkció legördülő listájának megnyitásához.
4. Kattintson a SUMIFS gombra a SUMIFS függvény párbeszédablakának megjelenítéséhez.

A párbeszédablak üres soraira beírandó adatok a SUMIFS függvény argumentumát alkotják.

Ezek az érvek jelzik a függvényt, hogy milyen feltételekkel teszteljük, és milyen adatösszeget összegezünk, ha ezek a feltételek teljesülnek.

05. oldal, 10

A Sum_range argumentum megadása

A Sum_range argumentum tartalmazza a hozzárendelni kívánt adatokhoz tartozó cella hivatkozásokat .

Amikor a függvény megegyezik a megadott kritériumok és a Criteria_range argumentumok között, akkor a Sum_range mező az adott rekord részét képezi.

Ebben a bemutatóban a Sum_range argumentum értéke a Total Sales oszlopban található.

Bemutató lépések

1. Kattintson a párbeszédablak Sum_range sorára.
2. Jelölje ki a munkalapon az F3-F9 cellákat, hogy ezeket a sejt-hivatkozásokat hozzáadja a Sum_range vonalhoz.

06. oldal, 10

A Criteria_range1 argumentum megadása

Ebben a bemutatóban két kritériumot próbálunk megegyezni minden adatrekordban :

1. A kelet-értékesítési régió értékesítési ügynökei.
2. Az értékesítési ügynökök, akik ebben az évben kevesebb mint 275 eladást értékesítettek.

A Criteria_range1 argumentum azt a cellatartományt jelöli, amelyet a SUMIFS az első kritériumoknak - a keleti értékesítési régiónak - próbál megkeresni.

Bemutató lépések

1. A párbeszédpanelen kattintson a Criteria_range1 sorra.
2. Jelölje ki a munkalapon a D3-D9 cellákat, hogy ezeket a sejt-referenciákat a függvény által keresendő tartományként adja meg.

07. oldal, 10

A Criteria1 argumentum megadása

Ebben a bemutatóban az első kritérium, amelyet meg akarunk egyezni, az, ha a D3: D9 tartományban lévő adatok Keletre esnek .

Habár a tényleges adatok - például a keleti szó - beilleszthetők a párbeszédablakba, ez az argumentum általában a legjobb, ha az adatokat hozzáadja a munkalap egyik cellájához , majd beírja azt a cella hivatkozást a párbeszédpanelbe.

Bemutató lépések

1. Kattintson a Criteria1 sorra a párbeszédpanelen .
2. Kattintson a D12 cellára a cella hivatkozásának beírásához. A funkció az előző lépésben kiválasztott tartományt keresi az e kritériumoknak megfelelő adatokhoz.
3. A keresési kifejezés (East) a D12 cellához kerül a tutorial utolsó lépésébe.

Hogyan használják a Cell Referenciák a SUMIFS sokoldalúságát?

Ha a C12 referenciaértéket a Criteria argumentumként adtuk meg, akkor a SUMIFS függvény megkeresi a munkadarab azon cella adatait.

Így a keleti régió értékesítési összegének megállapítása után könnyen megtalálhatja ugyanazokat az adatokat egy másik értékesítési régióhoz egyszerűen a D12-es cellában történő keleti vagy észak- nyugati váltással. A funkció automatikusan frissíti és megjeleníti az új eredményt.

08. oldal, 10

A Criteria_range2 argumentum megadása

Amint korábban említettük, ebben a bemutatóban mindkét adatrekordban két kritériumot próbálunk megegyezni:

1. A kelet-értékesítési régió értékesítési ügynökei.
2. Az értékesítési ügynökök, akik ebben az évben kevesebb mint 275 eladást értékesítettek.

A Criteria_range2 argumentum azt a cellatartományt jelöli, amelyet a SUMIFS keresni próbál, amikor megpróbálja megfeleltetni a második kritériumot - az értékesítési ügynökök, akik ebben az évben kevesebb, mint 275 megrendelést értékesítettek.

Bemutató lépések

1. A párbeszédpanelen kattintson a Criteria_range2 sorra.
2. Az E3-tól E9-ig terjedő Highligt-cellákat a munkalapon adja meg, hogy ezeket a sejt-referenciákat a függvény által keresett második tartományba írja be.

09. oldal, 10

A Criteria2 argumentum megadása

Ebben a bemutatóban a második kritérium, amellyel megegyezünk, az, ha az E3: E9 tartományban lévő adatok kevesebb mint 275 eladási rendeletet tartalmaznak.

A Criteria1 argumentumhoz hasonlóan a 2. kritérium helyét a párbeszédablakba helyezzük ahelyett, hogy maga az adat.

Bemutató lépések

1. Kattintson a Criteria2 vonalra a párbeszédpanelen .
2. Kattintson az E12 cellára a cella hivatkozásának beírásához. A funkció az előző lépésben kiválasztott tartományt keresi az e kritériumoknak megfelelő adatokhoz.
3. Kattintson az OK gombra a SUMIFS funkció befejezéséhez és zárja be a párbeszédpanelt.
4. A nullára (0) az F12 cellában - a cellában, ahol bevittük a funkciót - megjelenik a válasz, mivel még nem adtuk hozzá az adatokat a Criteria1 és Criteria2 mezőkhöz (C12 és D12). Amíg meg nem történik, nincs semmi, amiért a függvény összeadódna, így a teljes marad nulla.
5. A keresési feltételeket a bemutató következő lépéseként adják hozzá.

10/10

A keresési feltételek hozzáadása és a bemutató kiegészítése

A tutorial utolsó lépése, hogy adatokat adjon a Criteria argumentumokat tartalmazó munkalapon lévő cellákhoz .

Bemutató lépések

A fenti példával kapcsolatban lásd a fenti képet.

1. A D12 cellában írja be a Keletet, és nyomja meg az Enter gombot a billentyűzeten.
2. Az E12 cellában írja be a <275 típust, majd nyomja meg az Enter gombot a billentyűzeten (a "<" szimbólum kisebb, mint Excel).
3. A válasz $ 119,719.00 kell megjelennie az F12 cellában.
4. A 3. és 4. sorokban szereplő két rekord mindkét kritériumnak megfelel, ezért csak a két rekord eladási összegeit összegzi a függvény.
5. A $ 49,017 és a $ 70,702 összeg $ 119,719.
6. Amikor az F12 cellára kattint, a teljes funkció
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) megjelenik a munkalap feletti képletsávban .