Wie ersetzt man mithilfe von „Suchen und Ersetzen“ Werte, die größer oder kleiner als ein bestimmter Wert in Excel sind?
Bei der Arbeit mit großen Datensätzen in Excel müssen Sie häufig Zellen identifizieren und ersetzen, die bestimmte Kriterien erfüllen – beispielsweise Werte, die größer oder kleiner als ein vorgegebener Schwellenwert sind. So könnten Sie etwa alle Zahlen über 500 durch 0 ersetzen oder Werte unterhalb eines Leistungsstandards durch eine Warnmeldung austauschen. Im Gegensatz zum Standard-Tool „Suchen und Ersetzen“, das nur exakte oder teilweise Übereinstimmungen bei Text oder Zahlen findet, erfordern bedingte Ersetzungen auf Basis numerischer Vergleiche alternative Methoden. Dieses Tutorial stellt mehrere praktische Verfahren vor, um solche Szenarien effizient zu meistern und manuelle Fehler sowie Zeitverluste zu minimieren.
Weitere integrierte Excel-Methoden – Filtern/Sortieren und Ersetzen
Suchen und Ersetzen Werte, die größer/kleiner als ein bestimmter Wert sind, mithilfe von VBA-Code ersetzen
Angenommen, Sie möchten alle Werte in Ihrem Datensatz, die größer als 500 sind, schnell identifizieren und in einem einzigen Schritt auf 0 setzen. Diese Anforderung tritt häufig bei Notenanpassungen, der Kennzeichnung von Compliance-Ergebnissen oder der Datenbereinigung auf. Mit VBA automatisieren Sie diesen gesamten Prozess – und sparen sich so zeitaufwändige manuelle Bearbeitungen.
Die folgende VBA-Lösung ermöglicht es Ihnen, alle Zellwerte, die größer oder kleiner als ein bestimmter Vergleichswert sind, gleichzeitig durch einen von Ihnen festgelegten Ersatzwert zu ersetzen – ganz nach Ihren Anforderungen.
1. Halten Sie die ALT + F11-Tasten gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modul-Fenster ein.
VBA-Code: Suchen und Ersetzen Werte, die größer oder kleiner als ein bestimmter Wert sind
Sub FindReplace()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value > 500 Then
Rng.Value = 0
End If
Next
End Sub
3. Drücken Sie anschließend die F5-Taste, um diesen Code auszuführen. Wählen Sie bei der Aufforderung den Datenbereich aus, in dem Sie Werte suchen und ersetzen möchten. (Die Auswahl nur der relevanten Daten hilft dabei, unbeabsichtigte Ersetzungen in nicht betroffenen Zellen zu vermeiden.)
4. Klicken Sie im Dialogfeld auf OK. Anschließend durchsucht der Code automatisch Ihren ausgewählten Bereich und ersetzt alle Werte, die größer als 500 sind, durch 0 (oder einen anderen festgelegten Wert).
Hinweise und Tipps:
- Sie können den Schwellenwert und den Ersatzwert anpassen, indem Sie diese Zeilen im Code ändern:
If Rng.Value >500Then
Rng.Value =0 - Dieser Code ändert ausschließlich Zahlen – leere Zellen oder nicht-numerische Einträge in Ihren Daten bleiben unberührt.
- Bevor Sie VBA ausführen, empfiehlt es sich, eine Sicherungskopie Ihrer Datei zu speichern – falls Sie die Änderungen später rückgängig machen möchten.
- Sollte eine Makrosicherheitsabfrage erscheinen, stellen Sie sicher, dass Makros für diese Arbeitsmappe aktiviert sind.
Suchen und Ersetzen Werte, die größer/kleiner als ein bestimmter Wert sind, mithilfe von Kutools für Excel ersetzen
Falls Sie keine Erfahrung mit VBA oder Programmierung haben, bietet Kutools für Excel eine grafische Lösung für dieses Problem: Mit dem Bestimmte Zellen auswählen-Tool identifizieren Sie präzise alle Zellen, die Ihren Bedingungen entsprechen, und ersetzen deren Inhalte sofort – so minimieren Sie Benutzerfehler und beschleunigen die Datenbereinigung.
Nach der Installation von Kutools für Excelführen Sie die folgenden Schritte aus:
1. Wählen Sie den Datenbereich aus, den Sie verarbeiten möchten.
2. Gehen Sie zu Kutools > Auswählen > Bestimmte Zellen auswählen, um das Dialogfeld „Bestimmte Zellen auswählen“ zu öffnen.
3. Im Dialogfeld Bestimmte Zellen auswählen:
- Wählen Sie die Zelle für den Auswahltyp.
- Wählen Sie Größer als(oder)Kleiner als, je nach Bedarf) unter Typ angeben aus.
- Geben Sie Ihren Schwellenwert in das danebenliegende Feld ein (z. B. 500).
4. Klicken Sie auf OK. Alle Zellen, die Ihren Kriterien entsprechen, werden sofort hervorgehoben. Geben Sie nun den gewünschten Ersatzwert ein und drücken Sie gleichzeitig Strg + Enter – jeder ausgewählte Wert wird umgehend aktualisiert.
 |  |  |
