Použití Excelu k výpočtu průměru na základě kritérií

K výpočtu průměru můžete použít Excel. Představte si toto: Musíte vypočítat průměr ze seznamu čísel pomocí pouze čísel v seznamu, která odpovídají podmínce. Jinými slovy, vypočítejte průměr pomocí čísel, pokud odpovídají danému kritériu. Excel je plný překvapení a pro tento trik můžete vytáhnout skvělou funkci s názvem AVERAGEIF.

Následující obrázek ukazuje seznam zaměstnanců spolu s roky, kdy byli přijati, kolik účtů si zatím v roce 2016 otevřeli a (pro některé) zprávu o tom, že odvádějí skvělou práci.

V horní části listu je průměrný počet účtů otevřených na jednoho zaměstnance v roce 2015. Níže je očekávaný počet účtů otevřených v roce 2016. Průměr 44 za rok 2015 je pouze statické číslo. Rok 2015 je za námi a toto je konečný průměr. Je tomu tak – minulý rok! A co rok 2016? Tady to začíná být zajímavé.

Pro výpočet očekávaného počtu účtů otevřených v roce 2016 se používá výpočet, který odvodí počet jako průměr na základě počtu dosud otevřených účtů za daný rok. Toto je technika, kterou může management použít ke zvýšení konkurence mezi zaměstnanci. Jinými slovy, namísto pouhého stanovení očekávaného počtu, jako je 48 (což by byl 10procentní růst oproti roku 2015), je očekávaný počet otevřených účtů založen na tom, jak si zaměstnanci zatím vedli v daném roce.

V buňce C4 je použita funkce AVERAGEIF. Zadává se takto:

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

Jde o to, že se vezme průměr otevřených účtů ze seznamu účtů na zaměstnance a použije se pouze čísla, která jsou větší než 44 otevřených v roce 2015. To je určeno druhým argumentem ve funkci („>“ & C3), který uvádí určit průměr pouze pomocí hodnot větších než 44 nalezených v buňce C3.

Například Julie si zatím otevřela pouze 40 účtů, takže její počet se v průměru nepoužívá. Na druhou stranu si Yolanda otevřela 48 účtů, takže její počet se používá při výpočtu průměru.

Když zprůměrujete pouze čísla větší než 44 v roce 2015, vrátí se vyšší číslo pro očekávaná otevření pro rok 2016. Vypočítaná hodnota pro rok 2016 k tomuto bodu je 54. Vedle jména každého zaměstnance se zobrazí zpráva Skvělá práce! se objeví, pokud daný zaměstnanec překonává průměr. Zprávu obdrží pouze ti, kteří si otevřeli alespoň 54 účtů.

Jak? Pomocí funkce IF! V každé buňce, kde se zpráva objeví, funkce IF porovnává, zda počet účtů, které si zaměstnanec dosud otevřel, je roven nebo větší než průměr uvedený v buňce C4. Zde je vzorec v buňce D4, který ukazuje, že Harrison odvádí skvělou práci:

=IF(C8>=$C$4, "Skvělá práce!", ")

Funkce IF testuje podmínku. Pokud je podmínka pravdivá, zobrazí se první odpověď. Pokud podmínka není pravdivá, zobrazí se druhá odpověď.

Zatím je vše dobré! V této společnosti jsou agresivní prodejci odměňováni a opozdilci nedostávají žádnou chválu.

Je čas srovnat podmínky. Jedna věc, která se v tomto příkladu nebere v úvahu, je rok pronájmu. Někteří zaměstnanci byli přijati v roce 2016, takže spravedlivě by se počet dosud otevřených účtů v roce 2016 neměl srovnávat s vysokým průměrem.

Následující obrázek ukazuje aktualizaci listu. Jediná funkce IF je nahrazena sadou vnořených funkcí IF: Vnější IF bere v úvahu, zda je rok pronájmu 2016 (skutečným testem stavu je zjistit, zda je rok pronájmu větší než 2015); a pokud ano, vnitřní funkce IF otestuje, zda počet zaměstnanců otevřených účtů za dosavadní rok je roven nebo větší než polovina celkového průměru.

Nyní jsou pro zprávu možné tři možné výsledky:

Dobrá práce!
Pokračujte v praskání! Rok ještě nekončí!
Žádná zpráva

=KDYŽ((A8>$A$3), KDYŽ(C8>=($C$4/2), "Skvělá práce!","Pokračujte! Rok ještě neskončil!"),KDYŽ(C8>=$ C$4 "Skvělá práce!","))