在【城市合伙人社群】创作101第一季活动中，通过 帮简小哥在聊天记录中自动统计日更与点评数据 得到了每天日更和点评统计信息，简小哥每周将本周五天的数据（「最终成果」工作表）的简书昵称和简书主页两列内容汇总到一起，然后再统计本周每个作者的日更次数，最后根据日更次数从高到低进行排序。
这个工作可以通过手工来处理，处理步骤如下：
1、将汇总的数据复制一份在新的工作表，然后选中所有数据，选择 数据→删除重复项，这样得到一个没有重复的作者清单。
2、用COUNTIF链接到原表，统计每个简书昵称的日更次数；
3、根据日更次数，用 数据→排序 对日更次数列进行降序排列，完成工作。
考虑到该工作每周都要做，于是研究自动化实现的可能。
一、删除重复项
原始数据简书昵称和简书主页在A、B列，在D列先将不重复的简书昵称用万金油公式构造数组过滤出来。公式如下：
{=INDEX(A:A, SMALL(IF(MATCH($A$2:$A$128, $A$2:$A$128,0) = ROW($A$2:$A$128) -1, ROW($A$2:$A$128), 4^8), ROW(A1)))&""}
万金油公式之前介绍过，这里不再重复。这里介绍一下MATCH函数，该函数形式如下：
MATCH(lookup_value, lookup_array, match_type)
该函数返回符合特定值特定顺序的项在数组中的相对位置。换句话说，用match_type方式，在lookup_array中查找lookup_value值，返回lookup_value在lookup_array中的位置。match_type的参数值如下：
1或省略。查找小于等于lookup_value的最大值。lookup_array必须升序排列。
0。查找等于lookup_value的第一个值。lookup_array可无序。
-1。查找大于等于lookup_value的最小值。lookup_array必须降序排列。
注意：
1、上述公式里面MATCH($A$2:$A$128, $A$2:$A$128,0) = ROW($A$2:$A$128) -1，数据是从第二行开始，减去去1行，如数据从第7行开始，这里应该减去6。
2、MATCH函数里不能有空行，也就是数据到多少行就填多少行，这里是数据从第2行到第128行，如果数据改成了第3行开始，第86行结束，那公式应改为：MATCH($A$3:$A$86, $A$3:$A$86,0) = ROW($A$3:$A$86) -2。
二、建立自动排序辅助列
排序的目标是将日更次数从大往小排列，同样日更次数的简书昵称随意排列，Excel函数RANK只能得到日更次数的排名，无法自动将数据根据日更次数进行重新排列。那么需要借助其他方法来进行数据的自动排序。
经过研究，用COUNTIF函数在E2列得到每个简书昵称的日更次数，公式如下：
=COUNTIF($A$2:$A$500,D2)
对于同样日更次数的简书昵称没法进行区分，必须加以区分才能处理。这里用了一个小技巧：因处理的数据不会超过1000行，那么日更次数×1000+它的序号（行号-1），就能将所有的数据加以区分。即公式如下：
=COUNTIF($A$2:$\A$500,D2)1000+ROW(D2)-1*
这里特别说明一下，如学校招生考试成绩统计，在总分相同的情况下，语数外的总分高的排名在前，那么此时可以将行号替换为语数外总分的排名；如果语数外成绩还相同的情况下，体育成绩高的排名在前，类似地，可以将第一排名×第一排名系数+第二排名×第二排名系数+第三排名，第一、第二排名系数可根据实际情况调整，以第二排名最大值×第二排名系数＜第一排名最小值×第一排名系数为好。
三、通过辅助列进行自动排序
作为最后的结果，在G列增加序号便于查看，H列作为最终排序的简书昵称，用函数INDEX、MATCH、LARGE（与之前介绍过SMALL的完全一样，只是返回最大值）和ROW函数构造数组通过辅助列E列进行自动排序。公式如下：
{=INDEX(D:D, MATCH(LARGE($E:$E, ROW(1:1)), $E:$E, 0))}
这样得到了根据日更次数排序后的简书昵称。
得到了简书昵称之后，在 I 列利用VOOKUP从原始数据A、B列查找对应的作者主页，在 J 列利用COUNTIF从原始数据重新得到日更次数。这样整个自动排序就完成了。
汇总数据自动排序
四、总结
1、每次只需将每周五天的统计数据结果汇总到A、B列，然后将D2的公式根据数据的实际行数进行调整，之后按CTRL+SHIFT+ENTER三个键，即可得到根据日更次数自动化排序的结果。
2、D列的拖拉只能刚好拉到没有数据为止，当然可以先多拉一些行，发现为空，直接删除，后面的自动会变为正确的结果。
3、其实将五天的数据汇总也可以用VBA代码或数据透视表等多种方式实现数据自动化汇总。只是考虑到这个数据量不大，汇总三分钟左右可以完成，就没有采取这样的方案。如果数据量比较大，工作簿汇总个数比较多，可以进一步进行自动处理。这样，从数据源“聊天记录”到最终结果“日更次数排名”都可以实现自动化链接，最大限度的降低手工工作量。
网友评论