AND 函数:如果删除Arrayformula()可以正常工作,但不适用于Array Formula.我能知道原因吗?
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",)))),)) 推荐答案正如我的评论
由于使用了ARRAYFORMULA函数,因此应使用*代替AND函数
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",)))),))使用 ARRAYFORMULA 函数时,应使用 * 代替 AND 函数,并使用 + 代替 OR 函数.
When using the ARRAYFORMULA function you should use * instead of the AND function and + instead of the OR function.
我现在不记得有关此的官方网站.无论如何.
I don't recall an official site about this right now. In any case.
第1点 在arrayformula中, AND 给出一个值.不是一个由 TRUE/FALSE 组成的数组.
Point 1 Within an arrayformula, AND gives a single value. Not an array of TRUE/FALSE.
第2点 您还必须记住,使用数学语言", TRUE = 1 和 FALSE = 0
Point 2 You must also remember that in "math language", TRUE=1 and FALSE=0
含义
+----------+--------+ | Formula | Result | +----------+--------+ | =TRUE+2 | 3 | | =FALSE+2 | 2 | +----------+--------+如您所见,布尔型 TRUE/FALSE 和 1/0 数字之间可以互换.
As you can see one can interchange between boolean TRUE/FALSE and 1/0 numbers.
第3点 关于 AND 函数
如果提供的所有参数在逻辑上均为true,则AND函数返回true;如果提供的任何参数在逻辑上均为false,则AND函数返回false.
The AND function returns true if all the provided arguments are logically true and false if any of the provided arguments are logically false.
将它们放在一起 在一个数组公式中,我们在进行比较时不用使用 AND/OR 进行比较.
因此,乘法" (A3:A&=; 7.5)*(A3:A< = 8)将返回1(表示 TRUE),前提是双方均返回 TRUE .所有其他条件都返回0(表示 FALSE ).这是 AND 函数的确切行为,并且在 ARRAYFORMULA 中起作用.
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.
关于> OR 函数
如果提供的所有参数在逻辑上均为true,则 OR 函数将返回true;如果提供的所有参数在逻辑上均为false,则函数将返回false.
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.
使用 + ("addition")而不是 OR 函数时,在数组公式中应用相同的逻辑.
The same logic is applied within the arrayformula, when using + ("addition") instead of the OR function.
更多推荐
为什么AND()条件/函数不能与ArrayFomula一起使用
发布评论