跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

如何在Excel中根据多个条件统计唯一值的数量？

Author Xiaoyang Last modified

在许多实际场景中，通常不仅需要统计数值的数量，还需要确定数据中有多少唯一项符合某些条件。例如，您可能希望找出某个特定销售人员卖出了多少种不同的产品，或者在某个特定时间段内下了多少个唯一的订单。在Excel中高效处理此类任务需要熟悉合适的公式、高级功能（如数据透视表）甚至自定义VBA解决方案。本文将探讨几种基于一个或多个条件统计唯一值数量的实用方法，并提供分步说明和技巧。

根据单个条件统计唯一值

根据两个指定日期统计唯一值

根据两个条件统计唯一值

根据三个条件统计唯一值

使用数据透视表统计唯一值（去重计数，Excel 2013+）

使用VBA代码统计唯一值（适用于复杂/自动化情况）

arrow blue right bubble 根据单个条件统计唯一值

让我们考虑一个常见案例：您希望统计Tom卖出了多少种不同的产品。当您拥有一个简单数据集，并希望通过单一条件（例如某个人的销售记录）评估唯一性时，此方法非常适用。它虽然直接明了，但需要谨慎使用数组公式。

A screenshot showing a dataset for counting unique values based on one criteria in Excel

对于这种情况，在空白单元格（例如单元格G2）中输入以下公式：

=SUM(IF("Tom"=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20)),0))

输入公式后，按 Ctrl + Shift + Enter（而不是仅按Enter）确认为数组公式。公式栏中的公式周围会自动出现大括号，并且您将立即看到如下所示的结果：

A screenshot showing the result of counting unique values with one criteria

注意：

  • “Tom” 是您用于筛选结果的条件。如果您希望更加灵活，可以用另一个单元格的引用来替换 “Tom”（例如，$F$2）
  • $C$2:$C$20 包含需要评估的销售人员名称。
  • $A$2:$A$20 是您希望进行唯一计数的产品列。
  • 如果您的数据范围发生变化，请记得相应调整引用。

提示：如果使用Excel 365或Excel 2019及更高版本，您可以尝试使用UNIQUE和FILTER函数以获得更简单的公式。

如果遇到任何 #DIV/0! 错误，请仔细检查条件，并确保您的范围长度相等。

arrow blue right bubble 根据两个指定日期统计唯一值

当您需要在特定日期范围内查找唯一项的数量时，例如，所有在2016年9月1日至2016年9月30日之间售出的不同产品，可以应用这种方法。这在分析某些时期之间的数据趋势（如月度、季度或自定义日期范围）时尤其有用。不过请小心日期格式；它必须与工作表中的日期值匹配。

在您希望显示结果的空白单元格中输入以下公式：

=SUM(IF($D$2:$D$20<=DATE(2016,9,30)*($D$2:$D$20>=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016,9,30),$D$2:$D$20, ">="&DATE(2016,9,1))),0)

输入公式后，按 Ctrl + Shift + Enter 执行为数组公式。下方截图展示了结果：

A screenshot showing the result of counting unique values between two dates in Excel

注意：

  • 2016,9,1 和 2016,9,30 是起始日期和结束日期的标准。您可以根据需要修改这些日期，甚至可以使用单元格引用来实现动态日期过滤。
  • $D$2:$D$20 包含要检查的日期条目。
  • $A$2:$A$20 再次是您希望唯一计数的项目或产品列。
  • 确保您的日期存储为有效的Excel日期，而不是文本字符串。如果结果未按预期显示，请确认您的日期格式和范围。

提示：使用 DATE(year, month, day) 避免区域日期格式问题。当使用动态范围时，考虑使用命名范围以提高清晰度。

arrow blue right bubble 根据两个条件统计唯一值

假设您只想分析Tom在九月内售出的产品，结合姓名和日期范围来进行唯一计数。这种场景在基于期间的绩效审查或细分分析中很常见。随着条件的扩展，公式变得更加复杂，对数据准确性的关注也变得更为重要。

在任意空白单元格中输入以下公式，例如H2：

=SUM(IF(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016,9,30)*($D$2:$D$20>=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016,9,30),$D$2:$D$20, ">="&DATE(2016,9,1))),0)

输入公式后，按 Ctrl + Shift + Enter 确认。您应立即看到唯一计数；请参见以下插图：

A screenshot showing the result of counting unique values with two criteria in Excel

注意：

  • “Tom” 是名称标准，而 “2016,9,1” 和 “2016,9,30” 是日期范围的边界。根据需要进行调整，或通过单元格引用来使其动态化。
  • $C$2:$C$20 是员工（或其他第一个标准）列；$D$2:$D$20 是日期列；$A$2:$A$20 包含要计数的唯一项。
  • 范围必须全部长度相等，以免发生错误。

如果您希望使用“或”条件，例如统计由Tom卖出的唯一产品或在南部地区卖出的唯一产品，可以使用以下公式。这使搜索条件更加广泛，但如果数据同时满足两个条件，则结果可能会重叠：

=SUM(--(FREQUENCY(IF(("Tom"=$C$2:$C$20)+("South"=$B$2:$B$20), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20))>0))

不要忘记按 Ctrl + Shift + Enter。您将看到如下所示的结果：

A screenshot showing unique values counted based on an 'or' condition in Excel

提示：当应用“或”条件时，请注意如果同一记录满足两个条件，可能会导致重复计数。对于大型数据集，性能可能受到影响。

arrow blue right bubble 根据三个条件统计唯一值

