如果使用过滤的数据计数 [英] CountIf With Filtered Data
问题描述
即使数据被过滤,我正在研究一种数据列中零数的方法。我找到以下解决方案:
I was researching a way to count the number of zeroes in a column of data, even if the data gets filtered. I found the following solution:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0)
其中, B2:B18
是数据的总列表,Pear
是正在计算的标准。
Where, B2:B18
is the total list of data and "Pear"
is the criteria being counted.
- 有人可以解释这个公式是如何完成这项任务的?
- 有没有更简单的方法?
推荐答案
我能够确定:
SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1))
用于返回在范围内可见和隐藏哪些单元格的数组。 1返回可见,0返回隐藏。
is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden.
ISNUMBER(SEARCH("Pear",B2:B18))+0)
用于返回一个数组,其中包含Pear
。如果找到Pear,则返回1,否则为0。
is used to return an array of which cells contain "Pear"
. If "Pear" is found, 1 is returned, else 0.
SUMPRODUCT(arrayofvisiblecells , arrayofcellswithPear)
用于计算单元格可见时的所有时间,并显示Pear。 1 * 1
否则您将乘以 0
。
is used to sum all of the times when the cell is visible AND "Pear" is present. 1*1
else you will be multiplying by a 0
.
这篇关于如果使用过滤的数据计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!