我有一个 .xls 文件,列有一些数据。我如何计算这个列中有多少唯一值?
我已经搜索了很多选项,但是它们给出的公式总是给我错误。例如,
= INDEX(List,MATCH(MIN(IF(COUNTIF($ B $ 1:B1,List)= 0 ,1,MAX((COUNTIF(List,& List)+1)* 2))*(COUNTIF(List,& List)+1)),COUNTIF(List,< ;& List+1,0))返回
解决方案要计算A2:A100中不同值的数量(不包括空格):
= SUMPRODUCT((A2:A100& )/ COUNTIF(A2:A100,A2:A100&))
从@ Ulli Schmid 的答案复制到这个COUNTIF()公式在做什么?:
= SUMPRODUCT((A1:A100")/ COUNTIF(A1:A100,A1:A100&)) / pre>计数独特的单元格在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 have an .xls file with a column with some data. How do I count how many unique values contains this column?
I have googled many options, but the formulas they give there always give me errors. For example,
=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))returns
解决方案To count the number of different values in A2:A100 (not counting blanks):
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
Copied from an answer by @Ulli Schmid to What is this COUNTIF() formula 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 + ...) = 4Had 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.
更多推荐
在Excel中的列中计算唯一值
发布评论