Wie kann man mehrere Excel-Dateien nach Nummernmustern stapelweise ersetzen? Methoden für reguläre Ausdrücke und Datenmaskierung

Kundennummern, Institutionscodes oder Regionscodes in verschiedenen Excel-Dateien unterscheiden sich oft inhaltlich, haben jedoch dasselbe Präfix und dieselbe Stellenzahl. Bei solchen Daten ist es ineffizient, jede vollständige Nummer einzeln einzugeben und zu ersetzen. Dieser Artikel erläutert anhand einer xlsx-Arbeitsmappe, wie Sie in HeSoft Doc Batch Tool Dateien stapelweise importieren, den Verarbeitungsbereich für Zelltext einschränken, mit Formeln wie 030\d{3}, 46\d{3} unscharfe Suchregeln verwenden und die beiden Codetypen jeweils durch festgelegte Werte ersetzen. Gleichzeitig beschreibt der Artikel die Auswirkungen vor und nach der Verarbeitung, die zeilenweise Zuordnung der Regeln, Unterschiede bei den Datentypen, Risiken bei Formelzellen sowie Methoden zum Testen und Sichern vor der eigentlichen Verarbeitung.

Bei der Datenmaskierung, Systemmigration, dem Aufbau von Testumgebungen und der Standardisierung von Berichten müssen häufig Nummern in einer Reihe von Excel-Dateien geändert werden. Die Schwierigkeit besteht darin, dass diese Nummern in der Regel nicht völlig identische feste Texte sind, sondern „gleiche Präfixe, aber unterschiedliche Endziffern“ aufweisen. Beispielsweise können in einer Arbeitsmappe gleichzeitig Institutionsnummern wie 030006, 030010, 030064 vorkommen, und in einer anderen Arbeitsmappe können noch mehr Codes mit dem Anfang 030 auftreten. Wenn man die Nummern einzeln kopiert und ersetzt, ist das nicht nur langsam, sondern kann auch aufgrund vieler Dateien und vieler Arbeitsblätter zu Auslassungen führen.

In diesem Artikel wird eine besser für die Stapelverarbeitung im Büro geeignete Methode vorgestellt: Verwenden Sie in HeSoft Doc Batch Tool die Funktion „Schlüsselwörter in Excel suchen und ersetzen“, beschreiben Sie die Nummernregeln mithilfe von Platzhalter-Regex und verarbeiten Sie dann mehrere xlsx-Dateien auf einmal. Im Beispiel werden Inhalte, die mit 030 beginnen und auf drei Ziffern enden, durch 111111 ersetzt, und Inhalte, die mit 46 beginnen und auf drei Ziffern enden, durch 22222. Nach dem Lesen verstehen Sie den vollständigen Ablauf und die Prüfpunkte dieser Art von Stapelersetzungsaufgaben für Excel-Nummern.

1. Anwendbare Szenarien: Nummernersetzung, Codemigration und Excel-Datenmaskierung

Diese Methode eignet sich für Szenarien, in denen der Zielinhalt deutliche Regelmäßigkeiten aufweist, und ist besonders für die folgenden Arbeiten geeignet:

• Vor dem externen Versand von Excel-Listen einheitlich Institutionsnummern, Kundennummern oder interne Geschäftscodes verbergen;
• Beim Wechsel von einem Altsystem zu einem Neusystem alte Codes in mehreren Arbeitsmappen auf einmal in neue Codes oder temporäre Platzhalterwerte umwandeln;
• Beim Erstellen von Schulungsmaterialien, Demodaten und Testbeispielen echte Nummern durch fiktive Nummern ersetzen;
• Wenn mehrere Abteilungen jeweils xlsx-Tabellen pflegen und eine bestimmte Art von Präfixcodes einheitlich geändert werden muss;
• Wenn viele Berichte nach Quartal, Monat oder Region gespeichert wurden und dieselbe Gruppe von Ersetzungsregeln dateiübergreifend ausgeführt werden muss.

Zu beachten ist, dass das Beispiel die Ersetzung von Zelltext nach Regeln zeigt. Ob die Datenmaskierung den tatsächlichen Sicherheitsanforderungen entspricht, hängt auch von den Ersetzungsregeln, der Verwaltung der Ausgabedateien und den organisatorischen Vorgaben ab. Bei sensiblen Daten sollte man sich nicht nur darauf konzentrieren, „ob ersetzt wurde“, sondern auch prüfen, ob es Auslassungen, ausgeblendete Arbeitsblätter oder andere Stellen gibt, an denen die ursprünglichen Informationen weiterhin erhalten bleiben.

2. Vorschau der Wirkung: Nummernänderungen vor und nach der regelbasierten Ersetzung

1. Vor der Verarbeitung: Gleiche Nummerntypen mit unterschiedlichem Inhalt, aber einheitlichem Format

