[Excel]帮简小哥从聊天记录自动统计数据解读2

今天继续对 帮简小哥在聊天记录中自动统计日更与点评数据 进行详细解读——聊天记录粗加工汇总。

在Excel中对聊天记录粗加工后，聊天记录处理结果如下：

聊天记录粗加工

这里面除了必要的内容，其他无关的聊天记录都已经被筛选掉了，变为了空行，实际上就是IF函数条件为假时设置的零值或各函数出错时IFERROR函数设置的零值。这里是零值还是空值（“”）很重要，后面将要用到。

为了方便下一步的处理，将这些数据需要汇总到一起，这里必须用到Excel的万金油公式——INDEX-SMALL-IF-ROW。具体如下：
{=INDEX(A:A, SMALL(IF($A$2:$A$5000<>0, ROW($A$2:$A$5000), 4^8), ROW(A1))) &""}

INDEX-SMALL-IF-ROW组合可以实现一对多查找和满足多个条件的多对多查找，可以实现强大的查找功能。今天在这里结合日更、点评模板汇总的需要，简要介绍其数组形式。

1、Index函数

该函数有数组和引用两种形式，其数组形式如下：
　INDEX(array, row_num, [column_num])
该函数返回表或数组中元素的值，有行号和列号索引选择。
array 必需。为单元格区域或数组常量。
　 如果数组只包含一行或一列, 则相应的 row_num 或 column_num 参数是可选的。
　 如果数组具有多行和多列，并且仅使用 row_num 或 column_num, 则 INDEX 返回数组中整个行或列的数组。
row_num 必需。选择数组中的某行，函数从该行返回数值。如果省略 row_num, 则需要 column_num。
column_num 可选。选择数组中的某列，函数从该列返回数值。如果省略 column_num, 则需要 row_num。

2、SMALL函数

函数形式如下：
　SMALL(array, k)
该函数返回数据集中的第 k 个最小值。参数说明如下：
array：需要找到第 k 个最小值的数组或数值数据区域。
k：要返回的数据在数组或数据区域里的位置（从小到大），需大于等于1小于等于数组元素的个数。
如A1～A9的值分别为9～1，那么SMALL(A1:A9, 4)返回值为4；SMALL(A1:A9, 9)返回值为9。

3、IF函数

该函数在 用Excel帮简小哥在聊天记录中自动统计数据解读（一） 已经介绍过就不再重复。

4、ROW函数

函数形式如下：
　ROW([reference])
参数说明如下：
reference 可选。 需要得到其行号的单元格或单元格区域。
　如果省略 reference，则假定是对函数 ROW 所在单元格的引用。
　如果 reference 为一个单元格区域，并且 ROW 作为垂直数组输入，则 ROW 将以垂直数组的形式返回 reference 的行号。

为进一步理解INDEX-SMALL-IF-ROW组合，在这里介绍两种形式：
1、结果放在行的写法：
　INDEX(查询结果列, SMALL(IF(条件, ROW(条件列), 4^8), COLUMN(A1)))&""
2、结果放在列的写法：
　INDEX(查询结果列, SMALL(IF(条件, ROW(条件列), 4^8), ROW(A1)))&""

这里用到的是第二种形式，对前述实际公式进行详细解读：

1、结果查询列为“A:A”，表示A列所有数据，也就是经处理后的日更和点评信息。

2、条件为$A$2:$A$5000<>0，这里为什么是5000，是考虑到最大数据可能到5000行，实际没这么大时，根据实际设置，设置5000行会导致表格更新速度较慢。前面已经说过，空行的实际值为0，这里就是筛选非空行。"$"表示绝对引用，复制公式时地址不会跟着发生变化。

3、ROW($A$2:$A$5000)，这是IF函数条件为真时，返回结果列的行号。

4、4^8表示4的8次方等于65536，表示IF函数条件为假时，返回的行号为65536，实际上比需要的数大即可，比如这里5001即可。取4^8这么大是为保险起见。

5、&""表示INDEX返回值与空字符（""）合并。筛选公式就是利用这个特性，在条件筛选返回结果的公式中使用它来代替不满足条件的公式错误值，也就是说INDEX(…)&""相当于IFERROR(INDEX(…),"")。

前面的公式是一个数组公式，需要同时按CTRL+SHIFT+ENTER三键结束，然后拖拉或复制将经过粗加工的数据汇总到一起，也就是剔除了空行，汇总之后的资料清爽整齐，为后续工作提供方便。

聊天记录粗加工汇总

（附表格下载链接：https://yunpan.360.cn/surl_y3ajs3WfsgT ，提取码：2042）