对数据进行分组的方法.doc

《对数据进行分组的方法.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、Excel:使用函数将数据分组作者：oldshu 出自：似水流年浏览/评论：4,794/0 日期：2009年11月5日16:26——函数ROW()、INT()和CHAR()的综合运用——【注：本文只是本人的一个尝试和探讨，使用时请先多做测试，如有错误，请及时纠正。】Excel对数据的排序和分类汇总的自动处理功能相当强，然而在实际工作过程中，有时需要对一组排完序的数据，先按一定的数量要求将这些数据分组，比如3个一组，还是10个一组，或者是32个一组，在分组之后再汇总。在目前关注民生的数据统计中，表达贫富不均程度的洛仑兹曲线的绘制和基尼系数的计算，就必须使用这个方法。我在《用Exc

2、el表达贫富不均——洛仑兹曲线的绘制及基尼系数的定积分计算》（地址见【附录1】）一文中，就是对这样分组的数据再进行分类汇总求和，再将各组总和逐级累加，然后再进行后续处理。对其它关于大量数据分布的集中度的分析时，有时也要使用这一工具。这就对我们提出了一个问题：能不能利用Excel自动分组？Excel自带的“数据”操作暂时还不带这一功能，使用VBA编程当然可以处理这一问题，但对未学过者还得从启蒙开始，未免强人所难。其实杀鸡不需牛刀，完全可以简单地使用Excel中的几个函数公式综合解决。这一处理的主要思路是：先确定组内数据个数，再将数据纵向放置在某一列（可从第二行开始，第一行放置标题

3、“组别”和“数据”）。然后利用行号函数ROW()取得行号，按预先指定的个数对行号分组（除法、再使用INT()対商数取整），再按取整后的商数按CHAR()函数，返回数字指定的字符，达到分组目的以按升序排列的12个数据按3个一组分组为例，如下表：表1将上表的部分单元格内的公式显示如下：表2先在F4中确定每组数据个数，之后所有公式引用此单元格数据时，一定要使用绝对位置$F$4,而不能是相对位置F4。函数ROW()，就是取括号内相应单元格的行号。比如A2=ROW(C2)，就是单元格A2取C列相应单元格C2的行号。本例设定A列的公式，只是为了说明这个公式和按行号分组的对应的关系，其实就是

4、个摆设，实际操作中并不需要。因为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))，只是我本人的一个尝试与

5、探讨，可以尝试用小学四年级以下的知识作一个探讨，看看是不是对。】以上的方法已经解决了数据分组的问题：依次每三个数据一组，每组的取整后的数字是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

6、、2、3，……依次转化为英文大写字母A、B、C、D，……从而达到分组的目的。在设定B1公式后，将B1在该列拖曳到你所要分类的所有数据的最后一行，所有分类就完成了。不过需要说明的是表2，只是为了说明问题，表2才将公式显示出来，实际处理时必须将公式隐藏，如表1一样，显示数据才行。如果你的数据表是表2形式，一定要在“工具”下拉菜单中选择“选项”，如表3所示：表3在“选项”选项卡的“视图”选项中，将“公式”的勾取消。如表4所示（现在是打勾的——要点击一下这个复选框，将这个勾去掉）：表4以上公式比较简单，即使是几百个数据，一拖曳,瞬间就可完成。如果是数据表较多，或者是经常性地处理这一工作

7、，则可使用VBA编程解决，当然后续的汇总、累加、绘图之类的事也可一并解决。不过本文只就公式自动分类而言，其他就不再拓展了。【题外的话】：关于样本容量与分组需要说明的是，类似于洛仑兹曲线的绘制和基尼系数统计时的数据，分组可以按总数的10%为一组，但是有时在统计中取得的数据总量不见得一定是10的倍数，所以，有时按10%分组，不一定能除尽。不过，这并不要紧，这是因为：第一.可以使用重复采样方法使得数据能够满足10%分组的要求；第二.关于10%分组的说法，只不过是常规，你完全可以根据实际情况，多少个