Ako používať COUNTIFS, SUMIFS, AVERAGEIFS v programe Excel

Tri najčastejšie používané vzorce v programe Excel, ktoré vykonávajú jednoduché matematické výpočty, sú COUNT, SUM a PRIEMERNÁ. Či už spravujete finančný rozpočet v programe Excel alebo jednoducho sledujete svoju ďalšiu dovolenku, pravdepodobne ste predtým použili jednu z týchto funkcií.

V tomto článku budeme prechádzať základy týchto troch funkcií a ich relevantných a užitočných náprotivkov: COUNTIFS, SUMIFS a AVERAGEIFS.

Povedzme, že začíname nový on-line obchod predávajúci mobilné telefóny a máme list, ktorý uvádza zoznam predajov, ktoré sme vykonali v prvých dvoch mesiacoch. Stiahnite si príklad tabuľky programu Excel.

Excel COUNT, SUM a AVERAGE

Ak vieme, koľko mobilných telefónov sme predali, môžeme ich rýchlo použiť COUNT ako je znázornené nižšie:

= COUNT (E2: E16)

Na druhej strane, aby sme získali celkový objem predaja, ktorý sme urobili, môžeme použiť SUM ako je znázornené nižšie:

= SUM (E2: E16)

Nakoniec, aby sme zistili priemerný predaj, ktorý sme urobili pre všetky telefóny, môžeme použiť PRIEMERNÁ ako je uvedené nižšie:

= Stredná (E2: E16)

Výsledok by mal byť uvedený nižšie:

Vzorce COUNT, SUM a AVERAGE budú fungovať len pre záznamy, ktorých hodnota bunky je vo formáte čísel. Každý záznam v rámci vzorca (t. E2: E16 v tomto príklade), ktoré nie sú vo formáte čísla, budú ignorované.

Takže uistite sa, že všetky bunky v rámci vzorca COUNT, SUM a AVERAGE sú všetky formátované ako číslo, nie text. Pokúste sa použiť rovnaký vzorec, ale s E: E ako rozsah namiesto E2: E16. Vráti rovnaký výsledok ako predtým, pretože ignoruje záhlavie (t. Zľavnená cena), ktorý je v textovom formáte.

Teraz, ak chceme vedieť počet predajov, celkový objem predaja a priemerný objem predaja na telefón, len pre tie, ktoré sa predávajú v USA? Práve tu hrá dôležitú úlohu COUNTIFS, SUMIFS a AVERAGEIFS. Dodržujte nasledujúci vzorec:

COUNTIFS

Rozdelenie vzorcov:

1. = COUNTIFS ( - "=" označuje začiatok vzorca v bunke a COUNTIFS je prvá časť funkcie programu Excel, ktorú používame.
2. D2: D16 - Vzťahuje sa na rozsah údajov, aby sa skontrolovalo, či spĺňa kritériá, ktoré sa majú zahrnúť do vzorca.
3. "USA" - Hľadané kritériá v zadanom rozsahu údajov (D2: D16)
4. ) - Uzatváracia konzola indikujúca koniec vzorca.

Vzorec vráti 6, čo je počet predajov výrobkov odoslaných zo skladu v USA.

SUMIFS

Rozdelenie vzorcov:

1. = SUMIFS ( - "=" znova označuje začiatok vzorca.
2. E2: E16 - Vzťahuje sa na rozsah údajov, ktoré by sme chceli celkom, t. J. Predajná cena v našom príklade.
3. D2: D16 - Vzťahuje sa na rozsah údajov a kontroluje, či spĺňa kritériá, ktoré sa majú zahrnúť do celkovej sumy.
4. "USA" - Hľadané kritériá v zadanom rozsahu údajov (D2: D16)
5. ) - Uzatváracia konzola indikujúca koniec vzorca.

Vzorec ukazuje $ 6.050 celkový predaj, ktorý sa uskutočnil za výrobky odoslané zo skladu v USA.

AVERAGEIFS

Rozdelenie vzorcov:

1. = AVERAGEIFS ( - "=" uveďte začiatok vzorca.
2. E2: E16 - Vzťahuje sa na rozsah údajov, ktoré chceme priemernú hodnotu. V tomto príklade chceme získať priemernú sumu predaja pre všetky telefóny predávané v USA.
3. D2: D16 - Vzťahuje sa na rozsah údajov a kontroluje, či spĺňa kritériá, ktoré sa majú zahrnúť do priemerného vzorca.
4. "USA" - Hľadané kritériá v zadanom rozsahu údajov
5. ) - Uzatváracia konzola označujúca konce vzorca.

Vzorec ukazuje, že sme tento výrobok predávali $ 1.008 za telefón v USA.

Všetky tri vzorce môžu mať viac ako jedno kritérium. Napríklad, ak chceme poznať tie isté čísla (t. COUNT, SUM a PRIEMERNÁ) pre výrobky predávané v krajine USA, ale konkrétne len pre Samsung značky, stačí pridať rozsah údajov, ktorý sa má skontrolovať, a následne jej kritériá.

Pozrite si príklad nižšie, kde sa k prvým kontrolám kritérií pridáva druhé kritérium. (Modrý text označuje prvé kritérium a červená označuje druhé kritérium)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFY (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Všimnite si, že Excel má tiež COUNTIF, SUMIF a AVERAGEIF vzorce bez prípony "S". Používajú sa podobne COUNTIFS, SUMIFS a AVERAGEIFS. Avšak osoby bez prípony "S" vo vzorci majú obmedzenie, že povoľuje iba jedno kritérium pre vzorec.

Keďže syntax je mierne odlišný, odporúčam použiť COUNTIFS, SUMIFS a AVERAGEIFS iba ak môže byť použitá pre jedno alebo viac kritérií, ak je to potrebné. Užite si to!