在Microsoft Excel中计算唯一值的8种方法

在Microsoft Excel中计算唯一值的8种方法

您想计算 Excel 数据中唯一值的数量吗？

在 Excel 中执行数据分析时，了解列中唯一项目的数量可以为您提供有关数据的宝贵概览。

该统计数据可以帮助您快速识别错误或不一致之处，因为有时只出现一次的值可能是异常值。

本篇文章将向您展示如何在 Excel 中获取唯一值的数量。获取本篇文章中使用的示例文件副本并继续操作！

推荐：修复Microsoft帐户登录错误AADSTS7000112应用程序被禁用

独特 (Unique) 与 独特 (Distinct)：有什么区别？

术语“唯一”经常被误认为是“独特”。它们的含义截然不同，会导致不同的计数。

唯一{A, B, B, C, C, D}值是指在列表中仅出现一次的值。列表中的唯一值为{A, D}，计数为 2。

不同值是不同的值的完整集合。列表中的不同值为。本例中的计数为 4 {A, B, B, C, C, D}。{A, B, C, D}

 提示：如果您确实想了解不同项目的数量，请查看此帖子。

使用 COUNTIFS 函数计算唯一值

COUNTIFS函数允许您计算一定范围内满足一组条件的项目数。

这可用于计算列表中唯一项目的数量。

= SUM ( 1 * ( COUNTIFS ( B5:B14, B5:B14 ) = 1 ) )

上述公式将计算范围B5:B14中唯一项目的数量。

= COUNTIFS ( B5:B14, B5:B14 )

COUNTIFS函数将返回每个项目在范围内出现的次数。由于唯一项目仅出现一次，因此它们的计数为 1。

当您测试它是否等于 1 时，它会返回一个TRUE和FALSE值的数组，其中TRUE值将表示该项目是唯一的。

将此数组乘以 1，它会将TRUE值转换为 1，将FALSE值转换为 0。

将其相加即可得到唯一物品的数量！

 注意：对于没有动态数组的旧版本 Excel，您可能需要使用Ctrl+ Shift+输入此公式。Enter

使用 UNIQUE 函数计算唯一值

UNIQUE函数专门用于从列表中获取不同或唯一值的列表。

这可以与COUNTA函数结合使用来计算唯一值的数量。

= COUNTA ( UNIQUE ( B5:B14, FALSE, TRUE ) )

上述公式将计算B5:B14范围内的唯一项目。

UNIQUE函数返回唯一值列表。此版本的UNIQUE函数使用第二和第三个可选参数，分别设置为FALSE和TRUE。

• 第二个参数设置为FALSE，并告诉UNIQUE函数从数组输入中返回唯一的行。
• 第三个参数设置为TRUE，并告诉UNIQUE函数从输入数组中返回恰好出现一次的项目。

然后使用COUNTA函数计算UNIQUE函数返回的项目数。

使用条件格式计算唯一值

条件格式是一种很好的直观识别满足给定规则的数据的方法。这些单元格可以用填充颜色突出显示。

有一个条件格式规则可用于突出显示列表中的唯一值。

然后，您可以根据单元格颜色过滤列表，以仅显示列表中的唯一值。

然后计数就变得简单了。您可以使用SUBTOTAL函数仅计数可见单元格，也可以从状态栏获取计数。

1. 选择要计算唯一值的值范围。
2. 转到“主页”选项卡。

3. 单击功能区“样式”部分中的“条件格式”命令。
4. 选择突出显示单元格规则选项。
5. 从子菜单选项中选择重复值选项。

6. 从重复值菜单选项中选择唯一。
7. 选择任意颜色选项。请确保您尚未在数据中使用任何这些颜色。
8. 按“确定”按钮将条件格式规则应用到您选择的范围。

现在您将看到所有仅出现一次的值在您的范围内突出显示。

=SUBTOTAL(103,B5:B14)

9. 在工作表中的某个位置添加上述SUBTOTAL公式，以计算范围内的可见单元格

您现在可以基于这些彩色单元格进行过滤，以仅获取SUBTOTAL计数中的唯一项目。

10. 选择范围。
11. 转到“数据”选项卡。
12. 单击“过滤器”命令可将过滤器切换添加到范围。

 提示：您还可以使用Ctrl+ Shift+L键盘快捷键将过滤器切换添加到范围。

13. 单击过滤器切换按钮。
14. 选择按颜色过滤选项。
15. 选择用于突出显示唯一值的颜色。

