《excel函数排序与筛选选》由会员分享,可在线阅读,更多相关《excel函数排序与筛选选(4页珍藏版)》请在金锄头文库上搜索。
1、ExcelExcel 函数排序与筛选选函数排序与筛选选实例教你学 Excel 函数排序与筛选Execl 本身具有很方便的排序与筛选功能,下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足,首先无论排序或筛选都改变了原清单的原貌,特别是清单的数据从其它工作表链接来而源数据发生变化时,或清单录入新记录时必须从新进行排序或筛选。其次还有局限,例如排序只能最多对三个关键字(三列数据)排序,筛选对同一列数据可用“与” 、或“或”条件筛选,但对不同列数据只能用“与”条件筛选。例如对某张职工花名册工作簿,要求筛选出年龄大于 25 岁且小于 50 岁或年龄大于 50 岁或小于 25 岁都是
2、可行的,如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在 22 岁到 45 岁,男的年龄在 25 岁到 50 岁时 Execl 本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用,即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单,其中有的职工已经退休,对在职职工的年龄进行排序时无法剔除已退休职工的数据。本文试图用 Execl 的函数来解决上述问题。一、用函数实现排序题目:如有一张工资表,A2F501,共 6 列 500 行 3000 个单元格。表头 A1 为姓名代码(1 至 500)、B1 为姓名、C1 为津贴、D1 为奖金、E1 为工资、F1 收入合计
3、。现要求对职工收入从多到少排序,且在职工总收入相同时再按工资从多到少排序,在职工总收入和工资相同时再按奖金从多到少排序,在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。方法:G1 单元格填入公式“=if(F2=0,10100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)” ,CONCATENATE 是一个拼合函数,可以把 30 个以下的单元的数据拼合成一个数据,这些被拼合的数据之间用逗号分开。用 f2、e2 等被拼合的数据用 999 来减,是为了使它们位数相同。(假定任何一个职工的总收入少于 899 元)。被拼合成的函数是文本函数,CONCA
4、TENATE 与 INT 函数套用是为了使文本转换为数字。最外层的if 函数是排序时用来剔除不进行排序的记录,在本例中指收入为零的记录。(在上文提到的职工年龄排序,则公式改为“if(f2=退休, 10100,.)” ,即剔除了退休职工。)第二步把 G1 单元格的公式拖放到 G500 单元格(最简便的方法是点击 G1 单元格后向 G1 单元格右下方移动鼠标,见到黑十时双击鼠标就完成了 G1 到 G500 的填充)。第三步在在 H2 单元填入公式“=MATCH(SMALL(GG,ROW(A1),GG,0)”与第二步一样拖放到 H501 单元格。此公式实际上是把三列公式合成一列公式,ROW(A1)即
5、为 A1 的行数是 1,随着向下拖放依次为 2、3、4.,SMALL(GG,ROW(A1)为 G 列中最小的数随着向下拖放依次为第 2、第 3、.小的数,MATCH(SMALL(GG,ROW(A1),GG,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 单元格的公式
6、拖放到 N2 单元格,点击 N2 单元格后向 N2 单元格右下方移动鼠标见到黑十时双击鼠标就完成了 I2 到 N501 单元格的填充到此全部完成。以上叙述看似繁杂实际非常简单,只要把 A1 至 F1 的表头复制到 I1 至 N1 单元格,再分别在 G1、H2、I2 单元格输入公式然后向下拖放,即使对 EXCEL 应用不熟练的同志一分锺内便能完成。对上述程序稍作变化还可得到更多用度。上面例子数据是从大到小排列的,如 H 列的函数中的 SMALL 改为 LARGE,上面例子数据就从小到大排列了。如 H2 单元格的公式改为“=IF(O1=1,MATCH(SMALL(GG,ROW(A1),GG,0),
7、MATCH(LARGE(G G,ROW(A1),GG,0)”并把 H2 单元格的公式向下拖放。这样在O1 单元格输入 1 上面例子数据是从大到小排列的,O1 单元格输入 1以外的数上面例子数据就从小到大排列了。如在 H 列前插入若干列,如插入一列,则现在的 H 列输入类似G 列的公式,例如“=if(F2=0,10100,d2)” ,现在的 I 列的公式改为 “=IF(P1=1,MATCH(SMALL(GG,ROW(A1),GG,0),MATCH(SMALL(HH,ROW(A1),HH,0)” 即在 P 单元格输入 1 以外的值就实现了按奖金大小排序.这样只要通过改变 P1(原来的 O1 单元格
8、)单元格内容的改变就能立即得到按不同要求的排序。二、用函数实现筛选题目:如有一张职工名册表,A2F501,共 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)“,在
9、 H2 单元格输入公式”=IF(AND(C2=女,E2=博士),ROW(B1),0)“,在 I2 单元格输入公式”=IF(AND(C2=男,E2=博士后),ROW(B1),0)“。在 J2 单元格输入公式“=IF(K$2=1,LARGE(GG,ROW(A1),IF(K$2=2,LARGE(HH,ROW(A1),IF(K$2=3,LARGE(II,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 便可实现上述三种筛选。