Hoe voer je correct een optelling uit van getallen met eenheden in een bereik in Excel?
Bij het werken met Excel kun je een situatie tegenkomen waarin je getallen moet optellen die eenheden bevatten, zoals "5 kg", "12 lbs" of "10 m". Dit komt vaak voor bij het bijhouden van gewichten, lengtes of hoeveelheden - vooral in aankooplijsten, technische metingen, laboratoriumlogs en logistieke gegevens, waarbij elke waarde wordt aangevuld met een meeteenheid. Als je echter probeert om Excel's standaard SOM-formule te gebruiken op dergelijke gegevens, zal deze niet naar verwachting werken. Dit komt omdat Excel waarden die tekst (zoals eenheden) bevatten interpreteert als tekstreeksen en geen numerieke waarden, wat betekent dat SOM en andere basiswiskundige functies ze negeren of fouten retourneren. Om deze waarden nauwkeurig op te tellen, is een methode nodig om eerst het numerieke gedeelte te extraheren voordat de berekening wordt uitgevoerd. Deze tutorial leidt je door verschillende praktische en effectieve methoden, inclusief formules, ingebouwde functies en handige add-ins zoals Kutools voor Excel, om je te helpen getallen met eenheden correct op te tellen in verschillende realistische scenario's.
➤ Getallen met eenheden optellen met behulp van een formule
➤ Getallen met eenheden optellen met behulp van Kutools voor Excel
➤ Getallen optellen op basis van dezelfde eenheid met Kutools voor Excel
➤ Dubbele rijen combineren en overeenkomstige getallen optellen met Kutools voor Excel
➤ VBA-macro: Getallen uit tekstreeksen met eenheden extraheren en automatisch optellen
➤ Gebruik Power Query om getallen met eenheden te extraheren en op te tellen
Getallen met eenheden optellen met behulp van een formule
Wanneer al je waarden dezelfde eenheid delen en je een snelle, op formules gebaseerde oplossing nodig hebt, kunnen matrixformules in Excel helpen om het numerieke deel te extraheren en de som te berekenen. Deze methode is geschikt voor bereiken waarbij elke cel een getal bevat dat onmiddellijk wordt gevolgd door exact dezelfde eenheid, bijvoorbeeld "8 kg", "15 kg", enzovoort.
Selecteer een lege cel voor je resultaat en voer de volgende formule in:
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)Nadat je de formule hebt getypt, bevestig je deze door tegelijkertijd op Ctrl + Shift + Enter te drukken (op sommige systemen is dit vereist voor matrixformules), zodat Excel het herkent als een matrixbewerking. Je zult dan de som van de numerieke delen in je geselecteerde cel zien.
Tip: In de bovenstaande formule verwijst B2:B7 naar het databereik dat de waarden met eenheden bevat die je wilt optellen, en "kg" is de eenheid die in je data voorkomt. Zorg ervoor dat alle invoer in het bereik consistent is qua eenheid en dat er geen extra spaties of spellingfouten zijn, anders zal de formule de waarden niet correct extraheren. Als je eenheid of bereik anders is, pas dan beide dienovereenkomstig aan. Deze formule houdt geen rekening met scenario's waarin meerdere soorten eenheden in het bereik aanwezig zijn.
Getallen met eenheden optellen met Kutools voor Excel
Voor gebruikers die een gemakkelijkere, meer visuele aanpak prefereren of die formules willen vermijden te onthouden, biedt Kutools voor Excel een directe manier om numerieke waarden op te schonen. De functie Specifieke tekens verwijderen maakt het eenvoudig om de niet-numerieke eenheid te verwijderen, zodat je de getallen direct kunt optellen. Dit is ideaal voor situaties waarin je gegevens getallen en eenheden in één kolom bevat en je de getallen snel bruikbaar moet maken voor berekeningen.
1. Markeer de cellen die de getallen met eenheden bevatten. Ga naar het tabblad Kutools, klik op Tekst en selecteer Specifieke tekens verwijderen. Zie screenshot:
2. Selecteer alleen de optie Niet-numeriek in het dialoogvenster Specifieke tekens verwijderen. Deze instelling verwijdert alle tekens behalve cijfers. Zie screenshot:
3. Klik op OK of Toepassen. Nu blijven alleen de numerieke inhoud over in je geselecteerde cellen, die je nu kunt optellen met een eenvoudige formule. Selecteer een lege cel en voer onderstaande formule in om je resultaat te krijgen:
=SUM(B2:B7)De onderstaande afbeelding laat zien hoe de opgeschoonde kolom en de som eruit zien na deze procedure.
Kutools voor Excel - Boost Excel met meer dan 300 essentiële tools. Geniet van permanent gratis AI-functies! Nu verkrijgen
Opmerking: Kutools voert directe vervangingen uit in de geselecteerde cellen, dus je kunt kiezen om te werken met een kopie van je gegevens als je de originele tekst wilt behouden. Deze oplossing werkt het beste voor eenheidssoorten van één type. Controleer na gebruik van deze functie of alle opgeschoonde waarden numeriek zijn voordat je ze optelt.
Getallen met dezelfde eenheid optellen met Kutools voor Excel en de SOM-functie
Getallen optellen op basis van dezelfde eenheid met Kutools voor Excel
Als je tabel waarden bevat met verschillende eenheden - bijvoorbeeld gewichten in zowel "Kg" als "Pound" - en je wilt apart optellen voor elke eenheid (zoals het totaal aantal Kg en het totaal aantal Pound krijgen), biedt Kutools een efficiënte manier met zijn Formulehulp-functie. Dit is nuttig voor samenvattende tabellen, inventarisrapporten of bij het voorbereiden van gegevens voor verdere vergelijking.
Kutools voor Excel - Geleverd met meer dan 300 essentiële tools voor Excel. Geniet van permanent gratis AI-functies! Download nu!
1. Kies de cel waar je het opgetelde resultaat wilt laten verschijnen. Navigeer naar Kutools > Formulehulp > Statistisch > Som op basis van dezelfde tekst.
2. Stel je berekening in in het dialoogvenster Formulehulp: onder Argumentinvoer, kies je het volledige databereik als je Bereik en typ of selecteer je de specifieke eenheid (zoals "Kg" of "Pound") in het Tekstvak. Klik daarna op OK.
Tip: De standaardformule gebruikt absolute verwijzingen (bijv., $B$2:$B$7). Als je van plan bent om deze functie automatisch in te vullen voor meerdere eenheden of rijen, verander dan handmatig de verwijzingen naar relatief indien nodig.
Nu toont je resultaatcel de totaalwaarde voor de gekozen eenheid. Herhaal dit voor elke eenheid om alle groeptotalen te krijgen.
Opmerking: Deze methode werkt goed voor tabellen met duidelijk gelabelde eenheidstypen en helpt bij het automatiseren van het optellen van meerdere eenheden, waardoor de kans op fouten bij handmatige groepering wordt geminimaliseerd.
Dubbele rijen combineren en overeenkomstige getallen optellen met Kutools voor Excel
Sommige datasets bevatten items of entiteiten die meer dan eens worden vermeld met bijbehorende hoeveelheden en eenheden, zoals fruit met gewichtsinvoeringen in verschillende eenheden. Stel dat je waarden wilt optellen gegroepeerd per item en eenheid, dubbele rijen wilt samenvoegen en de totalen wilt consolideren. De functie Geavanceerd samenvoegen van rijen van Kutools voor Excel is hiervoor ontworpen, waardoor aggregatie snel en eenvoudig wordt.
1. Selecteer je tabel met dubbele items en gemengde eenheden, ga dan naar Kutools > Samenvoegen & Opsplitsen > Geavanceerd samenvoegen van rijen.
2. Selecteer in het dialoogvenster Rijen combineren op basis van kolom eerst de kolom die de eenheden bevat en markeer deze als Primaire sleutel. Dit laat Kutools je gegevens groeperen op basis van eenheid. Zie screenshot:
3. Markeer vervolgens de kolom met de numerieke waarden (zoals "Gewicht"), selecteer Berekenen en kies Optellen om de waarden voor elke groep bij elkaar op te tellen.
4. Klik op OK en je tabel zal samenvatten op basis van eenheid, waarbij de getallen zo nodig worden gecombineerd. Dit is vooral handig voor inventaris- en verkoopgegevens waarbij meerdere invoerregels moeten worden geaggregeerd op basis van overeenkomende eenheid.
Kutools voor Excel - Boost Excel met meer dan 300 essentiële tools. Geniet van permanent gratis AI-functies! Nu verkrijgen
Tip: Je kunt groeperen op meer dan één sleutel (bijv., item en eenheid) voor een diepgaandere consolidatie.
Dubbele waarden combineren en getallen optellen
VBA-macro: Getallen uit tekstreeksen met eenheden extraheren en automatisch optellen
Voor gevorderde gebruikers die volledige automatisering zoeken of werken met complexe of variërende patronen in eenheidsstrings, kan een VBA-macro extractie en optelling in één keer afhandelen. Deze methode is vooral nuttig wanneer je bereik getallen bevat die worden gevolgd door verschillende eenheden of inconsistente datastructuren, en wanneer ingebouwde Excel-formules te beperkend of omslachtig zijn om aan te passen voor elk geval.
1. Open de VBA-editor door te klikken op Ontwikkelaar > Visual Basic. In het venster Microsoft Visual Basic for Applications, klik op Invoegen > Module, en plak de volgende code in de module:
Sub SumNumbersWithUnits()
Dim cell As Range
Dim WorkRng As Range
Dim xTitleId As String
Dim NumSum As Double
Dim NumStr As String
Dim i As Long
Dim ch As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range with numbers and units", xTitleId, WorkRng.Address, Type:=8)
NumSum = 0
For Each cell In WorkRng
NumStr = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If ch Like "[0-9.]" Then
NumStr = NumStr & ch
ElseIf Len(NumStr) > 0 Then
Exit For
End If
Next i
If IsNumeric(NumStr) Then
NumSum = NumSum + Val(NumStr)
End If
Next cell
MsgBox "Sum of numbers is: " & NumSum, vbInformation, "Kutools for Excel"
End Sub2. Om de code uit te voeren, klik ergens in de macro en druk op F5, of klik op de 
Uitvoeren-knop boven het code-editorvenster. Er verschijnt een dialoogvenster waarin je wordt gevraagd om het bereik te selecteren dat getallen met eenheden bevat. Het script zal automatisch de eerste numerieke waarde uit elke tekstcel extraheren en alle resultaten optellen, en vervolgens de som weergeven in een pop-upvenster.
Tips en gebruiksaanwijzingen:
- Deze macro extraheert de numerieke tekens vanaf het begin van elke string totdat een niet-numeriek teken wordt gevonden, wat betekent dat het werkt voor de meeste formaten zoals "5kg", "12 lbs" of "20.5 m".
- Als de indeling van de getallen inconsistent is of ingebed binnen de string, kunnen verdere aanpassingen nodig zijn.
- Je kunt de VBA-code aanpassen om eenheden apart te verzamelen of te verwerken indien gewenst.
- Om verlies van originele gegevens te voorkomen, gebruik altijd macros op een kopie of back-up van je werkblad.
- Vereist dat macrofunctionaliteit is ingeschakeld in je Excel-instellingen.
Deze VBA-oplossing is veelzijdig bij het omgaan met variaties in je gegevens en bespaart aanzienlijk tijd wanneer toegepast op grote datasets.
Gebruik Power Query om getallen met eenheden te extraheren en op te tellen
Power Query, Excel's ingebouwde hulpmiddel voor gegevenstransformatie, is ideaal voor het verwerken van grotere of meer complexe datasets die getallen met eenheden bevatten.
Het werkt vooral goed wanneer gegevensstructuren inconsistent zijn of wanneer automatisering nodig is voor terugkerende taken.
1. Selecteer het bereik dat je gegevens bevat (inclusief getallen met eenheden), ga vervolgens naar het tabblad Gegevens en klik op Van Tabel/Bereik. Als je wordt gevraagd, controleer dan of je gegevens koppen heeft.
2. Selecteer in de Power Query Editor de kolom met je tekstwaarden. Gebruik de Kolom toevoegen tab > Tekst extraheren > Tekst voor scheidingsteken of Tekst tussen scheidingstekens.
Kies op basis van je gegevensindeling:
- Gebruik een spatie-scheidingsteken als de gegevens eruitzien als "12 kg"
- Gebruik een letter-gebaseerd scheidingsteken als het "12kg" is
3. Wijzig het gegevenstype van de nieuwe kolom naar Decimale getallen of Hele getallen indien nodig door met de rechtermuisknop op de kolomkop te klikken en Type wijzigen te selecteren.
4. (Optioneel) Als je wilt optellen op basis van eenheid, splits de eenheid verder af met Tekst na scheidingsteken extraheren, groepeer daarna met Groepeeren op (op het tabblad Start) en selecteer Optellen voor het nieuwe numerieke veld.
5. Klik op Sluiten & Laden om de verwerkte gegevens en sommen rechtstreeks in Excel te importeren als een nieuw werkblad of tabel.
Voordelen:
- Verwerkt complexe, inconsistente of bulkdata schoon en snel.
- Stappen kunnen worden opgeslagen en opnieuw toegepast op toekomstige of bijgewerkte gegevens - waardoor deze aanpak zeer herhaalbaar is.
- Je kunt transformatielogica aanpassen voorbij eenvoudige extractie, inclusief het verwerken van voorvoegsels, achtervoegsels, decimalen en groeperingen op basis van verschillende eenheden.
Beperkingen:
- Er is een leercurve voor gebruikers die nieuw zijn met Power Query.
- Best toegepast op gestructureerde gegevens of bij het werken met tabellen in plaats van directe ad-hoc celinvoer.
Problemen oplossen en suggesties:
- Voor de beste resultaten controleer je altijd geëxtraheerde of geconverteerde getallen op anomalieën, vooral als de indeling van eenheden of getallen sterk varieert.
- Maak een back-up van je brongegevens voordat je bulkacties uitvoert (ook met Kutools en VBA).
- Controleer bij het gebruik van formules altijd op verborgen spaties of typfouten in gegevens en eenheden.
- Als formules falen of fouten retourneren (zoals
#WAARDE!), controleer dan of alle gerefereerde cellen de verwachte indelingen bevatten en pas het bereik of eenheidscriteria dienovereenkomstig aan. - Zorg ervoor dat macros zijn ingeschakeld bij gebruik van VBA en sla op voordat je de macro uitvoert voor een gemakkelijker herstel.
- Transformatiestappen in Power Query kunnen na een preview worden aangepast via het deelvenster Toegepaste stappen als je gegevens zijn veranderd.
Gerelateerde artikelen:
- Hoe combineer je meerdere cellen met voorloopnullen in een cel in Excel?
- Hoe combineer je bereiken van verschillende werkbladen/werkboeken in één?
- Hoe voeg je snel rijen samen op basis van één kolomwaarde en doe je vervolgens enkele berekeningen in Excel?
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