Excel SUMIFS: súčet hodnôt len ​​na základe splnenia viacerých kritérií

Funkcia SUMIFS rozširuje užitočnosť funkcie SUMIF tým, že umožňuje určiť skôr 2 až 127 kritérií než len jeden ako v SUMIF.

Normálne SUMIFS pracuje s riadkami údajov nazývanými záznamy. V zázname sú všetky údaje v každej bunke alebo poli v riadku spojené - napríklad meno, adresa a telefónne číslo spoločnosti.

SUMIFS hľadá špecifické kritériá v dvoch alebo viacerých poliach záznamu a iba v prípade, že nájde zhodu pre každé zadané pole, sú údaje pre tento záznam zhrnuté.

01 z 10

Ako funguje funkcia SUMIFS

V kroku kroku SUMIF sme porovnali jedno kritérium obchodných zástupcov, ktorí predali viac ako 250 objednávok za rok.

V tejto príručke nastavíme dve podmienky pomocou SUMIFS - predajných agentov v regióne predaja na východ, ktorí v minulom roku mali menej ako 275 predajov.

Nastavenie viac ako dvoch podmienok je možné vykonať zadaním ďalších kritérií Criteria_range a Criteria for SUMIFS.

Nasledujúcimi krokmi v témach tutoriálu nižšie prechádzate vytvorením a použitím funkcie SUMIFS, ktorá je zobrazená na obrázku vyššie.

Tutorial Témy

• Zadanie údajov výučby
• Syntax funkcie SUMIFS
• Spustenie funkcie SUMIFS
• Argument Sum_range
• Argument Criteria_range1
• Argument kritéria1
• Argument Criteria_range2
• Argument kritéria2

02 z 10

Zadanie údajov výučby

Prvým krokom k používaniu funkcie SUMIFS v programe Excel je zadanie údajov.

Zadajte údaje do buniek D1 až F11 pracovného hárka programu Excel, ako je vidieť na obrázku vyššie.

Funkcia SUMIFS a kritériá vyhľadávania (menej ako 275 objednávok a obchodných zástupcov z východnej oblasti predaja) sa pridajú do riadku 12 pod údajmi.

Pokyny pre tutoriály neobsahujú kroky formátovania pre pracovný hárok.

To neovplyvní dokončenie tutoriálu. Váš pracovný hárok bude vyzerať odlišne od zobrazeného príkladu, ale funkcia SUMIFS vám poskytne rovnaké výsledky.

03 z 10

Syntax funkcie SUMIFS

V programe Excel sa syntax funkcie vzťahuje na rozloženie funkcie a obsahuje názov, zátvorky a argumenty funkcie .

Syntax pre funkciu SUMIFS je:

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

Upozornenie: V funkcii je možné špecifikovať až 127 dvojíc kriterií / kritérií .

Argumenty funkcie SUMIFS

Argumenty funkcie informujú o funkcii, o aké podmienky sa testujú a o tom, aký rozsah údajov súčet, keď sú tieto podmienky splnené.

Všetky argumenty v tejto funkcii sú povinné.

Sum_range - údaje v tomto rozsahu buniek sa zhrnú, keď sa nájde zhoda medzi všetkými špecifikovanými kritériami a ich zodpovedajúcimi parametrami Criteria_range .

Criteria_range - skupina buniek funkciou je hľadať zhodu s príslušným argumentom Criteria .

Kritériá - táto hodnota sa porovnáva s údajmi v príslušnom kriteriálnom poradí. Aktuálne dáta alebo odkaz bunky na dáta môžu byť zadané pre tento argument.

04 z 10

Spustenie funkcie SUMIFS

Hoci je možné jednoducho zadať funkciu SUMIFS do bunky v pracovnom hárku , veľa ľudí nájde jednoduchšie používať dialógové okno funkcie pre vstup do funkcie.

Tutorial Kroky

1. Kliknutím na bunku F12 ju vytvoríte aktívnou bunkou . Tu zadáme funkciu SUMIFS.
2. Kliknite na kartu Vzorce .
3. Kliknutím na ikonu Math & Trig na páse krabice otvoríte rozbaľovací zoznam funkcií.
4. Kliknutím na SUMIFS v zozname vyvoláte dialógové okno funkcie SUMIFS.

Údaje , ktoré zadáme do prázdnych riadkov v dialógovom okne, budú tvoriť argumenty funkcie SUMIFS.

Tieto argumenty vysvetľujú funkcii, aké podmienky testujeme a aký rozsah údajov sumujeme, keď sú tieto podmienky splnené.

05 z 10

Zadanie argumentu Sum_range

Argument Sum_range obsahuje bunkové odkazy na údaje, ktoré chceme doplniť.

Keď funkcia nájde zhodu medzi všetkými zadanými parametrami Kritériu a kritériá krivosti záznamu, pole Sum_range pre daný záznam je zahrnuté do súčtu.

V tomto výučbe sú údaje pre argument Sum_range umiestnené v stĺpci Celkové predajné údaje .

Tutorial Kroky

1. Kliknite na riadok Sum_range v dialógovom okne .
2. Zvýraznite bunky F3 až F9 v pracovnom hárku a pridajte tieto bunkové odkazy na riadok Sum_range .

