《EXCEL中公式与函数的使用》教案

《EXCEL中公式与函数的使用》教案一、课程名称Excel公式与函数的高效应用二、授课对象具备Excel基本操作基础，希望提升数据处理与分析能力的职场人士、学生或爱好者。三、课程时长建议3-4课时（每课时约45-60分钟，可根据实际情况调整各模块深度）四、教学目标1.知识与技能：*深刻理解Excel公式的构成要素与运算原理。*熟练掌握单元格引用的类型（相对引用、绝对引用、混合引用）及其在公式中的灵活运用。*掌握常用函数（如SUM、AVERAGE、COUNT、IF等）的语法结构、参数含义及典型应用场景。*学会通过Excel的函数库和帮助系统查找并学习新函数。*能够结合实际需求，运用公式与函数解决数据计算、统计、分析等问题。2.过程与方法：*通过案例驱动和任务导向的方式，引导学员主动思考和实践操作。*培养学员分析问题、拆解问题，并运用Excel工具解决问题的能力。*鼓励学员探索不同函数组合的应用，提升数据处理的效率与准确性。3.情感态度与价值观：*认识到公式与函数在Excel数据处理中的核心地位，激发学习兴趣。*培养严谨细致的工作态度，减少数据处理中的错误。*提升学员利用Excel进行高效办公和辅助决策的信心。五、教学重点与难点1.教学重点：*公式的正确输入与编辑方法。*相对引用、绝对引用、混合引用的区别与应用场景。*常用函数（SUM,AVERAGE,COUNT,COUNTA,IF）的实际应用。2.教学难点：*绝对引用与混合引用的灵活运用，尤其是在公式复制时的表现。*IF函数的嵌套使用逻辑。*根据实际问题需求，选择合适的函数并正确设置参数。六、教学准备1.安装有MicrosoftExcel（建议Excel2016或以上版本）的计算机。2.投影仪、幕布、音响设备。3.教学课件（PPT）。4.学员练习用Excel工作簿（包含各类案例数据）。七、教学过程第一课时：Excel公式基础与单元格引用1.导入（约5分钟）*提问：在Excel中，当我们需要计算一系列数据的总和或平均值时，除了手动计算，还有更高效的方法吗？*引出主题：公式与函数是Excel的灵魂，是实现数据自动化处理的核心。本课程将带大家系统学习。2.Excel公式概述（约10分钟）*什么是公式：公式是Excel中用于计算的表达式，以等号“=”开头。*公式的构成：等号、运算符、常量、单元格引用、函数等。*公式的输入与编辑：*直接在单元格内输入。*在编辑栏中输入和修改。*公式输入完毕后按Enter键确认。*运算符：*算术运算符：+（加）、-（减）、*（乘）、/（除）、^（乘方）、%（百分比）。*比较运算符：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）。*（简要介绍，后续结合实例深入）*案例演示1：简单的加减乘除运算。例如：在单元格A3中计算A1+A2的值。3.单元格引用（约25分钟）*什么是单元格引用：用单元格的列标和行号来标识单元格，如A1、B3等。公式中使用单元格引用，可以使公式结果随被引用单元格数据的变化而自动更新。*相对引用：*概念：当公式被复制到其他单元格时，引用的单元格地址会相对原位置发生变化。*表示方法：直接使用列标和行号，如A1,B2。*案例演示2：计算学生成绩表中每位学生的总分（假设语文、数学、英语成绩分别在B列、C列、D列）。在E2输入=B2+C2+D2，然后向下填充公式。观察E3、E4等单元格的公式变化。*绝对引用：*概念：当公式被复制到其他单元格时，引用的单元格地址保持不变。*表示方法：在列标和行号前都加上美元符号“$”，如$A$1,$B$2。*案例演示3：计算学生成绩的加权平均分（假设各科权重在F1、G1、H1）。在I2输入=B2*$F$1+C2*$G$1+D2*$H$1，然后向下填充公式。观察权重引用是否变化。*混合引用：*概念：引用中一部分是相对的，一部分是绝对的。可以是列绝对行相对（如$A1），或行绝对列相对（如A$1）。*案例演示4：制作一个简易的乘法九九表。在B2输入=$A2*B$1，然后向右向下填充。解释列绝对和行绝对的作用。*学员练习1：提供一个简单的成绩单数据，让学员练习使用相对引用计算总分，使用绝对引用计算按固定比例的加分。4.课堂小结与提问（约5分钟）*回顾本课时重点：公式的构成、运算符、三种单元格引用方式及其区别。*解答学员疑问。第二课时：常用函数（一）——求和、平均值、计数1.复习回顾（约5分钟）*提问：相对引用和绝对引用的主要区别是什么？在什么情况下需要使用绝对引用？*快速演示一个包含相对引用和绝对引用的公式复制案例。2.函数概述（约10分钟）*什么是函数：函数是Excel预定义的内置公式，用于执行特定运算。*函数的结构：函数名(参数1,参数2,...)*函数名：如SUM,AVERAGE。*参数：函数运算所需的数据或单元格引用，参数之间用逗号分隔。参数可以是常量、单元格引用、区域引用，甚至其他函数。*函数的输入方法：*直接输入：=函数名(参数)*通过编辑栏的“插入函数”按钮（fx）：引导用户选择函数并设置参数，适合初学者。*利用函数下拉列表：快速选择最近使用或常用函数。*认识函数参数对话框：解释各部分含义，如何选择区域。3.常用函数详解与应用（约30分钟）*SUM函数（求和）：*功能：计算指定区域内所有数值的总和。*语法：SUM(number1,[number2],...)*案例演示5：*对单个区域求和：=SUM(B2:B10)*对多个不连续区域求和：=SUM(B2:B10,D2:D10)*结合常量求和：=SUM(B2:B10,50)*学员练习2：计算成绩单中各学科的总分。*AVERAGE函数（求平均值）：*功能：计算指定区域内所有数值的算术平均值。*语法：AVERAGE(number1,[number2],...)*案例演示6：计算某班级某学科的平均分：=AVERAGE(C2:C10)*强调：AVERAGE会忽略文本和空单元格。*COUNT函数（计数）：*功能：计算指定区域内包含数字的单元格的个数。*语法：COUNT(value1,[value2],...)*COUNTA函数（计数）：*功能：计算指定区域内非空单元格的个数（包括文本、数字等）。*语法：COUNTA(value1,[value2],...)*案例演示7：*统计参考人数（假设成绩为空表示缺考）：=COUNT(C2:C10)*统计总人数（包括缺考）：=COUNTA(B2:B10)（假设B列为姓名）*学员练习3：对成绩单数据进行各科平均分计算，并统计各科有效成绩人数（非空）和总参考人数（数字）。4.课堂小结与提问（约5分钟）*回顾本课时重点：SUM,AVERAGE,COUNT,COUNTA函数的功能与用法。*引导学员思考：这些函数在工作和学习中有哪些具体的应用场景？*解答学员疑问。第三课时：常用函数（二）——IF函数及嵌套，查找引用函数入门1.复习回顾（约5分钟）*快速回顾上节课学习的SUM,AVERAGE等函数。*提问：如果想统计成绩在90分以上的人数，用之前学的函数可以直接实现吗？引出条件判断的需求。2.IF函数（条件判断）（约20分钟）*功能：根据指定的条件进行判断，返回不同的结果。*语法：IF(logical_test,[value_if_true],[value_if_false])*logical_test：要判断的条件，结果为TRUE或FALSE。*value_if_true：当条件为TRUE时返回的值。*value_if_false：当条件为FALSE时返回的值（可选）。*案例演示8：*简单判断：=IF(C2>=60,"及格","不及格")（判断C2单元格成绩是否及格）*带嵌套的IF函数：=IF(C2>=90,"优秀",IF(C2>=80,"良好",IF(C2>=60,"及格","不及格")))（多条件判断成绩等级）*强调：IF函数可以嵌套，但嵌套层数不宜过多，以免逻辑混乱。Excel对嵌套层数有一定限制（如Excel2016及以上版本支持64层）。*学员练习4：对成绩单中的成绩进行等级评定（优秀、良好、及格、不及格）。3.查找与引用函数入门（以VLOOKUP为例）（约15分钟）*功能：在表格或区域的首列查找指定的值，并返回该值所在行中指定列处的数值。*语法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])*lookup_value：要查找的值。*table_array：要查找的区域，lookup_value必须位于该区域的第一列。*col_index_num：返回数据在查找区域中的列序号。*range_lookup：可选，TRUE（近似匹配，默认）或FALSE（精确匹配）。*案例演示9：*制作一个“学号-姓名”对照表，在另一个表中通过学号查找对应的姓名。*=VLOOKUP(A2,学号姓名表!$A$1:$B$100,2,FALSE)*强调：精确匹配时range_lookup务必设为FALSE；查找区域的首列必须是查找值所在列；为保证复制公式时查找区域不变，通常对查找区域使用绝对引用。*学员练习5：利用VLOOKUP函数根据员工ID从员工信息表中匹配出员工姓名。4.函数的组合应用初步（约5分钟）*案例演示10：=SUMIF(B2:B10,"销售部",C2:C10)（条件求和，简单介绍，为后续深入做铺垫）*引导学员思考：如何将已学函数结合起来解决更复杂的问题？5.课堂小结与提问（约5分钟）*回顾本课时重点：IF函数的单条件与多条件（嵌套）应用，VLOOKUP函数的基本用法。*解答学员疑问。第四课时：函数的高级应用与实践案例分析1.复习回顾（约5分钟）*回顾IF函数的嵌套和VLOOKUP函数。*提问：如果需要对满足多个条件的数据进行求和或计数，该如何操作？2.常用函数进阶（约20分钟）*SUMIFS函数（多条件求和）：*功能：对满足多个条件的单元格区域求和。*语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)*案例演示11：计算“销售部”且“业绩>____”的员工的总业绩。*COUNTIFS函数（多条件计数）：*功能：计算满足多个条件的单元格个数。*语法：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...)*案例演示12：统计“销售部”且“性别为男”的员工人数。*MAX/MIN函数（最大值/最小值）：*功能：返回指定区域中的最大值/最小值。*语法：MAX(number1,[number2],...)/MIN(number1,[number2],...)*LEFT/RIGHT/MID函数（文本提取）：(简要介绍，根据学员需求和课时灵活调整)*功能：分别从文本字符串的左侧、右侧或中间提取指定数量的字符。3.综合案例分析与实践（约15分钟）*案例：提供一份包含员工基本信息、部门、销售额、入职日期等数据的综合表格。*任务：1.计算各部门的总销售额。2.计算各部门的平均销售额。3.统计各部门销售额超过某一阈值的员工人数。4.对员工销售额进行等级评定（如S,A,B,C）。5.根据员工ID查找其部门和销售额。*引导学员分组讨论或独立思考，运用所学公式与函数逐步完成任务。教师巡视指导，对共性问题进行集中讲解。4.函数学习资源与技巧（约5分钟）*如何利用Excel的“插入函数”对话框和帮助信息学习新函数。*常用的Excel快捷键（如F2编辑单元格，F4切换引用方式）。*注意公式中的中英文符号问题。*推荐一些Excel函数学习网站或书籍。5.课程总结与Q&A（约5分钟）*总结本课程学习的主要内容：公式基础、单元格引用、常用函数（SUM,AVERAGE,COUNT,IF,VLOOKUP,SUMIFS等）及其应用。*强调实践的重要性，鼓励学员在实际工作中多应用、多探索。*预留时间解答学员的疑问，并收集课程反馈。八、课后练习与拓展1.基础练习：完成课堂上未完成的