为什么 AND() 条件/函数不适用于 ArrayFomula [英] Why does the AND() condition/function doesn't work with ArrayFomula

问题描述

AND 函数:如果我删除 Arrayformula() 它工作正常但它不适用于数组公式.
我能知道原因吗?

=ARRAYFORMULA(IF(ISNUMBER(G3:G),IF(AND(G3:G>=7.5,G3:G<=8),全天"，IF(AND(G3:G>);8，G3:G＜24)，全天+"，IF(AND(G3＜7.5，G3＞＝4)，半天"，IF(G3＜4，短假"，)))),))

解决方案

正如我的 评论

<块引用>

既然你使用了 ARRAYFORMULA 函数，你应该使用 * 而不是 AND 函数

=ARRAYFORMULA(IF(ISNUMBER(A3:A),IF((A3:A>=7.5)*(A3:A<=8),全天"),IF((A3:A>;8)*(A3:A<24),全天+",IF((A3<7.5)*(A3>=4),半天",IF(A3<4,"Short离开",)))),))

当使用 ARRAYFORMULA 函数时，你应该使用 * 而不是 AND 函数和 + 而不是OR 函数.

---

说明

我现在不记得有关于此的官方网站.无论如何.

第 1 点
在数组公式中，AND 给出单个值.不是 TRUE/FALSE 的数组.

第 2 点
您还必须记住，在数学语言"中，TRUE=1 和 FALSE=0

含义

+----------+--------+|公式 |结果 |+-----------+--------+|=真+2 |3 ||=假+2 |2 |+-----------+--------+

如您所见，布尔值TRUE/FALSE 和1/0 数字之间可以互换.

第 3 点
关于AND 功能

<块引用>

如果所有提供的参数在逻辑上为真，AND 函数返回真，如果提供的任何参数在逻辑上为假，则返回假.

综合起来
在数组公式中，我们在进行比较时不使用 AND/OR，而是利用上述信息.

因此，乘法" (A3:A>=7.5)*(A3:A<=8) 将返回 1(意味着 TRUE) 仅当双方都返回 TRUE 时.所有其他条件返回 0(意味着 FALSE).
这是 AND 函数的确切行为，并且在 ARRAYFORMULA 中确实有效.

关于OR 功能

<块引用>

如果提供的任何参数在逻辑上为真，OR 函数返回真，如果所有提供的参数在逻辑上都为假，则返回假.

当使用 + ("addition") 而不是 OR 函数时，在数组公式中应用了相同的逻辑.

AND function: If I remove the Arrayformula() it works fine but It doesn't work with Array Formula.
Can I know the reason for it?

=ARRAYFORMULA(IF(ISNUMBER(G3:G),IF(AND( G3:G>=7.5,G3:G<=8),"Full Day",IF(AND(G3:G>8,G3:G<24) ,"Full Day+",IF(AND(G3<7.5,G3>=4),"Half Day",IF(G3<4,"Short Leave",)))),))

解决方案

As mentioned in my comment

Since you use the ARRAYFORMULA function you should use * instead of the AND function

=ARRAYFORMULA(IF(ISNUMBER(A3:A),IF((A3:A>=7.5)*(A3:A<=8),"Full Day",IF((A3:A>8)*(A3:A<24) ,"Full Day+",IF((A3<7.5)*(A3>=4),"Half Day",IF(A3<4,"Short Leave",)))),))

When using the ARRAYFORMULA function you should use * instead of the AND function and + instead of the OR function.

---

Explanation

I don't recall an official site about this right now. In any case.

Point 1
Within an arrayformula, AND gives a single value. Not an array of TRUE/FALSE.

Point 2
You must also remember that in "math language", TRUE=1 and FALSE=0

Meaning

+----------+--------+
| Formula  | Result |
+----------+--------+
| =TRUE+2  |      3 |
| =FALSE+2 |      2 |
+----------+--------+

As you can see one can interchange between boolean TRUE/FALSE and 1/0 numbers.

Point 3
About the AND function

The AND function returns true if all the provided arguments are logically true and false if any of the provided arguments are logically false.

Putting it all together
In an arrayformula, instead of using AND/OR when making comparisons, we take advantage of the above information.

So, the "multiplication" (A3:A>=7.5)*(A3:A<=8) will return 1 (meaning TRUE) only if both sides return TRUE. All other conditions return 0 (meaning FALSE).
This is the exact behaviour of the AND function and does work in an ARRAYFORMULA.

About the OR function

The OR function returns true if any of the provided arguments are logically true and false if all of the provided arguments are logically false.

The same logic is applied within the arrayformula, when using + ("addition") instead of the OR function.

这篇关于为什么 AND() 条件/函数不适用于 ArrayFomula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！