Ved hjelp av Excel for å beregne en gjennomsnittlig basert på kriterier - dummies

Du kan bruke Excel til å beregne et gjennomsnitt. Bilde dette: Du må beregne et gjennomsnitt fra en liste med tall, bare ved hjelp av tall i listen som samsvarer med en tilstand. Med andre ord, beregne gjennomsnittet ved å bruke tall hvis de samsvarer med et gitt kriterium. Excel er full av overraskelser, og for dette trikset kan du trekke ut en flott funksjon som heter AVERAGEIF.

Følgende figur viser en liste over ansatte, sammen med årene da de ble ansatt, hvor mange kontoer de har åpnet så langt i 2016, og (for noen) en melding som sier at de gjør en god jobb.

Øverst på regnearket åpnes gjennomsnittlig antall kontoer per ansatt i 2015. Nedenfor er det forventede antall kontoer som skal åpnes i 2016. Gjennomsnittet på 44 for 2015 er bare et statisk tall. 2015 er over, og dette er det endelige gjennomsnittet. Det er så - i fjor! Hva med 2016? Det er her det blir interessant.

For å beregne forventet antall kontoer som skal åpnes i 2016, brukes en beregning til å utlede tallet som et gjennomsnitt basert på antall kontoer som har blitt åpnet så langt for året. Dette er en teknikkstyring som kan brukes til å øke konkurransen blant ansatte. Med andre ord, i stedet for bare å fikse et forventet antall, som for eksempel 48 (som vil være 10 prosent vekst i løpet av 2015), er det forventede antall kontoer som skal åpnes, basert på hvordan ansatte har utført så langt for året.

I celle C4 brukes AVERAGEIF-funksjonen. Slik gjøres det:

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

Hva dette gjør, tar gjennomsnittet av åpne kontoer fra listen over kontoer per ansatt, bare ved hjelp av tallene som er større enn 44 åpnet i 2015. Dette bestemmes av det andre argumentet i funksjonen (">" og C3), som angir å bestemme gjennomsnittet med bare verdier som er større enn 44, funnet i celle C3.

For eksempel har Julie hittil kun åpnet 40 kontoer, så hennes telle er ikke brukt i gjennomsnittet. På den annen side har Yolanda åpnet 48 kontoer, så hennes telle brukes til å beregne gjennomsnittet.

Når du bare gjennomsnitter tallene høyere enn 2015 er 44, returneres et høyere tall for forventede åpninger for 2016. Beregnet verdi for 2016 på dette punktet er 54. Ved siden av hver ansattes navn, meldingen Great Job! vises hvis den ansatte har slått gjennomsnittet. Bare de som har åpnet minst 54 kontoer, mottar meldingen.

Hvordan? Ved å bruke IF-funksjonen! I hver celle hvor meldingen vises IF-funksjonen, sammenligner hvor mange kontoer ansatte har åpnet så langt, er lik eller større enn gjennomsnittet som vises i celle C4.Her er formelen i celle D4 som viser at Harrison gjør en god jobb:

= IF (C8> = $ C $ 4, "Great Job!", ")

IF-funksjonen tester en tilstand. Det er sant, det første svaret vises. Hvis tilstanden ikke er sant, vises det andre svaret.

Så langt, så bra! På dette selskapet belønnes de aggressive selgerne, og laggardene får ingen ros.

Det er på tide å avgjøre spillereglene. En ting som ikke er tatt i betraktning i dette eksemplet, er utleieår. Noen ansatte ble ansatt i 2016, så i rettferdighet må antallet kontoer som er åpnet så langt i 2016 ikke sammenlignes med det høye gjennomsnittlig.

Følgende figur viser en oppdatering av regnearket. Den enkle IF-funksjonen erstattes med et sett med nestede IF-funksjoner: Ytre IF tar hensyn til om leieåret er 2016 (den faktiske tilstandstesten er å se hvis leieåret er større enn 2015), og i så fall tester den indre IF-funksjonen om ansattens antall åpne kontoer f eller året så langt er lik eller større enn halvparten av gjennomsnittet.

Nå er tre mulige utfall mulig for meldingen:

Flott jobb! Fortsett å sprekke! Året er ikke over ennå! Ingen melding

Keep cracking! melding vises for de som er ansatt i 2016, som har åpnet en rekke kontoer som er lik eller større enn halvparten av gjennomsnittet. Det er en givende melding for de som er nye og gjør en god jobb. Resten av de ansatte er enten superstjerner eller trenger hjelp!

Formelen i Cell D8 (og for hver ansattes rad i kolonne D) er strukturert slik:

= IF ((A8> $ A $ 3), IF (C8> = ($ C $ 4/2) "Great Job!", "Fortsett å knekke! Året er ikke over!"), IF (C8> = $ C $ 4, "Great Job!", "))