《办公自动化案例教程》(贺鑫)852-6课件-项目七-使用Excel计算表格数据

办公自动化案例教程Windows7+Office2010目录计算水电费开支表数据计算学生成绩一览表

数据并设置级别计算员工考核表数据计算员工工资表数据任务一计算水电费开支表数据制作思路：新建工作簿后将工作表重命名，然后将其中的2个工作表成组，再在其中的一个工作表中输入基础数据并格式化，取消成组后修改另一个工作表数据，接着在“合计”工作表中制作表格，最后在各工作表中输入公式计算工作表数据。

一、输入工作表基本数据（1）新建“水电费开支”工作簿，然后分别将“Sheet1”，“Sheet2”和“Sheet3”重命名为8月、9月和合计。将8月和9月2个工作表成组，然后在8月工作表中输入基础数据并格式化。

（2）取消成组工作表状态后将9月工作表中的数据进行修改。再在“合计”工作表中制作表格。

二、使用公式计算工作表数据（1）在“8月”工作表中单击E4单元格，然后输入公式“=D4-C4”，然后向下拖动E4单元格右下角的填充柄到E13单元格后释放鼠标，计算出8月每个人的用电量。

（2）单击F4单元格，根据第16行中的说明，输入公式“=E4*$B$15”，按【Enter】键得到第1个租户的电费金额。拖动F4单元格右下角的填充柄至F13单元格后释放鼠标，得到其他租户的电费金额。

（3）单击I4单元格，输入公式“=H4-G4”，然后向下拖动I4单元格右下角的填充柄到I13单元格后释放鼠标，计算出8月的用水量。

（4）单击J4单元格，然后输入公式“=I4*$D$15”4，按【Enter】键得到第1个租户的水费金额。拖动J4单元格右下角的填充柄至J13单元格后释放鼠标，得到所有租户的水费金额。

（5）分别单击F14和J14单元格，然后单击“开始”选项卡上“编辑”组中的“自动求和”按钮，然后按【Enter】键计算出电费和水费合计值。（6）用同样的方法计算“9月”工作表中的水费、电费及其合计值。

三、计算水电费合计值（1）在“合计”工作表的B2单元格单击并输入等号“=”，然后单击“8月”工作表的F14单元格，输入加号“+”后再单击“9月”工作表中的F14单元格，然后按【Enter】键，计算出8～9月的电费合计。（2）在“合计”工作表的B3单元格单击并输入等号“=”，然后单击“8月”工作表的J14单元格，输入加号“+”后再单击“9月”工作表中的J14单元格，然后按【Enter】键，计算出8～9月的水费合计。从编辑栏中可看到不同工作表中的单元格引用。

任务二计算学生成绩一览表数据

并设置级别制作思路：首先在工作表中输入基础数据，再格式化工作表，然后依次计算总分、最高分、平均分、综合分和备注。

一、输入学生成绩一览表基本数据新建“学生成绩一览表”工作簿，然后在“Sheet1”工作表中输入数据并进行格式化。其中A4:A13单元格区域的数字格式为文本，灰色的空白填充区域是要进行计算的单元格。

二、计算总分、最高分和平均分（1）单击G4单元格后单击“开始”选项卡上“编辑”组中的“自动求和”按钮，按【Enter】键得到排到第1位学生的总分。向下拖动G4单元格右下角的填充柄至G13单元格后释放鼠标，得到其他学生的总分。

（2）单击C14单元格，然后在“自动求和”列表中选择“最大值”项，按【Enter】键得到结果。向右拖动C14单元格右下角的填充柄到G14单元格后释放鼠标，计算其他科目的最高分。

（3）单击C15单元格，然后在“自动求和”列表中选择“平均值”项，再在工作表中重新选择要进行求平均值的单元格区域，按【Enter】键得到结果，然后向右拖动C15单元格右下角的填充柄到G15单元格后释放鼠标，计算其他科目的平均分。

三、计算综合分并判断成绩（1）根据比例系数，在H4单元格输入公式，计算第1位学生的综合分。向下拖动H4单元格右下角的填充柄到H13单元格后释放鼠标，计算出所有学生的综合分。（2）假设综合分大于等于60分为及格，否则为不及格。在I4单元格输入公式，按【Enter】键判断出排在第1位的学生的成绩是否及格。向下拖动I4单元格右下角的填充柄至I13单元格的释放鼠标，判断出其他学生的成绩及格与否。

任务三计算员工考核表数据制作思路：首先制作出勤量统计表和绩效表，然后根据这两个工作表数据利用函数INDEX和直接引用单元格的方法计算各季度的考核情况，再计算出年度考核情况表，根据年度总成绩进行排名，最后根据排名确定奖金额，并利用LOOKUP函数计算员工的奖金额。

一、制作出勤量统计表、绩效表及其他季度和年度考核表（1）制作“出勤量统计表”。新建“员工考核表”工作簿，将“Sheet1”工作表重命名为“出勤量统计表”。（2）在“出勤量统计表”的A1单元格输入表格标题文本“出勤量统计表”，接着在A2和B2单元格输入列标题——编号和姓名。（3）在C2单元格输入“第一季度”文本后按【Alt+Enter】组合键换行，接着输入“出勤量”文本。用同样的方法输入其他列的列标题。

（4）自定义单元格区域的数字格式。选中A3:A11单元格区域，然后单击“开始”选项卡上“数字”组右下角的对话框启动器按钮，打开“设置单元格格式”对话框，在“分类”列表选择“自定义”项，然后在“类型”编辑框中输入“0000”，最后单击“确定”按钮。

（5）在A3单元格输入“0001”，然后拖动A3单元格右下角的填充柄至A11单元格，释放鼠标，单击“粘贴选项”按钮，在展开的列表中选择“填充序列”项。

