Hoe berekent u het gemiddelde van niet-aangrenzende cellen in Excel, exclusief nullen?
Bij het werken met Excel-gegevens moet u vaak het gemiddelde van een reeks getallen berekenen, terwijl de relevante cellen niet aaneengesloten zijn en u nulwaarden wilt uitsluiten—die immers ontbrekende gegevens of opzettelijk genulde waarden kunnen vertegenwoordigen. Denk bijvoorbeeld aan een verkoop- of voorraadlijst: sommige artikelen hebben mogelijk geen voorraad of geen verkopen op bepaalde data. In dergelijke gevallen geeft een eenvoudig gemiddelde geen accuraat beeld van de onderliggende trend, omdat nullen het resultaat kunnen vertekenen. Door ze uit te sluiten, richt u uw analyse uitsluitend op actieve datapunten.
Stel dat u een fruittabel heeft zoals in de onderstaande afbeelding en u het gemiddelde van alle cellen in de kolom *Aantal* wilt berekenen, exclusief nullen. De volgende methoden leiden u stapsgewijs door meerdere praktische benaderingen om dit in Excel te realiseren, zodat u flexibiliteit behoudt — ongeacht of uw gegevens verspreid zijn over niet-aangrenzende cellen, over meerdere rijen of kolommen zijn verdeeld, of u liever ingebouwde Excel-functionaliteiten dan eenvoudige formules gebruikt.
- Gemiddelde van niet-aangrenzende cellen, exclusief nullen, met formule
- Gemiddelde van niet-aangrenzende cellen, exclusief nullen, met een VBA-macro
- Gemiddelde van niet-aangrenzende cellen, exclusief nullen, met ingebouwde Excel-functie
Gemiddelde van niet-aangrenzende cellen, exclusief nullen, met formule
Deze methode maakt gebruik van een matrixformule om snel het gemiddelde te berekenen van niet-aangrenzende cellen—bijvoorbeeld elke tweede cel in een rij of kolom—waarbij eventuele nullen in de geselecteerde cellen automatisch worden uitgesloten. Ze is ideaal wanneer uw doelcellen een vast intervalpatroon volgen, en bijzonder efficiënt bij periodieke gegevens, zoals waarden om de dag of regelmatig verspreide gegevens die niet direct naast elkaar staan.
Beperkingen: deze aanpak werkt het beste wanneer niet-aangrenzende cellen met een constant interval zijn gerangschikt. Voor willekeurige, handmatige selecties verwijzen we naar de op formules gebaseerde methode hieronder.
1. Selecteer een lege cel voor het resultaat en voer de matrixformule in:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Druk vervolgens op Ctrl + Shift + Enter(voor oudere Excel-versies; in Excel 365 of 2019 is het indrukken van)Enter voldoende, aangezien deze versies dynamische matrices ondersteunen).
Opmerking: In de formule geven C2 en G2 de eerste en laatste cel aan van uw doelbereik. Het getal „2” bepaalt het kolominterval; pas deze waarde aan als uw niet-aangrenzende cellen door een ander aantal kolommen worden gescheiden. Pas het bereik (C2:G2) en het interval (2) aan op basis van uw gegevensindeling.
Tips:
- Zorg ervoor dat het geselecteerde bereik alle niet-aangrenzende cellen bevat die u wilt middelen.
- Als uw gegevensselectie niet op regelmatige intervallen is gebaseerd, gebruikt u de volgende formulegebaseerde oplossing voor willekeurige cellen.
- Deze formule negeert standaard lege cellen en nulwaarden, zodat u het werkelijke gemiddelde van betekenisvolle datapunten krijgt.
- Controleer of uw celverwijzingen kloppen en of u de juiste toetscombinatie hebt gebruikt voor matrixformules in oudere Excel-versies, zodat fouten worden voorkomen.
Gemiddelde van niet-aangrenzende cellen, exclusief nullen, met een VBA-macro
Als u snel het gemiddelde wilt berekenen van een groep niet-aangrenzende cellen — waarbij nullen worden overgeslagen en uw selectie geen vast patroon volgt (bijvoorbeeld bij het handmatig kiezen van specifieke cellen verspreid over verschillende rijen en kolommen) — dan biedt een VBA-macro een flexibele en krachtige oplossing. De code doorloopt al uw geselecteerde cellen en berekent het gemiddelde, met uitsluiting van cellen die een nul bevatten.
Deze methode is uitstekend geschikt voor:
- Snel verwerken van grote of onregelmatige gegevensselecties.
- Sla uw aangepaste logica op voor herhaald gebruik met vergelijkbare datasets.
- Omzeil de beperkingen van traditionele werkbladformules bij complexe selecties.
1. Klik op Ontwikkelaar > Visual Basic; klik in het venster dat zich opent op Invoegen > Module en plak de volgende code in de module:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Klik op de knop 
Uitvoeren of druk op F5 in de VBA-editor. Er verschijnt een dialoogvenster waarin u de cellen moet selecteren die u wilt middelen. Gebruik uw muis en de Ctrl-toets om niet-aangrenzende cellen te selecteren. Klik op OK en de macro toont het resultaat, exclusief eventuele nullen.
Gemiddelde van niet-aangrenzende cellen, exclusief nullen, met ingebouwde Excel-functie
Excel biedt ook ingebouwde methoden om deze taak aan te pakken met minimaal tot geen formulewerk—ideaal voor snelle, eenmalige berekeningen of visuele inspecties.
Gebruik de Statusbalk:
- Houd Ctrl ingedrukt en selecteer met uw muis handmatig elke niet-aangrenzende cel die u wilt middelen, waarbij u cellen met nulwaarden overslaat.
- Zodra u een selectie hebt gemaakt, kijkt u naar de Excel-statusbalk rechtsonder in uw venster—de gemiddelde waarde wordt automatisch weergegeven zodra er meer dan één cel is geselecteerd.
- Deze methode is ideaal voor snelle visuele controles en kleine datasets, maar levert helaas geen resultaat op in een werkbladcel.
Bij het kiezen van de juiste methode moet u rekening houden met de omvang van uw gegevens, hoe verspreid uw geselecteerde cellen zijn en of u een herhaalbare formule-uitvoer nodig hebt of alleen een snelle visuele controle. Als u fouten tegenkomt (zoals)#DEL/0!), controleer dan of er minstens één niet-nulwaarde in uw selectie aanwezig is. Sla bij het gebruik van VBA altijd uw werkmap op voordat u nieuwe macro’s uitvoert, om onbedoeld gegevensverlies te voorkomen.
Selecteer snel rijen of kolommen met interval (niet-aangrenzend) in Beperkt bereik in Excel:
De Kutools voor Excel's Selecteer Rijen/Kolommen met Tussenpozen-functie helpt u eenvoudig intervalrijen of -kolommen te selecteren binnen een beperkt bereik in Excel, zoals in de onderstaande afbeelding is weergegeven. Dit is handig vóór het gebruik van formules of macro’s, omdat u hiermee precies de cellen kunt selecteren die u wilt verwerken.
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
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