WPS专题之六-函数应用大宝典.pdf
《WPS专题之六-函数应用大宝典.pdf》由会员分享,可在线阅读,更多相关《WPS专题之六-函数应用大宝典.pdf(44页珍藏版)》请在咨信网上搜索。
1、WPS专题之六函数应用大宝典WPS表格是办公室自动化中非常重要的一款软件,方便地采集和处理数据,具有丰富灵活的计算功能,被广泛应用于管理、统计、财政、金融等众多办公 领域,如在人事管理、会计、统计处理方面WPS表格都是绝好的帮手,学好 ET的基础应用后,当进一步学习它的函数、图表和其它功能,然而很多人对 ET数据处理功能不了解,难以进一步深入,故而整理了这个函数应用的专题,力求向WPS fans们传达更多、更形象、更直观的信息,为大家学习函数提供 一个好的帮助:首先,我们来看看函数能为我们做什么?由出生日期判断属相论坛里用身份证号码提取性别、出生口期(年龄)、甚至出生地的方法都有介绍了。今天在
2、论坛中 又有网友提问,如果已知一个人的出生日期,能否判断其属相?这个问题看似复杂,其实也很简单。只要灵活运用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猪我们知
3、道,12生肖是按照“鼠牛虎兔龙蛇马羊猴鸡狗猪,的顺序循环的。通过计算得知,1900年应为“鼠 年”,其它以此类推。那么,我们只要算出出生年份与1900年的差,再用这个差除以12,并与“鼠年”相比 较,就可以准确判断该年出生的人的属相了。于是在D2单元格输入公式:=M1D(鼠牛虎兔龙蛇马羊猴鸡狗猪1MOD(YEAR(C2)-1900,12)+1,1)并回车,即可看到第一条记录,赵芳梅的出生年份是1973年,属相是“牛”。将该公式向下填充,即 可准确得出每个人的属相。公式解释:一、MOD函数函数功能:返回两数相除的余数。结果的正负号与除数相同。函数格式:MOD(数值,除数)例如:=MOD(25,4
4、)”即表示用25除以4,求其余数。结果为1。二、MID函数函数功能:从文本字符串中指定的位置开始,返回指定长度的字符串。函数格式:MOD(字符串,开始位置,字符个数)例如:=MID(中华人民共和国”,2,3)”即表示取字符串“中华人民共和国”第2个字符开始,取3个。即“华人民”。巧用函数生成随机数字在进行概率测试时(如玩彩票的网友就会经常用到),我们会经常用到随机数。当需要大量的随机 数时,也许你会为如何获得这么多符合要求的随机数感到困惑。其实,用WPS表格2007个人版,中心 满足你对随意数的各种要求。一、生成0-1之间的随机数这个最容易,因为WPS表格内置了随意机生成函数一一rand函数,
5、用法也很简单,只要在需要生 成随意数的单元格内输入“=rand()“(不含外侧双引号,且所有字符均为半角,如图1)。WPS 表格-Bookl.et*图1输入完成后回车,该单元格即出现一个大于或等于0,并小于1随机小数。如果你需要很多这样的随机 数的话,可以选中该单元格,然后用鼠标左键在该单元格右下角的方形点(即填充柄)按下,并向下拖 动到合适的位置,则这一范围内所有的单元格均生成一个随机数,如果还要更多,则再选中已经生成随机 数的所有单元格,并在选区右下方如法向右拖动,则拖出的矩形区域内的所有单元格都会生成随机数,要多少有多少啊(图2)!WPS 表格-Bookl.et*a文件9 编辑CE)视图
6、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.262634277
7、0.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.5780029
8、30.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(数值,小数位数),其功能
9、是“按指定的位数对数值进行四舍五入后返回”。如“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(最小整数,最大整数),其功能是取最小整数和最大整数之间的随意整数。上
10、面的公式可以改写成“=R ANDBET WEEN(0,100)”即可达至I相同的效果。三、随机数生成后不再变动用以上两个函数生成的随机数都有一个共同的特点,就是每当工作簿中有单元格的内容发生改变 时,随机数都会重新计算,也就是说,随机数随时都在变化之中,而不能固定下来。如果你希望这些随 机数一旦生成就不再变动,又该如何做呢?其实也很简单,就是在随机数生成后,把它们全部剪切到系 统剪贴板,并从“编辑”菜单中选择“选择性粘贴”,并在弹出的窗口中选择“数值”(见图3)。或者,在“常 用工具栏,中点击“粘贴工具,右侧的倒三角,并在下拉菜单中选择“值”(如图4)。这样,粘贴后的单元格 的值就不会再有变化
11、了。选择性粘贴粘贴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表格,表格上方的标题及单位信息可
12、以在页眉中输 入,按照表样设置好字体等。二、输入数据。姓名、性别、出生年月、学历、参加工作时间及调整前享受的教护龄 津贴标准等数据,可以直接调用单位人员档案复制粘贴,其中参加工作时间的格式为“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(F
13、3=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号文件精神,经审查,同意“等“位同志享受教(护)龄津贴,月增资”元,从二。六年元月一日起执行。
14、如调离教师、护士岗位后,取消教(护)津贴。)。至此,整个表格就制作完成了。如图_ O将此工作表中的人员信息等基本数据清空,保存为模板,今后本单位有增加教龄津贴 的人员时,审批表的填写就比较简单和轻松了。用WPS表格轻松设计工资条在任何企业之财务管理中,一定少不了工资计算和设计工资条。工资条头之美观及操作速度是设计的 一个重点。若能掌握技术则可事半功倍,否则将受限于大量的手工操用,效率低下、且准确性无法得到 保障。首先让我们看看工资条都有些什么特点,才能对症下药。工资条头可能一行,也可能两行,根据不同企业工资栏目需求而定。但相同处是每一项条目(或 者一个工人的工资信息)具有一个条头,条头具有指定
15、数目的重复性;二:每一项条目中间有一个空行,方便裁剪。根据以上特点,我们可以初步拟定工资条头制作方案:首先:建立“工资明细表”,用于储存员工工资表信息,也用于平时编辑和汇总。其次:建立“工资条目表”,用于引用工资信息,同时产生工资条形式之工资目。需要用到的函数可以因人而异,有很多公式都可以达成目的,但最后选择目标是效率最高,同时又 易于理解者。现在,通过二个实例向大家详解工资条头设计之过程,分析其中技巧。望大家能从中受益,举一反 三,设计出适合各自需求之公式。一、单行工资条头之设计先看下面数据,这是一个简易的单行条头工资信息摘录(数据随机生成,非实际工资,您可以 url=http:/ 1。图1
16、利用此表数据,可以通过函数公式来引用数据,生成需要的工资条形式。步骤与公式如下: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,并点击工具
17、栏边框按钮中的田字形按钮添加边框;再点击大方框按钮(见图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函数是返回指定行行号之函
18、数,若省略参数时则返回当前行。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,
19、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函数是一个相当强大的计算和格式转换工具,只要多运用,熟练掌握了函数功能后,可
20、 以为您的工作带来无穷便利。根据工资计算所需钞票数量目前有部分企业、工厂采用现金发放的方式发工资,而对于财务人员来说预估各种面额的钞票张数 则成了必不可少的程序,对于大部分不懂VBA和函数的人员同时也是一个难点。实事上ET2009的数组公式可以轻松解决这个问题,只需要几秒钟,借用一个数组公式就可以完成 所有人员的所需钞票数量。现具体演示一下需求与完成步骤。假设需要计算的面额包括100元、50元、20元、10元、5元、2元、1元(如果需在角与分也用同一个 公式,思路上没有分别),那么在工资存放列(假设为B歹D右边建立7列做为辅助区,用于存放每种面 额的钞票张数。1.在 C1:H 区域分别输入 1
21、00、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($
22、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李200
23、002000000006周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 一DEG
24、H 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)”部分;而计算其它面值的钞票张数时,只需要对剩下的部分工资进行计算。而如何确定已经计算过的钞票 面额的值是重点。
25、本例中利用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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- WPS 专题 函数 应用 宝典
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【曲****】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【曲****】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。