Google Sheets如何检测缺失数据并自动生成补全行？

公式实现方案 #

不需要宏，用Google Sheets的数组公式就能直接生成缺失条目。假设你的原始数据在A2:C区域（A列姓名、B列月份、C列数据），且要求的月份固定为1、2、3（可根据实际需求修改），可以在空白单元格（比如E2）输入以下公式：

=LET(
  唯一姓名, UNIQUE(A2:A),
  要求月份, {1,2,3},
  全量组合, FLATTEN(唯一姓名&"|"&TOROW(要求月份)),
  已存在组合, A2:A&"|"&B2:B,
  缺失组合, FILTER(全量组合, ISNA(MATCH(全量组合, 已存在组合, 0))),
  拆分结果, SPLIT(缺失组合, "|"),
  HSTACK(INDEX(拆分结果,,1), INDEX(拆分结果,,2), "")
)

公式逻辑说明 #

• UNIQUE(A2:A)：提取所有不重复的姓名
• {1,2,3}：定义每个姓名必须覆盖的月份列表
• FLATTEN(...)：生成姓名与月份的全量笛卡尔积组合（用|分隔方便后续对比）
• FILTER(...)：筛选出原始数据中不存在的组合
• SPLIT和HSTACK：拆分组合字符串，生成A列姓名、B列缺失月份、C列为空的结果

宏脚本实现方案 #

如果需要自动将缺失条目直接插入到原始数据中（而非单独生成在其他区域），可以用Google Apps Script编写宏：

1. 打开你的Google表格，点击菜单栏「扩展程序」→「Apps脚本」
2. 删除默认代码，粘贴以下脚本：

function 补全缺失月份条目() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const nameMonthMap = new Map();
  
  // 记录已存在的姓名-月份配对
  for (let i = 1; i < data.length; i++) {
    const name = data[i][0];
    const month = data[i][1];
    if (!nameMonthMap.has(name)) nameMonthMap.set(name, new Set());
    nameMonthMap.get(name).add(month);
  }
  
  // 修改这里为你实际需要的月份列表
  const requiredMonths = [1, 2, 3];
  const missingRows = [];
  
  // 遍历找出所有缺失条目
  nameMonthMap.forEach((existingMonths, name) => {
    requiredMonths.forEach(month => {
      if (!existingMonths.has(month)) {
        missingRows.push([name, month, ""]);
      }
    });
  });
  
  // 将缺失条目添加到表格末尾
  if (missingRows.length > 0) {
    sheet.getRange(data.length + 1, 1, missingRows.length, 3).setValues(missingRows);
  }
}

3. 点击「保存」，给脚本命名（比如「补全缺失条目」）
4. 点击运行按钮，首次运行需要授权脚本访问你的表格数据
5. 授权完成后，再次运行即可自动在表格末尾生成所有缺失的条目

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