office-2016高效办公案例教程(项目六)

Office2016

第二部分Excel高效办公篇项目六高效计算表格数据CONTENTS目录任务一计算水电费开支任务二计算与分析成绩表任务三制作工资表123CONTENTS目录任务四合并计算生产和销售情况表Excel高效办公技巧45学习利用成组工作表功能同时输入并编辑多个工作表中的数据的方法。学习使用公式和函数计算数据的方法。学习对数值数据设置数字格式的方法。学习对多个工作表进行合并计算的方法。学习为单元格添加批注的方法。学习要点任务一

计算水电费开支

陈大叔的租户有10个人，但已经连续两个月没交水电费了。为此，他需要统计一下所有租户这两个月的水费和电费，准备上门收缴。利用Excel制作，借助其中的公式和函数，不但计算速度快，而且准确率也高。

假设你是陈大叔的好朋友，请你根据上述情况，利用Excel2016帮他制作一份水电费开支表格。情景引入wa作品展示水电费开支文件效果

由图可知，该水电费开支工作簿由3个工作表组成。因此，需再新建两个工作表，并将其分别命名为“8月”“9月”和“合计”，然后将“8月”和“9月”工作表成组，再在其中的一个工作表中输入基础数据并格式化，取消成组后分别修改其个一个工作表中的数据，并在“合计”工作表中制作表格，最后在各工作表中输入公式计算工作表数据。制作思路任务实施一、输入工作表基本数据新建“水电费开支”工作簿，“8月”、“9月”和“合计”工作表。在成组工作表中输入数据并设置格式。将“9月”工作表中的数据进行修改。在“合计”工作表中制作表格。在成组工作表中输入数据并设置格式修改后“9月”工作表数据任务实施二、使用公式计算工作表数据计算出8月份所有租户的用电量。计算第一个租户的电费。计算其他租户的电费。计算出8月租户的用水量。计算出电费合计和水费合计。计算8月电费表底和水费表底数据；9月份同理计算。计算第一个租户的电费任务实施二、使用公式计算工作表数据计算“9月”工作表水电费任务实施三、计算水电费合计值计算8-9月电费合计值。计算8-9月水费合计值。计算8-9月电费合计值计算8-9月水费合计值任务二

计算与分析成绩表

期末考试成绩出来了，老师要求各班学习委员对本班成绩进行计算和分析，要求计算出学生的平均分和总分，并按平均分判断其成绩等级，按总分列出每位同学的名次，再对各科成绩的最高分、最低分，以及各分数段的人数进行统计。

假设你是2班的学习委员，请你根据上述要求，对本班的考试成绩进行计算与分析。情景引入作品展示劳务外包合同文档效果wa作品展示计算和分析后的文件效果

首先打开素材文件，重命名工作表后依次计算学生的平均分、总分、等级和名次，接下来对各科分数段所在人数进行统计，最后为单元格添加批注。制作思路任务实施一、计算平均分和总分新建并保存“成绩表”，重命名“Sheet1”工作表为“2班”。计算学生的平均分。设置小数位数。计算学生的总分。利用数组公式求总分提示

数组公式是指可以同时对一组或两组以上的数据进行计算的公式，计算的结果可能是一个，也可能是多个。例如，本案例中学生的总分也可利用数组公式计算，具体操作为：选择要放置计算结果的单元格或单元格区域M2:M30，然后输入数组公式“=(C2:C30+D2:D30+E2:E30+F2:F30+G2:G30+H2:H30+I2:I30+J2:J30+K2:K30)”并按【Ctrl+Shift+Enter】组合键，效果如图6-17所示。

在数组公式中使用的数据称为数组参数，公式中的数组参数必须为单元格区域，如A1:D5。

任务实施二、判断成绩等级设置函数的判断条件及返回值。设置IF函数。判断学生的等级。提示

使用IF函数可对数值和公式进行真假值条件判断，并根据逻辑判断情况返回不同结果，其语法格式为：IF(logical_test,value_if_true,value_if_false)其中：logical_test为判断条件，可用比较运算符（=，>,<,>=,<=,<>）表示。value_if_true”表示当“logical_test”为“true”时返回的值。value_if_false表示当“logical_test”为“false”时返回的值，返回值可以是常量或表达式。Excel2007以上版本中，IF函数最多可嵌套64层。

任务实施三、计算总分排名按总分对学生成绩进行排名。选择“RANK.EQ”函数。设置函数参数。复制公式到其他单元格。选择“RANK.EQ”函数提示

RANK.EQ函数的功能是返回一个数字在数字列表中的排位，其语法格式：RANK.EQ(number,ref,order)其中：number为需要排序的数值。ref为要排序的单元格区域。order指明排序方式。如果为“0”或者缺省，则按降序排名，即数值越大，排名结果数值越小；否则按升序排名，即数值越大，排名结果数值越大。RANK.EQ赋予重复数相同的排位，但重复数的存在将影响后续数值的排位。

任务实施四、分析成绩数据制作成绩统计与分析表格。计算“财政法规与职业道德”最高分。计算“财政法规与职业道德”最低分。计算所有课程的最高分和最低分。选择“COUNTIF”函数。设置函数参数。提示

COUNTIF函数的功能是用来统计单元格区域中满足给定条件的单元格的个数，其语法格式：COUNTIF(range,criteria)其中：Range为需要统计的单元格区域，即（条件区域）。Criteria为判断条件，其形式可以为数字、表达式或文本，即（条件）。

