Wie lassen sich Zahlen in Excel einfach rangieren, wobei leere Zellen automatisch übersprungen werden?
Beim Arbeiten mit Daten in Excel stoßen Sie häufig auf Listen, die leere Zellen enthalten. Wenn Sie Standard-Rangfunktionen wie RANG oder RANG.GLEICH auf solchen Listen verwenden, führen leere Zellen typischerweise zu Fehlern oder unerwünschten Ergebnissen in der Rangausgabe. Das erschwert die Interpretation Ihrer Daten – besonders dann, wenn Sie leere Zellen bewusst leer lassen möchten, statt Fehlerwerte oder willkürliche Ränge anzuzeigen. Eine effiziente Rangbildung von Zahlen, bei der leere Zellen automatisch übersprungen werden, steigert die Klarheit und Benutzerfreundlichkeit Ihrer Ergebnisse und verleiht Ihrer Tabelle einen professionelleren, leichter lesbaren Look.
In diesem Artikel erhalten Sie Schritt-für-Schritt-Anleitungen zur effizienten Nutzung von Formeln und VBA-Makros, um diese Aufgabe zu meistern. Die erweiterten Lösungen bieten zudem detaillierte Parametererklärungen, praktische Tipps sowie gezielte Fehlerbehebungsvorschläge, um häufige Fallstricke sicher zu umgehen.
- Werte rangieren, wobei leere Zellen in aufsteigender Reihenfolge mithilfe von Formeln übersprungen werden
- Werte rangieren, wobei leere Zellen in absteigender Reihenfolge mithilfe einer Formel übersprungen werden
- Werte rangieren, wobei leere Zellen mithilfe von VBA übersprungen werden
Werte rangieren, wobei leere Zellen in aufsteigender Reihenfolge mithilfe von Formeln übersprungen werden
In Situationen, in denen Sie Ränge in aufsteigender Reihenfolge vergeben möchten, dabei aber leere Zellen ignorieren wollen, bietet sich ein gängiger Ansatz an: mehrere Hilfsspalten mit Formellogik zu kombinieren, um sicherzustellen, dass leere Werte bei der Rangbildung außen vor bleiben.
Anwendungsszenario: Verwenden Sie diese Methode, wenn Sie inkrementelle (niedrig-zu-hoch) Ränge erstellen möchten, während die Position Ihrer leeren Zellen erhalten bleibt – insbesondere bei fortlaufenden Bereichen, in denen fehlende Einträge die Rangnummerierung nicht beeinflussen sollen.
Führen Sie zur Durchführung einer aufsteigenden Rangbildung unter Überspringen leerer Zellen die folgenden Schritte aus, die zwei Hilfsspalten zur Erstellung des Ergebnisses verwenden:
1. Wählen Sie eine leere Zelle neben Ihren Werten aus – beispielsweise Zelle B2, falls Ihre Liste bei A2 beginnt – und geben Sie die folgende Formel ein:
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))Die Formel gibt einen leeren Wert zurück, wenn A2 leer ist; andernfalls generiert sie Dezimalzahlen basierend auf dem Wert in A2 und fügt beim Herunterziehen des Ausfüllkästchens .0, .1, .2, .3 usw. hinzu, um die Formel für alle Zeilen mit Daten zu übernehmen.
Parametererklärungen und Tipps:
- $A2: Die erste Zelle, die sortiert werden soll. Passen Sie diese Referenz an, falls Ihre Liste in einer anderen Zeile beginnt.
- $B$2: Die Zelle, in die Sie diese Formel eingeben. Achten Sie auf die absoluten Bezüge (z. B. $A$2 und $B$2), damit die Formel beim Herunterziehen korrekt funktioniert.
2. Geben Sie in der nächsten Spalte, beispielsweise in C2, diese Formel ein, um eine sortierte Liste der Hilfswerte zu erstellen:
=SMALL($B$2:$B$8,ROW()-ROW($C$1))Diese Formel extrahiert nacheinander den kleinsten, nächstkleinsten und darauf folgenden Wert aus B2:B8 (Hinweis: Passen Sie den Bereich an, falls Ihre Daten weiter reichen), sobald Sie die Formel nach unten kopieren.
Parametererklärungen:
- $B$2:$B$8: Der Bereich, in dem die vorherige (erste) Hilfsformel angewendet wird.
- $C$1: Die Zelle direkt über derjenigen, in die Sie die Formel eingeben – dieser Offset bestimmt die Rangfolge.
3. Geben Sie in Zelle D2 die folgende Formel ein, um Ränge zuzuweisen, während leere Zellen unberührt bleiben:
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")Diese Formel sucht den Wert aus B2 innerhalb der sortierten Ergebnisse in C2:C8. Wenn eine Übereinstimmung gefunden wird, gibt sie den Rang aus; andernfalls – beispielsweise bei leeren Zellen – bleibt die Zelle leer, sodass das übersichtliche Erscheinungsbild erhalten bleibt. Ziehen Sie das Ausfüllkästchen nach unten, um die Formel auf alle relevanten Zeilen anzuwenden.
Parameter:
- $B2: Die Zelle, die den Hilfswert für die Rangbildung enthält.
- $C$2:$C$8: Der Bereich mit den sortierten Hilfswerten.
Vorsichtsmaßnahmen: Wenn Sie Daten hinzufügen oder entfernen, passen Sie alle Bereiche in jeder Formel entsprechend Ihrer neuen Datenmenge an. Verwenden Sie bei sehr großen Listen dynamische Bereiche oder Excel-Tabellen, um manuelle Anpassungen der Bezüge zu vermeiden.
Fehlerbehebung: Falls Ränge fehlen oder falsch ausgerichtet sind, überprüfen Sie, ob alle Hilfsformelbereiche korrekt ausgerichtet sind. Fehlausrichtungen zwischen Spalten führen zu falschen Rängen oder unbeabsichtigten Fehlern.
Werte rangieren, wobei leere Zellen in absteigender Reihenfolge mithilfe einer Formel übersprungen werden
Möchten Sie Ränge in absteigender Reihenfolge vergeben – also dem höchsten Wert den Rang 1 zuweisen –, gibt es einen schnelleren Ansatz mit nur einer einzigen Formel. Das ist besonders praktisch bei Testergebnissen, Verkaufszielen und ähnlichen Datensätzen, bei denen leere Zellen fehlende oder nicht verfügbare Informationen kennzeichnen und weder eine Rangposition erhalten noch Fehler anzeigen sollen.
Wählen Sie eine Zelle in derselben Zeile wie der erste Dateneintrag aus, in der das Ergebnis erscheinen soll, und geben Sie Folgendes ein:
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))Nachdem Sie die Formel eingegeben haben, verwenden Sie das Ausfüllkästchen, um sie entlang Ihrer Daten nach unten zu kopieren. Diese Formel prüft, ob die RANG-Funktion einen Fehler zurückgibt (z. B. wenn)A2 leer ist). Falls ja, bleibt das Ergebnis leer, anstatt „#NV“ anzuzeigen; enthält die Zelle hingegen einen gültigen Wert, wird der entsprechende Rang angezeigt.
Parameter:
- A2: Die Zelle, die sortiert werden soll (passen Sie dies an Ihren Datenbereich an).
- A$2:A$8: Der vollständige Bereich Ihrer Daten (verwenden Sie absolute Bezüge, um ihn beim Kopieren beizubehalten).
Fehlerhinweise: Wenn weiterhin „#NV“-Fehler angezeigt werden, überprüfen Sie, ob die Formelbezüge mit Ihrem beabsichtigten Datenbereich übereinstimmen und ob sich in den zu sortierenden Zellen keine nicht-numerischen Werte befinden.
Werte rangieren, wobei leere Zellen mithilfe von VBA übersprungen werden
Für Anwender, die mit Makros vertraut sind und die Rangbildung über einen Bereich mit leeren Zellen – egal ob in aufsteigender oder absteigender Reihenfolge – automatisieren möchten, vereinfacht ein benutzerdefiniertes VBA-Makro den Prozess erheblich, da es mehrere Hilfsspalten und laufende Formelwartung überflüssig macht.
Verwendung:
1. Wechseln Sie zur Entwicklertools-Registerkarte und klicken Sie auf Visual Basic, um den Editor Microsoft Visual Basic für Applikationen zu öffnen. Falls die Registerkarte Entwicklertools nicht sichtbar ist, lesen Sie diese Anleitung: Registerkarte Entwicklertools in Excel anzeigen.
2. Klicken Sie im neuen Fenster Microsoft Visual Basic für Applikationen auf Einfügen > Modul, und fügen Sie einen der folgenden Codes in das Modulfenster ein:
- So führen Sie eine aufsteigende Rangbildung durch, während leere Zellen übersprungen werden:
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 - So führen Sie eine absteigende Rangbildung durch, während leere Zellen übersprungen werden:
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. Drücken Sie F5, um das Makro auszuführen. Es erscheint ein Dialogfeld, in dem Sie den Bereich auswählen, der rangiert werden soll. Anschließend öffnet sich ein weiteres Dialogfeld, in dem Sie die erste Zelle festlegen, ab der die Rangfolge eingefügt werden soll. Das Makro gibt dann die Ränge ab Ihrer gewählten Zelle aus – und alle leeren Zellen im Quellbereich bleiben leer.
Tipps:
- Sollte nichts passieren, überprüfen Sie bitte, ob Makros aktiviert sind und ob Sie die erforderliche Berechtigung besitzen, um Code in Ihrer Arbeitsmappe auszuführen.
- Die Ausführung eines VBA-Makros lässt sich nicht rückgängig machen. Sichern Sie daher Ihre Daten, indem Sie sie vorab kopieren oder ein Backup erstellen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins