Wie kann man Nummern in mehreren XLSX-Tabellen stapelweise ersetzen? Anleitung zum Abgleich mit Platzhaltern und zur einheitlichen Änderung

Mehrere xlsx-Tabellen enthalten häufig verstreut Nummern mit gleichem Format, aber unterschiedlichen Werten, z. B. ein fester Präfix gefolgt von mehreren Ziffern. Das Aufzählen alter Werte einzeln ist nicht nur ineffizient, sondern führt auch leicht zu Auslassungen. Dieser Artikel geht von den praktischen Anforderungen der Datenbereinigung in Excel aus und zeigt, wie mithilfe von Platzhalter- und regulären Ausdrucksregeln sechsstellige Nummern mit dem Präfix 030 und fünfstellige Codes mit dem Präfix 46 abgeglichen werden und wie in HeSoft Doc Batch Tool mehrere Arbeitsmappen auf einmal importiert, der Textbereich der Zellen eingeschränkt, Zuordnungen zwischen Suchen und Ersetzen erstellt und schließlich die Ergebnisse einheitlich ausgegeben und überprüft werden.

Unternehmen müssen beim Organisieren von Kundenlisten, Anlagenverzeichnissen, Projektregistern, Regionalcodes oder Geschäftsarchiven häufig Nummerierungen in Excel stapelweise aktualisieren. Die Schwierigkeit besteht darin, dass die alten Daten nicht unbedingt ein einzelner Wert sind, sondern eine Gruppe ähnlich formatierter Werte sein können. Beispielsweise müssen 030006, 030010 und 030148 alle in eine neue einheitliche Nummer geändert werden, doch die gewöhnliche exakte Suche kann jeweils nur einen konkreten Inhalt verarbeiten. Wenn dieselben Daten zudem auf mehrere XLSX-Dateien verteilt sind, vervielfacht sich der manuelle Aufwand.

Für solche Anforderungen besteht die effizientere Methode darin, zunächst die Regelmäßigkeiten der Nummerierung zu analysieren und dann mit Platzhaltern oder regulären Ausdrücken den zu suchenden Inhalt zu beschreiben. Sobald eine Zelle dem Ausdruck entspricht, kann der zugehörige Ersetzungswert angewendet werden. Dieser Artikel erläutert anhand der tatsächlichen Oberfläche, wie Sie mit HeSoft Doc Batch Tool ein einheitliches Suchen und Ersetzen über mehrere Excel-Dateien durchführen. Das Produkt ist als Software zur Stapelverarbeitung von Bürodateien positioniert und eignet sich dafür, wiederkehrende Dateioperationen zu einer Aufgabe zu bündeln, statt dass Benutzer jede Datei einzeln öffnen, bearbeiten und erneut speichern müssen.

1. Das konkrete Problem, das dieses Tutorial löst

Die Beispielarbeitsmappe enthält zwei Arten von Daten, die normiert werden müssen. Die erste Art befindet sich in der Spalte Hospital CCN; die Nummern beginnen alle mit 030, gefolgt von drei weiteren Ziffern. Die zweite Art befindet sich in der Spalte CBSA; die Zieldaten beginnen mit 46, gefolgt von drei weiteren Ziffern. Die Aufgabenanforderungen lauten wie folgt:

• Text, der dem Format „030 plus drei Ziffern“ entspricht, durch 111111 ersetzen;
• Text, der dem Format „46 plus drei Ziffern“ entspricht, durch 22222 ersetzen;
• Dieselben Regeln müssen auf mehrere Excel-Dateien angewendet werden;
• Andere Nummern, die den Regeln nicht entsprechen, sollen unverändert bleiben.

Bei Verwendung der exakten Suche müsste man für die erste Datenart alte Werte wie 030006, 030010, 030011 und 030064 einzeln eingeben. Das wäre nicht nur eine große Anzahl von Regeln, sondern könnte auch Nummern übersehen, die nicht im Voraus erfasst wurden. Mit Ausdrücken genügt es, die gesamte Menge mit „030\d{3}“ zu beschreiben. Genau darin liegt der Hauptnutzen der stapelweisen Ersetzung mit Platzhaltern bei der Excel-Datenbereinigung.

