Tutorial: Bereinigen und Normalisieren von Excel-Arbeitsmappendaten

Artikel
11/29/2023

In diesem Tutorial erfahren Sie, wie Sie Daten aus einer Arbeitsmappe mit einem Office-Skript für Excel lesen. Sie werden ein neues Skript schreiben, das einen Kontoauszug formatiert und die Daten in diesem Auszug normalisiert. Im Rahmen der Datenbereinigung liest Ihr Skript Werte aus den Buchungszellen, wendet auf jeden Wert eine einfache Formel an und schreibt die resultierende Antwort in die Arbeitsmappe. Durch das Lesen von Daten aus der Arbeitsmappe können Sie einige Ihrer Entscheidungsfindungsprozesse im Skript automatisieren.

Tipp

Wenn Sie noch nicht mit Office-Skripts vertraut sind, empfehlen wir Ihnen, mit Tutorial: Erstellen und Formatieren einer Excel-Tabelle zu beginnen. Office-Skripts verwenden TypeScript, und dieses Lernprogramm richten sich an Anfänger bis Fortgeschrittene mit JavaScript oder TypeScript. Wenn Sie noch nicht mit JavaScript vertraut sind, empfehlen wir Ihnen, mit dem Mozilla-JavaScript-Lernprogramm zu beginnen.

Voraussetzungen

Für dieses Tutorial benötigen Sie Zugriff auf Office-Skripts. Überprüfen Sie die Plattformunterstützung , wenn die Registerkarte Automatisieren nicht angezeigt wird.

Lesen einer Zelle

Mit dem Action Recorder erstellte Skripte können nur Informationen in die Arbeitsmappe schreiben. Mit dem Code-Editor können Sie Skripte bearbeiten und erstellen, die auch Daten aus einer Arbeitsmappe lesen.

Erstellen Sie zunächst ein Skript, das Daten liest und basierend auf dem Gelesenen handelt. Im gesamten Tutorial arbeiten Sie mit einem Beispiel-Bankauszug. Diese Erklärung ist ein kombinierter Prüfungs- und Gutschrift-Kontoauszug. Leider ändert sich die Bilanz der Bank anders. Der Prüfungskontoauszug gibt die Einnahmen als positive Gutschrift und die Kosten als negative Belastung an. Der Gutschrift-Kontoauszug macht das Gegenteil.

Im weiteren Verlauf des Tutorials normalisieren Sie diese Daten mithilfe eines Skripts. Zunächst müssen Sie Daten aus der Arbeitsmappe lesen.

Erstellen Sie ein neues Arbeitsblatt in der Arbeitsmappe, die Sie für den Rest des Lernprogramms verwendet haben.

Kopieren Sie die folgenden Daten und fügen Sie sie ab Zelle A1 in das neue Arbeitsblatt ein.

Datum	Konto	Beschreibung	Lastschrift	Guthaben
10.10.2019	Wird geprüft	Coho Vineyard	-20.05
11.10.2019	Guthaben	The Phone Company	99.95
13.10.2019	Guthaben	Coho Vineyard	154.43
15.10.2019	Wird geprüft	Externe Einzahlung		1000
20.10.2019	Guthaben	Coho Vineyard – Rückerstattung		-35.45
25.10.2019	Wird geprüft	Best For You Organics Company	-85.64
01.11.2019	Wird geprüft	Externe Einzahlung		1000

Wechseln Sie zur Registerkarte Automatisieren , und wählen Sie Neues Skript aus.

Bereinigen Sie die Formatierung. Dies ist ein Finanzdokument. Daher sollte Ihr Skript die Zahlenformatierung in den Spalten Debit und Credit so ändern, dass Werte als Dollarbeträge angezeigt werden. Lassen Sie ihr Skript außerdem die Spaltenbreite an die Daten anpassen.

Ersetzen Sie den Skriptinhalt durch den folgenden Code:

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();

    // Format the range to display numerical dollar amounts.
    selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");

    // Fit the width of all the used columns to the data.
    selectedSheet.getUsedRange().getFormat().autofitColumns();
}

Lesen Sie nun einen Wert aus einer der Zahlenspalten. Fügen Sie am Ende des Skripts (vor dem schließenden }) den folgenden Code hinzu.
```
// Get the value of cell D2.
let range = selectedSheet.getRange("D2");
console.log(range.getValues());
```
Führen Sie das Skript aus.
Sie sollten [Array[1]] in der Konsole sehen. Dies ist keine Zahl, da Bereiche zweidimensionale Datenfelder sind. Dieser zweidimensionale Bereich wird direkt in der Konsole protokolliert. Glücklicherweise können Sie mit dem Code-Editor den Inhalt des Arrays anzeigen.
Wenn ein zweidimensionales Array in der Konsole protokolliert wird, werden Spaltenwerte unter jeder Zeile gruppiert. Erweitern Sie das Array-Protokoll, indem Sie das blaue Dreieck auswählen.
Erweitern Sie die zweite Ebene des Arrays, indem Sie das neu angezeigte blaue Dreieck auswählen. Sie sollten jetzt Folgendes sehen:

