SÅDAN BEREGNES GENNEMSNITTET I EXCEL

Microsoft Excel (ofte omtalt som Excel) er kraftfuld regnearkssoftware designet til at gemme store datasæt. Det gør det også muligt for brugere at organisere og analysere data og endda udføre grundlæggende til komplekse beregninger på de registrerede data. Excels væsentlige dataanalyseopgaver omfatter hovedsageligt at beregne eller finde middelværdien (eller aritmetisk middelværdi), tilstand og median.

I denne tutorial diskuterer vi forskellige trinvise metoder eller løsninger til, hvordan man beregner gennemsnittet i Excel. Før vi diskuterer metoderne, lad os først kort forstå begrebet Middel og dets fordele.

Hvad er middelværdien i Excel?

I Excel er den aritmetiske middelværdi intet andet end et gennemsnitstal, der typisk beregnes ved at tilføje alle numre af et givet datasæt og derefter dividere summen med det samlede antal værdier (eller antal) af givne elementer.

Selvom det er nemt at beregne det aritmetiske middelværdi for små datasæt i det virkelige liv, bliver det svært at arbejde med store datasæt. Det er her et kraftfuldt regnearksprogram som Excel kommer til nytte. Det kan nemt beregne gennemsnittet af hundreder til tusindvis af tal. Desuden er den mindre tilbøjelig til at fejle.

Der er mange væsentlige anvendelser af middelværdien til at analysere data, såsom:

At sammenligne historiske data
At fremhæve KPI'er (Key Performance Indicators) og citater
At finde midtpunktet for de givne datapunkter
At fastlægge projektledelse og forretningsplaner

Det grundlæggende scenarie, hvor vi muligvis skal beregne gennemsnittet i Excel, er, når vi skal finde ud af det gennemsnitlige salg af en vare i en hel måned, kvartal, år osv.

Hvordan beregner man middelværdi i Excel?

Desværre er der ingen bestemt MEAN-funktion til at beregne eller bestemme det aritmetiske middelværdi for dataene. Der er dog et par effektive måder. Generelt kan vi normalt anvende det matematiske koncept med at tilføje værdier og derefter dividere deres sum med antallet af værdier i Excel-celler. Desuden er der eksisterende funktioner som AVERAGE, AVERAGEA, AVERAGEIF og AVERAGEIFS, som også hjælper os med at beregne det aritmetiske middelværdi i forskellige anvendelsestilfælde.

For at få en bedre ide om, hvordan disse begreber virker til at beregne det aritmetiske middelværdi, lad os diskutere hver enkelt individuelt med et eksempel:

Beregning af middelværdi i Excel: Generel metode

Vi kan anvende funktionerne SUM og COUNT i et Excel-ark for at bruge tilgangen til at tilføje værdier og dividere dem med antallet af inkluderede værdier.

Antag for eksempel, at vi har givet tallene fra 1 til 5 og skal beregne det aritmetiske gennemsnit. Matematisk lægger vi først alle disse tal sammen, dvs. (1+2+3+4+5). Bagefter dividerer vi deres SUM med 5, fordi det er det samlede antal af givne tal.

Således bliver hele formlen til at beregne middelværdien denne:

(1+2+3+4+5)/ 5

Dette returnerer det aritmetiske gennemsnit 3.

I Excel kan vi bruge ovenstående matematiske formel ved at kombinere den med funktionerne SUM og COUNT på følgende måde:

=SUM(1;2;3;4;5)/ANTAL(1;2;3;4;5)

Her lægger SUM-funktionen de givne værdier sammen, mens COUNT-funktionen bestemmer antallet af givne værdier.

Hvis vi nu registrerer disse fem tal i området A1:A5, vil nedenstående formel i celle A6 beregne det aritmetiske middelværdi:

=SUM(A1:A5)/ANTAL(A1:A5)

Resultatet (eller middelværdien) opnået i ovenstående billede er det samme som det, vi beregnede ud fra den matematiske formel, dvs. 3.

