excel - 使用嵌套的 IF 和 AND 数组公式确定中值

Question

我正在尝试创建两个相似的数组公式:

一种是求B列的单元格的中值，但前提是J列既有对应的空白值，D列也有数值。(D和J分表.)

另一个公式做同样的事情，除了它只会确定 B 中的单元格的中位数，而 J 列中的相应单元格不为空。

我应该注意，我确保使用 command-shift-enter 输入它们，因为它们是数组公式。然而，对他们两个来说，结果都为零。我已经尝试了很长时间，但无法弄清楚为什么。我是 Excel 公式的新手，所以如果我应该澄清我的问题，请告诉我。

第一个公式:

=MEDIAN(IF(

 AND('Raw Data'!$J$3:'Raw Data'!$J$999="", ISNUMBER('Raw Data'!$D$3:'Raw Data'!$D$999)),

 B$6:B$1002))

第二个公式:

=MEDIAN(IF(

 AND(NOT(ISBLANK('Raw Data'!$J$3:'Raw Data'!$J$999)), ISNUMBER('Raw Data'!$D$3:'Raw Data'!$D$999)),

 B$6:B$1002))

Answer 1

AND 的数组等价于乘法 *:

=MEDIAN(IF(('Raw Data'!$J$3:$J$999="")*
            ISNUMBER('Raw Data'!$D$3:$D$999),
            B$6:B$1002)
       )

用数组输入(CTRL+SHIFT+ENTER)。

对于第二个公式:

=MEDIAN(IF(NOT(ISBLANK('Raw Data'!$J$3:$J$999))*
           ISNUMBER('Raw Data'!$D$3:$D$999),
           B$6:B$1002)
       )

也有数组条目。

---

解释为什么 AND 不起作用。

您期望的是 AND({TRUE,FALSE,FALSE},{TRUE,TRUE,FALSE}) 返回 {TRUE,FALSE,FALSE}。

但是，AND 获取 bool 值的数组 并返回单个 bool 值 - 它指示所有 值是 TRUE 或 FALSE。

因此，AND({TRUE,FALSE,FALSE},{TRUE,TRUE,FALSE}) 返回 FALSE 因为并非所有值都是 TRUE.

但是，乘法 {TRUE,FALSE,FALSE}*{TRUE,TRUE,FALSE} 完全按照您的需要工作 - 它返回 {TRUE*TRUE,FALSE*TRUE,FALSE *FALSE} = {TRUE, FALSE, FALSE}