使用OR的Google Spreadsheet多栏过滤器 [英] Google Spreadsheet multiple column filter using OR
问题描述
我有一个带有3列的Google Spreadsheet,它们是空白的或有价值的。我想获得具有A和B或C填充的行数。如果我正在编写SQL查询,它将是
select count(*)
from Table
where A不为空,并且(B不为空或C不为空)
但是我不能为了我的生活,弄清楚如何在Google Spreadsheet中得到这个结果。
下面的公式应该做你正在做的事情: = ROWS(FILTER(A2:A,NOT(ISBLANK(A2:A)),NOT(ISBLANK(B2:B ))+ NOT(ISBLANK(C2:C))))
并解释:
结合在一起。最后,如果您没有使用标题列,您可以将引用从 A2:A
更改为 A:A
或者,您可以使用 QUERY
功能:
(为了便于阅读,分成多行)
= ROWS(QUERY(A2:C,
SELECT A WHERE A NOT NOT NULL AND(B不是NULL或C不是NULL)))
有关查询语法的更多信息请参阅可视化API查询语言参考资料,特别是语言参考
I have a Google Spreadsheet with 3 columns which are either blank or have a value. I want to get the count of the number of rows that has A and either B or C populated. If I were writing a SQL query it would be
select count(*)
from Table
where A is not null and (B is not null or C is not null)
But I can't for the life of me figure out how to get this in a Google Spreadsheet
The formula below should do what you are after:
=ROWS(FILTER(A2:A, NOT(ISBLANK(A2:A)), NOT(ISBLANK(B2:B))+NOT(ISBLANK(C2:C)) ))
And to explain:
ROWS
counts the rows of the argument (filtered, in our case)FILTER
returns the rows of arg1 (A2:A
) that all subsequent arguments match- The
+
(addition) symbol combines two predicates with a logicalOR
Finally, if you are not using header columns you can change the references from A2:A
to A:A
Alternatively, you can use the QUERY
function:
(Broken into multiple lines for readability)
=ROWS(QUERY(A2:C,
"SELECT A WHERE A IS NOT NULL AND (B IS NOT NULL OR C IS NOT NULL)"))
For more information on the syntax of the queries, see the Visualization API Query Language Reference and specifically the Language Reference
这篇关于使用OR的Google Spreadsheet多栏过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!