☰

按照条件计算多个不相邻列的平均值

tag-icon • tag-icon microsoft-excel average
按照条件计算多个不相邻列的平均值

我正在尝试建立一份季度报告，其中包含每个医生的平均数字和每个专业的平均值。我想使用平均值来比较医生在这些指标上的表现，并将整个组与同一专业进行比较。我的问题是该组的平均公式是计算零（如果我使用公式 2）我不认为平均值中应该计入零。

我使用公式 1 解决了医生平均值（M、N、O 列）的问题，但无法解决 Q、R、S 列的问题。

数据表

公式1

=SUM(C3,F3,I3)/SUM(IF(C3=0,0,1),IF(F3=0,0,1),IF(I3=0,0,1))

二级方程式

=AVERAGEIF(iferror(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0),iferror(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0),iferror(AVERAGEIFS(I3:24,B3:B24,B3,I3:24,"<>0"),0))

答案1

（编辑：替代解决方案，更直观，在本答案的末尾。）
这应该可以解决问题。将以下内容粘贴到 Q3 中：

=IFERROR(SUM(($B$3:$B$11=$B3)*(C$3:C$11+F$3:F$11+I$3:I$11))/SUM(($B$3:$B$11=$B3)*((C$3:C$11<>0)+(F$3:F$11<>0)+(I$3:I$11<>0))),0)

按Ctrl- Shift-Enter使其成为数组公式。

将粘贴的公式拖/复制到另外两个专科汇总列中，公式对应的评级数据列将更新。在仍然选择所有三个单元格的情况下，将三个选定的单元格拖/复制到最后一行医生。Excel 会将所有专科筛选行号更新为相应的汇总行。

怎么运行的：

  • 首先，针对 Specialty 列数组的相等性测试=$B3会创建一个单列布尔数组。如果当前公式行 Specialty 与 Specialty 列行相匹配，则该数组将包含TRUE在创建的布尔数组的该行中，FALSE否则。
  • 第二个单列数组是通过合并三个单列数组创建的，每个数组对应当前评级的每个月。这是通过将 7 月+8 月+9 月逐行相加来创建的，以创建一个包含给定评级的每个医生三个月总分的单列数组。
  • 然后将这两个数组中相应的行值相乘。
    • 在数学表达式中使用任何FALSE布尔值时，Excel 都会自动将其转换为零。这会将不等于公式行的特长归零。TRUE布尔值将转换为 1，然后乘以相应行的评级总和，保留该行中该特长的 3 个月总和的值。结果是一个数组，其中不属于公式行特长的行的值为零。
  • 然后将结果数组与相加SUM。
  • 用该总和除以类似的总和。
  • 创建相同的布尔过滤数组来过滤掉不等同于公式行专业的专业。
  • 这次，第二个计算数组的每个元素都是该特定行中具有非零值的月份数。这是通过<>0对三个单列数据数组（给定评级的每个月份一个数据数组）中的每个项目执行“非零测试”来实现的。
  • 使用生成的三个单列布尔数组，将每个数组的给定行的项相加以创建计算的第二个数组。由于这些是表达式返回的布尔值，用于测试非零值，因此当要求执行此算术加法时， Excel 再次自动将TRUE值转换为 1，将值转换为 0。FALSE
  • 然后将此单列数组（每行的值范围从 0 到 3）逐行乘以布尔过滤器数组，将数组中与当前公式行不相同的任何行归零（如前所述）。
  • 再次将结果数组相加以获得用于计算平均值的除数。
  • IFERROR将除以零的错误替换为零 。
[编辑] 替代解决方案。一个更直观的公式，不是 CSE：

=IFERROR(( SUMIFS( C$3:C$12, $B$3:$B$12, $B3) + SUMIFS( F$3:F$12, $B$3:$B$12, $B3) + SUMIFS( I$3:I$12, $B$3:$B$12, $B3) ) / ( COUNTIFS( $B$3:$B$12, $B3, C$3:C$12, "<>0", C$3:C$12, "<>") + COUNTIFS( $B$3:$B$12, $B3, F$3:F$12, "<>0", F$3:F$12, "<>") + COUNTIFS( $B$3:$B$12, $B3, I$3:I$12, "<>0", I$3:I$12, "<>")), 0)

答案2

您也可以使用此数组公式：

{=ROUND(SUM(IF($F$25:$F$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$F$25:$F$34),IF($G$25:$G$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$G$25:$G$34),IF($H$25:$H$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$H$25:$H$34),0))))/3,2)}

在此处输入图片描述

  • 由于它是 CSE 公式，因此用Ctrl+Shift+Enter。

編輯：

您可以尝试这个公式，它比上面显示的公式更好、更有效。

在此处输入图片描述

=ROUND(IFERROR(SUMIFS($T$2:$T$11,$S$2:$S$11,$S$1,$T$2:$T$11,">0")+SUMIFS($U$2:$U$11,$S$2:$S$11,$S$1,$U$2:$U$11,">0")+SUMIFS($V$2:$V$11,$S$2:$S$11,$S$1,$V$2:$V$11,">0"),0)/(COUNTIFS($S$2:$S$11,$S$1,$T$2:$T$11,">0")+COUNTIFS($S$2:$S$11,$S$1,$U$2:$U$11,">0")+COUNTIFS($S$2:$S$11,$S$1,$V$2:$V$11,">0")),2)

注意：

  • 数据范围是S2:V11。
  • 单元格中的标准S1。
  • 根据需要调整两个公式中的单元格引用。

相关内容