|
EXCEL的函数十分丰富，用函数可以解决数值自动排名。方法如下。 如第一行为表头，A列(例如A2:A101，下同)为姓名，B列数据，在C2单元格输入公式“=IF(A2=0,0,INT(CONCATENATE(INT(B2),200-ROW(A1))))”。公式中ROW(A1)为A1单元格所在的行数即为1，(该公式下拉时依次为2、3、4.....),用200来减是为了CONCATENATE函数中的第2个参数保持3位数，CONCATENATE函数是一个拼合函数这里把B列的数据和它所在的行数拼合成一个数据。这样在对它进行排序后该数据包含了它所在行数的信息。CONCATENATE函数INT函数套用是为了把原来的文本变为数字。 在D2单元格输入公式“=LARGE(C:C,ROW(A1))”即对B列数值(包含所在行的信息)按大小排列。 在F2单元格(为了与原始清单分开中间空了一列)输入公式“=IF(D2=0,0,200-RIGHT(D2,3))”，函数 RIGHT(D2,3)即为D2单元格数据的后3位数，用200来减即为此数据所在的行数。 在G2单元格输入公式“=IF($F2=0,0,INDEX($A$2:$B$15,$F2,COLUMN(A1)))”，并拖到H2单元格。INDEX函数为引用函数，即根据F2单元格所标明的行数在$A$2:$B$15单元格矩阵中引用姓名及得分。 在I2单元格输入公式“=IF(H2=0,0,IF(H2=H1,I1,ROW(A1)))”，本来G、H列就是按得分大小排列的，但可能有平列名次，所以选用上述公式。 最后把C2到I2单元格的公式下拉，程序就完成了。
用Excel函数排序与筛选 Execl本身具有很方便的排序与筛选功能，下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足，首先无论排序或筛选都改变了原清单的原貌，特别是清单的数据从其它工作表链接来而源数据发生变化时，或清单录入新记录时必须从新进行排序或筛选。其次还有局限，例如排序只能最多对三个关键字(三列数据)排序，筛选对同一列数据可用“与”、或“或”条件筛选，但对不同列数据只能用“与”条件筛选。例如对某张职工花名册工作簿，要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的，如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁，男的年龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用，即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单，其中有的职工已经退休，对在职职工的年龄进行排序时无法剔除已退休职工的数据。 下面介绍用Execl的函数来解决上述问题的方法。 一、用函数实现排序 题目：如有一张工资表，A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。现要求对职工收入从多到少排序，且在职工总收入相同时再按工资从多到少排序，在职工总收入和工资相同时再按奖金从多到少排序，在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。 解决方法： 第一步在G1单元格填入公式 第二步把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), 题目：如有一张职工名册表，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)), 第二步在K1单元格输文字”筛选选择”，A1到F1表头复制到L1到Q1，在L2单元格输入 第三步在P1单元格输入1或2或3便可实现上述三种筛选。 |
