ArrayFormula和“AND” Google表格中的公式 [英] ArrayFormula and "AND" Formula in Google Sheets
问题描述
在Google表格中,当使用AND公式的ArrayFormula时,我得不到应有的结果。
A | B
2 | 7
在 C1
我把公式写成: =和(A1> 5,B1> 6)
然后我得到 True 。如果在D1中,我把公式写为:
= ArrayFormula(和(A1:A> 5,B1:B> 6))
我得到的结果是假
。
以下是我的两个问题:
- 为什么
ArrayFormula
对于列中的所有单元格都不重复? - 为什么在没有<$的情况下获得
true
c $ c> ArrayFormula 和False
与Arrayformula
?
AND
因为它 AND
整个数组放在不论维数的左上角的单元格中。
Ie它会检查>
是否是 FALSE
, AND $ c
$ b $您可以使用真值的乘法来创建与 ARRAYFORMULA
一起工作的 AND
ing,如下所示:
$ b $
= ArrayFormula((A1:A> 1)*(B1:B> 6)= 1)
OR
相当于显然是
< pre $ = ArrayFormula((A1:A> 1)+(B1:B> 6)> 0)
In Google Sheets, when using ArrayFormula with AND formula, I don't get the results as it should be.
A|B
2|7
In C1
I put formula as: =and(A1>5,B1>6)
then I get True
. If in D1 I put formula as: =ArrayFormula(and(A1:A>5,B1:B>6))
I get the results as False
.
Here are my two questions:
- Why is
ArrayFormula
not repeated for all cells in the column? - Why do I get
true
withoutArrayFormula
andFalse
withArrayformula
?
AND
doesn't work with that way with Array formulae because it AND
s the whole array together in the top left cell of the regardless of number of dimensions.
I.e. it checks if "">""
which is FALSE
, AND
ed with anything it will return FALSE
for the top left cell, that result is carried down.
You can use multiplication of truth values to create AND
ing that works with ARRAYFORMULA
like this:
=ArrayFormula((A1:A>1)*(B1:B>6) = 1)
The OR
equivalent would obviously be
=ArrayFormula((A1:A>1)+(B1:B>6) > 0)
这篇关于ArrayFormula和“AND” Google表格中的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!