EXECL的排序功能应用.docx

《EXECL的排序功能应用.docx》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、Execl本身具有很方便的排序与筛选功能，下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足，首先无论排序或筛选都改变了原清单的原貌，特别是清单的数据从其它工作表链接来而源数据发生变化时，或清单录入新记录时必须从新进行排序或筛选。其次还有局限，例如排序只能最多对三个关键字(三列数据)排序，筛选对同一列数据可用“与”、或“或”条件筛选，但对不同列数据只能用“与”条件筛选。　　例如对某张职工花名册工作簿，要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的，如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁，男的年

2、龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用，即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单，其中有的职工已经退休，对在职职工的年龄进行排序时无法剔除已退休职工的数据。　　本文试图用Execl的函数来解决上述问题。二、用函数实现筛选　　题目：如有一张职工名册表，A2:F501，共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为性别、D1为年龄、E1为学历、F1职称。现要求对职工的性别、年龄、学历、职称进行交错筛选，例如要求在同一张表上筛选出1、女的年龄在22岁到45岁，男的年龄

3、在25岁到50岁，2、女博士，3、男博士后。　　方法：第一步在G2单元格输入公式”=IF(OR(AND(C2="女"，D2>=22，D2<=45)，AND(C2="男"，　　D2>=25，D2<=50))，ROW(A1)，0)“，在H2单元格输入公式”=IF(AND(C2="女"，E2="博士")，　　ROW(B1)，0)“，在I2单元格输入公式”=IF(AND(C2="男"，E2="博士后")，ROW(B1)，0)“。在J2单元格输入公式“=IF(K$2=1，LARGE(G:G，ROW(A1))，IF(K$2=2，LARGE(H:H，ROW(A1))，　　IF(K$2=3，

4、LARGE(I:I，ROW(A1))，0)))”然后用上述提到的方法向下拖放。G、H、I列的公式的含义就是凡符合筛选条件的行记录下行号否则为零，J列的公式的含义根据K2的数值选择G、H、I中的一列进行排序并把不合条件的行除去。　　第二步在K1单元格输文字”筛选选择”，A1到F1表头复制到L1到Q1，在L2单元格输入　　公式“=IF($J2=0，0，INDEX($A$2:$F$501，$J2，COLUMN(A$1)))”，然后向右拖放到Q2，再向下拖放。INDEX函数的含义上文已说明。　　第三步在P1单元格输入1或2或3便可实现上述三种筛选。　　一、用函数实现排序　　题目：如有

5、一张工资表，A2:F501，共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。现要求对职工收入从多到少排序，且在职工总收入相同时再按工资从多到少排序，在职工总收入和工资相同时再按奖金从多到少排序，在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。　　方法：G1单元格填入公式　　“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，　　CONCATENATE是一个拼合函数，可以把30个以下的单元的数据拼合成一个数据，这些

6、被拼合的数据之间用逗号分开。用f2、e2等被拼合的数据用999来减，是为了使它们位数相同。(假定任何一个职工的总收入少于899元)。被拼合成的函数是文本函数，CONCATENATE与INT函数套用是为了使文本转换为数字。最外层的if函数是排序时用来剔除不进行排序的记录，在本例中指收入为零的记录。(在上文提到的职工年龄排序，则公式改为“if(f2="退休"，10^100，.....)”，即剔除了退休职工。)　　第二步把G1单元格的公式拖放到G500单元格(最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标，见到黑十时双击鼠标就完成了G1到G500的填充)。　　第三步在在H

7、2单元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”与第二步一样拖放到H501单元格。此公式实际上是把三列公式合成一列公式，ROW(A1)即为A1的行数是1，随着向下拖放依次为2、3、4...，SMALL(G:G，ROW(A1))为G列中最小的数随着向下拖放依次为第2、第3、..小的数，MATCH(Excel排序非常方便，在排名次时用起来特别顺手。笔者现总结利用Execl排名时的四种操作方法:把成绩录入完后，使用“自动求和”功能计算出每个人的总分，并单击“数据”→排序，以“