In diesem Artikel möchte ich fünf Blätter zu einem einzelnen Blatt zusammenführen. Mit Hilfe von Office Scripts kann ich diesen Prozess automatisieren, indem ich doppelte Daten entferne und Filter sowie Sortierungen anwende, um meinen Bericht zu erstellen.

Vorteile von Office Script

Ein Vorteil der Verwendung von Office Scripts liegt in der erhöhten Sicherheit im Vergleich zu VBA. Egal ob Sie Excel Online oder die reguläre Desktop-Version von Excel verwenden, Office Scripts bieten eine sichere Umgebung für die Automatisierung. Sie müssen die Sicherheitseinstellungen für Makros nicht deaktivieren oder Excel freigeben, um das Skript zu verwenden, was den Prozess reibungslos und problemlos gestaltet.

Sicherheit

OfficeScripts bieten im Vergleich zu VBA eine deutlich verbesserte Sicherheit, da sie nicht auf das Dateisystem, Win32-Bibliotheken oder COM-Bibliotheken zugreifen können. Diese erhöhte Sicherheit macht OfficeScripts zu einer sichereren Option für die Automatisierung von Aufgaben.

Abdeckung

Office Scripts bieten umfassende Abdeckung für Excel im Web und unterstützen verschiedene Szenarien. Darüber hinaus entwickeln sie sich kontinuierlich weiter, um neue webbasierte Funktionen zu unterstützen, einschließlich des Action Recorders und der JavaScript-APIs.

Es ist jedoch wichtig zu beachten, dass Office Scripts keine Ereignisse auf Excel-Ebene unterstützen. Stattdessen werden Skripte ausgelöst, wenn ein Benutzer sie manuell startet oder wenn sie von einem Power Automate-Flow aufgerufen werden.

Power Automate

Durch die Integration von Office Scripts mit Power Automate können Sie Aufgaben in Ihrem Arbeitsbuch mühelos automatisieren. Ob durch geplante oder ereignisgesteuerte Abläufe, Sie können Ihr Arbeitsbuch aktualisieren, ohne Excel manuell öffnen zu müssen, was Ihre Arbeitsabläufe vereinfacht und automatisiert.

Office Scripts: Zusammenfügen von Daten aus verschiedenen Blättern

In diesem Szenario haben wir fünf Tabellen auf verschiedenen Tabellenblättern, und unser Ziel ist es, diese Tabellen in ein einziges Tabellenblatt zusammenzuführen. Um dies zu erreichen, werden wir Office Script verwenden, um den Prozess zu automatisieren.

Schritt 1: Öffnen Sie Excel Online oder Excel Desktop

In diesem Beispiel habe ich Excel Desktop verwendet.

Schritt 2: Erstellen Sie ein neues Skript

Öffnen Sie die Excel-Arbeitsmappe, die die Daten mit den Duplikaten enthält, die Sie entfernen möchten.
Klicken Sie auf die Registerkarte „Automatisieren“ im Menüband und wählen Sie „Neues Skript“ aus.
Der Code-Editor wird auf der rechten Seite angezeigt.

Schritt 3: Das Skript umbenennen

Klicken Sie auf das rote Symbol mit dem Namen ‚Script‘ und benennen Sie es in ‚Bericht generieren‘ um. Drücken Sie dann die Eingabetaste.

Im Code-Editor sehen Sie ein Code-Fenster auf der rechten Seite. Kopieren Sie den folgenden Office Script-Code in den Editor und klicken Sie auf die Schaltfläche „Speichern“.

