Hoe schrap ik het laagste cijfer en bereken ik het gemiddelde of totaal van de overige waarden in Excel?
Wanneer u werkt met een lijst cijfers of scores in Excel, wilt u wellicht het eindcijfer van een student berekenen door het laagste cijfer – of zelfs de laagste *n* cijfers – uit te sluiten voordat u het gemiddelde of de som van de overige waarden bepaalt. Dit is een veelvoorkomende vereiste in onderwijsomgevingen, waar studenten hun slechtste resultaten mogen schrappen om rekening te houden met uitzonderlijke omstandigheden of om eerlijkheid te waarborgen. Deze taak handmatig uitvoeren kan tijdrovend zijn, zeker bij grote datasets of wanneer berekeningen regelmatig moeten worden aangepast. Gelukkig biedt Excel diverse flexibele oplossingen – van eenvoudige formules tot geautomatiseerde VBA-scripts voor batchverwerking – om dit efficiënt aan te pakken.
Schrap het laagste cijfer en verkrijg het gemiddelde of de som met formules
Schrap het laagste cijfer en verkrijg het gemiddelde of de som met formules
Als u de laagste of laagste n waarden uit een rij of lijst met gegevens wilt uitsluiten en vervolgens berekeningen wilt uitvoeren – zoals het gemiddelde of de som van de overgebleven getallen – bieden de ingebouwde Excel-formules een slimme en efficiënte oplossing. Deze formule-aanpak is vooral handig bij een beperkt aantal rijen of wanneer u liever kiest voor transparantie en eenvoudige aanpassingen via formules.
Hieronder vindt u aparte uitleg over zowel de som- als de gemiddelde-methode, inclusief gedetailleerde instructies en praktische tips voor een flexibele toepassing.
Tel de getallen op, maar schrap het laagste of de laagste N getallen:
Gebruik de volgende methode om de som per rij of lijst te berekenen terwijl het laagste getal wordt weggelaten:
1. Selecteer een lege cel waar het somresultaat van de eerste rij moet verschijnen (bijvoorbeeld cel I2 als uw gegevens zich bevinden in B2:H2) en voer de volgende formule in:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Druk op Enter om te bevestigen en sleep vervolgens de vulgreep omlaag om deze formule op andere rijen toe te passen. Zo worden alle getallen in elke rij opgeteld, exclusief de laagste waarde.
Hier is een schermafbeelding ter referentie:
Opmerkingen en tips:
- Om de twee, drie of meer laagste waarden uit te sluiten, breidt u de formule uit 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 u wilt optellen, en geven de getallen 1, 2, 3, enzovoort aan welke n kleinste waarden moeten worden uitgesloten. Pas n aan op basis van hoeveel laagste waarden u wilt schrappen.
- Zorg ervoor dat u n niet instelt op groter dan of gelijk aan het totale aantal waarden; anders krijgt u fouten of ongewenste resultaten.
- Deze formules werken per rij onafhankelijk. Als uw gegevens zich over kolommen in plaats van rijen uitstrekken, pas dan de bereiken dienovereenkomstig aan.
- Als uw dataset duplicaten bevat van het laagste getal, verwijdert SMALL(B2:H2;1) slechts één exemplaar per verwijzing. Om meerdere exemplaren te verwijderen, herhaalt u de SMALL-functie met verhoogde k-getallen, zoals hierboven weergegeven.
Bereken het gemiddelde, maar schrap het laagste of de laagste N getallen:
Gebruik de onderstaande formules om het gemiddelde te berekenen terwijl u de laagste of de laagste n waarden buiten beschouwing laat. Deze berekening is bijzonder waardevol in beoordelingsschema’s waarbij uitzonderlijk lage prestaties niet meetellen in het gemiddelde.
1. Selecteer een cel voor het gemiddelde resultaat (bijvoorbeeld J2 als uw scores in B2:H2 staan) en voer de volgende formule in:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Na het indrukken van Enter sleept u de formule omlaag om extra rijen te middelen, waarbij telkens het laagste cijfer in het bereik van die rij wordt weggelaten.
Opmerkingen en belangrijke richtlijnen:
- Om een gemiddelde te berekenen terwijl u meer dan één laagste score schrapt, breidt u de formule uit door extra SMALL-termen af te trekken en de deler dienovereenkomstig te verlagen:
=(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 geldt: B2:H2 is het bereik waarvan u het gemiddelde wilt berekenen, en n geeft aan hoeveel laagste waarden niet in de berekening worden meegenomen.
- Als u meer getallen probeert af te trekken dan er in het bereik aanwezig zijn, geven de formules een #GETAL!-foutmelding, wat aangeeft dat er onvoldoende waarden beschikbaar zijn om het gemiddelde te berekenen. Zorg er daarom altijd voor dat n kleiner is dan het aantal getallen.
- Controleer altijd zorgvuldig of de laagste waarden niet essentieel of vereist zijn voor uw berekening voordat u ze uitsluit, want dit kan uw eindresultaten beïnvloeden.
- Overweeg voor zeer grote datasets een geautomatiseerde of matrixoplossing voor dynamisch schrappen van de laagste n waarden.
Ontgrendel de magie van Excel met KUTOOLS AI
- Slimme uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak grafieken — allemaal met eenvoudige opdrachten.
- aangepaste formules: Genereer op maat gemaakte formules om uw workflows te stroomlijnen.
- VBA-programmeren: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules moeiteloos.
- Tekstvertaling: Doorbreek taalbarrières in uw spreadsheets.
VBA-code – Schrap het laagste of de laagste n cijfers en bereken automatisch de som of het gemiddelde
Voor situaties met grote of vaak wijzigende datasets – of wanneer u het verwijderen van de laagste *n* cijfers en het berekenen van sommen of gemiddelden over veel rijen wilt automatiseren – vereenvoudigt VBA repetitief werk aanzienlijk. Met een VBA-macro geeft u eenvoudig het gegevensbereik en het aantal laagste cijfers dat moet worden uitgesloten op, waarna de code alle geselecteerde rijen efficiënt in één keer verwerkt.
Deze automatische aanpak is vooral handig voor docenten die bladen beheren voor meerdere klassen, of voor iedereen die handmatige formule-invoer en mogelijke fouten wil vermijden. Met de onderstaande oplossing pas je eenvoudig zowel het uitsluitingsaantal als de doelfunctie (som of gemiddelde) aan.
Sla uw werkmap op voordat u begint, want het uitvoeren van macro’s kunt u niet direct ongedaan maken.
1. Klik op Ontwikkelaar > Visual Basic. Klik in het venster Microsoft Visual Basic for Applications op Invoegen > Module en voer 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. Nadat u de code hebt toegevoegd, klikt u op de knop 
of drukt u op F5 om uit te voeren.
3. Volg de weergegeven instructies:
- Selecteer het cijferbereik dat u wilt verwerken (zorg ervoor dat de cijfers van elke student in één rij staan).
- Kies de bovenste cel aan de linkerkant van het plaatsingsgebied (de uitvoer wordt naar beneden ingevuld op basis van het aantal rijen).
- Voer het aantal laagste cijfers in dat u wilt schrappen (bijvoorbeeld)1 om alleen het laagste cijfer in elke rij uit te sluiten).
- Typ SOM om het totaal te verkrijgen (exclusief geschrapt cijfers) of GEMIDDELDE om het herberekende gemiddelde te verkrijgen (exclusief geschrapt cijfers).
De macro verwerkt elke rij uit het opgegeven cijfergebied en plaatst de som of het gemiddelde (naar keuze) in uw Plaatsingsgebied-lijst. Als alle cijfers in een rij worden geschrapt, wordt het resultaat aangeduid als N/Bom fouten te voorkomen.
- Zorg ervoor dat het invoerbereik perfect aansluit bij uw gegevensstructuur: één rij per student.
- Niet-numerieke cellen, zoals lege cellen of tekst, worden standaard genegeerd.
- Deze VBA-code versnelt repetitieve cijferberekeningen voor hele klassen aanzienlijk en ondersteunt flexibele aanpassing van het aantal te schrappen cijfers.
- Als u dit soort bewerkingen vaak uitvoert, kunt u de macro aan een knop op uw werkblad koppelen voor nog snellere toegang.
Als u problemen ondervindt zoals onjuiste uitvoer of foutmeldingen, controleer dan of de bereiken correct zijn opgegeven en of „n” niet Groter dan of gelijk aan het totale aantal beschikbare cijfers per rij overschrijdt.
Voor vergelijkbare automatiseringsbehoeften, zoals het schrappen van zowel de hoogste als laagste scores of het verwerken van kolommen in plaats van rijen, kunnen kleine aanpassingen in de VBA-code-logica worden aangebracht.
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