Excel 2019无辅助单元格计算两组非相邻区域平均均值

问题场景 #

给定两组非相邻的Excel区域，部分单元格存在缺失值（如示例中的NA），需要先计算每行两组对应单元格的均值，再求这些行均值的总平均，要求用单个公式实现，无需辅助单元格。

示例数据 #

假设：

• 第一组区域为A1:A4：1, 2, 3, 2
• 第二组区域为C1:C4：2, 2, 0, NA
• 每行均值：1.5, 2, 1.5, 2
• 最终总均值：1.75

单个公式实现 #

=SUMPRODUCT(IFERROR((A1:A4 + C1:C4)/(2-(ISNA(A1:A4)+ISNA(C1:C4))),0))/SUMPRODUCT(--(NOT(ISNA(A1:A4)*ISNA(C1:C4))))

公式解释 #

1. 分子部分（行均值总和）：
   • (A1:A4 + C1:C4)：对应行两组值相加
   • 2-(ISNA(A1:A4)+ISNA(C1:C4))：计算当前行有效数值的个数（两行都有值则为2，仅一行有值则为1）
   • 整体逻辑：两行都有值时取平均，仅一行有值时直接取该有效值，两行都缺失时返回0（后续会被分母排除）
2. 分母部分（有效行数）：
   • NOT(ISNA(A1:A4)*ISNA(C1:C4))：判断该行是否至少存在一个有效数值
   • --将逻辑判断结果转为数值（1代表有效行，0代表无效行），最终统计所有有效行数

使用注意 #

在Excel 2019中输入公式后，需按下Ctrl+Shift+Enter组合键完成数组公式的输入（Office 365/2021支持动态数组，无需此操作）。

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