Office 2010办公软件高级应用07使用Excel计算表格数据

第1页Office 2010办公软件高级应用第2页项目七使用Excel计算表格数据第3页项目描述Excel强大的计算功能主要依赖于其公式和函数，利用公式和函数可以对表格中的数据进行各种计算和处理，从而提高制作复杂表格的效率和计算准确率。本项目中，通过计算期中考试成绩表和教师考评表数据，学习公式和函数的使用。第4页目录 Content1任务一 计算期中考试成绩表数据2任务二 计算教师考评表数据3Excel高效办公技巧第5页学习要点1学习使用公式计算数据的方法2学习自定义数字格式的方法3学习利用工作组在多个工作表的相同位置输入和编辑相同数据的方法第6页目录 Content1任务一 计算期中考试成绩表数据2任务二 计算教师考评表数据3Excel高效办公技巧第7页任务一 计算期中考试成绩表数据王老师刚把期中考试的各科成绩录入到Excel工作表中，正在考虑怎么让软件自动计算平均分和总分等，他的同学小郭就来找他了。巧的是，小郭对Excel特别熟悉，于是，在他的帮助下，王老师轻松地计算出了期中考试成绩表中各学生的总分、平均分，各科目的最高分与最低分，并按总分对学生成绩进行了年级排名。情景引入第8页任务一 计算期中考试成绩表数据任务描述本任务计算后的期中考试成绩表效果如图所示。案例的最终效果参见本书配套素材“素材与实例项目七期中考试成绩表（计算）”文件。第9页任务一 计算期中考试成绩表数据对工作表进行简单的格式化0303打开素材工作簿0101依次计算总分、平均分、年级排名、各科目的最高分和最低分0202制作本案例的大致思路为：第10页任务一 计算期中考试成绩表数据公式是对工作表中的数据进行计算的表达式，它由运算符和参与运算的操作数组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格引用和函数等。要输入公式必须先输入“=”，然后再在其后输入运算符和操作数，否则Excel会将输入的内容作为文本型数据处理。相关知识一、认识公式和函数第11页任务一 计算期中考试成绩表数据=A2*B5/B6+100 =AVERAGE(A2：B7)*A4/3单元格引用函数单元格引用运算符：*（乘）、/（除）常量常量运算符：*（乘）、/（除）、+（加）如图所示分别是在某个单元格中输入的未使用函数和使用函数的公式。左图公式的意义是：求A2单元格与B5单元格之积再除以B6单元格后加100的值；右图所示公式的意义是：使用函数AVERAGE求A2:B7单元格区域的平均值，并将求出的平均值乘以A4单元格后再除以3。计算结果将显示在输入公式的单元格中。第12页任务一 计算期中考试成绩表数据函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，其中函数名表示将执行的操作（如求平均值函数AVERAGE），参数表示函数将使用的值的单元格地址，通常是一个单元格区域，也可以是更为复杂的内容。在公式中合理地使用函数，可以完成诸如求和、求平均值、逻辑判断等功能。第13页任务一 计算期中考试成绩表数据运算符是用于对公式中的元素进行运算的特殊符号。Excel包含4种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。二、公式中的运算符第14页任务一 计算期中考试成绩表数据1算术运算符算术运算符有6个，如表所示。其作用是完成基本的数学运算，并产生数字结果。算术运算符含 义实 例+（加号）加法A1+A2-（减号）减法或负数A1-A2*（星号）乘法A1*2/（正斜杠）除法A1/3%（百分号）百分比50%（脱字号）乘方23第15页任务一 计算期中考试成绩表数据2比较运算符比较运算符有6个，如表所示。它们的作用是比较两个值，并得出一个逻辑值，即“TRUE”（真）或“FALSE”（假）。比较运算符含 义（大于号）大于=（大于等于号）大于等于=（小于等于号）小于等于（不等于号）不等于第16页任务一 计算期中考试成绩表数据3文本运算符使用文本运算符“”（与号）可将两个或多个文本值连起来产生一个连续的文本值。例如：输入“=祝你开心、快乐！”会生成“祝你开心、快乐！”。第17页任务一 计算期中考试成绩表数据4引用运算符引用运算符有3个，如表所示。它们的作用是对单元格区域中的数据进行合并计算。引用运算符含 义实 例:（冒号）区域运算符，用于引用单元格区域B5:D15,（逗号）联合运算符，用于引用多个单元格区域B5:D15,F5:I15（空格）交叉运算符，用于引用两个单元格区域的交叉部分B7:D7 C6:C8第18页任务一 计算期中考试成绩表数据单元格引用用于指明公式中所使用的数据的位置，它可以是一个单元格地址，也可以是单元格区域。通过单元格引用，可以在一个公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格中的数据；还可以引用同一个工作簿不同工作表中的数据。当公式中引用的单元格数值发生变化时，公式的计算结果也会自动更新。三、单元格引用第19页任务一 计算期中考试成绩表数据1相同或不同工作簿、工作表中的引用对于同一工作表中的单元格引用，直接输入单元格或单元格区域地址即可。在当前工作表中引用同一工作簿，不同工作表中的单元格的表示方法为：工作表名称!单元格或单元格区域地址例如，Sheet2!F8:F16，表示引用Sheet2工作表，F8:F16单元格区域中的数据。在当前工作表中引用不同工作簿中的单元格的表示方法为：工作簿名称.xlsx工作表名称！单元格或单元格区域地址第20页任务一 计算期中考试成绩表数据2相对引用、绝对引用和混合引用相对引用：是Excel默认的单元格引用方式，它直接用单元格的列标和行号表示单元格，如B5；或用引用运算符表示单元格区域，如B5:D15。在移动或复制公式时，系统会根据移动的位置自动调整公式中引用的单元格地址。绝对引用：绝对引用是指在单元格的列标和行号前面都加上“$”符号，如$B$5。不论将公式复制或移动到什么位置，绝对引用的单元格地址都不会改变。混合引用：指引用中既包含绝对引用又包含相对引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。第21页任务一 计算期中考试成绩表数据任务实施一、格式化期中考试成绩表格式化后的工作表第22页任务一 计算期中考试成绩表数据二、计算总分单击M3单元格，然后单击“开始”选项卡“编辑”组中的“自动求和”按钮 ，接着在工作表中拖动鼠标选择要进行求和计算的单元格区域D3:L3，按“Enter”键得到第1位学生的总分，如图所示。第23页任务一 计算期中考试成绩表数据三、计算年级排名和各科最高最低分输入公式计算年级排名第24页目录 Content1任务一 计算期中考试成绩表数据2任务二 计算教师考评表数据3Excel高效办公技巧第25页任务二 计算教师考评表数据又到季末，园长让小张统计一下大家第二季度的考评情况，然后据此计算出季度考评数据，并按“季度总成绩”进行排名，最后根据园里的规定给排名前3位的员工以一定数额的奖金。情景引入第26页任务二 计算教师考评表数据任务描述本任务计算出的教师考评表数据如图所示。案例的最终效果参见本书配套素材“素材与实例项目七幼儿园教师考评表”。第27页任务二 计算教师考评表数据制作本案例的大致思路为：根据季度总成绩进行排名，最后根据条件利用IF函数判断员工的奖金额。0505制作出员工信息和考核项目分值表0101在取消成组后根据实际情况修改相关工作表数据0303利用工作组快速制作其他表格0202计算季度考评工作表中各考核项目的季度平均值和季度总成绩0404第28页任务二 计算教师考评表数据相关知识自定义数字格式Excel中的数据类型有常规、数字、货币、会计专用、日期、时间、百分比、分数和文本等。为单元格中的数据设置不同数字格式只是更改它的显示形式，不影响其实际值。若想为单元格中的数据快速设置会计数字格式、百分比样式、千位分隔、增加或减少小数位数，可选中数据后直接单击“开始”选项卡“数字”组中的相应按钮 ；若希望设置更多的数字格式，可打开“设置单元格格式”对话框，在“数字”选项卡中设置。第29页任务二 计算教师考评表数据配合“Shift”键或“Ctrl”键在工作簿中选中多个工作表时，这些工作表即构成工作组，并且在当前工作簿的标题栏中会出现“工作组”字样。此时，用户可在所选的多个工作表的相同位置一次性输入或编辑相同的内容，该功能在制作内容相似的工作表时非常有用。成组工作表第30页任务二 计算教师考评表数据任务实施一、制作员工信息、考核项目分值、月份和季度考评表自定义单元格区域的数字格式第31页任务二 计算教师考评表数据选择该项，表示复制单元格，即源单元格与填充的单元格内容和格式完全相同选择该项，表示只填充单元格格式选择该项，表示只填充单元格的内容选择该项，表示以系统内置的或用户自定的序列填充单元格输入“编号”列数据第32页任务二 计算教师考评表数据在成组工作表中利用公式输入数据并进行简单的格式化第33页任务二 计算教师考评表数据二、计算季度考评数据计算第1位员工的“师德”月平均值第34页任务二 计算教师考评表数据单击“自动求和”按钮后显示的计算公式第35页任务二 计算教师考评表数据利用填充柄计算出所有员工所有考核项目的季度总成绩第36页任务二 计算教师考评表数据三、计算排名和奖金数据输入公式计算排名第37页任务二 计算教师考评表数据输入公式计算奖金第38页目录 Content1任务一 计算期中考试成绩表数据2任务二 计算教师考评表数据3Excel高效办公技巧第39页Excel高效办公技巧防止在编辑栏显示公式对于工作表中某些重要的计算公式，为防止他人查看，用户可以将其隐藏。为此，可选中要隐藏公式的单元格或单元格区域，然后打开“设置单元格格式”对话框，切换到“保护”选项卡，选中“隐藏”复选框（如图所示），最后单击“确定”按钮。第40页Excel高效办公技巧对不相邻的单元格的数据求和对于相邻的单元格，用户可以利用“求和”按钮对其进行求和计算。对于不相邻的单元格，用户可以在要放置结果的单元格中输入“=”，然后单击“开始”选项卡“编辑”组中的“求和”按钮 ，此时出现公式“=SUM()”（如图所示）第41页Excel高效办公技巧接着单击要进行计算的第一个单元格，并在其后输入逗号；单击第二个单元格，输入逗号；重复操作；单击要进行求和的最后一个单元格，按“Enter”键得到结果，如图所示。第42页Excel高效办公技巧快速查看所有工作表中的公式要在工作表的单元格中显示计算的公式而不是结果值，可单击工作表中的任意单元格，然后单击“公式”选项卡“公式审核”组中的“显示公式”按钮 ，此时可在单元格中显示计算公式，如图所示。第43页Excel高效办公技巧粘贴特殊格式在将工作表中复制的内容粘贴到其他位置时，用户可以在“粘贴”下拉列表中选择粘贴格式，如图所示。从左至右依次为粘贴、公式、公式和数字格式、保留源格式从左至右依次为无边框、保留源列宽、转置、合并条件格式从左至右依次为值、值和数字格式、值和源格式从左至右依次为格式、粘贴链接、图片、链接的图片选择“选择性粘贴”选项，可打开“选择性粘贴”对话框，在其中除了可以指定粘贴的内容外，还可以进行加、减、乘、除的数学运算第44页项目总结（1）（2）（3）（4）了解公式和函数的概念，了解公式中的运算符及其作用掌握在单元格中输入公式及利用填充柄复制公式的方法。要输入公式，必需先输入“=”，然后再在其后输入表达式引用的作用是通过标识工作表中的单元格或单元格区域，来指明公式中所使用的数据的位置。只有理解了相对引用、绝对引用和混合引用的含义，才能在公式中正确使用单元格引用。掌握使用常用函数计算工作表数据的方法第45页课后实训1.计算“素材与实例项目七办公用品明细表”工作簿每件商品的金额；2.计算“素材与实例项目七得分统计表”工作簿，每位选手的总分、平均得分，并按平均得分进行排名，最后计算表格中出现次数最多的分数。见本书第178179页。