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

EXCEL常用函数在工作中的应用常用函数在工作中的应用闵华荣闵华荣20162016年年1 1月月3 3日日2024/5/22 周三1 1EXCEL函数概述函数概述Excel是微软office办公软件的一个重要组成部分，具有强大的计算统计功能，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计、财经、金融等众多领域。Excel中的函数功能，可以执行指定计算，分析信息，尤其是Excel函数的组合使用，可以使十分复杂的计算、统计、查询、排版和管理，简便化，起到事半功倍的效果。2024/5/22 周三2 2EXCEL函数公式编写规则函数公式编写规则1、所有的公式必须在英文状态下输入，并以等号开头；2、函数名，如IF，均写在等号的后面再加一个括号，如=IF(A2=B2,”正确”,”不正确”)；3、公式的函数名和单元格的列号，都不区分大小写；4、在Excel的公式嵌套中，只使用小括号。嵌套时，新嵌套进去的公式，也要把函数名写在最前面、后加括号；5、当公式引用的单元格发生变化时，公式会自动重新计算结果，并显示在输入公式的单元格内；6、注意绝对引用（$A$5）、相对引用（A5）及混合引用（$A5与A$5）的作用：凡有$符号的，$右边的字母（即列）或数字（即行），复制时，其位置是不变的，反之就会改变。7、关于循环引用：若确实需要循环引用同一单元格或单元区域，则必须 Excel选项中开启迭代运算选项时，否则不能进行循环引用公式的计算，并自动提示报错。8、在编辑栏中输入等号和函数英文字母时，电脑会自动在编辑栏下方逐字显示相应函数命令，供你选择。2024/5/22 周三3 3EXCEL函数公式一般格式函数公式一般格式 不同函数公式，有不同的格式要求，必须按格式要求设置公式，尤其要注意函数公式中的嵌套逻辑关系，否则报错：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)在此不一一例举。以下介绍函数时，均会介绍函数的用途、格式等。2024/5/22 周三4 4学习学习EXCEL函数公式解决问题的简要方法函数公式解决问题的简要方法1、掌握常用函数的命令、格式、用途、各参数要求（包括函数的优势与不足）；2、掌握各常用函数混编组合公式的格式与逻辑关系，尤其是各参数、分隔逗号、括号、绝对引用、相对引用、混合引用、逻辑或、逻辑且等组合元素的位置与层次关系（技巧：由最里逐级向外，分清每一函数公式的层次，在该层次中，必须符合该函数的格式要求，括号必须对应成对，注：用鼠标在编辑栏选中全部公式后，凡对应成对的括号，均为同一颜色）；3、在构思函数公式前，必须先明确：要解决什么问题或存在什么问题，有什么已知条件，再判断需要使用什么函数或函数组合。类似于平面几何证明题：可以从已知条件入手，逐步推进，寻找解决办法；也可从问题入手，逆向分解，寻找规律，解决问题。特别说明：特别说明：与平面几何证明题一样，用函数公式解决工作当中的实际问题的方法，也有2种以上的方法，所不同的是：使用的函数不同、编写的公式长短不同、公式的繁简不同。2024/5/22 周三5 5你的你的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、大师级、大师级不用说了，这类是函数高手中的高手，没有他写不出的公式，解题思路已成系统。二分法、三维引用、数组维数转换2024/5/22 周三6 6一、函数名称：一、函数名称：IFIF主要功能：根据对指定条件的判断的是否成立，返回条件成立后的执行内容。使用格式：=IF(条件表达式,“条件成立时显示或执行的 内容“,“条件不成立时显示或执行的内容)说明：1、所有Exce函数公式的等号、括号、逗号均为英文状态下输入。2、office2003可嵌套7层；office2007嵌套64层。=IF(逻辑判断表达式1,当判断条件成立时显示或执行的内容,IF(逻辑判断表达式2,当判断条件成立时显示或执行的内容,当判断条件不成立时显示或执行的内容)2024/5/22 周三7 7IFIF函数应用实例一函数应用实例一进行校对：通过函数公式对2人及2人以上编辑的相同电子表文档数据进行校对，并对不正确的数据进行标注。=IF(原文档G4=用于校对!G4,正确,不正确)即：在原文档相应单元格中输入公式：=IF(原文档中需要进行校对的数据单元格=另一文档中的该数据单元格，“若相等显示：正确”,“若不相等显示：不正确)适用于表间单元格内容校对适用于表间单元格内容校对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、以单、以单“人员类别人员类别”=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、党费、党费缴费比例及金额自动充填缴费比例及金额自动充填计算案例计算案例2024/5/22 周三1010说明：说明：1、如果采用方法1和方法2，则需依据当前人员类别，输入相应的函数公式。不可将方法1或方法2复制到整个表的“比例”栏，否则报错：显示为FALSE。2、方法3可适用整个工作表，可将方法3复制到整个表的“比例”栏。原因是：原因是：在方法1和方法2的函数公式中，没有包涵所有人员类别。因IF函数在指定单元格中找不到设定的人员类别参数而报错。而方法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(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，按函数格式逻辑关系进行审核，然后审核第二个IF，经此类推。2、用鼠标在编辑状态栏内选中整个公式（同一级成对的括号为同色），由内向外以左、右括号成对判定其位置是否正确。5、IF函数嵌套逻辑关系图函数嵌套逻辑关系图2024/5/22 周三1515二、函数名称：二、函数名称：COUNTCOUNT、COUNTACOUNTA、COUNTIFCOUNTIF主要功能：COUNTCOUNT统计某个单元格区域中数字单元格数据 COUNTA COUNTA统计某个单元格区域中非空单元格数据 COUNTIF COUNTIF统计某个单元格区域中符合指定条件的单元格数目。使用格式：=COUNT（单元格区域，或文本、或数字）=COUNTA（单元格区域，或文本、或数字）注：注：以上2函数：若括号内有文本、或数字，则和单元格区域一并参与统计）=COUNTIF(要统计的单元格区域,给定条件)适用范围：需要使用统计区域的数字单元格个数、非空单元格个数、或符合给定条件的单元格个数参与计算的。6、用COUNT、COUNTA、COUNTIF函数分别统计案例2024/5/22 周三1616COUNTIFCOUNTIF函数应用实例一函数应用实例一根据给定条件，在指定单元格区域内统计符合条件的数量：1、在统计全队职工中，女职工人数：=countif(表“性别”所在列,女)2、在统计全队职工中，少数民族职工人数：=countif(表“民族”所在列,“*”)-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、提示：提示：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）统计1960年1月1日（不含）以前参加工作的职工人数时，计算机在执行日期计算时，均是按日期的系列天数进行运算的，1960年以后参加工作的系列天数，一定更大，即日期大的工龄小；日期小的工龄大。2）以某一日期节点统计前后数量时，一定要注意是包含本数，还是不包含本数。2024/5/22 周三2020特别注意：特别注意：“本数”只能包含一次，且只能不包含一次：即统计=本数与本数与=本数)：正确;或统计本数=本数)：正确;。若统计=本数与本数与本数)：错误;或统计=本数(本数)：错误。错误结果：1）=本数与=本数(或=本数)则会出现：本数重复统计；2）本数与本数(或本数)则会出现：本数未被统计；2024/5/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日出生人数；若“”或“”与“”配对，则统计会因漏“=”，漏统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），即行与列均为绝对引用，目的是固定单元格区域，使向下复制粘贴公式时，不至于因相对引用造成单元格区域出错而使结果错误。9、查找标记重复职工案例2024/5/22 周三2323四、函数名称：四、函数名称：COUNTIFS主要功能：主要功能：用于对某一区域内满足多重条件的单元格进行计数（多条件计数）。使用格式：使用格式：=countifs（第一个条件区，第一个对应的条件，第二个条件区，第二个对应的条件，第N个条件区，第N个条件对应的条件）说明：说明：“条件”：它的形式可以为数字、表达式或文本。当它是文本和表达式时，注意要使用双引号。且引号在英文状态下输入。技巧：技巧：当要选取的条件区域（每列或每行）数据特别多时，用鼠标拖动选取既费时又费力，很麻烦，建议大家通过ctrl+shift+选中10、多条件计数函数应用案例2024/5/22 周三2424五、函数名称：五、函数名称：AND AND主要功能：主要功能：返回逻辑值：如果所有参数值均为逻辑“真(TRUE)”，则返回逻辑“真(TRUE)”，反之返回逻辑“假(FALSE)”使用格式：使用格式：=AND(条件1,条件2,.)条件判断均成立为“真(TRUE)”，否则为“假(FALSE)”Excel2003版本AND函数支持最多30个逻辑表达式Excel2007版本AND函数支持最多255个逻辑表达式适用范围：适用范围：与其他函数组合使用。常用于多条件查询、多条件计算、汇总。说明：说明：在函数公式中，“&”(英语词意为：AND)常用作连接文本或数据2024/5/22 周三2525六、函数名称：六、函数名称：OR OR主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”使用格式：=OR(条件1,条件2,.)条件判断均不成立为“假(FALSE)”，否则为“真(TRUE)”适用范围：与其他函数组合使用。常用于多条件查询、多条件计算、汇总。2024/5/22 周三2626ANDAND和和OROR函数应用实例函数应用实例条件：条件：假设：副高五级或副高六级，工龄满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函数应用案例2024/5/22 周三2727七、函数名称：七、函数名称：ROW()ROW()主要功能：提取当前或指定单元格的行标数字。使用格式：1、提取当前单元格的行标数字=ROW()2、提取指定单元格的行标数字=ROW(指定单元格)适用范围：1）在列自动插入序列号；2）需要当前行号参与计算时。2024/5/22 周三2828ROW()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、自动提取或充填行列标字母或序列数字案例2024/5/22 周三2929八、函数名称：八、函数名称：COLUMN()COLUMN()主要功能：提取当前或指定单元格的列标数字。使用格式：1、提取当前单元格的列标数字=COLUMN()2、提取指定单元格的列标数字=COLUMN(指定单元格)适用范围：1）在行自动插入序列号；2）需要当前列号参与计算时。2024/5/22 周三3030COLUMN()COLUMN()函数应用实例函数应用实例1、在C列第2行输入：“=column()”，显示为：32、在C列第3行输入：“=column(E2)”，显示为：5作业题：作业题：1、请根据ROW()和COUMN()函数功能，设置乘法九九表公式，并向下、向右复制充填到其他单元格，要求充填的行列数超过9个。2、将ROW()和COUMN()函数与已学习过的函数进行公式组合设置，使乘法九九表更加美观。即：从A1开始，不论将设置好的公式复制到任何位置，均自动按三角形格式显示乘法九九表14、乘法九九表练习案例15、乘法九九表中IF、AND、OR函数嵌套逻辑关系及运行结果图2024/5/22 周三3131九、函数名称：九、函数名称：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、提取指定字符案例2024/5/22 周三3232十、函数名称：十、函数名称：INTINT主要功能：将数值向下取整向下取整为最接近的整数。使用格式：=INT(要取整的数值或包含数值、表达式的引用单元格)适用范围：1）需要进行取整计算时；2）需要设置精确小数位参与计算时：精确到小数点后第4位=INT(D4*10000)+0.5)/10000(第5位四含五入)精确到小数点后第5位=INT(D4*100000)+0.5)/100000(第6位四含五入)以此类推特别提醒：向下取整，即取最小整数：向下取整，即取最小整数：1、0时，不论小数点后的数字是多少，一律只取小数点左边的整数；时，不论小数点后的数字是多少，一律只取小数点左边的整数；2、0时，不论小数点后的数字是多少，一律向小数点左边进位再取整数；时，不论小数点后的数字是多少，一律向小数点左边进位再取整数；2024/5/22 周三3333十一、函数名称：十一、函数名称：ROUNDROUND主要功能：将数值四舍五入。使用格式：=ROUNDROUND(需进行四舍五入数值或单元格，保留小数点的位数)适用范围：1）需要进行指定小数位计算时；2）需要设置保留小数位参与计算时：保留5位小数点=ROUNDROUND(563.878628,5)(结果：563.87863，即保留5位小数，第6位四含五入)不保留数点=ROUNDROUND(563.878628,0)(结果：564，即不保留小数，小数点后第1位四含五入)保留-1位小数点=ROUNDROUND(563.878628,-1)(结果：560，即小数点左第1位四含五入)以此类推特别提醒：当设定小数位：1、大于 0，在小数点右侧指定的进行四舍五入。2、等于 0，四舍五入到最接近的整数。3、小于 0，在小数点左侧指定的进行四舍五入。17、数据取整及精确小数位案例 2024/5/22 周三3434十二、函数名称：十二、函数名称：LARGE、MAX主要功能：LARGE提取指定单元格区域第N个最大值。MAX提取指定单元格区域的最大值。使用格式：=LARGE(单元格区域,显示第N个最大值)=MAX(单元格区域)适用范围：1、需要使用指定单元格区域的第N个最大值参与计算；2、需要使用指定单元格区域的最大值参与计算。18、非空、最大最小值、排名、随机函数应用案例2024/5/22 周三3535十三、函数名称：十三、函数名称：SMALL、MIN主要功能：SMALL提取指定单元格区域的第N个最小值。MIN提取指定单元格区域的最小值。使用格式：=SMALL(单元格区域,显示第N个最小值)=MIN(单元格区域)适用范围：1、需要使用指定的第N个最小值参与计算；2、需要使用指定单元格区域的最大值参与计算。19、非空、最大最小值、排名、随机函数应用案例2024/5/22 周三3636十四、函数名称十四、函数名称：RAND()与RANDBETWEEN、CHAR主要功能：产生随机数、随机字母。使用格式：1、=RAND()产生0 1之间的随机数；2、=RAND()*90产生0 90之间的随机数；3、=RAND()*90+10产生10 100之间的随机数；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、非空、最大最小值、排名、随机函数应用案例2024/5/22 周三3737十五、函数名称：十五、函数名称：RANK、ABS主要功能：RANK将指定数据，在指定绝对单元格区域中进行排位。ABS求出相应数字或表达式的绝对值；使用格式：=RANK(需排位单元格或数据，排位绝对单元格区域)=ABS(单元格或数据)适用范围：RANK需要使用指定的第N个最小值参与计算；ABS需要使用绝对值计算时。注意：注意：对某单元或数据，在某区域进行排位时，该区域一定是绝对引用，否则会报错。对某单元或数据，在某区域进行排位时，该区域一定是绝对引用，否则会报错。21、非空、最大最小值、排名、随机函数应用案例2024/5/22 周三3838十六、函数名称：十六、函数名称：LEN、MOD主要功能：LEN计算单元格或字符串长度MOD求出两数相除的余数使用格式：=LEN(单元格或字符串)=MOD(被除数，除数)适用范围：1、LEN计算单元格或字符串长度，以确保参与计算时的结果正确时。2、MOD需要用2数的余数时；需要将余数作为判定条件（如余数的奇偶性），参与其他函数运算时；22、学历性别民族统计案例2024/5/22 周三3939十七、函数名称：十七、函数名称：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个字符)适用范围：需要保护隐私或重要内容时，如火车票的身份证号码部分被替换隐藏。2024/5/22 周三404023、替换隐藏部分内容案例2024/5/22 周三4141十八、函数名称：十八、函数名称：VLOOKUP、LOOKUP主要功能：VLOOKUP在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。LOOKUP（条件，条件所在区域，要显示内容所在区域）使用格式：=VLOOKUP(查找值，查找范围，查找列数，精确匹配 false或0 或者近似匹配 true或1）=LOOKUP(查找值，查找值所在范围，显示对应列数值）VLOOKUP与LOOKUP异同：Vlookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行，并且Vlookup可以选择采用精确查询或是模糊查询方式，而Lookup只有模糊查询。2024/5/22 周三4242特别提醒：LOOKUP“查找值”必须在“查找范围”的首列中；如果忽略匹配参数，则“查找范围”的首列必须进行升序排序，否则出错。VLOOKUP：无须升序排序。适用范围：通过与“查找值”精确匹配后，显示对应的某列单元格数据。24、LOOKUP、VLOOKUP查询案例25、非空、最大最小值、排名、随机函数应用案例（LOOKUP数组函数取值）26、VLOOKUP的高级使用（数组二分法）：自动从单元格中提取手号码2024/5/22 周三4343十九、函数名称：十九、函数名称：SUMIF、SUMIFS主要功能：SUMIF条件判断区域，单条件，在求和区域按条件求和。SUMIFS统计求和区域，分别按条件区域1、条件1；条件区域2，条件2对统计求和区域进行求和。使用格式：=SUMIF(条件判断区域,条件,求和区域)=SUMIFS(统计求和区域,第一条件区域,条件,第二条件区域,第二条件.)注意：SUMIF的求和区域在公式最后SUMIFS的求和区域在公式最前适用范围：根据条件进行求和计算，不需经过分类汇总。27、多条件求和案例2024/5/22 周三4444二十、函数名称：二十、函数名称：DATEDIF主要功能：2个日期的间隔计算。使用格式：1、间隔年数=DATEDIF(开始日期或开始日期所在单元格,结束日期或结束日期所在单元格,”y”)2、间隔月数=DATEDIF(开始日期或开始日期所在单元格,结束日期或结束日期所在单元格,”m”)3、间隔日数=DATEDIF(开始日期或开始日期所在单元格,结束日期或结束日期所在单元格,”d”)说明：说明：“Y”：时间段中的整年数。“M”：时间段中的整月数。“D”：时间段中的天数。“MD”：2个日期中天数的差。忽略日期中的月和年。YM：2个日期中月数的差。忽略日期中的日和年。YD：2个日期中天数的差。忽略日期中的年。特别提醒：特别提醒：1、DATEDIF函数是计算整年、整月的；2、工龄计算时，不论是年初1月1日、还是年末12月31日参加工作，都是一年；不论是月初1日、还是月末31日参加工作，都是一月。3、使用DATEDIF函数，设置工龄计算公式时，要特别注意，不满周年、不满月份、2个日期剩余天数大小关系，避免计算结果出错。适用范围：需要使用整年整月参与日期计算的范围。28、日期转换及工作年限（月）计算（20151222）2024/5/22 周三4545日期计算一、提取日期中的“年”、“月”、“日”1、提取年：YEAR(日期单元格)2、提取月：=MONTH(日期单元格)3、提取日：DAY(日期单元格)二、计算2个日期间的间隔时间：1、计算实际工龄时间（精确到月）：=IF(MONTH(F3)MONTH(D3),(YEAR(F3)-YEAR(D3)-1)*12+(MONTH(F3)+12-MONTH(D3)+1),(YEAR(F3)-YEAR(D3)*12+(MONTH(F3)-MONTH(D3)+1)2、以月为单位计算实际整月工龄：=DATEDIF(D3,F3,m“)3、以月为单位计算实际工龄：=IF(DAY(F3)DAY(D3),DATEDIF(D3,F3,m)+2,DATEDIF(D3,F3,m)+1)2024/5/22 周三4646二十一、函数名称：二十一、函数名称：INDEX、MATCH主要功能：主要功能：INDEX返回“引用指定区域内，指定行、指定列”交叉处的内容。MATCH返回目标值在查找区域中的行或列位置。使用格式：使用格式：=index(要引用的数据区域有绝对引用,指定引用的行,指定引用的列)=Match（目标值，查找目标所在区域，0）特别提醒：特别提醒：Match按横向列查找时，查找行必须升序排列，否则报错。29、INDEX、MATCH查询案例2024/5/22 周三4747INDEX与与MATCH组合公式应用：组合公式应用：一维查找一维查找（可按行，也可按列，无需排序）：=INDEX(要显示的另一表内容所在区域绝对引用,MATCH(本表查询条件相对引用,另一表查询条件所在区域绝对引用,0)二维查找：二维查找：=INDEX(另一表薪级数据区域用绝对引用,MATCH(本表条件1用相对引用,条件1在另表的区域绝对引用，0),MATCH(本表条件2用相对引用,条件2在另表的区域绝对引用,0)30、一维、二维查询在员工工资数据库管理中的应用2024/5/22 周三4848