Excel常用函数在工作中的应用.pptx
《Excel常用函数在工作中的应用.pptx》由会员分享,可在线阅读,更多相关《Excel常用函数在工作中的应用.pptx(48页珍藏版)》请在咨信网上搜索。
1、EXCEL常用函数在工作中的应用常用函数在工作中的应用闵华荣闵华荣20162016年年1 1月月3 3日日2024/5/22 周三1 1EXCEL函数概述函数概述Excel是微软office办公软件的一个重要组成部分,具有强大的计算统计功能,它可以进行各种数据的处理、统计分析和辅助决策操作,广泛地应用于管理、统计、财经、金融等众多领域。Excel中的函数功能,可以执行指定计算,分析信息,尤其是Excel函数的组合使用,可以使十分复杂的计算、统计、查询、排版和管理,简便化,起到事半功倍的效果。2024/5/22 周三2 2EXCEL函数公式编写规则函数公式编写规则1、所有的公式必须在英文状态下输
2、入,并以等号开头;2、函数名,如IF,均写在等号的后面再加一个括号,如=IF(A2=B2,”正确”,”不正确”);3、公式的函数名和单元格的列号,都不区分大小写;4、在Excel的公式嵌套中,只使用小括号。嵌套时,新嵌套进去的公式,也要把函数名写在最前面、后加括号;5、当公式引用的单元格发生变化时,公式会自动重新计算结果,并显示在输入公式的单元格内;6、注意绝对引用($A$5)、相对引用(A5)及混合引用($A5与A$5)的作用:凡有$符号的,$右边的字母(即列)或数字(即行),复制时,其位置是不变的,反之就会改变。7、关于循环引用:若确实需要循环引用同一单元格或单元区域,则必须 Excel选
3、项中开启迭代运算选项时,否则不能进行循环引用公式的计算,并自动提示报错。8、在编辑栏中输入等号和函数英文字母时,电脑会自动在编辑栏下方逐字显示相应函数命令,供你选择。2024/5/22 周三3 3EXCEL函数公式一般格式函数公式一般格式 不同函数公式,有不同的格式要求,必须按格式要求设置公式,尤其要注意函数公式中的嵌套逻辑关系,否则报错:1、=IF(条件,条件成立时执行的内容,条件不成立时执行的内容);2、=IF(AND(OR(G4=条件1,G4=条件2),I4=30,H4=条件3),满足条件执行的内容,满足条件执行的内容)3、=sum(需要进行汇总的数据区域);4、=COUNTIF(统计区
4、域,“统计条件”);5、=LOOKUP(查找条件,查找条件所在区域,满足条件的对象所在区域);6、=SUMIFS(汇总数据区域,条件1所在区域,条件1,条件2所在区域,条件2,条件3所在区域,条件3,条件n所在区域,条件n)在此不一一例举。以下介绍函数时,均会介绍函数的用途、格式等。2024/5/22 周三4 4学习学习EXCEL函数公式解决问题的简要方法函数公式解决问题的简要方法1、掌握常用函数的命令、格式、用途、各参数要求(包括函数的优势与不足);2、掌握各常用函数混编组合公式的格式与逻辑关系,尤其是各参数、分隔逗号、括号、绝对引用、相对引用、混合引用、逻辑或、逻辑且等组合元素的位置与层次
5、关系(技巧:由最里逐级向外,分清每一函数公式的层次,在该层次中,必须符合该函数的格式要求,括号必须对应成对,注:用鼠标在编辑栏选中全部公式后,凡对应成对的括号,均为同一颜色);3、在构思函数公式前,必须先明确:要解决什么问题或存在什么问题,有什么已知条件,再判断需要使用什么函数或函数组合。类似于平面几何证明题:可以从已知条件入手,逐步推进,寻找解决办法;也可从问题入手,逆向分解,寻找规律,解决问题。特别说明:特别说明:与平面几何证明题一样,用函数公式解决工作当中的实际问题的方法,也有2种以上的方法,所不同的是:使用的函数不同、编写的公式长短不同、公式的繁简不同。2024/5/22 周三5 5你
6、的你的EXCEL函数水平有多高?函数水平有多高?1、菜鸟级、菜鸟级会使用最常简的求和、求平均数函数SUM、AVERSE、MAX、MIN2、新手级、新手级比菜鸟会用更多的函数,会使用IF函数进行简单判断、会对数字进行四舍五入运算。IF、COUNT、LARDE、ROUND、INT、TODAY3、入门级、入门级对函数已有初步了解,熟悉最常用函数的使用方法。IFERROR、VLOOKUP、SUMIF、SUMPRODUCT4、初级、初级已能熟练使用更多常用函数的公式设置,但对函数的深入用法不够了解。MATCH、DATEDIF、SUMIFS、COUNTIFS5、中级、中级这部分用户在单位里已属函数高手级别
7、,已能解决复杂的函数问题,但公式优化还需学习。OFFSET、INDIRECT、LOOKUP、TXTE6、高级、高级对Excel函数已达到很高的境界,已能运用数组公式解决复杂的数据计算和处理。FREQUENGY、MMULT、T/N、SUBSTOTAL7、大师级、大师级不用说了,这类是函数高手中的高手,没有他写不出的公式,解题思路已成系统。二分法、三维引用、数组维数转换2024/5/22 周三6 6一、函数名称:一、函数名称:IFIF主要功能:根据对指定条件的判断的是否成立,返回条件成立后的执行内容。使用格式:=IF(条件表达式,“条件成立时显示或执行的 内容“,“条件不成立时显示或执行的内容)说
8、明:1、所有Exce函数公式的等号、括号、逗号均为英文状态下输入。2、office2003可嵌套7层;office2007嵌套64层。=IF(逻辑判断表达式1,当判断条件成立时显示或执行的内容,IF(逻辑判断表达式2,当判断条件成立时显示或执行的内容,当判断条件不成立时显示或执行的内容)2024/5/22 周三7 7IFIF函数应用实例一函数应用实例一进行校对:通过函数公式对2人及2人以上编辑的相同电子表文档数据进行校对,并对不正确的数据进行标注。=IF(原文档G4=用于校对!G4,正确,不正确)即:在原文档相应单元格中输入公式:=IF(原文档中需要进行校对的数据单元格=另一文档中的该数据单元
9、格,“若相等显示:正确”,“若不相等显示:不正确)适用于表间单元格内容校对适用于表间单元格内容校对1、进行校对案例2024/5/22 周三8 8IFIF函数应用实例二函数应用实例二根据指定类别,显示对应内容:根据党员类别和工资收入,自动选择并显示对应缴费比例或金额。党费收缴标准如下:党党费收收缴标准准党党员类别工工资收入收入缴费比例或金比例或金额家属、下岗无工资收入0.21.00元在岗30000.50%3000,50001.00%5000,100001.50%100002.00%离退休50000.50%50001.00%2024/5/22 周三9 9在“比例”单元格内输入:方法方法1、以单、以
10、单“人员类别人员类别”=IF(G12=在册,IF(M12=3000,0.5%,IF(M12=5000,1.0%,IF(M12=10000,1.5%,2.0%)对在册党员有效,对离退休和家属党员无效。方法方法2、以双、以双“人员类别人员类别”=IF(OR(G38=“家属”,G38=“下岗”),“”,IF(G38=“离退休”,IF(M38=5000,“0.5%”,“1.0%”)对家属、下岗和离退休党员有效,对在册党员无效。方法方法3、以多、以多“人员类别人员类别”=IF(G12=在册,IF(M12=3000,0.5%,IF(M12=5000,1.0%,IF(M12=10000,1.5%,2.0%)
11、,IF(OR(G12=家属,G12=下岗),IF(G12=离退休,IF(M12=5000,0.5%,1.0%)。对在册党员、家属、下岗和离退休党员均有效。2、党费、党费缴费比例及金额自动充填缴费比例及金额自动充填计算案例计算案例2024/5/22 周三1010说明:说明:1、如果采用方法1和方法2,则需依据当前人员类别,输入相应的函数公式。不可将方法1或方法2复制到整个表的“比例”栏,否则报错:显示为FALSE。2、方法3可适用整个工作表,可将方法3复制到整个表的“比例”栏。原因是:原因是:在方法1和方法2的函数公式中,没有包涵所有人员类别。因IF函数在指定单元格中找不到设定的人员类别参数而报
12、错。而方法3函数公式的参数设置涵盖了整个工作表,因此更适用。2024/5/22 周三11111、1934年9月30日前出生增100元;2、1934年10月1日1939年9月30日期间出生增60元第一列:=IF(D4=,0,IF(D4DATE(1934,9,30),D4=DATE(1939,9,30),60,0)二列合一:=IF(D39=,0,IF(D39DATE(1934,9,30),D39=85,优秀,IF(C4=74,称职,IF(C4=60,基本称职,不称职)公式2:条件升序=IF(C4=59,不称职,IF(C4=74,基本称职,IF(C4=84,称职,优秀)公式3:条件乱序(报错)=IF
13、(C6=59,不称职,IF(C6=84,称职,IF(C6=74,基本称职,优秀)原因:若“条件”属序列关系,则必须采用升序或降序设置IF函数公式,乱序则出错。因为函数公式运行规则是:自左到右,即先执行IF(C6=84,“称职”),再 执行IF(C6=74,“基本称职“),而C6=84就包括了C6=74,犯了逻辑错误,因此不显示6074成绩区间的“基本称职”。2024/5/22 周三1414设置IF函数公式注意事项:1、要使IF函数公式适用于整个工作表,其采用的条件关键词,必须涵盖整个工作表。2、嵌套的层次关系要明晰,并符合IF函数公式的格式要求。技巧是:技巧是:1、以第一个IF,按函数格式逻辑
14、关系进行审核,然后审核第二个IF,经此类推。2、用鼠标在编辑状态栏内选中整个公式(同一级成对的括号为同色),由内向外以左、右括号成对判定其位置是否正确。5、IF函数嵌套逻辑关系图函数嵌套逻辑关系图2024/5/22 周三1515二、函数名称:二、函数名称:COUNTCOUNT、COUNTACOUNTA、COUNTIFCOUNTIF主要功能:COUNTCOUNT统计某个单元格区域中数字单元格数据 COUNTA COUNTA统计某个单元格区域中非空单元格数据 COUNTIF COUNTIF统计某个单元格区域中符合指定条件的单元格数目。使用格式:=COUNT(单元格区域,或文本、或数字)=COUNT
15、A(单元格区域,或文本、或数字)注:注:以上2函数:若括号内有文本、或数字,则和单元格区域一并参与统计)=COUNTIF(要统计的单元格区域,给定条件)适用范围:需要使用统计区域的数字单元格个数、非空单元格个数、或符合给定条件的单元格个数参与计算的。6、用COUNT、COUNTA、COUNTIF函数分别统计案例2024/5/22 周三1616COUNTIFCOUNTIF函数应用实例一函数应用实例一根据给定条件,在指定单元格区域内统计符合条件的数量:1、在统计全队职工中,女职工人数:=countif(表“性别”所在列,女)2、在统计全队职工中,少数民族职工人数:=countif(表“民族”所在列
16、,“*”)-countif(表“民族”所在列,“汉族“)说明:说明:“*”是通配符,在此表示统计“民族”所在列的所有人数,减去“汉族”人数后,即为少数民族人数,即“排他法排他法”。7、学历性别民族统计案例2024/5/22 周三17173、以1960年1月1日为时间节点,统计全队职工:1)1960年1月1日(不含)以前参加工作的职工人数:=COUNTIF(AG$4:AG$373,=1960/1/1)注意:注意:1、单元格区域使用了混合引用(O$4:O$375),即行为绝对引用,列为相对引用,目的是为了输入方便快捷,因行数较多,拖动选择很费事,通过复制粘贴,再更改所需列标字母即可。2、提示:提示
17、:COUNTIF函数中的日期格式为:1960/1/1。IF函数中的日期格式为:DATE(1960,1,1)8、按参加工作时间段统计人数案例2024/5/22 周三18184、统计1960年1月1日(不含)以前及1983年1月1日(含)后参加工作的(两个时间段)人数:=COUNTIF(AG$4:AG$373,=1983/1/1)5、统计1960年1月1日(含)以后至1983年1月1日(不含)前参加工作的(一个时间段)人数:=COUNTIF(AG$4:AG$373,=1960/1/1)-COUNTIF(AG$4:AG$373,=1983/1/1)2024/5/22 周三1919说明:说明:1)统计
18、1960年1月1日(不含)以前参加工作的职工人数时,计算机在执行日期计算时,均是按日期的系列天数进行运算的,1960年以后参加工作的系列天数,一定更大,即日期大的工龄小;日期小的工龄大。2)以某一日期节点统计前后数量时,一定要注意是包含本数,还是不包含本数。2024/5/22 周三2020特别注意:特别注意:“本数”只能包含一次,且只能不包含一次:即统计=本数与本数与=本数):正确;或统计本数=本数):正确;。若统计=本数与本数与本数):错误;或统计=本数(本数):错误。错误结果:1)=本数与=本数(或=本数)则会出现:本数重复统计;2)本数与本数(或本数)则会出现:本数未被统计;2024/5
19、/22 周三2121因此,“=本数”,一定与“本数”匹配 “本数”匹配1960年1月1日 1983年1月1日1960年1月1日 1983年1月1日1960年1月1日统计小于等于1960年1月1日及大于1983年1月1日出生的人数的方法:2区间相加 统计大于1960年1月1日及小于等于1983年1月1日出生的人数的方法:2区间按本区间全范围按本区间全范围相减,否则会重叠统计。以时间节点统计人数时(如以1960年1月1日为节点),一定要注意一定要注意 不等式的匹配:不等式的匹配:若“=”与“=”或“=”配对,统计会因“=”重复,多统计1960年1月1日出生人数;若“”或“”与“”配对,则统计会因漏
20、“=”,漏统1960年1月1日出生的人数;区间区间=1231231960年1月1日2024/5/22 周三22226、根据COUNTIF函数的统计功能特性,可与IF函数组合为查重公式:在统计全队职工中,查找并标记重复职工:=IF(COUNTIF($J$4:$J$375,J4)1,重复,)公式运行:公式运行:在$J$4:$J$375单元格区域内查找等于J4的个数,如果1,说明J4有重复,因此显示“重复”,否则不显示(即单元格为空)注意:注意:单元格区域使用了绝对引用(($J$4:$J$375),即行与列均为绝对引用,目的是固定单元格区域,使向下复制粘贴公式时,不至于因相对引用造成单元格区域出错而
21、使结果错误。9、查找标记重复职工案例2024/5/22 周三2323四、函数名称:四、函数名称:COUNTIFS主要功能:主要功能:用于对某一区域内满足多重条件的单元格进行计数(多条件计数)。使用格式:使用格式:=countifs(第一个条件区,第一个对应的条件,第二个条件区,第二个对应的条件,第N个条件区,第N个条件对应的条件)说明:说明:“条件”:它的形式可以为数字、表达式或文本。当它是文本和表达式时,注意要使用双引号。且引号在英文状态下输入。技巧:技巧:当要选取的条件区域(每列或每行)数据特别多时,用鼠标拖动选取既费时又费力,很麻烦,建议大家通过ctrl+shift+选中10、多条件计数
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 常用 函数 在工作中 应用
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【a199****6536】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【a199****6536】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。