这个COUNTIF()公式是做什么的? [英] What is this COUNTIF() formula doing?
问题描述
我想使用excel公式来知道给定范围有多少唯一值。假设我有一些值 - 其中一些重复 - 在A1:A100范围内。我遇到了一个回答,提出了这个公式: (A1:A100, A1:A100&))
>COUNTIF函数中的条件(粗体字符)的参数究竟是什么?
= SUMPRODUCT((A1:A100")/ COUNTIF(A1:A100,A1:A100&))
<在A1:A100,中排除空白单元格中的 一个空字符串()。
它如何做?例如:
A1:A100 = [1,1,2,苹果,桃子,苹果 ,, - , - , - ,...]
then:
A1:A100&= [1,1,2,苹果桃子,苹果,,,,,,...]
所以这个&需要将空白单元格( - )变成空字符串()。如果您直接使用空白单元格,COUNTIF()返回0.使用诀窍,和 - 被视为相同:
COUNTIF(A1:A100,A1:A100)= [2,2,1,2,1,2,94,94,0,0,0,...]
但:
COUNTIF(A1:A100,A1:A100&)= [2,2,1,2,1,2,94,94,94,94,94,...]
如果我们想要获取所有唯一单元格的计数,不包括空格和,我们可以将
(A1:A100 - ),它是[1,1,1,1,1,1,0,0,0 ,0,0,...]
由我们的中间结果,COUNTIF(A1:A100,A1 :A100&),并总结出这些值。
SUMPRODUCT((A1:A100& / COUNTIF(A1:A100,A1:A100&))
=(1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0 / 94 + 0/94 + 0/94 + 0/94 + ...)
= 4
如果我们使用 COUNTIF(A1:A100,A1:A100)
而不是 COUNTIF(A1:A100,A1:A100& )
,然后一些t软管0/94将是0/0。由于零被除以不允许,我们会抛出一个错误。
I wanted to use an excel formula to know how many unique values a given range has. Let's say I have values--some of which repeat--in the range A1:A100. I came across an answer which suggested this formula:
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
What exactly is the argument under "criteria" (in bold characters above) in the COUNTIF function doing?
解决方案 =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
Counts unique cells within A1:A100, excluding blank cells and ones with an empty string ("").
How does it do that? Example:
A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]
so this &"" is needed to turn blank cells (-) into empty strings (""). If you were to count directly using blank cells, COUNTIF() returns 0. Using the trick, both "" and - are counted as the same:
COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]
If we now want to get the count of all unique cells, excluding blanks and "", we can divide
(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]
by our intermediate result, COUNTIF(A1:A100,A1:A100&""), and sum up over the values.
SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4
Had we used COUNTIF(A1:A100,A1:A100)
instead of COUNTIF(A1:A100,A1:A100&"")
, then some of those 0/94 would have been 0/0. As division by zero is not allowed, we would have thrown an error.
这篇关于这个COUNTIF()公式是做什么的?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!