（6）在其他单元格中输入数据，然后设置表格的格式，出勤量统计表制作完毕。（7）制作“绩效表”。将“Sheet2”工作表重命名为“绩效表”，然后用制作“出勤量统计表”的方法在其中输入数据并设置格式。

（8）制作季度和年度考核表。将“Sheet3”工作表重命名为“第一季度考核表”，然后再新建4个工作表，依次命名为“第二季度考核表”、“第三季度考核表”、“第四季度考核表”和“年度考核表”。（9）单击“第一季度考核表”工作表标签，然后按住【Shift】键的同时单击“第二季度考核表”、“第三季度考核表”、“第四季度考核表”和“年度考核表”工作表标签，将它们成组。然后在“第一季度考核表”工作表中输入数据并设置格式。可看到在其他4个工作表的相同位置也输入了同样的数据。

（10）单击任意工作表标签，取消成组工作表状态，然后分别修改其他工作表中A1单元格的季度期数。（11）在“年度考核表”修改F2单元格数据为“年度总成绩”，再在其后添加2列数据。其中“年度总成绩”列的小数位数为3位。

二、使用公式和函数计算季度考核表数据使用公式和函数计算季度、年度考核表数据。其中有两种方法：第一种是利用函数，第二种是直接引用单元格。（1）计算“第一季度考核表”数据。分别在C3，D3，E3，F3单元格输入公式，计算出编号为“0001”的员工第一季度的出勤量、工作态度、工作能力和季度总成绩表数据，其中“季度总成绩表”列中数据的计算依据是“出勤量的20%、工作态度的30%、工作能力的50%之和”。

（2）向下拖动C3:F3单元格区域右下角的填充柄至F11单元格后释放鼠标，计算出其他员工第一季度考核数据。这是第一种计算方法。

（2）计算“第二季度考核表”。在“第二季度考核表”的C3，D3，E3，F3单元格中输入公式，计算出编号为“0001”的员工第二季度的出勤量、工作态度、工作能力和季度总成绩表数据。（3）向下拖动C3:F3单元格区域右下角的填充柄至F11单元格后释放鼠标，计算出其他员工第二季度考核数据。这是第二种计算方法。最后用第二种方法计算出第三季度和第四季度的考核数据。

三、使用公式和函数计算年度考核表数据（1）计算员工的年度考核数据。分别在“年度考核表”的C3，D3，E3，F3单元格中输入公式，计算出计算编号为“0001”员工的年度考核数据。（2）向下拖动C3:F3单元格右下角的填充柄到单元格F11后释放鼠标，计算其他员工的年度考核数据。

（3）根据年度总成绩进行排名。在G3单元格输入公式，然后向下拖动G3单元格右下角的填充柄至G11单元格后释放鼠标，计算出所有员工的年度总成绩排名。（4）根据排名确定奖金额标准。在F14:G17单元格区域输入排名和奖金标准数据。

（5）在H3单元格输入公式，计算编号为0001的员工的奖金额。然后向下拖动H3单元格右下角的填充柄到H11单元格后释放鼠标，计算出其他员工的奖金额。

任务四计算员工工资表数据制作思路：首先制作考勤表和基本工资表，然后根据基本工资表数据计算出奖金表，根据奖金表和基本工资表数据计算出补贴表数据，根据补贴表数据计算工资发放明细表中的部分数据，再根据公式和函数计算出工资发放明细表中的其他数据，最后制作工资条。

一、输入考勤表、基本工资表数据（1）新建“员工工资”工作簿，分别将“Sheet1”～“Sheet3”工作表重命名为考勤表、基本工资表和奖金表，然后新建3个工作表：补贴表、工资发放明细表和工资条。（2）在“考勤表”和“基本工资表”中输入数据并设置格式。（3）根据“考勤表”中的提示，在F3单元格输入公式“=E3*100”，然后向下拖动F3单元格右下角的填充柄至F11单元格后释放鼠标，计算出考勤扣款。

二、计算奖金表和补贴表数据（1）制作并计算奖金表数据。在“奖金表”制作表格，然后分别在A3，B3，C3，D3单元格输入公式，将“基本工资表”中的数据引用到“奖金表”中。

（2）假设以所属部门确定奖金额，即部门为办公室的奖金额为1100，部门为财务部的为1000，部门为车间的为900，部门为供应部的为800，其他部门的为850。根据奖金标准，在E3单元格输入公式，计算出编号为“0001”的员工的奖金额。（3）向下拖动A3:E3单元格区域右下角的填充柄至E11单元格后释放鼠标，引用基本工资表数据并计算出所有员工的奖金额。

（4）制作并计算补贴表数据。在“补贴表”制作表格，然后分别在A3，B3，C3，D3单元格输入公式，将“奖金表”中的数据引用到“补贴表”中。

（4）计算补贴。假设补贴是根据其所属部门和基本工资进行确定的，即所属部门为办公室的，其补贴为其基本工资的16%，财务部的为其基本工资的15%，车间的为其基本工资的14%，供应部的为其基本工资的13%，其他部门的为11%。根据补贴标准，在E3单元格输入公式，计算编号为“0001”员工的补贴额。（5）向下拖动A3:E3单元格区域右下角的填充柄至E11单元格后释放鼠标，引用奖金表中的数据并计算所有员工的补贴额。

三、计算工资发放明细表数据（1）在制作工资发放明细表。在“工资发放明细表”制作表格。（2）分别在A4，B4，C4，D4，E4，F4，G4单元格输入公式，将补贴表中的数据引用到“工资发放明细表”中，并根据“编号”列数据填充基本工资、奖金和补贴列数据。然后向下拖动A4:G4单元格右下角的填充柄到G12单元格后释放鼠标，引用并计算数据。

（2