现在，您已经拥有了由SUBTOTAL函数给出的数据中唯一值的计数。

推荐：如何在Excel中创建数据库

使用数据透视表计算唯一值

数据透视表是汇总大量数据的绝佳工具。它们允许您以多种方式聚合字段，包括计数结果。

这意味着您可以计算列表中的项目数并显示每个项目的数量。

然后，您可以在数据透视表上使用值过滤器将结果过滤为计数为 1 的结果。

这将仅显示唯一的项目，并且数据透视表的总计将是数据集中唯一的项目。

首先，您需要根据数据集创建一个数据透视表。

添加数据透视表的方法如下。

1. 选择数据。
2. 转到“插入”选项卡。
3. 选择数据透视表选项。

4. 选择新工作表或现有工作表作为新数据透视表的位置。
5. 按“确定”按钮创建数据透视表。

6. 在数据透视表字段窗格的行区域中添加要计数的字段。
7. 在数据透视表字段窗格的值区域中添加要计数的字段。

当您将文本字段添加到值区域时，它将默认为Count类型的聚合。

现在您应该会看到行中所有项目的列表以及这些项目的值数量。任何值为 1 的项目都是数据中唯一的项目。

8. 单击行的排序和过滤切换。
9. 选择值过滤器选项。
10. 选择“等于”选项。

11. 选择左侧的“计数”字段。
12. 在右侧输入值1 。
13. 按下“OK”按钮。

这将仅过滤那些计数值为 1 的项目的数据透视表，因此仅显示唯一项目。现在总计将显示唯一项目的总数！

使用 DAX 度量计算唯一值

常规数据透视表的“值”区域可用的聚合类型相当有限，但您可以使用数据模型功能来创建更高级的计算。

这样，您就不需要过滤数据透视表来计算唯一值，只需通过数据透视表的值区域中的公式即可完成。

创建数据透视表时，请确保启用数据模型。

1. 选中“从表或范围菜单的数据透视表中将此数据添加到数据模型”选项。

这将允许您使用 DAX 公式语言为数据透视表的值区域创建度量。

2. 右键单击数据透视表字段窗格中的表。

这将打开测量编辑器。

3. 为新度量命名，例如“ Unique Count”。

=VAR mySummary =
    SUMMARIZE (
        Range,
        Range[Make],
        "Unique", IF ( COUNTA ( Range[Make] ) = 1, 1, 0 )
    )
RETURN
    SUMX ( mySummary, [Unique] )

4. 将上述公式添加到公式区域。
5. 按“确定”按钮将该度量添加到您的数据模型。

6. 单击并将字段拖到“行”区域。
7. 单击新度量并将其拖到值区域。

这将使用SUMMARIZE函数创建一个变量。这将通过Make列汇总数据，并使用计算创建一个名为Unique 的聚合列IF ( COUNTA ( Range[Make] ) = 1, 1, 0 )。对于唯一项，将显示 1，否则将显示 0。

然后使用 SUMX 函数对汇总的虚拟 mySummary 表变量进行迭代并对Unique列求和。

当字段项具有唯一值时，数据透视表将显示 1，否则将显示 0。总计还将反映唯一项的总数。

推荐：WordPress网站分析插件Independent Analytics

使用 Power Query 列分布计算唯一值

Power Query 将在查询编辑器中向您显示有关数据的预览统计信息。

这包括在数据预览中显示每列的唯一项目数。

下列是将数据放入 Power Query 编辑器的方法。

1. 选择数据。
2. 转到“数据”选项卡。
3. 选择“从表/范围”命令。

这将打开 Power Query 编辑器，您将看到数据的预览。您还应该在每列的顶部看到一个唯一计数。

如果您没有看到这些列分布统计信息，那么您可以从“查看”选项卡中启用它们。

1. 转到Power Query 编辑器中的“视图”选项卡。
2. 勾选列分布选项。

使用 Power Query 转换计算唯一值

您还可以通过几个数据转换步骤获取 Power Query 中项目的唯一计数。

您可以按汇总计数对数据进行分组，以查找一次出现的所有项目。

您可以根据计数为 1 的分组项目来过滤结果。

然后，当您计算这些过滤结果时，您将从数据中获得唯一项目的数量。

以下是对数据进行分组的步骤。

1. 转到“变换”选项卡。
2. 单击表格部分中的分组依据命令。

