excle常用函数教学课件

常用函数教学课件Excel目录0102Excel函数基础概念统计类函数了解函数结构、公式输入方法及引用类型SUM、AVERAGE、COUNT等常用统计函数0304逻辑判断函数查找引用函数IF、AND、OR等条件判断函数VLOOKUP、INDEX+MATCH等查询函数0506文本处理函数日期时间函数LEFT、RIGHT、CONCATENATE等字符处理函数TODAY、NOW、DATE等日期计算函数07条件汇总函数实用技巧与案例演示SUMIF、COUNTIF等条件统计函数第一章函数基础概念Excel函数结构公式输入函数名+参数（用逗号分隔）所有公式必须以=符号开头例如=SUMA1:A10可以通过公式栏或直接在单元格中输入引用类型函数嵌套相对引用A1（复制时会随位置变化）一个函数的结果可作为另一个函数的参数绝对引用$A$1（复制时保持不变）最多可嵌套64层函数混合引用$A1或A$1（锁定行或列）函数的组成示意图Excel等号=函数名参数所有函数必须以等号开始，告诉Excel这是一指定要执行的操作，如SUM、AVERAGE、IF函数需要处理的数据，用括号括起，多个参数个公式而非文本等用逗号分隔示例=SUMA1:A10计算A1到A10单元格的数值总和第二章统计类函数SUM AVERAGE求和函数，计算指定范围内所有数值的总和平均值函数，计算指定范围内所有数值的平均值语法=SUM数值1,数值2,...语法=AVERAGE数值1,数值2,...示例=SUMA1:A10示例=AVERAGEB1:B10COUNT COUNTA计数函数，统计指定范围内包含数值的单元格数量计数函数，统计指定范围内非空单元格的数量语法=COUNT值1,值2,...语法=COUNTA值1,值2,...示例=COUNTC1:C10示例=COUNTAD1:D10统计函数案例演示案例应用计算销售额总和=SUME2:E20计算E2到E20单元格的销售金额总和统计有效数据个数=COUNTB2:B30统计B列中的数值型数据数量=COUNTAA2:A30统计A列中非空单元格数量计算平均分数=AVERAGEF2:F15计算F列学生成绩的平均分销售数据表示例使用SUM和AVERAGE函数分析销售业绩与函数MAX MIN应用场景示例MAX函数•找出班级最高分和最低分查找一组数据中的最大值•找出销售团队最高业绩和最低业绩语法=MAX数值1,数值2,...•找出一段时间内的最高温度和最低温度•找出库存商品的最高价格和最低价格示例=MAXE1:E10•确定数据集中的极值，用于数据分析MIN函数查找一组数据中的最小值语法=MIN数值1,数值2,...示例=MINE1:E10第三章逻辑判断函数IF函数AND函数根据条件返回不同的值检查是否所有条件都为真语法=IF条件,真值,假值语法=AND条件1,条件2,...示例=IFA160,及格,不及格示例=ANDA160,A180OR函数IFERROR函数检查是否至少有一个条件为真捕获并处理公式错误语法=OR条件1,条件2,...语法=IFERROR值,错误值示例=ORA160,A190示例=IFERRORA1/B1,除数为零函数实战案例IF案例一判断成绩是否及格=IFA2=60,及格,不及格解释如果A2单元格的值大于等于60，则显示及格，否则显示不及格案例二多等级成绩评定=IFB3=90,优秀,IFB3=80,良好,IFB3=60,及格,不及格解释嵌套使用IF函数，根据不同分数范围返回不同的评级结果案例三销售提成计算=IFC410000,C4*

0.1,C4*

