Wie gibt man die erste bzw. letzte nicht leere Zelle einer Zeile oder Spalte zurück?
Im täglichen Excel-Arbeitsablauf kommt es häufig vor, dass Datensätze nur teilweise mit Werten befüllt sind, während andere Zellen leer bleiben. Oft müssen Sie rasch die allererste oder die allerletzte ausgefüllte Zelle in einer bestimmten Zeile oder Spalte identifizieren. Bei wenigen Einträgen gelingt das mühelos – doch in längeren Zeilen oder Spalten wird die manuelle Suche schnell ineffizient, zeitaufwendig und fehleranfällig.
Es gibt mehrere praktische Methoden, um effizient den Wert der ersten oder letzten nicht leeren Zelle in einer Zeile oder Spalte abzurufen – und so wertvolle Zeit zu sparen. Im Folgenden finden Sie sowohl formelbasierte als auch andere praxisnahe Lösungen für unterschiedliche Anwendungsszenarien, einschließlich der Handhabung dynamischer Bereiche und großer Datensätze.
Geben Sie die erste nicht leere Zelle in einer Zeile oder Spalte mithilfe einer Formel zurück
Geben Sie die letzte nicht leere Zelle in einer Zeile oder Spalte mithilfe einer Formel zurück
Geben Sie den Wert der ersten oder letzten nicht leeren Zelle mithilfe eines VBA-Makros zurück
Finden Sie die erste oder letzte nicht leere Zelle mithilfe der Excel-Filterfunktion
Geben Sie die erste nicht leere Zelle in einer Zeile oder Spalte mithilfe einer Formel zurück
Um die erste Zelle mit Daten aus einer Zeile oder Spalte zu extrahieren, kombinieren Sie die Funktionen INDEX und VERGLEICH. Dieser Ansatz eignet sich ideal, wenn Sie den Wert programmgesteuert abrufen möchten und Ihr Arbeitsblatt dynamisch bleiben soll – auch wenn sich die zugrunde liegenden Daten ändern. So geht’s:
1. Geben Sie in einer leeren Zelle neben Ihrem Datenbereich die folgende Formel ein:
=INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0))Befinden sich Ihre Daten beispielsweise in den Zellen A1 bis A13, gibt diese Formel die erste Zelle zurück, die weder leer noch gleich null ist. Siehe folgendes Beispiel:
2. Drücken Sie die Enter-Taste. Es wird der Wert der ersten Zelle im Bereich angezeigt, die weder leer noch null ist (je nach Formulierungslogik):
Hinweise & Tipps:
- In der obigen Formel können Sie A1:A13anpassen, um auf eine beliebige Spalte oder Zeile zu verweisen (z. B.)1:1 für Zeile 1 oder B2:M2 für einen Teilabschnitt einer Zeile).
- Diese Methode funktioniert zuverlässig bei einer einzelnen Zeile oder Spalte. Bei Tabellen oder mehrfach ausgewählten Bereichen wenden Sie die Formel stattdessen einzeln auf jede Zeile bzw. Spalte an.
- Falls die Formel einen Fehler ()#N/A) zurückgibt, prüfen Sie bitte, ob Ihr Bereich tatsächlich mindestens eine Zelle enthält, die weder leer noch nullwertig ist.
- Beachten Sie: Wenn Sie ausschließlich leere Zellen ignorieren möchten und nicht Nullwerte, ersetzen Sie
0durch""für echte Leerzeichen („""“).
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
Geben Sie die letzte nicht leere Zelle in einer Zeile oder Spalte mithilfe einer Formel zurück
Um den Wert der letzten nicht leeren Zelle in einem bestimmten Bereich abzurufen, bietet eine matrixbasierte SUCHEN-Formel eine effiziente und unkomplizierte Lösung – ideal für dynamische oder sich ständig ändernde Daten. So identifizieren Sie automatisch den neuesten Dateneintrag in einer Liste oder Zusammenfassungstabelle.
1. Geben Sie die folgende Formel in eine leere Zelle neben Ihrem Zielbereich ein:
=LOOKUP(2,1/(A1:A13<>""),A1:A13)Diese Formel durchsucht den begrenzten Bereich und gibt den Wert der letzten nicht leeren Zelle zurück. Beispielhaft für den Bereich A1:A13:
2. Nachdem Sie Enter gedrückt haben, berechnet Excel den Wert und zeigt ihn in der letzten nicht leeren Zelle an:
Hinweise & Vorschläge:
- Sie können diese Formel mit jeder beliebigen Spalte oder Zeile verwenden ()B1:B20, F8:F30 oder 2:2 usw.). Passen Sie den Bereichsbezug einfach entsprechend an.
- Falls Ihre Daten Nullwerte enthalten, die Sie ignorieren möchten, können Sie
A1:A13""durchA1:A130ersetzen – achten Sie jedoch darauf, dass Ihre Absicht hinsichtlich echter Leerzellen im Vergleich zu Nullwerten korrekt abgebildet wird. - Dieser Ansatz eignet sich am besten für einfache Bereiche. In Bereichen, in denen Formeln „""“ (leeren Text) zurückgeben, behandelt diese Formel solche Zellen als leer.
- Sind alle Zellen leer, gibt die Formel einen #N/A-Fehler zurück.
Geben Sie den Wert der ersten oder letzten nicht leeren Zelle mithilfe eines VBA-Makros zurück
Für Anwender, die mit großen Datensätzen arbeiten oder repetitive Aufgaben automatisieren müssen, vereinfacht ein einfaches VBA-Makro den Prozess erheblich – besonders wenn sich die betroffenen Bereiche ändern oder zahlreiche Zeilen und Spalten umfasst sind. Im Gegensatz zu Formeln führt VBA bedarfsgesteuert Aktionen wie das Auffinden der ersten oder letzten nicht leeren Zelle aus und eignet sich daher ideal für wiederholte Operationen über mehrere Bereiche hinweg.
1. Öffnen Sie den VBA-Editor über Entwicklertools > Visual Basic. Klicken Sie im erscheinenden VBA-Fenster auf Einfügen > Modul, und fügen Sie eines der folgenden Verfahren in das Modulfenster ein:
Makro zum Auffinden der erstennicht leeren Zelle in einer Bereich auswählen:
Sub FindFirstNonBlankCell()
Dim rng As Range
Dim cell As Range
Dim firstValue As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
firstValue = ""
For Each cell In rng
If cell.Value <> "" Then
firstValue = cell.Value
Exit For
End If
Next cell
If firstValue <> "" Then
MsgBox "The first non blank cell value is: " & firstValue, vbInformation, xTitleId
Else
MsgBox "No non blank cells found.", vbExclamation, xTitleId
End If
End SubAnalog hierzu der Code zum Auffinden der letztennicht leeren Zelle:
Sub FindLastNonBlankCell()
Dim rng As Range
Dim cell As Range
Dim lastValue As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
lastValue = ""
For Each cell In rng
If cell.Value <> "" Then
lastValue = cell.Value
End If
Next cell
If lastValue <> "" Then
MsgBox "The last non blank cell value is: " & lastValue, vbInformation, xTitleId
Else
MsgBox "No non blank cells found.", vbExclamation, xTitleId
End If
End Sub2. Um den Code auszuführen, klicken Sie im VBA-Editor auf die Schaltfläche Ausführen
. Sie werden aufgefordert, den Zielbereich zur Suche nach nicht leeren Zellen auszuwählen. Nachdem Sie den Bereich ausgewählt und bestätigt haben, wird in einem Dialogfeld je nach ausgeführtem Makro entweder der Wert der ersten oder der letzten nicht leeren Zelle angezeigt.
- Diese Makros sind flexibel einsetzbar – sowohl für Zeilen als auch für Spalten, ganz gleich, wie umfangreich Ihre Daten sind.
- VBA ermöglicht Automatisierung und wiederholbare Abläufe – ideal für häufige oder umfangreiche Aufgaben.
- Stellen Sie beim Ausführen von Makros sicher, dass Ihre Arbeitsmappe gespeichert ist und ggf. Makros aktiviert sind. Testen Sie das Makro stets zuerst an Beispieldaten, um seine Genauigkeit zu überprüfen, bevor Sie es auf wichtige Daten anwenden.
Finden Sie die erste oder letzte nicht leere Zelle mithilfe der Excel-Filterfunktion
Für Anwender, die eine schnelle visuelle Möglichkeit benötigen, um nicht leere Werte zu identifizieren – insbesondere bei sehr langen Spalten – bietet die integrierte Filter-Funktion in Excel eine hervorragende Lösung, um nicht leere Einträge auf einen Blick hervorzuheben. Obwohl diese Methode den Wert nicht automatisch in eine andere Zelle überträgt, eignet sie sich ideal zur Überprüfung und Navigation während der Datenanalyse.
So lokalisieren Sie visuell die erste oder letzte nicht leere Zelle mithilfe des Filters:
- Wählen Sie die Spalte oder Zeile aus, die Ihre Daten enthält. Für eine einfachere Filterung können Sie die gesamte Spalte markieren (z. B. durch Klicken auf den Spaltenbuchstaben).
- Klicken Sie auf die Registerkarte Daten und wählen Sie dann Filter.
- Klicken Sie auf den kleinen Filterspitzpfeil in der Überschrift Ihres Bereichs oder Ihrer Tabelle.
- Deaktivieren Sie die Option (Leer), damit nur ausgefüllte Zellen sichtbar bleiben.
- Nach dem Filtern ist der erste sichtbare Wert oben in der Spalte Ihre erste nicht leere Zelle; scrollen Sie nach unten, um die letzte zu sehen.
Vorteile: Die Filtermethode ist schnell, kommt ohne Formeln aus und funktioniert auch bei Spalten mit tausenden von Zeilen hervorragend.
Nachteile: Die Lösung ist rein visuell – sie gibt das Ergebnis nicht in eine Zelle aus und unterstützt keine Automatisierung durch Formeln oder VBA. Dennoch eignet sie sich ideal für manuelle Prüfungen, Überprüfungen und interaktive Datenanalyse.
Fehlerbehebung & Hinweise:
Sollte der Filter nicht wie erwartet funktionieren, stellen Sie sicher, dass Sie nicht nur einen Teil Ihrer Daten markiert haben – dies kann dazu führen, dass der Filter falsch angewendet wird. Entfernen Sie den Filter nach Abschluss, indem Sie auf Daten > Löschen klicken, um die vollständige Ansicht Ihres Datensatzes wiederherzustellen.
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