Google Sheets含条件子问题的问卷完成率动态计算求助

基础计算规则 #

问卷题量构成如下：

• 固定必答主问题共8道，所有受访者均需作答
• 第4、7、8题各绑定1道条件子问题，触发规则为：对应主问题回答Yes时，子问题纳入该受访者的应答题范围

单条受访者记录的预期应答题数为动态值，区间为8~11，计算逻辑为：
固定必答题数 + 满足触发条件的子问题总数

可直接套用的表格公式 #

假设数据排布规则为：每行对应1位受访者的独立作答记录，第4题主问题答案在D列、第7题主问题答案在G列、第8题主问题答案在H列，在预期应答题数计算列的首行数据单元格（如第2行，对应第一条有效记录）输入以下公式，下拉批量填充即可：

=8 + --(D2="Yes") + --(G2="Yes") + --(H2="Yes")

公式说明：--(判断条件)的作用是把逻辑判断的TRUE/FALSE结果转为1/0数值，满足触发条件就累加1道子题量，运算效率高于COUNTIF写法。

如果单道主问题对应多道触发子问题，直接给对应判断项乘以子问题数量即可，比如第4题选Yes会触发2道子问题，就把--(D2="Yes")修改为--(D2="Yes")*2。

配套完成率计算方法 #

原有实际有效作答数/预期应答题数的完成率计算逻辑成立，只需替换固定预期题数为上述动态计算值即可：

• 计算单条记录实际有效作答数：使用COUNTA函数统计该行所有题目列的非空单元格数量，注意提前排除受访者ID、提交时间、设备信息等非题目字段。例如题目字段分布在C列到N列时，公式为：

  =COUNTA(C2:N2)
  

• 计算单条记录完成率：用实际有效作答数除以动态预期应答题数，将单元格格式设置为百分比即可。

常见误差排查 #

• 选项格式不统一：如果作答数据中Yes存在大小写混写、前后带空格的情况，会导致触发判断失效，建议提前用数据验证统一选项格式，或把判断逻辑改为不区分大小写、自动忽略空格的匹配规则
• 多选题触发场景：如果是多选题（如“您今天状态如何”），只要选中内容包含触发选项即算触发，不要用单元格精确匹配，改用ISNUMBER(SEARCH("触发选项文本", 对应单元格))的判断逻辑替换原有精确匹配即可
• 无效记录干扰：如果存在仅提交ID、未作答任何题目的空记录，提前筛选过滤，避免拉低整体完成率统计结果

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