Wie findet man die erste / letzte positive / negative Zahl in Excel?
Wenn Sie mit einer Spalte von Zahlen arbeiten, die sowohl positive als auch negative Werte enthält, müssen Sie oft schnell die erste oder letzte positive oder negative Zahl im Bereich finden. Dies kann besonders hilfreich für die Datenanalyse, Trenderkennung oder das Identifizieren bestimmter Eintrittspunkte in großen Datensätzen sein. Die manuelle Überprüfung großer Datensätze ist ineffizient und fehleranfällig. Glücklicherweise bietet Excel mehrere praktische Methoden, um diese Aufgabe zu vereinfachen, sodass Sie die benötigten genauen Werte mit Formeln oder Automatisierung extrahieren können. Im Folgenden finden Sie verschiedene Lösungen für unterschiedliche Szenarien, einschließlich fortgeschrittener Ansätze, die sich ideal für wiederholte oder großangelegte Operationen eignen.
Finde die erste positive / negative Zahl mit Array-Formel
Finde die letzte positive / negative Zahl mit Array-Formel
VBA-Makro zur Suche der ersten / letzten positiven / negativen Zahl
Finde die erste positive / negative Zahl mit Array-Formel
Um die erste positive oder negative Zahl aus einer Reihe von Werten zu extrahieren, können Sie Array-Formeln in Excel verwenden. Diese Methode ist geeignet für Benutzer, die eine schnelle Lösung für moderate Datenbereiche benötigen und mit Formeln vertraut sind, insbesondere in Umgebungen, in denen zusätzliche Add-ins oder Makros eingeschränkt sind. Der Array-Ansatz aktualisiert sich automatisch, wenn sich Ihre Quelldaten ändern, was ihn ideal für dynamische Listen macht. Hier ist, wie Sie es implementieren können:
1. Wählen Sie eine leere Zelle aus, geben Sie die folgende Array-Formel ein, um die erste positive Zahl zu erhalten:
=INDEX(A2:A18,MATCH(TRUE,A2:A18>0,0))Hier bezieht sich A2:A18 auf die Liste der zu durchsuchenden Daten. Diese Formel findet die erste Zelle im Bereich mit einem Wert größer als 0 und gibt dann den Inhalt dieser Zelle zurück. Siehe folgenden Screenshot:
2. Nachdem Sie die Formel eingegeben haben, drücken Sie gleichzeitig Strg + Umschalt + Enter, anstatt nur Enter zu drücken. Dadurch wird die Array-Formel ordnungsgemäß ausgeführt und gibt die erste positive Zahl aus Ihrer Liste zurück, wie im folgenden Beispiel dargestellt:
Tipp: Um stattdessen die erste negative Zahl abzurufen, verwenden Sie einfach diese Formel (denken Sie daran, nach dem Eintippen Strg + Umschalt + Enter zu drücken):
=INDEX(A2:A18,MATCH(TRUE,A2:A18<0,0))In beiden Formeln ermöglicht das Ändern der Bedingung (>0 für positiv, <0 für negativ) das Zielgerichtete Suchen der gewünschten Zahlentypen. Beachten Sie, dass Array-Formeln keine leeren Zellreferenzen unterstützen, stellen Sie daher sicher, dass Ihr Datenbereich keine leeren Zellen enthält, um konsistente Ergebnisse zu gewährleisten. Wenn alle Zahlen entweder positiv oder negativ sind, kann die Formel einen Fehler zurückgeben – überlegen Sie, eine IFERROR-Funktion hinzuzufügen, wenn Sie Fehler unterdrücken und eine benutzerdefinierte Nachricht anzeigen möchten.
Hinweis: In neueren Versionen von Excel (Office 365 und Excel 2021 und höher) müssen Sie möglicherweise nicht Strg + Umschalt + Enter verwenden; es reicht aus, nur Enter zu drücken, da dynamische Arrays unterstützt werden.
Finde die letzte positive / negative Zahl mit Array-Formel
Wenn Ihr Ziel ist, die letzte positive oder negative Zahl in einer Spalte zu identifizieren, können Sie eine andere Array-Formel verwenden. Dieser Ansatz eignet sich zum schnellen Analysieren von Endtrends oder zum Auffinden des aktuellsten Datenpunkts eines bestimmten Typs. Beachten Sie, dass diese Methode Datenaktualisierungen dynamisch widerspiegelt, was besonders wertvoll ist, wenn Sie regelmäßig neue Zahlen zur Liste hinzufügen.
1. Wählen Sie eine leere Zelle neben Ihrer Datenspalte aus und geben Sie diese Array-Formel ein, um die letzte positive Zahl zu finden:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >0, $A$2:$A$18))Diese Formel funktioniert, indem sie das Verhalten von LOOKUP nutzt, das die letzte numerische Übereinstimmung für eine extrem große Zahl zurückgibt. Hier filtert IF($A$2:$A$18 >0, $A$2:$A$18) nur die positiven Zahlen, und LOOKUP gibt dann das letzte Auftreten zurück. Siehe dies unten illustriert:
2. Bestätigen Sie die Formel, indem Sie Strg + Umschalt + Enter drücken (es sei denn, Ihre Excel-Version unterstützt dynamische Arrays). Das Ergebnis zeigt die letzte positive Zahl im angegebenen Bereich, wie unten dargestellt:
Um die letzte negative Zahl zurückzugeben, verwenden Sie stattdessen die folgende Array-Formel, ebenfalls mit Strg + Umschalt + Enter:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 <0, $A$2:$A$18))Wenn keine positive oder negative Zahl gefunden wird, gibt die Formel einen Fehler (#N/A) zurück. Um solche Fälle elegant zu handhaben, schließen Sie die Formel in IFERROR ein. Zum Beispiel:
=IFERROR(LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >0, $A$2:$A$18)), "No match found")Es ist wichtig, zusammengeführte Zellen oder kombinierte Text-/Zahlenformate in Ihrem Bereich zu vermeiden, da sie die Berechnungsergebnisse der Formel stören können. Überprüfen Sie immer die Integrität Ihrer Daten, bevor Sie diese Methoden für optimale Genauigkeit verwenden.
VBA-Makro zur Suche der ersten / letzten positiven / negativen Zahl
Wenn Sie häufig die erste oder letzte positive oder negative Zahl in mehreren Bereichen oder sehr großen Datensätzen finden müssen, kann die Automatisierung dieser Aufgabe mit einem VBA-Makro erheblich Zeit sparen und manuelle Fehler reduzieren. Diese Lösung ermöglicht es Ihnen, einen ausgewählten Bereich zu durchsuchen und den benötigten Wert sofort abzurufen, was sie ideal für die Batch-Verarbeitung oder wiederholte Analyseaufgaben macht. Der VBA-Ansatz ist besonders hilfreich in Szenarien, in denen komplexe Kriterien oder angepasste Workflows benötigt werden, obwohl er grundlegende Kenntnisse der Entwicklertools von Excel erfordert.
1. Klicken Sie auf Entwickler > Visual Basic, um das Microsoft Visual Basic for Applications-Fenster zu öffnen. Dann klicken Sie im VBA-Editor auf Einfügen > Modul und kopieren Sie den folgenden Code in das neue Modul:
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 
Ausführen Schaltfläche) und folgen Sie diesen Schritten:
- Ein Dialogfeld fordert Sie auf, Ihren Zahlenbereich auszuwählen (z.B., A2:A18).
- Geben Sie als Nächstes Ihren Suchtyp ein: Geben Sie 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 ein (nicht Groß-/Kleinschreibung beachten).
- Nachdem Sie Ihre Wahl getroffen und bestätigt haben, wird das Ergebnis in einem Meldungsfeld angezeigt.
Tipps:
- Dieses Makro kann jeden vom Benutzer ausgewählten zusammenhängenden Zahlenbereich verarbeiten, was Flexibilität bei der Datenanordnung ermöglicht.
- Wenn der angegebene Typ keiner Zahl im Bereich entspricht, erhalten Sie eine Benachrichtigung statt eines Fehlers.
- Stellen Sie sicher, dass Makros in Ihrem Excel aktiviert sind, damit der VBA-Code funktioniert.
- Wenn Ihre Daten nicht-numerische Werte enthalten, ignoriert das Makro diese während der Verarbeitung.
Problembehandlung und Vorschläge: Bestätigen Sie bei allen Lösungen immer, dass Ihre Auswahl den beabsichtigten Bereich enthält und keine Überschriften umfasst. Wenn Sie große Bereiche verwenden, sollten Sie die Größe begrenzen, um Berechnungs- oder Leistungsverzögerungen zu vermeiden, insbesondere bei der Verwendung von Array-Formeln oder Makros.
Wenn Sie diese Aufgabe häufig ausführen oder mehr Anpassungsmöglichkeiten wünschen, sollten Sie mehrere Kriterien in Ihrem Makro kombinieren oder eine dedizierte Schaltfläche für leichteren Zugriff erstellen. Speichern Sie Ihre Arbeit immer vor dem Ausprobieren neuer VBA-Skripte und testen Sie diese auf Sicherungskopien, wenn Sie neu in der Programmierung sind.
Verwandte Artikel:
Wie findet man den ersten / letzten Wert größer als X in Excel?
Wie findet man den höchsten Wert in einer Zeile und gibt die Spaltenüberschrift in Excel zurück?
Wie findet man den höchsten Wert und gibt den angrenzenden Zellenwert in Excel zurück?
Wie findet man den Maximal- oder Minimalwert basierend auf Kriterien in Excel?
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins