Korištenje Excela za izračun prosjeka na temelju kriterija

Za izračunavanje prosjeka možete koristiti Excel. Zamislite ovo: trebate izračunati prosjek iz popisa brojeva, koristeći samo brojeve na popisu koji odgovaraju uvjetu. Drugim riječima, izračunajte prosjek koristeći brojeve ako odgovaraju zadanom kriteriju. Excel je pun iznenađenja, a za ovaj trik možete izvući sjajnu funkciju pod nazivom AVERAGEIF.

Sljedeća slika prikazuje popis zaposlenika, godine kada su primljeni, koliko su računa do sada otvorili u 2016. godini i (za neke) poruku da rade sjajno.

Na vrhu radne tablice je prosječan broj otvorenih računa po zaposleniku u 2015. Ispod je očekivani broj računa za otvaranje u 2016. Prosjek od 44 za 2015. samo je statičan broj. 2015. je završila, a ovo je konačni prosjek. Tako je — prošle godine! Što je s 2016? Ovdje postaje zanimljivo.

Za izračun očekivanog broja računa za otvaranje u 2016. izračun se koristi za izvođenje broja kao prosjeka na temelju broja računa koji su do sada otvoreni za godinu. Ovo je tehnika koju bi menadžment mogao koristiti za povećanje konkurencije među zaposlenicima. Drugim riječima, umjesto da se samo fiksira očekivani broj, kao što je 48 (što bi bio rast od 10 posto u odnosu na 2015.), očekivani broj računa za otvaranje temelji se na dosadašnjem poslovanju zaposlenika tijekom godine.

U ćeliji C4 koristi se funkcija AVERAGEIF. Ovako se upisuje:

=PROSJEČNOIF(C8:C16,">" & C3)

Ono što ovo čini je da se s popisa računa po zaposleniku uzima prosjek otvorenih računa, koristeći samo brojeve koji su veći od 44 otvorena u 2015. To je određeno drugim argumentom u funkciji (“>” & C3), koji navodi da se prosjek određuje koristeći samo vrijednosti veće od 44, koje se nalaze u ćeliji C3.

Primjerice, Julie je do sada otvorila samo 40 računa, pa se njezin broj ne koristi u prosjeku. S druge strane, Yolanda je otvorila 48 računa, pa se njezin broj koristi u izračunu prosjeka.

Kada u prosjeku unesete samo brojeve veće od 44 iz 2015., vraća se veći broj za očekivana radna mjesta za 2016. Izračunata vrijednost za 2016. od ovog trenutka je 54. Pored imena svakog zaposlenika, poruka Sjajan posao! pojavljuje se ako taj zaposlenik premašuje prosjek. Poruku će dobiti samo oni koji su otvorili najmanje 54 računa.

Kako? Korištenjem funkcije IF! U svakoj ćeliji u kojoj se pojavljuje poruka, funkcija IF uspoređuje je li broj računa koje je zaposlenik do sada otvorio jednak ili veći od prosjeka prikazanog u ćeliji C4. Evo formule u ćeliji D4 koja pokazuje da Harrison radi sjajan posao:

=IF(C8>=$C$4, "Sjajan posao!", ")

Funkcija IF testira stanje. Ako je uvjet istinit, prikazuje se prvi odgovor. Ako uvjet nije istinit, prikazuje se drugi odgovor.

Zasada je dobro! U ovoj tvrtki agresivni prodavači su nagrađeni, a zaostali se ne hvale.

Vrijeme je za izjednačavanje terena. Jedna stvar koja nije uzeta u obzir u ovom primjeru je godina zaposlenja. Dio djelatnika primljen je u 2016., pa se, pošteno rečeno, broj do sada otvorenih računa u 2016. ne treba uspoređivati ​​s visokim prosjekom.

Sljedeća slika prikazuje ažuriranje radnog lista. Pojedinačna funkcija IF zamjenjuje se skupom ugniježđenih funkcija IF: vanjski IF uzima u obzir je li godina zapošljavanja 2016. (test stvarnog stanja je da se vidi je li godina zapošljavanja veća od 2015.); i, ako je tako, unutarnja IF funkcija testira je li zaposlenikov broj otvorenih računa za dosadašnju godinu jednak ili veći od polovice ukupnog prosjeka.

Sada su moguća tri moguća ishoda za poruku:

Odličan posao!
Nastavi pucati! Godina još nije gotova!
Nema poruke

=IF((A8>$A$3), IF(C8>=($C$4/2), "Sjajan posao!","Nastavi s pukom! Godina još nije gotova!"),IF(C8>=$ 4 C$, "Sjajan posao!","))