WPS专题之六-函数应用大宝典.pdf

WPS专题之六函数应用大宝典WPS表格是办公室自动化中非常重要的一款软件，方便地采集和处理数据,具有丰富灵活的计算功能，被广泛应用于管理、统计、财政、金融等众多办公 领域，如在人事管理、会计、统计处理方面WPS表格都是绝好的帮手，学好 ET的基础应用后，当进一步学习它的函数、图表和其它功能，然而很多人对 ET数据处理功能不了解，难以进一步深入，故而整理了这个函数应用的专题,力求向WPS fans们传达更多、更形象、更直观的信息，为大家学习函数提供 一个好的帮助：首先，我们来看看函数能为我们做什么？由出生日期判断属相论坛里用身份证号码提取性别、出生口期(年龄)、甚至出生地的方法都有介绍了。今天在论坛中 又有网友提问，如果已知一个人的出生日期，能否判断其属相？这个问题看似复杂，其实也很简单。只要灵活运用MID函数和MOD函数即可轻松解决。如图所示，要求根据C列的出生日期，自动在D列生成属相。ABCD1序号姓名出生日期属相21赵芳梅1973-5-18牛32刘琳1974-1-25虎43钱德芳1980-9-14假54李健1981-6-23鸡65陈伟民1979-3-2羊工6金晓玲1976-4-15龙87蔡明军1983-11-7猪98丁静1985-12-21牛109王恒1984-6-23鼠1110曹娜1977-8-12鹿1211商丽丽1982-5-22狗1312戴家俊1983-10-18猪我们知道，12生肖是按照“鼠牛虎兔龙蛇马羊猴鸡狗猪，的顺序循环的。通过计算得知，1900年应为“鼠 年”，其它以此类推。那么，我们只要算出出生年份与1900年的差，再用这个差除以12,并与“鼠年”相比 较，就可以准确判断该年出生的人的属相了。于是在D2单元格输入公式：=M1D(鼠牛虎兔龙蛇马羊猴鸡狗猪1MOD(YEAR(C2)-1900,12)+1,1)并回车，即可看到第一条记录，赵芳梅的出生年份是1973年，属相是“牛”。将该公式向下填充，即 可准确得出每个人的属相。公式解释:一、MOD函数函数功能：返回两数相除的余数。结果的正负号与除数相同。函数格式：MOD（数值，除数）例如：=MOD（25,4）”即表示用25除以4,求其余数。结果为1。二、MID函数函数功能：从文本字符串中指定的位置开始，返回指定长度的字符串。函数格式：MOD（字符串，开始位置，字符个数）例如：=MID（中华人民共和国”,2,3）”即表示取字符串“中华人民共和国”第2个字符开始，取3个。即“华人民”。巧用函数生成随机数字在进行概率测试时（如玩彩票的网友就会经常用到），我们会经常用到随机数。当需要大量的随机 数时，也许你会为如何获得这么多符合要求的随机数感到困惑。其实，用WPS表格2007个人版，中心 满足你对随意数的各种要求。一、生成0-1之间的随机数这个最容易，因为WPS表格内置了随意机生成函数一一rand函数，用法也很简单，只要在需要生 成随意数的单元格内输入“=rand（）“（不含外侧双引号，且所有字符均为半角，如图1）。WPS 表格-Bookl.et*图1输入完成后回车，该单元格即出现一个大于或等于0,并小于1随机小数。如果你需要很多这样的随机 数的话，可以选中该单元格，然后用鼠标左键在该单元格右下角的方形点（即填充柄）按下，并向下拖 动到合适的位置，则这一范围内所有的单元格均生成一个随机数，如果还要更多，则再选中已经生成随机 数的所有单元格，并在选区右下方如法向右拖动，则拖出的矩形区域内的所有单元格都会生成随机数，要多少有多少啊（图2）!WPS 表格-Bookl.et*a文件9 编辑CE）视图9 插入9 格式（Q）工具（T）数据也）窗口世）帮助也）以口易国|白41繁启外值 71 2宋体 2|beu|三善云回富，|璃 湍Bookl.et*D15 戊=RANDOABCDE10.546997070.4552001950.1226806640.23831176820.990417480.6409301760.0083923340.2617187530.7008972170.6672973630.4043884280.35604858440.4968261720.5325622560.1041259770.45700073250.5032043460.8885498050.2626342770.7871704160.4459533690.2145690920.3577270510.51562570.5641784670.8682556150.2910461435.17080688580.7055358890.7851867680.4729919430.60336303790.6194763180.6469116210.0344238280.646087646100.5984802250.1814270020.7056274410.92477417110.3651428220.8999633790.7767333980.955657959120.3679199220.578002930.5857543950.863922119130.8326110840.6527099610.7153625490.805419922140.0978698730.1282348630.4953002930.517272949150.9553833010.0832214360.033386231 0,727081299.161 7图2二、生成0-100之间的整数用rand函数生成的随机数范围是0-1之间的小数，如果我想要生成0-100之间的任意整数应该如何操 作呢？如果还要用rand函数的话，则必须借助另一个函数-round函数。round函数的格式为：ROUND（数值，小数位数），其功能是“按指定的位数对数值进行四舍五入后返回”。如“round（L2586,2）”表 示取1.2586小数点后2位有效数字，即返回值为“1.26”。有了 round函数的帮助，生成0-100之间的整数就不难了，可以在单元格中输入“=ROUND（RAND（）,2）*100（不含外侧双引号），即先用RAND函数生成一个0-1之间的随机小数，再取 该数值小数点后2位有效数字，然后乘以100,即可生成一个0-100之间的整数。不过，在WPS表格2007中，引入了一个特别的函数RANDBETWEEN,这个函数的格式是RANDBETWEEN（最小整数，最大整数），其功能是取最小整数和最大整数之间的随意整数。上面的公式可以改写成“=R ANDBET WEEN（0,100）”即可达至I相同的效果。三、随机数生成后不再变动用以上两个函数生成的随机数都有一个共同的特点，就是每当工作簿中有单元格的内容发生改变 时，随机数都会重新计算，也就是说，随机数随时都在变化之中，而不能固定下来。如果你希望这些随 机数一旦生成就不再变动，又该如何做呢？其实也很简单，就是在随机数生成后，把它们全部剪切到系 统剪贴板，并从“编辑”菜单中选择“选择性粘贴”，并在弹出的窗口中选择“数值”（见图3）。或者，在“常 用工具栏，中点击“粘贴工具，右侧的倒三角，并在下拉菜单中选择“值”（如图4）。这样，粘贴后的单元格 的值就不会再有变化了。选择性粘贴粘贴O全部 O边框除外篁）O公式g O列宽醯班|O公式和数字格式国）O卷短 值和数字格式）运算无）O乘）O加也）O除Q）O减6）跳过空单元）转置也）图3图4利用函数自动填写工资审批表今天一早，单位会计小王拿来一张表，请我帮他设计一个可以自动填写工资审批表的 电子表格，原表如图一。丈）舲津贴申扯表V，二表一工工色，W行址它.我.三代克二冬一市.W乞5.二二 s=5二二、二rr-xt 争vw菱七r 二丁一 二 二-&三玄B=三三工号.二氏左般F=同5 FSt：r：s C f接过表一看，这个表并不复杂，可通过以下步骤完成。一、建立表格。把这个表输入WPS表格，表格上方的标题及单位信息可以在页眉中输 入，按照表样设置好字体等。二、输入数据。姓名、性别、出生年月、学历、参加工作时间及调整前享受的教护龄 津贴标准等数据，可以直接调用单位人员档案复制粘贴，其中参加工作时间的格式为“yyyy-mm-dd”。如图二。三、利用函数，完善表格。1、实际工作年限：在F3单元格输入函数“=year(now()-year(E3)2、实际教护年限：在G3单元格中输入“=F3”。3、调整后标准：国家政策规定，教龄津贴补助的标准为：20年及以上10元，15 19年 7元，10-14年5元，5 9年为3元。因此，在13中输入“=IF(F3=20,10,IF(F3=15,7,IF(F3=10,5,IF(F3=5,3)M以自动算出调整后的标准。然后 在J3中输入“=I3-H3”自动算出月增资额。上述公式建立完毕，复制到下边的单元格中。4、审批单位意见：在合并后的K3单元格中输入“=IF(I30,CONCATENATE。根据黔工 改办(86)4号、7号文件精神，经审查，同意”,A3,”等”,COUNTA(A3:A12)J位同志享受教(护)龄津贴，月增津贴”,SUM(J13:J22)J元从二。六年元月一日起执行。如调离教师、护士岗 位后，取消教(护)津贴。CONCATENATE。根据黔工改办(86)4号、7号文件精神，经审查，同意“等“位同志享受教(护)龄津贴，月增资”元，从二。六年元月一日起执行。如调离教师、护士岗位后，取消教(护)津贴。)。至此，整个表格就制作完成了。如图_ O将此工作表中的人员信息等基本数据清空，保存为模板，今后本单位有增加教龄津贴 的人员时，审批表的填写就比较简单和轻松了。用WPS表格轻松设计工资条在任何企业之财务管理中,一定少不了工资计算和设计工资条。工资条头之美观及操作速度是设计的 一个重点。若能掌握技术则可事半功倍，否则将受限于大量的手工操用，效率低下、且准确性无法得到 保障。首先让我们看看工资条都有些什么特点，才能对症下药。工资条头可能一行，也可能两行，根据不同企业工资栏目需求而定。但相同处是每一项条目（或 者一个工人的工资信息）具有一个条头，条头具有指定数目的重复性；二：每一项条目中间有一个空行，方便裁剪。根据以上特点，我们可以初步拟定工资条头制作方案：首先：建立“工资明细表”，用于储存员工工资表信息，也用于平时编辑和汇总。其次：建立“工资条目表”，用于引用工资信息，同时产生工资条形式之工资目。需要用到的函数可以因人而异，有很多公式都可以达成目的，但最后选择目标是效率最高，同时又 易于理解者。现在，通过二个实例向大家详解工资条头设计之过程，分析其中技巧。望大家能从中受益，举一反 三，设计出适合各自需求之公式。一、单行工资条头之设计先看下面数据，这是一个简易的单行条头工资信息摘录(数据随机生成，非实际工资，您可以 url=http:/ 1。图1利用此表数据，可以通过函数公式来引用数据，生成需要的工资条形式。步骤与公式如下：1.进入“单行表头工资条”工作表。2.选中单元格B1。3.输入以下公式：=CHOOSE(MOD(ROW(),3)+1,单行表头工资明细！A$l,0FFSET(单行表头工资明 细!A$l,R0W()/3+l,)4.选中单元格Bl,鼠标置于单元格右下角，当箭头变成十字形时(见图2)图2则向右拉至J1单元格。然后再选中Bl：J1向下拉，直至公式结果出现空白。此时工资条效果见图3。间圆SfPS茂格2005 工笠枭收计方JLet5.基本达到目的了，但表格还没有边框，打印则不太美观。现在为它加上边框：选中Bl：J2,并点击工具栏边框按钮中的田字形按钮添加边框；再点击大方框按钮（见图4）,使工资表条目四周边框显示 粗一些，比较美观。6.选择Bl：J3单元格，向下拖拉，直至有数据的最后一行。最后效果见图5。图5公式解释:=CHOOSE(MOD(ROW(),3)+1,单行表头工资明细！A$1,OFFSET(单行表头工资明 细!A$l,ROW()/3+l,)LCHOOSE函数是选择函数，支持30个参数。第一个参数是选择序号(数值)，其余参数是被选值。第一个参数是N则结果返回其余参数中第N个数值。2.MOD函数是求余数函数，支持两个参数，第一个参数是被除数，第二个参数是除数，结果返回余 数。3.R0W函数是返回指定行行号之函数，若省略参数时则返回当前行。4.OFFSET函数是返回偏移量之函数。支持五个参数，分别是参照区域卜行数、列数、高度、宽度O5.表示空白,返回空。本公式巧妙动用MOD和ROW函数产生一个循环的序列2/3/1/2/3/1/2/3/1,再通过CHOOSE函数参 数的变化动态的引用工资明细表的数据，其中”的作用是当前行行号为3的倍数时返回空，从而产生一个 空白行，方便制作工资条后裁剪。当然，实现功能还有很多公式，如用以下IF函数实现等，各位用户自己去多摸索吧：=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明 细!A$l,ROW()/3+l,0)J)二、双行工资条头之设计双行工资条头之设计先看数据：图6双行条头工资条和单行条头实现方法基本致，仅仅是公式有些差异。现暂列如下：=CHOOSE(MOD(ROW(),4)+1,”,双行表头工资明细!A$l,双行表头工资明细!A$2,OFFSET(双行表头 工资明细!A$1,RO W()/4+2,)输入公式后向后拉再向下拉至末行，然后通过前面介绍的方法设置边框，其中部分单元格需去掉左 边框或者右边框，使之显得美观。最后结果如下见图7：SEEBfPS云格2005 l工笠珏设计方案.et.评析：WPS函数是一个相当强大的计算和格式转换工具，只要多运用，熟练掌握了函数功能后，可 以为您的工作带来无穷便利。根据工资计算所需钞票数量目前有部分企业、工厂采用现金发放的方式发工资，而对于财务人员来说预估各种面额的钞票张数 则成了必不可少的程序，对于大部分不懂VBA和函数的人员同时也是一个难点。实事上ET2009的数组公式可以轻松解决这个问题，只需要几秒钟，借用一个数组公式就可以完成 所有人员的所需钞票数量。现具体演示一下需求与完成步骤。假设需要计算的面额包括100元、50元、20元、10元、5元、2元、1元（如果需在角与分也用同一个 公式，思路上没有分别），那么在工资存放列（假设为B歹D右边建立7列做为辅助区，用于存放每种面 额的钞票张数。1.在 C1:H 区域分别输入 100、50、20、10、5、2、1；2.选择C1:I1区域，再单击右键，选择“设置单元格格式”菜单；3.在“数字”选项卡的“分类”中选择“自定义”；4.右边的“类型”框中显示了“G/通用格式”，将其修改为“G/通用格式“元”，此时单元格中虽然只有数字，但显示效果却包括了单位“元”，见图一所示:J计宜面值个数et ci 100ABCDEGh i-rpr姓名工资1100元0元20元10元5元2元1元2赵69873钱25864孙25785李200006周123567吴20098郑4203图一自定义数字格式5.在C2单元格录入以下公式：=IF(COLUMN()=3,INT($B2/C$1),INT($B2-SUM(OFFSET($B$1,ROWS(A$1:A1),1,1,COLU MNS($B:B)-1)OFFSET($B$1,1,1,COLUMNS($B:B)-1)/C$l)录入公式后需要同时按下“Ctrl+Shift+Enter”三键结束，表示按照数组公式计算，否则无法产生正确结果。6.选择单元格C1,将公式向右填充至12,再双击填充柄，将C2:I2的公式向下填充至末尾。公式的计算结果见图二所示：J计宜面值个数et 023 萩ABDEFGh rr1姓名工资100元50元20元10元5元2元1元2赵6987691111103钱2586251111014孙2578251101115李200002000000006周123561231001017吴2009200001208郑420342000011图二利用数组公式计算钞票张数7.为了验证计算是否准确，再在J列建立一个辅助区，用于汇总所有面额与数量的乘积。在J1输入“汇总”，在J2输入以下公式:=SUM($C$1:$I$1*C2:I2)仍然以“Ctrl+Shift+Enter”三键结束，否则无法产生正确结果。8.双击J2单元格的填充柄，将公式向下填充到最末单元格。9.根据J列的汇总值与B列的工资进行比较，可以清晰分辨公式的正确性。见图三所示：日计宜而0个数et J2 卷 潢=SUM($C$1:$I$1*C2:I2)ABC 一DEGH IJ1姓名工资100元50元20元10元5元2元1元汇总2赵69876911111 069873钱25862511110 125864孙25782511011 125785李2000020000000 0200006周1235612310010 1123567吴20092000012 020098郑42034200001 14203图三验证公式的准确性公式思路解释：计算100元面额的钞票数量时最简单，将工资除以100,然后利用INT函数取整即可，即公式中“INT($B2/C$1)”部分;而计算其它面值的钞票张数时，只需要对剩下的部分工资进行计算。而如何确定已经计算过的钞票 面额的值是重点。本例中利用OFFSET 1,1,COLUMNS($B:B)-1)W获取已经计算过的钞票面额，再用OFFSET($B$1,ROWS(A$1:A1),1,1,COLUMNS($B:B)-1)获取已计算过的钞票对应 的数量，两者乘积并汇总，再总薪资求差即为剩下的待计算金额。而“两个区域乘积并汇总”在ET中有一个专用函数一一MMULT,所以本例公式可以改 为(COLUMN()=3,INT($B2/C$1),INT($B2-MMULT(0FFSET($B$l,R0WS(A$l:Al),1,1,COLU MNS($B:B)-1),TRANSPOSE(OFFSET($B$1,1,1,COLUMNS($B:B)-1)/C$1)”根据前面的分析，计算100元钞票的数量和其它面值的数量使用了不同的公式。为了让 两者统一，即仅使用一个公式完成，通过IF函数将两段公式结合即可，使公式在第3列是 按前一种方式计算，列号大于3时则按另一种方式计算。最后补充一点，如果用同类软件EXCEL解决此问题，可以改用以下普通公式完成：=IF(COLUMN()=3,INT($B2/C$1),INT($B2-SUMPRODUCT(B2:$C2*B$1:$C$1)/C$1)巧用函数快速录入字母序列用过WPS2005的朋友也许会发现，新版本的表格可以自动填充序列。如我们输入数字、日期后，按 住鼠标左I拖放，下面的行(列)会自动填充。但是如果我们输入字母，却不能实现序列填充。图BcDEF献123456g月29日9月30日10月1日10月2日10月3日10月4日*管 号aaaaaa4AAAAAk,OQSOflxxnr那么我们怎么办呢？好办！新版本的表格新增了自定义序列的功能。1、单击工具菜单、在下拉菜单中选中“选项”（图2）：工具复）数据）窗口世）帮助)%重篁活动工作簿C）文本转换成数值国）F9保护9金山权限管理国）COM加载项&）.选项（Q）.i嬲哈光F8m京I v JB/U室建 a 2tonin 曰1 口 inH?n in 曰&曰 mH图22、在下面出现的“选项”选项卡中选中“自定义序列”，单击“自定义序列（L）”下的“新序列”，在右面的“输入序列（E）”下的空白档中输入26个字母。3、单击“添加”、，“确定”后即可（图3）：w0b.扃MoHom*aA月29日 b/u 维国学，：4方 叁XJMS|KMitX|H月显示|索以月保存 自定义序列|跋色|安全性|自定义*列3*入序列（X）正月，二宵2月.*：2配息是L.5小Tu明 Tfcwu Fri,SetSidey,Bondajr,Y如rd0九 ThJf Pb.Btr,Apr,Juft,J&A”三月.六月.十.未，申,S,辛.壬,聂Jguary.Fbrutry,Ntreh.April,.)玄2 71 14 回旗，M9月6HG二J脸|取种pspmgsoR 8m图3但是字母算上大小写总共52个，我们能不能偷个懒，让表格替我们输入呢？可以！1、新建一个空白表格，在单元格A1中输入公式“=CHAR（65+ROW（）-1）”，然后按住鼠标2、3、执行上段1、2步操作，在“输入序列”下的空白档中，单击右I、单击“粘贴”后，单击“添加”、，“确定”。这样大写字母序列就添加完成了（图6）：p&.kingsoftcomX,二.A选项重新计算I端情与显示I章旗与俣存自定义再列19色|安全性|自定义序列(D六昂占Sun,Mg Tut/Itd,Thq Fri/Sz Mondty,Tucd”，fednsdty Th 丁皿 Ftb,Wur,Apr,May,Jg,Jul.Av Itfiutry,F thru try,rch,Aril,V.N 0添加Q)|Q二1喉 I)清图6如果要输入小写字母，把公式“=CHAR(65+ROW()1)”中的“65”改为“97”即可。ET真聪明，自己会做账最近，一杯绿茶使用多年的的财泉软件打不开了，多年的财经数据毁于一旦！对了，WPS表格稳定 性好，她集科学计算、图形和数据库的功能于一身，是一种功能强大的开发工具，就用她来做账了。1,用WPS表格做一个简单的收支明细表，验证WPS的功能。股市收入支出明细表凭证项 目借 方贷 方余额收1资产15202.99支1成本20850.002,以实际工作中常用的三栏式账户为例，做一个账户。3,如果让会计们在小表格里一位一位的录入数字，那样比用计算器还累。怎么办呢？用辅助列帮忙录入数字吧！那样就好办了，让电脑计算后再自动填入格子里。在“借方”的F3单元格填入公式：=LEFT(RIGHTS&R0UND($D3,2)*100,11-COLUMN(E:E)+D),横拉，填充到 L3,当你在D3铺助格输入127,电脑会自动把127这个数字分列填在对应的格子里。5,在 V16单元格输入=(F16&G16&H16&H6&J16&K16&L16)*l-(N16&016&P16&Q16&R16&S16&T16*l)*l,并隐藏该列。6,在“余额”的 W16单元格输入=IF($V16,LEFT(RIGHT(&$V16*100,9-C0LUMN(A:A),“)。在“余额”里可以实现自动跨行计算,不过，由于记帐时位置是 变化的，自动计算的结果没有实际意义。好，一个自动做账的帐页做好了。7,做一个红色的收款凭证，在D4单元格输入：-LEFT(RIGHT C GROUND($C4,2)*100,11-COLUMN(C:C)+1),横拉,填充到L4；再做一个蓝色的付款凭证。XX公司收款凭证0K!我们可以按账务处理程序记帐了。提醒一下，为了美观，打印帐页时要把辅助列隐藏起来。巧用函数组合求每页小计之总计在使用WPS表格制作财务报表时，要求每页人数固定且有工资小计，最后一页要有工资总计，即 各页工资小计的总和。一般会使用求和函数SUM,但如果工资财务报表页数很多，使用SUM函数时很 麻烦，要先输入该函数，再用鼠标点选参加计算的单元格，再按加号，再拖动WPS表格找到另一页小 计单元格点选一一这可是既考验体力更考验耐心的苦差事，疲劳之后，经常会点错或看错单元格。导致 结果错误，又得“重新来过！有没有更简单安全的方法呢？经试验发现通过一些函数组合就可以实现隔行求和。报表要求每页10人（如图中第38-47行），在每 页第11行设计一个小计（如图中48行），表格的最后一页统计出各页小计的总和（如图中第49行）。所有 数据输入完成后，设置好页面并给每页添加小计行，并计算出每页小计数据。接下来在所有页后的任 意一行设计出总计行，在相应的总计单元格中输入公式“=$1；乂（正.0口（1)、小于(=60)这里的“=60”需要用英文双引号()引起来，否则将报错。如果需要引用单元格，除了“恰好等于”的情况外，也不能直接引用，除了要用英文双引号将“=”这 样的关系符引起来之外，还要用连接符把关系符和单元格地址连接起来，如需要求成绩高于平均分 的人数，就应该用下面的公式：=COUNTIF(D3:D15,&D 16)利用VLOOKUP函数轻松查询表格数据在日常工作中，我们会经常用wps表格处理工资和学生成绩等数据。如果单位人员很多，手工查询 个人数据不太方便。利用VLOOKUP函数，查询个人信息则变得十分便捷。下面以开工资介绍为例进 行说明。一、建立单位工资基本信息将工作表标签Sheetl命名为“工资”，在该工作表内输入姓名及各项工资内容。如图一二、建立工资介绍页面将工作表标签Sheet2命名为“工资介绍”，输入所需要填写的内容，其中工资项目可以复制“工 资”工作表中的表格。如图二三、建立函数在“工资介绍”工作表B9单元格中输入函数”=VLOOKUP(工资介绍!$A$9,(工W!$A$4:$AA$9),2,FALSE)”，该函数的含义是:根据本工作表单元格A9中的数据信 息，在“工资”工 作表中A4至AA9的范围内查询与本工作表A9相同的信息，并返回相同信息单元格后第2个单元格的内容。将该函数向右复制到各单元格中，在C9中将括号中的2改为3,在D9中将2改为4,依此类推。如图三文件也）I编辑I视图W）插入Q）格式）工具（X）数据（）窗口过）帮助国）口二匚a，国7 工.繁着c 八a I蝮，z 3欣I助1。%心呼;宋体，9，,B/g,妄妄善回，I 4=%，3:Bookl.xls*函数建立完毕后，我们如果要为李四开具工资介绍，只需在“工资介绍”工作表的A9单元格中输入“李四”，其所有工资信息将自动显示在后面的对应单元格。如图四上述方法适用于学校查询学生成绩等方面。SUMIF、COUNTIF和SUMPRODUCT函数在成绩统计中的应用在学校组织的学年考试考务工作中，为了便于评卷实行流水作业和避免学生作弊，经常将同一年级 不同班级的学生拆散混编。在考试登分完毕之后，如何做到不改变原始表格的状态而进行分班成绩统计 呢？利用SUMIF、COUNTIF和SUMPRODUCT这三个函数，一切都将会变得十分轻松。下面举例说明。一、造册登分登分册一般有班级、考号、姓名及语文、数学等学科名称，我们可以把这个工作表建立在Sheetl,并将其重命名为“登分册”，将学生成绩输入其中（见图一）。将Sheet2命名为“统计表”，在其中设置好班 级、科目、平均分、及格人数、及格率、优生人数、优生率等统计信息列标题，输入班级和需要统计的 科目内容（如图二）。令IPS表格-Book3 题翻修加匚|回国，文件9 编辑视图9 插入Q）格式。）工具）数据也）窗口世）帮助国）觉得WPS Office的.-日看H 3 二繁 T 与 e 二，/0 嚏.z，2 1agi 幽 1。；:C o：、，-输入要搜索的内容二二 二仁E宋体 卧2 菱回圜T富，温线I-，|日 Book3*C 14，笈A I B C号 考1班级2一3-（2）4-5-6-（2）7一8一9-10-11-12-13一1412 3 4 5 6语文7591667879807682568492860 5 07 6 9学数，:绘图，自选图形）、匚闻阖I,IPS 表格-Book3 /茄（3回I1文件电）编辑鱼）视图9 插入9 格式）工具复）数据）窗口世）帮助 觉得WPS Office的-S.c h e 国 I工保 T a c 入，5 w 2 i a g w 1。g/:宋体汾 Book3B312343 gI8212 3 12 3班二二二目区区文学学学 科语数数数BD|E|F 平均分及格人数及格率图二E I F I G I H名二、建立函数在“统计表中的C2单元格输入数组公式=SUMIF（登分册!$A$2：A$13,一（1）”,登分册!$D$2：D$13）,按住Ctrl和Shift键回车可以计算出一（1）班的总分，将公式向下复制到各单元格，并将其中的“一（1）”班分别改为相应班级名称。这个数组公式的含义是：在“登分册”工作表中查找“一（1）班对应的成绩，并返回一（1）班所有成绩之和。如图三 wro 值m-.xxs文件9 编辑更）视图9 插入复）格式）工具任）数据也）窗口世）帮助国）口*国Id繁T上e 4,I嚏 21 H住体，12，|B I II多塞售回回，|喝，温司成籍茨计*C 2，&笈 仁SUMIF（登分册!$A$2:$A$13,“一（1）”,登分册!$D$2:$D$13)E F及格人数及格率288311285347目文文文学学学 科语语语数数数12 3 12 3zk rk 好一一 一一二A在“统计表”D2单元格输入公式=1=60）,其含义是：若“登分册”工作表A列中有与“统计表”A2相同的信息,即“一（1）班”，则返回与“一（1）班”相对应的成绩中大于或等于60分的人数（如图四）。将公式向下复 制后，在F2单元格中输入公式=E2/COUNTIF（登分册!$A$2：A$13,统计表!$A2）计算该班的及格率。WPS表格-成绩统计.xh,文件）Iq辑量）视图9 插人=60”修改成学校规定的优生标准（如是80分为优 生，则将60改为80即可）（如图五）。同理，将F列中的公式复制到H列即可得到优生率的统计结果。；21班基2一_3_一（2）4一5一6-（2）7一89过 已Boo蛇*A 仁SUIPRDDUCTa登分册!$A$2：$A$13;统计表A2）*（登分册!$D$2：$D$13）=80）仁 文传戊）蛾ifia）（V）茹入9 格式）xAa）必霆色）青口 帘勖opi一上g&T 一 宏 _ c 二_ 域E 4二 Y吧X.一浮体 72 b/u三荃三aa%方方自 J43I78.25867277.75100.00%25.00100.00%3 75.00%75.00%2 50.00K100.00%1 2 5.00%100.00%75.00%q371.2 586.75图五3 75.00%2 50.00%需要提醒大家的是，使用数组函数，一定不要忘记是CtrMShifU同车。数学与三角函数ABS返回数字的绝对值ACOS返回数字的反余弦值ACOSH返回数字的反双曲余弦值ASIN返回数字的反正弦值ATAN返回数字的反正切值ATAN2从X和Y坐标返回反正切CEILING将数字舍入为最接近的整数，或最接近的有效数字的倍数COMBIN计算从给定数目的对象集合中提取若干对象的组合数COS返回数字的余弦值COSH返回数字的双曲余弦值DEGREES将弧度转换为度EVEN将数字向上舍入为最接近的偶型整数EXP返回e的指定数乘幕FACT返回数字的阶乘FACTDOUBLE返回数字的双倍阶乘FLOOR将数字朝着零的方向向下舍入GCD返回最大公约数INT将数字向下舍入为最接近的整数LGM返回整数参数的最小公倍数。LN返回数字的自然对数LOG返回数字的指定底数的对数LOG 10返回数字的常用对数MULTINOMIAL返回参数和的阶乘与各参数阶乘乘积的比值MINVERSE返回数组矩阵的逆距阵MDETERM返回一个数组的矩阵行列式的值MMULT返回两个数组的矩阵乘积MOD返回两数相除的余数MROUND返回一个舍入到所需倍数的数字ODD将数字向上舍入为最接近的奇型整数PI返回PI值POWER返回数的乘累结果PRODUCT将所有以参数形式给出的数字相乘QUOTIENT返回商的整数部分，该函数可用于舍掉商的小数部分。RADIANS将度转换为弧度RAND返回0到1之间的随机数RANDBETWEEN返回指定数字之间的随机数ROMAN将阿拉伯数字转换为文本形式的罗马数字ROUND将数字舍入到指定位数ROUNDDOW将数字朝零的方向舍入ROUNDUP将数朝远离零的方向舍入SERIESSUM返回I基于公式的基级数的和SIGN返回数字的符号SIN返回给定角度的正弦值SINH返回数字的双曲正弦值SQRTPI返回某数与PI的乘积的平方根SQRT返回正平方根SUBTOTAL返回数据库清单或数据库中的分类汇总SUM将参数求和SUMIF按给定条件将指定单元格求和SUMPRODUCT返回相对应的数组部分的乘积和SUMSQ返回参数的平方和SUMX2MY2返回两数组中对应值平方差之和SUMX2PY2返回两数组中对应值的平方和之和SUMXMY2返回两个数组中对应值差的平方和TAN返回数字的正切值TANH返回数字的双曲正切值TRUNC将数字截尾取整GCD返回两个或多个整数的最大公约数财务函数ACCRINTM返回到期一次性付息有价证券的应计利息。DB使用固定余额递减法，返回一笔资产在指定期间内的折旧值DDB使用双倍余额递减法或其他指定方法，返回一笔资产在指定期间内的折旧值FV返回投资的未来值TPMT返回给定期间内投资的利息偿还额IRR返回一系列现金流的内部收益率ISPMT计算在投资的特定期间内支付的利息M1RR返回正负现金流在不同利率下支付的内部收益率NPER返回投资的期数NPV基于一系列定期的现金流和贴现率，返回一项投资的净现值PMT返回年金的定期付款额PPMT返回投资在某一给定期间内的本金偿还额PV返回投资的现值RATE返回年金的各期利率SLN返回一项资产在一个期间中的线性折旧费SYD返回某项资产按年限总和折旧法计算的指定期间的折旧值VDB使用余额递减法，返回指定期间内或部分期间内的某项资产折旧值数据库函数DCOUNTA计算数据库中满足指定条件的非空单元格个数DGET从数据库提取符合指定条件的单个记录DVARP基于选择的数据库条目满足指定条件的样本作为总体计算的方差DVAR基于选择的数据库条目满足指定条件的样本的估算方差DSUM将数据库中符合条件的记录的字段列中的数字的和DSTDEVP基于选择的数据库条目满足指定条件的样本作为总体计算的标准偏差DSTDEV基于选择的数据库条目满足指定条件的样本的估算标准偏差DPRODUCT将数据库中符合指定条件的记录的特定字段中的值的乘积DMIN返回选择的数据库条目满足指定条件的最小值DMAX返回选择的数据库条目满足指定条件的最大值DCOUNT计算数据库中包含指定条件数字的单元格个数DAVERAGE返回选择的数据库条目的平均值息函数CELL返回有关单元格格式、位置或内容的信息ERROR.TYPE返回对应于错误类型的数字IS本部分描述了用来检验数值或引用类型的九个工作表函数。ISEVEN如果参数number为偶数，返回TRUE,否则返回FALSEISODD如果参数number为奇数，返回TRUE,否则返回FALSEN返回转换为数字的值NA返回错误值#N/ATYPE返回表示值的数据类型的数字逻辑函数AND如果所有参数均为TRUE,则返回TRUEIF指定要执行的逻辑检测NOT对参数的逻辑值求反OR如果任一参数为TRUE,则返回TRUEFALSE返回逻辑值FALSETRUE返回逻辑值TRUE文本函数ASC将字符串内的全角（双字节）英文字母或片假名更改为半角（单字节）字符CHAR返回由代码数字指定的字符CLEAN删除文本中所有打印不出的字符CODE返回文本字符串中第一个字符的数字代码CONCATENATE将若干文本项合并到一个文本项中DOLLAR按$（美元）货币格式将数字转换为文本EXACT检查两个文本值是否完全相同FIND在一文本值内查找另一文本值（区分大小写）FINDB在一文本值内查找另一文本值（区分大小写）FIXED将数字设置为具有固定小数位的文本格式LEFT返回文本值最左边的字符LEFTB返回I文本字符串中的字符个数LEN将文本转换为小写形式LENB返回文本字符串中的字符个数LOWER从文本字符串中的指定位置起返回特定个数的字符MID替换文本内的字符MIDB从文本字符串中的指定位置起返回特定个数的字符PROPER将文本值中每一个单词的首字母设置为大写REPLACE替换文本内的字符REPLACEB替换文本内的字符REPT按给定次数重复文本RIGHT返回文本值最右边的字符RTGHTB返回文本串中最后一个或多个字符RMB按（RMB）货币格式将数字转换为文本SEARCH在一文本值中查找另一文本值