2. Geeignete Szenarien: Welche Excel-Nummern sich regelbasiert ändern lassen

1. Festes Präfix plus laufende Nummer

Beispiele sind Abteilungsnummern wie HR001 und HR002, Projektnummern wie XM202501 und XM202502 oder die Werte 030006 und 030010 aus dem Beispiel. Solche Felder besitzen meist ein festes alphabetisches oder numerisches Präfix; der hintere Teil ist eine laufende Nummer mit fester Länge. Gleichartige Inhalte können mit einem Ausdruck in einem Schritt abgeglichen werden.

2. Migration von Regions- und Klassifikationscodes

Nach organisatorischen Änderungen, Systemaktualisierungen oder geänderten Codierungsstandards müssen alte Regionscodes möglicherweise auf neue einheitliche Codes abgebildet werden. Solange die alten Codes eine klare Regel für Präfix, Länge oder Zeichenzusammensetzung aufweisen, können sie zunächst stapelweise gesucht und anschließend durch den festgelegten Wert ersetzt werden.

3. Standardisierung von Tabellen aus mehreren Quellen

Excel-Vorlagen verschiedener Niederlassungen oder Lieferanten können dieselben Felder enthalten, sich aber in Dateiname und Zeilenzahl unterscheiden. Wenn diese Dateien in einem Schritt in die Bürosoftware importiert und mit demselben Regelsatz verarbeitet werden, lassen sich Standardabweichungen vermeiden, die durch manuelle Bearbeitung durch verschiedene Personen entstehen.

4. Datenbereinigung historischer XLSX-Tabellen

Wenn sich in einem Ordner viele Monats-, Quartals- und Jahresberichte angesammelt haben, können alte Nummern in zahlreichen Arbeitsmappen vorhanden sein. Die Stapelverarbeitung reduziert das Öffnen und Speichern von Dateien. Bevor jedoch verschiedene Excel-Formate wie XLSX oder XLS verarbeitet werden, sollte die Lesbarkeit der Dateien anhand weniger Dateien bestätigt und eine Sicherung der Originaldaten aufbewahrt werden.

3. Vorschau: Vorher-Nachher-Vergleich der stapelweisen Nummernänderung

Excel-Daten vor der Verarbeitung

In der ursprünglichen Arbeitsmappe sind in der Spalte Hospital CCN Werte wie 030006, 030010, 030011, 030064, 030074, 030085, 030111, 030114 und 030148 zu sehen; in der benachbarten Spalte CBSA erscheint fortlaufend 46060. Diese Zahlen wirken optisch ähnlich, doch der tatsächlich in jeder Zelle gespeicherte Inhalt kann unterschiedlich sein.

Die Pfeile im Screenshot markieren die beiden Spalten, die in diesem Beispiel verarbeitet werden sollen. Wenn man jede Zelle einzeln auswählt und ändert, muss man wiederholt Eingaben machen und kann leicht eine Zeile überspringen. Wenn man in Excel in jeder Datei einzeln Suchen und Ersetzen verwendet, muss man die Bedingungen wiederum in allen Arbeitsmappen neu konfigurieren. Daher eignet sich diese Aufgabe besser dafür, einen wiederverwendbaren Regelsatz zu erstellen.

Excel-Daten nach der Verarbeitung

Nach Abschluss der Stapelverarbeitung wurden alle Nummern, die der Regel mit dem Präfix 030 entsprachen, einheitlich in 111111 geändert, und alle Daten, die der Regel mit dem Präfix 46 entsprachen, einheitlich in 22222. Der gelb markierte Bereich zeigt die wesentlichen Änderungen. Daten wie 010039, 010052, 010065, 010079, 010131 und 010175, die den Ausdrücken nicht entsprechen, wurden nicht einheitlich ersetzt.

Das Ergebnis zeigt, dass die Regeln die Zielinhalte anhand der Textstruktur auswählen können, statt einfach alle Zellen einer ganzen Spalte zu ändern. Für reale Geschäftsdaten ist dieses Vorgehen, nur passende Inhalte zu verarbeiten, besser steuerbar als das Überschreiben einer gesamten Spalte – vorausgesetzt, die Ausdrücke sind präzise genug formuliert.

