《计算机应用基础 Win10+Office 2016》教案 模块四 Excel 2016的应用（二）

PAGE8PAGE8PAGE9PAGE9

课题Excel2016的应用（二）——处理学生成绩表数据课时2课时（90min）教学目标知识技能目标：了解公式和函数的概念，并认识公式中的运算符了解单元格引用的作用和类型掌握利用公式和函数对工作表数据进行计算与分析的操作掌握利用条件格式对满足特定条件的单元格以醒目方式突出显示的方法掌握保护工作表数据的方法素质目标：熟悉国家政策，增强数据安全意识、法律意识；了解救援抗灾事件，培养热爱祖国、关爱他人的美德教学重难点教学重点：Excel2016中公式和函数的概念及应用教学难点：（1）利用公式和函数对工作表数据进行计算与分析的操作（2）利用条件格式对满足特定条件的单元格以醒目方式突出显示的方法教学方法演示法、问答法、讲授法、情景教学法教学用具电脑、投影仪、多媒体课件、教材教学设计第1节课：→→→传授新知（5min）→项目讲解（7min）→→项目讲解（20min）第2节课：实操演练（15min）→项目讲解（5min）→实操演练（5min）→项目讲解（7min）→实操演练（8min）→课堂小结（3min）→作业布置（2min）教学过程主要教学内容及步骤设计意图第一节课课前任务【教师】布置课前任务，和学生负责人取得联系，让其提醒同学通过文旌课堂APP或其他学习软件，完成课前任务（1）请大家提前预习本节课内容。（2）了解《中华人民共和国数据安全法》。【学生】完成课前任务通过课前任务，让学生提前了解所学课程的内容，增加学生的求知欲考勤

（2min）【教师】使用文旌课堂APP进行签到【学生】班干部报请假人员及原因培养学生的组织纪律性，掌握学生的出勤情况情景导入（3min）【教师】讲述“小谭对表格中的数据进行计算和处理”情景，并提出以下问题：如果你是小谭，你打算如何帮助李老师处理成绩表中的数据呢？通过情景导入的方法，引导学生主动思考，使学生身临其境，激发学生的学习兴趣传授新知（5min）【教师】通过PPT课件来展示计算和处理完成的工作表的效果，并讲解相关知识认识公式、函数和公式中的运算符公式和函数公式由运算符和参与运算的操作数组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格引用和函数等。要输入公式必须先输入“=”，然后在其后输入运算符和操作数，否则Excel会将输入的内容作为文本型数据处理。函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，其中函数名表示将执行的操作（如求平均值函数AVERAGE），参数表示函数将使用的值的单元格地址，通常是一个单元格区域，也可以是更为复杂的内容。在公式中合理地使用函数，可以完成诸如求和、求平均值、逻辑判断等数据处理操作。公式中的运算符（1）文本运算符。使用文本运算符“&”（与号）可将两个或多个文本值串起来产生一个连续的文本值。例如：输入“祝你”&“快乐、开心！”会生成“祝你快乐、开心！”。（2）算术运算符。算术运算符如表所示，其作用是完成基本的数学运算，并产生数值结果算术运算符含义实例+（加号）加法A1+A2-（减号）减法或负数A1-A2*（星号）乘法A1*2/（正斜杠）除法A1/3%（百分号）百分比50%^（脱字号）乘方2^3（3）比较运算符。比较运算符如表所示。它们的作用是比较两个值，并得出一个逻辑值，即“TRUE”（真）或“FALSE”（假）。比较运算符含义比较运算符含义>（大于号）大于>=（大于等于号）大于等于<（小于号）小于<=（小于等于号）小于等于=（等于号）等于<>（不等于号）不等于（4）引用运算符。运算引用运算符含义实例:（冒号）区域运算符，用于引用单元格区域B5:D15,（逗号）联合运算符，用于引用多个单元格区域B5:D15,F5:I15（空格）交叉运算符，用于引用两个单元格区域的交叉部分B7:D7C6:C8单元格引用相同或不同工作簿、工作表间的引用对于同一工作表中的单元格引用，直接输入单元格或单元格区域地址即可。在当前工作表中引用同一工作簿、不同工作表中的单元格的表示方法为工作表名称!单元格或单元格区域地址在当前工作表中引用不同工作簿中的单元格的表示方法为[工作簿名称.xlsx]工作表名称!单元格（或单元格区域）地址相对引用、绝对引用和混合引用（1）相对引用：Excel默认的单元格引用方式。它直接用单元格的列标和行号表示单元格，如B5；或用引用运算符表示单元格区域，如B5:D15。在移动或复制公式时，系统会根据移动的位置自动调整公式中相对引用的单元格地址。（2）绝对引用：指在单元格的列标和行号前面都加上“$”符号，如$B$5。不论将公式复制或移动到什么位置，绝对引用的单元格地址都不会改变。（3）混合引用：指引用中既包含绝对引用又包含相对引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。三、Excel中的常用函数Excel提供了大量的函数，下表列出了常用的函数类型和使用范例。函数类型函数使用范例常用SUM（求和）、AVERAGE（求平均值）、MAX（求最大值）、MIN（求最小值）、COUNT（计数）等=AVERAGE(F2:F7)