有时您的分析可能需要三个或更多条件，例如确定Tom在九月内仅在北部地区售出的唯一产品。这在多维数据分析中很常见，用于报告或有针对性的业务洞察。在处理这种复合逻辑时，仔细管理引用至关重要。

在此空白单元格（例如I2）中放置此数组公式：

=SUM(IF(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016,9,30))*($D$2:$D$20>=DATE(2016,9,1))*("North"=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016,9,30), $D$2:$D$20, ">="&DATE(2016,9,1), $B$2:$B$20, "North")),0)

按 Ctrl + Shift + Enter 完成。以下是参考示例结果：

A screenshot showing unique values counted based on three criteria in Excel

对于高级条件，请仔细检查所有范围是否一致，并确保数据类型（例如日期和文本）正确。不对齐可能导致错误或误导结果。

提示：

  • 如果在大型数据集上遇到性能问题，请考虑分解公式或使用Excel的数据透视表解决方案。
  • 为所有条件使用命名范围或引用单元格可以提高可读性并减少公式错误。
  • 对于频繁使用的场景，请考虑将这些公式记录在命名单元格引用或自定义函数中。

arrow blue right bubble 使用数据透视表统计唯一值（去重计数，Excel 2013+）

对于Excel 2013或更高版本的用户，数据透视表提供了一种更具交互性、无需公式的替代方法，可以在一个或多个条件下统计唯一值。去重计数功能帮助您高效地汇总和过滤大型数据集，因此这种方法特别适合于动态、基于报告的环境。但是，请注意早期版本的Excel不支持数据透视表内的去重计数功能。

如何使用此方法：

  1. 选择您的数据集，然后转到 插入 > 数据透视表。
  2. 在创建数据透视表对话框中，选择放置数据透视表的位置，勾选“将此数据添加到数据模型”框，然后点击 确定。
  3. 将您希望唯一计数的字段（例如产品）拖动到值区域。默认情况下，它将显示为“计数...”。
  4. 点击值区域中的字段并选择 值字段设置。
  5. 在弹出的对话框中，向下滚动并选择 去重计数 （此选项仅在Excel 2013或更高版本中可用，并且在使用“将此数据添加到数据模型”选项创建数据透视表时才会出现）。
  6. 将您的条件字段（例如销售人员、区域、日期）添加到筛选器或行/列区域，以应用单个或多个条件。
  7. 您的数据透视表现在将显示根据您选择的条件过滤后的唯一值计数。

优点：高度可视化，易于调整过滤器而无需编辑公式，适合交互式报告。

局限性：在Excel 2010或更早版本中不可用；添加新数据需要手动刷新数据透视表。

实用提示：始终确保源数据在同一记录中没有意外的重复项。如果发现去重计数选项缺失，请重新创建数据透视表并检查“将此数据添加到数据模型”选项。

arrow blue right bubble 使用VBA代码统计唯一值（适用于复杂/自动化情况）

有时，您可能需要根据各种条件自动统计唯一值，尤其是在处理非常大的数据集或经常重复分析时。VBA宏非常适合这种情况，因为它可以在设置后快速处理不同逻辑——包括多条件过滤——而无需手动干预。然而，VBA比常规Excel功能更高级，因此最好由熟悉宏或有持续分析需求的用户使用。

操作步骤：

  1. 按 Alt + F11 打开VBA编辑器。在编辑器中，选择 插入 > 模块 创建一个新模块。
  2. 将以下VBA代码复制并粘贴到模块中：
Sub CountUniqueWithCriteria()
    Dim DataRange As Range
    Dim CriteriaRange As Range
    Dim CriteriaValue As Variant
    Dim Dict As Object
    Dim i As Long
    Dim UniqueCount As Long
    Dim ResultCell As Range
    
    Set Dict = CreateObject("Scripting.Dictionary")
    
    ' Prompt for range settings
    Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
    Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
    CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
    Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
    
    On Error Resume Next
    For i = 1 To DataRange.Rows.Count
        If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
            If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
                Dict.Add DataRange.Cells(i, 1).Value, 1
            End If
        End If
    Next i
    
    UniqueCount = Dict.Count
    ResultCell.Value = UniqueCount
    
    MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub
  1. 关闭VBA编辑器并返回到您的工作表。按 Alt + F8，选择 CountUniqueWithCriteria 并运行宏。
  2. 按照输入提示根据您的数据指定范围和条件。结果将出现在您选择的单元格中，并且还会作为消息框显示。

参数说明和注意事项：

  • 此宏目前设置为一个条件。要将其扩展为多个条件，请修改循环内的 If ... Then 逻辑。
  • 运行宏之前始终保存您的工作簿，因为宏所做的更改无法撤销。
  • 如果遇到执行错误，请在您的Excel设置中启用宏。
  • 这种方法适用于较大或频繁更新的数据集，手动公式在这种情况下会显得笨拙。

优势：高度可定制和自动化，高效处理大而变化的数据集。适用于高级或重复的工作流程需求。

缺点：需要宏权限，初学者可能需要时间熟悉VBA操作。

在基于条件统计唯一值时，始终确认您的范围引用，并确保所有条件列大小对齐。范围不匹配是错误或不正确结果的常见来源。如果公式返回意外结果，请检查隐藏的格式问题或空白单元格。对于性能关键的场景，数据透视表和VBA提供了强大的替代方案，优于数组公式。选择最适合您舒适度和数据集复杂性的解决方案。请记住，Kutools for Excel 提供了额外的工具和快捷方式，可以简化许多任务，从而在复杂工作簿中提高效率。

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）……
15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言！

通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……

Office Tab为Office带来多标签界面，让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