0.05解释销售额超过10000元按10%提成，否则按5%提成成绩评级系统根据分数自动判断等级逻辑函数组合示例多条件判断任一条件满足=IFANDA160,B1100,合格,不合格=IFORA260,A290,需要关注,表现正常此公式检查两个条件A1必须大于60且B1必须小于100只有当两个条件同时满足此公式检查学生成绩是否低于60或高于90时，才返回合格，否则返回不合格当成绩过低或过高时，都会标记为需要关注，只有在60-90之间才显示表现正常复杂逻辑组合=IFANDC35000,ORD3=北京,D3=上海,重点客户,普通客户解释当订单金额大于5000且客户来自北京或上海时，标记为重点客户，否则为普通客户第四章查找与引用函数12VLOOKUP函数HLOOKUP函数垂直查找在表格的第一列查找指定的水平查找在表格的第一行查找指定的值，并返回该行中指定列的值值，并返回该列中指定行的值语法=VLOOKUP查找值,查找区域,语法=HLOOKUP查找值,查找区域,列索引,[精确匹配]行索引,[精确匹配]示例=VLOOKUP张三,A1:C10,3,示例=HLOOKUP销售额,A1:F3,3,FALSE FALSE3INDEX+MATCH组合灵活的查找方式，可以克服VLOOKUP的限制语法=INDEX返回区域,MATCH查找值,查找区域,匹配类型示例=INDEXC1:C10,MATCH张三,A1:A10,0函数详解VLOOKUP参数说明查找值要在表格第一列中查找的值查找区域包含数据的表格范围列索引返回值在表格中的列号（从1开始）精确匹配FALSE表示精确匹配，TRUE表示近似匹配精确匹配与近似匹配区别精确匹配FALSE必须找到完全相同的值近似匹配TRUE查找小于或等于查找值的最大值（数据必须按升序排列）常见错误及解决方法#N/A错误找不到匹配值，检查拼写或使用IFERROR处理#REF!错误列索引超出范围，检查列号是否正确返回意外结果检查是否使用了正确的匹配类型查找函数案例员工信息查询商品价格查询=VLOOKUPA2,员工表!A:D,3,FALSE=VLOOKUPB3,产品表!A:E,4,FALSE根据员工编号A2在员工表中查找对应的部门信息（第3列）根据产品编号B3在产品表中查找对应的价格信息（第4列）实用技巧为提高VLOOKUP函数的灵活性，可以使用MATCH函数代替固定列号=VLOOKUPA2,员工表!A:D,MATCH部门,员工表!1:1,0,FALSE这样即使表格结构变化，只要列标题不变，公式仍能正确工作与组合优势INDEX MATCHINDEX函数优势一双向查找返回表格或区域中的值可以同时按行和列查找，不局限于固定列语法=INDEX数组,行号,[列号]MATCH函数优势二查找方向灵活在区域中查找指定项目的位置解决VLOOKUP只能从左向右查找的限制语法=MATCH查找值,查找区域,匹配类型组合使用优势三性能更好=INDEXC2:E10,MATCH张三,A2:A10,0,MATCH工资,C1:E1,0对于大型数据表，执行速度通常比VLOOKUP更快优势四表格变化适应性强当插入或删除列时，不需要调整公式第五章文本处理函数LEFT/RIGHT/MID LEN从文本的左侧、右侧或中间提取字符计算文本中的字符数量语法=LEFT文本,字符数语法=LEN文本语法=RIGHT文本,字符数示例=LENExcel函数返回7语法=MID文本,起始位置,字符数CONCATENATE/TEXTJOIN TRIM合并多个文本单元格的内容去除文本中的多余空格语法=CONCATENATE文本1,文本2,...语法=TRIM文本语法=TEXTJOIN分隔符,忽略空值,文本1,...示例=TRIM Excel函数返回Excel函数这些文本处理函数可以帮助您清理数据、提取需要的部分并进行格式化，非常适合处理导入的外部数据文本函数案例案例一提取身份证号中的出生案例二合并姓名与部门信息年月=CONCATENATEB2,-,C2=MIDA2,7,8或使用新函数=TEXTJOIN-,TRUE,解释从身份证号A2的第7个字符开B2,C2始，提取8个字符，获取出生年月日信息解释将员工姓名B2与部门C2合并，年龄计算进阶中间用-连接案例三提取邮箱用户名=YEARTODAY-VALUEMIDA2,7,4解释提取身份证中的出生年份并计算=LEFTD2,FIND@,D2-1当前年龄解释提取邮箱地址中@符号之前的所有字符第六章日期与时间函数TODAY NOW返回当前日期（不含时间）返回当前日期和时间语法=TODAY语法=NOW示例=TODAY返回当前日期示例=NOW返回当前日期和时间DATE/TIME函数YEAR/MONTH/DAY构造指定的日期或时间值从日期中提取年份、月份或日语法=DATE年,月,日语法=YEAR日期语法=TIME时,分,秒语法=MONTH日期语法=DAY日期日期函数应用计算两个日期间天数差计算年龄=D5-C5=YEARTODAY-YEARB2简单方法直接用两个日期相减基础版本只考虑年份差，不考虑生日是否已过=ABSD5-C5使用ABS函数确保结果为正数精确计算年龄判断是否为工作日=DATEDIFB2,TODAY,Y=IFWEEKDAYA1,25,周末,工作日使用DATEDIF函数精确计算年龄，考虑月份和日期判断A1单元格中的日期是周末还是工作日计算工作天数计算到期天数=NETWORKDAYSC3,D3=E6-TODAY计算两个日期之间的工作日数量，自动排除周末计算截止日期还有多少天到期第七章条件汇总函数SUMIF/SUMIFS根据一个或多个条件求和语法=SUMIF条件范围,条件,求和范围语法=SUMIFS求和范围,条件范围1,条件1,...COUNTIF/COUNTIFS根据一个或多个条件计数语法=COUNTIF范围,条件语法=COUNTIFS范围1,条件1,范围2,条件2,...AVERAGEIF/AVERAGEIFS根据一个或多个条件计算平均值语法=AVERAGEIF条件范围,条件,平均值范围语法=AVERAGEIFS平均值范围,条件范围1,条件1,...这些条件汇总函数能大幅简化数据分析工作，避免创建复杂的辅助计算和多步骤操作对于处理大型数据集特别有用条件汇总函数案例统计销售额超过目标的订单总和统计特定区域客户数量=SUMIFC2:C20,10000,D2:D20=COUNTIFSE2:E30,北京,F2:F30,是解释统计C列中金额大于10000的订单，并解释统计既在北京地区E列，又是VIP客计算D列中对应的销售额总和户F列值为是的客户数量多条件汇总高级案例=SUMIFSF2:F100,C2:C100,电子产品,D2:D100,=1000,E2:E100,北京解释计算产品类别为电子产品且价格大于等于1000且销售地区为北京的销售额总和第八章实用技巧与函数组合函数嵌套技巧将一个函数的结果作为另一个函数的参数，构建更复杂的公式例如=IFSUMA1:A101000,达标,未达标动态范围引用使用OFFSET函数创建可变大小的引用区域例如=SUMOFFSETA1,0,0,COUNTAA:A,1使用名称管理器为复杂的公式或常用范围创建名称，提高公式可读性例如将=SUMA1:A10定义为名称销售总额，然后在公式中直接使用销售总额函数嵌套示例复杂嵌套公式分解=IFCOUNTIFA1:A10,1000,有超标,无超标内层函数COUNTIFA1:A10,100统计A1:A10范围内大于100的单元格数量条件判断...0检查统计结果是否大于0（即是否存在超过100的值）返回结果IF...,有超标,无超标根据条件返回相应的文本结果函数嵌套时，Excel从内到外计算在复杂公式中，可以使用F9键测试选中部分的计算结果，帮助调试错误处理技巧IFERROR函数IFNA函数ISERROR函数捕获并处理公式中的错误专门处理#N/A错误检查是否是任何错误类型语法=IFERROR值,错误值语法=IFNA值,错误值语法=ISERROR值示例=IFERRORA1/B1,除数为零示例=IFNAVLOOKUPA1,B:C,2,FALSE,示例=IFISERRORA1/B1,0,A1/B1未找到常见错误类型解析错误值含义常见原因#N/A找不到引用的值VLOOKUP未找到匹配项#DIV/0!除数为零错误公式尝试除以零或空单元格#VALUE!无效的参数或操作数使用了错误的数据类型#REF!无效的单元格引用引用了已删除的单元格Excel函数快捷键与输入技巧1快速输入函数输入=后键入函数名首字母，然后从下拉列表中选择函数或按=+函数名+Tab快速展开函数参数2F4键切换引用类型在编辑公式时，选中单元格引用并反复按F4键可在相对、绝对和混合引用之间切换3使用函数向导按下公式选项卡中的插入函数按钮，或使用快捷键Shift+F3打开函数向导常见函数错误及排查方法#N/A错误#REF!错误#VALUE!错误表示未找到查找值或缺少值表示引用无效表示使用了错误的数据类型常见原因VLOOKUP未找到匹配项常见原因引用了已删除的单元格或超出范围的常见原因尝试将文本用于数学运算单元格解决方法检查拼写、格式，确保查找值确实存解决方法使用VALUE函数转换文本为数值，或在，或使用IFERROR函数处理解决方法重新指定有效的引用范围，或使用名检查单元格格式称管理器定义固定引用逐步调试公式技巧对于复杂公式，从内到外逐步测试每个部分，选中部分公式并按F9查看中间结果，帮助定位问题实战案例演练销售数据分析综合分析公式区域销售统计=IFERRORVLOOKUPB3,客户表!A:F,6,FALSE,0*SUMIFS销售表!D:D,=SUMIFS销售表!D:D,销售表!C:C,北京销售表!A:A,B3,销售表!F:F,=G3,销售表!F:F,=H3统计北京地区的销售总额此公式结合了查找和条件汇总功能，用于计算特定客户在指定日期范围内的销售额与折扣率乘积客户信息匹配•VLOOKUP查找客户折扣率•SUMIFS统计符合条件的销售额=VLOOKUPA2,客户表!A:E,3,FALSE•IFERROR处理可能的错误根据客户ID查找对应的联系人信息销售目标完成情况=IFE2/F2=1,已完成,未完成判断实际销售额是否达到目标销售额实战案例演练截图1数据准备整理销售数据、客户信息和产品目录三个表格2基础统计使用SUM、AVERAGE等函数计算基本销售指标3条件分析使用SUMIFS等条件汇总函数按地区、产品类别分析销售情况4查找匹配使用VLOOKUP或INDEX+MATCH关联多表数据5绩效评估使用IF函数评估销售目标完成情况课后练习建议基础练习高级练习•设计一个简单的成绩单，使用SUM、•设计销售数据表，使用SUMIFS、AVERAGE、MAX、MIN函数分析成COUNTIFS进行多条件统计绩•创建自动化报表，综合使用多种函数•创建一个员工信息表，使用•练习函数嵌套，如COUNT、COUNTA统计员工数量IF+VLOOKUP+SUMIFS组合应用•练习使用IF函数设计条件格式，如根探索新函数据销售额判断是否达标•尝试使用XLOOKUP替代VLOOKUP进阶练习（Excel365新函数）•创建产品库存表，使用VLOOKUP查•学习TEXTJOIN函数进行高级文本合询产品信息并•设计多条件筛选，使用AND、OR函•探索FILTER、SORT等动态数组函数数组合判断（Excel365）•练习使用INDEX+MATCH组合实现灵活查找资源推荐官方Excel函数帮助文档Microsoft Office官方网站提供了完整的Excel函数参考文档，包括详细的语法说明和示例支持中文搜索，可按函数类别浏览优质Excel学习网站Excel Home-国内最大的Excel中文社区，提供教程、模板和交流ExcelFunc.net-专注于Excel函数教学的网站Excel易用宝-提供丰富的Excel教程视频和实例视频课程推荐中国大学MOOC平台的Excel专项课程哔哩哔哩、腾讯课堂等平台上的Excel函数教学视频函数速查表建议下载Excel函数速查表，打印出来放在办公桌上，方便随时查阅包含最常用函数的语法、用途和简单示例结束语掌握函数，提升办公效率ExcelExcel函数不仅是办公技能，更是提升工作效率的重要工具通过本课程的学习，您已经掌握了Excel中最常用的函数及其应用场景持续练习和实际应用是提高Excel技能的关键希望您能将所学知识灵活运用到实际工作中，解决各种数据处理和分析问题感谢您的参与，欢迎随时提问交流，共同进步！。