Im ursprünglichen Excel-Arbeitsblatt enthält die Spalte Hospital CCN mehrere sechsstellige Nummern. Im markierten Bereich des Screenshots sind Inhalte wie 030006, 030010, 030011, 030064 und 030074 zu sehen. Ihre letzten drei Stellen sind unterschiedlich, entsprechen aber alle der Struktur „030 plus drei Ziffern“. In der Spalte CBSA erscheint fortlaufend 46060, was der Struktur „46 plus drei Ziffern“ entspricht.

Bei einer exakten Suche müsste jede CCN-Nummer einzeln aufgeführt werden. Mit der unscharfen Formelsuche genügt ein einziger Ausdruck, um gleichartige Nummern zu beschreiben und damit mehrere unterschiedliche Werte abzudecken.

2. Nach der Verarbeitung: Unterschiedliche Kategorien werden durch die jeweiligen Zielwerte ersetzt

In der Ausgabearbeitsmappe werden Zellen, die ursprünglich der 030-Nummernregel entsprechen, einheitlich als 111111 angezeigt, und Zellen, die ursprünglich der 46-Nummernregel entsprechen, einheitlich als 22222. Andere Zeilen ohne passende Regel behalten ihre ursprünglichen Werte, und auch die Krankenhausnamen und Regionsnamen wurden durch diese Regel nicht verändert.

Aus dem Ergebnis ist unmittelbar ersichtlich, dass für die beiden Nummerntypen zwei unabhängige Suchen-und-Ersetzen-Zuordnungen verwendet wurden. Um diesen Effekt bei einer Stapelaufgabe zu erreichen, kommt es vor allem darauf an, den richtigen Verarbeitungsbereich auszuwählen, die unscharfe Formelsuche zu aktivieren und sicherzustellen, dass die Regeln auf der linken und rechten Seite zeilenweise einander entsprechen.

3. Arbeitsschritte: Erstellen einer Stapelersetzungsaufgabe für Nummern in mehreren Dateien

Schritt 1: Die entsprechende Funktion im Excel-Werkzeug auswählen

Starten Sie HeSoft Doc Batch Tool und öffnen Sie in der linken Navigationsleiste den Bereich „Excel-Werkzeuge“. Das Hauptpanel des Werkzeugs enthält mehrere Funktionen wie Excel-Passwort, Bildexport und Formatkonvertierung. Wählen Sie dieses Mal die erste Option „Schlüsselwörter in Excel suchen und ersetzen“.

Nach dem Aufrufen der richtigen Funktion werden oben vier Aufgabenphasen angezeigt: Auswählen der zu verarbeitenden Datensätze, Festlegen der Verarbeitungsoptionen, Festlegen des Speicherorts und Starten der Verarbeitung. Dieser schrittweise Ablauf ermöglicht es, Dateiliste, Übereinstimmungsregeln und Ausgabeort jeweils getrennt zu bestätigen und Fehler zu reduzieren, die durch gemischte Bedienung auf einer einzigen Seite entstehen.

Schritt 2: Mehrere Excel-Arbeitsmappen auf einmal hinzufügen

Verwenden Sie in der Dateiauswahlphase je nach Art der Dateiablage „Dateien hinzufügen“ oder „Dateien aus Ordner importieren“. Wenn nur einige bestimmte Berichte verarbeitet werden, können Sie „Dateien hinzufügen“ verwenden; wenn die zu verarbeitenden Dateien zentral in einem Verzeichnis gespeichert sind, eignet sich der Ordnerimport besser für Stapelaufgaben.

Die Beispielliste enthält bereits drei xlsx-Dateien: product_list.xlsx, team-participant-list.xlsx und test.xlsx. Die Seite zeigt außerdem den Datenträgerpfad, die Erweiterung, die Erstellungszeit und die Änderungszeit jeder Datei an; unten wird die Gesamtzahl der Datensätze mit 3 angegeben.

Klicken Sie in dieser Phase nicht voreilig auf „Weiter“. Prüfen Sie zunächst, ob alte Sicherungen, bereits verarbeitete Ergebnisdateien oder für die Aufgabe irrelevante Tabellen enthalten sind. Falls etwas falsch ist, können Sie die Datei über die Löschaktion in der entsprechenden Zeile entfernen; wenn die gesamte Auswahl neu erfolgen soll, klicken Sie auf „Leeren“. Nachdem Sie die Liste bestätigt haben, gehen Sie zum nächsten Schritt.

Schritt 3: Den Verarbeitungsgegenstand auf Zelltext beschränken

