Excel: Finden und ersetzen Sie mehrere Werte gleichzeitig

In diesem Tutorial werden wir verschiedene Möglichkeiten betrachten, mehrere Wörter, Zeichenfolgen oder einzelne Zeichen zu finden und zu ersetzen, damit Sie die auswählen können, die Ihren Anforderungen am besten entspricht.

Wie suchen die Leute normalerweise in Excel? Meistens verwenden Sie die Find & Ersatz -Funktion, die für einzelne Werte gut funktioniert. Aber was ist, wenn Sie Zehn oder sogar Hunderte von Gegenständen ersetzen müssen? Sicherlich möchte niemand alle diese Ersatzteile einseitig manuell machen und es dann noch einmal machen, wenn sich die Daten ändert. Glücklicherweise gibt es in Excel ein paar wirksamere Methoden, um Mass zu ersetzen, und wir werden jeden von ihnen im Detail untersuchen.

• Verschachtelte Ersatzformel, um mehrere Werte zu ersetzen
• Xlookup -Formel, um nach mehreren Wörtern zu suchen
• Multi-Replace mit rekursiver Lambda-Funktion
• Massenfind und Ersatz durch UDF
• Makro-Replace-Makro, um mehrere Werte zu finden und zu ersetzen
• Mehrere Suchwerkzeuge finden und ersetzen Sie mit Substring -Tools

Finden und ersetzen Sie mehrere Werte durch einen verschachtelten Ersatz

Der einfachste Weg, mehrere Einträge in Excel zu finden und zu ersetzen, ist die Verwendung der Ersatzfunktion.

Die Logik der Formel ist sehr einfach: Sie schreiben ein paar individuelle Funktionen, um einen alten Wert durch einen neuen zu ersetzen. Und dann nisten Sie diese Funktionen in einen anderen, so dass jeder nachfolgende Ersatz die Ausgabe des vorherigen Ersatzes verwendet, um nach dem nächsten Wert zu suchen.

Ersatz (Ersatz (Ersatz ( Text , Old_Text1 , New_Text1 ), Old_Text2 , New_Text2 ), Old_Text3 , New_Text3 )

In der Liste der Standorte in A2: A10 möchten Sie die abgekürzten Landnamen (wie FR , Großbritannien und USA ) durch vollständige Namen ersetzen.

Geben Sie die alten Werte in D2: D4 und die neuen Werte in E2: E4 wie im Screenshot unten gezeigt. Und geben Sie dann die folgende Formel in B2 ein und drücken Sie die Eingabetaste:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)

… Und Sie werden alle Ersatzteile gleichzeitig erledigen:

Bitte beachten Sie, dass der obige Ansatz nur in Excel 365 funktioniert und dynamische Arrays unterstützt.

In prä-dynamischen Versionen von Excel 2019, Excel 2016 und früher muss die Formel für die oberste Zelle (B2) geschrieben und dann in die folgenden Zellen kopiert werden:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

Bitte beachten Sie, dass wir in diesem Fall die Ersatzwerte mit absoluten Zellreferenzen sperren, sodass sie beim Kopieren der Formel nicht verschoben werden.

Notiz. Die Ersatzfunktion ist Fallempfindlichkeit , dh Sie sollten die alten Werte ( old_text ) in den gleichen Buchstaben-Fall eingeben, wie sie in den Originaldaten angezeigt werden.

So einfach es auch sein mag, diese Methode hat einen erheblichen Nachteil - wenn Sie Dutzende von Elementen ersetzen müssen, werden verschachtelte Funktionen ziemlich schwierig zu verwalten.

Vorteile : einfach zu implementieren; in allen Excel -Versionen unterstützt

Nachteile : Am besten für eine begrenzte Anzahl von Find-/Ersatzwerten verwendet werden

Suchen und ersetzen Sie mehrere Einträge durch Xlookup

In Situation, wenn Sie den gesamten Zellinhalt ersetzen möchten, und nicht seinen Teil, ist die Xlookup -Funktion nützlich.

Angenommen, Sie haben eine Liste der Länder in Spalte A und wollen alle Abkürzungen durch die entsprechenden vollständigen Namen ersetzen. Wie im vorherigen Beispiel beginnen Sie mit dem Eingeben der Elemente "Find" und "Ersetzen" in separaten Spalten (D bzw. E) und geben dann diese Formel in B2 ein:

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