Beregning af middelværdi i Excel: AVERAGE Funktion

Da middelværdien er et gennemsnit, er Excels AVERAGE-funktion den mest effektive måde at beregne det aritmetiske middelværdi på. Det er en mest almindeligt anvendt statistisk funktion til at beregne middelværdien for et bestemt interval, hvis der ikke er nogen speciel betingelse eller begrænsning. Det er den indbyggede Excel-funktion.

Syntaks

Syntaksen for Excels AVERAGE-funktion er som følger:

=MIDDEL(tal1, [tal2], ...)

Tallet1 er det påkrævede argument, hvilket betyder, at ét argument er obligatorisk i funktionen AVERAGE. Vi kan dog levere flere argumenter i forskellige former som tal, celler, områder, arrays, konstanter osv. Når vi beregner det aritmetiske middelværdi, skal vi angive mindst to argumenter i AVERAGE-funktionen.

Trin til beregning af middelværdi ved hjælp af AVERAGE-funktionen

Lad os nu forstå virkemåden af AVERAGE-funktionen og beregne det aritmetiske middelværdi for en gruppe af tal i et Excel-ark ved hjælp af eksempeldataene.

Antag, at vi lister nogle frugter i et Excel-ark med deres tilsvarende priser. Frugtnavnene er registreret i kolonne A (A2:A9), og deres priser er i den følgende kolonne B (B2:B9). Vi skal finde eller beregne gennemsnitsprisen (eller aritmetisk middelværdi for priserne) i celle B10.

Vi skal udføre nedenstående trin for at beregne det aritmetiske middelværdi i vores eksempelarbejdsark:

Først skal vi vælge eller fremhæve cellen for at registrere det resulterende aritmetiske gennemsnit, dvs. celle B10.
Dernæst skal vi indsætte funktionsnavnet begyndende med et lighedstegn og give de nødvendige argumenter i funktionen. I vores tilfælde skriver vi lighedstegnet (=) og AVERAGE-funktionen. Vi kan også vælge funktionen fra listen over funktioner foreslået af Excel.
Efter indtastning af funktionsnavnet skal vi indtaste startparentesen og levere vores effektive række af datasæt. Da vi har data i tilstødende celler eller et område (fra B2 til B9), kan vi videregive hele området i funktionen på følgende måde:
Til sidst skal vi indtaste den afsluttende parentes og trykke på Gå ind tasten på tastaturet for at få det effektive resultat eller aritmetiske middelværdi for det givne datasæt. Billedet nedenfor viser det aritmetiske gennemsnit for priserne i celler fra B2 til B9:

I ovenstående billede brugte vi de tilstødende celler og leverede hele rækken af datasæt på én gang. Vi kan dog også beregne middelværdien for de ikke-tilstødende celler i vores Excel-ark ved at levere cellerne individuelt.
Antag, at vi kun behøver at beregne det aritmetiske gennemsnit af priserne for kun tre frugter: Æble, mango og druer. I så fald kan vi videregive hver celle separat i funktionen på følgende måde:
=MIDDEL(B2;B3;B6)

Her er B2, B3 og B6 de enkelte celler med priserne på æble, mango og druer.

Selvom AVERAGE-funktionen kan bruges med tomme celler, tekster, nuller, logiske værdier og endda blandede argumenter, ignorerer den tomme celler, tekst og logiske værdier under beregningen. Imidlertid er nulværdicellerne inkluderet i AVERAGE-funktionen.

substring_index i sql

Beregning af middelværdi i Excel: AVERAGEA-funktion

AVERAGEA-funktionen er en lidt avanceret version af AVERAGEA-funktionen. AVERAGEA-funktionen hjælper os også med at beregne det aritmetiske middelværdi for givne datasæt og fungerer i alle versioner af Excel. En fordel ved AVERAGEA-funktionen er, at den kan inkludere tekstværdier og de boolske datatyper, mens den beregner middelværdien i Excel. Tekstværdierne evalueres som nuller, mens de boolske værdier TRUE og FALSE evalueres som henholdsvis 1 og 0.

