重复公式函数公式式求出现的次数（去除重复的）？

来源：蜘蛛抓取(WebSpider) 时间：2023-12-06 09:06 标签： 重复公式函数公式
在数据统计时，我们可能要对某列进行非重复值进行计数，例如从订单数据中统计出【订单数】：订单数据数据中的 Order001 订单中共销售了 3 件商品，在订单数据中出现了 3 行，但我们统计订单数时，这种情况是属于 1 张订单，所以图中数据共有 5 张订单。这种情况还是很常见的，在 Excel 中也有多种方法可以实现，本文就做一个小总结。操作类方法删除重复项，这是最直接的办法。但是建议把订单号整列复制一份出来去操作，避免直接破坏了数据源。操作的方法很简单：删除重复项然后就能得到唯一值列表了：唯一值列表高级筛选，这个操作是可以筛选唯一值的，用起来也比较方便：高级筛选传统函数传统函数就是所有版本都通用的函数，可以直接在C2单元格中写公式：= SUM(1 / COUNTIFS(B5:B13, B5:B13))很多朋友都会觉得这个有点难理解，以下简述一下它的运算过程，相信可以方便理解：step1：上面的公式中，首先是计算COUNTIFS(B5:B13, B5:B13)，正常来说第二参数应该是一个单元格，但现在传入了一个区域，所以 Excel 会帮你把这个区域里的每个单元格都执行一次计数，得到的就是一组数，结果如下：= SUM(1 / {3;3;3;2;2;1;1;2;2})step2：然后执行的是1 / {3;3;3;2;2;1;1;2;2}，这个就是一个除法计算，正常来说分母应该是一个值，但现在传入了多个值，Excel 会帮你循环逐个进行计算，得到的结果如下：=SUM({0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;1;1;0.5;0.5})用 1 除以它们的出现次数，多次出现的值的计数就会变成 1/n ，只出现 1 次的值即使用 1 除了后还是 1。step3：最后再用 SUM 函数把它们加起来，就能算出非重复值计数的统计结果了！SUM 函数的计算可以理解为以下的过程，就是把上面的数字加起来，为了方便理解，我就写成分数形式了：1/3 + 1/3 + 1/3 + 1/2 + 1/2 + 1 + 1 + 1/2 + 1/2最终的计算结果就是 5新函数这里的新函数，就是指 Excel 2021 及 365 版本才有的函数。可以直接在C2单元格中写公式：= COUNTA(UNIQUE(B5:B13))可以看到，新函数的出现，让我们统计起来更方便了！有些朋友可能还在用旧版本，可以先了解一下它的逻辑：step1：公式当然就是先计算UNIQUE(B5:B13)，得到的结果是B5:B13的唯一值列表（返回的是一组数据，可以理解为一个区域）：UNIQUE返回唯一值列表step2：然后就是 COUNTA 去计算这个区域的非空值个数：= COUNTA({"Order001";"Order002";"Order003";"Order004";"Order005"})最终的计算结果就是 5传统透视表传统透视表就是所有版本都通用的透视表功能。透视表可以非常方便我们进行分组汇总，当你把字段拖动到行区域时，得到的结果就已经是唯一值列表。透视表得到唯一值列表后，你用函数统计也行，甚至临时用鼠标选中单元格区域，看看状态栏上的计数也行，很简单很方便，就不多说了。超级透视表自从 Excel 2016 后，Excel 就有了超级透视表（Power Pivot），在我们要插入透视表的界面中，勾选【将此数据添加到数据模型】使用 Power Pivot只是勾选一个选项就能使用 Power Pivot 了，真的很简单！接下来就是把订单号字段拖动到值区域：值字段设置然后鼠标左键点击，选择值字段设置，选择非重复计数即可。值汇总方式：非重复计数只是通过鼠标的点击就能完成统计，而且透视表还能分组汇总，Power Pivot 真的很高效！Power QueryPower Query 也是 Excel 2016 版本后的内置功能，只需要点点鼠标也能轻松完成统计！Power QuerySQLExcel 里也是支持使用 SQL 语句查询的，我也一直认为在 Excel 中学习 SQL 是适合非计算机专业人士的最友好方式。先依次地按下快捷键 Alt → D → D → D，然后选择当前的工作簿进行连接，具体还是看动图吧：在 Excel 中使用 SQL select
count(*) as 订单计数
from
(
select
distinct [订单号]
from
[Sheet1$A4:E13]
)VBA 自定义函数VBA 在 Excel 里是万能的，上面所有的办法都可以用 VBA 来调用。除此之外，还能使用 VBA 原生的一些方法去处理，例如用集合或字典去计数，用集合的话在 mac OS 也能通用，以下就用集合去定义一个函数：Public Function DistinctCount(target As Range) As Long
Application.Volatile
Dim colDistinct As New Collection, rng As Range
On Error Resume Next
For Each rng In target
If Not IsEmpty(rng.Value) Then colDistinct.Add rng.Value, CStr(rng)
Next rng
DistinctCount = colDistinct.Count
End Function然后我们把以上的代码放到模块中把代码放到一个新建模块中然后我们就能在工作表中去正常地使用自定义的DistinctCount函数了：使用自定义函数ps：使用了 VBA 代码，记得把工作簿另存为 xlsm 格式的文件小结由于 Excel 的技能方向实在太多了，所以同一个事情在 Excel 里经常会有很多种解决方法，选择自己熟悉的或者是适合当前场景的方法就是最佳的方法。Excel 这几年的更新还在不断加强多端设备（Windows/mac OS/ios/Android/Web 等）通用的功能，现在的 Office JS 脚本已经趋于完善了，以后的应用场景还将会更丰富，用好 Excel 就确实能解决企业日常 90%以上的分析场景。

我要回帖

更多关于 重复公式函数公式 的文章

 

随机推荐