You need to enable JavaScript to run this app. Google Sheets基于跨表列表设置单元格格式:表单自动更新公式咨询
优惠活动
大模型
产品
解决方案
定价
更多

Google Sheets基于跨表列表设置单元格格式:表单自动更新公式咨询

培训汇总表自动更新配置方案

前置结构校准

在配置公式前,先确认两个工作表的实际列位(所有公式的列号需要和实际位置对应调整):

  • 响应源表Form Responses 1:逐行存储表单提交记录,其中固定包含「提交人姓名」列、「提交的培训课程编号」列,其余列不影响匹配逻辑
  • 培训汇总表:首列纵向存储所有员工姓名,首行横向存储所有培训课程编号,行列交叉的单元格为需要标记完成状态的位置
  • 特殊规则前置:课程编号为SP134X、SP135X的两个表头所在列,完全保留手动录入状态,不配置自动更新公式,从根源上避免公式覆盖手动输入内容

通用自动更新公式

针对除上述两个特殊课程外的所有交叉单元格,统一配置多条件匹配公式,示例(以汇总表第2行、B列交叉单元格为例):
=IF(COUNTIFS('Form Responses 1'!$B:$B, $A2, 'Form Responses 1'!$C:$C, B$1)>0, "Yes", "")

公式逻辑拆解

  • 核心匹配逻辑:通过COUNTIFS同时校验两个匹配条件,只要源响应表中存在1条及以上记录,同时满足「提交人姓名与当前行的员工姓名完全一致」「提交的课程编号与当前列的课程编号完全一致」,就判定员工已完成该培训
  • 引用锁定设计:源表的整列范围、当前行的员工姓名位置、当前列的课程编号位置都通过$符号做绝对引用锁定,公式横向、纵向拖拽填充到所有非特殊课程列的单元格时,引用位置不会错位
  • 无匹配返回空值:不存在匹配记录时单元格保持空白,不会填充多余提示内容

常见适配调整

  • 列位错位调整:如果源响应表里的姓名存储在D列、课程编号存储在E列,只需要把公式里对应的$B:$B、$C:$C替换成实际的列位置即可
  • 匹配精度优化:如果出现因为姓名、课程编号前后带空格导致的匹配失败,可以给匹配条件套上TRIM函数去除首尾空格,调整后的公式示例:
    =IF(COUNTIFS(TRIM('Form Responses 1'!$B:$B), TRIM($A2), TRIM('Form Responses 1'!$C:$C), TRIM(B$1))>0, "Yes", "")
  • 自动更新机制说明:Google Sheets的表格函数会随源表数据变更自动重算,只要Form Responses 1有新的表单提交写入,汇总表对应的交叉单元格就会自动从空白更新为"Yes",不需要手动触发刷新。

内容的提问来源于stack exchange,提问作者John Spartan

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.17 15:36:00