06 z 10

Zadanie argumentu Criteria_range1

V tomto výučbe sa snažíme v každom dátovom zázname zhodovať dve kritériá:

1. Obchodníci z východnej oblasti predaja.
2. Obchodníci, ktorí tento rok uskutočnili menej ako 275 predajov.

Argument Criteria_range1 označuje rozsah buniek, ktoré má SUMIFS vyhľadávať pri pokuse o zhodu s prvým kritériom - Východisková oblasť predaja.

Tutorial Kroky

1. V dialógovom okne kliknite na riadok Criteria_order1 .
2. Zvýraznite bunky D3 až D9 v pracovnom hárku a zadajte tieto odkazy na bunky ako rozsah, ktorý má funkcia vyhľadávať .

07 z 10

Zadanie argumentu Criteria1

V tomto príručke prvé kritérium, ktoré chceme nájsť, je, ak sa dáta v rozsahu D3: D9 rovnajú východu .

Hoci skutočné dáta - napríklad slovo East - môžu byť zadané do dialógového okna pre tento argument , je zvyčajne najlepšie pridať údaje do bunky v pracovnom hárku a potom zadať odkaz bunky do dialógového okna.

Tutorial Kroky

1. Kliknite na riadok Criteria1 v dialógovom okne .
2. Kliknutím na bunku D12 zadáte odkaz na bunku. Funkcia vyhľadá rozsah vybratý v predchádzajúcom kroku pre údaje, ktoré zodpovedajú týmto kritériám.
3. Hľadaný výraz (východ) sa pridá do bunky D12 v poslednom kroku tutoriálu.

Ako bunkové odkazy zväčšujú všestrannosť SUMIFS

Ak sa ako argument pre kritériá zadá odkaz na bunku, ako napríklad D12, funkcia SUMIFS vyhľadá zhodné údaje, akékoľvek údaje zadané do tejto bunky v pracovnom liste .

Takže po zistení objemu predaja pre východný región bude jednoduché nájsť rovnaké údaje pre iný predajný región jednoducho zmenou východu na sever alebo západ v bunke D12. Funkcia automaticky aktualizuje a zobrazí nový výsledok.

08 z 10

Zadanie argumentu Criteria_range2

Ako sme uviedli predtým, v tomto výučbe sa snažíme v každom dátovom zápise porovnať dve kritériá:

1. Obchodníci z východnej oblasti predaja.
2. Obchodníci, ktorí tento rok uskutočnili menej ako 275 predajov.

Argument Criteria_range2 označuje rozsah buniek, ktoré má SUMIFS vyhľadávať pri pokuse o zaradenie do druhého kritéria - obchodných zástupcov, ktorí tento rok predali menej ako 275 objednávok.

Tutorial Kroky

1. V dialógovom okne kliknite na riadok Criteria_range2 .
2. Vysoké bunky E3 až E9 v pracovnom hárku na zadanie týchto odkazov na bunky ako druhý rozsah, ktorý má funkcia vyhľadávať .

09 z 10

Zadanie argumentu Criteria2

V tomto príručke je druhým kritériom, ktoré sa snažíme porovnať, ak údaje v rozsahu E3: E9 predstavujú menej ako 275 objednávok.

Rovnako ako u argumentu Criteria1 , zadáme referenciu bunky na miesto Criteria2 do dialógového okna namiesto samotných údajov.

Tutorial Kroky

1. Kliknite na riadok Criteria2 v dialógovom okne .
2. Kliknutím na bunku E12 zadáte odkaz na bunku. Funkcia vyhľadá rozsah vybratý v predchádzajúcom kroku pre údaje, ktoré zodpovedajú týmto kritériám.
3. Kliknutím na tlačidlo OK dokončite funkciu SUMIFS a zatvorte dialógové okno.
4. Odpoveď nula (0) sa objaví v bunke F12 - v bunke, do ktorej sme vstúpili do funkcie - pretože sme ešte nezadali údaje do polí Criteria1 a Criteria2 (C12 a D12). Až kým to neurobíme, nie je nič, prečo sa táto funkcia pridá, takže celkový pobyt zostane nulový.
5. Kritériá vyhľadávania budú pridané v ďalšom kroku príručky.

10 z 10

Pridanie kritérií vyhľadávania a dokončenie výučby

Posledným krokom v príručke je pridanie údajov do buniek v pracovnom hárku označenom ako obsahujúci argumenty Kritériá .

Tutorial Kroky

Pomoc s týmto príkladom nájdete na obrázku vyššie.

1. V bunke D12 typ East a stlačte kláves Enter na klávesnici.
2. V bunke E12 typ <275 a stlačte kláves Enter na klávesnici ("<" je symbol pre menej ako v programe Excel).
3. Odpoveď $ 119,719.00 by sa mala objaviť v bunke F12.
4. Iba dve záznamy, ktoré sa nachádzajú v riadkoch 3 a 4, zodpovedajú obom kritériám, a preto sú pre túto sumu zhrnuté iba súhrny predajov pre tieto dve záznamy.
5. Suma 49 017 USD a 70 702 USD je 119 719 USD.
6. Keď kliknete na bunku F12, kompletná funkcia
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) sa objaví vo formulári nad pracovným hárkom .