在Excel中计算唯一值

什么是唯一值

在Excel中，”唯一值”一词指的是数据集或单元格范围中仅出现一次且没有重复的值。基本上，这意味着集合中的每个值都是不同的并且只出现一次。唯一值可以在各种类型的数据中找到，例如姓名、地址、数字、日期或任何其他类型的信息。识别和处理唯一值在许多领域都很重要，例如数据分析、数据库管理和信息技术。了解唯一值在数据管理和分析中很重要，因为它让您能够更清晰准确地了解数据。

Excel中计算唯一值的方法

在Excel中有几种计算唯一值的方法。这些方法如下所述，

1. 使用Sum-Product和Countif公式

结合Sum-Product和Countif公式有助于在Excel中找到唯一值。需要按照以下步骤操作，

步骤1：在工作表中输入数据，即A1:C11

在Excel中计算唯一值

第2步：选择用户想要显示结果的新单元格，并输入公式为=SUMPRODUCT(1/COUNTIF(B2:B11, B2:B11))

第3步：按Enter键。该公式返回所选列中存在的唯一值的数量。

在Excel中计算唯一值

在工作表中，总共有11个数值，并且公式返回这11个数值中的唯一值6。

公式的工作过程

公式的语法是，

=SUMPRODUCT (1/COUNTIF (data, data))

公式中的data代表需要计算唯一值的范围。

COUNTIF函数计算选定范围内data重复的次数。这里选定的范围是B2:B11，并以数组格式表示。

COUNTIF公式将数组称为除数，以1作为分子进行除法运算。例如，如果COUNTIF公式返回的数据计数是{4,4,1,5}，则进行除法运算为{1/4, 1/4, 1/1, 1/5}。除了值为1的情况，其他值的结果是分数形式，如{0.25, 0.25, 1, 0.2}。

SUMPRODUCT会将数组中的值相加并显示结果。

如果数据范围中存在空单元格会发生什么

在这个例子中，计算唯一值时数据范围中存在空单元格。结果显示如下，

在Excel中计算唯一值

COUNTIF函数对于每个空单元格返回零值；而在计算值时，SUMPRODUCT函数将该值返回为1/0=0，称为#DIV/0!错误。为了纠正这个错误，公式被修改为，

=SUMPRODUCT (((data<>"")/COUNTIF (data, data&"")))

这里的data表示需要计算到唯一值的范围。

在这个例子中，公式被写作

=SUMPRODUCT (((B2:B11<>"")/COUNTIF (B2:B11, B2:B11&"")))

在Excel中计算唯一值

在这里，该函数不会返回除以零错误；它返回单元格中唯一值的数量。

2. 使用SUM和COUNTIF函数

组合使用SUM和COUNTIF函数计算所选单元格范围中的唯一值。

步骤1：在工作表中输入数据，即A1:C11。

步骤2：选择新的单元格，用户希望在其中显示结果，并输入公式为=SUM(IF(ISTEXT(B2:B11),1/COUNTIF(B2:B11,B2:B11),””))。该范围表示需要计算的选择范围。

步骤3：按下Ctrl+Shift+Enter。花括号围绕着该公式。

在Excel中计算唯一值

公式的工作流程

在这个公式中，ISTEXT函数对包含文本的单元格返回true值，对于其他单元格返回False值。

COUNTIF函数计算选择的单元格范围中文本重复的次数。

COUNTIF公式将被称为被除数的数组结果除于一个被称为被除数的分子。例如，如果COUNTIF公式返回数据计数为{2,2,1,5}，那么它被分为{1/2,1/2,1/1,1/5}。除了值为1之外，其他的值得到的分数结果为{0.5,0.5,1,0.2}。

SUM函数对数组中的值求和并显示结果。

3. 求和、频率和匹配公式

求和、频率和匹配公式从给定的数据集中找到不同的值。

SUM- Excel中的SUM公式将一系列值或单元格相加。使用SUM公式时，选择要相加的单元格范围，然后在要显示总和的单元格中键入”=SUM(“。然后，选择要相加的单元格范围，并在括号中键入”)”。公式应该看起来像这样：=SUM(单元格范围)。