Aus der Excel -Sprache in die menschliche Sprache übersetzt, ist die Formel:

Suchen Sie nach dem A2 -Wert (SOOKUP_VALUE) in D2: D4 (Lookup_array) und geben Sie eine Übereinstimmung von E2: E4 (return_array) zurück. Wenn nicht gefunden, ziehen Sie den ursprünglichen Wert von A2.

Doppelklicken Sie auf den Füllgriff, um die Formel in die folgenden Zellen kopieren zu lassen, und das Ergebnis wird Sie nicht warten:

Da die Xlookup -Funktion nur in Excel 365 verfügbar ist, funktioniert die obige Formel in früheren Versionen nicht. Sie können dieses Verhalten jedoch leicht mit einer Kombination aus IFERROR oder IFNA und VLOOKUP nachahmen:

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)

Notiz. Im Gegensatz zum Ersatz sind die Funktionen von Xlookup und VLOOKUP nicht von Fallempfindlichkeit , was bedeutet, dass sie nach den Suchwerten suchen, die den Brieffall ignorieren. Zum Beispiel würde unsere Formel sowohl FR als auch Frankreich durch Frankreich ersetzen.

Vorteile : ungewöhnliche Verwendung der üblichen Funktionen; Funktioniert in allen Excel -Versionen

Nachteile : Arbeitet auf Zellenebene, können keinen Teil des Zellinhalts ersetzen

Mehrfach ersetzen mit rekursiver Lambda -Funktion

Für Microsoft 365 -Abonnenten bietet Excel eine spezielle Funktion, mit der benutzerdefinierte Funktionen mithilfe einer herkömmlichen Formelsprache erstellt werden können. Ja, ich spreche von Lambda. Das Schöne an dieser Methode ist, dass sie eine sehr lange und komplexe Formel in eine sehr kompakte und einfache Umwandlung umwandeln kann. Darüber hinaus können Sie Ihre eigenen Funktionen erstellen, die in Excel nicht existieren, etwas, das nur mit VBA möglich war.

Die detaillierten Informationen zum Erstellen und Verwenden benutzerdefinierter Lambda -Funktionen finden Sie in diesem Tutorial: So schreiben Sie Lambda -Funktionen in Excel. Hier werden wir einige praktische Beispiele besprechen.

Vorteile : Das Ergebnis ist eine elegante und erstaunlich einfach zu verwendende Funktion, unabhängig von der Anzahl der Ersatzpaare

Nachteile : Nur in Excel 365 verfügbar; Arbeitsbuchspezifisch und kann nicht in verschiedenen Arbeitsmappen wiederverwendet werden

Beispiel 1. Suchen und ersetzen Sie mehrere Wörter / Zeichenfolgen gleichzeitig

Um mehrere Wörter oder Text auf einmal zu ersetzen, haben wir eine benutzerdefinierte Lambda -Funktion namens Multireplace erstellt, die eine dieser Formen annehmen kann:

=LAMBDA(text, old, new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

Oder

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

Beide sind rekursive Funktionen, die sich selbst nennen. Der Unterschied liegt nur darin, wie der Ausstiegspunkt festgelegt wird.

In der ersten Formel prüft die IF -Funktion, ob die alte Liste nicht leer ist (alt ""). Wenn wahr, wird die Multirplace -Funktion aufgerufen. Wenn falsch, gibt die Funktion den Text ihrer aktuellen Form zurück und beendet.

In der zweiten Formel wird die umgekehrte Logik verwendet: Wenn alt (old = "") ist, geben Sie Text zurück und beenden Sie. Ansonsten rufen Sie Multireplace an .

Der schwierigste Teil ist erreicht! Was Sie noch tun müssen, ist, die Multirplace -Funktion im Namensmanager zu nennen, wie im folgenden Screenshot gezeigt. Die detaillierten Richtlinien finden Sie unter dem Namen einer Lambda -Funktion.

Sobald die Funktion einen Namen erhält, können Sie sie genau wie jede andere integrierte Funktion verwenden.

Welche der beiden Formel-Variationen, die Sie aus der Sicht der Endbenutzer auswählen, ist die Syntax so einfach wie folgt:

Multirplace (Text, alt, neu)

Wo:

• Text - Die Quelldaten
• Alt - die Werte zu finden
• Neu - die Werte zu ersetzen, die er ersetzen soll

Wenn wir das vorherige Beispiel ein wenig weiter gehen, ersetzen wir nicht nur die Abkürzungen des Landes, sondern auch die staatlichen Abkürzungen. Geben Sie dazu die Abkürzungen ( alte Werte) in Spalte D ab D 2 und die vollständigen Namen ( neue Werte) in Spalte E ab E2 ab.

Geben Sie in B2 die Multirplace -Funktion ein:

=MultiReplace(A2:A10, D2, E2)

Drücken Sie die Ergebnisse und genießen Sie die Ergebnisse :)

