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
相关产品推荐
相关产品推荐

