如何根据订单中包含两种特定产品组合的订单计算总列的平均值

如何根据订单中包含两种特定产品组合的订单计算总列的平均值

我正在尝试添加一个公式,该公式将根据包含特定产品组合的订单计算总列的平均值。我尝试了几种不同的公式,但都没有成功。我希望它是动态的,并基于单元格 J2:K6 中的产品描述。理想情况下,对于包含产品组合的每个订单,我希望对总列(H 列)求和或平均值。

更新示例 电子表格示例

答案1

假设标题从 A1 开始,最后的总数在 H20,因此黄色单元格中突出显示的内容位于 B 列和 F 列,然后使用:

=AVERAGE(  FILTER(H2:H20,  (B2:B20<>0) * (F2:F20<>0)  )  )

FILTER将收集符合条件的行。这些条件在后半部分,其中一个条件是非零值的单元格符合条件,因为这些单元格似乎是篮子中有产品的单元格,其中 B 列和 F 列被选中。每个测试都会产生一个 TRUE/FALSE 结果数组,由于两个数组相乘,导致 Excel 强制转换为 1/0。因此,当两个测试都为 TRUE 时,您将得到 1*1=1,对于其他三种可能性,您将得到零乘以某个数(1 或 0),因此等于 0。

然后,`FILTER 会保留结果为 1 的所有行。诀窍在于,您只是要求它返回总计列 (H 列) 中每个成功行的单元格。

然后,利用所有(且只有所有)符合条件的总数,AVERAGE即可获得您想要的最终结果。

请注意,您需要将每个测试括在各自的括号中。如果没有它们,Excel 肯定会误解算术应该如何进行。

可以设置更复杂的版本,将乘法设置为 AND,将加法设置为 OR。您只需将各个测试放在各自的括号内,并使用括号将结果组放在一起即可。

相关内容