4. Vollständige Arbeitsschritte: Nummern in mehreren XLSX-Dateien stapelweise ersetzen

Schritt 1: Das Werkzeug zum Suchen und Ersetzen von Excel-Schlüsselwörtern öffnen

Starten Sie HeSoft Doc Batch Tool und suchen Sie in der linken Navigationsleiste den Punkt „Excel-Tools“. Auf der rechten Seite werden die mit Excel verbundenen Stapelverarbeitungsfunktionen angezeigt; die erste Funktion lautet „Schlüsselwörter in Excel suchen und ersetzen“. Klicken Sie auf diese Funktion, um die Aufgabenseite zu öffnen.

Dieser Schritt dient dazu, das richtige Verarbeitungsmodul auszuwählen. Da sich die Aufgabe auf Zelltexte innerhalb von Arbeitsmappen bezieht, sollten keine anderen Werkzeuge wie Dateinamen-, Ordnernamen- oder Excel-Formatkonvertierung gewählt werden. Nach dem Öffnen der Funktion ist zu erkennen, dass die Aufgabe aus den Phasen Datensätze auswählen, Verarbeitungsoptionen festlegen, Speicherort festlegen und Verarbeitung starten besteht.

Schritt 2: Mehrere Excel-Arbeitsmappen auf einmal importieren

In der ersten Phase können Sie über „Dateien hinzufügen“ mehrere Excel-Dateien manuell auswählen. Liegen die Zieldateien gesammelt im selben Verzeichnis, können Sie auch „Dateien aus Ordner importieren“ verwenden. Im Beispiel wurden product_list.xlsx, team-participant-list.xlsx und test.xlsx importiert; unten auf der Seite wird die Datensatzanzahl mit 3 angezeigt.

Die Dateiliste zeigt Name, Pfad, Erweiterung, Erstellungszeit und Änderungszeit. Vor der eigentlichen Verarbeitung sollten Sie insbesondere die Dateipfade prüfen, um zu vermeiden, dass Sicherungsverzeichnisse, bereits verarbeitete Verzeichnisse oder nicht zugehörige Tabellen in die Aufgabe aufgenommen werden. Falls eine Datei versehentlich ausgewählt wurde, können Sie sie über die Löschschaltfläche in der entsprechenden Zeile entfernen. Soll die Auswahl vollständig neu erfolgen, klicken Sie auf „Leeren“. Wenn die Datensätze bestätigt sind, klicken Sie auf „Weiter“.

Das erwartete Ergebnis ist, dass alle Dateien, auf die dieselben Nummernersetzungsregeln angewendet werden sollen, in die Aufgabenliste aufgenommen werden. Bei Dutzenden oder mehr strukturell ähnlichen Tabellen ist der Import aus einem Ordner meist zeitsparender als die Auswahl jeder einzelnen Datei.

Schritt 3: Verarbeitungsbereich auf Zelltext beschränken

Auf der Seite „Verarbeitungsoptionen festlegen“ konfigurieren Sie zuerst den „Verarbeitungsbereich“. In der Oberfläche sind die drei Optionen „Zelltext“, „Name des Arbeitsblatts“ und „Text in Formen auf dem Arbeitsblatt“ zu sehen. Da in diesem Beispiel die Nummern in den Spalten Hospital CCN und CBSA geändert werden sollen, aktivieren Sie „Zelltext“.

Arbeitsblattnamen und Formtexte sind nicht Ziel dieser Aufgabe und müssen daher nicht gleichzeitig aktiviert werden. Durch die richtige Eingrenzung des Verarbeitungsobjekts verringert sich die Wahrscheinlichkeit, dass die Regeln auf Titel, grafische Beschriftungen oder andere Positionen angewendet werden.

Schritt 4: Zellformeltyp und Datentyp festlegen

