StarLight
0%

Excel去重复计数套路公式

发表于 更新于 分类于

我们常常需要在Excel中计算某一列的去重复计数，例如客户数量，SKU数量等等。但是在Excel中并没有类似于DISTINCTCOUNT这样的函数可以直接求出一列中去重复计数的值，所以我们只能用两个或者两个以上的函数组合来求出去重复计数。

去重复计数，我们往往会遇到两种情况，一种是无筛选条件的去重复计数，另一种是有筛选条件的去重复计数。

下面我分别给大家介绍几个常用的公式套路。

无筛选条件的去重复计数

下面就介绍两个最简单的计算“无筛选条件的去重复计数”的公式。

什么是无筛选条件的去重复计数呢？我们可以看到下图，有Channel列，Customer Category列，不过我们计算的是所有Channel，Customer Category的不重复Customer ID的数量。也就是对Channel和CustomerCategory不做筛选。

需要说明，这两个公式并不是运行效率最高的公式，但是最简单最容易理解。如果数据行数超过50万行，不建议使用此公式，反之，推荐使用。
image-20230817111505556

  1. 如果你用的是比较新的Excel版本，例如公司笔记本的Excel版本，可以使用此公式。
    =COUNTA( UNIQUE( C2:C151 ) )
    大家在使用此公式的时候，只需要将C2:C151替换为你要计算去重复计数的列单元格范围即可。
  2. 如果你用的Excel版本是2019版本之前的（公司的Excel版本是2019之后的。）
    =SUMPRODUCT( 1 / COUNTIF( C2:C151 , C2:C151 ) )
    同样，和第一条公式一样，只需要将COUNTIF中的两个C2:C151替换为你要计算去重复计数的列单元格范围即可。

有筛选条件的去重复计数

什么是有筛选条件的去重复计数呢？我们可以看到下图，我们计算的是每个Channel的不重复Customer ID的数量。也就是对Channel做了筛选。这样的去重复计数就是有筛选条件的去重复计数。

  1. 下面来介绍一种单筛选条件的去重复计数。也就是只对一列做筛选，例如下图只对Channel做了筛选。

image-20230817114016404

此处我只介绍一种计算公式，如上图。因为其他计算方式会比较复杂难以理解。

COUNTA( UNIQUE( FILTER( C:C , A:A=H13 ) ) )

大家在使用此公式的时候

  • 记住将C:C替换为你要做去重复计数的列（也可以像前面的C2:C151一样写计算的单元格范围）
  • 记住将A:A替换为你要做筛选的列，例如Channel列就是A:A，如果是Customer Category列就是B:B
  • 记住将H13修改为筛选列需要等于的值，例如DS / WS / KA，图中为引用了单元格的值。大家也可以将A:A=H13改为A:A="DS"，直接输入需要等于的值。
  • 考虑到我们很多时候需要横向或者纵向自动填充公式，我们需要对上面两条中的C:C & A:A酌情考虑是否需要锁定（官方叫法是绝对引用）。

  1. 下面来介绍一种多筛选条件的去重复计数。也就是对多列做筛选，例如下图对Channel & Customer Category同时做了筛选。
    image-20230817115220921

    此处我们基于单筛选条件的公式稍作修改，即可。

    COUNTA( UNIQUE( FILTER( C:C , ( A:A=H13 ) * (B:B=J3) ) ) )

    可以发现，此公式和上面的但筛选条件公式，唯一的区别是从 A:A=H13 变为了 ( A:A=H13 ) * (B:B=J3)。

    原理很简单，多条件筛选，就是让多个判断条件相乘即可。同理，如果有100个筛选条件，就把100个筛选条件相乘即可。

本次仅仅介绍计算公式套路，不对公式做详细的介绍。如果想要了解每一个函数的具体使用方法和原理，可以自行百度，或者来找我了解。