表示求F2:F7单元格区域中数字的平均值财务DB（资产的折扣值）、IRR（现金流的内部报酬率）、PMT（分期偿还额）等=PMT(B4,B5,B6)

表示在输入利率、周期和规则作为变量时，计算周期支付值日期

与时间DATA（日期）、HOUR（小时数）、SECOND（秒数）、TIME（时间）等=DATA(C2,D2,E2)

表示返回C2,D2,E2所代表的日期数学

与三角=ABS(E4)

表示得到E4单元格中数值的绝对值，即不带负号的绝对值统计AVERAGE（求平均值）、RANK.EQ（求大小排名）、COUNTIF（统计单元格区域中符合指定条件的单元格数）=COUNTIF(H3:H13,">=120")

表示求H3:H13单元格区域中数据大于等于120的单元格数逻辑AND（与）、OR（或）、FALSE（假）、TRUE（真）、IF（如果）、NOT（非）表示使用条件测试A3是否大于等于B5，条件结果要么为真，要么为假保护数据的方法工作簿制作好后，用户可以利用Excel提供的保护功能，通过设置密码的方式，对其结构和窗口进行保护，或对整个工作表及工作表中的部分单元格进行保护，以防止他人进行修改。【学生】聆听、思考、理解、记录通过教师的讲解，使学生了解Excel2016计算和处理工作表中数据的方法项目讲解（7min）【教师】通过多媒体对计算和处理学生成绩表的数据进行分步骤演示讲解小谭通过以上知识的学习已经对处理学生成绩表中的数据跃跃欲试了，下面我们就来看看小谭是如何一步步将其处理完成的吧！一、使用求和按钮计算平均分（具体步骤见教材）（1）打开本书配套素材“模块四”/“项目一”/“学生成绩表（美化）”工作簿，将其以“学生成绩表（处理）”为名另存到“模块四”/“项目二”文件夹中。（2）单击要计算平均分的单元格H3，然后单击“开始”选项卡“编辑”组中的“求和”按钮右侧的下拉按钮，在展开的下拉列表中选择“平均值”选项。（3）此时，可看到单元格和编辑栏中自动显示要计算平均值的单元格区域，对该区域进行确认。如果不正确的话，可以在工作表中拖动鼠标重新选择，这里保持默认。（4）按“Enter”键，即可计算出第一个学生的平均分。（5）选中含有公式的单元格H3，将鼠标指针移到该单元格右下角的填充柄处，此时鼠标指针由空心十字形变成实心的十字形，按住鼠标左键向下拖动，至目标位置后释放鼠标，将求平均值公式复制到同列的其他单元格中，计算出其他学生的平均分。二．使用公式计算总分（具体步骤详见教材）（1）单击要计算总分的单元格I3，输入等号“=”，然后输入要参与运算的单元格和运算符E3+F3+G3，也可以直接单击要参与运算的单元格，将其添加到公式中。（2）按“Enter”键或单击编辑栏中的“输入”按钮结束公式编辑，计算出第一个学生的总分。（3）将鼠标指针移到I3单元格右下角的填充柄处，待鼠标指针变成实心的十字形时，按住鼠标左键向下拖动，至目标位置后释放鼠标，将求和公式复制到同列的其他单元格中，计算出其他学生的总分。【学生】聆听、思考、理解、记录通过一边演示一边讲解Excel2016计算和处理数据的过程，让学生更加直观的了解Excel2016的功能和作用，方便学生记忆实操演练（8min）【教师】组织学生以小组为单位，按照上述步骤进行实际操作假如你是小谭，你会不会以上两个任务的操作呢？试试看吧！【学生】聆听、思考、处理数据【教师】巡堂辅导，及时解决学生遇到的问题通过实操演练加深学生的记忆，让学生趁热打铁，增加学生学习的信心项目讲解（20min）【教师】通过多媒体对计算和处理学生成绩表的数据进行分步骤演示讲解学会了求和按钮的使用和在单元格中输入公式的方法，下面要引入函数来处理数据了，先来看看小谭是怎样做的吧！三．常用函数的应用（具体步骤详见教材）1．使用RANK.EQ函数计算总分排名（1）单击“名次”列中的单元格J3，然后单击编辑栏左侧的“插入函数”按钮，打开“插入函数”对话框，选择“统计”类别，然后选择“RANK.EQ”函数。（2）单击“确定”按钮，打开“函数参数”对话框，单击第一个参数编辑框，然后在工作表中选择要进行排位的单元格I3。（3）单击第2个参数编辑框，然后在工作表中拖动鼠标选择参与排位的单元格区域I3:I32，松开鼠标可在编辑框中看到选择的单元格区域。（4）按键盘上的“F4”键将选择的单元格区域转换为绝对引用，这样可以保证后面复制排序公式时，公式内容不变，从而使返回的排名准确。（5）单击“确定”按钮，计算出第一个学生的排名名次，即J3单元格在单元格区域I3:I32中的排名。拖动J3单元格的填充柄到单元格J32，计算出其他学生的名次。2．使用IF函数判断平均分等次（1）根据假设条件，在K3单元格中输入公式“=IF(G3>=90,"优",IF(G3>=80,

"良",IF(G3>=70,"中",IF(G3>=60,"及格",IF(G3<60,"不及格",0)))))”。（2）向下拖动K3单元格的填充柄到K32单元格后释放鼠标，可依据平均分判断出所有学生的等次。3．使用MAX和MIN函数计算总分最高分和最低分（1）在工作表数据的下方创建计算表格。（2）在单元格F34输入公式“=MAX(I3:I32)”，按“Enter”键得到总分最高分。（3）在单元格F35输入公式“=MIN(I3:I32)”，按“Enter”键得到总分最低分。4．使用COUNTIF和COUNT函数统计人数（1）在单元格F36输入公式“=COUNTIF(E3:E32,">=60")”，高等数学课程的及格人数。（2）在单元格F37输入公式“=COUNT(F3:F32)”，按“Enter”键得到专业英语课程的实际参加考试人数。5．使用REPLACE函数替换学号（1）在“学号”列的右侧插入一个辅助列。右击B列，在弹出的快捷菜单中选择“插入”选项。（2）在辅助列的B3单元格输入公式“=REPLACE(A3,1,2,"18")”，按“Enter”键得到替换结果。（3）向下拖动B3单元格的填充柄，到B32单元格后释放鼠标，可看到所有学号的替换效果。（4）选中B3:B32单元格区域，然后按“Ctrl+C”组合键，将选择的单元格区域数据复制，单击A3单元格，然后单击“开始”选项卡“剪贴板”组中“粘贴”按钮下方的下拉按钮，在展开的下拉列表中选择“值”选项，即可将所选数据以值方式粘贴。（5）右击辅助列B，在弹出的快捷菜单中选择“删除”选项，将辅助列删除。6．使用VLOOKUP函数奖励不同等次的学生（1）根据假设条件，在L3单元格输入公式“=VLOOKUP(K3,{"优",200;"良",150;"中",100;"及格",50;"不及格",0},2,)”。（2）按“Enter”键得到查找结果，向下拖动L3单元格的填充柄到L32单元格后释放鼠标，可看到所有学生的奖学金。（3）在单元格F35输入公式“=MIN(I3:I32)”，按“Enter”键得到总分最低分。7．使用SUMIF函数计算女生获得的奖学金总额（1）在F38单元格输入公式“=SUMIF(D3:D32,"女",L3:L32)”。（2）按“Enter”键得到计算结果。【学生】聆听、思考、理解、记录通过一边演示一边讲解Excel2016计算和处理数据的过程，让学生更加直观的了解Excel2016的功能和作用，方便学生记忆第二节课实操演练（15min）【教师】组织学生以小组为单位，按照上述步骤进行实际操作下面请同学们跟着小谭的步伐将以上7种常用函数应用到表格中吧！【学生】聆听、思考、处理数据【教师】巡堂辅导，及时解决学生遇到的问题通过实操演练加深学生的记忆，让学生趁热打铁，增加学生学习的信心项目讲解（5min）【教师】通过多媒体对计算和处理学生成绩表的数据进行分步骤演示讲解在Excel中应用条件格式，可以让满足特定条件的单元格以醒目方式突出显示，便于对工作表数据进行更好的比较和分析。我们先来看看小谭是怎样做的。四．使用条件格式标识学生成绩（具体步骤详见教材）（1）选择要添加条件格式的单元格区域，本例选择E3:G32单元格区域。（2）单击“开始”选项卡“样式”组中的“条件格式”按钮，在展开的下拉列表中选择“突出显示单元格规则”选项，再在展开的子列表中选择一种具体的条件，如“大于”选项。（3）打开“大于”对话框，设置“大于”对话框中的参数。（4）单击“确定”按钮。此时，各课程成绩大于80的单元格，背景为浅红色，字体颜色为深红色显示。（5）保持单元格区域的选中，在“条件格式”下拉列表中选择“突出显示单元格规则”/“介于”选项，打开“介于”对话框，设置突出显示参数，最后单击“确定”按钮。（6）保持单元格区域的选中，使用同样的方法打开“介于”对话框，设置突出显示参数，最后单击“确定”按钮，即可将成绩数据工作表中各分数段突出显示。【学生】聆听、思考、理解、记录通过一边演示一边讲解Excel2016计算和处理数据的过程，让学生更加直观的了解Excel2016的功能和作用，方便学生记忆实操演练（5min）【教师】组织学生以小组为单位，按照上述步骤进行实际操作下面请同学们也来帮助李老师使用条件格式对学生的成绩进行标识吧！【学生】聆听、思考、处理数据【教师】巡堂辅导，及时解决学生遇到的问题通过实操演练加深学生的记忆，让学生趁热打铁，增加学生学习的信心项目讲解（7min）【教师】通过多媒体对计算和处理学生成绩表的数据进行分步骤演示讲解2021年6月10日，第十三届全国人民代表大会常务委员会第二十九次会议通过《中华人民共和国数据安全法》，自2021年9月1日起施行。这是我国第一部以数据为保护对象的法律，作为全球数据安全综合立法的首创性探索，对于全球数据安全、利用安全具有引领和示范意义。那么Excel2016是如何进行数据保护的呢？我们来看看小谭的做法……五．保护数据（具体步骤详见教材）1．保护工作簿（1）选中要进行保护的工作簿，单击“审阅”选项卡“保护”组中的“保护工作簿”按钮。（2）在打开的对话框中选中“结构”复选框，然后在“密码”编辑框中输入保护密码并单击“确定”按钮，再在打开的对话框中输入同样的密码并确定。（3）对工作簿执行保存操作后，删除、移动、复制、重命名、隐藏工作表或插入新的工作表等操作均无效（但允许对工作表内的数据进行操作）。（4）要撤销工作簿的保护，可单击“审阅”选项卡“保护”组中的“保护工作簿”按钮，在打开的对话框中输入保护工作簿时设置的密码然后保存，即可撤销工作簿的保护。2．保护工作表（1）在工作簿中选择要进行保护的工作表，如“成绩数据”，然后单击“审阅”选项卡“保护”组中的“保护工作表”按钮，或在右键菜单中选择“保护工作表”选项，打开“保护工作表”对话框。（2）在“取消工作表保护时使用的密码”编辑框中输入密码；在“允许此工作表的所有用户进行”列表框中选择允许操作的选项，然后单击“确定”按钮，并在随后打开的对话框中输入同样的密码后单击“确定”按钮。（3）此时，工作表中的所有单元格都被保护起来，不能进行在“保护工作表”对话框中没有选择的操作。如果试图进行这些操作，系统会弹出提示对话框，提示用户该工作表是受保护。（4）要撤销工作表的保护，只需单击“审阅”选项卡“保护”组中的“撤销工作表保护”按钮。若设置了密码保护，此时会打开“撤销工作表保护”对话框，输入保护时的密码，方可撤销工作表的保护。3．保护单元