对数据进行分组的方法

Excel: 使用函数将数据分组

作者：oldshu 出自：似水流年浏览/评论：4,794/0 日期：2009年11月5日 16:26

——函数ROW()、INT()和CHAR()的综合运用——

【注：本文只是本人的一个尝试和探讨，使用时请先多做测试，如有错误，请及时纠正。】

Excel对数据的排序和分类汇总的自动处理功能相当强，然而在实际工作过程中，有时需要对一组排完序的数据，先按一定的数量要求将这些数据分组，比如3个一组，还是10个一组，或者是32个一组，在分组之后再汇总。在目前关注民生的数据统计中，表达贫富不均程度的洛仑兹曲线的绘制和基尼系数的计算，就必须使用这个方法。我在《用Excel表达贫富不均——洛仑兹曲线的绘制及基尼系数的定积分计算》（地址见【附录1】）一文中，就是对这样分组的数据再进行分类汇总求和，再将各组总和逐级累加，然后再进行后续处理。对其它关于大量数据分布的集中度的分析时，有时也要使用这一工具。

这就对我们提出了一个问题：能不能利用Excel自动分组？Excel自带的“数据”操作暂时还不带这一功能，使用VBA编程当然可以处理这一问题，但对未学过者还得从启蒙开始，未免强人所难。其实杀鸡不需牛刀，完全可以简单地使用Excel中的几个函数公式综合解决。

这一处理的主要思路是：先确定组内数据个数，再将数据纵向放置在某一列（可从第二行开始，第一行放置标题“组别”和“数据”）。然后利用行号函数ROW()取得行号，按预先指定的个数对行号分组（除法、再使用INT()対商数取整），再按取整后的商数按CHAR()函数，返回数字指定的字符，达到分组目的以按升序排列的12个数据按3个一组分组为例，如下表：

表1

将上表的部分单元格内的公式显示如下：

表2

先在F4中确定每组数据个数，之后所有公式引用此单元格数据时，一定要使用绝对位置$F$4,而不能是相对位置F4。

函数ROW()，就是取括号内相应单元格的行号。比如A2=ROW(C2)，就是单元格A2取C列相应单元格C2的行号。本例设定A列的公式，只是为了说明这个公式和按行号分组的对应的关系，其实就是个摆设，实际操作中并不需要。因为B列的公式已经把这一行号公式都包含在内了

函数INT（）是取整函数。(INT((ROW(C2)-2)/($F$4))，是将C2的行号减2后除以$F$4（现在是按3个一组分组，（$F$4）=3，然后对除后的商取整。

为什么是被除数行号减2之后再除呢？这是因为标题占了第一行，行号减2无非是为了处理思路方便，将实际行号向上后退2行，以保障从第0行开始计算,前三行落在第0组,而第四行则分在第1组。也就是处理分组临界点的问题。

【上述的：被除数选(ROW(C2)-2)，以及与INT（）综合成(INT((ROW(C2)-2)/($F$4))，只是我本人的一个尝试与探讨，可以尝试用小学四年级以下的知识作一个探讨，看看是不是对。】

以上的方法已经解决了数据分组的问题：依次每三个数据一组，每组的取整后的数字是0、1、2、3，……。至此，其实分组已毕，问题已经解决了，即第0组，第1组、第2组、第3组，……，等等。只是习惯上总想冠以一个名称，那就给予各组以一个个最简单的英文字符，这就让我们想起函数CHAR() 。

函数CHAR()是返回数字指定的字符，在计算机代码制定时规定CHAR(65)="A"，CHAR(66)="B"，……依此类推（见【附录2】）。CHAR((INT((ROW(C2)-2)/($F$4))+65)) 就可以将每组的取整后的数字0、1、2、3，……依次转化为英文大写字母A、B、C、D，……从而达到分组的目的。

在设定B1公式后，将B1在该列拖曳到你所要分类的所有数据的最后一行，所有分类就完成了。

不过需要说明的是表2，只是为了说明问题，表2才将公式显示出来，实际处理时必须将公式隐藏，如表1一样，显示数据才行。如果你的数据表是表2形式，一定要在“工具”下拉菜单中选择“选项”，如表3所示：

表3

在“选项”选项卡的“视图”选项中，将“公式”的勾取消。如表4所示（现在是打勾的——要点击一下这个复选框，将这个勾去掉）：

表4

以上公式比较简单，即使是几百个数据，一拖曳,瞬间就可完成。如果是数据表较多，或者是经常性地处理这一工作，则可使用VBA编程解决，当然后续的汇总、累加、绘图之类的事也可一并解决。不过本文只就公式自动分类而言，其他就不再拓展了。

【题外的话】：关于样本容量与分组

需要说明的是，类似于洛仑兹曲线的绘制和基尼系数统计时的数据，分组可以按总数的10%为一组，但是有时在统计中取得的数据总量不见得一定是10的倍数，所以，有时按10%分组，不一定能除尽。不过，这并不要紧，这是因为：

第一.可以使用重复采样方法使得数据能够满足10%分组的要求；

第二.关于10%分组的说法，只不过是常规，你完全可以根据实际情况，多少个一组计算比较方便，就按这个标准分组，不一定拘泥于10%的说法。不过那时候，你做洛仑兹曲线的那条绝对平均线的对角线也应该按此标准绘制；

第三.在洛仑兹曲线的绘制和基尼系数的计算时，在分组后还要做汇总求和、对分组总和再做逐次累加，我们使用的是这个累加值。这样，每一组的数据数量是否平均，并不重要，各组的样本容量的微小差异(前后两组只差1个)，不会影响最后的结果。也就是说此类计算，对各组的样本容量的依赖性较低第四.由于上述例子中的$F$4的取值都是整数，其实该取值不一定是整数，也照样分组，只不过会有许多组内的样本容量上下会相差1个，而由于上述第三条原因，不会影响最终结果；

第五．由于此类计算是用来表达数据集中分散程度，比如贫富不均、两极分化大小的程度，因此对两极的极端值的样本，不宜像大奖赛打分一样，去掉几个最高分和最低分。因为很可能这几个极端样本恰恰最起作用。比如《环球日报》2009年10月27日文章说：“根据联合国开发组织的报告，在全球27个发达经济体当中，香港的贫富悬殊情况最严重，基尼系数达到43.4，在全球排名第一。报告称香港最富有的10%的人口拥有34. 9%的财富，但最穷的10%的人口只占有2%，两者差距17.8倍。”香港《头条日报》则分析说，“香港富豪多，其中一个重要原因是香港2003年推出了投资移民计划，吸引超过4300人获批来港定居。其中不少为内地超级富豪，身家数以亿计。”这样的两端的极端数据还是保留为好。在第一条提及重采样时，也要注意这个问题。