Im Bereich der Excel-Optionen umfasst der Verarbeitungsbereich „Zelltext“, „Namen von Arbeitsblättern (Sheet)“ und „Text auf Formen im Arbeitsblatt“. In diesem Beispiel wird nur „Zelltext“ aktiviert, da sich die Nummern in Tabellenzellen befinden. Wenn gleichzeitig andere Bereiche aktiviert werden, können die Übereinstimmungsregeln Sheet-Namen oder Formtexte beeinflussen und den Verarbeitungsumfang unnötig erweitern.

Im Screenshot sind die Bedingungen für Arbeitsblattnamen und Zellspaltennamen deaktiviert, sodass die Regeln in Zellen gesucht werden können, die andere Bedingungen erfüllen. Wenn bei einer offiziellen Aufgabe nur bestimmte Arbeitsblätter oder ein bestimmtes Feld geändert werden dürfen, können Sie den Bereich über die auf der Oberfläche angebotenen Bedingungsoptionen weiter eingrenzen.

Wählen Sie als Formeltyp der zu verarbeitenden Zellen „Keine Einschränkung“, als Verarbeitungsweise für Zellen mit Formeln „Wert nach Formelberechnung“ und aktivieren Sie als Datentyp „Text“. Diese Einstellungen passen zu den Textnummern im Screenshot, aber die Art der Datenspeicherung kann in verschiedenen Arbeitsmappen unterschiedlich sein. Insbesondere Nummern mit führenden Nullen werden in der Regel besser als Text gespeichert; wenn Excel sie als Zahl erkennt, enthält der tatsächliche Wert die führende Null möglicherweise nicht mehr.

Schritt 4: Unscharfe Formelsuche für Text aktivieren

Wählen Sie beim Festlegen der Schlüsselwortoptionen „Unscharfe Formelsuche für Text verwenden“ statt „Exakte Textsuche“. Tragen Sie anschließend in der „Liste der zu suchenden Schlüsselwörter“ zwei Zeilen ein:

• 030\d{3}
• 46\d{3}

Dabei steht \d für eine einzelne Ziffer und {3} für drei aufeinanderfolgende Wiederholungen. Die erste Regel dient daher zum Erkennen der drei Ziffern nach 030, die zweite Regel zum Erkennen der drei Ziffern nach 46.

Schritt 5: Zielinhalte nach der Ersetzung zeilenweise eintragen

Tragen Sie in der rechten „Liste der Schlüsselwörter nach der Ersetzung“ in der ersten Zeile 111111 und in der zweiten Zeile 22222 ein. Die Zeilennummern der linken und rechten Liste geben die Zuordnung an:

• 030\d{3} entspricht 111111;
• 46\d{3} entspricht 22222.

Wenn die beiden Ersetzungswerte vertauscht werden, erfolgt auch die endgültige Ausgabe nach der falschen Zuordnung. Daher sollten bei Aufgaben mit mehreren Regeln die Zeilen vor dem Fortfahren einzeln überprüft werden. In der Mitte des Screenshots sind außerdem Bediensymbole zum Anpassen des Listeninhalts dargestellt; bei der tatsächlichen Verwendung sind die endgültigen Zeilennummern auf der linken und rechten Seite maßgeblich.

Schritt 6: Ausgabeort auswählen und Verarbeitung ausführen

Klicken Sie nach Abschluss der Regeleinstellungen unten auf „Weiter“, um zur Festlegung des Speicherorts zu gelangen. Es wird empfohlen, für diese Aufgabe ein eigenes Ausgabeverzeichnis anzulegen und einen Ordnernamen zu verwenden, der den Zweck verdeutlicht, z. B. „Nummernersetzungsergebnis“ oder „Maskierungstest-Ausgabe“. So bleiben die ursprünglichen Arbeitsmappen erhalten und der spätere Vergleich wird erleichtert.

Nachdem Sie zur Phase „Verarbeitung starten“ weitergegangen sind, starten Sie die Aufgabe gemäß den Hinweisen auf der Programmseite. Bestätigen Sie nach Abschluss der Stapelverarbeitung nicht nur, dass Dateien erzeugt wurden, sondern öffnen Sie auch die Ergebnisdateien und überprüfen Sie den Inhalt. Es wird empfohlen, jeweils eine ursprüngliche Nummer mit 030 am Anfang, einen Code mit 46 am Anfang und einen Datensatz, der nicht ersetzt werden sollte, zu prüfen und zu bestätigen, dass alle drei Ergebnisarten den Erwartungen entsprechen.

4. Wie Sie Regeln entsprechend Ihrem eigenen Nummernformat erstellen

Die Ausdrücke im Beispiel sind keine feste Vorlage, sondern wurden auf Basis der tatsächlichen Nummernstruktur geschrieben. Bei der Analyse von Regeln können Sie die Nummer zunächst in einen festen und einen variablen Teil zerlegen. Beispielsweise lässt sich 030006 in das feste Präfix 030 und die variablen drei Ziffern 006 zerlegen und daher als 030\d{3} schreiben.