Im Screenshot ist beim Punkt „Zu verarbeitender Zellformeltyp“ die Option „Keine Einschränkung“ gewählt. Wenn die Arbeitsmappe sowohl normale Zellen als auch Zellen enthält, deren Ergebnisse durch Formeln erzeugt werden, schließt diese Einstellung Inhalte nicht vorab anhand der Formeleigenschaft aus. Die Oberfläche bietet außerdem „Nur Zellen mit Formeln verarbeiten“ und „Nur Zellen ohne Formeln verarbeiten“ an; in der Praxis können Sie je nach Datenstruktur wählen.

Für Zellen mit Formeln ist im Screenshot „Wert nach Formelberechnung“ gewählt. Das bedeutet, dass der Schwerpunkt der Suche auf dem letztlich angezeigten Ergebnis der Formel liegt und nicht auf dem Formelausdruck selbst. Soll dagegen die Formelzeichenfolge geändert werden, ist das sorgfältig abzuwägen und eine zur Aufgabe passende Verarbeitungsweise zu wählen.

Beim Punkt „Zu verarbeitender Zell-Datentyp“ ist „Text“ aktiviert. Nummern sehen häufig wie reine Zahlen aus, können in Excel jedoch als Text gespeichert sein, um führende Nullen zu erhalten. Genau dafür ist 030006 im Beispiel ein typischer Fall. Wenn die Quelldaten tatsächlich als Zahlen gespeichert sind, muss die Datentypauswahl an den Dateiinhalt angepasst werden; verlassen Sie sich nicht allein auf den visuellen Eindruck.

Schritt 5: Unscharfe Formelsuche für Text aktivieren

Unter „Suchmodus“ im Bereich „Schlüsselwortoptionen festlegen“ bietet die Oberfläche „Exakte Textsuche“ und „Unscharfe Formelsuche für Text“. Soll ein vollständig fester Begriff gesucht werden, kann die exakte Suche gewählt werden. In diesem Beispiel soll jedoch eine Gruppe unterschiedlicher Nummern abgeglichen werden, daher wählen Sie „Unscharfe Formelsuche für Text“.

Die „unscharfe Formelsuche“ übernimmt hier die regelbasierte Textübereinstimmung. Benutzer müssen nicht jeden alten Nummernwert einzeln auflisten, sondern können über Ziffernpräfix und Stellenzahl einen regulären Ausdruck mit Platzhaltern konstruieren.

Schritt 6: Suchausdrücke und Ersetzungswerte eingeben

Geben Sie in der linken Liste „Zu suchende Schlüsselwörter“ zwei Zeilen ein. Zeile 1 lautet „030\d{3}“, Zeile 2 lautet „46\d{3}“. Dabei steht „\d“ für eine einzelne Ziffer und „{3}“ für drei aufeinanderfolgende Ziffern. Die erste Regel kann daher sechsstellige Texte wie 030006, 030010 und 030148 abgleichen, die zweite Regel fünfstellige Texte wie 46060.

Geben Sie in der rechten Liste „Ersetzte Schlüsselwörter“ in Zeile 1 den Wert 111111 und in Zeile 2 den Wert 22222 ein. Linke und rechte Seite werden über die Zeilennummer einander zugeordnet: Die erste Suchregel entspricht dem ersten Ersetzungswert, die zweite Suchregel dem zweiten Ersetzungswert.

Nach der Eingabe sollten Sie die Zeilen einzeln prüfen. Achten Sie besonders darauf, ob Backslash, Ziffernanzahl und geschweifte Klammern im Ausdruck korrekt sind und ob die Ersetzungsliste nicht zeilenverschoben ist. Bei vielen Regeln können Sie die Zuordnungsliste zunächst extern vorbereiten und dann in die entsprechenden Listen kopieren, um manuelle Eingabefehler zu reduzieren.

Schritt 7: Arbeitsblatt- oder Spaltenbereich bei Bedarf eingrenzen

Auf der Einstellungsseite sind außerdem Schalter wie „Der Name des Arbeitsblatts muss diese Bedingung erfüllen“ und „Der Spaltenname der Zelle muss diese Bedingung erfüllen“ zu sehen. Im Beispiel sind diese Bedingungen nicht aktiviert, das heißt, die Aufgabe schränkt bestimmte Arbeitsblattnamen oder Spaltennamen nicht weiter ein.

