Wie findet man in Excel die erste oder letzte positive bzw. negative Zahl?
Wenn Sie mit einer Spalte arbeiten, die sowohl positive als auch negative Zahlen enthält, müssen Sie oft rasch die erste oder letzte positive bzw. negative Zahl im Bereich ermitteln. Das ist besonders wertvoll für Datenanalyse, Trenderkennung oder das gezielte Identifizieren von Einstiegspunkten in umfangreichen Datensätzen. Eine manuelle Prüfung großer Datenmengen ist zeitaufwendig und fehleranfällig. Zum Glück bietet Excel mehrere effiziente Methoden, um diese Aufgabe zu vereinfachen – so extrahieren Sie die gesuchten Werte präzise per Formel oder Automatisierung. Im Folgenden finden Sie passende Lösungen für verschiedene Szenarien, einschließlich fortschrittlicher Ansätze, die sich ideal für wiederkehrende oder groß angelegte Operationen eignen.
Ermitteln der ersten positiven / negativen Zahl mithilfe einer Matrixformel
Ermitteln der letzten positiven / negativen Zahl mithilfe einer Matrixformel
VBA-Makro zum Finden der ersten / letzten positiven / negativen Zahl
Ermitteln der ersten positiven / negativen Zahl mithilfe einer Matrixformel
Um die erste positive oder negative Zahl aus einer Reihe von Werten zu extrahieren, nutzen Sie Excel-Matrixformeln. Diese Methode ist ideal für Anwender, die eine schnelle Lösung für moderate Datenbereiche benötigen und mit Formeln vertraut sind – besonders in Umgebungen, in denen zusätzliche Add-Ins oder Makros nicht erlaubt sind. Der Matrixansatz aktualisiert sich automatisch, sobald sich Ihre Quelldaten ändern, und eignet sich daher perfekt für dynamische Listen. So setzen Sie das um:
1. Wählen Sie eine leere Zelle aus und geben Sie die folgende Matrixformel ein, um die erste positive Zahl zu ermitteln:
=INDEX(A2:A18,MATCH(TRUE,A2:A18>0,0))Hierbei bezieht sich A2:A18 auf die Datenliste, die Sie durchsuchen möchten. Diese Formel ermittelt die erste Zelle im Bereich, deren Wert größer als 0 ist, und gibt deren Inhalt zurück. Siehe folgender Screenshot:
2. Drücken Sie nach der Eingabe der Formel gleichzeitig Strg + Umschalt + Enter, anstatt nur die Eingabetaste zu drücken. Dadurch wird die Matrixformel korrekt ausgeführt, und die erste positive Zahl Ihrer Liste wird wie im folgenden Beispiel angezeigt:
Tipp:Um stattdessen die erste negative Zahl abzurufen, verwenden Sie einfach diese Formel (denken Sie daran, nach der Eingabe)Strg + Umschalt + Enterzu drücken):
=INDEX(A2:A18,MATCH(TRUE,A2:A18<0,0))In beiden Formeln ermöglicht die Anpassung der Bedingung ()>0 für positive, <0 für negative Zahlen), gezielt den gewünschten Zahlentyp zu finden. Beachten Sie: Matrixformeln unterstützen keine leeren Zellreferenzen. Stellen Sie daher sicher, dass Ihr Datenbereich keine leeren Zellen enthält, um konsistente Ergebnisse zu erhalten. Falls alle Zahlen entweder positiv oder negativ sind, kann die Formel einen Fehler zurückgeben – fügen Sie in diesem Fall eine WENNFEHLER-Funktion hinzu, um Fehler zu unterdrücken und eine benutzerdefinierte Meldung anzuzeigen.
Hinweis: In neueren Excel-Versionen (Office 365 und Excel 2021 und höher) müssen Sie Strg + Umschalt + Enter möglicherweise nicht mehr verwenden – das einfache Drücken der Enter-Taste reicht dank der Unterstützung dynamischer Arrays aus.
Ermitteln der letzten positiven / negativen Zahl mithilfe einer Matrixformel
Wenn Ihr Ziel darin besteht, den letzten positiven oder negativen Wert in einer Spalte zu ermitteln, können Sie eine alternative Matrixformel verwenden. Dieser Ansatz eignet sich hervorragend, um Endtrends schnell zu analysieren oder den aktuellsten Datenpunkt eines bestimmten Typs zu lokalisieren. Beachten Sie, dass diese Methode Datenaktualisierungen dynamisch berücksichtigt – besonders wertvoll, wenn Sie regelmäßig neue Werte an die Liste anhängen.
1. Wählen Sie eine leere Zelle neben Ihrer Datenspalte aus und geben Sie diese Matrixformel ein, um die letzte positive Zahl zu ermitteln:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >0, $A$2:$A$18))Diese Formel nutzt das Verhalten der VERWEIS-Funktion, die bei einer extrem großen Zahl den letzten numerischen Treffer zurückgibt. Dabei filtert WENN($A$2:$A$18>0;$A$2:$A$18) nur die positiven Zahlen, und VERWEIS liefert anschließend das letzte Vorkommen. Das folgende Beispiel veranschaulicht dies:
2. Bestätigen Sie die Formel durch Drücken von Strg + Umschalt + Enter (sofern Ihre Excel-Version keine dynamischen Arrays unterstützt). Das Ergebnis zeigt den letzten positiven Wert im begrenzten Bereich, wie im folgenden Beispiel dargestellt:
Um die letzte negative Zahl zurückzugeben, verwenden Sie stattdessen die folgende Matrixformel, ebenfalls mit Strg + Umschalt + Enter:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 <0, $A$2:$A$18))Wenn weder ein positiver noch ein negativer Wert gefunden wird, gibt die Formel einen Fehler ()#NV) zurück. Um solche Fälle elegant zu behandeln, umschließen Sie die Formel mit WENNFEHLER. Beispiel:
=IFERROR(LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >0, $A$2:$A$18)), "No match found")Vermeiden Sie unbedingt zusammengeführte oder kombinierte Text-/Zahlenformate in Ihrem Bereich, da diese die Berechnungsergebnisse Ihrer Formeln beeinträchtigen können. Stellen Sie stets die Datenintegrität sicher, bevor Sie diese Methoden anwenden – so garantieren Sie optimale Genauigkeit.
VBA-Makro zum Finden der ersten / letzten positiven / negativen Zahl
Wenn Sie regelmäßig die erste oder letzte positive bzw. negative Zahl in mehreren Bereichen oder sehr großen Datensätzen ermitteln müssen, spart Ihnen die Automatisierung dieser Aufgabe mit einem VBA-Makro wertvolle Zeit und minimiert manuelle Fehler. Mit dieser Lösung wählen Sie einfach den gewünschten Bereich aus und erhalten sofort den gesuchten Wert – ideal für Batch-Verarbeitung oder wiederkehrende Analysen. Der VBA-Ansatz eignet sich besonders gut für Szenarien mit komplexen Kriterien oder maßgeschneiderten Workflows, setzt jedoch grundlegende Kenntnisse der Excel-Entwicklertools voraus.
1. Klicken Sie auf Entwicklertools > Visual Basic, um das Fenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie anschließend im VBA-Editor auf Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modul ein:
Sub FindFirstOrLastPosNegNumber()
Dim rng As Range
Dim cell As Range
Dim result As Variant
Dim firstPos As Variant, firstNeg As Variant
Dim lastPos As Variant, lastNeg As Variant
Dim selType As String
On Error Resume Next
Set rng = Application.InputBox("Select the data range", "KutoolsforExcel", Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
selType = Application.InputBox("Type 'FirstPos' for first positive, 'FirstNeg' for first negative, 'LastPos' for last positive, or 'LastNeg' for last negative:", "KutoolsforExcel", "FirstPos", Type:=2)
If selType = "" Then Exit Sub
firstPos = Empty
firstNeg = Empty
lastPos = Empty
lastNeg = Empty
' Find first positive and first negative
For Each cell In rng
If IsNumeric(cell.Value) Then
If firstPos = Empty And cell.Value > 0 Then
firstPos = cell.Value
End If
If firstNeg = Empty And cell.Value < 0 Then
firstNeg = cell.Value
End If
If cell.Value > 0 Then
lastPos = cell.Value
End If
If cell.Value < 0 Then
lastNeg = cell.Value
End If
End If
Next cell
Select Case UCase(selType)
Case "FIRSTPOS"
result = firstPos
Case "FIRSTNEG"
result = firstNeg
Case "LASTPOS"
result = lastPos
Case "LASTNEG"
result = lastNeg
Case Else
result = "Invalid input"
End Select
If IsEmpty(result) Then
MsgBox "No matching value found in the selected range.", vbInformation, "KutoolsforExcel"
Else
MsgBox "Result: " & result, vbInformation, "KutoolsforExcel"
End If
End Sub2. Um das Makro auszuführen, drücken Sie F5(oder klicken Sie auf die Schaltfläche)
Ausführen), und befolgen Sie diese Schritte:
- Ein Dialogfeld fordert Sie auf, Ihren Zahlenbereich (z. B. A2:A18) auszuwählen.
- Geben Sie anschließend Ihren Suchtyp ein: FirstPos für die erste positive Zahl, FirstNeg für die erste negative Zahl, LastPos für die letzte positive Zahl oder LastNeg für die letzte negative Zahl (Groß-/Kleinschreibung wird nicht beachtet).
- Nachdem Sie Ihre Auswahl eingegeben und bestätigt haben, erscheint das Ergebnis in einem Meldungsfeld.
Tipps:
- Dieses Makro verarbeitet jeden vom Benutzer ausgewählten zusammenhängenden Zahlenbereich und bietet so maximale Flexibilität bei der Datenanordnung.
- Wenn der angegebene Typ mit keiner Zahl im Bereich übereinstimmt, erhalten Sie eine Benachrichtigung anstelle einer Fehlermeldung.
- Stellen Sie sicher, dass Makros in Ihrem Excel aktiviert sind, damit der VBA-Code einwandfrei funktioniert.
- Sollten Ihre Daten nicht-numerische Werte enthalten, ignoriert das Makro diese während der Verarbeitung.
Fehlerbehebung und Empfehlungen: Achten Sie bei allen Lösungen darauf, dass Ihre Auswahl den vorgesehenen Bereich vollständig abdeckt und keine Überschriften enthält. Verwenden Sie bei großen Bereichen möglichst begrenzte Zellbereiche, um Berechnungs- oder Leistungsverzögerungen zu vermeiden – besonders bei der Nutzung von Matrixformeln oder Makros.
Wenn Sie diese Aufgabe häufig durchführen oder mehr Flexibilität benötigen, empfiehlt es sich, mehrere Kriterien in Ihr Makro zu integrieren oder eine dedizierte Schaltfläche für schnelleren Zugriff einzurichten. Speichern Sie Ihre Arbeit stets, bevor Sie neue VBA-Skripte testen – und greifen Sie besonders als Programmieranfänger auf Sicherungskopien zurück.
Verwandte Artikel:
Wie findet man den ersten bzw. letzten Wert, der in Excel größer als X ist?
Wie ermittelt man in Excel den höchsten Wert einer Zeile und die dazugehörige Spaltenüberschrift?
Wie findet man in Excel den maximalen oder minimalen Wert basierend auf bestimmten Kriterien?
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