function main(workbook: ExcelScript.Workbook) {
  // Arbeitsblattnamen festlegen, von denen Tabellen abgerufen werden sollen.
  const sheetNames = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5'];

  // Das Arbeitsblatt "Ergebnis" löschen, falls vorhanden.
  workbook.getWorksheet('Result')?.delete();

  // Ein neues Arbeitsblatt mit dem Namen "Ergebnis" für die kombinierte Tabelle erstellen.
  const newSheet = workbook.addWorksheet('Result');

  // Eine neue Tabelle mit denselben Überschriften wie bei den anderen Tabellen erstellen.
  const headerValues = workbook.getWorksheet(sheetNames[0]).getTables()[0].getHeaderRowRange().getTexts();
  const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length - 1, headerValues[0].length - 1);
  targetRange.setValues(headerValues);
  const combinedTable = newSheet.addTable(targetRange.getAddress(), true);

  // Durch jedes aufgelistete Arbeitsblatt gehen und deren Tabellen abrufen.
  sheetNames.forEach((sheet) => {"
  sheetNames.forEach((sheet) => {
    const tables = workbook.getWorksheet(sheet).getTables();
    for (let table of tables) {
      // Die Zeilen aus den Tabellen abrufen.
      let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
      let rowCount = table.getRowCount();

      // Wenn Daten in der Tabelle vorhanden sind, diese zur kombinierten Tabelle hinzufügen.
      if (rowCount > 0) {
        combinedTable.addRows(-1, dataValues);
      }
    }
  });

  newSheet.activate();

}

Schritt 4: Skript ausführen:

Klicken Sie auf die Schaltfläche „Ausführen“ (▶), um das Skript auszuführen.

Schritt 5: Überprüfen der Ergebnisse:

Nachdem das Skript ausgeführt wurde, wird ein neues Arbeitsblatt mit dem Namen „Result“ erstellt und die Daten aus den angegebenen Blättern darin eingefügt.

Dieses Skript kombiniert Daten aus mehreren Tabellenblättern oder Arbeitsmappen zu einem umfassenden Bericht. Sie können das Skript weiter anpassen, um Berechnungen durchzuführen, den Bericht zu formatieren oder zusätzliche Datenquellen hinzuzufügen, falls erforderlich.

Office Scripts: Automatisches Entfernen von Duplikaten

Nachdem zusammenführen der Datensätze möchte ich sicherstellen, dass es keine Duplikate in den Daten gibt. Um das zu erreichen, folgen wir diesen Schritten.

Schritt 1: Erstellen eines neuen Skripts

Öffnen Sie die Excel-Arbeitsmappe, die die Daten mit Duplikaten enthält, die Sie entfernen möchten.
Klicken Sie auf die Registerkarte „Automatisieren“ im Menüband und wählen Sie „Neues Skript“.
Der Code-Editor wird rechts angezeigt.

Schritt 2: Umbenennen des Skripts

Klicken Sie auf das rote Symbol mit dem Namen „Script“ und benennen Sie es dann in “ Remove Duplicate“ um. Drücken Sie die Eingabetaste.

Im Code-Editor sehen Sie ein Code-Fenster auf der rechten Seite. Kopieren Sie den folgenden Office Script-Code in den Editor und klicken Sie auf die Schaltfläche „Speichern“.

function main(workbook: ExcelScript.Workbook) {
  let tblResult = workbook.getTable("tblResult");
  // Duplikate aus der Tabelle "tblResult" entfernen
  tblResult.getRange().removeDuplicates([0,1,2,3,4,5], true);
}

„tblResult“ ist der Name der Tabelle. Um den Tabellennamen zu erfahren, gehen Sie zum Register „Tabellenentwurf“.

Schritt 3: Skript ausführen:

Klicken Sie auf die Schaltfläche „Ausführen“ (▶), um das Skript auszuführen.

Ergebnisse überprüfen:

Nachdem das Skript ausgeführt wurde, werden Duplikate automatisch aus dem angegebenen Bereich entfernt.

Bitte beachten Sie, dass das Ausführen des Skripts Änderungen direkt an Ihren Excel-Daten vornimmt. Stellen Sie sicher, dass Sie eine Sicherungskopie erstellen oder ein Testblatt verwenden, bevor Sie es auf wichtigen Daten ausführen.

Office Scripts: Sortieren und Filtern

Schritt 1: Neues Skript erstellen

Klicken Sie auf die Registerkarte „Automatisieren“ in der Menüleiste und wählen Sie „Neues Skript“.
Der Code-Editor wird auf der rechten Seite angezeigt.

Schritt 2: Umbenennen des Skripts

Klicken Sie auf das rote Symbol mit dem Namen „Script“ und benennen Sie es dann in “ Filter and Sort“ um. Drücken Sie die Eingabetaste.

Im Code-Editor sehen Sie ein Code-Fenster auf der rechten Seite. Kopieren Sie den folgenden Office Script-Code in den Editor und klicken Sie auf die Schaltfläche „Speichern“.

function main(workbook: ExcelScript.Workbook) {
  let tblResult = workbook.getTable("tblResult");
  // Filter mit ausgewählten Elementen auf die Spalte "Gender" der Tabelle "tblResult" anwenden
  tblResult.getColumnByName("Gender").getFilter().applyValuesFilter(["Male"]);
"  
// Sortiere die Tabelle ""tblResult"" nach dem Spaltenindex '5'"
  tblResult.getSort().apply([{key: 5, ascending: false}]);
}

Schritt 3: Skript ausführen:

Klicken Sie auf die Schaltfläche „Ausführen“ (▶), um das Skript auszuführen.

Nachdem das Skript ausgeführt wurde, werden die Daten in Ihrem Datensatz automatisch nach Ihren angegebenen Kriterien sortiert und gefiltert.

Dieses Skript zeigt, wie Sie Daten in Excel mithilfe von Office Scripts sortieren und filtern können, was besonders nützlich ist, wenn Sie mit großen Datensätzen arbeiten. Sie können das Skript anpassen, um zusätzliche Datenanalyse oder Berechnungen entsprechend Ihren spezifischen Anforderungen durchzuführen.