Excel常用函数在工作中的应用

EXCEL常用函数在工作中的应用闵华荣2016年1月3日EXCEL函数概述Excel是微软office办公软件的一个重要组成部分，具有强大的计算统计功能，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计、财经、金融等众多领域。Excel中的函数功能，可以执行指定计算，分析信息，尤其是Excel函数的组合使用，可以使十分复杂的计算、统计、查询、排版和管理，简便化，起到事半功倍的效果。EXCEL函数公式编写规则1、所有的公式必须在英文状态下输入，并以等号开头；2、函数名，如IF，均写在等号的后面再加一个括号，如=IF(A2=B2,”正确”,”不正确”)；3、公式的函数名和单元格的列号，都不区分大小写；4、在Excel的公式嵌套中，只使用小括号。嵌套时，新嵌套进去的公式，也要把函数名写在最前面、后加括号；5、当公式引用的单元格发生变化时，公式会自动重新计算结果，并显示在输入公式的单元格内；6、注意绝对引用（$A$5）、相对引用（A5）及混合引用（$A5与A$5）的作用：凡有$符号的，$右边的字母（即列）或数字（即行），复制时，其位置是不变的，反之就会改变。7、关于循环引用：若确实需要循环引用同一单元格或单元区域，则必须Excel选项中开启迭代运算选项时，否则不能进行循环引用公式的计算，并自动提示报错。8、在编辑栏中输入等号和函数英文字母时，电脑会自动在编辑栏下方逐字显示相应函数命令，供你选择。EXCEL函数公式一般格式不同函数公式，有不同的格式要求，必须按格式要求设置公式，尤其要注意函数公式中的嵌套逻辑关系，否则报错：1、=IF(条件，条件成立时执行的内容,条件不成立时执行的内容)；2、=IF(AND(OR(G4=条件1,G4=条件2),I4=30,H4=条件3),满足条件执行的内容,满足条件执行的内容)3、=sum(需要进行汇总的数据区域)；4、=COUNTIF(统计区域,“统计条件”)；5、=LOOKUP(查找条件,查找条件所在区域,满足条件的对象所在区域)；6、=SUMIFS(汇总数据区域,条件1所在区域,条件1,条件2所在区域,条件2,条件3所在区域,条件3,……条件n所在区域,条件n)…………在此不一一例举。以下介绍函数时，均会介绍函数的用途、格式等。学习EXCEL函数公式解决问题的简要方法1、掌握常用函数的命令、格式、用途、各参数要求（包括函数的优势与不足）；2、掌握各常用函数混编组合公式的格式与逻辑关系，尤其是各参数、分隔逗号、括号、绝对引用、相对引用、混合引用、逻辑或、逻辑且等组合元素的位置与层次关系（技巧：由最里逐级向外，分清每一函数公式的层次，在该层次中，必须符合该函数的格式要求，括号必须对应成对，注：用鼠标在编辑栏选中全部公式后，凡对应成对的括号，均为同一颜色）；3、在构思函数公式前，必须先明确：要解决什么问题或存在什么问题，有什么已知条件，再判断需要使用什么函数或函数组合。类似于平面几何证明题：可以从已知条件入手，逐步推进，寻找解决办法；也可从问题入手，逆向分解，寻找规律，解决问题。特别说明：与平面几何证明题一样，用函数公式解决工作当中的实际问题的方法，也有2种以上的方法，所不同的是：使用的函数不同、编写的公式长短不同、公式的繁简不同。你的EXCEL函数水平有多高？1、菜鸟级会使用最常简的求和、求平均数函数SUM、AVERSE、MAX、MIN2、新手级比菜鸟会用更多的函数，会使用IF函数进行简单判断、会对数字进行四舍五入运算。IF、COUNT、LARDE、ROUND、INT、TODAY3、入门级对函数已有初步了解，熟悉最常用函数的使用方法。IFERROR、VLOOKUP、SUMIF、SUMPRODUCT4、初级已能熟练使用更多常用函数的公式设置，但对函数的深入用法不够了解。MATCH、DATEDIF、SUMIFS、COUNTIFS5、中级这部分用户在单位里已属函数高手级别，已能解决复杂的函数问题，但公式优化还需学习。OFFSET、INDIRECT、LOOKUP、TXTE6、高级对Excel函数已达到很高的境界，已能运用数组公式解决复杂的数据计算和处理。FREQUENGY、MMULT、T/N、SUBSTOTAL7、大师级不用说了，这类是函数高手中的高手，没有他写不出的公式，解题思路已成系统。二分法、三维引用、数组维数转换一、函数名称：IF主要功能：根据对指定条件的判断的是否成立，返回条件成立后的执行内容。使用格式：=IF(条件表达式,“条件成立时显示或执行的内容“,“条件不成立时显示或执行的内容)说明：1、所有Exce函数公式的等号、括号、逗号均为英文状态下输入。2、office2003可嵌套7层；office2007嵌套64层。=IF(逻辑判断表达式1,当判断条件成立时显示或执行的内容,IF(逻辑判断表达式2,当判断条件成立时显示或执行的内容……,当判断条件不成立时显示或执行的内容))IF函数应用实例一进行校对：通过函数公式对2人及2人以上编辑的相同电子表文档数据进行校对，并对不正确的数据进行标注。=IF(原文档G4=用于校对!G4,正确,不正确)即：在原文档相应单元格中输入公式：=IF(原文档中需要进行校对的数据单元格=另一文档中的该数据单元格，“若相等显示：正确”,“若不相等显示：不正确)适用于表间单元格内容校对1、进行校对案例IF函数应用实例二根据指定类别，显示对应内容：根据党员类别和工资收入，自动选择并显示对应缴费比例或金额。党费收缴标准如下：党费收缴标准党员类别工资收入缴费比例或金额家属、下岗无工资收入0.2～1.00元在岗≤30000.50%＞3000，≤50001.00%＞5000，≤100001.50%＞100002.00%离退休≤50000.50%＞50001.00%在“比例”单元格内输入：方法1、以单“人员类别”=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%))),IF(OR(G12=家属,G12=下岗),,IF(G12=离退休,IF(M12=5000,0.5%,1.0%))))。对在册党员、家属、下岗和离退休党员均有效。2、党费缴费比例及金额自动充填计算案例说明：1、如果采用方法1和方法2，则需依据当前人员类别，输入相应的函数公式。不可将方法1或方法2复制到整个表的“比例”栏，否则报错：显示为FALSE。2、方法3可适用整个工作表，可将方法3复制到整个表的“比例”栏。原因是：在方法1和方法2的函数公式中，没有包涵所有人员类别。因IF函数在指定单元格中找不到设定的人员类别参数而报错。而方法3函数公式的参数设置涵盖了整个工作表，因此更适用。1、1934年9月30日前出生增100元；2、1934年10月1日—1939年9月30日期间出生增60元第一列：=IF(D4=,0,IF(D4=DATE(1934,9,30),100,0))第二列：=IF(D4=,0,IF(AND(D4DATE(1934,9,30),D4=DATE(1939,9,30)),60,0))二列合一：=IF(D39=,0,IF(D39=DATE(1934,9,30),100,IF(D39=,0,IF(AND(D39DATE(1934,9,30),D39=DATE(1939,9,30)),60,0))))3、根据出生年月日自动判定增加离退休费案例IF函数应用实例三IF函数应用实例四根据分数段，显示等级：根据综合考核成绩所在区段，对应显示等级：4、综合考核统计案例序号成绩区间等级备注10～59不称职260～74基本称职375～84称职484～100优秀公式1：条件降序=IF(C4=85,优秀,IF(C4=74,称职,IF(C4=60,基本称职,不称职)))公式2：条件升序=IF(C4=59,不称职,IF(C4=74,基本称职,IF(C4=84,称职,优秀)))公式3：条件乱序（报错）=IF(C6=59,不称职,IF(C6=84,称职,IF(C6=74,基本称职,优秀)))原因：若“条件”属序列关系，则必须采用升序或降序设置IF函数公式，乱序则出错。因为函数公式运行规则是：自左到右，即先执行IF(C6=84,“称职”……），再执行IF(C6=74,“基本称职“……），而C6=84就包括了C6=74，犯了逻辑错误，因此不显示60～74成绩区间的“基本称职”。设置IF函数公式注意事项：1、要使IF函数公式适用于整个工作表，其采用的条件关键词，必须涵盖整个工作表。2、嵌套的层次关系要明晰，并符合IF函数公式的格式要求。技巧是：1、以第一个IF，按函数格式逻辑关系进行审核，然后审核第二个IF，经此类推。2、用鼠标在编辑状态栏内选中整个公式（同一级成对的括号为同色），由内向外以左、右括号成对判定其位置是否正确。5、IF函数嵌套逻辑关系图二、函数名称：COUNT、COUNTA、COUNTIF主要功能：COUNT统计某个单元格区域中数字单元格数据COUNTA统计某个单元格区域中非空单元格数据COUNTIF统计某个单元格区域中符合指定条件的单元格数目。使用格式：=COUNT（单元格区域，或文本、或数字）=COUNTA（单元格区域，或文本、或数字）注：以上2函数：若括号内有文本、或数字，则和单元格区域一并参与统计）=COUNTIF(要统计的单元格区域,给定条件)适用范围：需要使用统计区域的数字单元格个数、非空单元格个数、或符合给定条件的单元格个数参与计算的。6、用COUNT、COUNTA、COUNTIF函数分别统计案例COUNTIF函数应用实例一根据给定条件，在指定单元格区域内统计符合条件的数量：1、在统计全队职工中，女职工人数：=countif(表“性别”所在列,女)2、在统计全队职工中，少数民族职工人数：=countif(表“民族”所在列,“*”)-countif(表“民族”所在列,“汉族“)说明：“*”是通配符，在此表示统计“民族”所在列的所有人数，减去“汉族”人数后，即为少数民族人数，即“排他法”。7、学历性别民族统计案例3、以1960年1月1日为时间节点，统计全队职工：1）1960年1月1日(不含)以前参加工作的职工人数：=COUNTIF(AG$4:AG$373,1960/1/1)2）1960年1月1日(含)以后参加工作的职工人数：=COUNTIF(AG$4:AG$373,=1960/1/1)注意：1、单元格区域使用了混合引用（O$4:O$375），即行为绝对引用，列为相对引用，目的是为了输入方便快捷，因行数较多，拖动选择很费事，通过复制粘贴，再更改所需列标字母即可。2、提示：COUNTIF函数中的日期格式为：1960/1/1。IF函数中的日期格式为：DATE(1960,1,1)8、按参加工作时间段统计人数案例4、统计1960年1月1日(不含)以前及1983年1月1日(含)后参加工作的（两个时间段）人数：=COUNTIF(AG$4:AG$373,1960/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)说明：1）统计1960年1月1日（不含）以前