Excel中函数的应用实例

1.用Excel函数自动排名次

　　笔者认为无论对原数据清单进行排序或筛选最好不要破坏原清单的原貌。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单元格的公式下拉，程序就完成了。

 2.重名检索与姓氏频率统计

　　人数较多(例如500人左右)的机关、团体、单位的人事管理部门，或者户籍管理部门，都会遇到重名的问题。例如笔者所在单位783人就有12人6对重名。在用EXCEL电子表格制作各类管理文件时重名会带来很多问题(例如以姓名作参数用VLOOKUP函数，来查找该人的信息时就会出错)。因此有一个方便快速的重名检索办法就十分必要。(笔者根据经验建议凡用EXCEL电子表格进行办公业务自动化管理的单位，应给每个人设立一个代码，像居民身份证号码一样是终身的唯一的，不要把调离、退休等人员的代码用于新增人员)。

　　方法如下：

　　先制作空表格：把本文所附的只有2行的表头打开，下拉菜单“编辑”、点击“定位”在引用位置栏输入“B2:H1001”、按“确定”、再下拉菜单“编辑”、点击“填充”、“向下填充”空表格制作完成。(这是大量填充单元格的最快方法)。然后把姓名清单从A2单元格开始拷贝至A列。这样检索程序操作就完成了。用该检索程序，在奔III733机器上1秒钟内便完成了783人的重名检索。

　　下面简单介绍B2至H2单元格的公式，B2单元格“=IF(A2=0,0,SUBSTITUTE(A2,"",""))”中SUBSTITUTE函数是去掉A2单元格中的名字的前、后、中间的空格，C2单元格“=IF(B2=0,0,IF(ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE))=TRUE,0,ROW(A1)))”中ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE))=TRUE,0,ROW(A1))即如在B3到B1001单元格中找不到与B2相同的姓名时为零，否则为从第1个姓名开始计数的行数。意即B3往下有重名时标明行数，否则为零。D2单元格“=LARGE(C:C,ROW(A1))”就是把重名所在行的行数从大到小进行排列。E2单元格“=IF(D2=0,0,INDEX(B$2:B$1001,D2))”就是在B列根据D2单元格标明的行数查找重名的姓名。F2单元格“=IF(E2=0,0,IF(ISERROR(VLOOKUP(E2,E3:E$1001,1,FALSE))=TRUE,ROW(A1),0))”与C2单元格的公式相似，只是根据条件取舍相反。即让已检出的重名只出现一次。G2单元格的公式“=IF(ROW(A1)>COUNTIF(F:F,">0"),0,INDEX(E$2:E$1001,LARGE(F:F,ROW(A1))))”就是对F列标明的行数，按大到小进行排列并在E列查找重名的姓名。H2单元格的公式“=IF(G2=0,0,COUNTIF(B:B,G2))”就是对B列在G列列出的重名进行计数。下表为工作表的前三行。

 

　　此程序稍作改变便能用来统计姓氏的频数与频率。

　　方法如下：

　　先制作空表格：把本文所附的只有4行的表头打开，用上述方法填充B4:l1002单元格.再把姓名清单从A3单元格开始拷贝至A列。这样姓氏的频数与频率统计程序操作就完成了。下表为工作表的前五行。

 　　用该程序对笔者所在单位783人统计有160个姓氏，张姓最多有95人出现频率为12.1%。样本太少不具全国姓氏的频数与频率统计上的意义，但似乎张姓为中国第一大姓。B到F列的公式与重名检索工作表的公式极相似，G到K列的公式在笔者的“排序与筛选”一文中有详细说明。

　　笔者在奔III733计算机上制作一张统计10000人姓氏频数与频率的空表需时6分37秒，复制这样一张空表瞬时就能完成，在空表上填充10000人的姓名后统计姓氏频数与频率的时间为2分42秒。填充完后文件大小为4996k。笔者所以测试以上时间是笔者有一个强烈的愿望：把程序用于全国千分之一到万分之一抽样人口即12万到120万人的姓氏频数与频率的统计。笔者在此请求网友支持，提供你能到的某一群体人员的姓氏或姓名样本，和所在省市。笔者每收集到1万个样本便在网站公布一次姓氏频数与频率的统计结果。

 3.用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单元格填入公式

“=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便可实现上述三种筛选。

 

　　2000年中国甲A联赛程序由4个工资簿组成。

 

　　第1个工作簿足球2001.xls由9张工作表组成。每个球队有一个代码，在代码表上可查到。本工作簿只需每轮比赛后在比分表上录入各球队间的比分，其他各表的统计数据都是自动生成的。在各轮排名表、主客场统计表、胜负表和队间比表上还有用红色底色标明的可选项，在这些单元格可输入你所关注的球队的代码来查询有关该球队的统计数据。

 

　　第2个工作簿足球2002.xls是用来统计计算各球队及队员进球及进球时间。本工作表需在每轮比赛后在C列录入轮次、D列录入球队代码、E列录入队员号码、某队获得乌龙球时其球员号填入40，I、J列分别录入所进球在上下半场的时间。这些数据在每轮赛后由中国足协网站www.fa.org.com发布。录入上述数据后便可通过射手榜、总射手榜、进球时间分布和乌龙球等4张表查阅各类统计数据。由于作者录入的数据与中国足协发布的统计资料可能有差别，因此本工作簿只供球迷参考。

 

　　第3个工作簿足球2003.xls是对下一轮各对阵球队的比分进行预测，前5轮是根据该两队上一年主客场的得分能力、第6轮开始根据前5轮主客场的得分能力来预测，没有考虑其他各类因素因此准确率不高，作者用此程序对98－2000年预测的比分准确率都为14％，胜平负预测准确率为45－50％之间。

 

　　第4个工作簿足球2004.xls是用来统计计算各球队及队员红黄牌数、处罚和停赛场次。本工作表需在每轮比赛后在C列录入轮次、D列录入球队代码、E列录入队员号码、I、J、K、L列分别录入所得黄牌、黄红牌、红牌及被处罚定赛场数。这些数据在每轮赛后由中国足协网站www.fa.org.com发布。录入上述数据后便可通过各队处罚汇总及停赛名单与场次两工作表查阅处罚与停赛的统计资料。由于作者对中国足协竞赛规则的理解不尽准确与中国足协发布的统计资料可能有差别，因此本工作簿只供球迷参考。