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.
Zistite, ako môžete použiť funkciu SUMIFS v aplikáciách Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 pre Mac, Excel 2016 pre Mac, Excel pre Mac 2011 a Excel Online.
01 z 11Ako funguje funkcia SUMIFS
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é.
V postupnom kroku SUMIF sme porovnali jediné 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: obchodných zástupcov v východ ktorý mal v minulom roku 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.
02 z 11Zadanie ú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 11Syntax 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, …)
Až do 127 rozsah_pro_aplikaci_kritérií / kritériá páry môžu byť špecifikované vo funkcii.
04 z 11Argumenty 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 uvedenými kritériá a ich zodpovedajúce rozsah_pro_aplikaci_kritérií argumenty.
rozsah_pro_aplikaci_kritérií - skupina buniek je funkcia hľadania odpovede na zodpovedajúce kritériá argumentom.
kritériá - táto hodnota sa porovná s údajmi v príslušnej rozsah_pro_aplikaci_kritérií , Aktuálne dáta alebo odkaz bunky na dáta môžu byť zadané pre tento argument.
05 z 11Spustenie 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
- Kliknutím na bunku F12 ju vytvoríte aktívnou bunkou. Tu zadáme funkciu SUMIFS.
- Klikni na vzorca tab.
- Kliknite na Math & Trig v skupine funkčných knižníc.
- Kliknite na SUMIFS v zozname vyvoláte dialógové okno funkcie SUMIFS.
Program Excel Online nemá kartu Vzorce. Ak chcete použiť SUMIFS v aplikácii Excel Online, použite tlačidlo Vložiť funkciu.
- Kliknite na bunku F12 aby bola aktívna bunka. Tu zadáme funkciu SUMIFS.
- Kliknite na tlačidlo Vložiť funkciu Tlačidlo. Otvorí sa dialógové okno Vložiť funkciu.
- Kliknite na Math & Trig v zozname kategórií.
- Kliknutím na položku SUMIFS v zozname spustite funkciu.
Ú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é.
06 z 11Zadanie argumentu Sum_range
Sum_range argument 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 súbore Celkové predaje stĺpec.
Tutorial Kroky
- Klikni na Sum_range line v dialógovom okne.
- Zvýraznite bunky F3 až F9 v pracovnom hárku pridajte tieto odkazy na bunku Sum_range linka.
Zadanie argumentu Criteria_range1
V tomto výučbe sa snažíme v každom dátovom zázname zhodovať dve kritériá:
- Obchodníci z východnej oblasti predaja.
- Obchodníci, ktorí tento rok uskutočnili menej ako 275 predajov.
Criteria_range1 argument označuje rozsah buniek, ktoré má SUMIFS vyhľadávať pri pokuse o zhodu s prvým kritériom - región východného predaja.
Tutorial Kroky
- Klikni na Criteria_range1 line v dialógovom okne.
- Zvýraznite bunky D3 až D9 v pracovnom hárku a zadajte tieto odkazy na bunky ako rozsah, ktorý má funkcia vyhľadávať.
Zadanie argumentu Criteria1
V tomto príručke prvé kritérium, ktoré sa snažíme porovnať, je, ak sa údaje v rozsahu D3: D9 rovnajú východ .
Hoci sú aktuálne údaje, napríklad slovo východ , môže 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 zadajte odkaz bunky do dialógového okna.
Tutorial Kroky
- Klikni na Criteria1 line v dialógovom okne.
- Kliknite na bunku D12 zadajte odkaz na bunku. Táto funkcia vyhľadá rozsah vybratý v predchádzajúcom kroku pre údaje, ktoré zodpovedajú týmto kritériám.
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 odkaz na bunku, napríklad D12, zadá ako kritériá Argument, funkcia SUMIFS bude vyhľadávať zhody s údajmi, ktoré boli zadané do tejto bunky v pracovnom hárku.
Takže po zistení objemu predaja za východ regiónu bude jednoduché nájsť rovnaké údaje pre iný predajný región jednoducho zmenou východ na sever alebo západ v bunke D12. Funkcia automaticky aktualizuje a zobrazí nový výsledok.
09 z 11Zadanie argumentu Criteria_range2
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
- Klikni na Criteria_range2 line v dialógovom okne.
- Zvýraznite bunky E3 až E9 v pracovnom hárku zadajte tieto bunkové referencie ako druhý rozsah, ktorý má funkcia vyhľadávať.
Zadanie argumentu Criteria2
Druhou normou, ktorú hľadáme, je, ak sú údaje v rozsahu E3: E9 menej ako 275 zákaziek.
Rovnako ako v prípade Criteria1 argument, zadáme referenciu bunky na miesto Criteria2 do dialógového okna namiesto samotných údajov.
Tutorial Kroky
- Klikni na Criteria2 line v dialógovom okne.
- Kliknite na bunku E12 zadajte odkaz na bunku. Funkcia vyhľadá rozsah vybratý v predchádzajúcom kroku pre údaje, ktoré zodpovedajú kritériám.
- kliknite OK dokončite funkciu SUMIFS a zatvorte dialógové okno.
Odpoveď nula (0) sa objaví v bunke F12 (bunka, 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ý.
Kritériá vyhľadávania budú pridané v ďalšom kroku príručky.
11 z 11Pridanie 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 kritériá argumenty.
Tutorial Kroky
Pomoc s týmto príkladom nájdete na obrázku vyššie.
- V bunke D12 typ východ a stlačte tlačidlo vstúpiť na klávesnici.
- V bunke E12 typ <275 a stlačte tlačidlo vstúpiť klávesu na klávesnici ("<" je symbol pre menej ako v programe Excel).
Odpoveď $ 119,719.00 by sa mala objaviť v bunke F12.
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.
Suma 49 017 USD a 70 702 USD je 119 719 USD.
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.
