Excel中如何计算满足Dataset1+Dataset2/2a规则的研究完成百分比？

需求说明 #

需要统计参与者的研究完成百分比，规则为：每个阶段需同时满足两个条件才算完成：

1. 该阶段的Dataset1已完成（单元格值为1代表完成，0代表未完成）
2. 该阶段的Dataset2已完成，或同期的Dataset2a已完成（任意一个完成即可）

例如：4个月节点的Dataset2未完成（值为0），但同期Dataset2a已完成（值为1），则该阶段仍视为完成。

解决方案 #

假设数据布局如下（可根据实际列范围调整）：

• Dataset1的各阶段完成状态：B2:E2（共4个阶段）
• Dataset2的对应阶段完成状态：G2:J2
• Dataset2a的对应阶段完成状态：K2:N2

方案1：兼容所有Excel版本（SUMPRODUCT） #

使用SUMPRODUCT实现逐单元格的条件判断与统计：

=SUMPRODUCT(--(B2:E2=1), --((G2:J2=1)+(K2:N2=1)>0)) / COUNTA(B2:E2)

设置单元格格式为百分比即可显示结果。

公式解释

• --(B2:E2=1)：将Dataset1的完成状态转换为1（完成）或0（未完成）的数组
• --((G2:J2=1)+(K2:N2=1)>0)：对每个阶段，判断Dataset2或Dataset2a是否至少一个完成，满足则返回1，否则返回0
• SUMPRODUCT将两个数组对应元素相乘后求和，得到完成的阶段总数
• COUNTA(B2:E2)自动统计总阶段数，避免手动输入固定数值

方案2：适用于Excel 365/2021及以上（FILTER+COUNTA） #

利用动态数组函数简化逻辑：

=COUNTA(FILTER(B2:E2, (B2:E2=1)*((G2:J2=1)+(K2:N2=1)>0))) / COUNTA(B2:E2)

公式解释

• FILTER筛选出满足条件的阶段（Dataset1完成且Dataset2/Dataset2a至少一个完成）
• COUNTA统计筛选后的阶段数量，再除以总阶段数得到完成百分比

原公式无效原因 #

你之前尝试的=SUM(B2:F2) + SUMIF(G2:J2>0, K2:N2)存在两个问题：

1. SUMIF语法错误，正确格式为SUMIF(条件范围, 条件, 求和范围)，无法直接用G2:J2>0作为条件
2. SUMIF无法实现逐单元格对应判断，必须使用数组型函数（如SUMPRODUCT）来处理多列的对应逻辑

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