Wie findet und ersetzt man Werte, die größer/kleiner als ein bestimmter Wert in Excel sind?

Das Arbeiten mit großen Datensätzen in Excel erfordert oft, dass Sie Zellen identifizieren und ersetzen, die bestimmte Kriterien erfüllen, wie z.B. Werte, die größer oder kleiner als eine bestimmte Schwelle sind. Zum Beispiel müssen Sie möglicherweise alle Zahlen über 500 durch 0 ersetzen oder alle Werte unterhalb eines Leistungsstandards durch eine Warnmeldung ersetzen. Im Gegensatz zum Standard-Suchen-und-Ersetzen-Werkzeug – das nur exakte oder teilweise Text-/Zahlenübereinstimmungen findet – erfordern bedingte Ersetzungen basierend auf numerischen Vergleichen alternative Ansätze. Dieses Tutorial skizziert mehrere praktische Methoden, um diese Szenarien effizient zu handhaben und Ihnen Zeit zu sparen sowie manuelle Fehler zu minimieren.

Finde und ersetze Werte, die größer/kleiner als ein bestimmter Wert mit VBA-Code sind

Finde und ersetze Werte, die größer/kleiner als ein bestimmter Wert mit Kutools für Excel sind

Excel-Formel - Verwenden Sie die WENN-Funktion in einer Hilfsspalte, um Werte zu ersetzen, die größer oder kleiner als eine Schwelle sind

Andere integrierte Excel-Methoden - Filtern/Sortieren und Ersetzen

Finde und ersetze Werte, die größer/kleiner als ein bestimmter Wert mit VBA-Code sind

Stellen Sie sich zum Beispiel vor, dass Sie schnell alle Werte in Ihrem Datensatz finden möchten, die größer als 500 sind, und sie alle in einer einzigen Operation durch 0 ersetzen wollen. Dies ist ein häufiger Bedarf bei Notenanpassungen, Konformitätsmarkierungen oder Datenbereinigungen. Mit VBA können Sie diesen gesamten Prozess automatisieren und wiederholte manuelle Bearbeitungen vermeiden.

Die folgende VBA-Lösung ermöglicht es Ihnen, alle Zellenwerte, die größer oder kleiner als eine bestimmte Zahl sind, auf einmal zu ersetzen. Sie können den Vergleichswert und das Ersetzungsergebnis je nach Fall anpassen:

1. Halten Sie die Tasten ALT + F11 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 Modulfenster ein.

VBA-Code: Finde und ersetze 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 dann die F5-Taste, um diesen Code auszuführen. Wenn Sie dazu aufgefordert werden, wählen Sie den Datenbereich aus, in dem Sie Werte finden und ersetzen möchten. (Die Auswahl der relevanten Daten hilft, unbeabsichtigte Ersetzungen in nicht zusammenhängenden Zellen zu vermeiden.)

4. Klicken Sie im Dialogfeld auf OK. Der Code wird automatisch Ihren ausgewählten Bereich durchsuchen und alle Werte, die größer als 500 sind, durch 0 ersetzen (oder wie sonst angegeben).

Hinweise und Tipps:

• Sie können die Schwelle und den Ersetzungswert anpassen, indem Sie diese Zeilen im Code ändern:
  If Rng.Value >500 Then
  Rng.Value =0
• Dieser Code ändert nur Zahlen. Wenn Ihre Daten leere Zellen oder nicht-numerische Einträge enthalten, bleiben diese unverändert.
• Bevor Sie VBA ausführen, sollten Sie eine Sicherungskopie Ihrer Datei speichern, falls Sie die Änderungen rückgängig machen möchten.
• Wenn Sie eine Makro-Sicherheitswarnung erhalten, stellen Sie sicher, dass Makros für diese Arbeitsmappe aktiviert sind.

Finde und ersetze Werte, die größer/kleiner als ein bestimmter Wert mit Kutools für Excel sind

Wenn Sie keine Erfahrung mit VBA oder Programmierung haben, bietet Kutools für Excel eine grafische Möglichkeit, dieses Problem zu lösen. Seine Funktion 'Bestimmte Zellen auswählen' ermöglicht es Ihnen, alle Zellen, die Ihre Bedingungen erfüllen, genau zu lokalisieren und deren Inhalte auf einmal zu ersetzen, wodurch Benutzerfehler minimiert und die Datenbereinigung beschleunigt wird.

Nach der Installation von Kutools für Excel folgen Sie den untenstehenden Schritten:

1. Wählen Sie den Datenbereich aus, den Sie bearbeiten 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':

1. Wählen Sie Zelle für den Auswahltyp.
2. Wählen Sie Größer als (oder Kleiner als, je nach Bedarf) aus dem Typ auswählen.
3. Geben Sie Ihren Schwellenwert in das angrenzende 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 Ersetzungswert ein und drücken Sie gleichzeitig Strg + Enter; jeder ausgewählte Wert wird sofort aktualisiert.

Zusätzliche Tipps:

• Sie können andere Kriterien wie Kleiner als, Gleich oder Enthält verwenden, je nach Bedarf.
• Um versehentliche Ersetzungen zu vermeiden, überprüfen Sie Ihre Auswahl doppelt, bevor Sie Strg + Enter drücken.

Kutools für Excel jetzt herunterladen und kostenlos testen!

Excel-Formel - Verwenden Sie die WENN-Funktion in einer Hilfsspalte, um Werte zu ersetzen, die größer oder kleiner als eine Schwelle sind

Dieser Ansatz nutzt die integrierte WENN-Funktion in Excel, um eine Hilfsspalte zu erstellen, was es einfach macht, visuell zu überprüfen und dann bedingte Ersetzungen anzuwenden. Es ist besonders nützlich, wenn Sie mehr Transparenz bevor Sie Ihre Originaldaten überschreiben möchten, oder wenn Sie eine nicht-destruktive Methode benötigen, um zu sehen, wie die Ergebnisse aussehen werden. Sie können auch die Ersetzungslogik für verschiedene Szenarien wie Berichterstattung, Kategorisierung oder Markierung von Ausreißern anpassen.

1. Fügen Sie eine neue Spalte neben Ihren Daten ein (z.B., wenn Ihre Daten in Spalte A sind, fügen Sie eine neue Spalte B ein).

2. Geben Sie in der ersten Zelle der Hilfsspalte (z.B., B2) die folgende Formel ein, um alle Werte, die größer als 500 sind, durch 0 zu ersetzen:

=IF(A2>500,0,A2)

Wenn Sie Werte ersetzen möchten, die kleiner als eine Schwelle sind (z.B., kleiner als 200), verwenden Sie:

=IF(A2<200,0,A2)

Sie können 500 oder 200 und 0 durch beliebige Schwellenwerte und Ersetzungswerte ersetzen, die Ihren Anforderungen entsprechen. Die A2-Referenz sollte basierend auf Ihrem tatsächlichen Datenbereich angepasst werden.

3. Drücken Sie nach dem Eingeben der Formel die Eingabetaste. Kopieren Sie dann die Formel nach unten in den Rest der Hilfsspalte (ziehen Sie den Ausfüllknauf nach unten oder doppelklicken Sie darauf).

4. Sobald Sie bestätigt haben, dass die Hilfsspalte die gewünschten Ergebnisse liefert, wählen Sie die neuen Daten aus und kopieren Sie sie, 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 zu erkennen und zu überprüfen, bevor die Originaldaten ersetzt werden, was das Risiko reduziert.
• Seien Sie vorsichtig mit Zellbezügen, wenn Sie Formeln auf nicht zusammenhängende Bereiche anwenden – stellen Sie sicher, dass die richtige Ausrichtung erfolgt.
• Dieser Ansatz bewahrt Ihre Originaldaten, bis Sie die Überprüfung abgeschlossen haben und sich entscheiden, sie zu überschreiben.
• Bei sehr großen Datensätzen kann die Verwendung von Formeln langsamer sein als VBA oder Kutools, aber sicherer für die Überprüfung von Datenänderungen.

Andere integrierte Excel-Methoden - Filtern und Ersetzen

Das Filtern kann helfen, visuell alle Werte auszuwählen, die größer oder kleiner als Ihre spezifischen Anforderungen sind, sodass Sie dann alle relevanten Zellen schnell mit der Standard-Excel-Bearbeitung ersetzen können. Diese Methode ist flexibel und erfordert keine Formeln oder Codes, was sie ideal für diejenigen macht, die lieber direkt mit Excels Interface arbeiten, insbesondere für einmalige oder visuelle Aufgaben.

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), geben Sie dann den Schwellenwert ein (z.B., 500).

3. Excel zeigt nur die Zeilen an, die Ihren Filterkriterien entsprechen. Wählen Sie alle sichtbaren gefilterten Zellen in Ihrer Spalte aus.

4. Geben Sie den Ersetzungswert ein (z.B., 0) und drücken Sie Strg + Enter – Excel wird nur die aktuell sichtbaren (gefilterten) Zellen überschreiben.

5. Deaktivieren Sie den Filter, um Ihr endgültiges Dataset zu sehen und zu überprüfen.

Tipps, Vor- und Nachteile:

• Filtern und Ersetzen ist unkompliziert und ideal für moderate Datensätze, bei denen Sie eine visuelle Bestätigung benötigen, welche Zellen geändert wurden.
• Für Spalten, die Formeln enthalten, wird diese Methode Formeln überschreiben und möglicherweise zerstören; verwenden Sie sie mit Vorsicht.
• Wenn Sie versehentlich den falschen Bereich ausgewählt und Änderungen vorgenommen haben, drücken Sie Strg + Z, um rückgängig zu machen, passen Sie dann Ihre Auswahl oder Filterkriterien an und versuchen Sie es erneut.

Verwandte Artikel:

Wie findet und ersetzt man eine genaue Übereinstimmung in Excel?

Wie ersetzt man Text durch entsprechende Bilder in Excel?

Wie findet und ersetzt man die Füllfarbe in Excel?