V tomto článku sa naučíme, ako v programe Excel získať priemer s viacerými kritériami.
Problém?
Napríklad máme veľký zoznam údajov a musíme nájsť priemer ceny alebo sumy podľa niektorých kritérií. Kritériá je možné použiť v ktoromkoľvek stĺpci údajov.
Ako vyriešiť problém.
Teraz z funkcie vytvoríme vzorec. Tu sú uvedené údaje a potrebovali sme nájsť PRIEMERNÝ číselný údaj s určitými kritériami
Všeobecný vzorec:
= AVERAGEIFS (Priemerný_rozsah, rozsah1, kritérium1, [rozsah2, kritérium2], [rozsah3, kritérium3], …)
- Priemerný_rozsah: rozsah hodnôt, kde je potrebné vyhodnotiť priemer
- rozsah1: prvý rozsah, v ktorom sa uplatňujú kritériá1.
- kritériá1: prvé kritériá aplikované na rozsah1.
- rozsah2: druhý rozsah, kde sa uplatňujú kritériá1.
- kritériá2: druhé kritérium aplikované na rozsah2.
- rozsah3: tretí rozsah, kde sa uplatňujú kritériá1.
- kritériá3: tretie kritérium uplatňované na rozsah3.
Poznámky: neposkytovať dátum priamo do funkcie. Na zadanie dátumu v Exceli použite funkciu DATE alebo odkaz na bunku, pretože Excel číta dátum iba so správnym poradím.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Tu sme uviedli údaje z A1: D51. Musíme nájsť priemer prijatého množstva podľa rôznych kritérií.
Podmienky sú nasledujúce.
- Dátum musí byť do 31.1.2019.
- Mesto "Boston"
- Produkt je definovaný ako „mrkva“
- Množstvo musí byť väčšie ako 50 (> 50).
Vyššie uvedené 4 podmienky alebo kritériá je potrebné splniť. Funkcia AVERAGEIFS nám môže pomôcť extrahovať priemer s rôznymi kritériami.
Použite vzorec:
= AVERAGEIFS (množstvo, dátum_objednávky, "<=" & F4, mesto, "Boston", produkt, F5, množstvo, F6)
Množstvo: pomenovaný rozsah použitý pre pole D2: D50
Order_date: pomenovaný rozsah použitý pre pole A2: A50
Mesto: pomenovaný rozsah používaný pre pole B2: B50
Výrobok: pomenovaný rozsah používaný pre pole C2: C50
F4: odkaz na bunku použitý pre kritérium 1
F5: odkaz na bunku použitý pre kritérium 2
F6: odkaz na bunku použitý pre kritérium 3
Vysvetlenie:
- dátum objednávky, "<=" & F4prvé kritériá, ktoré navrhujú dátum 31. januára 2019 vo F4.
- mesto, "Boston" druhé kritérium, kde sa mesto zhoduje s „Bostonom“
- výrobok, F5 kde sa výrobok zhoduje s mrkvou v bunke F5
- Množstvo, musí byť pod kritériami v Bunka F6 tj viac ako 50.
Tu je A2 uvedená ako referencia bunky a pomenované rozsahy sú uvedené ako rng (D2: D51) a dátum objednávky (A2: A51).
Ako môžete vidieť na vyššie uvedenom obrázku, priemerná hodnota v januári sa zdá byť 54.00. Pretože existuje iba jedna hodnota, ktorá spĺňa všetky podmienky.
Príklad 2:
Tu sme uviedli údaje z A1: D51. Musíme nájsť priemer prijatého množstva podľa rôznych kritérií.
Podmienky sú nasledujúce.
- Dátum musí byť do 27.2.2019.
- Mesto "New York"
- Výrobok je definovaný ako „čokoládový čip“.
- Množstvo musí byť menšie ako 50 (<50).
Vyššie uvedené 4 podmienky alebo kritériá je potrebné splniť. Funkcia AVERAGEIFS nám môže pomôcť extrahovať priemer s rôznymi kritériami.
Použite vzorec:
= AVERAGEIFS (množstvo, dátum_objednávky, "<=" & F4, mesto, "New York", produkt, F5, množstvo, F6)
Množstvo: pomenovaný rozsah použitý pre pole D2: D50
Order_date: pomenovaný rozsah použitý pre pole A2: A50
Mesto: pomenovaný rozsah používaný pre pole B2: B50
Výrobok: pomenovaný rozsah používaný pre pole C2: C50
F4: odkaz na bunku použitý pre kritérium 1
F5: odkaz na bunku použitý pre kritérium 2
F6: odkaz na bunku použitý pre kritérium 3
Vysvetlenie:
- dátum objednávky, "<=" & F4prvé kritériá, ktoré navrhujú dátum pred 27.2.2019 v bunke F4.
- mesto, "Boston" druhé kritérium, kde sa mesto zhoduje s „Bostonom“
- výrobok, F5 kde sa výrobok zhoduje s mrkvou v bunke F5
- Množstvo, musí byť pod kritériami v Bunka F6 tj menej ako 50.
Tu je A2 uvedená ako referencia bunky a pomenované rozsahy sú uvedené ako rng (D2: D51) a dátum objednávky (A2: A51).
Ako môžete vidieť na vyššie uvedenom obrázku, priemerná hodnota v januári sa zdá byť 33.33… . Pretože existuje iba jedna hodnota, ktorá spĺňa všetky podmienky.
Tu je niekoľko postrehov k použitiu vzorca nižšie.
Poznámky:
- Funkcia vráti #HODNOTU! chyba, ak argument funkcie nie je číselný.
- Funkcia vráti #HODNOTU! chyba, ak zadaným kritériám nevyhovuje žiadna hodnota.
- Vzorec akceptuje operátory ako =, <, =, <= &.
- Nie zadajte argument dátumu priamo do funkcie. Na zadanie dátumu v Exceli použite funkciu DATE alebo odkaz na bunku, pretože Excel číta dátum iba so správnym poradím.
Dúfam, že tento článok o tom, ako priemerovať s viacerými kritériami v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o funkciách SUMPRODUCT. Podeľte sa o svoj dotaz nižšie v poli pre komentáre. Pomôžeme vám.
Ako používať funkciu AVERAGEIFS v programe Excel
Ako používať funkciu DAVERAGE v programe Excel
Ako zvýrazniť bunky nad a pod priemernou hodnotou
Ignorujte nulu v priemere čísel
Vypočítajte vážený priemer
Priemerný rozdiel medzi zoznamami
Overovanie textových záznamov
Vytvorte rozbaľovací zoznam v Exceli s farbami
Populárne články
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu
Pripojte meno a priezvisko v Exceli
Spočítajte bunky, ktoré sa zhodujú s A alebo B
V programe Excel 2016 preveďte palce na nohy a palce
50 Skratka pre Excel, ktorá zvýši vašu produktivitu