Excel - 如果 6 个单元格中有任意一个大于 0,则除以该单元格数

Excel - 如果 6 个单元格中有任意一个大于 0,则除以该单元格数

电子表格的屏幕截图

我正在尝试做以下事情:

一次“单元格 B19:B20”“第 21 排”有一个值,'第 22 行'除以“单元格 B19:B20”具有成本值的列数量之间(“第 21 排”)。

例如:

要是D21有一个价值D22=B19:B20

如果D21F21那么就有价值D22=(B19:B20/2)

如果D21F21H21那么就有价值D22=(B19:B20/3)

基本上,计算时不管列的顺序如何,只考虑有值的列数。

这些是我目前掌握的公式,但它们只能计算列是否按顺序排列(A、B、C、D),而不是随机排列(A、C、H):

=如果((D21>0)*AND(E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0)*AND(I21>0),(B19/6),如果((D21>0)*AND(E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0),(B19/5),如果((D21>0)*AND(E21>0)*AND(F21>0)*AND(G21>0),(B19/4),如果((D21>0)*AND(E21>0)*AND(F21>0),(B19/3),如果((D21>0)*AND(E21>0),(B19/2),如果((D21>0),B19,0))))))

=IF((E21=0),0,IF((D22>0),D22,IF((E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0)*AND(I21>0),(B19/5),IF((E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0),(B19/4),IF((E21>0)*AND(F21>0)*AND(G21>0),(B19/3),IF((E21>0)*AND(F21>0),(B19/2),IF((E21>0),B19,0)))))))

=IF((F21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F21>0)*AND(G21>0)*AND(H21>0)*AND(I21>0),(B19/4),IF((F21>0)*AND(G21>0)*AND(H21>0),(B19/3),IF((F21>0)*AND(G21>0),(B19/2),IF((F21>0),B19,0)))))))

=IF((G21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F22>0),F22,IF((G21>0)*AND(H21>0)*AND(I21>0),(B19/3),IF((G21>0)*AND(H21>0),(B19/2),IF((G21>0),B19,0)))))))

=IF((H21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F22>0),F22,IF((G22>0),G22,IF((H21>0)*AND(I21>0),(B19/2),IF((H21>0),B19,0)))))))

=IF((I21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F22>0),F22,IF((G22>0),G22,IF((H22>0),H22,IF((I21>0),B19,0)))))))

答案1

此数组(CSE)公式可以解决这个问题:

在此处输入图片描述

怎么运行的:

  • A103单元格&中的两个值B103除以单元格数,(A101:E101)具有除以外的值ZERO
  • 单元格中的数组公式C103:

    {=IF(COUNTIF(A101:E101,">0")=0,0,(A103:B103)/COUNTIF(A101:E101,">0"))}
    

注意:

  • 完成配方Ctrl+年代切换+沒有。

  • COUNTIF单元格中的公式F101,证明C數。

  • 稍后您可以隐藏单元格F101

根据需要调整公式中的单元格引用。

答案2

您使用 AND() 的方式很奇怪。使用 AND() 括号内的逗号分隔要与 AND() 结合的所有条件。您使用的是*,它已经完成了 AND() 的功能,因此您实际上是在重复这个概念。

正确应用 AND() 后,您的公式可以像这样开始:

=IF(AND(D21>0,E21>0,F21>0,G21>0,H21>0,I21>0),B19/6),
    IF(AND(D21>0,E21>0,F21>0,G21>0,H21>0),(B19/5), [...]

等等

不过,实现相同结果的一种更简单的方法是查看单元格的总和。

=IF(Sum(D21:I21)>0,B19/6,
   IF(Sum(D21:H21)>0,B19/5,
      IF(sum(D21:G21)>0,B19/4,
        IF(sum(D21:F21)>0,B19/3,
          IF(sum(D21:E21)>0,B19/2,
             IF(D21>0,B19,0)
          )
         )
       )
    )
  )

严格来说,您甚至不需要>0在条件中使用,因为 a 的0计算结果为 False,而任何大于零的数字的计算结果为 True。

=IF(Sum(D21:I21),B19/6,
   IF(Sum(D21:H21),B19/5,
      IF(sum(D21:G21),B19/4,
         IF(sum(D21:F21),B19/3,
            IF(sum(D21:E21),B19/2,
               IF(D21,B19,0))))))

不要用括号。有时,在不需要的地方多加一对括号会有帮助,但不要到处都用它们。

相关内容