Hoe sorteert u waarden in Excel terwijl u nullen negeert?
Wanneer u in Excel werkt met een gegevensset die zowel getallen als nullen bevat, plaatst de standaardsorteerfunctie bij oplopende volgorde doorgaans alle nulwaarden bovenaan de lijst. In veel situaties wilt u echter uw gegevens sorteren terwijl de nulwaarden onderaan blijven staan, zodat ze tijdens het sorteren effectief worden genegeerd. Denk bijvoorbeeld aan het analyseren van verkoopcijfers, productiecijfers of toetsresultaten: dan richt u zich eerst op de resultaten ongelijk aan nul en bekijkt u pas aan het einde de nulwaarden. Zo worden uw rapporten duidelijker en relevanter voor verdere analyse.
Sorteer waarden en negeer nullen met een hulpkolom
Sorteer waarden en negeer nullen met één formulekolom (alternatieve methode)
Sorteer waarden en negeer nullen met VBA-code (alternatieve oplossing)
Sorteer waarden en negeer nullen met een hulpkolom
Om gegevens te sorteren met nulwaarden onderaan, kunt u een hulpkolom gebruiken met een specifieke formule. Deze aanpak is praktisch, werkt in alle Excel-versies en is daarom ideaal als uw lijst nullen bevat tussen andere waarden, terwijl u getallen ongelijk aan nul samen wilt sorteren en de nullen gegroepeerd onderaan wilt plaatsen.
1. Voer in een lege cel naast uw waarden (bijvoorbeeld B2, naast de oorspronkelijke gegevens in kolom A) de volgende formule in:
=IF(A2=0,"",A2)Deze formule controleert de waarde in cel A2. Als die nul is, retourneert ze een lege cel („"); anders geeft ze de oorspronkelijke waarde weer. Gebruik de vulgreep om de formule naar beneden te kopiëren voor alle relevante rijen in uw gegevens. Uw hulpkolom toont dan alleen waarden ongelijk aan nul en laat nullen als lege cellen staan. De schermafbeelding laat zien hoe de hulpkolom eruitziet na toepassing van de formule:
Tip: Zorg ervoor dat uw formule verwijst naar de juiste kolom en het juiste celbereik, conform de opzet van uw werkblad, om fouten te voorkomen.
2. Nadat u de hulpkolom hebt gemaakt, selecteert u alle cellen in die kolom, gaat u naar het Gegevens-tabblad en kiest u Sorteren Kleinste naar grootste. In het dialoogvenster Sorteerwaarschuwing dat verschijnt, kiest u Selectie uitbreiden, zodat de volledige rij gegevens samen met de waarden in uw hulpkolom wordt gesorteerd.
Tip: Het uitbreiden van de selectie is belangrijk; anders wordt alleen de hulpkolom gesorteerd en raakt uw gegevensverzameling misplaatst. Bevestig altijd dat u de volledige Gegevensbereik sorteert.
3. Klik op Sorteren. De gegevens worden nu oplopend gesorteerd, met alle waarden ongelijk aan nul bovenaan en nullen onderaan, precies zoals gewenst. Na het sorteren kunt u de hulpkolom verwijderen of verbergen als deze niet langer nodig is.
Opmerking: Als uw gegevens formules bevatten, voorkomt u onverwachte resultaten door deze eerst als waarden te kopiëren en plakken voordat u sorteert.
Een belangrijk voordeel van deze methode is de eenvoud en compatibiliteit met alle Excel-versies. Het vereist echter wel een hulpkolom, wat mogelijk niet ideaal is voor gebruikers die extra kolommen willen vermijden. Bovendien moet u, als uw gegevensset regelmatig wordt bijgewerkt, ervoor zorgen dat nieuwe rijen worden meegenomen in het bereik van uw hulpformule.
Probleemoplossing: Als u merkt dat nullen niet zoals verwacht naar beneden worden gesorteerd, controleer dan of de formule correct is ingevoerd en naar alle relevante cellen is doorgekopieerd, en zorg ervoor dat de selectie wordt uitgebreid tijdens het sorteervoorbeeld.
Tip. Standaard plaatst Excel lege cellen onderaan bij het sorteren. Als u de lege cellen — die de oorspronkelijke nullen vertegenwoordigen — bovenaan wilt sorteren, gebruikt u de functie Geavanceerd sorteren van Kutools voor Excel en selecteert u de optie Lege cellen aan het begin, zoals te zien in de schermafbeelding hieronder. Deze functie biedt meer flexibele sorteerscenario’s, bijvoorbeeld wanneer u prioriteit wilt geven aan ontbrekende of nulwaarden. De tool is 30 dagen lang gratis te proberen, inclusief alle functies. Download hier een proefversie.
Sorteer waarden en negeer nullen met één formulekolom (alternatieve methode)
Deze methode is handig wanneer u alleen waarden ongelijk aan nul uit een lijst wilt halen en sorteren, zonder extra stappen of ingebouwde sorteerfuncties te gebruiken. De aanpak is geschikt voor scenario’s waarin u een aparte gesorteerde lijst van waarden ongelijk aan nul wilt maken terwijl de oorspronkelijke gegevens ongewijzigd blijven.
1. Voer de volgende matrixformule in een nieuwe kolom in (bijvoorbeeld in C2):
=SMALL(IF($A$2:$A$11<>0,$A$2:$A$11),ROW(A1))2. Druk op Ctrl+Shift+Enter (voor Excel 2019 en eerder) of gewoon op Enter (voor Excel 365/Excel 2021). Sleep daarna de vulgreep zover naar beneden als nodig. Deze formule retourneert de gesorteerde waarden die ongelijk zijn aan nul uit het oorspronkelijke gegevensbereik in kolom A.
Uitleg: IF($A$2:$A$110,$A$2:$A$11) filtert de waarden die ongelijk zijn aan nul, terwijl SMALL(...,ROW(A1)) de resultaten oplopend gesorteerd retourneert. Pas het bereik $A$2:$A$11 aan uw gegevens aan en zorg ervoor dat u voldoende rijen in kolom C hebt voor alle waarden ongelijk aan nul; anders geeft de formule fouten weer in overtollige cellen.
Deze aanpak is efficiënt en laat de oorspronkelijke lijst onaangetast, maar de gesorteerde waarden verschijnen alleen op een nieuwe locatie.
Opmerking: De formule toont eerst de unieke waarden en sorteert ze daarna.
Sorteer waarden en negeer nullen met VBA-code (alternatieve oplossing)
Als u lijsten herhaaldelijk moet sorteren terwijl u nullen negeert, kunt u dit automatiseren met VBA – een tijdbesparende oplossing voor gebruikers die vertrouwd zijn met macro’s en regelmatig grote of dynamisch bijgewerkte lijsten verwerken. Maak altijd eerst een back-up van uw gegevens voordat u een VBA-script uitvoert.
1. Ga naar Ontwikkelaarshulpmiddelen > Visual Basic om het venster Microsoft Visual Basic for Applications te openen. Klik op Invoegen > Module en plak de volgende code in de module:
Sub SortIgnoreZeros()
Dim rng As Range
Dim lastRow As Long
Dim ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng = Application.InputBox("Select the range to sort", xTitleId, "", Type:=8)
lastRow = rng.Rows.Count
' Move nonzeros to top
rng.Sort Key1:=rng, Order1:=xlAscending, Header:=xlNo
End Sub2Nadat u de code hebt ingevoerd, drukt u op 
om de macro uit te voeren. Er verschijnt een dialoogvenster waarin u het bereik kunt selecteren dat u wilt sorteren. Het VBA-script sorteert het geselecteerde bereik oplopend en plaatst nullen onderaan. Zorg ervoor dat u alleen de relevante kolom met waarden selecteert; anders kunnen rijen verkeerd worden geplaatst.
Deze VBA-aanpak is uiterst efficiënt voor herhalende taken, maar wees voorzichtig met niet-opgeslagen gegevens en verifieer altijd uw selectie voordat u de macro uitvoert.
Kort samengevat kunt u waarden in Excel op verschillende manieren sorteren terwijl u nullen negeert – elke methode heeft zijn eigen voordelen. De beste keuze hangt af van hoe vaak u deze taak uitvoert, de omvang van uw gegevens en of u de oorspronkelijke data ongewijzigd wilt laten. Mocht u onverwachte sorteerresultaten krijgen, controleer dan altijd uw formulebereiken, verifieer de instellingen voor het uitbreiden van de selectie en overweeg een back-up van uw werkblad te maken. Door de ingebouwde Excel-functies, aanvullende formules, VBA-macro’s of de verbeterde sorteermogelijkheden van Kutools te verkennen, vindt u de workflow die het beste aansluit bij uw behoeften.
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