Excel 中计算唯一值

什么是唯一值？

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

在 Excel 中计算唯一值的方法

在 Excel 中有几种计算唯一值的方法。这些方法解释如下：

1. 使用 Sum-Product 和 Countif 公式

结合使用 Sum-Product 和 Countif 公式有助于在 Excel 中查找唯一值。要遵循的步骤是：

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

步骤 2：选择一个新单元格，用户希望在此处显示结果，并输入公式 =SUMPRODUCT(1/COUNTIF(B2:B11, B2:B11))

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

在工作表中，总值为 11，公式在 11 个值中返回唯一值 6。

公式的工作过程

公式的语法是：

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

公式数据表示需要计算以查找唯一值的范围。

COUNTIF 函数计算数据在选定范围内重复的次数。此处选定的范围是 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 将数组中的值相加并显示结果。

如果数据范围中存在空单元格会怎样？

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

COUNTIF 为每个空单元格返回零值；计算值时，SUMPRODUCT 函数返回 1/0=0 的值，称为 #DIV/0! 错误。为了纠正此错误，公式修改为：

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

此处 data 表示需要计算以获得唯一值的范围。

在此示例中，公式编写为：

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

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

2. 使用 SUM 和 COUNTIF 函数

SUM 和 COUNTIF 的组合计算选定单元格范围中的不同值。

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

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

步骤 3：按 Ctrl+Shift+Enter。花括号将公式括起来。

公式的工作过程

在公式中，ISTEXT 函数为包含文本的单元格返回 True，为其他单元格返回 False。

COUNTIF 函数计算文本在选定单元格范围内重复的次数。

COUNTIF 公式将数组（称为除数）的结果除以 1（作为分子，称为被除数）。例如，如果 COUNTIF 公式返回数据计数为 {2, 2,1,5}，则除以 {1/ 2,1/ 2,1/1/,1/5}。除了值 1 之外，其他值都得到分数结果 {0.5, 0.5, 1, 0.2}。

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

3. Sum、Frequency 和 Match 公式

Sum、Frequency 和 Match 公式从给定数据集中查找不同的值。

SUM - Excel 中的 SUM 公式将一系列值或单元格相加。要使用 SUM 公式，请选择要相加的单元格范围，并在要显示总计的单元格中键入 "=SUM("。然后，选择要相加的单元格范围，并通过键入 ")" 关闭括号。公式应如下所示：=SUM(单元格范围)。

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

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

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

步骤 2：选择一个新单元格，用户希望在此处显示结果，并输入公式 =SUM(IF(FREQUENCY(IF(B2:B11<>"",MATCH(B2:B11, B2:B11,0)), ROW(B2:B11)-ROW(B2)+1),1))

步骤 3：按 Ctrl+Shift+Enter。唯一值的数量将显示在选定的单元格中。

公式的工作过程

该公式的通用语法如下：

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

数据 - 选定的数据范围称为 RangeRange。

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

Frequency 公式用于查找选定 RangeRange 中的唯一值。因为它与数字一起使用，所以给定值被转换为数字。

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

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

Frequency 函数返回该值在选定日期范围内出现的频率，IF 函数为唯一值返回“1”，为重复值返回“FALSE”。

4. 用户定义函数

以下是一个 Excel 中用户定义函数的示例，用于计算范围内的唯一值数量

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

步骤 2：按“Alt + F11”打开 Visual Basic 编辑器。

步骤 3：单击顶部菜单中的“插入”，然后选择“模块”以插入新模块。

步骤 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 中唯一值的数量。

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

5. 使用筛选选项

您可以使用 Excel 中的“高级筛选”功能来筛选数据范围并查找唯一值。方法如下：

步骤 1：选择要筛选的数据范围，包括任何列标题。

步骤 2：单击功能区菜单中的“数据”选项卡。

步骤 3：单击“排序和筛选”部分中的“高级”。

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

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

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

步骤 7：勾选“只显示不重复的记录”复选框。

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

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

总结

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