Massentextersetzung durch Formeln

Angenommen, Sie haben eine Liste, in die mit unterschiedlicher „Einfachheit“ Ausgangsdaten geschrieben werden – beispielsweise Adressen oder Firmennamen:

Es ist deutlich zu sehen, dass die gleiche Stadt oder Firma hier in kunterbunten Varianten präsent ist, was bei der Arbeit mit diesen Tabellen in Zukunft natürlich viele Probleme bereiten wird. Und wenn Sie ein wenig nachdenken, finden Sie viele Beispiele für ähnliche Aufgaben aus anderen Bereichen.

Stellen Sie sich nun vor, dass solche krummen Daten regelmäßig zu Ihnen kommen, dh dies ist keine einmalige „Manuell beheben, vergessen“-Geschichte, sondern ein Problem, das regelmäßig und in einer großen Anzahl von Zellen auftritt.

Was zu tun ist? Ersetzen Sie den krummen Text nicht 100500 Mal manuell durch den richtigen durch das Feld „Suchen und Ersetzen“ oder durch Klicken Ctrl+H?

Das erste, was in einer solchen Situation in den Sinn kommt, ist, einen Massenaustausch gemäß einem vorkompilierten Nachschlagewerk zum Abgleichen falscher und richtiger Optionen vorzunehmen – wie folgt:

Leider verfügt Microsoft Excel angesichts der offensichtlichen Verbreitung einer solchen Aufgabe nicht über einfache integrierte Methoden, um sie zu lösen. Lassen Sie uns zunächst herausfinden, wie Sie dies mit Formeln tun können, ohne „schweres Geschütz“ in Form von Makros in VBA oder Power Query einzusetzen.

Fall 1. Massenweiser vollständiger Ersatz

Beginnen wir mit einem relativ einfachen Fall – einer Situation, in der Sie den alten krummen Text durch einen neuen ersetzen müssen. voll.

Nehmen wir an, wir haben zwei Tabellen:

In der ersten – die ursprünglichen bunten Namen von Unternehmen. In der zweiten – ein Nachschlagewerk der Korrespondenz. Wenn wir im Namen des Unternehmens in der ersten Tabelle ein Wort aus der Spalte finden Finden, dann müssen Sie diesen krummen Namen vollständig durch den richtigen ersetzen – aus der Spalte Ersatz zweite Nachschlagetabelle.

Zur Bequemlichkeit:

• Beide Tabellen werden per Tastaturkürzel in dynamisch („smart“) umgewandelt Ctrl+T oder Team Tabelle einfügen (Tabelle einfügen).
  
• Auf der angezeigten Registerkarte Bauherr (Design) erste Tabelle benannt Datum, und die zweite Referenztabelle – Auswechslungen.

Um die Logik der Formel zu erklären, gehen wir ein wenig aus der Ferne.

Nehmen wir das erste Unternehmen aus Zelle A2 als Beispiel und vergessen wir vorübergehend die restlichen Unternehmen. Versuchen wir herauszufinden, welche Option aus der Spalte ausgewählt wurde Finden trifft sich dort. Wählen Sie dazu im freien Teil des Blattes eine beliebige leere Zelle aus und geben Sie dort die Funktion ein FINDEN (FINDEN):

Diese Funktion bestimmt, ob die angegebene Teilzeichenfolge enthalten ist (das erste Argument sind alle Werte aus der Spalte Finden) in den Quelltext (das erste Unternehmen aus der Datentabelle) und sollte entweder die Ordnungszahl des Zeichens ausgeben, aus dem der Text gefunden wurde, oder einen Fehler, wenn der Teilstring nicht gefunden wurde.

Der Trick dabei ist, dass, da wir nicht einen, sondern mehrere Werte als erstes Argument angegeben haben, diese Funktion als Ergebnis auch nicht einen Wert zurückgibt, sondern ein Array aus 3 Elementen. Wenn Sie nicht über die neueste Version von Office 365 verfügen, die dynamische Arrays unterstützt, dann nach Eingabe dieser Formel und Klicken auf Enter Sie werden dieses Array direkt auf dem Blatt sehen:

Wenn Sie frühere Versionen von Excel haben, dann nach dem Klicken auf Enter wir sehen nur den ersten Wert aus dem Ergebnisarray, dh Fehler #VALUE! (#WERT!).

Du solltest keine Angst haben 🙂 Tatsächlich funktioniert unsere Formel und du kannst immer noch die gesamte Reihe von Ergebnissen sehen, wenn du die eingegebene Funktion in der Formelleiste auswählst und die Taste drückst F9(nur drücken nicht vergessen Esczurück zur Formel):

Die resultierende Reihe von Ergebnissen bedeutet, dass im ursprünglichen krummen Firmennamen (GK Morozko OAO) aller Werte in einer Spalte Finden habe nur die zweite gefunden (Morozko), und ab dem 4. Zeichen in Folge.

Jetzt fügen wir unserer Formel eine Funktion hinzu ANZEIGEN(NACHSCHLAGEN):

Diese Funktion hat drei Argumente:

1. Gewünschter Wert – Sie können jede ausreichend große Zahl verwenden (Hauptsache, sie überschreitet die Länge eines beliebigen Textes in den Quelldaten)
   
