Hoe rangschikt u eenvoudig getallen in Excel en slaat u lege cellen automatisch over?
Bij het werken met gegevens in Excel komt het vaak voor dat u lijsten tegenkomt met lege cellen. Als u standaard Excel-rangschikkingsfuncties zoals RANG of RANG.GELIJK op dergelijke lijsten gebruikt, tonen de lege cellen meestal foutmeldingen of ongewenste resultaten in de rangschikkingsuitvoer. Hierdoor worden uw gegevens moeilijker te interpreteren, vooral als u lege cellen juist leeg wilt laten in plaats van foutwaarden of willekeurige rangnummers te tonen. Getallen efficiënt rangschikken terwijl lege cellen automatisch leeg blijven, verhoogt de duidelijkheid en bruikbaarheid van uw resultaten en maakt uw werkblad professioneler en beter leesbaar.
In dit artikel vindt u stapsgewijze instructies voor het gebruik van formules en VBA-macro’s om deze taak uit te voeren, inclusief gedetailleerde uitleg van parameters, praktische tips en handige suggesties voor probleemoplossing om veelvoorkomende valkuilen te omzeilen.
- Rangschik waarden terwijl lege cellen worden overgeslagen in oplopende volgorde met behulp van formules
- Rangschik waarden terwijl lege cellen worden overgeslagen in aflopende volgorde met behulp van een formule
- Rangschik waarden terwijl lege cellen worden overgeslagen met VBA
Rangschik waarden terwijl lege cellen worden overgeslagen in oplopende volgorde met behulp van formules
In situaties waarin u rangnummers in oplopende volgorde moet toewijzen maar lege cellen wilt negeren, is een veelgebruikte aanpak het gebruik van meerdere hulpkolommen en formulelogica om ervoor te zorgen dat lege waarden buiten beschouwing blijven bij het rangschikken.
Toepasselijk scenario: Gebruik deze methode wanneer u incrementele (van laag naar hoog) rangnummers wilt genereren, terwijl de positie van lege cellen behouden blijft—vooral in een continu bereik waar ontbrekende gegevens geen invloed mogen hebben op de rangnummering.
Volg deze stappen voor een oplopende rangschikking terwijl lege cellen worden overgeslagen; hiervoor zijn twee hulpkolommen nodig om het resultaat op te bouwen:
1. Selecteer een lege cel naast uw waarden – bijvoorbeeld cel B2 als uw lijst begint bij A2 – en voer de volgende formule in:
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))De formule retourneert een lege cel als A2 leeg is; anders genereert deze decimale getallen op basis van de waarde in A2, zoals .0, .1, .2, .3, enzovoort, naarmate u de vulgreep naar beneden sleept om de formule naar alle rijen met gegevens te kopiëren.
Uitleg van parameters en tips:
- $A2: De eerste cel die gesorteerd moet worden. Pas dit aan als uw lijst op een andere rij begint.
- $B$2: De cel waarin u deze formule invoert. Let op de absolute verwijzingen (bijv. $A$2 en $B$2) om te garanderen dat de formule correct blijft werken wanneer u deze naar beneden kopieert.
2. Voer in de volgende kolom, bijvoorbeeld C2, de volgende formule in om een gesorteerde lijst van hulpwaarden te genereren:
=SMALL($B$2:$B$8,ROW()-ROW($C$1))Deze formule haalt achtereenvolgens de kleinste, daarna de op één na kleinste en vervolgens de volgende waarden uit B2:B8 (let op: pas het bereik aan als uw gegevens verder reiken) terwijl u de formule naar beneden kopieert.
Uitleg van parameters:
- $B$2:$B$8: Het bereik waarin de eerdere (eerste) hulpformule wordt gebruikt.
- $C$1: De cel direct boven de plek waar u de formule invoert; deze offset bepaalt de rangschikkingsvolgorde.
3. Voer in cel D2 de volgende formule in om rangnummers toe te wijzen, terwijl lege cellen onaangeroerd blijven:
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")Deze formule zoekt de waarde in B2 op in de gesorteerde resultaten in C2:C8. Als er een overeenkomst is, wordt het rangnummer weergegeven; zo niet (bijvoorbeeld bij lege cellen), dan blijft de cel leeg. Sleep de vulgreep naar beneden om dit toe te passen op alle relevante rijen.
Parameters:
- $B2: De cel met de hulpwaarde die wordt gebruikt voor het rangschikken.
- $C$2:$C$8: Het bereik met gesorteerde hulpwaarden.
Voorzorgsmaatregelen: Als u gegevens toevoegt of verwijdert, vergeet dan niet om alle bereiken in elke formule bij te werken, zodat deze overeenkomen met de nieuwe omvang van uw gegevens. Overweeg voor zeer grote lijsten het gebruik van dynamische bereiken of Excel-tabellen om handmatige aanpassingen van bereiken tot een minimum te beperken.
Probleemoplossing: Als rangnummers ontbreken of niet op de juiste plek staan, controleer dan of alle bereikberekeningen van hulpformules correct zijn uitgelijnd. Een verkeerde uitlijning tussen kolommen leidt tot onjuiste rangnummers of onbedoelde fouten.
Rangschik waarden terwijl lege cellen worden overgeslagen in aflopende volgorde met behulp van een formule
Wanneer u rangnummers wilt toewijzen in aflopende volgorde — waarbij de hoogste waarde rang 1 krijgt — is er een snellere methode met slechts één formule. Dit is vooral handig bij toetsresultaten, verkoopdoelstellingen en vergelijkbare datasets waar lege cellen ontbrekende of niet-beschikbare gegevens vertegenwoordigen, en u niet wilt dat deze rangposities innemen of foutmeldingen genereren.
Selecteer een cel op dezelfde rij als de eerste gegevensvermelding, waar u het resultaat wilt weergeven, en voer het volgende in:
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))Nadat u de formule hebt ingevoerd, gebruikt u de vulgreep om deze naar beneden te kopiëren langs uw gegevens. De formule controleert of de RANG-functie een fout retourneert (bijvoorbeeld wanneer)A2 leeg is); in dat geval blijft het resultaat leeg in plaats van „#N/B” weer te geven. Bevat de cel een geldige waarde, dan wordt het juiste rangnummer weergegeven.
Parameters:
- A2: De cel die gesorteerd moet worden (pas dit aan aan uw gegevensbereik).
- A$2:A$8: Het volledige bereik van uw gegevens (gebruik absolute verwijzingen om probleemloos te kopiëren).
Foutmeldingen: Als u nog steeds „#N/B”-fouten ziet, controleer dan of de formuleverwijzingen overeenkomen met uw beoogde gegevensbereik en of er geen niet-numerieke waarden staan in de cellen die worden gesorteerd.
Rangschik waarden terwijl lege cellen worden overgeslagen met VBA
Voor gebruikers die bekend zijn met macro’s en het sorteren van een bereik met lege cellen – zowel oplopend als aflopend – kan een aangepaste VBA-macro het proces aanzienlijk vereenvoudigen door de noodzaak van meerdere hulpkolommen en voortdurend formuleonderhoud te elimineren.
Hoe te gebruiken:
1. Ga naar het tabblad Ontwikkelaar en klik op Visual Basic om de editor Microsoft Visual Basic for Applications te openen. Als het tabblad Ontwikkelaar niet zichtbaar is, raadpleeg dan deze handleiding: Het tabblad Ontwikkelaar weergeven in Excel.
2. Klik in het nieuwe venster Microsoft Visual Basic for Applications op Invoegen > Module en plak een van de volgende codes in het modulevenster:
- Voor een oplopende rangschikking terwijl lege cellen worden overgeslagen:
Sub RankSkipBlank_Ascending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) > NumArr(k) Then ' ← CHANGE HERE temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k ' 1 = smallest Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub - Voor een aflopende rangschikking terwijl lege cellen worden overgeslagen:
Sub RankSkipBlank_Descending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) < NumArr(k) Then temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub
3. Druk op F5 om de macro uit te voeren. Eerst verschijnt een dialoogvenster waarin u het bereik kunt selecteren dat u wilt rangschikken. Vervolgens verschijnt een tweede dialoogvenster waarin u de eerste cel kiest waar de rangschikkingsresultaten moeten worden geplaatst. De macro genereert dan de rangnummers vanaf de door u geselecteerde cel, en eventuele lege cellen in het bronbereik blijven leeg.
Tips:
- Als er niets gebeurt, controleer dan of macro’s zijn ingeschakeld en of u toestemming hebt om code uit te voeren in uw werkmap.
- Het uitvoeren van een VBA-macro kan niet ongedaan worden gemaakt. Maak daarom altijd eerst een kopie of back-up van uw gegevens voordat u de macro uitvoert.
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