Hoe berekent u het gemiddelde van een kolom op basis van criteria in een andere kolom in Excel?
In veel praktische Excel-scenario’s is het vaak nodig om het gemiddelde van waarden in één kolom te berekenen, gegroepeerd of gefilterd op basis van overeenkomende waarden in een andere kolom. U wilt bijvoorbeeld het gemiddelde van de omzet per verkoper of per regio bepalen, zoals in de onderstaande afbeelding. Dit soort berekening wordt veel gebruikt in samenvattende rapporten, prestatieanalyses en gegevensreviews. In dit artikel tonen we verschillende effectieve methoden om dit resultaat te bereiken, zodat u de aanpak kunt kiezen die het beste aansluit bij uw behoeften en vaardigheidsniveau.
Bereken het gemiddelde in een kolom op basis van dezelfde waarde in een andere kolom met formules
Bereken het gemiddelde in een kolom op basis van dezelfde waarde in een andere kolom met Kutools voor Excel
Bereken het gemiddelde per groep met behulp van Draaitabel
Automatiseer de berekening van gegroepeerde gemiddelden met een VBA-macro
Bereken het gemiddelde in een kolom op basis van dezelfde waarde in een andere kolom met formules
Een van de eenvoudigste manieren om het gemiddelde per groep te berekenen op basis van een andere kolom in Excel, is met voorwaardelijke formules zoals GEMIDDELDE.ALS of GEMIDDELDE.ALS.MEERDERE. Deze aanpak is ideaal als u gerichte resultaten wilt voor specifieke criteria, zoals het gemiddelde van de omzet per stad of verkoper.
1. Selecteer een lege cel waarin u het resultaat wilt weergeven, voer daarna de volgende formule in en druk op Enter:
=AVERAGEIF(B2:B13,E2,C2:C13)
Uitleg van de parameters: In bovenstaande formule is B2:B13 het bereik met de criteria (bijvoorbeeld stad of verkoper), E2 de specifieke waarde waarmee u wilt vergelijken (bijvoorbeeld „Owenton”) en C2:C13 het bereik met de numerieke waarden waarvan u het gemiddelde wilt berekenen.
Na het indrukken van Enter krijgt u direct het gemiddelde voor de groep die in cel E2 is opgegeven – bijvoorbeeld het gemiddelde van de omzet voor „Owenton”.
Wilt u het gemiddelde berekenen voor elke unieke waarde in de criteriakolom? Pas dan eenvoudig de waarde in de criteriacel (E2) aan, of kopieer de formule omlaag als u een lijst met unieke waarden hebt.
Praktische tip: Bij grotere datasets of een groot aantal unieke groepen kunt u deze formule combineren met een lijst van unieke waarden (met tools zoals ‘Dubbele items verwijderen’ of de functie UNIEK in Office 365 en Excel 2021) om alle groepsgemiddelden in één keer te berekenen. Controleer altijd dubbel of de bereiken in uw formule alle relevante gegevens bestrijken en goed uitgelijnd blijven wanneer u de formule kopieert.
Veelvoorkomende fouten en probleemoplossing:
- Als u een #DIV/0!-fout krijgt, controleer dan of uw criteria-waarde daadwerkelijk voorkomt in het geselecteerde bereik.
- Zorg ervoor dat uw numerieke bereik uitsluitend geldige getallen bevat—tekst of lege cellen kunnen de berekening verstoren.
Bereken het gemiddelde in een kolom op basis van dezelfde waarde in een andere kolom met Kutools voor Excel
Als u automatisch het gemiddelde van alle unieke waarden in één kolom wilt berekenen — zonder steeds opnieuw formules in te voeren of handmatig te filteren — biedt Kutools voor Excel een gestroomlijnde oplossing. Ideaal bij het verwerken van grote lijsten of complexe datasets!
1. Selecteer het volledige gegevensbereik dat zowel de kolom waarmee u wilt groeperen als de numerieke kolom bevat waarvan u het gemiddelde wilt berekenen. Ga vervolgens naar Kutools > Samenvoegen en splitsen > Geavanceerd samenvoegen van rijen.
2. Voer in het dialoogvenster Rijen samenvoegen op basis van kolom de volgende stappen uit:
- Selecteer de kolom waarnaar u wilt groeperen (bijvoorbeeld Stad of Verkoper) en klik op de knop Primaire sleutel om deze als groepeerkolom in te stellen.
- Selecteer de numerieke kolom waarvan u het gemiddelde wilt berekenen en klik vervolgens op Berekenen > Gemiddelde.
Tip: Voor andere kolommen (zoals datums) kunt u aangeven hoe hun waarden moeten worden gecombineerd, bijvoorbeeld door ze samen te voegen met een komma. - Klik op OK om de bewerking uit te voeren.
Kutools groepeert de gegevens direct op basis van uw geselecteerde sleutel en toont het gemiddelde voor elke groep in de numerieke kolom.
Batchverwerking met Kutools is perfect voor gebruikers die regelmatig gegroepeerde statistieken analyseren, zoals maandrapporten, afdelingsoverzichten of andere multigroepsberekeningen. Als invoegtoepassing laat Kutools uw oorspronkelijke gegevensstructuur onaangetast en stelt de voorbeeldfunctie u in staat de groeperingen te controleren voordat u wijzigingen toepast.
Opmerkingen en tips:
- Zorg ervoor dat uw geselecteerde bereik geen lege rijen bevat voordat u de tool uitvoert.
- Als u de gegroepeerde gemiddelden ergens anders nodig heeft, kopieert en plakt u de resultaten na de berekening.
- Controleer bij zeer grote gegevenssets of de groepeer- en numerieke kolommen correct zijn toegewezen, zodat verwarring wordt voorkomen.
Kutools voor Excel– Geef Excel een boost met meer dan 300 essentiële hulpmiddelen, waardoor uw werk sneller en eenvoudiger verloopt, en profiteer van AI-functies voor slimmere gegevensverwerking en productiviteit.Nu verkrijgen
Bereken het gemiddelde per groep met behulp van Draaitabel
Draaitabellen bieden een krachtige, ingebouwde manier om gegevens samen te vatten, te groeperen en te analyseren – inclusief het berekenen van gemiddelden per groep – zonder formules of externe invoegtoepassingen. Deze methode is ideaal als u een interactief overzicht wilt van gemiddelden en totalen per categorie, en werkt naadloos voor zowel kleine als zeer grote datasets.
Hoe configureert u een Draaitabel om groepsgemiddelden te berekenen:
- Selecteer een willekeurige cel in uw gegevensset en ga vervolgens naar Invoegen > Draaitabel. Kies in het dialoogvenster waar u de draaitabel wilt plaatsen (nieuw werkblad of bestaand werkblad) en klik op OK.
- Sleep in het deelvenster Draaitabelvelden de kolom waarnaar u wilt groeperen naar het gebied Rijen (bijvoorbeeld ‘Stad’ of ‘Verkoper’).
- Sleep de numerieke kolom waarvan u het gemiddelde wilt berekenen (bijvoorbeeld „Omzet”) naar het gebied Waarden. Excel berekent standaard mogelijk de som; om dit te wijzigen, klikt u op het waardeveld, selecteert u Waarde-instellingen en kiest u Gemiddelde.
Uw draaitabel toont direct het gemiddelde per groep. Filter, sorteer en vorm het rapport razendsnel in. Deze methode is gebruiksvriendelijk en voorkomt formulefouten.
Voordelen: Interactief, hanteert grote hoeveelheden gegevens elegant en kan meerdere statistieken tegelijk samenvatten.
Nadelen: Het resultaat wordt weergegeven in een Draaitabel-indeling in plaats van een eenvoudige lijst; af en toe is vernieuwing vereist als de brongegevens veranderen.
Tip: Dubbelklik op een samenvattende cel in de draaitabel om automatisch een nieuw werkblad te openen met de onderliggende gegevens van die groep—ideaal om details snel te controleren of afwijkingen te ontdekken.
Veelvoorkomende problemen:
- Als u het gemiddelde niet kunt verkrijgen, controleer dan of het waardeveld in de Waarde-instellingen is ingesteld op „Gemiddelde”.
- Controleer uw brongegevens op extra lege rijen of kolommen die layoutproblemen in de draaitabel kunnen veroorzaken.
Automatiseer de berekening van gegroepeerde gemiddelden met een VBA-macro
Voor gebruikers die regelmatig gemiddelden voor veel groepen moeten berekenen of automatisch een overzicht willen genereren, bespaart het schrijven van een VBA-macro aanzienlijk handmatig werk. VBA is vooral handig als u verwacht dat uw gegevensstructuur consistent blijft of wanneer u met één muisklik herhaaldelijk samenvattende rapporten wilt genereren.
Zorg ervoor dat u uw werkmap opslaat en macro’s inschakelt voordat u begint. Zo bent u klaar om direct aan de slag te gaan:
1. Klik op Ontwikkelaarshulpmiddelen > Visual Basic om de VBA-editor te openen. Klik in de editor op Invoegen > Module om een nieuwe codemodule te maken. Plak de onderstaande code in de module:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Nadat u de code hebt ingevoegd, sluit u de VBA-editor. Ga terug naar Excel en druk op Alt+F8, selecteer GroupAverageSummary in de lijst en klik op Uitvoeren. De macro vraagt u om de kolom met groepen (criteria) en de kolom met waarden (numeriek) te selecteren. Zodra u deze hebt geselecteerd, genereert de macro automatisch een nieuw werkblad met de naam „Group Average Summary”, waarin elke unieke groep en de bijbehorende gemiddelde waarden worden weergegeven.
Opmerkingen over parameters en werking:
- Zorg ervoor dat uw groep- en waardekolommen even lang zijn en alleen geldige gegevens bevatten – vermijd gedeeltelijke selecties.
- Deze macro kan, indien nodig, worden aangepast voor geavanceerdere groeperingen of extra samenvattingsstatistieken.
- Als uw werkblad al een werkblad met de naam „Overzicht groepsgemiddelde” bevat, maakt de macro een nieuw werkblad met de standaardnaam Werkbladnaam.
Probleemoplossing:
- Als u een foutmelding zoals ‘subscript buiten bereik’ of iets vergelijkbaars ontvangt, controleer dan of uw selectiebereiken correct zijn uitgelijnd en op hetzelfde werkblad staan.
- Zorg voor optimale resultaten door ervoor te zorgen dat de waardekolom uitsluitend numerieke gegevens bevat—tekst of lege cellen binnen het numerieke bereik worden door de macro overgeslagen.
Deze macro is ideaal voor batchverwerking, geautomatiseerde rapporten of situaties waarin u regelmatig nieuwe of bijgewerkte datasets moet samenvatten.
Demo: Bereken het gemiddelde in een kolom op basis van dezelfde waarde in een andere kolom met Kutools voor Excel
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins