Använda Excel för att beräkna ett genomsnitt baserat på kriterier

Du kan använda Excel för att beräkna ett genomsnitt. Föreställ dig detta: Du måste beräkna ett medelvärde från en lista med siffror, med endast siffror i listan som matchar ett villkor. Med andra ord, beräkna medelvärdet med hjälp av siffror om de matchar ett givet kriterium. Excel är fullt av överraskningar, och för detta trick kan du ta fram en fantastisk funktion som heter AVERAGEIF.

Följande figur visar en lista över anställda, tillsammans med åren då de anställdes, hur många konton de har öppnat hittills under 2016 och (för vissa) ett meddelande som säger att de gör ett bra jobb.

Överst på arbetsbladet finns det genomsnittliga antalet öppnade konton per anställd 2015. Nedan visas det förväntade antalet konton som ska öppnas 2016. Genomsnittet på 44 för 2015 är bara ett statiskt tal. 2015 är över, och detta är det slutliga snittet. Det är så - förra året! Vad sägs om 2016? Det är här det blir intressant.

För att beräkna det förväntade antalet konton som ska öppnas 2016 används en beräkning för att härleda antalet som ett genomsnitt baserat på antalet konton som hittills har öppnats för året. Detta är en teknik som ledningen kan använda för att öka konkurrensen bland anställda. Med andra ord, istället för att bara fastställa ett förväntat antal, till exempel 48 (vilket skulle vara 10 procents tillväxt under 2015), är det förväntade antalet konton att öppna baserat på hur anställda har presterat hittills under året.

I cell C4 används AVERAGEIF-funktionen. Så här skrivs det in:

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

Vad detta gör är att ta genomsnittet av öppnade konton från listan över konton per anställd, med endast de siffror som är större än de 44 som öppnades 2015. Detta bestäms av det andra argumentet i funktionen (“>” & C3), vilket tillstånd för att bestämma medelvärdet med endast värden som är större än 44, som finns i cell C3.

Till exempel har Julie hittills bara öppnat 40 konton, så hennes räkning används inte i genomsnittet. Å andra sidan har Yolanda öppnat 48 konton, så hennes räkning används för att beräkna genomsnittet.

När du bara snittar siffrorna som är större än 2015 års 44, returneras ett högre antal för de förväntade öppningarna för 2016. Det beräknade värdet för 2016 från och med denna punkt är 54. Bredvid varje anställds namn, meddelandet Bra jobbat! visas om den anställde slår genomsnittet. Endast de som har öppnat minst 54 konton får meddelandet.

Hur? Genom att använda IF-funktionen! I varje cell där meddelandet visas jämför IF-funktionen om antalet konton som den anställde har öppnat hittills är lika med eller större än genomsnittet som visas i cell C4. Här är formeln i cell D4 som visar att Harrison gör ett bra jobb:

=OM(C8>=$C$4, "Bra jobbat!", ")

IF-funktionen testar ett tillstånd. Om villkoret är sant, visas det första svaret. Om villkoret inte är sant, visas det andra svaret.

Än så länge är allt bra! På det här företaget belönas de aggressiva försäljarna, och efterslänningarna får inget beröm.

Det är dags att jämna ut spelplanen. En sak som inte beaktas i det här exemplet är anställningsåret. Vissa anställda anställdes under 2016, så i rättvisans namn ska antalet öppnade konton hittills under 2016 inte jämföras med det höga genomsnittet.

Följande bild visar en uppdatering av arbetsbladet. Den enda IF-funktionen ersätts med en uppsättning kapslade IF-funktioner: Den yttre IF tar hänsyn till om anställningsåret är 2016 (det faktiska tillståndstestet är att se om anställningsåret är större än 2015); och i så fall testar den inre IF-funktionen om den anställdes antal öppnade konton för året hittills är lika med eller större än hälften av det totala genomsnittet.

Nu är tre möjliga utfall möjliga för meddelandet:

Bra jobbat!
Fortsätt knäcka! Året är inte slut än!
Inget meddelande

=IF((A8>$A$3), IF(C8>=($C$4/2), "Bra jobbat!","Fortsätt knäcka! Året är inte över än!"),IF(C8>=$ 4 C$,"Bra jobbat!",""))