使用场景

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

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

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

Excel 统计次数

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

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

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

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

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

Excel 数据去重

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

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

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

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

结果分析

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