Wenn in anderen Arbeitsblättern derselben Arbeitsmappe ebenfalls gleich formatierte Nummern auftreten können, diese Daten aber nicht geändert werden sollen, sollten Sie die entsprechenden Bedingungen aktivieren, um den Bereich einzugrenzen. Sollen dagegen die Zielnummern in allen Arbeitsblättern einheitlich verarbeitet werden, sind keine zusätzlichen Einschränkungen erforderlich. Bereichsbedingungen sollten anhand der Dateistruktur festgelegt und nicht pauschal aktiviert werden.

Schritt 8: Speicherort für Ergebnisse festlegen

Klicken Sie nach Abschluss der Verarbeitungsoptionen auf „Weiter“ und folgen Sie dem oberen Ablauf zu „Speicherort festlegen“. Vor dem Start einer Stapelaufgabe empfiehlt es sich, ein gut erkennbares neues Verzeichnis für die Ergebnisse zu wählen, damit die verarbeiteten Dateien von den Originaldateien getrennt bleiben. Insbesondere bei der ersten Verwendung neuer Ausdrücke ist es nicht ratsam, nur eine einzelne Datei zu behalten und direkt zu überschreiben.

Sie können in den Namen des Ausgabeverzeichnisses Begriffe wie „ersetzt“, „neue Nummer“ oder das Verarbeitungsdatum aufnehmen, um die spätere Kontrolle zu erleichtern. Nachdem der Speicherort bestätigt ist, wechseln Sie zur Phase „Verarbeitung starten“.

Schritt 9: Verarbeitung starten und Ausgabedateien stichprobenartig prüfen

Nach dem Start verarbeitet die Software die Arbeitsmappen in der Dateiliste gemäß den festgelegten Regeln. Prüfen Sie nach Abschluss nicht nur, ob die Aufgabe beendet ist, sondern öffnen Sie auch die Ausgabedateien, um die Daten zu verifizieren.

Es wird empfohlen, mindestens Folgendes zu prüfen: ob die ursprünglichen 030006, 030010 usw. zu 111111 geworden sind; ob 46060 zu 22222 geworden ist; ob Werte, die den Regeln nicht entsprechen, wie 010039, 26620 und 10760, unverändert geblieben sind; und ob die Arbeitsblattstruktur sowie andere Geschäftsfelder normal sind. Erst nach bestandener Stichprobe sollten die Ergebnisse für die spätere Zusammenführung oder den Systemimport verwendet werden.

5. Sicherere reguläre Ausdrücke für Excel-Abgleiche formulieren

1. Zuerst die festen Bestandteile bestimmen

Die festen Bestandteile sind eine wichtige Grundlage, um Zieldaten von anderen Inhalten zu unterscheiden. In diesem Beispiel dienen 030 und 46 als Präfixe. Würde man nur „beliebig viele Ziffern“ schreiben, könnten auch Daten, Beträge, Mengen oder andere Nummern in der Arbeitsmappe getroffen werden; der Bereich wäre zu groß.

2. Danach den Typ der variablen Zeichen bestimmen

Im Beispiel bestehen die variablen Teile ausschließlich aus Ziffern, daher wird „\d“ verwendet. Enthalten die Daten auch Buchstaben, Leerzeichen oder Bindestriche, sollten Sie zuerst echte Stichproben ansehen und die Regel anschließend anhand der von der Software unterstützten Ausdrucksweise formulieren, statt direkt eine Ziffernregel zu übernehmen.

3. Zeichenanzahl präzise begrenzen

„{3}“ steht für drei aufeinanderfolgende Ziffern. Durch die Längenbegrenzung wird vermieden, dass Codes unterschiedlicher Länge gemeinsam verarbeitet werden. Wenn im Geschäftskontext gleichzeitig 03001 und 030001 auftreten, müssen diese getrennt analysiert werden; sie dürfen nicht standardmäßig als dieselbe Nummernklasse behandelt werden.