FREQUENCY- Excel中的FREQUENCY公式计算落在某个范围或分组内的值的数量。使用FREQUENCY公式时，指定数据的范围和要使用的分组。然后，可以使用该公式创建一个表示每个分组频率的值数组。

MATCH- Excel中的MATCH公式用于找到一个值在一系列单元格中的位置。使用MATCH公式时，指定要查找的值、要搜索的单元格范围以及要执行的匹配类型（精确匹配或近似匹配）。

步骤1：在工作表中输入数据，即A1:C11。

在Excel中计算唯一值

第二步：选择新的单元格，用户希望在其中显示结果，并输入公式=SUM(IF(FREQUENCY(IF(B2:B11<>",MATCH(B2:B11, B2:B11,0)), ROW(B2:B11)-ROW(B2)+1),1))

第三步：按下Ctrl+Shift+Enter。唯一值的数量将显示在所选择的单元格中。

在Excel中计算唯一值

公式的工作过程

公式的一般语法如下，

=SUM(IF(FREQUENCY(IF(data<>"",MATCH(data,data,0)),ROW(data)-ROW(firstcell)+1),1)).

数据-所选数据范围称为RangeRange。

第一个单元格-所选单元格范围的第一个单元格称为第一个单元格。

使用频率函数在所选RangeRange中找到唯一值。由于它处理数字，给定的值被转换为数字。

MATCH函数返回在数据范围中出现的项目的位置。如果找到任何重复值，它将返回数据范围中该值的第一次出现。

IF语句用于对数据范围应用条件。如果存在空单元格，则MATCH函数返回#N/A错误。因此，IF语句应用条件数据<>””。

Frequency函数返回所选日期范围内值返回的频率，IF函数对于唯一值返回值’1’，对于重复值返回值’FALSE’。

4. 用户定义的函数

这是Excel中用户定义函数的一个示例，它计算范围中唯一值的数量：

步骤1：打开Microsoft Excel并创建一个新工作簿。

步骤2：按下”Alt + F11″打开Visual Basic Editor。

步骤3：点击顶部菜单中的”插入”，选择”模块”插入一个新模块。

在Excel中计算唯一值

步骤 4：将以下代码粘贴到模块中

Function CountUnique(rng As RangeRange)

Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")

Dim cell As Range

For Each cell In rng

If Not dict.exists(cell.Value) Then

dict.Add cell.Value, 1

End If

Next cell

CountUnique = dict.Count

End Function

步骤 5：保存工作簿并返回到 Excel。

步骤 6：在一个单元格范围内输入一系列数值，例如，A1:C11。

步骤 7：在另一个单元格中输入公式“=Count Unique(B2:B11)”并按 Enter 键。

步骤 8：结果将是 RangeRange 中独特值的数量。

在Excel中计算唯一值

使用VBA公式，CountUnique函数计算选择的RangeRange中唯一值的数量。

5. 使用过滤选项

您可以使用Excel中的“高级筛选”功能来过滤一系列数据并查找唯一值。具体操作如下：

步骤1：选择要过滤的数据的RangeRange，包括任何列标题。

步骤2：点击菜单栏中的“数据”选项卡。

步骤3：在“排序和筛选”部分点击“高级”选项。

在Excel中计算唯一值

步骤4：在“高级筛选”对话框中，选择“复制到其他位置”，并指定筛选数据的位置。

步骤5：在“列表范围”字段中，输入要筛选的数据的单元格范围。

步骤6：在“复制到”字段中，输入要将唯一值复制到的位置。

步骤7：勾选“仅唯一记录”框。

步骤8：点击“确定”应用筛选。

在Excel中计算唯一值

Excel将对数据进行筛选，并将唯一值复制到指定位置。筛选后的数据还将包括原始RangeRange的列标题。

在Excel中计算唯一值

总结

在Excel中，查找唯一值在各种情况下都非常有用，例如数据分析、筛选和排序。在一列中查找唯一值可以快速确定不同的类别、消除重复数据并获取数据摘要。总之，在Excel中查找唯一值是一种有用的数据处理技术，可以帮助您快速高效地了解数据。它还可以帮助您清理数据并删除重复项，从而提高数据质量和更准确的分析。