如何用Google Sheets高效跟踪多人多技能长清单完成进度

一、单张3万行表格完全可行 #

Google Sheets单工作表支持最高100万行数据，3万行远在承载范围内。统一表格的优势非常明确：

• 避免Drive中文件杂乱，不用维护数十上百个独立表格
• 直接在同一张表内做聚合分析（比如数据透视表、统计函数），无需跨文件引用，效率高
• 数据结构统一，便于后续扩展功能（比如添加自动化提醒）

关键前提：必须设计清晰的核心字段，每行对应唯一的「人员-技能-步骤」组合，建议字段包括：

• 人员姓名/ID
• 技能路径名称
• 步骤序号
• 步骤描述
• 完成状态（未完成/待评估/已完成）
• 评估日期
• 评估人（可选）

二、批量预填充技能步骤的两种实现方式 #

1. 无代码方案：用公式批量生成 #

先创建一个技能模板表（同文件内单独工作表），把所有技能的步骤按「技能名称-步骤序号-步骤描述」的格式整理好。

当需要给某人员添加新技能时，用QUERY+ARRAYFORMULA组合批量生成数据：
假设主跟踪表叫进度跟踪，模板表叫技能模板，主表A列是人员姓名，B列是技能名称，要在C-E列自动填充步骤：

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(
      ROW(A:A),
      QUERY(
        {FLATTEN(IF(进度跟踪!B:B="目标技能名称", 进度跟踪!A:A&"|"&进度跟踪!B:B, "")), 技能模板!B:B, 技能模板!C:C},
        "select Col1, Col2, Col3 where Col1 is not null"
      ),
      {2,3},
      FALSE
    )
  )
)

更灵活的方式是结合数据验证下拉框，选择人员和技能后，用FILTER筛选对应步骤并自动填充。

2. 自动化方案：Google Apps Script #

如果需要一键批量添加，写个简单脚本就能实现，步骤如下：

1. 在Google Sheets中打开「扩展程序」→「Apps Script」
2. 粘贴以下代码（根据你的表名和字段调整）：

// 打开表格时添加自定义菜单
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("技能管理")
    .addItem("添加新技能路径", "addSkillPath")
    .addToUi();
}

// 批量添加技能步骤
function addSkillPath() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName("进度跟踪"); // 替换成你的主表名称
  const templateSheet = ss.getSheetByName("技能模板"); // 替换成你的模板表名称
  
  // 弹窗获取人员和技能信息
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt("添加技能路径", "格式：姓名,技能名称", ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() !== ui.Button.OK) return;
  
  const [name, skill] = response.getResponseText().split(",").map(item => item.trim());
  if (!name || !skill) {
    ui.alert("输入格式错误，请按「姓名,技能名称」填写");
    return;
  }
  
  // 从模板表筛选对应技能的步骤
  const templateData = templateSheet.getDataRange().getValues();
  const skillSteps = templateData
    .filter(row => row[0].trim() === skill) // 假设模板A列是技能名称
    .map(row => [name, skill, row[1], row[2], "未完成"]); // 对应主表字段：姓名、技能、步骤序号、步骤描述、完成状态
  
  // 批量插入到主表末尾
  if (skillSteps.length > 0) {
    mainSheet.getRange(mainSheet.getLastRow() + 1, 1, skillSteps.length, skillSteps[0].length).setValues(skillSteps);
    ui.alert(`已成功为${name}添加${skill}的${skillSteps.length}个步骤`);
  } else {
    ui.alert(`未找到「${skill}」对应的技能模板`);
  }
}

3. 保存脚本并授权，回到表格后就能看到顶部的「技能管理」菜单，点击「添加新技能路径」，输入姓名和技能即可批量生成100+行步骤。

三、后续数据管理优化建议 #

• 数据验证：给人员姓名、技能名称列设置下拉列表，避免手动输入错误
• 筛选视图：创建多个自定义筛选视图（比如「待评估清单」「某人员进度」），方便快速查看特定数据
• 归档机制：把已完成所有步骤的「人员-技能」组合移到单独的「已归档」工作表，保持主表简洁
• 聚合分析：用数据透视表快速统计人员技能完成率、各技能整体进度，无需复杂公式

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