2. Gesehener_Vektor – der Bereich oder das Array, in dem wir nach dem gewünschten Wert suchen. Hier ist die zuvor eingeführte Funktion FINDEN, das ein Array {#WERT!:4:#WERT!} zurückgibt
   
3. Vektor_Ergebnisse – der Bereich, aus dem wir den Wert zurückgeben wollen, wenn der gewünschte Wert in der entsprechenden Zelle gefunden wird. Hier sind die richtigen Namen aus der Spalte Ersatz unsere Referenztabelle.
   

Das wichtigste und nicht offensichtliche Merkmal hier ist die Funktion ANZEIGEN Wenn es keine genaue Übereinstimmung gibt, wird immer nach dem nächsten kleinsten (vorherigen) Wert gesucht. Daher werden wir erzwingen, indem wir eine kräftige Zahl (z. B. 9999) als gewünschten Wert angeben ANZEIGEN finde die Zelle mit der nächstkleineren Zahl (4) im Array {#WERT!:4:#WERT!} und gib den entsprechenden Wert aus dem Ergebnisvektor zurück, dh korrekten Firmennamen aus der Spalte Ersatz.

Die zweite Nuance ist, dass unsere Formel technisch gesehen eine Matrixformel ist, weil sie funktioniert FINDEN gibt als Ergebnis nicht einen, sondern ein Array aus drei Werten zurück. Aber da die Funktion ANZEIGEN unterstützt Arrays out of the box, dann müssen wir diese Formel nicht als klassische Array-Formel – per Tastaturkürzel – eingeben Ctrl+Shift+Enter. Eine einfache reicht aus Enter.

Das ist alles. Hoffe du verstehst die Logik.

Es bleibt, die fertige Formel in die erste Zelle B2 der Spalte zu übertragen Behoben – und unsere Aufgabe ist gelöst!

Natürlich funktioniert diese Formel auch mit gewöhnlichen (nicht intelligenten) Tabellen hervorragend (vergessen Sie nur nicht den Schlüssel F4 und Korrigieren der entsprechenden Links):

Fall 2. Massenteilersatz

Dieser Fall ist etwas kniffliger. Wieder haben wir zwei „intelligente“ Tabellen:

Die erste Tabelle mit falsch geschriebenen Adressen, die korrigiert werden müssen (ich habe sie genannt Daten2). Die zweite Tabelle ist ein Nachschlagewerk, nach dem Sie eine Teilzeichenfolge innerhalb der Adresse teilweise ersetzen müssen (ich habe diese Tabelle genannt Auswechslungen2).

Der grundlegende Unterschied besteht hier darin, dass Sie nur einen Bruchteil der Originaldaten ersetzen müssen – beispielsweise hat die erste Adresse eine falsche „St. Petersburg“ rechts „St. Petersburg“, wobei der Rest der Adresse (Postleitzahl, Straße, Haus) unverändert bleibt.

Die fertige Formel sieht so aus (zur leichteren Wahrnehmung habe ich sie in wie viele Zeilen unterteilt Andere+Enter):

Die Hauptarbeit übernimmt hier die Standard-Excel-Textfunktion ERSATZ (ERSATZ), die 3 Argumente hat:

1. Quelltext – die erste krumme Adresse aus der Adressspalte
2. Was wir suchen – hier verwenden wir den Trick mit der Funktion ANZEIGEN (NACHSCHLAGEN)von der vorherigen Methode, um den Wert aus der Spalte zu ziehen Finden, die als Fragment in einer gekrümmten Adresse enthalten ist.
3. Was zu ersetzen ist – auf die gleiche Weise finden wir den entsprechenden Wert aus der Spalte Ersatz.

Geben Sie diese Formel mit ein Ctrl+Shift+Enter wird hier auch nicht benötigt, obwohl es sich eigentlich um eine Matrixformel handelt.

Und es ist deutlich zu sehen (siehe #N/A-Fehler im vorherigen Bild), dass eine solche Formel bei aller Eleganz einige Nachteile hat:

• Funktion Bei SUBSTITUTE wird zwischen Groß- und Kleinschreibung unterschieden, also wurde „Spb“ in der vorletzten Zeile nicht in der Ersetzungstabelle gefunden. Um dieses Problem zu lösen, können Sie entweder die Funktion verwenden ZAMENIT (ERSETZEN), oder bringen Sie beide Tabellen vorläufig in dasselbe Register.
• Ist der Text anfangs richtig oder drin es gibt kein zu ersetzendes Fragment (letzte Zeile), dann wirft unsere Formel einen Fehler. Dieser Moment kann neutralisiert werden, indem Fehler mit der Funktion abgefangen und ersetzt werden IFERROR (IFERROHR):

  

• Wenn der Originaltext enthält mehrere Fragmente aus dem Verzeichnis auf einmal, dann ersetzt unsere Formel nur die letzte (in der 8. Zeile, Ligovsky «Avenue« gewechselt zu „pr-t“, Aber „S-Pb“ on „St. Petersburg“ nicht mehr, weil „S-Pb“ ist weiter oben im Verzeichnis). Dieses Problem kann gelöst werden, indem unsere eigene Formel erneut ausgeführt wird, jedoch bereits entlang der Spalte Behoben:

  
  

Stellenweise nicht perfekt und umständlich, aber viel besser als der gleiche manuelle Austausch, oder? 🙂

PS

Im nächsten Artikel erfahren Sie, wie Sie eine solche Massenersetzung mithilfe von Makros und Power Query implementieren.

• Funktionsweise der SUBSTITUTE-Funktion zum Ersetzen von Text
  
• Finden exakter Textübereinstimmungen mit der EXACT-Funktion
• Suche und Ersetzung mit Berücksichtigung der Groß-/Kleinschreibung (VLOOKUP mit Berücksichtigung der Groß-/Kleinschreibung)