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

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 ein bestimmter Schwellenwert sind. Zum Beispiel müssen Sie möglicherweise alle Zahlen über 500 durch 0 ersetzen oder alle Werte unter einem Leistungsstandard durch eine Warnmeldung ersetzen. Im Gegensatz zum Standard-Such- und Ersetzungstool – das nur exakte oder teilweise Text-/Zahlenübereinstimmungen findet – erfordern bedingte Ersetzungen basierend auf numerischen Vergleichen alternative Ansätze. Dieses Tutorial zeigt mehrere praktische Methoden zur effizienten Handhabung dieser Szenarien, um Zeit zu sparen und manuelle Fehler zu minimieren.

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

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

Excel-Formel - Verwenden Sie die IF-Funktion in einer Hilfsspalte, um Werte zu ersetzen, die größer oder kleiner als ein Schwellenwert sind

Andere integrierte Excel-Methoden - Filtern/Sortieren und Ersetzen

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

Angenommen, Sie möchten schnell alle Werte in Ihrem Datensatz finden, die größer als 500 sind, und diese in einer einzigen Operation durch 0 ersetzen. Dies ist ein häufiger Bedarf bei der Notenkorrektur, der Markierung von Konformitätsergebnissen oder bei der Datenbereinigung. 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 die Ersetzung je nach Bedarf anpassen:

1. Halten Sie die Tasten ALT + F11 gedrückt, um das Microsoft Visual Basic for Applications-Fenster 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 nur relevanter Daten hilft, unbeabsichtigte Ersetzungen in nicht zusammenhängenden Zellen zu vermeiden.)

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

Hinweise und Tipps:

• Sie können den Schwellenwert 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 dieses Arbeitsblatt aktiviert sind.

Finde und ersetze Werte, die größer oder 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 Ihren Bedingungen entsprechen, genau zu identifizieren 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 unten stehenden 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 spezifischen Typ.
3. Geben Sie Ihren Schwellenwert in das benachbarte 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 verwenden, wie Kleiner als, Gleich oder Enthält, je nach Ihren Anforderungen.
• Um versehentliche Ersetzungen zu vermeiden, überprüfen Sie Ihre Auswahl doppelt, bevor Sie Strg + Enter drücken.

Jetzt Kutools für Excel herunterladen und kostenlos testen!

Excel-Formel - Verwenden Sie die IF-Funktion in einer Hilfsspalte, um Werte zu ersetzen, die größer oder kleiner als ein Schwellenwert sind

Dieser Ansatz nutzt die integrierte IF-Funktion von Excel, um eine Hilfsspalte zu erstellen, was es einfach macht, visuell zu überprüfen und dann bedingte Ersetzungen vorzunehmen. Es ist besonders nützlich, wenn Sie mehr Transparenz vor dem Überschreiben Ihrer Originaldaten benötigen oder wenn Sie eine nicht-destruktive Methode zum Testen der Ergebnisse brauchen. Sie können auch die Ersetzungslogik für verschiedene Szenarien wie Berichterstattung, Kategorisierung oder das Kennzeichnen von Ausreißern anpassen.

1. Fügen Sie neben Ihren Daten eine neue Spalte 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 ein Schwellenwert sind (z. B. kleiner als 200), verwenden Sie:

=IF(A2<200,0,A2)

Sie können 500 oder 200 und 0 durch einen beliebigen Schwellenwert und Ersetzungswert ersetzen, um Ihren Bedarf zu decken. Die Referenz A2 sollte basierend auf Ihrem tatsächlichen Datenbereich angepasst werden.

3. Drücken Sie die Eingabetaste, nachdem Sie die Formel eingegeben haben. Kopieren Sie dann die Formel 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 das gewünschte Ergebnis 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:

• Hilfsspaltenformeln erleichtern das Erkennen und Überprüfen der Änderungen, bevor die Originaldaten ersetzt werden, wodurch das Risiko reduziert wird.
• Seien Sie vorsichtig mit Zellbezügen, wenn Sie Formeln auf nicht zusammenhängende Bereiche anwenden – stellen Sie sicher, dass die richtige Ausrichtung gegeben ist.
• Dieser Ansatz bewahrt Ihre Originaldaten, bis Sie die Überprüfung abgeschlossen haben und sich entscheiden, sie zu überschreiben.
• Wenn Sie große Datensätze haben, kann die Verwendung von Formeln langsamer sein als VBA oder Kutools, aber sicherer, um Datenänderungen zu überprüfen.

Andere integrierte Excel-Methoden - Filtern und Ersetzen

Filtern kann helfen, visuell alle Werte auszuwählen, die größer oder kleiner als Ihre spezifische Anforderung sind, damit Sie 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 Personen macht, die direkt mit der Excel-Oberfläche für einmalige oder visuelle Aufgaben 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 Zahlfilter > Größer als (oder Kleiner als) und geben Sie 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 überschreibt nur die aktuell sichtbaren (gefilterten) Zellen.

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

Tipps, Vor- & Nachteile:

• Filtern und Ersetzen ist einfach 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, kann diese Methode Formeln überschreiben und möglicherweise kaputt machen; 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 exakte Übereinstimmungen in Excel?

Wie ersetzt man Text durch entsprechende Bilder in Excel?

Wie findet und ersetzt man Füllfarben in Excel?