3. 选择基本选项。
4. 选择要计算唯一值的列。
5. 为操作选择“计数行”选项。
6. 按下“OK”按钮。

这将创建一个新的数据表，其中包含每个项目及其相应的项目数量。

现在您可以过滤这些数据。

7. 单击计数列中的过滤器切换按钮。
8. 选择数字过滤器选项。
9. 选择“等于”选项。

10. 在过滤行菜单中输入 1 。
11. 按下“OK”按钮。

现在您可以计算结果了。

12. 转到“变换”选项卡。
13. 单击“计数行”命令。

这将为您计算唯一项的数量！您将在此处获得一个值，然后可以将其加载回 Excel 或在其他查询中使用它。

使用 VBA 计算唯一值

由于现在有可以计算唯一值的 Excel 函数，因此一种解决方案就是构建自己的函数。

您可以使用 VBA 代码创建用户定义函数。您可以像使用网格中的任何其他函数一样使用它，与以前的公式解决方案相比，它将简化公式结构。

转到“开发人员”选项卡，然后单击“Visual Basic”命令以打开 Visual Basic 编辑器。如果您在功能区中没有看到“开发人员”选项卡，也可以按Alt+F11键盘快捷键直接打开 Visual Basic 编辑器。

现在您需要创建一个新模块来放置您的代码。转到Visual Basic 编辑器的“插入”选项卡，然后从菜单中选择“模块”选项。

Public Function COUNTUNIQUEVALUES(rng As Range) As Integer

uniqueCount = 0

For i = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count
        If Application.WorksheetFunction.CountIfs(rng, rng.Cells(i, j)) = 1 Then
            uniqueCount = uniqueCount + 1
        End If
    Next j
Next i

COUNTUNIQUEVALUES = uniqueCount

End Function

将以上代码粘贴到模块中。

这使用该Application.WorksheetFunction方法从工作表访问COUNTIFS函数并在代码中使用它来计算唯一项目的数量。

如果COUNTIFS函数中某个项目的计数为 1，则唯一项目的计数uniqueCount将增加 1。

= COUNTUNIQUEVALUES ( B5:B14 )

现在，您可以使用上述公式来计算范围B5:B14中的唯一值。

使用 Office 脚本计算唯一值

使用代码计算唯一值的另一种选择是使用 Office 脚本。

您可以创建一个 Office 脚本，允许您选择工作表中的某个范围并返回仅出现一次的项目的数量。

转到“自动化”选项卡并选择“新建脚本”选项。这将打开 Office 脚本代码编辑器，您可以在其中编写代码。

function main(workbook: ExcelScript.Workbook) {

  let selectedRange = workbook.getSelectedRange();
  let selectedValues = selectedRange.getValues();
  let rowHeight = selectedRange.getRowCount();
  let colWidth = selectedRange.getColumnCount();

  var allItems: (string | number | boolean)[] = [];
  var uniqueItems: (string | number | boolean)[] = [];

  //Loop through selected range and add values
  //to 1 dimensional array
  for (let i = 0; i 

将上述代码粘贴到代码编辑器并按保存脚本按钮。

现在，您可以在工作表中选择一个范围，然后按代码编辑器中的“运行”按钮。代码将运行并在控制台日志输出中返回计数。

此代码循环遍历所选范围内的所有单元格，并将它们添加到名为的一维数组中allItems。

然后它将循环遍历数组中的所有项目allItems，并使用此方法检查它是否是重复的项目indexOf。

该indexOf方法查找某项首次出现的数组索引号。诀窍在于使用此方法查找当前项的下一个索引。如果没有下一个索引，则返回indexOf-1，并且该项是唯一的！

然后将唯一的项目添加到另一个名为的数组中uniqueItems。

然后使用长度uniqueItems方法在控制台日志中返回数组中项目的数量。

结论

Excel 中没有专门用于查找列表中唯一项数的功能。但有很多有趣的方法可以获取此信息。

COUNTIFS或UNIQUE公式解决方案可能是最简单、最动态的选项。其他功能（如条件格式和过滤器）可能不太适合。

对于涉及重复分析的情况，数据透视表或 Power Query 是不错的选择。

可以构建自定义的 VBA 用户定义函数解决方案，使网格中的公式更易于实现。

也可以使用 Office 脚本，但需要输出到控制台日志或网格中预定的单元格。

推荐：如何在Excel中按颜色求和(公式和VBA)