用Excel函数进行多个字段排序与筛选

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

2、的也是可行的。 但要求筛选出 女的年龄在22岁到45岁，男的年龄在25岁到50岁时Execl本身具 有的筛选功能则无能为力了。 再者排序与筛选不能结合使用， 即不能 在排序时根据条件筛选出来的记录进行排序。 例如有一张职工资料清 单，其中有的职工已经退休， 对在职职工的年龄进行排序时无法剔除 已退休职工的数据。本文试图用Execl的函数来解决上述问题。一、用函数实现排序题目如有一张工资表，A2:F501共6列500行3000个单元格。表头A1为 姓名代码（1至500）、B1为姓名、C1为津贴、D1为奖金、E1为工资、 F1 收入合计。现要求对职工收入从多到少排序，且在职工总收入相 同时再按工资

3、从多到少排序， 在职工总收入和工资相同时再按奖金从 多到少排序， 在职工职工总收入和工资、 奖金相同时再按津贴从多到 少排序。方法G1 单元格填入公式“二if（F2=0,10八100,INT（CONCATENATE（999-f2,999-e2,999-d2,999-C”） CONCATENATE个拼合函数，可以把30个以下的单元的数据拼合 成一个数据，这些被拼合的数据之间用逗号分开。用f2、 e2 等被拼合的数据用 999 来减，是为了使它们位数相同。（假定任何一个职工的总收入少于 899 元）。被拼合成的函数是文本函数，CONCATENATE与INT函数套用是为了使文本转换为数字。 最外层的

4、if函数是排序时用来剔除不进行排序的记录，在本例中指收入为零的记录。(在上文提到的职工年龄排序，则公式改为“ if(f2二"退休",10八100,.)”，即剔 除了退休职工。 )第二步把 G1 单元格的公式拖放到 G50 0单元格 (最简便的方法是点击 G1 单元格后向 G1 单元格右下方移动鼠标， 见到黑十时双击鼠标就完 成了 G1到G500的填充)。第三步在在 H2 单元填入公式“二MATCH(SMALL(G:G,ROW(A1),G：G0) 与第二步一样拖放到 H501 单元格。此公式实际上是把三列公式合成 一列公式，ROW(A1即为A1的行数是1,随着向下拖放依次为2

5、、3、 4,SMALL(G:G,ROW(A1为G列中最小的数随着向下拖放依次为第 2、 第 3、.小的数，MATCH(SMALL(G:G,ROW(A1),G:G即为 G 列各行的 数据中最小、第 2、第 3小等的数据在第几行。第四步把A1至F1单元格的表头复制到11至N1单元格，在I2单元 格输入公式“ =INDEX($A$2:$F$501,$H2,COLUMN(A$1)NDEX 函数是 一个引用函数，即把$A$2:$F$501单元格列阵第$H2行第COLUMN(A$1) 列的数据放入I2单元格。然后把I2单元格的公式拖放到N2单元格， 点击 N2 单元格后向 N2 单元格右下方移动鼠标见到黑

6、十时双击鼠标 就完成了 I2到N501单元格的填充到此全部完成。以上叙述看似繁杂实际非常简单,只要把 A1 至 F1 的表头复制到 I1 至N1单元格，再分别在G1、H2、12单元格输入公式然后向下拖放， 即使对EXCELS用不熟练的同志一分锺内便能完成。对上述程序稍作变化还可得到更多用度。 上面例子数据是从大到小排列的，如H列的函数中的SMALL改为LARGE上面例子数据就从小 到 大 排 列 了 。 如 H2 单 元 格 的 公 式 改 为“ =IF(O1=1,MATCH(SMALL(G:G,ROW(A1),G:G,0),MATCH(LARGE(G:G,RO W(A1),G:G,0)”并把

7、H2单元格的公式向下拖放。这样在 01单元格输入1上面例子 数据是从大到小排列的, O1 单元格输入 1 以外的数上面例子数据就 从小到大排列了。如在 H 列前插入若干列,如插入一列,则现在的 H 列输入类似 G 列 的公式,例如“二if(F2=0,10八100,d2) ”,现在的I列的公 式改为“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1),G:G,0),MATCH(SMALL(H:H,ROW(A1),H:H,0) ”即在P单元格输入1以外的值就实现了按奖金大小排序这样只要通 过改变P1(原来的01单元格)单元格内容的改变就能立即得到按不同 要求的排序。二、用函数实现筛选

8、 题目如有一张职工名册表，A2:F501共6列500行3000个单元格。表头 A1为姓名代码(1至500)、B1为姓名、C1为性别、D1为年龄、E1为 学历、F1职称。现要求对职工的性别、年龄、学历、职称进行交错 筛选，例如要求在同一张表上筛选出 1、女的年龄在 22岁到 45岁， 男的年龄在 25岁到 50岁，2、女博士， 3、男博士后。方法 第一步在 G2 单元格输入公式” =IF(OR(AND(C2=，女 ",D2>=22,D2<=45),AND(C2=",D2>=25,D2<=50),ROW(A1),0),在 H2 单元格输入公式” =IF(

9、AND(C2二" 女",E2='博士")，ROW(B1),0)“，在I2单元格输入公式” =IF(AND(C2二男",E2=，博士后 "),R0W(B1),0)“。 在 J2 单 元 格 输 入 公 式“=IF(K$2=1,LARGE(G:G,R0W(A1),IF(K$2=2,LARGE(H:H,R0W(A1), IF(K$2=3,LARGE(I:I,ROW(A1),0)然后用上述提到的方法向下拖放。 G、H、I 列的公式的含义就是凡符合筛选条件的行记录下行号否则为 零，J列的公式的含义根据K2的数值选择G、H、I中的一列进行排序 并把不合条件的行除去。第二步在K1单元格输文字