Použitie Excelu na výpočet priemeru na základe kritérií

Na výpočet priemeru môžete použiť Excel. Predstavte si toto: Musíte vypočítať priemer zo zoznamu čísel pomocou iba čísel v zozname, ktoré zodpovedajú podmienke. Inými slovami, vypočítajte priemer pomocou čísel, ak zodpovedajú danému kritériu. Excel je plný prekvapení a na tento trik si môžete vytiahnuť skvelú funkciu s názvom AVERAGEIF.

Nasledujúci obrázok zobrazuje zoznam zamestnancov spolu s rokmi, kedy boli prijatí, koľko účtov si doteraz otvorili v roku 2016 a (pre niektorých) správu o tom, že robia skvelú prácu.

V hornej časti pracovného hárka je priemerný počet účtov otvorených na zamestnanca v roku 2015. Nižšie je očakávaný počet účtov otvorených v roku 2016. Priemer 44 za rok 2015 je len statické číslo. Rok 2015 je za nami a toto je konečný priemer. Je to tak – minulý rok! A čo rok 2016? Tu to začína byť zaujímavé.

Na výpočet predpokladaného počtu účtov otvorených v roku 2016 sa používa výpočet na odvodenie čísla ako priemer na základe počtu doteraz otvorených účtov za daný rok. Toto je technika, ktorú môže manažment použiť na zvýšenie konkurencie medzi zamestnancami. Inými slovami, namiesto toho, aby sme len stanovili očakávaný počet, ako je 48 (čo by bol 10-percentný nárast oproti roku 2015), očakávaný počet účtov na otvorenie je založený na doterajších výkonoch zamestnancov v danom roku.

V bunke C4 sa používa funkcia AVERAGEIF. Takto sa zadáva:

=AVERAGEIF(C8:C16;">" & C3)

Ide o to, že sa zo zoznamu účtov na zamestnanca vezme priemer otvorených účtov, pričom sa použijú iba čísla, ktoré sú väčšie ako 44 otvorených v roku 2015. Toto je určené druhým argumentom vo funkcii („>“ & C3), ktorý uvádza určiť priemer iba pomocou hodnôt, ktoré sú väčšie ako 44, ktoré sa nachádzajú v bunke C3.

Napríklad Julie si doteraz otvorila len 40 účtov, takže jej počet sa v priemere nevyužíva. Na druhej strane, Yolanda má otvorených 48 účtov, takže jej počet sa používa pri výpočte priemeru.

Keď spriemerujete iba čísla väčšie ako 44 v roku 2015, vráti sa vyššie číslo pre očakávané otvorenie na rok 2016. Vypočítaná hodnota pre rok 2016 je od tohto bodu 54. Vedľa mena každého zamestnanca sa zobrazí správa Skvelá práca! sa objaví, ak daný zamestnanec prekonáva priemer. Správu dostanú len tí, ktorí si otvorili aspoň 54 účtov.

ako? Pomocou funkcie IF! V každej bunke, kde sa správa zobrazuje, funkcia IF porovnáva, či počet účtov, ktoré si zamestnanec doteraz otvoril, je rovnaký alebo väčší ako priemer uvedený v bunke C4. Tu je vzorec v bunke D4, ktorý ukazuje, že Harrison odvádza skvelú prácu:

=IF(C8>=$C$4, "Skvelá práca!", ")

Funkcia IF testuje stav. Ak je podmienka pravdivá, zobrazí sa prvá odpoveď. Ak podmienka nie je pravdivá, zobrazí sa druhá odpoveď.

Zatiaľ je všetko dobré! V tejto spoločnosti sú agresívni predajcovia odmeňovaní a zaostávajúci nedostávajú žiadnu chválu.

Je čas vyrovnať podmienky. Jedna vec, ktorá sa v tomto príklade neberie do úvahy, je rok prenájmu. Niektorí zamestnanci boli prijatí v roku 2016, takže spravodlivo by sa počet doteraz otvorených účtov v roku 2016 nemal porovnávať s vysokým priemerom.

Nasledujúci obrázok zobrazuje aktualizáciu pracovného hárka. Jediná funkcia IF je nahradená množinou vnorených funkcií IF: Vonkajší IF berie do úvahy, či je rok prenájmu 2016 (skutočným testom stavu je zistiť, či je rok prenájmu väčší ako 2015); a ak áno, vnútorná funkcia IF testuje, či počet otvorených účtov zamestnanca za doterajší rok je rovnaký alebo väčší ako polovica celkového priemeru.

Teraz sú pre správu možné tri možné výsledky:

Dobrá práca!
Pokračujte v praskaní! Rok ešte nekončí!
Žiadna správa

=AK((A8>$A$3), IF(C8>=($C$4/2), "Skvelá práca!","Pokračujte! Rok sa ešte neskončil!"),AK(C8>=$ C$4 "Skvelá práca!","))