《EXCEL常用公式函数实战技巧与应用入门课件.ppt》由会员分享，可在线阅读，更多相关《EXCEL常用公式函数实战技巧与应用入门课件.ppt（95页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EXCEL常用公式函数实战技巧与应用入门常用公式函数实战技巧与应用入门2023/1/112目录目录常用操作技巧常用操作技巧引用切换引用切换公式与结果切换公式与结果切换手动重算和手动重算和F9设置计算精度设置计算精度设置底纹设置底纹隐藏与保护隐藏与保护序列数据填充序列数据填充数据有效性数据有效性常用函数实践常用函数实践数学函数数学函数IF与奖金核算与奖金核算多条件求和与业绩统计多条件求和与业绩统计舍入取整舍入取整统计单元格数量统计单元格数量查找和排名查找和排名日期函数与在库天数、员日期函数与在库天数、员工工龄计算工工龄计算查找重复值查找重复值2023/1/113学习学习Excel的必要性、心态及
2、方法的必要性、心态及方法普及性普及性效率性效率性通用性通用性积极心态积极心态循序渐进循序渐进善用资源善用资源学以致用学以致用愿你从一个新手成长成为一个高手。愿你从一个新手成长成为一个高手。2023/1/114EXCEL技能的层次技能的层次新手新手：刚刚开始接触：刚刚开始接触初级初级：基本掌握常用功能：录入、排序、筛选等：基本掌握常用功能：录入、排序、筛选等中级中级：熟练使用常用功能：熟练使用常用功能+20个以上函数个以上函数+数据透视表数据透视表+简单的宏等简单的宏等高级高级：+数组公式数组公式+VBA编程编程专家专家：EXCEL技能技能+专业知识专业知识+行业经验行业经验从新手到专家从新手到
3、专家2023/1/115公式中的算术运算符公式中的算术运算符运算符运算符名称名称含义含义操作举例操作举例 运算结果运算结果+加号加号加加1+12-减号减号减减2-11*星号星号乘乘2*24/斜杠斜杠除除2/21脱字符脱字符 乘幂乘幂1051000002023/1/116公式中的比较运算符公式中的比较运算符运算符运算符 名称名称含义含义操作举例操作举例=等于号等于号等于等于A1=B1小于号小于号小于小于A1大于号大于号大于大于A1B1不等于号不等于号不等于不等于A1B1=大于等于号大于等于号 大于等于大于等于 A1=B1=小于等于号小于等于号 小于等于小于等于 A1=B12023/1/117公式
4、中的文本运算符公式中的文本运算符连字符连字符=CONCATENATE=CONCATENATE（参数（参数1 1，参数，参数2 2）2023/1/118公式中运算符优先级顺序公式中运算符优先级顺序优先级顺序优先级顺序 运算符运算符说明说明1：引用运算符引用运算符2空格空格引用运算符引用运算符3，引用运算符引用运算符4-5%6，*/，+-先乘幂先乘幂7连接符连接符8=比较运算符比较运算符2023/1/119常用操作技巧常用操作技巧单元格引用单元格引用相对引用相对引用 （默认状态）（默认状态）绝对引用绝对引用混合引用混合引用切换技巧：切换技巧：F42023/1/1110常用操作技巧常用操作技巧单元格
5、公式与结果切换：单元格公式与结果切换：w菜单方式：菜单方式：工具工具-选项选项-视图视图-公公式（勾选）式（勾选）w快捷键：快捷键：Ctrl+2023/1/1111常用操作技巧常用操作技巧自动重算和手动重算自动重算和手动重算 默认自动，但数据量大时运行速度慢默认自动，但数据量大时运行速度慢切换方式：切换方式：工具工具选项选项重新计算重新计算（勾选）手动计算（勾选）手动计算重算活动工作表：重算活动工作表：Shift+F9编辑栏内选中公式切换结果：编辑栏内选中公式切换结果：F9所有工作薄：所有工作薄：Shift+Ctrl+F92023/1/1112常用操作技巧：常用操作技巧：设置计算精度设置计算精
6、度：菜单方式：菜单方式：工具工具选项选项重新计算重新计算（勾选）以显示精度为准（勾选）以显示精度为准函数方式：函数方式：RONUD(数据，位数）数据，位数）2023/1/1113常用操作技巧常用操作技巧设置不同底纹设置不同底纹格式格式条件格式条件格式公式公式（进行设置）（进行设置）奇偶行不同底纹：奇偶行不同底纹：=MOD(ROW(),2)=1奇偶列不同底纹：奇偶列不同底纹：=MOD(COLUMN(),2)=1根据部门名称设置不同间隔底纹：根据部门名称设置不同间隔底纹：=MOD(SUM(-($D$2:$D2$D$1:$D1),2)=1=MOD(SUM(-($D$2:$D2$D$1:$D1),2)
7、=0=MOD(COUNTA($A$2:$A2),2)=12023/1/1114常用操作技巧常用操作技巧隐藏数据：隐藏数据：1、单元格格式、单元格格式-数字数字-分类分类-自定义自定义-类型类型-输入输入三个英文半角状态下的分号三个英文半角状态下的分号2、切换到保护，勾选隐藏和锁定，退出、切换到保护，勾选隐藏和锁定，退出3、工具、工具-保护保护-保护工具表，设定密码保护工具表，设定密码2023/1/1115常用操作技巧常用操作技巧隐藏公式隐藏公式：1、单元格格式，、单元格格式，“保护保护”清除清除“锁定锁定”，返，返回回2、“编辑编辑”-定位定位-定位条件定位条件-选择选择公式，返公式，返回回3
8、、单元格格式，保护、单元格格式，保护（勾选）隐藏和锁定（勾选）隐藏和锁定4、工具、工具保护工作表，设置密码保护工作表，设置密码2023/1/1116常用操作技巧常用操作技巧数据保护数据保护权限：权限：查看和编辑、部分和全部、工作表和簿查看和编辑、部分和全部、工作表和簿是否可见、可改：单元格和工作表保护是否可见、可改：单元格和工作表保护2023/1/1117常用操作技巧常用操作技巧数据填充数据填充左键（范本左键（范本,智能标记）智能标记）右键（菜单）右键（菜单）录入范本，左键拖动填充录入范本，左键拖动填充录入基准，录入基准，1，左键复制，左键复制，Ctrl，放开左键，放开左键录入基准录入基准1，
9、左键填充，显示智能标记，左键填充，显示智能标记选中区域选中区域-编辑编辑-填充填充-序列序列设定基准，设定基准，1，右键，右键-自动填充选项自动填充选项-序列序列2023/1/1118常用操作技巧常用操作技巧复写纸功能复写纸功能：同时在多张工作表上编辑数据同时在多张工作表上编辑数据Ctrl,Shift编辑编辑填充填充至同组工作表至同组工作表2023/1/1119常用操作技巧常用操作技巧隔一行插一空行隔一行插一空行：插入辅助列插入辅助列先用内容加行号先用内容加行号123-之后之后1.1,2.1,3.1,-排序升序排序升序删除辅助列删除辅助列2023/1/1120常用操作技巧常用操作技巧数据有效性
10、与二级下拉菜单数据有效性与二级下拉菜单：1、数据有效性与下拉菜单、数据有效性与下拉菜单2、序列引用名称实现跨工作表、序列引用名称实现跨工作表3、建立名称区域，序列、建立名称区域，序列=indirect(单元格）单元格）实现二级下拉菜单实现二级下拉菜单 单元格：上级菜单中的一个，相对引用单元格：上级菜单中的一个，相对引用2023/1/1121常用操作技巧常用操作技巧-常用快捷键常用快捷键选中活动单元格周围的当前区域选中活动单元格周围的当前区域：Ctrl+SHIFT+8选定整个工作表：选定整个工作表：CTRL+A单元格内换行：单元格内换行：Alt+回车键回车键每次回车在原单元格：每次回车在原单元格
11、：Ctrl+回车键回车键快速录入相同文本快速录入相同文本:Ctrl+D Ctrl+R同时多选单元格或工作表：同时多选单元格或工作表：Ctrl,Shift2023/1/1122常用操作技巧常用操作技巧-常用快捷键常用快捷键显示显示“单元格格式单元格格式”对话框：对话框：Ctrl+1 隐藏行：隐藏行：Ctrl+9 取消隐藏行：取消隐藏行：Ctrl+Shift+9 隐藏列：隐藏列：Ctrl+0（零）（零）取消隐藏列：取消隐藏列：Ctrl+Shift+0插入新工作表：插入新工作表：Shift+F11 2023/1/1123常用操作技巧常用操作技巧-常用快捷键常用快捷键插入新工作表：插入新工作表：Shi
12、ft+F11 定义名称：定义名称：Ctrl+F3插入时间插入时间 ctrl+shift+:输入日期输入日期 ctrl+;2023/1/1124单元格函数错误提示：单元格函数错误提示：#:列不够宽或包含一个无效的时间列不够宽或包含一个无效的时间#VALUE:参数类型错误参数类型错误#REF!:单元格引用无效单元格引用无效#NAME?:不能识别公式中的文本，名称拼写不能识别公式中的文本，名称拼写错误错误#NUM!:公式中使用了无效数值公式中使用了无效数值#N/A:数值对公式或函数不可用。数值对公式或函数不可用。2023/1/1125什么是函数？什么是函数？Excel函数即是预先定义，执行计算、分析
13、等处函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。理数据任务的特殊公式。以常用的求和函数以常用的求和函数SUM为例，它的语法是为例，它的语法是“SUM(number1,number2,)”。其中。其中“SUM”称为函称为函数名称，一个函数只有唯一的一个名称，它决定了数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。号表示函数结束。2023/1/1126函数的参数函数的参数1.常量常量2.逻辑值逻辑值
14、3.数组数组4.错误值错误值5.单元格引用单元格引用6.嵌套函数嵌套函数7.名称和标记名称和标记2023/1/1127函数的参数函数的参数 常量是直接输入到单元格或公式中的数常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，字或文本，或由名称所代表的数字或文本值，例如数字例如数字“2890.56”、日期、日期“2003-8-19”和文本和文本“黎明黎明”都是常量。但是公式或由公都是常量。但是公式或由公式计算出的结果都不是常量，因为只要公式式计算出的结果都不是常量，因为只要公式的参数发生了变化，它自身或计算出来的结的参数发生了变化，它自身或计算出来的结果就会发生变化。果就
15、会发生变化。常量常量2023/1/1128函数的参数函数的参数 逻辑值是比较特殊的一类参数，它只有逻辑值是比较特殊的一类参数，它只有TRUE(真真)或或FALSE(假假)两种类型。例如在两种类型。例如在公式公式“=IF(A3=0,A2/A3)”中，中，“A3=0”就是一个可以返回就是一个可以返回TRUE(真真)或或FALSE(假假)两种结果的参数。当两种结果的参数。当“A3=0”为为TRUE(真真)时在公式所在单元格中填入时在公式所在单元格中填入“0”，否则在，否则在单元格中填入单元格中填入“A2/A3”的计算结果。的计算结果。逻辑值逻辑值2023/1/1129函数的参数函数的参数 数组用于可
16、产生多个结果，或可以对存放数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在中有常量和区域两类数组。前者放在“”(按下按下Ctrl+Shift+Enter组合键自动生组合键自动生成成)内部，而且内部各列的数值要用逗号内部，而且内部各列的数值要用逗号“，”隔开，各行的数值要用分号隔开，各行的数值要用分号“；”隔开。假隔开。假如你要表示第如你要表示第1行中的行中的56、78、89和第和第2行中行中的的90、76、80，就应该建立一个，就应该建立一个2行行3列的常列的常量数组量数组“56，78，89；
17、90，76，80。数组数组2023/1/1130函数的参数函数的参数 区域数组是一个矩形的单元格区域，该区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使作为数组公式使用时，它所引用的矩形单元格区域用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。就是一个区域数组。区域数组区域数组2023/1/1131函数的参数函数的参数 使用错误值作为参数的主要是信息函数，使用错误值作为参数的主要是信息函数，例如例如“ERROR.TYPE”函数就是以错误值作为函数就是以错误
18、值作为参数。它的语法为参数。它的语法为“ERROR.TYPE(error_val)”，如果其中的参，如果其中的参数是数是#NUM!，则返回数值，则返回数值“6”。错误值错误值2023/1/1132函数的参数函数的参数 单元格引用是函数中最常见的参数，引单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以个函数中使用同一个单元格的数据。还可以引用同
19、一工作簿不同工作表的单元格，甚至引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。引用其他工作簿中的数据。单元格引用单元格引用2023/1/1133函数的参数函数的参数 函数也可以是嵌套的，即一个函数是另一个函函数也可以是嵌套的，即一个函数是另一个函数的参数，例如数的参数，例如“=IF(OR(RIGHTB(E2,1)=1，RIGHTB(E2,1)=3，RIGHTB(E2,1)=5，RIGHTB(E2,1)=7，RIGHTB(E2,1)=9),男男,女女)”。其中公式中的。其中公式中的IF函数使用了嵌套的函数使用了嵌套的RIGHTB函数，并将后者返回的结果作为函数，并将后者返回的结果
20、作为IF的逻辑的逻辑判断依据。判断依据。嵌套函数嵌套函数2023/1/1134函数的参数函数的参数 为了更加直观地标识单元格或单元格区为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。例如公式或函数中直接引用。例如“B2:B46”区区域存放着学生的物理成绩，求解平均分的公域存放着学生的物理成绩，求解平均分的公式一般是式一般是“=AVERAGE(B2:B46)”。在给。在给B2:B46区域命名为区域命名为“物理分数物理分数”以后，该以后，该公式就可以变为公式就可以变为“=AVERAGE(物理分数物理分数)”，从而使
21、公式变得更加直观。从而使公式变得更加直观。名称和标记名称和标记2023/1/1135常用函数解读与应用常用函数解读与应用-数学函数数学函数求和（差）求和（差）：SUM（参数（参数1，参数，参数30）返回某一单元格区域中数字、逻辑值及返回某一单元格区域中数字、逻辑值及数字的文本表达式之和。数字的文本表达式之和。2023/1/1136常用函数解读与应用常用函数解读与应用-数学函数数学函数求积：求积：=PRODUCT（参数（参数1，参数参数30）2023/1/1137常用函数解读与应用常用函数解读与应用-数学函数数学函数求商求商：返回商的整数部分返回商的整数部分=QUOTIENT（被除数，除数）（被
22、除数，除数）返回余数返回余数=MOD(被除数被除数,除数除数)2023/1/1138常用函数解读与应用常用函数解读与应用-数学函数数学函数舍入与取整函数舍入与取整函数四舍五入四舍五入:ROUND按位舍入：按位舍入：ROUNDUPROUNDDOWN按倍舍入按倍舍入:CEILINGFLOOR截断舍入：截断舍入：TRUNC截断取整：截断取整：INT奇偶取整：奇偶取整：ODDEVEN 2023/1/1139常用函数解读与应用常用函数解读与应用-数学函数数学函数舍入与取整函数舍入与取整函数按指定位数按指定位数四舍五入四舍五入某个数字某个数字=ROUND（数据，保留的位数）（数据，保留的位数）按绝对值减小
23、的方向舍入某一数字按绝对值减小的方向舍入某一数字=ROUNDDOWN(数据，位数）数据，位数）=ROUNDDOWN(3.14159，3)返回返回3.141 按绝对值增大的方向舍入一个数字按绝对值增大的方向舍入一个数字=ROUNDUP(-3.14159，1)返回返回-3.2 2023/1/1140常用函数解读与应用常用函数解读与应用-数学函数数学函数舍入与取整函数舍入与取整函数将数字的小数部分按指定小数截去，返回整数将数字的小数部分按指定小数截去，返回整数=TRUNC（数据，保留的位数）（数据，保留的位数）数据舍入取整数据舍入取整=INT（数据）（数据）两函数在处理负数时有所不同：两函数在处理负
24、数时有所不同：TRUNC(-4.3)返回返回-4，而而INT(-4.3)返回返回-5。2023/1/1141常用函数解读与应用常用函数解读与应用-数学函数数学函数舍入与取整函数舍入与取整函数 按照货币格式将小数四舍五入到指定的位按照货币格式将小数四舍五入到指定的位数并转换成文字数并转换成文字=DOLLAR(number，decimals)=RMB(数据，数据，位数位数)（省略参数（省略参数decimals，则以，则以2计算）计算）指定位数四舍五入后文本形式显示指定位数四舍五入后文本形式显示FIXED(数据，位数，逻辑值数据，位数，逻辑值)2023/1/1142常用函数解读与应用常用函数解读与应
25、用-数学函数数学函数舍入与取整函数舍入与取整函数 四舍四舍五入五入取值方向取值方向位数位数可控可控ROUND是是绝对值四舍五入绝对值四舍五入是是ROUNDUP否否绝对值增大绝对值增大是是ROUNDDOWN 否否绝对值减小绝对值减小是是TRUNC否否绝对值减小绝对值减小是是INT否否数值减小数值减小否否FLOOR否否绝对值减小绝对值减小是是2023/1/1143常用函数解读与应用常用函数解读与应用-统计函数统计函数求平均值：求平均值：计算所有参数的算术平均值计算所有参数的算术平均值=AVERAGE(参数参数1，参数，参数30)计算参数清单中数值的平均值（包含文本等）计算参数清单中数值的平均值（包
26、含文本等）=AVERAGEA（参数（参数1，参数，参数30）掐头去尾求平均值掐头去尾求平均值=TRIMMEAN(array，percent)=TRIMMEAN(区域，去掉的数据百分比区域，去掉的数据百分比)=TRIMMEAN($B3:$I3,2/8)2023/1/1144常用函数解读与应用常用函数解读与应用-统计函数统计函数求最大值求最大值=MAX（参数（参数1，参数，参数30）=MAXA（参数（参数1，参数，参数30）求中位值求中位值=MEDIAN(number1，number30)求最小值求最小值=MIN（参数（参数1，参数，参数30）=MINA（参数（参数1，参数，参数30）2023/1
27、/1145常用函数解读与应用常用函数解读与应用-统计函数统计函数MODE求众数求众数用途：返回在某一数组或数据区域中的众数。用途：返回在某一数组或数据区域中的众数。语法：语法：MODE(number1，number2，.)。33 参数：参数：Number1，number2，.是用是用于众数计算的于众数计算的1到到30 个参数。个参数。实例：如果实例：如果A1=71、A2=83、A3=71、A4=4 9、A5=92、A6=88，则公式则公式“=MODE(A1:A6)”返回返回71。2023/1/1146常用函数解读与应用常用函数解读与应用-统计函数统计函数计算单元格个数计算单元格个数统计单元格区
28、域中含有统计单元格区域中含有数字数字的单元格个数的单元格个数=COUNT（参数（参数1，参数，参数30）返回参数组返回参数组中非空值中非空值的数目的数目=COUNTA（参数（参数1，参数，参数30）计算某个单元格区域中计算某个单元格区域中空白单元格空白单元格的数目的数目=COUNTBLANK(区域区域)2023/1/1147常用函数解读与应用常用函数解读与应用-统计函数统计函数计算单元格个数计算单元格个数计算区域中计算区域中满足给定条件满足给定条件的单元格的个数的单元格的个数=COUNTIF(range，criteria)=COUNTIF(区域，条件区域，条件)2023/1/1148常用函数解
29、读与应用常用函数解读与应用-统计函数统计函数返回某一数据集中的返回某一数据集中的某个最大值某个最大值=LARGE(array，k)=LARGE(区域，名次区域，名次)返回数据集中第返回数据集中第k 个最小值个最小值=SMALL(array，k)=SMALL(数据集，名次数据集，名次)2023/1/1149常用函数解读与应用常用函数解读与应用-统计函数统计函数返回一个数值在一组数值中的排位返回一个数值在一组数值中的排位=RANK(数值，区域，数值，区域，0或省略或省略)降序降序=RANK(数值，区域，数值，区域，1或大于或大于1)升序升序RANK(要判断的单元格要判断的单元格,查找的区域查找的区
30、域,1为最小为最小数排第一数排第一0为最大数排第一为最大数排第一)=COUNTIF(A$3:A$12,&A3)+1=SUM(-(A$3:A$12A3)+1业绩排名业绩排名2023/1/1150常用函数解读与应用常用函数解读与应用-文本函数文本函数从一个文本字符串的指定位置开始，截取指定从一个文本字符串的指定位置开始，截取指定数目的字符数目的字符=MID（文本字符串，起始位置，截取数目）（文本字符串，起始位置，截取数目）从一个文本字符串的最后一个字符开始从一个文本字符串的最后一个字符开始,截取截取指定数目的字符指定数目的字符RIGHT（文本字符串，截取数目）（文本字符串，截取数目）提取文本的一部
31、分提取文本的一部分2023/1/1151常用函数解读与应用常用函数解读与应用-文本函数文本函数从一个文本字符串的第一个字符开始从一个文本字符串的第一个字符开始,截取指截取指定数目的字符定数目的字符=LEFTB(目标单元格目标单元格,从内容的左边开始截取从内容的左边开始截取指定的字的个数指定的字的个数)=LEFT（目标单元格，截取数目）（目标单元格，截取数目）提取文本的一部分：提取文本的一部分：2023/1/1152常用函数解读与应用常用函数解读与应用-文本函数文本函数统计文本字符串中字符数目统计文本字符串中字符数目=LEN(目标单元格目标单元格)检索字符位置检索字符位置(区分或不区分大小写区分
32、或不区分大小写)=FIND(要查找的内容加引号或单元格要查找的内容加引号或单元格,查找区查找区域域(单元格单元格),从几个字开始查找从几个字开始查找)=SEARCH(要查找的内容加引号或单元格要查找的内容加引号或单元格,查查找区域找区域(单元格单元格),从几个字开始查找从几个字开始查找)计算文本的长度：计算文本的长度：2023/1/1153常用函数解读与应用常用函数解读与应用-文本函数文本函数EXACT：检查两文本是否完全相同检查两文本是否完全相同=EXACT(目标单元格或文本目标单元格或文本1,目标单元格或文本目标单元格或文本2)REPT：根据指定次数重复文本：根据指定次数重复文本REPT(
33、要显示的内容或单元格要显示的内容或单元格,要求重复的次数要求重复的次数)2023/1/1154常用函数解读与应用常用函数解读与应用-文本函数文本函数TEXT(要改变数值或单元格要改变数值或单元格,要显示的方式代码要显示的方式代码)神奇的神奇的TEXTTEXT函数函数数值数值代码代码结果结果6.55#.06.660.006.00600000639618yy-mm-dd08-09-192023/1/1155常用函数解读与应用常用函数解读与应用-实战案例实战案例性别提取：性别提取：=IF(A2,IF(MOD(RIGHT(LEFT(A2,17),2),男男,女女),)年龄计算：年龄计算：=IF(A2,
34、DATEDIF(TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00),TODAY(),y),)身份证解析要用到的函数身份证解析要用到的函数2023/1/1156常用函数解读与应用常用函数解读与应用-实战案例实战案例出生日期提取：出生日期提取：IF(A2,TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)有效性验证有效性验证=OR(LEN(A2)=15,IF(LEN(A2)=18,MID(10X98765432,MOD(SUM(MID(A2,ROW(INDIRECT(1:
35、17),1)*2(18-ROW(INDIRECT(1:17),11)+1,1)=RIGHT(A2)身份证解析要用到的函数身份证解析要用到的函数2023/1/1157常用函数解读与应用常用函数解读与应用-实战案例实战案例提取所属省市提取所属省市IF(A5,VLOOKUP(LEFT(A5,2),data,2,)&VLOOKUP(LEFT(A5,4),data,2,),)15位升级位升级18位：位：=IF(LEN(A3)=15,REPLACE(A3,7,19)&MID(10X98765432,MOD(SUM(MID(REPLACE(A3,7,19),ROW(INDIRECT(1:17),1)*2(1
36、8-ROW(INDIRECT(1:17),11)+1,1),A3)身份证解析要用到的函数身份证解析要用到的函数2023/1/1158常用函数解读与应用常用函数解读与应用-逻辑函数逻辑函数IF:根据条件满足与否返回不同的值根据条件满足与否返回不同的值AND:检测所有的条件是否为检测所有的条件是否为 真真OR:检测任意一项条件是否为真检测任意一项条件是否为真NOT:对示条件的参数的逻辑值求反对示条件的参数的逻辑值求反逻辑值：逻辑值：TRUE:表示总是为真表示总是为真FALSE:表示总是为假表示总是为假逻辑函数逻辑函数2023/1/1159常用函数解读与应用常用函数解读与应用-逻辑函数逻辑函数IF逻
37、辑函数的运用逻辑函数的运用:如果如果就就否则否则=IF(条件表达式，条件表达式，TRUE结果，结果，FALSE的结果）的结果）=IF($J$3/$L$31.2,500,IF($J$3/$L$3=1.15,400,IF($J$3/$L$3=1.1,300,IF($J$3/$L$3=1.05,200,0)=CHOOSE(IF($J$3/$L$31.2,4,IF($J$3/$L$3=1.15,3,IF($J$3/$L$3=1.1,2,IF($J$3/$L$3=1.05,1,5),200,300,400,500,0)2023/1/1160常用函数解读与应用常用函数解读与应用-数学函数数学函数条件求和条
38、件求和根据指定条件对若干单元格、区域或引用求和根据指定条件对若干单元格、区域或引用求和=SUMIF(条件区域，条件，求和区域）条件区域，条件，求和区域）=SUMIF(B1:B1000，中级中级，F1:F1000)2023/1/1161常用函数解读与应用常用函数解读与应用-数学函数数学函数SUBTOTAL(计算方式计算方式,要计算的区域要计算的区域)含隐藏含隐藏的方式的方式不含隐藏不含隐藏的方式的方式功能功能同等函数同等函数1101计算平均值计算平均值AVERAGE2102计算数值的个数计算数值的个数COUNT3103计算数据的个数计算数据的个数COUNTA4104计算最大值计算最大值MAX51
39、05计算最小值计算最小值MIN6106计算积计算积PRODUCT9109计算和计算和SUM2023/1/1162常用函数解读与应用常用函数解读与应用-实战案例实战案例多条件求和多条件求和:数组公式数组公式：=SUM(IF($Q$91:$Q$120=店销店销)*($G$91:$G$120=C8),1,0)*($J$91:$J$120)=SUM(IF(条件区域条件区域=条件）条件）*（条件区域（条件区域2=条件条件2）,1,0)*(求和区域求和区域)2023/1/1163常用函数解读与应用常用函数解读与应用-实战案例实战案例多条件求和多条件求和:数组公式数组公式=SUM($R$91:$R$120=
40、“分期分期)*($G$91:$G$120=$C$5)*($J$91:$J$120)=SUM(（条件（条件1）*（条件（条件2）*（求和区域）（求和区域）)2023/1/1164常用函数解读与应用常用函数解读与应用-实战案例实战案例多条件求和多条件求和:函数函数：=SUMPRODUCT($R$91:$R$120=分期分期)*($G$91:$G$120=$C$5),($J$91:$J$120)=SUMPROCUCT(（条件区域（条件区域=条件）条件）*（条件（条件区域区域2=条件条件2）*(求和区域）求和区域）)2023/1/1165常用函数解读与应用常用函数解读与应用-实战案例实战案例多条件求和
41、多条件求和:函数函数：（：（2007）=SUMIFS（求和区域，条件区域（求和区域，条件区域1，条件，条件1，条，条件区域件区域2，条件，条件2）2023/1/1166常用函数解读与应用常用函数解读与应用-实战案例实战案例多条件计数多条件计数:=SUM(（条件（条件1）*（条件（条件2）*（条件（条件n）)=SUMPROCUCT(（条件区域（条件区域1=条件条件1）*（条（条件区域件区域2=条件条件2）*(条件区域条件区域n=条件条件n）)2023/1/1167常用函数解读与应用常用函数解读与应用-日期与时间函数日期与时间函数今天和现在今天和现在=TODAY()=NOW()如何快捷输入当天日期
42、？如何快捷输入当天日期？2023/1/1168常用函数解读与应用常用函数解读与应用-日期与时间函数日期与时间函数从日期中提取出年或月或日或星期几从日期中提取出年或月或日或星期几=YEAR(日期）日期）=MONTH(日期）日期）=DAY(日期）日期）=WEEKDAY(要查的单元格要查的单元格,2)提取时分秒与此雷同提取时分秒与此雷同2023/1/1169常用函数解读与应用常用函数解读与应用-日期与时间函数日期与时间函数将文本方式转换成标准日期：将文本方式转换成标准日期：=DATE(年，月，日）年，月，日）从指定的年、月、日来计算日期序列号值从指定的年、月、日来计算日期序列号值.=DATEVALU
43、E(要计算的单元格要计算的单元格)从表示日期的文本来计算序列号值从表示日期的文本来计算序列号值=TIME(hour,minute,secod)从时、分、秒来计算出时间的序列号值从时、分、秒来计算出时间的序列号值=TIMEVALUE(时间时间)从表示时间的文本来计算序列号值从表示时间的文本来计算序列号值2023/1/1170常用函数解读与应用常用函数解读与应用-日期与时间函数日期与时间函数计算计算年龄，库龄，工龄年龄，库龄，工龄=IF($K$2DATE(YEAR($K$2),MONTH(D5),DAY(D5),YEAR($K$2)-YEAR(D5),YEAR($K$2)-YEAR(D5)-1)=
44、DATEDIF(D5,C5,y)=IF($K$2-D6)/3651,INT($K$2-D6)/365),0)2023/1/1171常用函数解读与应用常用函数解读与应用-日期与时间函数日期与时间函数隐秘函数隐秘函数DATEDIF很多版本里都没有，不能从很多版本里都没有，不能从“插入函数插入函数”对话对话框中输入框中输入.用来计算两个日期的年、月、天数的差用来计算两个日期的年、月、天数的差=DATEDIF(起始日期起始日期,结束日期结束日期,y)=DATEDIF(起始日期起始日期,结束日期结束日期,“m)=DATEDIF(起始日期起始日期,结束日期结束日期,“d)2023/1/1172常用函数解读
45、与应用常用函数解读与应用-查找与引用函数查找与引用函数VLOOKUPVLOOKUP（查找条件，总区域，结果列数，（查找条件，总区域，结果列数，0 0）在表格或数值数组的首列查找指定的数值在表格或数值数组的首列查找指定的数值,并由此返并由此返回表格或数组当前行中指定列处的数值回表格或数组当前行中指定列处的数值VLOOKUP(VLOOKUP(要查找的内容要查找的内容,搜索的区域搜索的区域,从查找区域从查找区域首列开始到要找的内容的列数首列开始到要找的内容的列数,指定是近似匹配指定是近似匹配还是精确匹配查找方式还是精确匹配查找方式)精确查找是精确查找是vlookupvlookup最基本也是最常用的功
46、能最基本也是最常用的功能,对于对于数据量大的查找数据量大的查找,其速度比菜单中的查找还快其速度比菜单中的查找还快.设置设置vlookupvlookup第四个参数为第四个参数为falsefalse或或0,0,即为精确查找即为精确查找.2023/1/1173常用函数解读与应用常用函数解读与应用-查找与引用函数查找与引用函数=VLOOKUP=VLOOKUP（查找条件，总区域，结果列数，（查找条件，总区域，结果列数，0 0）=VLOOKUP=VLOOKUP（查找值，所在区域，结果所在列数，（查找值，所在区域，结果所在列数，0 0）=VLOOKUP=VLOOKUP（查找条件，搜索区域，列序号，查找方式）
47、（查找条件，搜索区域，列序号，查找方式）=VLOOKUP=VLOOKUP（指定条件，查找区域，右移几行，（指定条件，查找区域，右移几行，0 0）指定搜查条件，设定搜查区域，向右设定到指定搜查条件，设定搜查区域，向右设定到结果所在的列，设定搜查方式是否精确查找。结果所在的列，设定搜查方式是否精确查找。2023/1/1174常用函数解读与应用常用函数解读与应用-查找与引用函数查找与引用函数MATCH(查找条件查找条件,查找区域查找区域,0)返回在指定方式下与指定数值匹配的数返回在指定方式下与指定数值匹配的数组中元素的相应位置组中元素的相应位置2023/1/1175常用函数解读与应用常用函数解读与应
48、用-查找与引用函数查找与引用函数CHOOSE(指定区域的第几个指定区域的第几个,指定区域指定区域1,指定指定区域区域2,)第一个参数是用来指明待选参数序号的值，第一个参数是用来指明待选参数序号的值，它必须是它必须是1到到29之间的数字、或者是包含数之间的数字、或者是包含数字字1到到29的公式或单元格引用；其他的公式或单元格引用；其他29个参个参数可以是数字、单元格，已定义的名称、公数可以是数字、单元格，已定义的名称、公式、函数或文本。式、函数或文本。2023/1/1176常用函数解读与应用常用函数解读与应用-查找与引用函数查找与引用函数COLUMN（）返回列号（）返回列号ROW()返回行号返回
49、行号ADDRESS(第几行第几行,第几列第几列,引用类型引用类型,工作表名工作表名称称)2023/1/1177常用函数解读与应用常用函数解读与应用查找重复值查找重复值1、防止录入重复数据、防止录入重复数据数据有效性数据有效性 =COUNTIF(A:A,A1)=12、查找区域内重复值、查找区域内重复值条件格式条件格式 =COUNTIF(A:A,A1)1=IF(COUNTIF(A:A,A1)=2,重复重复,)2023/1/1178常用函数解读与应用常用函数解读与应用查找重复值查找重复值3、不同列中找重复数据、不同列中找重复数据=ISNUMBER(MATCH($B2,$A$2:$A$18,0)=IF
50、(ISNUMBER(MATCH($B8,$A$2:$A$18,0),重复重复,)2023/1/1179常用函数解读与应用常用函数解读与应用查找重复值查找重复值4、VLOOKUP函数是否可以查找重复值？函数是否可以查找重复值？请大家自己学习。请大家自己学习。2023/1/1180常用函数解读与应用常用函数解读与应用5、高级筛选解决重复值、高级筛选解决重复值A、提取单个表中的不重复值、提取单个表中的不重复值全选全选-数据数据-高级筛选高级筛选-列表区域列表区域-选择不重复记选择不重复记录录B、提取两个表中的相同数据、提取两个表中的相同数据一个条件区域，一个列表区域一个条件区域，一个列表区域2023