Wie findet man in Excel schnell den größten Wert, der kleiner als X ist?
Bei alltäglichen Excel-Aufgaben ist es einfach, den größten Wert innerhalb eines Datensatzes mithilfe grundlegender Funktionen zu ermitteln. Sobald jedoch die Anforderung besteht, den größten Wert zu finden, der gleichzeitig kleiner als ein bestimmter Schwellenwert ist – beispielsweise „der größten Wert unter 100“ – steigt die Komplexität deutlich an, insbesondere bei großen Bereichen oder dynamischen Bedingungen. Dieses Szenario tritt häufig auf, wenn Datenbegrenzungen festgelegt, Leistungsdaten relativ zu einem Mindestschwellenwert analysiert oder Werte für Berichtszwecke gefiltert werden müssen. Zum Glück bietet Excel mehrere effiziente und zuverlässige Ansätze, um diese Anforderung zu meistern. In diesem Leitfaden führen wir Sie Schritt für Schritt durch praktikable Methoden, mit denen Sie schnell den größten Wert ermitteln, der unter einer vorgegebenen Zahl liegt – inklusive typischer Anwendungsfälle, nützlicher Tipps und hilfreicher Hinweise zur Fehlerbehebung.
Größten Wert finden, der kleiner als X ist, mithilfe von Formeln
Größten Wert finden, der kleiner als X ist, mit Kutools für Excel
Größten Wert finden, der kleiner als X ist, mithilfe von VBA-Code
Größten Wert finden, der kleiner als X ist, mithilfe von Formeln
Excel-Formeln ermöglichen es Ihnen, schnell den größten Wert unter einer bestimmten Zahl – beispielsweise 100 – innerhalb eines begrenzten Bereichs (z. B. A1:E7) zu ermitteln. Diese Lösung eignet sich ideal für statische Datensätze oder für Benutzer, die direkt mit Tabellenfunktionen arbeiten möchten. Sie ist präzise und erfordert weder Add-Ins noch fortgeschrittene Kenntnisse.
1. Klicken Sie auf eine leere Zelle, in der das Ergebnis angezeigt werden soll, und geben Sie die folgende Formel ein. In diesem Beispiel suchen wir den größten Wert im Bereich A1:E7, der kleiner als 100 ist:
=MAX(IF(A1:E7<100,A1:E7,""))Tipp: Dies ist eine Matrixformel. In Excel-Versionen vor Office 365 müssen Sie Strg + Umschalt + Enter drücken, um sie zu aktivieren – dann erscheinen geschweifte Klammern {} um die Formel. In neueren Excel-Versionen (Office 365 und später) genügt ein einfacher Druck auf Enter.
 |
 |
 |