4. Mit einer kleinen Stichprobe validieren

Ein Ausdruck kann syntaktisch korrekt erscheinen, aber aufgrund des Zell-Datentyps, versteckter Zeichen oder uneinheitlicher Nummernlängen zu anderen Ergebnissen führen. Am zuverlässigsten ist es, eine Arbeitsmappe als Testdatei zu kopieren und nach der Ausführung Treffer und Nicht-Treffer einzeln zu prüfen.

6. Häufige Fragen und Hinweise

Warum wurden manche scheinbar gleichen Nummern nicht ersetzt?

Mögliche Ursachen sind unterschiedliche Zelltypen, Leerzeichen in der Nummer, bereits verlorene führende Nullen, eine nicht zur Regel passende tatsächliche Stellenzahl oder Inhalte, die außerhalb des aktivierten Verarbeitungsbereichs liegen. Klicken Sie die Zelle an und prüfen Sie den tatsächlichen Inhalt in der Bearbeitungsleiste, bevor Sie die Regel anpassen.

Warum hat eine Regel zu viele Daten ersetzt?

Meist fehlt der Regel ein festes Präfix oder eine Längenbegrenzung. Beschreiben Sie die Zielnummer möglichst vollständig und grenzen Sie Arbeitsblattname, Spaltenname und Datentyp entsprechend der tatsächlichen Gegebenheiten ein. Je konkreter die Regel, desto geringer ist in der Regel das Risiko von Fehltreffern.

Müssen Suchliste und Ersetzungsliste dieselbe Anzahl haben?

Damit jede Suchregel ein eindeutiges Ersetzungsergebnis besitzt, wird empfohlen, die linke und rechte Liste zeilenweise zuzuordnen und in gleicher Anzahl zu halten. Soll eine Zeile nicht ersetzt werden, löschen oder ordnen Sie sie vor der Ausführung neu; behalten Sie keine unklaren Leerzuordnungen bei.

Wie soll man Zellen mit Formeln behandeln?

Soll nur das von der Formel angezeigte Endergebnis gesucht werden, können Sie wie im Screenshot „Wert nach Formelberechnung“ wählen. Betrifft das Ziel dagegen die Formelzeichenfolge selbst, muss zuerst geklärt werden, ob eine Änderung der Formel die Berechnungslogik beeinflusst. Bei wichtigen Berichten sollte zuerst in einer Kopie getestet werden.

Wie lässt sich verhindern, dass eine Stapelersetzung nicht rückgängig gemacht werden kann?

Bewahren Sie die Originaldateien auf, legen Sie ein separates Ausgabeverzeichnis fest und verarbeiten Sie zunächst nur wenige Stichproben. Stapelverarbeitung erhöht zwar die Effizienz, verstärkt aber auch die Auswirkungen fehlerhafter Regeln. Daher sind Sicherung und Stichprobenprüfung nicht optionale Schritte.

7. Fazit

Wenn alte Nummern in mehreren Excel- oder XLSX-Dateien ein festes Präfix, feste Zeichentypen und feste Längenregeln aufweisen, müssen sie nicht mehr einzeln aufgelistet und geändert werden. Mit Platzhalterausdrücken wie „030\d{3}“ und „46\d{3}“ lässt sich eine ganze Gruppe von Zieldaten in einem Schritt beschreiben; die Treffer können anschließend getrennt durch 111111 und 22222 ersetzt werden.

HeSoft Doc Batch Tool führt Dateiimport, Verarbeitungsbereich, Formeltyp, Datentyp, Schlüsselwortregeln und Speicherort in einem Assistenten zusammen und eignet sich für stark repetitive Bürodateiaufgaben. Es wird empfohlen, zunächst ein bis zwei XLSX-Kopien zu testen, die Übereinstimmung und die Ersetzungsergebnisse zu prüfen und erst danach alle Tabellen aus dem Ordner zu importieren und stapelweise auszuführen. So verkürzt sich die Zeit für wiederholte Arbeitsschritte, und zugleich wird sichergestellt, dass mehrere Dateien nach einem einheitlichen Datenänderungsstandard bearbeitet werden.