Syntaks

Syntaksen for Excels AVERAGEA-funktion er som følger:

=MIDDEL(værdi1; [værdi2], ...)

Ligesom funktionen AVERAGE kræves det første argument i AVERAGEA-funktionen. Vi kan dog bruge flere argumenter efter behov.

Syntaksen for funktionerne AVERAGE og AVERAGEA er næsten ens; deres arbejdskoncept er dog lidt anderledes. Funktionen AVERAGEA kan kun beregne det aritmetiske middelværdi for numeriske værdier, mens AVERAGEA-funktionen kan beregne det samme for enhver datatype, inklusive tekst og booleaner.

Trin til beregning af middelværdi ved hjælp af AVERAGEA-funktionen

Lad os gentage de foregående eksempeldatasæt, hvor vi har frugtnavne i kolonne A (A2:A9) og deres priser i kolonne B (B2:B9). Vi har dog ikke prisen for druerne denne gang (registreret som 'Ikke tilgængelig' i den tilsvarende celle B6), som vist nedenfor:

Vi kan udføre nedenstående trin og beregne middelværdien ved hjælp af AVERAGEA-funktionen:

Ligesom den foregående metode vælger vi først den resulterende celle B10 for at registrere middelværdien. Dernæst skriver vi lighedstegnet (=), indtaster eller vælger vores funktion AVERAGEA og sender hele dataområdet til funktionen indesluttet i parentesen.
Til sidst skal vi trykke på Gå ind nøgle til at opnå effektiv output (eller middelværdi).

Ovenstående billede returnerer det gennemsnitlige output på 57,125 med funktionen AVERAGEA.
Hvis vi bruger AVERAGE-funktionen i dette eksempel og beregner det aritmetiske middelværdi, ser vi en enorm forskel mellem resultaterne opnået af begge funktioner. Middeloutputtet på 65.285 vises med AVERAGE-funktionen som vist nedenfor:

Forskellene i aritmetisk middelværdi i dette eksempel giver mening, fordi divisionstal er forskelligt for begge disse funktioner. Antallet tæller for AVERAGEA-funktionen er 8, da den også evaluerer TEKST som gyldige data. Men for AVERAGE-funktionen er divisionsantallet 7, da det ikke inkluderer TEKST-dataene i dens beregning.
I stedet for TEXT-data, hvis datasættet indeholder nul (0), vil det samme resultat (eller aritmetiske middelværdier) vises for begge funktioner (AVERAGE og AVERAGEA), fordi begge inkluderer nul som gyldige data i beregningen.

Beregning af middelværdi i Excel: AVERAGEIF-funktion

AVERAGEIF-funktionen er den relevante funktion af AVERAGE-funktionen og hjælper os med at beregne det aritmetiske middelværdi med en given betingelse eller kriterier. Med AVERAGEIF-funktionen kan vi kun beregne middelværdien for visse tal, der følger de angivne kriterier. Excel evaluerer først de specifikke kriterier, lokaliserer de effektive celler efter disse kriterier og beregner derefter gennemsnittet for kun de lokaliserede celler i overensstemmelse hermed.

Syntaks

Syntaksen for Excels AVERAGEIF-funktion er som følger:

=GENNEMSN.AIF(interval; kriterier; [gennemsnit_interval])

Her er området og kriterierne de påkrævede argumenter, mens gennemsnitsområdet er valgfrit.

Et område indeholder celler, der skal testes i forhold til de givne kriterier.
Et kriterium refererer til argumentet, hvor vi kan specificere den ønskede betingelse, der skal følges. Det kan omfatte tekst, numeriske værdier, cellereference, interval og logisk udtryk.
Gennemsnitsområdet er argumentet, hvor vi nævner celler (eller et område), som vi ønsker at beregne middelværdien for. Hvis dette argument ikke er angivet, bruger Excel området automatisk.

Trin til beregning af middelværdi ved hjælp af AVERAGEIF-funktionen