Zusätzliche Tipps:
- Sie können auch andere Kriterien wie Kleiner als, Gleich oder Enthält verwenden – ganz nach Ihren Anforderungen.
- Um versehentliche Ersetzungen zu vermeiden, überprüfen Sie Ihre Auswahl sorgfältig, bevor Sie Strg + Enter drücken.
Laden Sie Kutools für Excel jetzt herunter und testen Sie es kostenlos!
Excel-Formel – Verwenden Sie die WENN-Funktion in einer Hilfsspalte, um Werte zu ersetzen, die über oder unter einem Schwellenwert liegen
Dieser Ansatz nutzt die integrierte WENN-Funktion von Excel, um eine Hilfsspalte zu erstellen – so können Sie bedingte Ersetzungen visuell prüfen und anschließend sicher anwenden. Diese Methode ist ideal, wenn Sie mehr Transparenz brauchen, bevor Originaldaten überschrieben werden, oder wenn Sie das Ergebnis nicht-destruktiv im Voraus testen möchten. Zudem lässt sich die Ersetzungslogik flexibel an verschiedene Szenarien anpassen – etwa für Berichterstattung, Kategorisierung oder das Hervorheben von Ausreißern.
1. Fügen Sie eine neue Spalte direkt neben Ihren Daten ein (z. B. Spalte B, wenn sich Ihre Daten in Spalte A befinden).
2. Geben Sie in die erste Zelle der Hilfsspalte (z. B. B2) die folgende Formel ein, um alle Werte größer als 500 durch 0 zu ersetzen:
=IF(A2>500,0,A2)Wenn Sie Werte ersetzen möchten, die unter einem bestimmten Schwellenwert liegen (z. B. kleiner als 200), verwenden Sie:
=IF(A2<200,0,A2)Sie können 500, 200 und 0 durch beliebige Schwellen- und Ersatzwerte ersetzen, die Ihren Anforderungen entsprechen. Passen Sie den Bezug A2 entsprechend Ihrem tatsächlichen Datenbereich an.
3. Drücken Sie nach der Eingabe der Formel Enter. Anschließend kopieren Sie die Formel in die restlichen Zellen der Hilfsspalte – ziehen Sie dazu das Ausfüllkästchen nach unten oder doppelklicken Sie es.
4. Sobald Sie bestätigt haben, dass die Hilfsspalte das gewünschte Ergebnis liefert, markieren und kopieren Sie die neuen Daten, klicken Sie dann mit der rechten Maustaste auf den ursprünglichen Datenbereich und wählen Sie Inhalte einfügen > Werte, um die Originaldaten mit den berechneten Ergebnissen zu überschreiben.
Tipps und Vorsichtsmaßnahmen:
- Formeln in Hilfsspalten erleichtern es, Änderungen vor der Ersetzung der Originaldaten zu erkennen und zu prüfen – und senken so das Risiko.
- Achten Sie bei der Anwendung von Formeln auf nicht zusammenhängende Bereiche darauf, korrekte Zellbezüge zu verwenden und eine präzise Ausrichtung sicherzustellen.
- Mit diesem Ansatz bleiben Ihre Originaldaten erhalten, bis Sie die Überprüfung abgeschlossen und sich entschieden haben, sie zu überschreiben.
- Bei großen Datensätzen mag die Verwendung von Formeln langsamer sein als VBA oder Kutools – sie bietet jedoch mehr Sicherheit bei der Überprüfung von Datenänderungen.
Weitere integrierte Excel-Methoden – Filtern und Ersetzen
Das Filtern hilft Ihnen, visuell alle Werte auszuwählen, die größer oder kleiner als Ihre spezifische Vorgabe sind – und so sämtliche relevanten Zellen im Anschluss mithilfe der bewährten Excel-Bearbeitungsfunktionen blitzschnell zu ersetzen. Diese Methode ist besonders flexibel, kommt ganz ohne Formeln oder Code aus und eignet sich ideal für Anwender, die bei einmaligen oder visuell orientierten Aufgaben direkt in der Excel-Oberfläche arbeiten möchten.
1. Wählen Sie Ihren Datenbereich aus und aktivieren Sie den Filter, indem Sie auf Daten > Filter klicken.
2. Klicken Sie auf den Dropdown-Pfeil in der Spalte, die Sie auswerten möchten. Wählen Sie Zahlenfilter > Größer als(oder)Kleiner als), und geben Sie anschließend den Schwellenwert ein (z. B. 500).
3. Excel zeigt nun nur die Zeilen an, die Ihren Filterbedingungen entsprechen. Markieren Sie alle sichtbaren, gefilterten Zellen in Ihrer Spalte.
4. Geben Sie den Ersatzwert ein (z. B. 0) und drücken Sie Strg + Enter – Excel überschreibt dann ausschließlich die aktuell sichtbaren (gefilterten) Zellen.
5. Deaktivieren Sie den Filter, um Ihren endgültigen Datensatz anzuzeigen und zu prüfen.
Tipps, Vor- und Nachteile:
- Filtern und Ersetzen ist einfach und ideal für mittelgroße Datensätze, bei denen Sie visuell bestätigen möchten, welche Zellen geändert werden.
- Bei Spalten mit Formeln kann diese Methode diese überschreiben und beschädigen – verwenden Sie sie daher mit Vorsicht.
- Falls Sie versehentlich den falschen Bereich ausgewählt und Änderungen vorgenommen haben, drücken Sie Strg + Z, um diese rückgängig zu machen, passen Sie anschließend Ihre Auswahl oder Filterbedingungen an und versuchen Sie es erneut.
Verwandte Artikel:
Wie führt man in Excel eine Suche und Ersetzung mit exakter Übereinstimmung durch?
Wie ersetzt man Text in Excel durch passende Bilder?
Wie führt man eine Suche und Ersetzung nach Füllfarben in Excel durch?
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