用Excel函数实现排序与筛选

一、用函数实现排序

题目

如有一张工资表，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个以下的单元的数据拼合成一个数据，这些被拼合的数据之间用逗号分开。用f2、e2等被拼合的数据用999来减，是为了使它们位数相同。(假定任何一个职工的总收入少于899元)。被拼合成的函数是文本函数，CONCATENATE与INT函数套用是为了使文本转换为数字。最外层的if函数是排序时用来剔除不进行排序的记录，在本例中指收入为零的记录。(在上文提到的职工年龄排序，则公式改为“if(f2="退休",10^100,.....)”,即剔除了退休职工。)

第二步把G1单元格的公式拖放到G500单元格(最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标，见到黑十时双击鼠标就完成了G1到G500的填充)。

第三步在在H2单元填入公式“=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(SMALL(G:G,ROW(A1)),G:G,0)即为G列各行的数据中最小、第2、第3小等的数据在第几行。

第四步把A1至F1单元格的表头复制到I1至N1单元格，在I2单元格输入公式“=INDEX($A$2:$F$501,$H2,COLUMN(A$1))”INDEX函数是一个引用函数，即把$A$2:$F$501单元格列阵第$H2行第COLUMN(A$1)列的数据放入I2单元格。然后把I2单元格的公式拖放到N2单元格，点击N2单元格后向N2单元格右下方移动鼠标见到黑十时双击鼠标就完成了I2到N501单元格的填充到此全部完成。

以上叙述看似繁杂实际非常简单，只要把A1至F1的表头复制到I1至N1单元格，再分别在G1、H2、I2单元格输入公式然后向下拖放，即使对EXCEL应用不熟练的同志一分锺内便能完成。

对上述程序稍作变化还可得到更多用度。上面例子数据是从大到小排列的，如H列的函数中的SMALL改为LARGE，上面例子数据就从小到大排列了。如H2单元格的公式改为“=IF(O1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(LARGE(G:G,ROW(A1)),G:G,0))”

并把H2单元格的公式向下拖放。这样在O1单元格输入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(原来的O1单元格)单元格内容的改变就能立即得到按不同要求的排序。

二、用函数实现筛选

题目

如有一张职工名册表，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(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,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便可实现上述三种筛选。