Ändern Sie den Wert einer Zelle

Da Ihr Skript nun Daten lesen kann, verwenden Sie diese Daten, um die Arbeitsmappe zu ändern. Machen Sie den Wert der Zelle D2 mit der Math.abs Funktion positiv. Das Math-Objekt enthält viele Funktionen, auf die Ihre Skripte Zugriff haben. Weitere Informationen zu Math und andere integrierte Objekte finden Sie unter Verwenden von integrierten JavaScript-Objekten in Office-Skripts.

Verwenden Sie getValue die Methoden und setValue , um den Wert der Zelle zu ändern. Diese Methoden funktionieren bei einer einzelnen Zelle. Wenn Sie mehrere Zellbereiche bearbeiten möchten, verwenden Sie getValues und setValues. Fügen Sie am Ende des Skripts den folgenden Code hinzu.
```
// Run the `Math.abs` method with the value at D2 and apply that value back to D2.
let positiveValue = Math.abs(range.getValue() as number);
range.setValue(positiveValue);
```
Hinweis

Der zurückgegebene Wert wird unter Verwendung des Schlüsselworts as von range.getValue()in numberumgewandelt. Dies ist notwendig, da ein Bereich aus Zeichenfolgen, Zahlen oder booleschen Werten bestehen kann. In diesem Fall wird explizit eine Zahl benötigt.
Der Wert von Zelle D2 sollte jetzt positiv sein.

Ändern Sie die Werte einer Spalte

Nachdem Sie nun wissen, wie Sie eine einzelne Zelle lesen und schreiben, können Sie das Skript generalisieren, um die gesamte Debit- und Credit-Spalte zu bearbeiten.

Entfernen Sie den Code, der nur eine einzelne Zelle betrifft (den vorherigen Absolutwertcode), sodass Ihr Skript jetzt folgendermaßen aussieht:

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();

    // Format the range to display numerical dollar amounts.
    selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");

    // Fit the width of all the used columns to the data.
    selectedSheet.getUsedRange().getFormat().autofitColumns();
}

Fügen Sie am Ende des Skripts eine Schleife hinzu, die die Zeilen in den letzten beiden Spalten durchläuft. Für jede Zelle setzt das Skript den Wert auf den absoluten Wert des aktuellen Werts.

Beachten Sie, dass das Array, das die Zellenpositionen definiert, auf Null basiert. Das heißt, Zelle A1 ist range[0][0].
```
// Get the values of the used range.
let range = selectedSheet.getUsedRange();
let rangeValues = range.getValues();

// Iterate over the fourth and fifth columns and set their values to their absolute value.
let rowCount = range.getRowCount();
for (let i = 1; i < rowCount; i++) {
    // The column at index 3 is column "4" in the worksheet.
    if (rangeValues[i][3] != 0) {
        let positiveValue = Math.abs(rangeValues[i][3] as number);
        selectedSheet.getCell(i, 3).setValue(positiveValue);
    }

    // The column at index 4 is column "5" in the worksheet.
    if (rangeValues[i][4] != 0) {
        let positiveValue = Math.abs(rangeValues[i][4] as number);
        selectedSheet.getCell(i, 4).setValue(positiveValue);
    }
}
```
Dieser Teil des Skripts führt mehrere wichtige Aufgaben aus. Zunächst werden die Werte und die Zeilenanzahl des verwendeten Bereichs abgerufen. Dadurch kann das Skript werte untersuchen und wissen, wann es beendet werden soll. Zweitens durchläuft es den verwendeten Bereich und überprüft jede Zelle in den Spalten Lastschrift und Gutschrift. Wenn der Wert in der Zelle nicht 0 ist, wird er durch seinen absoluten Wert ersetzt. Das Skript ignoriert Nullen, sodass Sie die leeren Zellen unverändert lassen können.
Führen Sie das Skript aus.

Ihr Kontoauszug sollte nun ordnungsgemäß formatierte positive Zahlen aufweisen.

Nächste Schritte

Öffnen Sie den Code-Editor, und probieren Sie einige unserer Beispielskripts für Office-Skripts in Excel aus. Weitere Informationen zum Erstellen von Office-Skripts finden Sie unter Grundlagen für Office-Skripts in Excel .

Die nächste Reihe von Office-Skripts-Lernprogrammen konzentriert sich auf die Verwendung von Office-Skripts mit Power Automate. Erfahren Sie mehr über die Vorteile der Kombination der beiden Plattformen unter Ausführen von Office-Skripts mit Power Automate , oder probieren Sie Tutorial: Aktualisieren einer Kalkulationstabelle aus einem Power Automate-Flow aus, um einen Power Automate-Flow zu erstellen, der ein Office-Skript verwendet.

Freigeben über