本文档旨在指导您使用 Google Sheets 脚本来比较两个工作表中支票号码和金额，以识别不一致的数据。我们将创建一个脚本，该脚本能够检测无效的支票号码、无效的支票金额以及支票号码匹配但金额不匹配的情况，并将结果记录到控制台中。通过学习本教程，您将能够有效地验证和清理财务数据，确保数据的准确性和一致性。
脚本实现
以下是一个 Google Apps Script 函数，用于比较两个工作表中的支票号码和金额，并识别不一致之处：
function CHK_BothChecks_V3() {
const ss = SpreadsheetApp.getActive();
const sh1 = ss.getSheetByName("OpenCheckDataBase");
const sh2 = ss.getSheetByName("IncomingChecks");
var rg1 = sh1.getRange("C1:D" + sh1.getLastRow()).getValues();
var rg2 = sh2.getRange("C1:D" + sh2.getLastRow()).getValues();
var correctChecks = 0;
var invalidChecks = 0;
var invalidAmounts = 0;
// 创建一个 Map 来存储 OpenCheckDataBase 中的支票号码和金额
const openCheckData = new Map();
for (let i = 0; i < rg1.length; i++) {
const checkNumber = rg1[i][0];
const checkAmount = rg1[i][1];
openCheckData.set(checkNumber, checkAmount);
}
for (var i = 0; i < rg2.length; i++) {
const checkNumber = rg2[i][0];
const checkAmount = rg2[i][1];
if (openCheckData.has(checkNumber)) {
// 支票号码存在于 OpenCheckDataBase 中
if (openCheckData.get(checkNumber) === checkAmount) {
correctChecks++;
} else {
invalidAmounts++;
console.log(`Row ${i + 1} in IncomingChecks has an inconsistent check amount.`);
}
} else {
// 支票号码不存在于 OpenCheckDataBase 中
invalidChecks++;
console.log(`Row ${i + 1} in IncomingChecks has an inconsistent check number.`);
}
}
console.log("Total number of valid check numbers is " + correctChecks);
console.log("Total number of invalid check numbers is " + invalidChecks);
console.log("Total number of invalid amounts is " + invalidAmounts);
}代码解释
-
获取工作表和数据范围：
- SpreadsheetApp.getActive() 获取当前活跃的电子表格。
- getSheetByName() 获取名为 "OpenCheckDataBase" 和 "IncomingChecks" 的两个工作表。
- getRange("C1:D" + sh1.getLastRow()).getValues() 获取两个工作表 C 列和 D 列的数据，其中 getLastRow() 用于动态获取最后一行的行号。
-
初始化计数器：
- correctChecks：记录支票号码和金额都匹配的支票数量。
- invalidChecks：记录支票号码不匹配的支票数量。
- invalidAmounts：记录支票号码匹配但金额不匹配的支票数量。
-
使用 Map 存储 OpenCheckDataBase 数据：
- 创建一个 Map 对象 openCheckData，用于存储 "OpenCheckDataBase" 工作表中的支票号码和金额。这可以提高查找效率。
- 遍历 "OpenCheckDataBase" 工作表的数据，将支票号码作为键，金额作为值存储到 openCheckData 中。
-
循环遍历 "IncomingChecks" 工作表的数据：
- 使用 for 循环遍历 "IncomingChecks" 工作表的数据。
- 对于每一行数据，获取支票号码和金额。
- 使用 openCheckData.has(checkNumber) 检查支票号码是否存在于 openCheckData 中。
- 如果存在，则比较金额是否匹配。如果匹配，则 correctChecks 加 1；如果不匹配，则 invalidAmounts 加 1，并在控制台中输出错误信息。
- 如果不存在，则 invalidChecks 加 1，并在控制台中输出错误信息。
-
输出统计结果：
- 在控制台中输出 correctChecks、invalidChecks 和 invalidAmounts 的值，用于统计分析。
使用方法
- 打开你的 Google Sheets 电子表格。
- 点击 "工具" -> "脚本编辑器"。
- 复制上面的代码并粘贴到脚本编辑器中。
- 修改 sh1 和 sh2 的工作表名称，确保它们与你的实际工作表名称匹配。
- 保存脚本。
- 运行 CHK_BothChecks_V3 函数。 首次运行可能需要授权。
- 查看控制台输出，了解不一致的支票数据。 可以通过 "查看" -> "日志" 打开控制台。
注意事项
- 确保 "OpenCheckDataBase" 和 "IncomingChecks" 工作表存在，并且包含支票号码和金额数据，分别位于 C 列和 D 列。
- 此脚本假设数据从第一行开始，并且没有标题行。如果你的数据包含标题行，请修改 getRange() 函数的起始行号。
- 此脚本区分大小写。如果支票号码或金额的大小写不一致，可能会导致错误的比较结果。
- 在处理大量数据时，脚本的运行时间可能会较长。可以考虑使用批量更新或优化算法来提高性能。
总结
本教程提供了一个使用 Google Sheets 脚本比较多个列以检测不一致支票数据的完整解决方案。通过使用 Map 对象来提高查找效率，该脚本能够有效地识别无效的支票号码和金额，并将结果记录到控制台中。通过学习本教程，您可以轻松地验证和清理财务数据，确保数据的准确性和一致性。 您可以根据实际需求修改脚本，例如，将错误信息写入到工作表中，或者添加更多的验证规则。
