使用场景

使用七牛云的 sms 短信，最近发现短信每天的使用量都超过了设置报警的每日下限。这种情况是突然出现的，大概就在微信开始对验证手机号组件开始收费之后。微信给到的说辞是，新用户验证手机号是会发送验证码的，而这个之前一直没有收费，现在开始这个费用要转嫁到商户头上。但这个收费对于用户来说，应该是透明的，无从知晓的，所以应该是不会对登录短信造成压力的。

对于突然增加的短信使用量，有过猜测是个别用户在搞事情。不知道七牛云是否设置了用户每日发送上限，亦或是登录获取验证码接口存在 bug 导致了短信的滥发。短信本身的发送和验证都是七牛云接口，所以不存在接口被爆破的情况。下载暴增的近几日的发送记录，想要从中发现发送量存在异常的用户。

导出的 Excel 数据，包含了手机号及一些发送时间、状态之类的信息，想要知道发送量异常的用户，需要对数据进行统计和去重。

Excel 统计次数

参考的是百度经验 excel怎么统计每个数据出现的次数。主要是通过 COUNTIF 函数在空白列统计选中单元格在选择范围内出现的次数。其中需要注意两点：

  • 参数选择数据范围需要添加上固定字符“$”
  • 鼠标下拉填充没有双击方便

一开始使用 COUNTIF 函数在选择数据范围时，是直接在数据栏里选择的，比如 A2:A100。此时如果直接去双击查询所有，再按照统计次数倒序排列，你会发现相同的手机号统计出来的次数竟然不一致，且呈递减态势分布。双击下面的统计数据单元格，会发现数据统计范围是变化的，比如第二行是 A2:A100，第三行是 A3:A100，第四行是 A4:A100... 这恰好是双击动态生成数据的关键，因为选中的单元格也是这样变化的：A2、A3、A4...

所以想要让选择的范围开始不跟着变，那么就得使用 “$”，具体显示为 $A$2:$A$100。

按照统计次数倒序排列，相同的数据号会统计很多次。此时需要对数据去重

Excel 数据去重

Excel 有一个删除重复项的数据快捷操作，只需要选择区域，如选择手机号那一列，然后点击删除重复项就会自动删除选中区域重复的记录。但会有个新问题，因为右侧刚生成的次数是按照手机号区域统计的，所以如果左边列去重了，那么右边的统计会都变成 1。这显然不能达到要求。

可以选择筛选，高级筛选，选择“将筛选结果复制到其他位置”，然后选中当前的所有的数据，复制到可以新开一个 sheet 表单，然后选择同样数量的列，并勾选“选择不重复的记录”，然后确认。

注意：需要选中数据没有多余的列，不然会判定行数据不重复。

当然也可以先将整体数据复制到新的 sheet 中，再通过上面的重复项快捷删除操作。

结果分析

结果分析来，分析去，都没有明确的存在问题的手机号。排在前面的是开发组的几个测手机号，so，没有结果。