Falls keine Werte unterhalb des festgelegten Schwellenwerts liegen (z. B. wenn alle Daten größer oder gleich 100 sind), wird null als Ergebnis zurückgegeben. In solchen Fällen können Sie stattdessen die folgende Formel verwenden, um „Keine Übereinstimmung“ anzuzeigen:
=IF(SUMPRODUCT(--(A1:E7<100))=0, "No match", MAX(IF(A1:E7<100,A1:E7)))Nachdem Sie diese Formel in die Zielszelle eingegeben haben, drücken Sie Strg + Umschalt + Enter, um die größte Zahl zurückzugeben, die kleiner als 100 ist. Um sie auf andere Bereiche anzuwenden, passen Sie einfach die Zellbezüge innerhalb der Formel an.
Vorteile: Schnell und flexibel – ideal für einmalige Abfragen oder einfache Bedingungen.
Nachteile: Bei wechselnden Kriterien oder dynamischen Bereichen sind möglicherweise Anpassungen der Formel nötig. Matrixformeln wirken für Anfänger oft weniger intuitiv.
Praktische Tipps: Verwenden Sie Zellbezüge oder benannte Bereiche statt fester Werte, um mehr Flexibilität zu gewinnen. Ersetzen Sie beispielsweise den Wert 100 durch eine Zellreferenz (z. B. $G$1), um den Schwellenwert dynamisch anzupassen.
Fehlerbehebung: Sollte ein #WERT!-Fehler auftreten, stellen Sie sicher, dass Ihr Bereich ausschließlich Zahlen enthält und der Schwellenwert korrekt angegeben ist. Beachten Sie: Bei großen Bereichen können Matrixformeln die Leistung Ihrer Arbeitsmappe beeinträchtigen.
Größten Wert finden, der kleiner als X ist, mit Kutools für Excel
Kutools für Excel bietet erweiterte Formelwerkzeuge, die Ihren Arbeitsablauf optimieren – besonders dann, wenn Standardformeln zu komplex oder schwer zu merken sind. Mit der integrierten Formelhelfer-Funktion richten Sie suchkriterienbasierte Abfragen per einfacher Mausaktion ein, sodass manuelle Formeleingaben entfallen und das Fehler risiko sinkt. Ideal für alle, die regelmäßig Parameter anpassen oder große Tabellen effizient bearbeiten müssen.
Nachdem Sie kostenlos installiert habenKutools für Excel, führen Sie bitte die folgenden Schritte aus:
1. Klicken Sie auf die Zelle, in der der gefundene Wert angezeigt werden soll, und navigieren Sie dann zu Kutools > Formelhelfer > Formelhelfer.
2. Im Formelhelfer-Dialog:
1) Wählen Sie im Lookup aus dem Dropdown-Menü Formeltyp aus;
2) Wählen Sie in Wählen Sie eine Formel aus die Option Den maximalen Wert finden, der kleiner als ein angegebener Wert in einem Bereich ist aus;
3) Geben Sie für Bereich Ihren Datenbereich an. Geben Sie in Maximaler Wert die Schwellenwertzahl ein.
3. Klicken Sie auf OK. Kutools gibt sofort den größten Wert zurück, der kleiner als Ihre angegebene Zahl ist.
Dieser Ansatz erfordert keine speziellen Formelkenntnisse und ermöglicht flexible Anpassungen der Parameter. Bei wiederholten Analysen spart Kutools im Vergleich zur manuellen Eingabe erheblich Zeit.
Vorteile: Ideal für häufige oder komplexe Abfragen; vermeidet Formelfehler und ist besonders benutzerfreundlich für nicht-technische Anwender.
Nachteile: Erfordert die Installation von Kutools und ist in einer Standard-Excel-Umgebung nicht verfügbar.
Hinweis: Wählen Sie den Bereich sorgfältig aus und prüfen Sie die Eingabewerte, um logische Fehler zu vermeiden. Falls die Daten leere Zellen oder nicht-numerische Inhalte enthalten, überprüfen Sie die Ausgabe auf Richtigkeit.
Praktischer Tipp: Benannte Bereiche erleichtern die Auswahl in Kutools-Dialogen – besonders bei großen Tabellen.
Fehlerbehebung: Falls keine Ergebnisse angezeigt werden, überprüfen Sie Ihre Eingabekriterien, stellen Sie sicher, dass Kutools ordnungsgemäß installiert ist, und vergewissern Sie sich, dass Ihr Datenbereich numerische Werte enthält, die unterhalb des Schwellenwerts liegen.
Größten Wert finden, der kleiner als X ist, mithilfe von VBA-Code
Mit Excel-VBA (Visual Basic for Applications) können Sie automatisch einen Bereich durchlaufen und den maximalen Wert ermitteln, der kleiner als ein vorgegebener Schwellenwert ist. VBA eignet sich besonders für folgende Situationen:
- Sie müssen dynamische Bereiche verwalten, deren Größe sich ändern kann
- Sie möchten den Suchvorgang automatisieren, ohne komplexe Matrixformeln zu verwenden
- Sie müssen diese Analyse wiederholt durchführen oder die Lösung in einen Arbeitsablauf einbetten
Diese Methode ist ideal für Power-User oder alle, die regelmäßig aktualisierte Datensätze verwalten. Die folgenden Schritte zeigen Ihnen, wie Sie die VBA-Lösung einrichten und ausführen.
1. Gehen Sie in Excel zur Registerkarte Entwicklertools, klicken Sie auf Visual Basic, um den Editor Microsoft Visual Basic for Applications zu öffnen. Klicken Sie auf Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modul ein:
Sub FindLargestLessThanX()
Dim WorkRng As Range
Dim xCell As Range
Dim xMax As Double
Dim xThreshold As Double
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to search", xTitleId, WorkRng.Address, Type:=8)
xThreshold = Application.InputBox("Enter the threshold value", xTitleId, "", Type:=1)
xMax = -1E+308 ' Initialize to very small number
For Each xCell In WorkRng
If IsNumeric(xCell.Value) Then
If xCell.Value < xThreshold And xCell.Value > xMax Then
xMax = xCell.Value
End If
End If
Next
If xMax = -1E+308 Then
MsgBox "No value found less than " & xThreshold, vbInformation
Else
MsgBox "Largest value less than " & xThreshold & " is: " & xMax, vbInformation
End If
End Sub2. Um den Code auszuführen, klicken Sie auf die Schaltfläche 
oder drücken Sie F5. Anschließend erscheint ein Dialogfeld, in dem Sie zunächst einen Datenbereich auswählen und danach den Schwellenwert eingeben (z. B. 100). Das Ergebnis wird in einer Meldungsbox angezeigt.
Vorteile: Ideal für Automatisierung und dynamische Tabellen – passt sich sich ändernden Bereichen automatisch an, ohne dass manuelle Anpassungen nötig sind.
Nachteile: Erfordert die Aktivierung von Makros; einige Benutzer könnten mit der VBA-Einrichtung nicht vertraut sein.
Hinweis: Stellen Sie sicher, dass Makros in Ihrer Excel-Umgebung aktiviert sind. Führen Sie VBA-Code nur in vertrauenswürdigen Arbeitsmappen aus.
Tipp: Sie können das VBA-Skript so anpassen, dass der Ergebniswert nicht in einer Meldungsbox angezeigt, sondern direkt in eine festgelegte Zelle des Arbeitsblatts geschrieben wird – ideal für die weitere Verarbeitung.
Fehlerbehebung: Sollte der Code nicht ausgeführt werden, überprüfen Sie, ob Makros aktiviert sind, ob Ihre Eingaben dem korrekten Format entsprechen und ob die ausgewählten Daten ausschließlich numerische Werte enthalten.
Dieser VBA-Ansatz vereinfacht die Aufgabenerledigung bei dynamisch aktualisierten Daten, automatisiert wiederkehrende Abläufe und umgeht Einschränkungen von Formeln in sehr großen Bereichen.
Verwandte Artikel:
- Wie extrahiert man alle Duplikate aus einer Spalte in Excel?
- Wie zählt man die Anzahl der farblich markierten Zellen in Excel?
- Wie maximiert man alle Spalten in Excel?
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