Hoe voer je een optelsom uit zonder of met uitsluiting van subtotaal in Excel?
Wanneer je in Excel met datasets werkt, is het gebruikelijk om je gegevens te organiseren en analyseren door subtotaal voor verschillende groepen of categorieën te berekenen. Echter, nadat je deze subtotaal hebt toegevoegd, wil je mogelijk een eindtotaal berekenen dat deze subtotalen uitsluit, om dubbel tellen in de uiteindelijke som te voorkomen. Het probleem is dat bij direct optellen van alle getallen de subtotalen worden meegenomen in het totaal, wat resulteert in een som die hoger uitkomt dan bedoeld. Om dit te voorkomen moet je een manier vinden om de originele gegevens op te tellen terwijl je de subtotalen rijen uitsluit. In dit artikel presenteren we verschillende praktische methoden om dit probleem op te lossen, zodat je nauwkeurige resultaten kunt berekenen in verschillende scenario's.
Waarden optellen zonder subtotalen met de Som-functie in Excel
Een veelgebruikte aanpak voor het berekenen van groepssubtotalen is het invoegen van de SOM-functie binnen of onder elke relevante groep. Maar wanneer je de eindtotaal voor de hele lijst wilt berekenen terwijl je deze groepssubtotaalrijen negeert, is vaak een extra stap vereist. Hier is een methode die je kunt gebruiken om dit te bereiken:
1. Gebruik de SOM-functie binnen elke groep om de respectieve subtotalen te genereren, zoals te zien in deze schermafbeelding:
2. Voer vervolgens de volgende formule in een lege cel in waar je het resultaat wilt laten verschijnen om de eindtotaal te vinden zonder de subtotalenrijen mee te nemen:
=SOM(B2:B21)/2
Druk op Enter om de juiste totaal te krijgen. Deze formule werkt omdat ervan wordt uitgegaan dat je subtotalen hebt ingevoegd met de SOM-functie direct na relevante groepen, waardoor de som van de ruwe gegevens effectief wordt verdubbeld. Door te delen door 2 wordt de gedupliceerde totaal verwijderd. Houd er rekening mee dat deze methode het meest geschikt is wanneer de dataset is gestructureerd zodat de som van de oorspronkelijke waarden en subtotalen samen precies tweemaal de som van de originele gegevens vormen.
Als je gegevensstructuur complexer is, of het aantal subtotale rijen varieert, kunnen alternatieve methoden hieronder een nauwkeuriger en flexibeler oplossing bieden.
Waarde optellen zonder subtotalen met de Subtotaal-functie in Excel
De SUBTOTAAL-functie in Excel biedt een ingebouwde manier om berekeningen uit te voeren op gefilterde of zichtbare rijen. Dit maakt het vooral nuttig bij het werken met lijsten die subtotalen bevatten, omdat SUBTOTAAL-formules kunnen worden ingesteld om andere SUBTOTAAL-resultaten binnen het bereik te negeren, waardoor dubbel tellen van waarden wordt voorkomen.
Om de SUBTOTAAL-functie in deze context te gebruiken, volg je deze stappen:
1. Voer de SUBTOTAAL-functie voor elke groep als volgt in:
=SUBTOTAAL(9,B2:B10)
Dit past de SOM-operatie (functienummer 9) toe op het opgegeven bereik en wordt vaak gebruikt als onderdeel van Excel’s ingebouwde Data > Subtotaal functie.
2. Voer de volgende formule in je gekozen cel in voor de eindtotaal die interne subtotalen uitsluit:
=SUBTOTAAL(9,B2:B21)
Druk op Enter om automatisch de datawaarden op te tellen, waarbij geneste SUBTOTAAL-functies binnen het bereik worden genegeerd. Deze aanpak is geschikt voor lijsten die de SUBTOTAAL-functie voor groepssamenvattingen gebruiken en elimineert het risico van meerdere keren subtotalen optellen.
Houd er rekening mee dat de SUBTOTAAL-functie het beste werkt wanneer subtotaalcellen zijn gegenereerd met behulp van SUBTOTAAL in plaats van SOM. Bovendien kan SUBTOTAAL worden ingesteld om alleen zichtbare (ongemarkeerde) gegevens op te tellen als je rijen filtert of verbergt, wat handig is in dynamische rapportagesituaties.
VBA-code - Waarden optellen exclusief subtotalen rijen
Als je gegevensstructuur complex is of je een geautomatiseerde methode wilt om alleen de brongegevensrijen op te tellen (subtotalen uitsluiten), kun je een eenvoudige VBA-macro gebruiken. Deze aanpak stelt je in staat programmatisch te identificeren en op te tellen alleen die rijen die voldoen aan je criteria, zoals op basis van specifieke opmaak, de aanwezigheid van formules, of andere onderscheidende kenmerken.
Bijvoorbeeld, als je subtotalenrijen zijn berekend met formules (zoals SOM of SUBTOTAAL) en je brongegevensrijen alleen statische waarden bevatten, kun je de VBA-code instellen om alleen die cellen in een specifieke kolom op te tellen die geen formules bevatten. Zo doe je dat:
1. Klik Ontwikkelaarsgereedschappen > Visual Basic om het Microsoft Visual Basic for Applications-venster te openen.
2. Klik in het nieuwe venster op Invoegen > Module en plak de volgende code in de module:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Klik op de 
Uitvoeren knop om de code uit te voeren. Er verschijnt een prompt waarin je wordt gevraagd het bereik met je gegevens te selecteren. De macro zal vervolgens alleen de cellen in je selectie optellen die geen formules bevatten, waarbij typische subtotalenrijen worden overgeslagen.
Filteren - Gebruik filter en SUBTOTAAL om alleen zichtbare (niet-subtotaal) rijen op te tellen
Als je dataset het mogelijk maakt om subtotalenrijen te filteren (bijvoorbeeld als er een label, trefwoord of patroon is om ze te identificeren), kun je Excel’s ingebouwde Filterfunctie samen met de SUBTOTAAL-functie gebruiken om alleen de zichtbare (niet-gefilterde) gegevensrijen op te tellen. Deze techniek is praktisch voor gevallen waarin subtotalen een consistente indicator of label hebben.
1. Klik ergens in je dataset, ga vervolgens naar het Gegevens tabblad en klik op Filter om filters voor je kolommen in te schakelen.
2. Klik op de vervolgkeuzelijst in je hulpcolumn, deselecteer "Subtotaal" om subtotalenrijen te verbergen en toon alleen de originele gegevens.
3. Voer in een aparte cel de volgende formule in om alleen zichtbare (gefilterde) rijen op te tellen.
=SUBTOTAL(9,B2:B21)De SUBTOTAAL-functie (met functienummer 9 voor SOM) zal verborgen rijen negeren en retourneert de totaal van alleen die rijen die momenteel zichtbaar zijn (dat wil zeggen, de niet-subtotaalrijen die je hebt behouden na filteren). Deze oplossing is snel en dynamisch - als je filters opnieuw toepast of je dataset uitbreidt, zal de formule dienovereenkomstig worden bijgewerkt.
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