2021-08-14 15:30 来源: OFFICE 职场训练营
上个月，在给北京一家药企培训时，有伙伴问如何计算不重复的个数。这是一个有代表性的问题，我将方法整理出来，看你能不能用得上？
案例数据
这是公司的部分销售订单表，主要涉及省份、城市、业务类别和金额。
要求：
统计公司三大业务覆盖的省份数量。
这是典型的非重复计数的案例，从今天开始营长会给你介绍函数法、透视表法、Power Query 法和 Power Pivot 法，供你参考。
1. 组合函数法
首先介绍经典的组合函数
SUMPRODUCT(1/COUNTIF)
先不考虑业务类别的因素，仅仅统计表中共有几个省份
在 H7 单元格中，输入公式：
\=SUMPRODUCT(1/COUNTIF(C2:C23,C2:C23))
在 365 版本中，可以用 UNIQUE 函数获取唯一值的列表，再用 COUNTA 函数统计。
H7=COUNTA(UNIQUE(C2:C23))
那要加上业务类别的限制怎么办？
公式要做调整，COUNTIF 变成 COUNTIFS
组合函数为
SUMPRODUCT(条件 1*（1/COUNTIFS(条件 1 范围，条件 1 范围，统计范围，统计范围)))
条件 1 范围和统计范围在函数中的位置可以互换。
I7 单元格中输入公式：
\=SUMPRODUCT(($EE
%5C*(1%2FCOUNTIFS(#card=math&code=23%3DH5%29%5C%2A%281%2FCOUNTIFS%28&id=WE8ST)C
C
C
C
E
E
E
E$23)))
当然，用 365 版本公式要简单得多，除了用 COUNTA 和 UNIQUE 函数外，还要用到动态筛选函数 FILTER
公式变成了
\=COUNTA(UNIQUE(FILTER($CC
E
E$23=H5)))
2. 数据模型透视法
如果用 Excel 默认的透视表来做，会发现统计结果还是重复计数，非重复计数是灰色的。
怎么办？
怎么办？
怎么办？
其实，特别简单，只需勾选 “将此数据添加到数据模型” 即可。
数据模型功能建议至少用 2016 版本。
这样，在 “值汇总依据” 中可以用“非重复计数” 功能了。
结果也就显示出来了。
看来，数据模型做的透视表还真不一样。数据模型支持多表建立关系，Excel 数据不再像信息孤岛一样，仅仅通过 VLOOKUP 建立联系。而是组团作战，多张表可以形成互相关联的数据库，也就是模型。
根据数据模型可以实现多表关联透视，我称之为 “超级透视”。
所以，将来表哥表姐见面了，会问到 “你用超级透视了吗？”
3.Power Query 数据查询法
Power Query 是从 Excel 2016 开始软件内置的数据查询工具，我称之为 Excel 最最强大的后台，也就是我们要逐步更新一种观念，Excel 数据处理分为前台和后台两种工具，我们平时看到的更多的就是 Excel 的前台表格。
下图显示的是 Office 2016 和 Office 365 版本中 Excel 的 Power Query 功能。
Excel 2016
Excel 365
在 Excel 中与 Power Query 有关的功能都集中在功能区 “数据” 选项卡中，不同版本功能菜单名称略有差别。
利用上面的工具我们可以开始数据加载，在进行查询编辑的时，Excel 也会自动打开 “Power Query 编辑器”。
回到我们的案例，首先要将数据导入 PQ 编辑器中
进入 PQ 编辑器，点击 “分组依据” 进行分类汇总
按业务类别进行计数
默认的汇总结果如下，很显然没有去除重复数据。
这时候，需要改变上方的函数语句
原始语句为
\= Table.Group(更改的类型, {“业务类别”}, {{“计数”, each Table.RowCount(_), Int64.Type}})
更改为
\= Table.Group(更改的类型, {“业务类别”}, {{“省份数量”, each List.Count(List.Distinct(_[省份])), Int64.Type}})
最后，关闭并上载即可。
就将数据传送到了 Excel 前台表格中，还支持一键刷新。
4.Power Pivot 度量值法
Power Pivot 在 Excel 作为一个 “COM 加载项” 提供，默认没有启用。下面我们介绍启用该加载项的方法。
步骤 1： 在 Excel 的 “文件” 选项卡中选择【选项】 ，出现的对话窗中左边选择【加载项】 ，右侧窗口选择【管理：COM 加载项】 ，然后点击【转到】 按钮。
步骤 2：出现的对话窗中勾选 “Microsoft Power Pivotfor Excel” 选项。
这里大家也可以选择其它 ExcelPower 加载项。完成加载后可以在功能区上看到对应的工具选项卡。
首先需要将数据添加到数据模型中
添加后的 Power Pivot 窗口
退出 Power Pivot 窗口后，点击 “新建度量值”
度量值名称：不重复省份数量
公式中输入
\=DISTINCTCOUNT(‘订单表’[省份])
DISTINCT 函数可以去除重复值
DISTINCTCOUNT 函数是统计去除重复值后的数量
这些都是 Power Pivot 模型中的 DAX 函数。
建立好度量值后，就可以创建数据透视表，需要从数据模型中创建。
会发现透视表的字段列表中增加了刚刚建立的度量值
fx 不重复省份数量
前面有 fx 标记
将度量值和业务类别拖动到对应的统计位置即可
那么，问题来了
透视表中的总计为什么不是 13，而是 7？
好了，这次关于不重复计数的教程就全部结束了，希望对你有帮助。返回搜狐，查看更多
若有收获，就点个赞吧
0 人点赞
