Microsoft Excel (vaak Excel genoemd) is krachtige spreadsheetsoftware die is ontworpen om grote gegevenssets op te slaan. Bovendien stelt het gebruikers in staat gegevens te ordenen en analyseren en zelfs eenvoudige tot complexe berekeningen uit te voeren op de geregistreerde gegevens. De essentiële gegevensanalysetaken van Excel omvatten voornamelijk het berekenen of vinden van het gemiddelde (of rekenkundig gemiddelde), de modus en de mediaan.
In deze zelfstudie bespreken we verschillende stapsgewijze methoden of oplossingen voor het berekenen van het gemiddelde in Excel. Voordat we de methoden bespreken, moeten we eerst kort het concept van Mean en de voordelen ervan begrijpen.
Wat is het gemiddelde in Excel?
In Excel is het rekenkundig gemiddelde niets anders dan een gemiddeld getal dat doorgaans wordt berekend door alle getallen van een gegeven gegevensset op te tellen en vervolgens de som te delen door het totale aantal waarden (of aantal) van bepaalde items.
Hoewel het in de praktijk gemakkelijk is om het rekenkundig gemiddelde van kleine datasets te berekenen, wordt het lastig om met grote datasets te werken. Dat is waar een krachtig spreadsheetprogramma als Excel van pas komt. Het kan eenvoudig het gemiddelde van honderden tot duizenden getallen berekenen. Bovendien is het minder gevoelig voor fouten.
Er zijn veel essentiële toepassingen van het gemiddelde bij het analyseren van gegevens, zoals:
- Om historische gegevens te vergelijken
- Om KPI's (Key Performance Indicators) en citaten onder de aandacht te brengen
- Om het middelpunt van de gegeven gegevenspunten te vinden
- Het vaststellen van projectmanagement en businessplannen
Het basisscenario waarin we mogelijk het gemiddelde in Excel moeten berekenen, is wanneer we de gemiddelde verkoop van een artikel over een hele maand, kwartaal, jaar, enz. moeten weten.
Hoe het gemiddelde in Excel berekenen?
Helaas is er geen definitieve MEAN-functie om het rekenkundig gemiddelde voor de gegevens te berekenen of te bepalen. Er zijn echter een aantal effectieve manieren. Over het algemeen kunnen we meestal het wiskundige concept toepassen van het optellen van waarden en het vervolgens delen van de som door het aantal waarden in Excel-cellen. Verder zijn er bestaande functies zoals AVERAGE, AVERAGEA, AVERAGEIF en AVERAGEIFS, die ons ook helpen het rekenkundig gemiddelde in verschillende gebruikssituaties te berekenen.
Laten we, om een beter idee te krijgen van hoe deze concepten werken voor het berekenen van het rekenkundig gemiddelde, ze allemaal afzonderlijk bespreken met een voorbeeld:
Gemiddelde berekenen in Excel: algemene methode
We kunnen de functies SUM en COUNT toepassen in een Excel-werkblad om de aanpak te gebruiken waarbij waarden worden opgeteld en deze worden gedeeld door het aantal opgenomen waarden.
Stel dat we bijvoorbeeld de getallen 1 tot en met 5 hebben gegeven en het rekenkundig gemiddelde moeten berekenen. Wiskundig gezien tellen we eerst al deze getallen bij elkaar op, d.w.z. (1+2+3+4+5). Daarna delen we hun SOM door 5, omdat dit het totale aantal gegeven getallen is.
De hele formule om het gemiddelde te berekenen wordt dus deze:
(1+2+3+4+5)/ 5
afdrukinstructie in Java
Dit retourneert het rekenkundig gemiddelde 3.
In Excel kunnen we de bovenstaande wiskundige formule gebruiken door deze op de volgende manier te combineren met de functies SUM en COUNT:
=SOM(1,2,3,4,5)/AANTAL(1,2,3,4,5)
Hier telt de SUM-functie de gegeven waarden op, terwijl de COUNT-functie het aantal gegeven waarden bepaalt.
Als we nu deze vijf getallen in het bereik A1:A5 registreren, berekent de onderstaande formule in cel A6 het rekenkundig gemiddelde:
=SOM(A1:A5)/AANTAL(A1:A5)
Het resultaat (of gemiddelde) verkregen in de bovenstaande afbeelding is hetzelfde als dat we hebben berekend op basis van de wiskundige formule, dat wil zeggen: 3.
Gemiddelde berekenen in Excel: GEMIDDELDE functie
Omdat het gemiddelde een gemiddelde is, is de GEMIDDELDE functie van Excel de meest efficiënte manier om het rekenkundig gemiddelde te berekenen. Het is een van de meest gebruikte statistische functies om het gemiddelde voor een bepaald bereik te berekenen als er geen speciale voorwaarde of beperking is. Het is de ingebouwde Excel-functie.
Syntaxis
De syntaxis van de GEMIDDELDE functie van Excel is als volgt:
=GEMIDDELD(getal1, [getal2], ...)
Het getal1 is het vereiste argument, wat betekent dat één argument verplicht is in de functie GEMIDDELDE. We kunnen echter meerdere argumenten in verschillende vormen opgeven, zoals getallen, cellen, bereiken, arrays, constanten, enz. Bij het berekenen van het rekenkundig gemiddelde moeten we ten minste twee argumenten opgeven in de functie GEMIDDELDE.
Stappen om het gemiddelde te berekenen met behulp van de GEMIDDELDE functie
Laten we nu de werking van de GEMIDDELDE functie begrijpen en het rekenkundig gemiddelde berekenen voor een groep getallen in een Excel-werkblad met behulp van de voorbeeldgegevens.
Stel dat we een aantal fruitsoorten in een Excel-sheet zetten met de bijbehorende prijzen. De fruitnamen worden vermeld in kolom A (A2:A9) en hun prijzen staan in de volgende kolom B (B2:B9). We moeten de gemiddelde prijs (of het rekenkundig gemiddelde van de prijzen) in cel B10 vinden of berekenen.
kaarten Java
We moeten de onderstaande stappen uitvoeren om het rekenkundig gemiddelde in ons voorbeeldwerkblad te berekenen:
- Eerst moeten we de cel selecteren of markeren om het resulterende rekenkundige gemiddelde vast te leggen, dat wil zeggen cel B10.
- Vervolgens moeten we de functienaam invoegen, beginnend met een gelijkteken, en de nodige argumenten in de functie opgeven. In ons geval typen we het gelijkteken (=) en de GEMIDDELDE functie. We kunnen de functie ook kiezen uit de lijst met functies voorgesteld door Excel.
- Na het invoeren van de functienaam moeten we het beginhaakje invoeren en ons effectieve bereik aan datasets aanleveren. Omdat we gegevens in aangrenzende cellen of een bereik hebben (van B2 tot B9), kunnen we het hele bereik op de volgende manier in de functie doorgeven:
- Ten slotte moeten we het haakje sluiten invoeren en op drukken Binnenkomen op het toetsenbord om het effectieve resultaat of het rekenkundig gemiddelde voor de gegeven dataset te krijgen. De onderstaande afbeelding toont het rekenkundig gemiddelde van de prijzen in de cellen van B2 tot en met B9:
In de bovenstaande afbeelding hebben we de aangrenzende cellen gebruikt en in één keer het hele scala aan datasets aangeleverd. We kunnen echter ook het gemiddelde voor de niet-aangrenzende cellen in ons Excel-blad berekenen door de cellen afzonderlijk aan te leveren.
Stel dat we alleen het rekenkundig gemiddelde van de prijzen voor slechts drie soorten fruit hoeven te berekenen: Appel, mango en druiven. In dat geval kunnen we elke cel afzonderlijk in de functie doorgeven op de volgende manier:
=GEMIDDELD(B2,B3,B6)
Hier zijn B2, B3 en B6 de individuele cellen met de prijzen van appel, mango en druiven.
Hoewel de functie GEMIDDELDE kan worden gebruikt met lege cellen, teksten, nullen, logische waarden en zelfs gemengde argumenten, negeert deze tijdens de berekening de lege cellen, tekst en logische waarden. De cellen met nulwaarde zijn echter opgenomen in de GEMIDDELDE functie.
Gemiddelde berekenen in Excel: AVERAGEA-functie
De functie GEMIDDELDEA is een enigszins geavanceerde versie van de functie GEMIDDELDE. De functie GEMIDDELDEA helpt ons ook het rekenkundig gemiddelde voor bepaalde gegevenssets te berekenen en werkt in alle versies van Excel. Een voordeel van de functie AVERAGEA is dat deze tekstwaarden en de Booleaanse gegevenstypen kan bevatten bij het berekenen van het gemiddelde in Excel. De tekstwaarden worden geëvalueerd als nullen, terwijl de Booleaanse waarden TRUE en FALSE respectievelijk worden geëvalueerd als 1 en 0.
Syntaxis
De syntaxis van de functie AVERAGEA van Excel is als volgt:
=GEMIDDELDEA(waarde1, [waarde2], ...)
Net als de functie GEMIDDELDE is het eerste argument in de functie GEMIDDELDEA vereist. We kunnen echter indien nodig meerdere argumenten gebruiken.
De syntaxis van de functies AVERAGE en AVERAGEA zijn vrijwel gelijk; hun werkconcept is echter iets anders. De functie GEMIDDELDE kan alleen het rekenkundig gemiddelde voor numerieke waarden berekenen, terwijl de functie GEMIDDELDEA hetzelfde kan berekenen voor elk gegevenstype, inclusief tekst en Booleaanse waarden.
Stappen om het gemiddelde te berekenen met behulp van de AVERAGEA-functie
Laten we de vorige voorbeelddatasets opnieuw nemen, waarbij we fruitnamen hebben in kolom A (A2:A9) en hun prijzen in kolom B (B2:B9). We hebben deze keer echter niet de prijs voor de druiven (geregistreerd als 'Niet beschikbaar' in de overeenkomstige cel B6), zoals hieronder weergegeven:
We kunnen de onderstaande stappen uitvoeren en de gemiddelde waarde berekenen met behulp van de AVERAGEA-functie:
- Net als bij de vorige methode selecteren we eerst de resulterende cel B10 om het gemiddelde vast te leggen. Vervolgens typen we het gelijkteken (=), voeren we onze functie AVERAGEA in of selecteren deze en geven het volledige gegevensbereik door aan de functie tussen haakjes.
- Ten slotte moeten we op drukken Binnenkomen sleutel om effectieve output (of gemiddelde) te verkrijgen.
De bovenstaande afbeelding retourneert de gemiddelde uitvoer van 57,125 met de functie AVERAGEA.
Als we in dit voorbeeld de functie GEMIDDELDE gebruiken en het rekenkundig gemiddelde berekenen, zien we een enorm verschil tussen de resultaten die door beide functies worden verkregen. De gemiddelde output van 65,285 verschijnt met de GEMIDDELDE functie, zoals hieronder weergegeven:
De verschillen in het rekenkundig gemiddelde in dit voorbeeld zijn logisch omdat het aantal delingen voor beide functies verschillend is. Het aantal getallen voor de functie AVERAGEA is 8, omdat deze ook de TEKST als geldige gegevens evalueert. Maar voor de functie GEMIDDELDE is het aantal delingen 7, omdat de TEXT-gegevens niet in de berekening worden meegenomen.
Als de gegevensset nul (0) bevat in plaats van TEXT-gegevens, verschijnt hetzelfde resultaat (of rekenkundige gemiddelden) voor beide functies (AVERAGE en AVERAGEA), omdat beide nul als geldige gegevens in de berekening opnemen.
Gemiddelde berekenen in Excel: AVERAGEIF-functie
De GEMIDDELDE ALS-functie is de relevante functie van de GEMIDDELDE functie en helpt ons het rekenkundig gemiddelde te berekenen met een gegeven voorwaarde of criteria. Met de GEMIDDELDE ALS-functie kunnen we alleen het gemiddelde berekenen voor bepaalde getallen die aan de opgegeven criteria voldoen. Excel evalueert eerst de specifieke criteria, lokaliseert de effectieve cellen volgens die criteria en berekent vervolgens het gemiddelde voor alleen de gelokaliseerde cellen.
Syntaxis
De syntaxis van de AVERAGEIF-functie van Excel is als volgt:
=GEMIDDELDEAIF(bereik; criteria, [gemiddeld_bereik])
Hier zijn het bereik en de criteria de vereiste argumenten, terwijl het gemiddelde_bereik optioneel is.
- Een bereik bevat cellen die moeten worden getoetst aan de opgegeven criteria.
- Een criterium verwijst naar het argument waarin we de gewenste voorwaarde kunnen specificeren die moet worden gevolgd. Het kan tekst, numerieke waarden, celverwijzingen, bereik en logische expressie bevatten.
- Het gemiddelde_bereik is het argument waarin we cellen (of een bereik) vermelden waarvoor we het gemiddelde willen berekenen. Als dit argument niet is opgegeven, gebruikt Excel het bereik automatisch.
Stappen om het gemiddelde te berekenen met behulp van de AVERAGEIF-functie
Stel dat we in hetzelfde voorbeeld bepaalde soorten fruit meerdere keren vermelden met verschillende prijzen. Deze keer hoeven we alleen het gemiddelde te berekenen voor een specifieke herhaalde vrucht (bijvoorbeeld appel) in cel B11. We gebruiken dus de GEMIDDELDE ALS-functie en passen de voorwaarde toe bij het berekenen van het gemiddelde. We moeten de onderstaande stappen uitvoeren om de gemiddelde prijs voor Apple te berekenen:
- Eerst selecteren we de resulterende cel (B11) en voegen we de AVERAGEIF-formule op de volgende manier in:
=GEMIDDELDIF(A2:B9, 'Appel', B2:B9)
Hier vertegenwoordigt A2:B9 het effectieve bereik, Apple vertegenwoordigt onze gewenste criteria/voorwaarde, en B2:B9 vertegenwoordigt het gemiddelde_bereik.
Hier hebben we het optionele gemiddelde_bereik handmatig opgegeven om alleen de cellen met de prijzen op te nemen. Als we in ons voorbeeld het gemiddelde_bereik niet doorgeven, gebruikt de functie het primaire bereik (A2:B9) met de tekstgegevens en zorgt ervoor dat de #DIV! fout.
Opmerking: het is essentieel op te merken dat de functie AVERAGEIF de lege cellen in het argument gemiddelde_bereik negeert. Als een cel die in de criteria is opgegeven leeg is, behandelt de functie deze bovendien als een cel met een nulwaarde.
Gemiddelde berekenen in Excel: AVERAGEIFS-functie
De AVERAGEIFS-functie is een uitgebreide of geavanceerde versie van de AVERAGEIF-functie. Met de functie AVERAGEIF kunnen we slechts één voorwaarde tegelijk toepassen. Met de functie AVERAGEIFS kunnen we echter meerdere criteria toepassen om tegelijkertijd te testen. Bovendien stelt de functie AVERAGEIFS ons in staat cellen met Booleaanse TRUE- en FALSE-waarden te gebruiken, waardoor we eenvoudig meerdere criteria kunnen testen. Dit is ook niet mogelijk met de AVERAGEIF-functie.
Syntaxis
De syntaxis van de functie AVERAGEIFS van Excel is als volgt:
=GEMIDDELDEAIFS(gemiddeld bereik, criteria1_bereik1, criteria1, [criteria2_bereik2, criteria2], ...)
Hier zijn het gemiddelde_bereik, criteria1_bereik1 en criteria1 de vereiste argumenten. We kunnen echter de optionele argumenten leveren op basis van onze effectieve datasets. Verder verwijst criteria1 naar een aandoening die moet worden getest op bereik1, verwijst criteria2 naar een aandoening die moet worden getest op bereik2, en meer.
Stappen om het gemiddelde te berekenen met behulp van de AVERAGEIFS-functie
Laten we dezelfde voorbeeldgegevens bekijken die we in de vorige methode hebben gebruikt. In de vorige methode gebruikten we de GEMIDDELDE ALS-functie en berekenden we het gemiddelde voor de prijzen van herhaald fruit (Apple). In dat geval hadden we slechts één voorwaarde.
verander de mapnaam linux
Stel nu dat we het gemiddelde voor de prijzen van Apple moeten berekenen, maar alleen die cellen moeten opnemen waar de prijzen boven de 40 liggen.
In dit geval hebben we twee verschillende criteria, dus de functie AVERAGEIF kan niet worden gebruikt.
Criteria1: In een bereik A2:A9 is het initiële criterium het controleren op 'Apple' waaraan wordt voldaan door drie cellen (of rijen)
Criteria2: In een bereik B2:B9 is het andere criterium het controleren op prijzen boven de 40 (>40), waaraan wordt voldaan door de twee van de drie cellen (of rij) met een fruitappel
Als we dus het gemiddelde wiskundig berekenen, gebruiken we de onderstaande formule:
(95+68)/2
Dit retourneert de gemiddelde waarde van 81,5. Laten we controleren of we dezelfde resultaten krijgen met de AVERAGEIFS-functie in Excel.
Wanneer we voorwaarden in Excel moeten testen, gebruiken we meestal de hulp van de logische operator OR. De OR-operator retourneert TRUE en FALSE, afhankelijk van of aan de gegeven voorwaarde wordt voldaan. Stel dat we bijvoorbeeld moeten controleren of cel A2 Apple bevat. In dit geval kunnen we de OR-operator als volgt gebruiken:
=OF(A2='Appel')
We voegen de bovenstaande voorwaarde in ons voorbeeldblad in om cellen met de fruitappel te testen of te lokaliseren. We maken dus een nieuwe kolom (C) en typen de specifieke voorwaarde in cel C2. Daarna kopiëren we dezelfde voorwaarde naar de resterende cellen van kolom C. Het ziet er als volgt uit:
De bovenstaande afbeelding laat zien dat de OR-operator TRUE retourneert voor cellen die overeenkomen met de vrucht Appel, terwijl deze voor andere FALSE retourneert. Op basis van dit resultaat passen we nu de functie AVERAGEIFS toe en voegen we op de volgende manier meerdere voorwaarden in voor TRUE-waardecellen:
- Net als bij andere methoden selecteren we opnieuw eerst de resulterende cel (d.w.z. B12).
- Vervolgens voeren we de AVERAGEIFS-formule op de volgende manier in:
=GEMIDDELDIFS(B2:B9,C2:C9,'WAAR',B2:B9,'>40')
Met de bovenstaande AVERAGEIFS-formule hebben we alleen het fruit 'Appel' en prijzen boven de 40 meegenomen. - Ten slotte moeten we op drukken Binnenkomen toets om het resultaat van de formule te verkrijgen. Voor onze dataset retourneerde de formule het rekenkundig gemiddelde van 81,5, hetzelfde als ons wiskundig berekende resultaat.
Speciale gevallen
Hoewel we verschillende scenario's kunnen hebben bij het berekenen van het rekenkundig gemiddelde in Excel, worden de twee meest voorkomende scenario's hier besproken. In deze gevallen moeten we voorzichtig zijn bij het berekenen van het rekenkundig gemiddelde.
Berekening van het gemiddelde inclusief nullen
Wanneer we het gemiddelde met de nullen moeten berekenen, kunnen we normaal gesproken de functie GEMIDDELDE gebruiken en de effectieve cellen leveren, inclusief de nulwaardecellen. De functie GEMIDDELDE neemt de nullen als geldige getallen op in de berekening.
anders Java
In de volgende voorbeeldgegevensset passen we de functie GEMIDDELDE toe om het gemiddelde voor de cijfers van twee verschillende studenten te berekenen:
De bovenstaande afbeelding toont de verschillende gemiddelde waarden voor beide studenten. Als we naar de cijfers kijken, is de som van de cijfers voor beide studenten hetzelfde. De tweede leerling heeft nul punten voor één vak (cel nulwaarde), terwijl de eerste leerling afwezig is (tekstwaardecel) voor één vak.
Omdat de functie GEMIDDELDE tekstwaarden negeert maar nulwaarden bevat, varieert het aantal delingen voor beide leerlingen. Voor de eerste leerling is het aantal divisies 4, terwijl dit voor een andere leerling 5 is. Er is dus een verschil in de gemiddelde resultaten. Indien nodig kunnen we echter ook expliciet nulwaarden uitsluiten.
Berekening van het gemiddelde exclusief nullen
Wanneer we het gemiddelde moeten berekenen exclusief de nullen, moeten we profiteren van de AVERAGEIF-functie van Excel. We kunnen de specifieke voorwaarde in deze functie toepassen, waarbij Excel wordt geïnstrueerd om de nulwaardecellen tijdens de berekening niet mee te nemen.
De onderstaande AVERAGEIF-formule kan worden gebruikt om nulwaarden uit te sluiten bij het berekenen van het gemiddelde in Excel:
=AVERAGEIF(cel of een bereik,'>0')
Als we de bovenstaande formule voor onze voorbeeldgegevens invoegen, verschijnen voor beide leerlingen dezelfde gemiddelde waarderesultaten.
De bovenstaande AVERAGEIF-formule negeert de tekst- en nulwaardecellen. Het aantal delingen is dus hetzelfde (d.w.z. 4), en dat geldt ook voor het rekenkundig gemiddelde van de cijfers van beide studenten.
Belangrijke punten om te onthouden
- Het is belangrijk op te merken dat elke functie in Excel, inclusief AVERAGE, AVERAGEA, AVERAGEIF en AVERAGEIFS, maximaal 255 argumenten kan accepteren.
- Het handmatig typen van de functienaam in een Excel-cel wordt niet aanbevolen. Het is beter om de gewenste functie uit de suggestielijst te selecteren om verkeerd gespelde functienamen te voorkomen en fouten in de resultaten te elimineren.