Google Sheets合规条件单元格定位与数据质量检测报告生成需求

需求明确 #

需要检测两类异常数据：

• 数值型定位值大于1000的单元格
• 内容为Don't know的单元格（首个出现于E3）

实现方法 #

方法一：公式手动生成报告 #

1. 标记异常单元格

在空白辅助列（如H列）首行输入公式，下拉覆盖所有数据行：

=IF(OR(ISNUMBER(A2)*A2>1000, A2="Don't know"), "异常", "")

注：将公式中的A2替换为你数据区域的首行单元格

2. 汇总异常统计

新建「数据质量报告」工作表，用以下公式统计：

• 统计Don't know出现次数：

=COUNTIF(整个数据区域, "Don't know")

• 统计定位值>1000的次数：

=COUNTIF(整个数据区域, ">1000")

• 提取所有异常行详情：

=FILTER(数据区域, OR(ISNUMBER(数据区域)*数据区域>1000, 数据区域="Don't know"))

方法二：脚本自动生成完整报告 #

如果需要自动化输出带单元格地址的报告，使用Google Apps Script：

function generateDataQualityReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("原始数据"); // 替换为你的数据工作表名称
  let reportSheet = ss.getSheetByName("数据质量报告");
  if (!reportSheet) reportSheet = ss.insertSheet("数据质量报告");
  
  const dataRange = dataSheet.getDataRange();
  const values = dataRange.getValues();
  const exceptionList = [];
  
  // 遍历所有单元格检测异常
  for (let rowIdx = 0; rowIdx < values.length; rowIdx++) {
    for (let colIdx = 0; colIdx < values[rowIdx].length; colIdx++) {
      const cellVal = values[rowIdx][colIdx];
      const isOver1000 = typeof cellVal === 'number' && cellVal > 1000;
      const isDontKnow = cellVal === "Don't know";
      
      if (isOver1000 || isDontKnow) {
        const cellAddr = dataSheet.getRange(rowIdx+1, colIdx+1).getA1Notation();
        exceptionList.push([cellAddr, cellVal]);
      }
    }
  }
  
  // 清空旧报告并写入新内容
  reportSheet.clear();
  reportSheet.getRange(1,1,1,3).setValues([["数据质量检测报告", "", `总异常数：${exceptionList.length}`]]);
  reportSheet.getRange(2,1,1,2).setValues([["异常单元格地址", "异常内容"]]);
  reportSheet.getRange(3,1, exceptionList.length, 2).setValues(exceptionList);
  
  // 添加分类统计
  const dontKnowCount = exceptionList.filter(item => item[1] === "Don't know").length;
  reportSheet.getRange(2,3).setValue(`"Don't know"数量：${dontKnowCount}`);
  reportSheet.getRange(3,3).setValue(`定位值>1000数量：${exceptionList.length - dontKnowCount}`);
}

使用方法：打开表格的「扩展程序」→「Apps脚本」，粘贴代码并运行，授权后即可自动生成报告。

内容的提问来源于stack exchange，提问作者user16239103