Wie findet und gibt man den vorletzten Wert in einer bestimmten Zeile oder Spalte in Excel zurück?
Bei der Arbeit mit großen Excel-Arbeitsblättern ist es oft erforderlich, nicht nur den letzten Wert einer Zeile oder Spalte zu identifizieren, sondern gezielt den vorletzten Wert. Wie in der Abbildung unten gezeigt, müssen Sie beispielsweise im Tabellenbereich A1:E16 den vorletzten Wert in der 6. Zeile oder in Spalte B abrufen. Dieser Bedarf tritt häufig auf, wenn jüngste Änderungen verfolgt, Zeitreihendaten überwacht oder die aktuellsten, aber nicht aktuellen Einträge eines Datensatzes analysiert werden. Im Vergleich zur einfachen Suche nach dem letzten Wert kann das Auffinden des vorletzten Werts weniger direkt sein, insbesondere wenn die Daten Leere Zellen enthalten oder regelmäßig aktualisiert werden. Dieser Artikel bietet klare, schrittweise Lösungen für diese Aufgabe, um Ihren Arbeitsablauf effizienter zu gestalten und häufige Fehlerquellen zu vermeiden.
- Suchen und Rückgabe des vorletzten Werts in einer bestimmten Zeile oder Spalte mithilfe von Formeln
- VBA-Code – Verwenden Sie ein Makro, um den vorletzten Wert in einer angegebenen Zeile oder Spalte zu finden
- Weitere integrierte Excel-Methoden – Filtern Sie alle leeren Zellen und identifizieren Sie den vorletzten Wert manuell
Suchen und Rückgabe des vorletzten Werts in einer bestimmten Zeile oder Spalte mithilfe von Formeln
Wie in der obigen Abbildung gezeigt, liefern Excel-Formeln eine dynamische und effiziente Lösung, wenn Sie den vorletzten Wert entweder in Zeile 6 oder in Spalte B innerhalb des Bereichs A1:E16 suchen und zurückgeben möchten. Formeln eignen sich ideal für Szenarien, in denen sich Ihre Daten häufig aktualisieren oder die Position des vorletzten Werts durch Hinzufügen oder Löschen von Einträgen verschiebt. Im Gegensatz zu manuellen Methoden passt eine Formel Ihr Ergebnis automatisch an den aktuellsten Dateneintrag an.
Suchen und Rückgabe des vorletzten Werts in Spalte B
1. Wählen Sie eine leere Zelle aus, in der der vorletzte Wert angezeigt werden soll. Geben Sie die folgende Matrixformel in die Formelleiste ein und drücken Sie anschließend Strg + Umschalt + Enter (für ältere Excel-Versionen), um sie als Matrixformel zu bestätigen. In Excel 365 oder Excel 2021 genügt das Drücken der Enter-Taste, da Matrixformeln automatisch verarbeitet werden.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Hinweis: In dieser Formel bezieht sich B:B auf Spalte B. Wenn Sie den vorletzten Wert in einer anderen Spalte benötigen, passen Sie einfach B:Bentsprechend an (z. B. ändern Sie dies in)C:C für Spalte C). Diese Methode funktioniert für Bereiche mit oder ohne leere Zellen – achten Sie jedoch auf mögliche Abweichungen, falls ausgeblendete Werte oder herausgefilterte Daten vorhanden sind. Überprüfen Sie stets Ihren Datenbereich, wenn Sie nicht den erwarteten Wert erhalten.
Suchen und Rückgabe des vorletzten Werts in Zeile 6
Wählen Sie eine leere Zelle aus, um das Ergebnis für die 6. Zeile anzuzeigen. Geben Sie dann die folgende Formel in die Formelleiste ein und drücken Sie Enter, um die Eingabe zu bestätigen.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Hinweis: In der obigen Formel ist $A$6 die erste Zelle der Zeile 6, und 6:6bezeichnet die gesamte Zeile 6. Passen Sie diese Bezüge bei Bedarf an, um andere Zeilen anzusprechen. Die Formel zählt dynamisch die Anzahl der nicht leeren Zellen in Zeile 6 und verschiebt sich dann ausgehend von der Startzelle, um die vorletzte ausgefüllte Zelle zu lokalisieren. Falls Ihre Zeile Formeln enthält, die leere Zeichenfolgen zurückgeben ()""), kann COUNTA diese dennoch als nicht leer werten, was das Ergebnis beeinflussen könnte. Überprüfen Sie bei Bereichen mit einer Mischung aus Konstanten und Formeln die Ergebnisse sorgfältig, um Fehler zu vermeiden.
Tipps zur Verwendung von Formeln:
- Bei großen Datensätzen können Vollspaltenbezüge (z. B.)
B:B) die Leistung beeinträchtigen. Beschränken Sie den Bereich nach Möglichkeit auf den tatsächlich benötigten Bereich (z. B.B1:B100). - Wenn Ihre Daten ausgeblendete oder gefilterte Zeilen enthalten, berücksichtigen Formeln weiterhin alle Zellen – auch die ausgeblendeten und herausgefilterten. Verwenden Sie bei gefilterten Daten daher spezielle Teilsummenfunktionen oder Hilfsspalten.
- Wenn alle Werte in einer Zielzeile oder -spalte leer sind, können diese Formeln zu Fehlern oder unerwarteten Ergebnissen führen. Stellen Sie daher sicher, dass Ihre Daten mindestens zwei nicht leere Werte enthalten.
VBA-Code – Verwenden Sie ein Makro, um den vorletzten Wert in einer angegebenen Zeile oder Spalte zu finden
Für Anwender, die regelmäßig den vorletzten Wert in wechselnden oder umfangreichen Bereichen ermitteln müssen, spart die Automatisierung dieses Vorgangs per VBA-Makro wertvolle Zeit – besonders bei dynamischen oder komplexen Tabellen. Makros eignen sich ideal, wenn sich der Umfang Ihrer Daten ändert und Sie Formeln nicht jedes Mal neu anpassen möchten, oder wenn Sie mit derselben Methode Werte aus mehreren unterschiedlichen Positionen extrahieren wollen. Im Folgenden finden Sie eine VBA-Lösung, die Sie zur Auswahl Ihrer Zielzeile oder -spalte auffordert und anschließend automatisch den vorletzten Wert liefert – ganz ohne manuelles Zählen oder wiederholte Anpassungen.
1. Wechseln Sie im Excel-Menüband zu Entwicklertools, und klicken Sie auf Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im Editor auf Einfügen > Modul. Fügen Sie anschließend den folgenden VBA-Code in das neu erstellte Modul ein:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. Nachdem Sie den Code eingegeben haben, kehren Sie zu Excel zurück und führen das Makro entweder über die Schaltfläche 
Ausführen aus oder drücken Alt + F8 und wählen GetSecondToLastValue aus der Liste aus. Es erscheint eine Eingabeaufforderung, in der Sie aufgefordert werden, entweder einen einzelnen Zeilen- oder Spaltenbereich auszuwählen (z. B. B1:B16 für eine Spalte oder A6:E6 für eine Zeile). Nach Bestätigung Ihrer Auswahl zeigt das Makro den vorletzten Wert in einem Dialogfeld an.
Hinweise:
- Dieses Makro zählt ausschließlich nicht leere Zellen und überspringt leere Zellen innerhalb der ausgewählten Zeile oder Spalte automatisch.
- Wenn sich in Ihrer Auswahl weniger als zwei nicht leere Werte befinden, erhalten Sie eine Warnmeldung und es wird kein Wert zurückgegeben.
- Das Makro ist für die Auswahl einzelner Zeilen oder Spalten vorgesehen. Sollten Sie gleichzeitig mehrere Zeilen und Spalten markieren, werden Sie aufgefordert, Ihre Auswahl entsprechend anzupassen.
- Für die Automatisierung können Sie den Code so erweitern, dass das Ergebnis stattdessen in eine bestimmte Zelle des Arbeitsblatts kopiert wird, anstatt eine Meldungsbox anzuzeigen.
Diese VBA-Lösung ist besonders vorteilhaft für Benutzer, die mit dynamischen Tabellen oder häufig wechselnden Datensätzen arbeiten, da sie manuelle Fehler und repetitive Aufgaben minimiert.
Weitere integrierte Excel-Methoden – Filtern Sie alle leeren Zellen und identifizieren Sie den vorletzten Wert manuell
Während Formeln und Makros automatisierte Möglichkeiten bieten, um den vorletzten Wert zu ermitteln, bevorzugen Sie manchmal einen schnellen, visuellen Ansatz – besonders bei unregelmäßigen oder nicht zusammenhängenden Daten oder wenn Sie nur gelegentlich einen Wert überprüfen müssen. Mit den integrierten Filterfunktionen von Excel blenden Sie temporär leere oder irrelevante Zellen aus und machen so den gewünschten Eintrag auf einen Blick erkennbar – ganz ohne das Schreiben oder Anpassen von Formeln.
Filtermethode:
- Wählen Sie den Datenbereich für Ihre Spalte oder Zeile aus (für Spalten die Zellen B1:B16, für Zeilen A6:E6 in Ihrem Arbeitsblatt).
- Klicken Sie im Excel-Menüband auf Daten > Filter, um die Filtersymbole zu aktivieren. Klicken Sie dann im Spaltenkopf oder neben Ihrem markierten Bereich auf das Filter-Dropdown-Menü.
- Deaktivieren Sie das Kontrollkästchen (Leer), um leere Zellen auszublenden. Die Datenanzeige listet nun nur nicht leere Werte auf.
- Scrollen Sie in einer Spalte zum Ende der gefilterten Liste und zeigen Sie den vorletzten Wert an. Bei einer Zeile (sofern nach dem Filtern möglich) zählen Sie von rechts, um den vorletzten nicht leeren Eintrag zu ermitteln.
Hinweise und Tipps:
- Filtern ist am schnellsten bei kleinen bis mittelgroßen Listen oder wenn Sie die Position der Werte visuell bestätigen müssen.
- Bei sehr großen Datensätzen kann das Filtern die Leistung beeinträchtigen und ist daher weder ideal für die Automatisierung noch für wiederholte Anwendung.
- Achten Sie bei angewendeten Filtern darauf, den richtigen Kontext im Blick zu behalten – gefilterte Listen können ausgeblendete Zeilen oder Spalten überspringen.
- Um Filter zu entfernen, klicken Sie im Register „Daten“ auf Löschen.
Diese Methode eignet sich weniger für fortlaufende dynamische Analysen oder große Datensätze, ermöglicht jedoch eine transparente, manuelle Prüfung des vorletzten nicht leeren Eintrags – ideal zur schnellen Fehlersuche oder Verifikation.
Verwandte Artikel:
- Wie findet man die Position der ersten bzw. letzten Zahl in einer Textzeichenfolge in Excel?
- Wie findet man in Excel den ersten oder letzten Freitag eines Monats?
- Wie sucht man mit VLOOKUP den ersten, zweiten oder n-ten übereinstimmenden Wert in Excel?
- Wie findet man in Excel den Wert, der in einem Bereich am häufigsten vorkommt?
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