Wenn Formeln in mehreren XLSX-Tabellendateien gleichzeitig geändert werden müssen, ist das manuelle Öffnen jeder Datei zum Suchen und Ersetzen sehr ineffizient. In diesem Artikel wird anhand des Büroszenarios "Batch-Suche und Ersetzung von Formelausdrücken in Excel-Tabellendateien" demonstriert, wie mit HeSoft Doc Batch Tool Excel-Dateien im Stapel importiert, die Verarbeitung nur auf Zellen mit Formeln beschränkt und der Formelausdruck "*2" durch "*3" ersetzt wird. Anhand von Screenshot-Vergleichen vor und nach der Verarbeitung sowie schrittweisen Anleitungen können Benutzer schnell die Methode zur einheitlichen Änderung von Formeln über mehrere Dateien hinweg erlernen.
In vielen Büroszenarien werden Excel-Dateien häufig stapelweise auf Basis von Vorlagen generiert. Zum Beispiel Produktlisten, Angebotstabellen, Bestelldetails, Bestandsstatistiken, Monatsberichte usw. – jede Abteilung oder jedes Projekt hat möglicherweise eine eigene xlsx-Datei. Wenn die Vorlage erstmalig verwendet wird, funktionieren die Formeln einwandfrei, aber sobald sich die Geschäftsregeln ändern, müssen alle Formeln in allen Dateien entsprechend angepasst werden. Lautete die ursprüngliche Formel beispielsweise =CONCAT(D6*2,E6) und muss nun zu =CONCAT(D6*3,E6) geändert werden, kostet es viel Zeit, wenn man jede Datei einzeln öffnen und bearbeiten muss – bei Dutzenden von Tabellen.
Dieser Artikel stellt eine Methode vor, die sich besser für die Stapelverarbeitung von Bürodateien eignet: Verwenden von HeSoft Doc Batch Tool zur Stapelsuche und -ersetzung von Excel-Formelausdrücken. Es handelt sich um ein Stapelverarbeitungstool für Dokumente in Büroszenarien, das sich für Aufgaben mit vielen sich wiederholenden Vorgängen und einer hohen Dateianzahl eignet. Dieser Artikel wird anhand von Screenshots erklären, wie Sie von der Funktionsauswahl über das Importieren von Dateien und Festlegen der Formelverarbeitungsoptionen bis hin zur endgültigen Überprüfung der Verarbeitungsergebnisse die einheitliche Änderung von Formeln in mehreren Excel-Dateien abschließen können.
Anwendungsszenario: Formelregeln in mehreren Excel-Dateien müssen synchron geändert werden
Wenn Sie auf die folgenden Situationen stoßen, können Sie die Methode der Stapelsuche und -ersetzung von Formelausdrücken in Betracht ziehen:
- Mehrere Excel-/xlsx-Dateien stammen aus derselben Vorlage, die Formelstruktur ist einheitlich, nur über verschiedene Dateien verteilt;
- Multiplikatoren, Koeffizienten oder Einheitenumrechnungsbeziehungen in Formeln müssen einheitlich angepasst werden, z. B. von Multiplikation mit 2 auf Multiplikation mit 3 ändern;
- Es soll die Formel selbst geändert werden, nicht das aktuell in der Zelle angezeigte Berechnungsergebnis;
- Die Anzahl der Dateien ist hoch, und das einzelne Öffnen von Excel zum Suchen und Ersetzen kann leicht zu Auslassungen führen;
- Es sollen Bürodateien stapelweise verarbeitet werden, um wiederholte Arbeit zu reduzieren und die Effizienz der Datenpflege zu erhöhen.
Es ist zu beachten, dass die Stapelersetzung für Aufgaben mit „klaren Regeln" geeignet ist. Beispielsweise soll in diesem Fall der Formelausdruck *2 durch *3 ersetzt werden, das Ziel ist sehr klar. Wenn sich die Formelstrukturen in verschiedenen Dateien stark unterscheiden, sollten Sie zuerst die Formelregelmäßigkeiten analysieren und dann entscheiden, wie der Suchinhalt einzugeben ist.
Effektvorschau: Der Multiplikator in der Formel war vor der Verarbeitung 2
Der Excel-Screenshot vor der Verarbeitung zeigt, dass Zelle F6 ausgewählt ist und in der Bearbeitungsleiste =CONCAT(D6*2,E6) zu sehen ist. Die Überschrift der Spalte F lautet „Remark", das angezeigte Ergebnis ist „2000g". Dies verdeutlicht, dass der Inhalt der Spalte F durch eine Formel generiert wird: Menge in Spalte D multipliziert mit 2, dann verknüpft mit der Einheit aus Spalte E.
Im Screenshot ist auch zu erkennen, dass in vielen Zellen „2000g", in einigen Zeilen „400g" oder „2piece" angezeigt wird. Diese Werte sind kein einfacher statischer Text, sondern die Ergebnisse der Formelberechnung. Wenn die Geschäftsanforderung daher darin besteht, den Multiplikator auf 3 zu ändern, besteht der korrekte Ansatz darin, das *2 in der Formel in *3 zu ändern, damit das Ergebnis automatisch aktualisiert wird, anstatt direkt „2000g" durch „3000g" zu ersetzen.
Effektvorschau: Der Multiplikator in der Formel wurde nach der Verarbeitung zu 3
Nach der Stapelverarbeitung zeigt die Bearbeitungsleiste an derselben Position =CONCAT(D6*3,E6). Gleichzeitig haben sich auch die angezeigten Ergebnisse in Spalte F geändert: Das ursprüngliche „2000g" wurde zu „3000g", das ursprüngliche „400g" wurde zu „600g", das ursprüngliche „2piece" wurde zu „3piece". Dies zeigt, dass der Ersetzungsvorgang auf den Formelausdruck angewendet wurde und neue Berechnungsergebnisse ausgelöst hat.
Genau dieser Effekt ist das Ziel der stapelweisen Änderung von Excel-Formeln: Kein manuelles Bearbeiten Zeile für Zeile, kein Öffnen von Datei für Datei, sondern das einmalige Ersetzen von Schlüsselfragmenten in der Formel, um die Regelkonsistenz über mehrere Dateien hinweg zu wahren.
Vorgehensschritte: Stapelsuche und -ersetzung von Excel-Formelausdrücken
Schritt 1: Die Suchen-und-Ersetzen-Funktion im Excel-Tool finden
Öffnen Sie HeSoft Doc Batch Tool und klicken Sie in der linken Navigation auf „Excel-Tools". Die Programmoberfläche listet verschiedene Excel-bezogene Funktionen auf, wie z. B. Formatkonvertierung, Bildexport, Hinzufügen oder Entfernen von Passwortschutz usw. Für diese Aufgabe benötigen Sie Punkt 1: „Schlüsselwörter in Excel suchen und ersetzen".
Ziel dieses Schrittes ist es, den Funktionseinstieg für die Stapelsuche und -ersetzung von Excel-Inhalten zu finden. Obwohl wir Formelausdrücke verarbeiten möchten, geschieht dies im Programm weiterhin über „Schlüsselwörter in Excel suchen und ersetzen". Nach dem Aufruf führt das Programm den Benutzer assistentengestützt durch die Dateiauswahl, Optionseinstellungen, den Speicherort und den Start der Verarbeitung.
Schritt 2: Einzelne Dateien hinzufügen oder Excel-Dateien aus Ordner importieren
Nach dem Aufrufen der Funktionsseite ist der erste Schritt „Wählen Sie die zu verarbeitenden Datensätze aus". Im Screenshot sehen Sie oben rechts die Schaltflächen „Datei hinzufügen", „Dateien aus Ordner importieren", „Leeren" usw. Wenn nur einige bestimmte Dateien verarbeitet werden sollen, verwenden Sie „Datei hinzufügen"; befinden sich die Dateien gesammelt in einem Ordner, eignet sich „Dateien aus Ordner importieren" besser für die Stapelverarbeitung.
Im Screenshot wurden bereits 4 xlsx-Dateien hinzugefügt, die Dateiliste zeigt Nummer, Name, Pfad, Erweiterung, Erstellungs- und Änderungsdatum. Nach dem Hinzufügen sollten Sie die Liste zunächst überprüfen und sicherstellen, dass Anzahl und Pfade der Dateien korrekt sind. Der Vorteil der Stapelverarbeitung liegt in der gleichzeitigen Verarbeitung mehrerer Dateien, aber nur unter der Voraussetzung, dass alle importierten Dateien zum Umfang dieser Aufgabe gehören. Falls Dateien in der Liste enthalten sind, die nicht geändert werden sollen, können Sie diese vor dem Start der Verarbeitung löschen.
Schritt 3: Verarbeitungsoptionen aufrufen und Zelltext als Verarbeitungsbereich wählen
Sobald die Dateien bestätigt sind, klicken Sie unten auf „Weiter", um zum zweiten Schritt „Verarbeitungsoptionen festlegen" zu gelangen. Im Screenshot ist beim „Verarbeitungsbereich" die Option „Zelltext" aktiviert. Der Grund dafür ist, dass sich die Excel-Formeln innerhalb der Zellen befinden und wir die Formelfragmente im Zellinhalt suchen müssen.
Wenn Ihr Ziel lediglich die Änderung von Formeln ist, ist es nicht empfehlenswert, gleichzeitig „Namen der Tabellenblätter" oder „Text in Formen im Arbeitsblatt" auszuwählen. Die Einschränkung des Verarbeitungsbereichs kann das Risiko von Fehlersetzungen verringern. Bei Stapeloperationen gilt: Je präziser der Bereich, desto kontrollierbarer das Ergebnis.
Schritt 4: Einstellen, dass nur Zellen mit Formeln verarbeitet werden
Unter „Zu verarbeitende Zellformeltypen" ist im Screenshot die Option „Nur Zellen verarbeiten, die Formeln enthalten" ausgewählt. Diese Einstellung schließt normale Textzellen aus und lässt das Programm nur in Zellen suchen und ersetzen, die Formeln enthalten.
Dieser Punkt eignet sich gut für unser Beispiel. Denn wir möchten nicht jedes *2 in allen Zellen ersetzen, sondern nur das *2 in den Formeln ändern. Beispielsweise könnten auch in manchen Beschreibungstexten ähnliche Zeichen vorkommen – ohne die Beschränkung auf Formelzellen könnte es zu unerwünschten Ersetzungen kommen.
Schritt 5: Als Verarbeitungsweise für formelhaltige Zellen „Formelausdruck" wählen
Anschließend ist im Screenshot bei der „Verarbeitungsweise für Zellen, die Formeln enthalten" die Option „Formelausdruck" ausgewählt. Dies bedeutet, dass das Objekt der Suche und Ersetzung der Formeltext selbst ist, also beispielsweise der Ausdruck =CONCAT(D6*2,E6).
Wählen Sie hier nicht versehentlich „Vom Formel berechneter Wert". Bei der Verarbeitung des berechneten Wertes würde das Programm mit Ergebnissen wie „2000g", „400g" usw. arbeiten, nicht mit dem *2 in der Formel. Das Ziel in diesem Beispiel ist die einheitliche Änderung der Formellogik, daher muss der Formelausdruck verarbeitet werden. Nur bei korrekter Einstellung wird das Verarbeitungsergebnis so aussehen wie in der Effektvorschau: Die Formel ändert sich von *2 zu *3, und der Anzeigewert passt sich entsprechend an.
Schritt 6: Suchbegriff und Ersatzbegriff eingeben
Im Bereich „Schlüsselwortoptionen festlegen" ist im Screenshot als „Suchmethode" „Text genau suchen" ausgewählt. In der linken unteren Liste „Liste der zu suchenden Schlüsselwörter" ist *2 eingetragen, in der rechten Liste „Liste der zu ersetzenden Schlüsselwörter" steht *3.
Dieser Schritt legt die Ersetzungsregel fest: In den Excel-Zellformelausdrücken, die die vorherigen Bedingungen erfüllen, wird *2 gefunden und durch *3 ersetzt. Wenn Ihre tatsächliche Anforderung die Änderung anderer Formelfragmente ist, können Sie diese auf die gleiche Weise eintragen. Ersetzen Sie beispielsweise einen bestimmten festen Koeffizienten, festen Text oder ein festes Funktionsfragment durch einen neuen Ausdruck.
Um eine genaue Ersetzung zu gewährleisten, empfiehlt es sich, vor der Eingabe eine Beispieldatei zu öffnen und die tatsächliche Schreibweise in der Bearbeitungsleiste zu überprüfen. Excel-Formeln können Unterschiede in Leerzeichen, absoluten Bezügen oder verschiedenen Funktionsschreibweisen aufweisen. Der Suchinhalt sollte mit dem tatsächlichen Formelfragment übereinstimmen.
Schritt 7: Speicherort festlegen und Verarbeitung ausführen
Nachdem die Verarbeitungsoptionen festgelegt sind, klicken Sie unten auf „Weiter". Entsprechend dem Ablauf oben auf der Seite folgt nun „Speicherort festlegen" und abschließend „Verarbeitung starten". Auch wenn die Screenshots die detaillierten Oberflächen der letzten beiden Schritte nicht zeigen, lässt sich aus dem Assistentenablauf klar ableiten, dass das Programm den Benutzer bei der Wahl des Speichermodus für die Verarbeitungsergebnisse anleitet und die Stapelaufgabe ausführt.
Es wird empfohlen, die verarbeiteten Dateien in einem separaten Verzeichnis zu speichern, um sie leichter von den Originaldateien unterscheiden zu können. Öffnen Sie nach Abschluss der Verarbeitung die Ausgabedatei zur Überprüfung: Sehen Sie nach, ob die Bearbeitungsleiste von =CONCAT(D6*2,E6) zu =CONCAT(D6*3,E6) geändert wurde, und beobachten Sie dann, ob sich der Anzeigewert in Spalte F in neue Ergebnisse wie „3000g" geändert hat. Nach der Bestätigung, dass alles korrekt ist, können die Ergebnisdateien für die weitere Verwendung eingesetzt werden.
Häufig gestellte Fragen und Hinweise
1. Das Formelergebnis wird bereits als Text angezeigt, kann die Formel trotzdem geändert werden?
Ja. Im Screenshot zeigt Spalte F Inhalte wie „2000g" an, aber in der Bearbeitungsleiste ist ersichtlich, dass sie durch eine Formel erzeugt werden. Solange die Zelle selbst eine Formel enthält und im Programm „Nur Zellen verarbeiten, die Formeln enthalten" sowie „Formelausdruck" ausgewählt sind, kann der Formeltext geändert werden.
2. Warum wurde als Verarbeitungsbereich nur Zelltext gewählt?
Weil sich die zu bearbeitende Formel in diesem Fall in den Zellen befindet. Die Auswahl nur des notwendigen Bereichs kann Fehlverarbeitungen reduzieren. Es sei denn, Sie müssen tatsächlich Tabellenblattnamen oder Formtexte ersetzen, ist es nicht empfehlenswert, den Bereich zu erweitern.
3. Müssen die Formeln in mehreren Dateien absolut identisch sein?
Nicht unbedingt identisch, aber sie müssen das zu suchende Formelfragment enthalten. Solange der Formelausdruck beispielsweise *2 enthält und die anderen Verarbeitungsbedingungen erfüllt, kann er ersetzt werden. Wenn die Formelschreibweise in einigen Dateien abweicht, müssen dafür gesondert passendere Suchen-und-Ersetzen-Regeln aufgestellt werden.
4. Was sollte vor der Stapelersetzung überprüft werden?
Es empfiehlt sich, drei Punkte zu prüfen: Erstens, ob die importierten Excel-Dateien alle zu verarbeitenden Dateien dieser Aufgabe sind; zweitens, ob der Suchinhalt hinreichend genau ist; drittens, ob der Speicherort Original- und Ergebnisdateien unterscheidbar macht. Die Stapelverarbeitung ist sehr effizient, aber falsch eingestellte Regeln werden ebenfalls stapelweise ausgeführt, daher ist die Bestätigung vor der Verarbeitung sehr wichtig.
Fazit: Für dateiübergreifende einheitliche Änderungen von Excel-Formeln eignen sich Stapelverarbeitungstools besser
Wenn Formeln in mehreren xlsx-Tabellen einheitlich geändert werden müssen, besteht das Hauptproblem der manuellen Vorgehensweise im Zeitaufwand, der Wiederholung und der leichten Anfälligkeit für Auslassungen. Mit HeSoft Doc Batch Tool können Sie über die Funktion „Schlüsselwörter in Excel suchen und ersetzen" Dateien stapelweise importieren, das Verarbeitungsobjekt auf formelhaltige Zellen und den Formelausdruck beschränken und schließlich *2 durch *3 ersetzen. Diese Methode bewahrt sowohl die Einheitlichkeit der Formelregeln als auch die deutlich reduzierte manuelle Bearbeitungszeit.
Wenn Sie eine große Anzahl von Excel-Berichten, Vorlagen oder Produktlisten verarbeiten, empfiehlt es sich, die Ersetzungsregeln zunächst mit einer kleinen Anzahl von Dateien zu testen, die Wirkung zu bestätigen und dann die Stapelausführung durchzuführen. So kann sowohl die Effizienz gesteigert als auch die Zuverlässigkeit des Ergebnisses der Formeländerung gewährleistet werden.