Wie diese Formel funktioniert

Der Hinweis auf das Verständnis der Formel ist das Verständnis der Rekursion. Dies mag kompliziert klingen, aber das Prinzip ist recht einfach. Bei jeder Iteration löst eine rekursive Funktion einen kleinen Beispiel eines größeren Problems. In unserem Fall schleifen die Multirplace -Funktion die alten und neuen Werte durch und führt mit jeder Schleife einen Ersatz durch:

Multirplace (SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

Wie bei verschachtelten Ersatzfunktionen wird das Ergebnis des vorherigen Ersatzes zum Textparameter für den nächsten Ersatz. Mit anderen Worten, bei jedem nachfolgenden Multirplace -Ruf verarbeitet der Ersatzfunktion nicht die ursprüngliche Textzeichenfolge, sondern die Ausgabe des vorherigen Aufrufs.

Um alle Elemente in der alten Liste zu verarbeiten, beginnen wir mit der obersten Zelle und verwenden die Offset -Funktion mit jeder Interaktion 1 Zeile nach unten:

OFFSET(old, 1, 0)

Gleiches gilt für die neue Liste:

OFFSET(new, 1, 0)

Die wichtige Sache ist, einen Ausstiegspunkt zu liefern, um zu verhindern, dass rekursive Anrufe für immer fortfahren. Es geschieht mit Hilfe der IF -Funktion - wenn die alte Zelle leer ist, gibt die Funktion den Text zurück und beendet:

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))

oder

=LAMBDA(text, old, new, IF(old"", MultiReplace(…), text))

Beispiel 2. Ersetzen Sie mehrere Zeichen in Excel

Im Prinzip kann die im vorherige Beispiel diskutierte Multirplace -Funktion auch einzelne Zeichen verarbeiten, sofern jedes alte und neue Zeichen in eine separate Zelle eingegeben wird, genau wie die abgekürzten und vollständigen Namen in den obigen Screenshots.

Wenn Sie die alten Zeichen lieber in einer Zelle und den neuen Zeichen in einer anderen Zelle eingeben oder sie direkt in der Formel eingeben, können Sie eine andere benutzerdefinierte Funktion namens Ersatz für eine dieser Formeln erstellen:

=LAMBDA(text, old_chars, new_chars, IF(old_chars"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))

Oder

=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))

Denken Sie daran, Ihre neue Lambda -Funktion wie gewohnt im Namensmanager zu benennen:

Die Funktion "title =" tastechars "benennen eine benutzerdefinierte Ersatztechars -Funktion"> ">"> ">"> ">"> ">">

Und Ihre neue benutzerdefinierte Funktion ist zur Verwendung bereit:

Ersatztechars (Text, Old_chars, New_Chars)

Wo:

• Text - die ursprünglichen Zeichenfolgen
• Alt - die Charaktere, nach denen man suchen sollte
• Neu - die Charaktere, die er ersetzen sollten

Um ihm einen Feldtest durchzuführen, machen wir etwas, das häufig an importierten Daten durchgeführt wird - ersetzen Sie intelligente Zitate und intelligente Apostrophes durch direkte Zitate und gerade Apostrophe.

Zunächst geben wir die intelligenten Zitate und Smart -Apostroph in D2, gerade Zitate und gerade Apostrophe in E2 ein und trennen die Zeichen mit Räumen für eine bessere Lesbarkeit. (Da wir in beiden Zellen denselben Trennzeichen verwenden, hat es keinen Einfluss auf das Ergebnis - Excel ersetzt nur einen Raum durch einen Raum.)

Danach geben wir diese Formel in B2 ein:

=ReplaceChars(A2:A4, D2, E2)

Und erhalten Sie genau die Ergebnisse, nach denen wir gesucht haben:

Es ist auch möglich, die Zeichen direkt in der Formel einzugeben. Denken Sie in unserem Fall einfach daran, die geraden Zitate wie diese zu "duplizieren":

=ReplaceChars(A2:A4, "“ ” '", """ "" '")

Wie diese Formel funktioniert

Die Funktionen von Austauschechars fahren durch die Saiten Old_Chars und New_Chars und ersetzen jeweils einen Ersatz. Dieser Teil erfolgt durch die Ersatzfunktion:

SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))

Bei jeder Iteration streift die rechte Funktion einen Zeichen links von den Saiten Old_Chars und New_Chars , so dass das nächste Mal das nächste Zeichen von Zeichen für die Substitution abrufen konnte:

ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))

Vor jedem rekursiven Aufruf bewertet die IF -Funktion die Zeichenfolge old_chars . Wenn es nicht leer ist, ruft sich die Funktion auf. Sobald das letzte Zeichen ersetzt wurde, endet der Iterationsprozess, gibt die Formel den text ihrer gegenwärtigen Formular zurück und verlässt.

Notiz. Da die in unseren Kernformeln verwendete Ersatzfunktion fälschlichem sensitiv ist, behandeln sowohl Lambdas ( Multireplace als auch Ersatzteile ) Großbuchstaben und Kleinbuchstaben als unterschiedliche Zeichen.

Massenfind und Ersatz durch UDF

Falls die Lambda-Funktion in Ihrem Excel nicht verfügbar ist, können Sie eine benutzerdefinierte Funktion für Multi-Replace auf herkömmliche Weise mit VBA schreiben.

Um die UDF von der Lambda-definierten Multirplace -Funktion zu unterscheiden, werden wir sie anders benennen, sagen wir MassReplace . Der Code der Funktion lautet wie folgt:

Function MassReplace(InputRng As Range, FindRng As Range, ReplaceRng As Range) As Variant () Dim arRes() As Variant 'array to store the results Dim arSearchReplace(), sTmp As String 'array where to store the find/replace pairs, temporary string Dim iFindCurRow, cntFindRows As Long 'index of the current row of the SearchReplace array, count of rows Dim iInputcurrow, iInputCurcol, cntinputrows, cntinputcols als langer Index der aktuellen Zeile im Quellbereich, Index der aktuellen Spalte im Quellbereich, Anzahl der Zeilen, Anzahl der Spalten cntInputrows = inputRng.rows.count cntInputcols = inputRng . arres (1 bis cntinputrows, 1 bis cntinputcols) reduziert Arsearchreplace (1 bis cntfindrows, 1 bis 2) 'Vorbereitung des Arrays von Find-/Ersetzenpaaren für ifindcurrow = 1 bis cntfindrows arsearchreplace (ifindcurrow, 1). ARSEARCHREPLACE ( IFindCurrow , 2 ) = Ersatz . Finden /Ersetzen Sie Paare in jeder Zelle für ifindcurrow = 1 bis cntfindrows stmp = ersetzen ( STMP, ArSearchReplace (ifindcurrow, 1), ArsearchReplace (ifindcurrow, 2)) Nächst

Wie in Lambda-definierte Funktionen sind UDFs Arbeitsbuchweit . Das bedeutet, dass die MassReplace -Funktion nur in der Arbeitsmappe funktioniert, in der Sie den Code eingefügt haben. Wenn Sie sich nicht sicher sind, wie dies korrekt ist, befolgen Sie bitte die in Excel einfügigen Schritte, in denen der VBA -Code einfügt.

Sobald der Code zu Ihrer Arbeitsmappe hinzugefügt wurde, wird die Funktion in der Formel Intellisense angezeigt - nur der Name der Funktion, nicht die Argumente! Ich glaube jedoch, dass es keine große Sache ist, sich an die Syntax zu erinnern:

MassReplace (input_range, find_range, ersetzen_range)

Wo:

• Input_range - Der Quellbereich, in dem Sie Werte ersetzen möchten.
• Find_range - die Zeichen, Zeichenfolgen oder Wörter, nach denen man suchen sollte.
• Ersetzen_range - die Zeichen, Zeichenfolgen oder Wörter, die ersetzt werden.

In Excel 365 funktioniert dies aufgrund der Unterstützung dynamischer Arrays als normale Formel, die nur in die obere Zelle (B2) eingegeben werden muss:

=MassReplace(A2:A10, D2:D4, E2:E4)

In Pre-Dynamic Excel funktioniert dies als CSE-Array-Formel im alten Stil: Sie wählen den gesamten Quellbereich (B2: B10) aus, geben die Formel ein und drücken die Strg-Schalttasten gleichzeitig, um sie zu vervollständigen.

Vorteile : Eine anständige Alternative zu einer benutzerdefinierten Lambda -Funktion in Excel 2019, Excel 2016 und früheren Versionen

Nachteile : Die Arbeitsmappe muss als makro-fähiger .xlsm-Datei gespeichert werden

Masse ersetzen in Excel durch VBA -Makro

Wenn Sie es lieben, gemeinsame Aufgaben mit Makros zu automatisieren, können Sie den folgenden VBA -Code verwenden, um mehrere Werte in einem Bereich zu finden und zu ersetzen.

Sub bulkreplace () dim rng als Bereich , Sourcerng als Bereich , Ersatz als Reichweite für Fehler Lebenslauf Nächster Satz Sourcerng = Anwendung.inputbox ( "Quelldaten:" , "Bulk Ersatz " , application.Selection . Ersatz ist nichts dann als Anwendung .​​​​​​​​

Um das Makro sofort zu verwenden, können Sie unser Beispiel -Arbeitsbuch mit dem Code herunterladen. Oder Sie können den Code in Ihre eigene Arbeitsmappe einfügen.

So verwenden Sie das Makro

Geben Sie vor dem Ausführen des Makro die alten und neuen Werte in zwei benachbarte Spalten ein, wie im Bild unten gezeigt (C2: D4).

Wählen Sie dann Ihre Quelldaten aus, drücken Sie ALT F8 , wählen Sie das Bulkreplace -Makro und klicken Sie auf Ausführen .

Wenn die Quellwut vorgewählt ist, überprüfen Sie einfach die Referenz und klicken Sie auf OK:

Wählen Sie danach den Bereich Ersetzen und klicken Sie auf OK:

Erledigt!

Vorteile : Einmal einrichten, jederzeit wiederverwenden

Nachteile : Das Makro muss mit jeder Datenänderung ausgeführt werden

Mehrfach Finden und Ersetzen in Excel durch Substring -Tool

Im ersten Beispiel erwähnte ich, dass verschachtelter Ersatz der einfachste Weg ist, mehrere Werte in Excel zu ersetzen. Ich gebe zu, dass ich mich geirrt habe. Unsere ultimative Suite macht die Dinge noch einfacher!

Um in Ihrem Arbeitsblatt Ersatz zu ersetzen, besuchen Sie die Registerkarte "AbleTbits" und klicken Sie auf Substring -Tools > Substrings ersetzen .

Das Dialogfeld Substrings ersetzen Sie und werden Sie aufgefordert, den Quellbereich und den Substringsbereich zu definieren.

Klicken Sie mit den beiden ausgewählten Bereichen auf die Schaltfläche Ersetzen und finden Sie die Ergebnisse in einer neuen Spalte, die rechts neben den Originaldaten eingefügt wurde. Ja, es ist so einfach!

Tipp. Bevor Sie auf Ersetzen klicken, gibt es eine wichtige Sache, die Sie berücksichtigen müssen - das Fall -sensitive Box. Wählen Sie es unbedingt aus, wenn Sie die Groß- und Kleinbuchstaben als verschiedene Zeichen verarbeiten möchten. In diesem Beispiel ticken wir diese Option, weil wir nur die aktivierten Zeichenfolgen ersetzen und die Unterstrings wie "FR", "UK" oder "AK" in anderen Worten intakt lassen möchten.

Wenn Sie neugierig wissen, welche anderen Massenoperationen an Saiten ausgeführt werden können, lesen Sie andere Substring -Tools, die in unserer ultimativen Suite enthalten sind. Oder noch besser, laden Sie die Bewertungsversion unten herunter und probieren Sie es aus!

So finden und ersetzen Sie mehrere Wörter und Zeichen gleichzeitig in Excel. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Verfügbare Downloads

Multiple Find und Ersatz in Excel (.xlsm-Datei) Ultimate Suite 14-tägige Vollfunktionale Version (.exe-Datei)

Das obige ist der detaillierte Inhalt vonExcel: Finden und ersetzen Sie mehrere Werte gleichzeitig. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!