Hoe nummers in Excel af te korten?
Bij het werken met grote datasets in Excel is het gebruikelijk om grote getallen tegen te komen die je werkblad kunnen overladen en de gegevens moeilijker leesbaar of analyseerbaar maken. Bijvoorbeeld, het presenteren van waarden zoals 1.234.567 of 12.000.000 kan je kijkers overweldigen, vooral in dashboards of samenvattingsrapporten. In dergelijke gevallen helpt het afkorten van getallen (bijv. 1234567 omzetten naar 1,23M of 12000 naar 12K) om de helderheid te verbeteren en een compacte indeling te behouden. Deze handleiding biedt verschillende praktische oplossingen voor het afkorten van getallen in Excel, inclusief ingebouwde opmaak, formules en VBA-macros. Elke optie is geschikt voor verschillende scenario's, afhankelijk van je voorkeuren en workflowvereisten.
 |  |  |
Afkorting van getallen | Afkorten met Excel-formule | Getallen afkorten met VBA-macro
Getal afkorten
Een eenvoudige manier om getallen in Excel af te korten, is door de getalindeling aan te passen via het dialoogvenster Celopmaak. Deze methode verandert alleen de weergave van de getallen, niet hun onderliggende waarden, wat betekent dat berekeningen met deze cellen nauwkeurig blijven. Het is vooral handig voor snelle rapporten of wanneer je wilt dat de getallen automatisch worden vernieuwd zonder extra kolommen toe te voegen. Houd er echter rekening mee dat dit geen tekstresultaten zal opleveren of gemengde eenheden (zoals M en K in dezelfde kolom) met complexe logica toestaat — formules of macro's zijn daar beter voor geschikt.
1. Selecteer het bereik van getallen dat je wilt afkorten. Klik vervolgens met de rechtermuisknop ergens in het geselecteerde bereik en kies Celopmaak in het contextmenu.
2. Ga in het Celopmaak dialoogvenster naar het tabblad Getal, selecteer Aangepast in de lijst aan de linkerkant en voer in het veld Type in: [>999999]#,,"M";#,"K" Deze aangepaste indeling vertelt Excel om getallen groter dan 999.999 weer te geven als miljoenen (met een “M”) en alle andere getallen als duizenden (met een “K”). Zie screenshot:
3. Klik op OK om de opmaak toe te passen. De getallen in je geselecteerde bereik worden nu in een verkorte vorm weergegeven, wat het scannen en interpreteren van grote datasets gemakkelijker maakt.
Tip: Als je alleen grote getallen als duizenden (“K”) wilt weergeven, gebruik dan #,"K" in het Type-vak. Voor alleen miljoenen, gebruik #,,"M". Voor miljarden of triljoenen, gebruik respectievelijk #,,,"B" of #,,,,"T". Om miljoenen en miljarden samen met de juiste eenheid weer te geven, gebruik [>999999999.999]#,,,"B";[>999999.999]#,,"M";#,##0 _M. Let op, getalopmaak is alleen voor weergave en verandert de waarde niet voor berekeningen. Als je meer aangepaste logica nodig hebt, zoals afronden, decimalen tonen of negatieven afhandelen, overweeg dan een formulebenadering of een VBA-macro.
Waarschuwingen: Als je gegevens waarden kleiner dan 1.000 bevat, worden ze weergegeven als het originele getal zonder afkorting. Deze methode kan M en K niet combineren binnen dezelfde celcontext, behalve door uitgebreide aangepaste opmaak, wat beperkingen heeft. Wees je ervan bewust dat opgemaakte getallen nog steeds de originele waarde in onderliggende formules gebruiken, wat verwarring kan veroorzaken bij het kopiëren of exporteren van gegevens.
Afkorten van getallen met Excel-formules (zal worden omgezet in tekst)
Als je verkorte resultaten in een nieuwe kolom wilt weergeven en de logica wilt aanpassen, zoals decimalen weergeven (1,25M), gemengde eenheden (K, M, B, T) afhandelen of de uitkomst als tekst controleren, kun je Excel-functies gebruiken om dynamische afkortingen te genereren. Dit is ook ideaal wanneer je de weergegeven tekst in andere formules of uitvoer wilt vastleggen of manipuleren.
Toepasbare scenario's: Deze methode is het beste wanneer je tekstuitvoer nodig hebt of geavanceerde afkortingslogica die niet wordt ondersteund door aangepaste formaten. Het is geschikt voor samenvattingen, dashboards of wanneer je geëxporteerde/geplakte resultaten deelt.
1. Voer de volgende formule in de doelcel in (bijvoorbeeld, als je getal in cel A2 staat, voer deze in cel B2 in):
=IF(ABS(A2)>=1000000000,TEXT(A2/1000000000,"0.00")&"B",IF(ABS(A2)>=1000000,TEXT(A2/1000000,"0.00")&"M",IF(ABS(A2)>=1000,TEXT(A2/1000,"0.00")&"K",A2)))Deze formule controleert de waarde in A2 en verkort deze met B (miljard), M (miljoen) of K (duizend), waarbij twee decimalen worden weergegeven. Negatieve getallen worden ondersteund en waarden kleiner dan 1.000 worden zo weergegeven.
2. Druk op Enter om de formule te bevestigen. Als je dit wilt toepassen op meer rijen, sleep dan het vulgreepje naar beneden of kopieer de formule naar andere cellen indien nodig.
Praktische tips: Om decimalen aan te passen, wijzig het "0,00"-gedeelte in de functie TEKST (bijv., gebruik "0,0" voor één decimaal). - Bij het verwerken van zeer grote getallen (triljoenen of meer), breid de ALS-patroon verder uit.
Waarschuwingen: - Dit creëert tekstuitvoer, dus getallen zullen niet correct berekend worden in SOM of andere wiskundige formules tenzij ze terug worden geconverteerd. Als je wilt voorkomen dat er nullen achteraan verschijnen (bijv., laat "12M" zien in plaats van "12,00M"), gebruik aangepaste opmaak binnen TEKST.
Getallen afkorten met VBA-macro
Voor gebruikers die het afkorten van getallen volledig willen automatiseren in een bereik of aangepaste regels willen toepassen die niet eenvoudig te verwerken zijn met formules of getalopmaak, kun je een Excel VBA-macro gebruiken. Deze benadering stelt je in staat om ter plekke te vervangen of een nieuwe kolom met verkorte resultaten toe te voegen, en is geschikt voor herhalende taken of het verwerken van grote tabellen.
Beste voor: Het automatiseren van afkortingen over grote of dynamische bereiken; aangepaste, herbruikbare workflows; batchverwerking van variabele datalay-outs.
1. Druk eerst op Alt + F11 om de VBA-editor in Excel te openen. Klik in het VBA-venster op Invoegen > Module om een nieuwe module te maken en kopieer de volgende code in het modulevenster:
Sub AbbreviateNumbersAddColumn()
Dim rng As Range
Dim cell As Range
Dim destCol As Range
Dim abbrevValue As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range of numbers to abbreviate", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
Set destCol = rng.Offset(0, 1).Columns(1)
For Each cell In rng
If IsNumeric(cell.Value) Then
If Abs(cell.Value) >= 1000000000 Then
abbrevValue = Format(cell.Value / 1000000000, "0.00") & "B"
ElseIf Abs(cell.Value) >= 1000000 Then
abbrevValue = Format(cell.Value / 1000000, "0.00") & "M"
ElseIf Abs(cell.Value) >= 1000 Then
abbrevValue = Format(cell.Value / 1000, "0.00") & "K"
Else
abbrevValue = cell.Value
End If
Else
abbrevValue = cell.Value
End If
destCol.Cells(cell.Row - rng.Row + 1, 1).Value = abbrevValue
Next
MsgBox "Abbreviated values written to the column to the right of the selection."
End Sub2 Om de code uit te voeren, sluit het VBA-venster. Selecteer in Excel het bereik van getallen dat je wilt afkorten, druk vervolgens op de F5-toets of klik op Uitvoeren. De macro zal je vragen een bereik te selecteren en schrijft vervolgens de verkorte resultaten in de kolom direct rechts van je originele bereik.
Gebruikstips: Veilig te gebruiken als je de originele gegevens wilt behouden — resultaten verschijnen in een nieuwe aangrenzende kolom. - Je kunt de code aanpassen om resultaten ter plaatse te schrijven als je de originele waarden wilt overschrijven, maar dit wordt niet aanbevolen tenzij je een back-up hebt.
Probleemoplossing herinneringen: Zorg ervoor dat macro's zijn ingeschakeld in je werkmap. Als je gegevens tekst of lege cellen bevat, laat de macro deze ongewijzigd. Voor aangepaste labels (bijv., "T" weergeven voor triljoenen), pas de Als-blokken dienovereenkomstig aan.
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