教程：清理和规范化 Excel 工作簿数据

项目
11/29/2023

本教程介绍如何使用 Office 脚本 for Excel 从工作簿读取数据。你将编写一个新脚本，该脚本可设置银行对帐单的格式并规范化该对帐单中的数据。在此数据清理过程中，你的脚本将从事务单元格中读取值，将一个简单的公式应用到每个值，并将生成的答案写入工作簿。通过从工作簿中读取数据，可在脚本中自动执行某些决策过程。

提示

如果不熟悉 Office 脚本，建议从教程：创建 Excel 表格并设置其格式开始。 Office 脚本使用 TypeScript，本教程面向在 JavaScript 或 TypeScript 方面具备初级到中级知识的人员。如果你不熟悉 JavaScript，建议从 Mozilla JavaScript 教程入手。

先决条件

在本教程中，你需要访问 Office 脚本。如果未显示“自动”选项卡，请查看平台支持。

读取单元格

使用操作录制器创建的脚本只能将信息写入工作簿。借助代码编辑器，可以编辑并创建也从工作簿中读取数据的脚本。

首先创建一个脚本来读取数据并根据读取的内容执行操作。在本教程中，你将使用示例银行对帐单。此帐单是结合了支票和信贷的帐单。不幸的是，银行报告余额的变化不同。支票帐单将收入作为正面信贷，将费用作为负面借记。信贷帐单与之相反。

在本教程的其余部分，你将使用脚本规范化此数据。首先，需要从工作簿中读取数据。

在用于教程其余部分的工作簿中创建新工作表。

复制以下数据，并将其粘贴到新工作表中，从单元格 A1 开始。

日期	帐户	说明	借记	信贷
2019 年 10 月 10 日	支票	Coho Vineyard	-20.05
2019 年 10 月 11 日	信贷	The Phone Company	99.95
2019 年 10 月 13 日	信贷	Coho Vineyard	154.43
2019 年 10 月 15 日	支票	外部存款		1000
2019 年 10 月 20 日	信贷	Coho Vineyard - 退款		-35.45
2019 年 10 月 25 日	支票	Best For You Organics Company	-85.64
2019 年 11 月 1 日	支票	外部存款		1000

转到“ 自动 ”选项卡，然后选择“ 新建脚本”。

清理格式。这是一个财务文档，因此，请让脚本更改 “借方” 和 “信用” 列中的数字格式，以将值显示为美元金额。此外，使脚本适合数据的列宽。

将脚本内容替换为以下代码：

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();
}

现在，从其中一个数字列读取值。在结束 }) 之前，将以下代码添加到脚本 (末尾。
```
// Get the value of cell D2.
let range = selectedSheet.getRange("D2");
console.log(range.getValues());
```
运行脚本。
应在控制台中看到 [Array[1]]。这不是数字，因为区域是数据的二维数组。该二维区域直接记录到控制台。幸运的是，代码编辑器让你能够看到数组的内容。
将二维数组记录到控制台时，它会对每行下面的列值进行分组。通过选择蓝色三角形展开数组日志。
通过选择新展示的蓝色三角形展开数组的第二级。现在，你应该会看到：

修改单元格的值

现在，脚本可以读取数据，请使用该数据修改工作簿。使用 Math.abs 函数使单元格 D2 的值为正值。 Math 对象包含许多脚本具有访问权限的函数。可在使用 Office 脚本中的内置 JavaScript 对象中找到有关 Math 和其他内置对象的详细信息。

使用 getValue 和 setValue 方法更改单元格的值。这些方法适用于单个单元格。处理多单元格区域时，需使用 getValues 和 setValues。将以下代码添加到脚本末尾。
```
// 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);
```
注意

我们正使用 as 关键字将 range.getValue() 的返回值转换为 number。这样做很有必要，因为区域可能是字符串、数字或布尔值。在本实例中，我们明确需要数字。
单元格 D2 的值现在应为正值。

修改列的值

了解如何读取和写入单个单元格后，可以通用化脚本以处理整个借方列和信用列。

删除仅影响单个单元格的代码（先前的绝对值代码），以便你的脚本现在如下所示：

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();
}

在脚本末尾添加循环访问最后两列中的行的循环。对于每个单元格，脚本将值设置为当前值的绝对值。

请注意，定义单元格位置的数组是从零开始的。这意味着单元格 A1 为 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);
    }
}

此部分的脚本执行几项重要任务。首先，获取已用区域的值和行计数。这允许脚本查看值并知道何时停止。其次，循环访问已用区域，检查“借记”或“信贷”列中的每个单元格。最后，如果单元格中的值不为 0，则该值将替换为其绝对值。该脚本忽略零，因此可以原样保留空白单元格。

运行脚本。

银行对帐单现在应该已正确设置正数格式。

后续步骤

打开代码编辑器，然后尝试使用一些 Excel 中的 Office 脚本的示例脚本。还可以访问 Excel 中的 Office 脚本基础知识，了解有关创建 Office 脚本的详细信息。

下一系列的 Office 脚本教程重点介绍如何将 Office 脚本与 Power Automate 一起使用。若要详细了解将两个平台组合在一起的优势，请参阅运行 Office 脚本和 Power Automate 或尝试教程：从 Power Automate 流更新电子表格，以创建使用 Office 脚本的 Power Automate 流。

Share via