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、MIN2、新手级比菜鸟会用更多的函数，会使用IF函数进行简单判断、会对数字进行四舍五入运算。IF、COUNT、LARDE、ROUND、INT、TODAY3、入门级对函数已有初步了解，熟悉最常用函数的使用方法。IFERROR、VLOOKUP、SUMIF、SUMPRODUCT4、初级已能熟练使用更多常用函数的公式设置，但对函数的深入用法不够了解。MATCH、DATEDIF、SUMIFS、COUNTIFS5、中级这部分用户在单位里已属函数高手级别，已能解决复杂的函数问题，但公式优化还需学习。OFFSET、INDIRECT、LOOKUP、TXTE6、高级对Excel函数已达到很高的境界，已能运用数组公式解决复杂的数据计算和处理。FREQUENGY、MMULT、T/N、SUBSTOTAL7、大师级不用说了，这类是函数高手中的高手，没有他写不出的公式，解题思路已成系统。二分法、三维引用、数组维数转换,一、函数名称：IF,主要功能：根据对指定条件的判断的是否成立，返回条件成立后的执行内容。使用格式：=IF(条件表达式,“条件成立时显示或执行的内容“,“条件不成立时显示或执行的内容)说明：1、所有Exce函数公式的等号、括号、逗号均为英文状态下输入。2、office2003可嵌套7层；office2007嵌套64层。=IF(逻辑判断表达式1,当判断条件成立时显示或执行的内容,IF(逻辑判断表达式2,当判断条件成立时显示或执行的内容,当判断条件不成立时显示或执行的内容),IF函数应用实例一,进行校对：通过函数公式对2人及2人以上编辑的相同电子表文档数据进行校对，并对不正确的数据进行标注。=IF(原文档G4=用于校对!G4,正确,不正确)即：在原文档相应单元格中输入公式：=IF(原文档中需要进行校对的数据单元格=另一文档中的该数据单元格，“若相等显示：正确”,“若不相等显示：不正确)适用于表间单元格内容校对1、进行校对案例,IF函数应用实例二,根据指定类别，显示对应内容：根据党员类别和工资收入，自动选择并显示对应缴费比例或金额。党费收缴标准如下：,在“比例”单元格内输入：方法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=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=1983/1/1),说明：1）统计1960年1月1日（不含）以前参加工作的职工人数时，计算机在执行日期计算时，均是按日期的系列天数进行运算的，1960年以后参加工作的系列天数，一定更大，即日期大的工龄小；日期小的工龄大。2）以某一日期节点统计前后数量时，一定要注意是包含本数，还是不包含本数。,特别注意：“本数”只能包含一次，且只能不包含一次：即统计=本数与本数与本数=本数)：正确;。若统计=本数与本数与=本数(本数)：错误。错误结果：1）=本数与=本数)则会出现：本数重复统计；2）本数与本数)则会出现：本数未被统计；,因此，“=本数”，一定与“本数”匹配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日出生人数；若“”或“”与“区间1,重复,)公式运行：在$J$4:$J$375单元格区域内查找等于J4的个数，如果1，说明J4有重复，因此显示“重复”，否则不显示(即单元格为空)注意：单元格区域使用了绝对引用（($J$4:$J$375），即行与列均为绝对引用，目的是固定单元格区域，使向下复制粘贴公式时，不至于因相对引用造成单元格区域出错而使结果错误。9、查找标记重复职工案例,四、函数名称：COUNTIFS,主要功能：用于对某一区域内满足多重条件的单元格进行计数（多条件计数）。使用格式：=countifs（第一个条件区，第一个对应的条件，第二个条件区，第二个对应的条件，第N个条件区，第N个条件对应的条件）说明：“条件”：它的形式可以为数字、表达式或文本。当它是文本和表达式时，注意要使用双引号。且引号在英文状态下输入。技巧：当要选取的条件区域（每列或每行）数据特别多时，用鼠标拖动选取既费时又费力，很麻烦，建议大家通过ctrl+shift+选中10、多条件计数函数应用案例,五、函数名称：AND,主要功能：返回逻辑值：如果所有参数值均为逻辑“真(TRUE)”，则返回逻辑“真(TRUE)”，反之返回逻辑“假(FALSE)”使用格式：=AND(条件1,条件2,.)条件判断均成立为“真(TRUE)”，否则为“假(FALSE)”Excel2003版本AND函数支持最多30个逻辑表达式Excel2007版本AND函数支持最多255个逻辑表达式适用范围：与其他函数组合使用。常用于多条件查询、多条件计算、汇总。说明：在函数公式中，“&”(英语词意为：AND)常用作连接文本或数据,六、函数名称：OR,主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”使用格式：=OR(条件1,条件2,.)条件判断均不成立为“假(FALSE)”，否则为“真(TRUE)”适用范围：与其他函数组合使用。常用于多条件查询、多条件计算、汇总。,AND和OR函数应用实例,条件：假设：副高五级或副高六级，工龄满30年，且学历为本科的职工，每月增加补贴200元。公式：=IF(AND(OR(AJ4=“副高五级”,AJ4=副高六级),AL4=30,AK4=本科),200,0)说明：1、OR(AJ4=“副高五级”,AJ4=“副高六级)：OR后的2个条件只要有一个条件成立即可；2、AND(OR(AJ4=“副高五级”,AJ4=“副高六级),AL4=30,AK4=”本科“)：AND后的3个条件必须全部条件同时成立才可。12、AND和OR函数应用案例,七、函数名称：ROW(),主要功能：提取当前或指定单元格的行标数字。使用格式：1、提取当前单元格的行标数字=ROW()2、提取指定单元格的行标数字=ROW(指定单元格)适用范围：1）在列自动插入序列号；2）需要当前行号参与计算时。,ROW()函数应用实例,1、在第5行输入：“=row()”，显示为：52、在第5行输入：“=row(C10)”，显示为：10作业题：1、请设置ROW()函数公式，要求从B列的B18单元格开始，将所设置的ROW()函数公式向下复制，能自动从1开始，向下充填连续序列号。2、利用ROW()功能，在C17输入1后，请在C18设置ROW()函数公式，要求：当在D18及以下单元格中连续输入任何非空数据后，对应的C18及以下单元格内，会对应自动显示连续序列号。技巧：使隐藏的行不参与序号的编号，并使已显示的序号为连续序号（A1为序号栏，B1为姓名栏，在A2输入下列公式）=IF(B2=,SUBTOTAL(103,INDIRECT(B2:B&ROW()13、自动提取或充填行列标字母或序列数字案例,八、函数名称：COLUMN(),主要功能：提取当前或指定单元格的列标数字。使用格式：1、提取当前单元格的列标数字=COLUMN()2、提取指定单元格的列标数字=COLUMN(指定单元格)适用范围：1）在行自动插入序列号；2）需要当前列号参与计算时。,COLUMN()函数应用实例,1、在C列第2行输入：“=column()”，显示为：32、在C列第3行输入：“=column(E2)”，显示为：5作业题：1、请根据ROW()和COUMN()函数功能，设置乘法九九表公式，并向下、向右复制充填到其他单元格，要求充填的行列数超过9个。2、将ROW()和COUMN()函数与已学习过的函数进行公式组合设置，使乘法九九表更加美观。即：从A1开始，不论将设置好的公式复制到任何位置，均自动按三角形格式显示乘法九九表14、乘法九九表练习案例15、乘法九九表中IF、AND、OR函数嵌套逻辑关系及运行结果图,九、函数名称：LEFT、MID、RIGHTFIND,主要功能：LEFT从单元格左边第1个开始，向右截取指定数目的字符MID从单元格指定位置开始，向右截取指定数目的字符RIGHT从单元格最后一个字符开始，向左截取指定数目的字符FIND从指定单元格的指定位置，开始查找指定字符使用格式：=LEFT(单元格或字符串，从左第1个字符开始截取的数目)=MID(单元格或数据，指定位置，向右截取的数目)=RIGHT(单元格或数据，从右第1个字符开始截取的数目)=FIND(要查找的字符，字符所在单元格，从左第几位开始查找，省略为1)=LEFT(A1,FIND(“&”,A1)-1)提取A1单元格中“&”以前的字符=MID(A1,FIND(“&”,A1)+1,100)提取A1单元格中“&”后的字符适用范围：需要提取单元格中指定位置的字符，进行计算或判定。16、提取指定字符案例,十、函数名称：INT,主要功能：将数值向下取整为最接近的整数。使用格式：=INT(要取整的数值或包含数值、表达式的引用单元格)适用范围：1）需要进行取整计算时；2）需要设置精确小数位参与计算时：精确到小数点后第4位=INT(D4*10000)+0.5)/10000(第5位四含五入)精确到小数点后第5位=INT(D4*100000)+0.5)/100000(第6位四含五入)以此类推特别提醒：向下取整，即取最小整数：1、0时，不论小数点后的数字是多少，一律只取小数点左边的整数；2、0时，不论小数点后的数字是多少，一律向小数点左边进位再取整数；,十一、函数名称：ROUND,主要功能：将数值四舍五入。使用格式：=ROUND(需进行四舍五入数值或单元格，保留小数点的位数)适用范围：1）需要进行指定小数位计算时；2）需要设置保留小数位参与计算时：保留5位小数点=ROUND(563.878628,5)(结果：563.87863，即保留5位小数，第6位四含五入)不保留数点=ROUND(563.878628,0)(结果：564，即不保留小数，小数点后第1位四含五入)保留-1位小数点=ROUND(563.878628,-1)(结果：560，即小数点左第1位四含五入)以此类推特别提醒：当设定小数位：1、大于0，在小数点右侧指定的进行四舍五入。2、等于0，四舍五入到最接近的整数。3、小于0，在小数点左侧指定的进行四舍五入。17、数据取整及精确小数位案例,十二、函数名称：LARGE、MAX,主要功能：LARGE提取指定单元格区域第N个最大值。MAX提取指定单元格区域的最大值。使用格式：=LARGE(单元格区域,显示第N个最大值)=MAX(单元格区域)适用范围：1、需要使用指定单元格区域的第N个最大值参与计算；2、需要使用指定单元格区域的最大值参与计算。18、非空、最大最小值、排名、随机函数应用案例,十三、函数名称：SMALL、MIN,主要功能：SMALL提取指定单元格区域的第N个最小值。MIN提取指定单元格区域的最小值。使用格式：=SMALL(单元格区域,显示第N个最小值)=MIN(单元格区域)适用范围：1、需要使用指定的第N个最小值参与计算；2、需要使用指定单元格区域的最大值参与计算。19、非空、最大最小值、排名、随机函数应用案例,十四、函数名称：RAND()与RANDBETWEEN、CHAR,主要功能：产生随机数、随机字母。使用格式：1、=RAND()产生01之间的随机数；2、=RAND()*90产生090之间的随机数；3、=RAND()*90+10产生10100之间的随机数；4、=RAND()*(n-m)+m产生MN之间的随机数；5、=ROUND(最小值+(最大值-最小值)*rand(),3)产生指定最小值最大值之间，且保留3位小数的随机数；6、=RANDBETWEEN(最小整数,最大整数)产生指定最小整数最大整数之间的随机数；7、=CHAR(INT(RAND()*26)+97)产生随机小写字母；8、=CHAR(INT(RAND()*26)+65)产生随机大写字母。适用范围：1、需要通过大量数据对公式进行检验时；2、需要随机数参与计算时。20、非空、最大最小值、排名、随机函数应用案例,十五、函数名称：RANK、ABS,主要功能：RANK将指定数据，在指定绝对单元格区域中进行排位。ABS求出相应数字或表达式的绝对值；使用格式：=RANK(需排位单元格或数据，排位绝对单元格区域)=ABS(单元格或数据)适用范围：RANK需要使用指定的第N个最小值参与计算；ABS需要使用绝对值计算时。注意：对某单元或数据，在某区域进行排位时，该区域一定是绝对引用，否则会报错。21、非空、最大最小值、排名、随机函数应用案例,十六、函数名称：LEN、MOD,主要功能：LEN计算单元格或字符串长度MOD求出两数相除的余数使用格式：=LEN(单元格或字符串)=MOD(被除数，除数)适用范围：1、LEN计算单元格或字符串长度，以确保参与计算时的结果正确时。2、MOD需要用2数的余数时；需要将余数作为判定条件（如余数的奇偶性），参与其他函数运算时；22、学历性别民族统计案例,十七、函数名称：SUBSTITUTE,主要功能：从单元格指定位置开始，用指定字符替换指定数目的字符MID从单元格指定位置开始，向右截取指定数目的字符RIGHT从单元格最后一个字符开始，向左截取指定数目的字符使用格式：=SUBSTITUTE(需替换的单元格,被替换位置和数目,“替换后的符号串”)实例:=SUBSTITUTE(K4,LEFT(K4,6),“*”)从K4单元格左边第1个字符开始，向右用*替换6个字符)=SUBSTITUTE(K4,RIGHT(K4,6),“*”)从K4单元格左边第1个字符开始，向左用*替换6个字符)=SUBSTITUTE(K4,MID(K4,6,6),“*”)从K4单元格左边第6个字符开始，向右用*替换6个字符)适用范围：需要保护隐私或重要内容时，如火车票的身份证号码部分被替换隐藏。,23、替换隐藏部分内容案例,十八、函数名称：VLOOKUP、LOOKUP,主要功能：VLOOKUP在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。LOOKUP（条件，条件所在区域，要显示内容所在区域）使用格式：=VLOOKUP(查找值，查找范围，查找列数，精确匹配false或0或者近似匹配true或1）=LOOKUP(查找值，查找值所在范围，显示对应列数值）VLOOKUP与LOOKUP异同：Vlookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行，并且Vlookup可以选择采用精确查询或是模糊查询方式，而Lookup只有模糊查询。,特别提醒：LOOKUP“查找值”必须在“查找范围”的首列中；如果忽略匹配参数，则“查找范围”的首列必须进行升序排序，否则出错。VLOOKUP：无须升序排序。适用范围：通过与“查找值”精确匹配后，显示对应的某列单元格数据。24、LOOKUP、VLOOKUP查询案例25、非空、最大最小值、排名、随机函数应用案例（LOOKUP数组函数取值）26、VLOOKUP的高级使用（数组二分法）：自动从单元格中提取手号码,十九、函数名称：SUMIF、SUMIFS,主要功能：SUMIF条件判断区域，单条件，在求和区域按条件求和。SUMIFS统计求和区域，分别按条件区域1、条件1；条件区域2，条件2对统计求和区域进行求和。使用格式：=SUMIF(条件判断区域,条件,求和区域)=SUMIFS(统计求和区域,第一条件区域,条件,第二条件区域,第二条件.)注意：SUMIF的求和区域在公式最后SUMIFS的求和区域在公式最前适用范围：根据条件进行求和计算，不需经过分类汇总。27、多条件求和案例,二十、函数名称：DATEDIF,主要功能：2个日期的间隔计算。使用格式：1、间隔年数=DATEDIF(开始日期或开始日期所在单元格,结束日期或结束日期所在单元格,”y”)2、间隔月数=DATEDIF(开始日期或开始日期所在单元格,结束日期或结束日期所在单元格,”m”)3、间隔日数=DATEDIF(开始日期或开始日期所在单元格,结束日期或结束日期所在单元格,”d”)说明：“Y”：时间段中的整年数。“M”：时间段中的整月数。“D”：时间段中的天数。“MD”：2个日期中天数的差。忽略日期中的月和年。YM：2个日期中月数的差。忽