Wenn Geschäftsnummern dasselbe Präfix haben, aber die Stellenzahl nicht einheitlich ist, sollten Sie {3} nicht einfach übernehmen. Ermitteln Sie zunächst die tatsächliche Datenlänge und entwerfen Sie dann den Ausdruck. Bei Codes, die Buchstaben, Bindestriche, Leerzeichen oder andere Zeichen enthalten können, müssen Sie außerdem die Syntax der vom Programm verwendeten unscharfen Formelsuche bestätigen, bevor Sie die Einstellungen vornehmen.

Je allgemeiner der Ausdruck ist, desto mehr Daten werden abgedeckt, aber desto höher ist auch das Risiko falscher Treffer. Je strenger der Ausdruck ist, desto höher ist in der Regel die Genauigkeit, aber es können auch Datensätze mit Formatabweichungen übersehen werden. Ein zuverlässiges Vorgehen besteht darin, zunächst aus einer kleinen Zahl repräsentativer Dateien Stichproben zu entnehmen, die Trefferergebnisse zu testen und den Dateiumfang erst danach zu erweitern.

5. Häufige Fragen und Hinweise

1. Warum beeinflussen führende Nullen die Übereinstimmung?

030006 behält als Text die führende 0; wenn es von Excel als Zahl gespeichert wird, können Anzeigeformat und tatsächlicher Wert unterschiedlich sein. Die Regex-Regel gleicht den vom Programm eingelesenen Inhalt ab. Daher muss der tatsächliche Datentyp der Zelle geprüft werden und darf nicht nur anhand der Anzeige auf der Oberfläche beurteilt werden.

2. Warum bleiben manche Inhalte unbehandelt, obwohl nur Text ausgewählt wurde?

Unbehandelte Zellen können Zahlen, Wahrheitswerte, Datums- und Uhrzeitwerte oder Formelergebnisse sein oder unsichtbare Leerzeichen enthalten. Sie können in der ursprünglichen Arbeitsmappe das Zellformat und die Bearbeitungsleiste prüfen und die Datentypoptionen des Werkzeugs entsprechend erneut testen.

3. Können zwei Arten von Nummern durch unterschiedliche Ergebnisse ersetzt werden?

Ja. Genau das wird in diesem Beispiel durch zwei Suchregeln und zwei Ersetzungsinhalte für die klassifizierte Ersetzung umgesetzt. Dabei muss sichergestellt werden, dass Anzahl und Reihenfolge übereinstimmen und es beim Bearbeiten nicht zu Zeilenverschiebungen kommt.

4. Sollten die Quelldateien überschrieben werden?

Bei einer Stapelersetzung wird grundsätzlich nicht empfohlen, beim ersten Lauf die einzige Quelldatei zu überschreiben. Wenn der Ausdruck oder die Bereichseinstellung fehlerhaft ist, kann dies mehrere Arbeitsmappen gleichzeitig beeinträchtigen. Die Originaldateien zu behalten und in ein neues Verzeichnis auszugeben, ist das sicherere Vorgehen.

5. Was muss nach der Datenmaskierung noch geprüft werden?

Neben den Textzellen im Hauptteil sollten je nach tatsächlicher Datei auch andere Arbeitsblätter, Formelverweise, Anmerkungen, Formtexte sowie Dateinamen daraufhin geprüft werden, ob sie vertrauliche Informationen enthalten. Der Screenshot zeigt nur die Ersetzung von Zelltext; daraus kann nicht geschlossen werden, dass andere Stellen bereits automatisch maskiert wurden.

6. Zusammenfassung

Für Excel-Inhalte, bei denen die Nummern unterschiedlich, aber das Format gleich ist, sind Platzhalter-Regex effizienter als die einzelne exakte Suche. Mit HeSoft Doc Batch Tool können mehrere xlsx-Dateien in dieselbe Aufgabe aufgenommen und über unscharfe Formelsuchregeln einheitlich Zelltexte verarbeitet werden. In diesem Beispiel werden mit 030\d{3} und 46\d{3} zwei Codearten erkannt und anschließend durch 111111 bzw. 22222 ersetzt, wodurch die Umsetzung einer Stapelersetzung mit mehreren Regeln klar veranschaulicht wird.

Bei der Vorbereitung der tatsächlichen Verarbeitung wird empfohlen, in der Reihenfolge „Originale sichern – wenige Stichproben importieren – Regeln festlegen – linke und rechte Zuordnung prüfen – in neues Verzeichnis ausgeben – Ergebnisse stichprobenartig kontrollieren“ vorzugehen. So lassen sich sowohl der Wert der Bürosoftware bei der Stapelverarbeitung von Dateien und der Reduzierung von Routinearbeit nutzen als auch die Risiken durch zu weite Regeln, unterschiedliche Datentypen oder falsch ausgewählte Dateien verringern.