问题描述
ColA ColB ColC ColD ColE
DATE COUNTRY 1 COUNTRY 2 COUNTRY 3 COUNTRY 4
01/xx/2017 INDONESIA GERMANY PHILIPPINES PAKISTAN
01/xx/2017 MOROCCO MOROCCO MOROCCO ITALY
23/xx/2017 USA UK NETHERLANDS MOROCCO
23/xx/2017 MOROCCO TANZANIA AUSTRALIA SWEDEN
在 ColB中,我可以使用什么公式来计算国家/地区的发生率(比如
,但仅在同一行中出现多个事件时才算一次吗?例如,在这种情况下,结果应为摩洛哥
): ColE 3
。
推荐答案
尝试此:
=ArrayFormula(COUNTIF(MMULT(--(B2:E5="MOROCCO"),TRANSPOSE(COLUMN(B2:E5)^0)),">0"))
BTW任何逻辑表达式都可以是使用而不是-(B2:E5 = MOROCCO)
BTW any logical expression may be used instead of --(B2:E5="MOROCCO")
第一个步骤是具有 true
和 false
数组:ArrayFormula(B2:E5 = MOROCCO)
the first act is to have true
and false
array: ArrayFormula(B2:E5="MOROCCO")
输出是这样的
true false false false
true true true false
false false false true
false false false false
然后我们需要将其转换为 1/0
数组。简单的数学运算就可以做到:
Then we need to convert it into 1 / 0
array. Simple math operation will do it:
ArrayFormula(-(B2:E5 = MOROCCO))
现在的输出为:
1 0 0 0
1 1 1 0
0 0 0 1
0 0 0 0
现在我们可以按行添加它们:使用 mmult
函数。但是首先我们需要一列 1
,其中元素数=数组中的列数。要获取它,请使用公式:
= ArrayFormula(TRANSPOSE(COLUMN(B2:E6)^ 0))
Now we may add them by row: use mmult
function. But first we need a column of 1
with the number of elements = number of columns in our array. To get it use formula:=ArrayFormula(TRANSPOSE(COLUMN(B2:E6)^0))
结果是:
1
1
1
1
最后使用mmult: = ArrayFormula(MMULT(-(B2:E6 =摩洛哥),TRANSPOSE(COLUMN(B2:E6)^ 0)))
And finally use mmult: =ArrayFormula(MMULT(--(B2:E6="MOROCCO"),TRANSPOSE(COLUMN(B2:E6)^0)))
结果是:
1
3
1
0
我真的不知道为什么它起作用。
I really don't know, why it works. just use this.
最后一步是计算所有> 0
= 3
And final step is counting all that is > 0
= 3
这篇关于Google表格:多列为COUNTIF,但同一行中超过1则为ONCE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!