Lad os antage, at vi i det samme eksempel viser nogle frugter flere gange med forskellige priser. Denne gang behøver vi kun at beregne middelværdien for enhver specifik gentagne frugter (dvs. æble) i celle B11. Så vi bruger AVERAGEIF-funktionen og anvender betingelsen, mens vi beregner middelværdien. Vi skal udføre nedenstående trin for at beregne gennemsnitsprisen for Apple:

Først vælger vi den resulterende celle (B11) og indsætter AVERAGEIF-formlen på følgende måde:
=MIDDELHVIS(A2:B9; 'Æble'; B2:B9)
Her repræsenterer A2:B9 det effektive interval, Apple repræsenterer vores ønskede kriterier/tilstand, og B2:B9 repræsenterer gennemsnitsområdet.

Her leverede vi det valgfri gennemsnit_område manuelt for kun at inkludere cellerne med priserne. I vores eksempel, hvis vi ikke passerer gennemsnitsområdet, vil funktionen bruge det primære område (A2:B9) med tekstdataene og forårsage #DIV! fejl.

Bemærk: Det er vigtigt at bemærke, at funktionen AVERAGEIF ignorerer de tomme celler i dens argument gennemsnitsområde. Derudover, hvis en celle angivet i kriterierne er tom, behandler funktionen den som en nulværdicelle.

Beregning af middelværdi i Excel: AVERAGEIFS-funktion

AVERAGEIFS-funktionen er en udvidet eller avanceret version af AVERAGEIFS-funktionen. Funktionen AVERAGEIF giver os kun mulighed for at anvende én betingelse ad gangen. Men med AVERAGEIFS-funktionen kan vi anvende flere kriterier, der skal testes samtidigt. Derudover gør AVERAGEIFS-funktionen os i stand til at bruge celler med boolske SAND- og FALSK-værdier, hvilket hjælper os med nemt at teste flere kriterier. Dette er heller ikke muligt med AVERAGEIF-funktionen.

Syntaks

Syntaksen for Excels AVERAGEIFS-funktion er som følger:

=GENNEMSN.AIFS(gennemsnitsområde, kriterie1_område1, kriterie1, [kriterie2_område2, kriterium2], ...)

Her er gennemsnitsområdet, kriterie1_område1 og kriterie1 de påkrævede argumenter. Vi kan dog levere de valgfrie argumenter baseret på vores effektive datasæt. Endvidere refererer kriterie1 til en tilstand, der skal testes på område1, kriterium2 refererer til en tilstand, der skal testes på område2, og mere.

Trin til beregning af middelværdi ved hjælp af AVERAGEIFS-funktionen

Lad os overveje de samme eksempeldata, som vi brugte i den foregående metode. I den foregående metode brugte vi AVERAGEIF-funktionen og beregnede middelværdien for priserne på gentagen frugt (æble). I så fald havde vi kun én betingelse.

Antag nu, at vi skal beregne gennemsnittet for priserne på Apple, men kun inkludere de celler, hvor priserne er over 40.

Vi har to forskellige kriterier i dette tilfælde, så funktionen AVERAGEIF kan ikke bruges.

Kriterier 1: I et område A2:A9 er startkriteriet at tjekke for 'Apple', der er opfyldt af tre celler (eller rækker)

Kriterier 2: I et område B2:B9 er de andre kriterier at tjekke for priser over 40 (>40), som er opfyldt af to ud af tre celler (eller række) med et frugtæble

Så hvis vi matematisk beregner middelværdien, bruger vi nedenstående formel:

(95+68)/2

Dette returnerer middelværdien af 81,5. Lad os tjekke, om vi får de samme resultater med AVERAGEIFS-funktionen i Excel.

Når vi skal teste forhold i Excel, tager vi normalt hjælp af den logiske OR-operator. ELLER-operatoren returnerer SAND og FALSK afhængig af om den givne betingelse opfylder. Antag for eksempel, at vi skal kontrollere, om celle A2 indeholder Apple. I dette tilfælde kan vi bruge OR-operatoren sådan her:

=ELLER(A2='Apple')

Vi indsætter ovenstående betingelse i vores eksempelark for at teste eller lokalisere celler med frugten Æble. Så vi opretter en ny kolonne (C) og skriver den specifikke betingelse i celle C2. Bagefter kopierer vi den samme tilstand ned i de resterende celler i kolonne C. Det ser sådan ud:

Ovenstående billede viser, at OR-operatøren returnerer SAND for celler, der svarer til frugten Æble, mens den for andre returnerer FALSK. Baseret på dette resultat anvender vi nu AVERAGEIFS-funktionen og indsætter flere betingelser for TRUE-værdiceller på følgende måde:

Som andre metoder vælger vi igen først den resulterende celle (dvs. B12).
Dernæst indtaster vi AVERAGEIFS-formlen på følgende måde:
=AVERAGEIFS(B2:B9,C2:C9,'TRUE',B2:B9;'>40')

Med ovenstående AVERAGEIFS-formel inkluderede vi kun frugten 'Æble' og priser over 40.
Til sidst skal vi trykke på Gå ind tasten for at opnå formelresultatet. For vores datasæt returnerede formlen det aritmetiske gennemsnit af 81,5, det samme som vores matematisk beregnede resultat.

Særlige tilfælde

Selvom vi kan have forskellige scenarier, mens vi beregner det aritmetiske gennemsnit i Excel, diskuteres de to mest almindelige her. Vi skal være forsigtige, når vi beregner det aritmetiske gennemsnit i disse tilfælde.

java streng join

Beregning af middelværdi inklusive nuller

Når vi skal beregne middelværdien med nullerne, kan vi normalt bruge AVERAGE-funktionen og levere de effektive celler, inklusive nulværdicellerne. Funktionen AVERAGE inkluderer nullerne som gyldige tal i sin beregning.

I det følgende eksempeldatasæt anvender vi AVERAGE-funktionen til at beregne middelværdien for to forskellige elevers karakterer:

Ovenstående billede viser de forskellige middelværdier for begge elever. Hvis vi ser på karaktererne, er summen af karakterer den samme for begge elever. Den anden elev har nul karakterer i ét fag (nulværdicelle), mens den første elev er fraværende (tekstværdicelle) i ét fag.

Da AVERAGE-funktionen ignorerer tekstværdier, men inkluderer nulværdier, varierer divisionsantallet for begge elever. For den første elev er delingstallet 4, mens det for en anden elev er 5. Der er således forskel på gennemsnitsresultaterne. Vi kan dog også eksplicit ekskludere nulværdier, hvis det er nødvendigt.

Beregning af middelværdi ekskl. nuller

Når vi skal beregne middelværdien eksklusive nullerne, skal vi udnytte Excels AVERAGEIF-funktion. Vi kan anvende den specifikke betingelse i denne funktion og instruere Excel om ikke at inkludere nulværdicellerne under beregningen.

Nedenstående AVERAGEIF-formel kan bruges til at udelukke nulværdier under beregning af middelværdien i Excel:

=MIDDELHVIS(celle eller et område,'>0')

Hvis vi indsætter ovenstående formel for vores prøvedata, vises de samme middelværdiresultater for begge elever.

Ovenstående AVERAGEIF-formel ignorerer tekst- og nulværdicellerne. Så divisionsantallet er det samme (dvs. 4), og det samme er det aritmetiske gennemsnit for begge elevers karakterer.

Vigtige punkter at huske

Det er vigtigt at bemærke, at enhver funktion i Excel, inklusive AVERAGE, AVERAGEA, AVERAGEIF og AVERAGEIFS, kan maksimalt acceptere 255 argumenter.
Manuel indtastning af funktionsnavnet i en Excel-celle anbefales ikke. At vælge den ønskede funktion fra forslagslisten er bedre for at undgå fejlstavede funktionsnavne og eliminere fejl i resultaterne.