统计所有分数段的人数任务实施五、为单元格添加批注为单元格添加批注。确定单元格后单击“新建批注”按钮。辑批注，可选中添加批注的单元格。确定单元格后单击“新建批注”按钮为单元格添加批注提示

默认情况下，将鼠标指针指向带有批注的单元格时

批注会显示出来。此外，单击“审阅”选项卡“批注”组中的“显示所有批注”按钮，可显示工作表中的所有批注。再次单击该按钮，则隐藏所有批注。

任务三

制作工资表

某学院为了确保8月份教师工资的准时发放，需要人事部将该月的工资数据进行核算，以工资条的形式发放到教师手上，方便教师查看各项工资数据。

假设你是人事部的一员根据8月份教师的已知工资数据，绘制工资表和工资条文件。情景引入wa作品展示工资表效果wa作品展示工资条文件效果

打开素材工作表后先利用条件格式标识“加班天数”列和“请假天数”列数据，接下来依次计算教师的职称津贴、课时费、应发工资、税前总额、扣税和实发工资，然后设置各项工资数据的数字格式，最后制作工资条。制作思路任务实施一、利用条件格式标识数据新建并保存”工资表”。选择“大于”项。选择“大于”项设置条件格式设置条件格式。将设置大于0的数据所在单元格的显示为“浅红填充色深红色文本”。任务实施二、计算职称津贴和课时费计算职称津贴。计算出其他教师的职称津贴。计算课时费。计算出其他教师的课时费。计算第1位教师的课时费任务实施三、计算应发工资和税前总额计算应发工资。计算税前总额。任务实施四、计算扣税和实发工资计算扣税。计算实发工资。个人所得税计算7级标准任务实施五、设置各项工资数据的数字格式将各项工资数据的格式设置为货币。选择“货币”项知识库货币格式和会计专用格式的区别在于币种符号的位置不同。货币的币种符号是与数字连在一起靠右的，而会计专用的币种符号是靠左，数字靠右的，可使一列数值以小数点对齐。任务实施六、计算应发工资和税前总额新建“工资条”工作表，并输入公式。复制公式计算数据，设置单元格样式。提示

在输入图6-45所示的公式时，可借助“公式”选项卡“函数库”组“逻辑”下拉列表中的“IF”函数输入。在输入公式中的A$2和$A:$R时，可先输入A2和A:R，然后按【F4】键即可。图6-45所示的公式中，各函数的作用如下：①ROW函数：表示返回一个引用的行号，其语法格式为：ROW(reference)其中：reference为需要得到其行号的单元格或单元格区域。如果省略reference，则表示对函数ROW所在单元格的引用；如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将reference的行号以垂直数组的形式返回。reference不能引用多个区域。提示

②MOD函数：是一个求余函数，即两个数值表达式作除法运算后的余数，其语法格式为：MOD(number,divisor)其中：number为被除数；divisor为除数。如果divisor为零，函数MOD返回值为number。③COLUMN函数：表示返回一引用的列号，其语法格式为：COLUMN(reference)其中：reference为需要得到其列标的单元格或单元格区域。如果省略reference，则表示对函数COLUMN所在单元格的引用；如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则函数COLUMN将reference中的列标以水平数组的形式返回。reference不能引用多个区域。提示

④INDEX函数：表示返回表或区域中的值，或对值的引用，其语法格式为：INDEX(array,row_num,column_num)其中：array为一个单元格区域或数组常量；row_num表示要从中返回值的数组中的行；column_num表示要从中返回值的数组中的列。⑤INT函数：表示将数值向下或向上取整为最接近的整数，其语法格式为：INT(number)number为需要进行向下或向上舍入取整的实数。图6-45所示公式中，“MOD(ROW(),3)=0,""”表示被3整除的行，该行为空行（对应工资条中的第3行）；“MOD(ROW(),3)=1,'8月工资表'!A$2”表示被3除余1时，该行显示8月工资表中的A2行（对应工资条中的第1行），否则，按“INDEX('8月工资表'!工资条!,INT((ROW()+10)/3),COLUMN())”输出。任务四

合并计算生产和销售情况表

公司已经将3个分厂的生产和销售数据录入到一个工作表中，现在让小悦利用合并计算功能快速将3个分厂的数据进行合计和求平均值，并将计算结果以表格形式放置在原工作表中，方便公司高层领导随时查看。情景引入wa作品展示生产和销售情况表文件效果

打开素材文件后在源工作表的右侧分别制作一个求合计和平均值的工作表格并进行格式化，然后利用合并计算功能进行各产品生产量和销售量的合计及平均值计算。制作思路任务实施一、计算合计数据打开现有表格，并新建新表格。单击“合并计算”按钮。添加所有合并数据合并计算结果选择数据区域。添加所有合并数据。合并计算结果。任务实施二、计算平均数据在“函数”下拉列表中选择“平均值”函数。将合并计算的函数改为“平均值”。合并计算平均值。将合并计算的函数改为“平均值”合并计算平均值效果Excel高效办公技巧

可将一些中重要的计算公式隐藏

为此，可选中要隐藏公式的单元格或单元格区域，然后打开“设置单元格格式”对话框，切换到“保护”选项卡，选中“隐藏”复选框并确定，如下图所示，最后对工作表进行保护（设置保护选项时，要选中“保护工作表及锁定的单元格内容”复选框。后面的项目中有介绍）。一、防止在编辑栏显示公式选中“隐藏”复选框

在要放置结果的单元格中输入“=”单击“开始”选项卡“编辑”组中的“求和”按钮，此时出现公式“=SUM()”，单击要进行计算的单元格，输入逗号后再单击要进行，求和的单元格，重复该操作，最后按【Enter