|
做数据分析的朋友经常都会做各种报表，日报、周报、月报，专项分析报告……，如果每份报表、每次数据更新我们都要重复地做一次报表，那工作效率就太低了！ 虽然Excel任我们“蹂躏”，但我们应该要知道，最终的报表方向应该是：自动化报表。很多人学了很多年的Excel技能，但还是没有做出自动化报表，很多情况下就是因为方向走偏了。 自动化报表是什么？用月报来理解一下：每月的1号，早上9点钟回到公司，打开电脑，启动做好的月报Excel模板，点一下“刷新”，然后去冲杯咖啡，回到座位后，月报就已经做好了！然后……刷刷微信、刷刷微博……下午下班前告诉老板：“搞了一天，月报终于搞完了！”~~~ 要实现自动化报表，首先应该自己在处理一些常见问题时，时刻要往自动化方向去想，而不要只是纯粹地解决一个问题，否则你会陷入各种Excel的疑难杂症里，搞得出不来~ 今天给大家分享一个常见的疑难点：“不重复计数”如何实现自动化。本文会提供一个自动化的解决套路，帮助大家轻松解决它！ 什么是不重复计数？直接来看实际问题： 想看每天的订单数？ （数据源如下图所示） 理解数据 上图的数据源结构，我们在公司的系统里经常可见。以2019年1月1当天的订单看，是有3张订单D001、D002、D003，但是因为D001、D002订单中都包含了多个商品，所以同一天对应的订单号是会有重复值的。 如果是要统计每一天的成交数量、成交金额，相信大家都会。因为可以直接通过生成数据透视表，然后把【成交数量】、【成交金额】字段拉到值区域即可完成汇总统计。但我们现在是要对【订单编号】进行不重复计数，这个要怎么解决呢？ 思路分析 可能有些高阶一些的朋友，会想到了函数法。若在这里单纯地使用函数法去汇总，我非常不推荐，因为一定会用到数组函数。数据分析报表中，不建议使用数组函数的原因：➀写起来复杂➁可读性差➂数据增加后，还要修改公式➃运行速度慢（所以经常做数据分析的朋友，不建议过多花时间去研究数组函数，因为并不实用，多去想更高效的方法。） 要进行分类汇总，数据透视表仍应该是我们首先想到的。但是数据透视表的值字段只能设置为“计数”，并没有“不重复计数”。所以如果直接按传统做法去使用数据透视表，会出现下图情况： 可以看到1月1日的订单编号计数为8，即是当天对应的所有订单编号的计数，并没有识别重复项目统计，这并不是我们想要的结果。 所以若要继续用数据透视表，现在的思路是有2个方向：
方法一：提前处理 这个方法的整体思路是需要在数据源中，增加一个【重复项标记】字段，然后再生成数据透视表，最后用【重复项标记】字段去进行筛选不重复项计数。 ➀ 增加【重复项标记】字段 这里要用到countifs函数来去对日期、订单编号字段进行计数，而且是一个“滚动统计”的技巧： “滚动统计”的技巧是利用了绝对引用的B2、C2单元格，当公式往下填充时，保证计数的区域是从第2行开始到当前行，所以就能统计到重复项是第几次出现，那么只要计数结果是大于1的，就肯定都是重复的项，所以外嵌一个if函数判断即可！（这个滚动统计的技巧，在累计求和等场景都可应用，大家要好好理解~）大家再看看下图，好好感受一下在不同的行里填充的公式： 通过这样的技巧，我们可以看到1月1日，当D001第1次出现时，显示为“不重复”，当后面出现时就显示为“重复”，后面我们在透视表中只要筛选“不重复”即能实现不重复计数！ 这时我们再插入透视表，用【重复项标记】字段进行筛选“不重复”，即可实现不重复计数！ 在Excel2016版本以下的用户，推荐使用以上的办法实现，因为即使你增加了更多的数据，要算1个月的、1年的等更长时间段的统计，都能自动化实现，一劳永逸！ 方法二：后期处理 数据源不提前处理，我们就只能通过在透视表处理，但也只能在Excel 2016以上的版本，我们才可以直接在透视表里实现。 因为Excel 2016开始，Excel就集成了Power Pivot组件，从名字我们就知道是一个“强大”的数据透视表，其中就增加了“不重复计数”的功能。操作的方法就是需要我们在插入数据透视表的时候，点一下“将此数据添加到数据模型”，再按“确定”生成数据透视表即可。 然后在透视表中，可直接在值字段设置中，切换值汇总方式为“非重复计数”： 按下“确定”后，就可以看到已完成了不重复计数了： 总结 既然两种方法都能实现自动化，我们应该怎么选择呢？来总结总结好处： 方法一：用函数增长了辅助字段，略显麻烦；但各版本通用，生成的透视表中总计是等于每天的值相加。 方法二：方便快捷！但仅能在Excel2016以上版本使用，所以若发文件给低版本的Excel，将无法刷新计算。生成的透视表中总计是【订单编号】的不重复计数。 两种方法都能实现自动化，在总计的汇总方法会有区别（不过这样分析，一般也用不上显示总计），大家根据自己的版本，实际工作场景需要选用即可！ 好，关于“不重复计数”的自动化实现方法就介绍到这里了，希望对大家有些帮助。 当然关于Excel自动化报表的知识，还有太多了，不可能短短一篇文章讲得完。但给到大家的建议就是，如果你在学习Excel，希望你能选择那些能告诉你更多“自动化”的实用技能的课程，这样你的方向就是正确的，制作报表就会越走越顺！ 例如黄成明老师推出的《数说》栏目，在制作数据报表时，就会分享数据分析产品化的思维。这种思维告诉我们在做报表时，我们其实是在做一个产品！如果大家也在学Excel，也在学数据分析，欢迎加入《数说》会员： |
|
|
