计算 Excel 列中的唯一值

本文介绍了计算 Excel 列中的唯一值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个 .xls 文件，其中有一列包含一些数据.如何计算包含此列的唯一值的数量?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))返回推荐答案要计算 A2:A100 中不同值的数量(不计算空格):To count the number of different values in A2:A100 (not counting blanks):=SUMPRODUCT((A2:A100"")/COUNTIF(A2:A100,A2:A100&""))从@Ulli Schmid 的回答复制到 这个 COUNTIF() 公式在做什么?:Copied from an answer by @Ulli Schmid to What is this COUNTIF() formula doing?:=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,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", "", "", "", "", "", ...]所以需要这个 &"" 将空白单元格 (-) 转换为空字符串 ("").如果您要直接使用空白单元格进行计数，则 COUNTIF() 返回 0.使用该技巧，"" 和 - 都被视为相同: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, ...]通过我们的中间结果 COUNTIF(A1:A100,A1:A100&"")，并对值求和.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如果我们使用 COUNTIF(A1:A100,A1:A100) 而不是 COUNTIF(A1:A100,A1:A100&"")，那么其中一些0/94 本来就是 ​​0/0.由于不允许除以零，我们会抛出错误.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. 这篇关于计算 Excel 列中的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！