Използване на Excel за изчисляване на средна стойност въз основа на критерии

Можете да използвате Excel за изчисляване на средна стойност. Представете си това: Трябва да изчислите средна стойност от списък с числа, като използвате само числа от списъка, които отговарят на условие. С други думи, изчислете средната стойност, като използвате числа, ако отговарят на даден критерий. Excel е пълен с изненади и за този трик можете да извадите страхотна функция на име AVERAGEIF.

Следващата фигура показва списък на служителите, заедно с годините, в които са били наети, колко сметки са отворили досега през 2016 г. и (за някои) съобщение, че вършат страхотна работа.

В горната част на работния лист е средният брой открити сметки на един служител през 2015 г. По-долу е очакваният брой сметки за откриване през 2016 г. Средната стойност от 44 за 2015 г. е просто статична цифра. 2015 г. приключи и това е крайната средна стойност. Така е - миналата година! Ами 2016 г.? Тук става интересно.

За да се изчисли очакваният брой сметки за откриване през 2016 г., се използва изчисление, за да се изведе броят като средна стойност въз основа на броя на сметки, които са били открити досега за годината. Това е техника, която ръководството може да използва за увеличаване на конкуренцията между служителите. С други думи, вместо просто да се фиксира очакван брой, като 48 (което би било 10% ръст спрямо 2015 г.), очакваният брой сметки за откриване се основава на това как служителите са се представяли досега за годината.

В клетка C4 се използва функцията AVERAGEIF. Ето как се въвежда:

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

Това, което прави, е да вземе средната стойност на откритите сметки от списъка със сметки на служител, като използва само числата, които са по-големи от 44-те, открити през 2015 г. Това се определя от втория аргумент във функцията (“>” & C3), което гласи да се определи средната стойност, като се използват само стойности, които са по-големи от 44, открити в клетка C3.

Например Джули досега е открила само 40 сметки, така че нейният брой не се използва в средната стойност. От друга страна, Йоланда е открила 48 сметки, така че нейният брой се използва при изчисляването на средната стойност.

Когато осредните само числата, по-големи от 44 за 2015 г., се връща по-голямо число за очакваните отвори за 2016 г. Изчислената стойност за 2016 г. към този момент е 54. До името на всеки служител се появява съобщението Страхотна работа! се появява, ако този служител надминава средното. Само тези, които са отворили поне 54 акаунта, получават съобщението.

Как? С помощта на функцията IF! Във всяка клетка, където се появява съобщението, функцията IF сравнява дали броят на сметките, които служителят е отворил досега, е равен или по-голям от средния, показан в клетка C4. Ето формулата в клетка D4, която показва, че Харисън върши страхотна работа:

=IF(C8>=$C$4, "Страхотна работа!", ")

Функцията IF тества условие. Ако условието е вярно, се показва първият отговор. Ако условието не е вярно, се показва вторият отговор.

Дотук добре! В тази компания агресивните търговци се възнаграждават, а изоставащите не се хвалят.

Време е да изравните игралното поле. Едно нещо, което не е взето предвид в този пример, е годината на наемане. Някои служители бяха наети през 2016 г., така че честно казано, броят на откритите сметки до момента през 2016 г. не трябва да се сравнява с високата средна стойност.

Следващата фигура показва актуализация на работния лист. Единичната IF функция се заменя с набор от вложени IF функции: Външният IF взема предвид дали годината на наемане е 2016 (тестът на действителното състояние е да се види дали годината на наемане е по-голяма от 2015); и ако е така, вътрешната функция IF проверява дали броят на откритите сметки на служителя за годината до момента е равен или по-голям от половината от общата средна стойност.

Сега са възможни три възможни резултата за съобщението:

Добра работа!
Продължавай да пукаш! Годината още не е свършила!
Няма съобщение

=IF((A8>$A$3), IF(C8>=($C$4/2), "Страхотна работа!","Продължавайте да пляскате! Годината все още не е свършила!"),IF(C8>=$ 4 канадски долара, "Страхотна работа!","))