Hoe het laagste cijfer te laten vallen en het gemiddelde of de totale waarde in Excel te berekenen?
Wanneer je werkt met een lijst van cijfers of scores in Excel, moet je mogelijk het eindcijfer van een student berekenen door hun laagste score, of zelfs de laagste n scores, te negeren voordat je het gemiddelde of de som van de overige waarden berekent. Dit is een veelvoorkomende vereiste in onderwijsomgevingen, waar studenten hun slechtste prestaties mogen laten vallen om afwijkingen te corrigeren of om eerlijkheid te waarborgen. Deze handeling handmatig uitvoeren kan tijdrovend zijn, vooral bij grote datasets of frequent veranderende berekeningen. Gelukkig biedt Excel verschillende flexibele manieren om dit te bereiken, variërend van eenvoudige formules tot automatisering met VBA voor batchbewerkingen.
Laat het laagste cijfer vallen en bereken het gemiddelde of de som met formules
Laat het laagste cijfer vallen en bereken het gemiddelde of de som met formules
Als je het laagste of laagste n waarden wilt uitsluiten van een rij of lijst met gegevens en vervolgens berekeningen wilt uitvoeren, zoals het gemiddelde of de som van de overige getallen, bieden de ingebouwde formules van Excel een praktische oplossing. Deze formule-oplossingen zijn vooral handig als je een matig aantal rijen moet verwerken of liever een formule-gebaseerde aanpak gebruikt voor transparantie en gemakkelijke aanpassingen.
Hieronder vind je aparte uitleg voor zowel de som- als de gemiddelde methoden, inclusief gedetailleerde instructies en praktische tips voor flexibele toepassing.
Sommeer de getallen maar laat het laagste of laagste N getallen vallen:
Om de som voor elke rij of lijst te berekenen terwijl je het laagste getal negeert, gebruik je de volgende methode:
1. Selecteer een lege cel waarin je het somresultaat voor de eerste rij wilt weergeven (bijvoorbeeld cel I2 als je gegevens zich in B2:H2 bevinden), en voer vervolgens de volgende formule in:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Druk op Enter om te bevestigen, en sleep vervolgens de vulgreep naar beneden om deze formule indien nodig toe te passen op andere rijen. Dit zal alle getallen in elke rij optellen, met uitzondering van het laagste getal.
Hier is een schermafbeelding ter referentie:
Opmerkingen en tips:
- Om de laagste twee, drie of meer waarden uit te sluiten, kun je de formule uitbreiden door extra SMALL-resultaten af te trekken. Bijvoorbeeld:
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- In deze formules is B2:H2 het bereik dat je wilt optellen, en de nummers 1, 2, 3, enz., specificeren de n kleinste getallen die moeten worden uitgesloten. Pas n aan op basis van hoeveel van de laagste cijfers je wilt laten vallen.
- Wees voorzichtig om n niet groter dan of gelijk aan het totale aantal waarden in te stellen; anders krijg je fouten of ongewenste resultaten.
- Deze formules werken onafhankelijk voor rijen. Als je gegevenskolommen in plaats van rijen beslaan, pas dan de bereiken dienovereenkomstig aan.
- Als je dataset meerdere keren het laagste getal bevat, zal SMALL(B2:H2,1) slechts één exemplaar per verwijzing laten vallen. Om meerdere exemplaren te laten vallen, herhaal je de SMALL-term met oplopende k-getallen zoals hierboven weergegeven.
Bereken het gemiddelde maar laat het laagste of laagste N getallen vallen:
Om het gemiddelde te berekenen terwijl je het laagste of laagste n waarden negeert, kun je de onderstaande formules gebruiken. Deze berekening is vooral nuttig in cijferreeksen waarbij uitschieters in lage prestaties niet in het gemiddelde moeten worden meegenomen.
1. Selecteer een cel voor het gemiddelde resultaat (bijvoorbeeld J2 als je scores zich in B2:H2 bevinden), en voer de formule in:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Nadat je op Enter hebt gedrukt, sleep je de formule indien nodig omlaag om extra rijen te middelen, waarbij telkens de laagste score in het bereik voor die rij wordt weggelaten.
Opmerkingen en belangrijke richtlijnen:
- Om het gemiddelde te berekenen terwijl je meer dan één laagste score laat vallen, breid je de formule uit door extra SMALL-termen af te trekken en de deler dienovereenkomstig te verminderen:
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- Opnieuw, B2:H2 is het bereik om te middelen, en n vertegenwoordigt hoeveel van de laagste waarden niet in de berekening worden meegenomen.
- Als je probeert meer getallen af te trekken dan er in het bereik bestaan, retourneren de formules een #GETAL!-fout, wat aangeeft dat er onvoldoende waarden zijn voor het berekenen van het gemiddelde. Zorg er altijd voor dat n kleiner is dan het aantal getallen.
- Het wordt aanbevolen om dubbel te controleren dat de laagste waarden niet cruciaal of vereist zijn voor je berekening voordat je ze uitsluit, omdat dit de eindresultaten kan beïnvloeden.
- Overweeg voor extreem grote datasets of dynamisch laten vallen van de laagste n waarden een geautomatiseerde of array-oplossing.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
VBA-code - Laat het laagste of laagste n cijfers vallen en bereken de som of het gemiddelde automatisch
Voor situaties waarbij grote of vaak veranderende datasets betrokken zijn, of wanneer je het automatisch laten vallen van de laagste n cijfers en het berekenen van sommen of gemiddelden over veel rijen moet automatiseren, kan het gebruik van VBA het herhalende werk aanzienlijk vereenvoudigen. Met een VBA-macro kun je het gegevensbereik en het aantal laagste cijfers dat moet worden uitgesloten specificeren, en zal de code alle geselecteerde rijen efficiënt in één stap verwerken.
Deze automatische aanpak is vooral handig voor leraren die spreadsheets voor meerdere klassen beheren, of voor iedereen die handmatige formule-ingaven en mogelijke fouten wil minimaliseren. De onderstaande oplossing stelt je in staat om zowel het uitsluitingsnummer als de doelfunctie (som of gemiddelde) eenvoudig aan te passen.
Voordat je begint, sla je je werkmap op, omdat het uitvoeren van macro's niet direct kan worden ongedaan gemaakt.
1. Klik op Ontwikkelaar > Visual Basic. In het Microsoft Visual Basic for Applications-venster klik je op Invoegen > Module, en voer je vervolgens de volgende code in:
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. Na het toevoegen van de code, klik je op de 
knop of druk je op F5 om uit te voeren.
3. Volg de prompts die verschijnen:
- Selecteer het scorebereik dat je wilt verwerken (zorg ervoor dat de scores van elke student in een rij staan).
- Kies de linkerbovenhoekcel van het uitvoerbereik (de uitvoer zal omlaag worden gevuld op basis van het aantal rijen).
- Voer het aantal laagste scores in dat je wilt laten vallen (bijvoorbeeld 1 om alleen het laagste cijfer in elke rij uit te sluiten).
- Typ SOM om het totaal te krijgen (met uitzondering van de uitgesloten cijfers) of GEM om het herberekende gemiddelde te krijgen (met uitzondering van de uitgesloten cijfers).
De macro verwerkt elke rij van het gespecificeerde scoregebied en plaatst de som of het gemiddelde (naar keuze) in het uitvoerbereik. Als alle scores in een rij worden uitgesloten, wordt het resultaat gemarkeerd als NVT om fouten te voorkomen.
- Zorg ervoor dat het invoerbereik overeenkomt met je datastructuur (één studentsscore per rij).
- Niet-numerieke cellen (bijvoorbeeld blanco of tekst) worden standaard genegeerd.
- Deze VBA-code versnelt repetitieve cijferberekeningen voor hele klassen aanzienlijk en ondersteunt flexibele aanpassing van het aantal cijfers dat moet worden uitgesloten.
- Als je dergelijke bewerkingen vaak uitvoert, kun je deze macro aan een knop op je werkblad toewijzen voor nog snellere toegang.
Als je problemen tegenkomt zoals onjuiste uitkomsten of fouten, controleer dan of de bereiken nauwkeurig zijn opgegeven en dat "n" niet groter dan of gelijk aan het totale aantal beschikbare cijfers voor elke rij is.
Voor vergelijkbare automatiseringsvereisten, zoals het laten vallen van zowel de hoogste als de laagste scores of het verwerken van kolommen in plaats van rijen, kunnen kleine aanpassingen worden gemaakt aan de logica van de VBA-code.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen