《办公自动化案例教程（Windows 10+Office 2016）》 项目七 使用Excel计算表格数据

项目七使用Excel计算表格数据主讲教师：

签到扫码下载文旌课堂APP扫码签到（2022.3.2515:00至2022.3.2515:10）签到方式教师通过“文旌课堂APP”生成签到二维码，并设置签到时间，学生通过“文旌课堂APP”扫描“签到二维码”进行签到。。计算服装销售表数据任务一办公技巧目录CONTENTS任务三任务二计算期末考试成绩表数据

计算员工考核表数据

Excel高效办公技巧任务四

计算职工工资表数据项目描述本项目通过使用Excel2016计算服装销售表数据、期末考试成绩表数据、员工考核表数据和职工工资表数据，来学习公式和函数的使用方法。能力目标（1）使用公式对工作表数据进行计算

（2）在公式中正确设置单元格引用

（3）将公式复制或移动到工作表的其

他地方，以便快捷计算工作表其他数

据

（4）正确地使用函数对工作表数

据进行计算和分析

素质目标

（1）提升自身的实践能力、

思维能力和创新能力，做到举一反三、触类旁通（2）坚守爱岗敬业精神，不断提升自身的工作能力和水平计算服装销售表数据销售表任务一情境引入销售表是记录与统计商品销售情况的表格。博雅玛服饰公司各店铺11月上旬的商品销售记录已初步汇总到一览表中，现在领导要求小吴对汇总一览表中的相关数据进行计算，以查看商品的总成本和毛利，从而做好成本核算和毛利管控。作品展示本任务计算数据后的服装销售表效果如图所示。制作思路打开素材工作簿后首先使用公式计算各条销售记录的销售金额、成交金额、总成本和毛利，然后使用函数计算各条销售记录涉及商品的折扣和成本单价，最后计算11月上旬所有销售记录的总成本合计和毛利合计。任务实施—计算服装销售表数据一、使用公式计算销售金额、成交金额、总成本和毛利（1）

打开本书配套素材“项目七”/“任务一”/“服装销售表”工作簿，然后切换到“销售数据统计报表”。（2）计算销售金额。在J3单元格中输入公式“=H3*I3”，按“Enter”键确认，得到计算结果，如图所示。任务实施—计算服装销售表数据（3）向下拖动J3单元格的填充柄，到J53单元格后释放鼠标，计算出所有销售记录的销售金额。（4）

计算成交金额。在L3单元格中输入公式“=J3-K3”，按“Enter”键确认后向下拖动L3单元格的填充柄到L53单元格后释放鼠标，复制公式到该列的其他单元格中。（5）计算总成本。在N3单元格中输入公式“=H3*M3”，按“Enter”键确认后向下拖动N3单元格的填充柄到N53单元格后释放鼠标，复制公式到该列的其他单元格中。（6）计算毛利。在O3单元格中输入公式“=L3-N3”，按“Enter”键确认后向下拖动O3单元格的填充柄到O53单元格后释放鼠标，复制公式到该列的其他单元格中。任务实施—计算服装销售表数据此时未引用成本单价和折扣数据到该工作表中，所以“成交金额（元）”“总成本（元）”“毛利（元）”列数据还不是最终计算结果。小提示！任务实施—计算服装销售表数据二、使用函数计算折扣和成本单价（1）计算折扣。根据A55单元格中的注释（1），在K3单元格中输入公式“=IF(I3>200,(J3*0.1),0)”，按“Enter”键确认计算出第1条销售记录的折扣。因为该商品的单价不足200元，所以折扣为0，如图所示。任务实施—计算服装销售表数据（2）向下拖动K3单元格的填充柄到K53单元格后释放鼠标，计算出所有销售记录的折扣。（3）在M3单元格中输入公式“=VLOOKUP(C3,商品表!$A$2:$F$32,6)”，确认后根据商品编码引用“商品列表”工作表第6列（即“成本单价（元）”列）中的相应数据，如图所示。任务实施—计算服装销售表数据此时未引用成本单价和折扣数据到该工作表中，所以“成交金额（元）”“总成本（元）”“毛利（元）”列数据还不是最终计算结果。小提示！任务实施—计算服装销售表数据VLOOKUP函数的作用是在表格或数值数组的首列查找指定数值，并由此返回表格或数值数组当前行中指定列处的数值。其语法格式：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])。其中，lookup_value为要查找的值，它可以是数值、引用或文本字符串；table_array为要查找的区域，查找值必须位于这个区域的最左列；col_index_num为相对列号（最左列为1，其右边一列为2，依次类推）；range_lookup为一个逻辑值，指明函数VLOOKUP查找时是精确匹配（FALSE），还是近似匹配（TRUE）。小提示！任务实施—计算服装销售表数据（4）

向下拖动M3单元格的填充柄到M53单元格后释放鼠标，复制公式到该列的其他单元格中，结果如图所示。此时可看到“总成本（元）”和“毛利（元）”列数据发生相应变化。任务实施—计算服装销售表数据三、计算总成本合计和毛利合计（1）在N54单元格中单击，然后单击“开始”选项卡“编辑”组中的“自动求和”按钮，如图所示。任务实施—计算服装销售表数据2）此时在N54单元格中会自动出现带函数的公式“=SUM(N3:N53)”，确认函数参数是否正确，如图所示。这里保持默认参数，按“Enter”键确认，计算出所有销售记录的总成本合计。任务实施—计算服装销售表数据（3）向右拖动N54单元格的填充柄到O54单元格，复制公式计算出所有销售记录的毛利合计，如图所示。至此，服装销售表数据计算完毕，再次保存工作簿。

扫码播放“计算服装销售表数据”视频，确认步骤实践操作计算期末考试成绩表数据成绩表任务二情境引入考试成绩是学生阶段性学习效果的直观体现，对成绩数据进行计算，可以更好地了解学生的学习情况。为了更好地掌握法律专业各班学生学习的整体情况，教务处要求小程根据期末考试成绩表数据判断出学生所在的班级，并计算其总分、平均分、年级排名、等级，以及各科目的最高分与最低分，最后突出显示等级为“优”的单元格，便于学院领导掌握该专业学生的宏观情况。作品展示本任务计算后的法律专业期末考试成绩表效果如图所示。制作思路打开素材工作簿后首先根据学号判断学生所在的班级，接下来依次计算学生的总分、平均分、年级排名、等级，以及各科目的最高和最低分，最后突出显示等级为“优”的单元格。任务实施—计算期末考试成绩表数据一、根据学号判断学生所在班级（1）

打开本书配套素材“项目七”/“任务二”/“法律专业期末考试成绩表”工作簿。（2）根据A107单元格中的注释（1），在A3单元格中输入公式“="法律"&TEXT(MID(B3,5,2),"[DBNum1]")&"班"”，按“Enter”键判断出第1名学生所在的班级，如图所示。该公式的含义：截取出B3单元格中的第5～6位字符（从第5位开始截取，长度为2），然后将截取的数字转换为中文小写数字，最后将“法律”、转换的中文小写数字和“班”用连字符“&”连起来组成一个字符串。任务实施—计算期末考试成绩表数据TEXT函数的作用是将数值转换为按指定数字格式表示的文本。其语法格式：TEXT(value,format_text）。其中，value为数值，或计算结果为数值的公式，或对包含数值的单元格引用；format_text为文本形式的数字格式。MID()函数的作用是截取字符串中从指定位置开始的指定个数的字符。其语法格式：MID(text,start_num,num_chars)。其中，text为用于字符截取的字符串；start_num为指定要截取的第一个字符的位置，系统定义其为1，依次类推；num_chars为指定要截取的字符个数。DBNum1的含义是将单元格的数值格式设置为中文小写数字。小提示！任务实施—计算期末考试成绩表数据（3）向下拖动A3单元格的填充柄，至A102单元格后释放鼠标，或双击A3单元格的填充柄，判断出所有学生所在班级，如图所示。任务实施—计算期末考试成绩表数据二、利用公式计算总分和平均分（1）在L3单元格中单击，然后单击“开始”选项卡“编辑”组中的“自动求和”按钮，按“Enter”键计算出第1名学生的总分。双击L3单元格的填充柄，计算出所有学生的总分。（2）在M3单元格中单击，然后单击“开始”选项卡“编辑”组中的“自动求和”下拉按钮，在展开的下拉列表中选择“平均值”选项，如图所示。任务实施—计算期末考试成绩表数据（3）在工作表中重新选择要求平均值的单元格区域D3:K3，然后按“Enter”键计算出第1名学生的平均分。双击M3单元格的填充柄，计算出所有学生的平均分，如图所示。任务实施—计算期末考试成绩表数据三、利用函数计算年级排名和等级（1）在N3单元格中输入公式“=RANK.EQ(L3,L$3:L$102,0)”，确认后双击N3单元格的填充柄，计算出所有学生的年级排名，如图所示。任务实施—计算期末考试成绩表数据RANK.EQ函数的作用是返回一个数字在数字列表中的排位。其语法格式：RANK.EQ(number,ref,order)。其中，number为需要进行排位的数字（单元格）；ref为参与排位的数字列表（单元格区域），其中的非数值型参数将被忽略；order为一个数字，用于指明排位的方式：如果order为0（零）或省略，Excel对数字的排位所基于的ref是按照降序排列的列表；如果order不为0，Excel对数字的排位所基于的ref是按照升序排列的列表。使用RANK.EQ函数进行排名时，对于并列的情况将直接进行增位排名。小提示！任务实施—计算期末考试成绩表数据（2）根据A107单元格中的注释（2），在O3单元格中输入公式“=IF(M3>=90,"优",IF(M3>=80,"良",IF(M3>=60,"中","不及格")))”，确认后双击O3单元格的填充柄，按平均分计算出所有学生所在级别，如图所示。任务实施—计算期末考试成绩表数据IF函数的作用是根据指定条件执行真假值判断，结果为真则返回某个值，结果为假则返回另一个值。其语法格式：IF(logical_test,[value_if_true],[value_if_false])。其中，logical_test为判断条件，即计算结果是true或false的任意值或表达式；value_if_true为真值，即当logical_test条件为true时返回的结果（若省略该参数，则结果默认为true）；value_if_false为假值，即当logical_test条件为false时返回的结果（若省略该参数，则结果默认为false）。小提示！任务实施—计算期末考试成绩表数据四、计算各科目最高分和最低分（1）D104单元格中单击，然后在“开始”选项卡“编辑”组的“自动求和”下拉列表中选择“最大值”选项，修改函数参数为D3:D102单元格区域，按“Enter”键，计算出“法理学”科目的最高分。（2）向右拖动D104单元格的填充柄，至K104单元格后释放鼠标，计算出所有科目的最高分，如图所示。任务实施—计算期末考试成绩表数据（3）在D105单元格中单击，然后在“开始”选项卡“编辑”组的“自动求和”下拉列表中选择“最小值”选项，修改函数参数为D3:D102单元格区域，按“Enter”键，计算出“法理学”科目的最低分。（4）向右拖动D105单元格的填充柄，至K105单元格后释放鼠标，计算出所有科目的最低分，如图所示。任务实施—计算期末考试成绩表数据五、突出显示等级为“优”的单元格（1）选中O3:O102单元格区域，然后单击“开始”选项卡“样式”组中的“条件格式”按钮，在展开的下拉列表中选择“突出显示单元格规则”/“等于”选项，如图所示。任务实施—计算期末考试成绩表数据（2）打开“等于”对话框，在对话框左侧的编辑框中输入“优”，在“设置为”下拉列表中选择“自定义格式”选项，如图所示。任务实施—计算期末考试成绩表数据（3）打开“设置单元格格式”对话框并自动显示“字体”选项卡，在“字形”列表中选择“加粗”选项，在“颜色”下拉列表中选择“深红”；切换到“填充”选项卡，在“背景色”列表中选择一种背景颜色，参数如图所示。任务实施—计算期末考试成绩表数据（4）单击“确定”按钮返回“等于”对话框，再次单击“确定”按钮返回工作表。此时可看到，在选中的单元格区域中，“优”字所在单元格以深红、加粗、橙色底纹突出显示，如图所示。至此，法律专业期末考试成绩表数据计算完毕，再次保存工作簿。

扫码播放“计算期末考试成绩表数据”视频，确认步骤实践操作计算员工考核表数据考核表任务三情境引入员工考核是指公司领导按照一定的标准，采用科学的方法衡量与评定员工完成岗位职务的能力与效果的管理方法。本年度的员工考核基础数据表已初步汇总到公司领导，这些数据表包括员工的出勤量统计表、绩效表、4个季度的考核表及年度考核表。现在需要小胡计算出员工的各季度出勤率、季度考核、年度考核、排名和奖金数据，便于公司领导掌握本年度员工的基本情况。作品展示本任务计算后的员工考核表数据效果如图所示。作品展示作品展示作品展示制作思路打开素材工作簿，首先计算员工各季度的出勤率，然后计算各季度、年度的考核情况，再根据年度总成绩进行排名，最后根据排名计算员工的奖金。任务实施—计算员工考核表数据一、计算各季度的出勤率（1）打开本书配套素材“项目七”/“任务三”/“员工考核表”工作簿，然后切换到“出勤量统计表”工作表。（2）根据A23单元格中的注释，在C3单元格中输入公式“=E3/D3*100%”，确认后向下拖动C3单元格的填充柄，至C22单元格后释放鼠标，计算出第一季度各员工的出勤率，如图所示。任务实施—计算员工考核表数据任务实施—计算员工考核表数据（3）使用同样的方法，分别在F3、I3和L3单元格中输入如图所示的公式，确认后向下拖动F3、I3、L3单元格的填充柄，至F22、I22、L22单元格后释放鼠标，计算出其他3个季度各员工的出勤率。至此，出勤量统计表数据计算完毕，如图（a）所示。任务实施—计算员工考核表数据二、使用公式计算季度考核数据（1）计算员工的季度考核数据。在“第一季度考核表”工作表的C3、D3、E3、F3单元格中输入如图所示的公式，确认后计算出编号为“0001”的员工第一季度的出勤率、工作态度、工作能力和季度总成绩数据。其中，“季度总成绩”列中数据的计算依据是“出勤率的20%、工作态度的30%、工作能力的50%之和”。任务实施—计算员工考核表数据在C3、D3、E3单元格中输入公式时，也可先输入“=”，然后单击要引用的工作表标签，接着单击其中要引用的单元格，以防止引用错误。小提示！任务实施—计算员工考核表数据（3）向下拖动C3:F3单元格区域的填充柄，至F22单元格后释放鼠标，计算出其他员工第一季度的考核数据，效果如图所示。（4）使用同样的方法，计算出“第二季度考核表”“第三季度考核表”“第四季度考核表”中的考核数据，效果如图（c）（d）（e）所示。任务实施—计算员工考核表数据三、使用公式和函数计算年度考核数据（1）计算员工的年度考核数据。在“年度考核表”的C3单元格中输入如图所示的公式，引用4个季度考核表中的出勤率数据，确认后计算出编号为“0001”的员工的年度出勤率考核数据。任务实施—计算员工考核表数据（2）在D3单元格中输入如图所示的公式，引用4个季度考核表中的工作态度数据，确认后计算出编号为“0001”的员工的年度工作态度考核数据。任务实施—计算员工考核表数据（3）在E3和F3单元格中输入如图所示的公式，引用4个季度考核表中的工作能力、年度总成绩数据，确认后计算出编号为“0001”的员工的年度工作能力和年度总成绩考核数据。任务实施—计算员工考核表数据（4）根据年度总成绩排名。在G3单元格中输入如图所示的公式，确认后计算出编号为“0001”的员工的年度总成绩排名。（5）根据排名确定奖金。在H3单元格中输入如图所示的公式，确认后计算出编号为“0001”的员工的奖金。该公式的含义：在J列中查找G3单元格中的数值，然后返回K列中同一行的值。任务实施—计算员工考核表数据LOOKUP函数有向量形式和数组形式两种语法形式。其向量形式的作用是在单行区域或单列区域（向量）中查找数值，然后返回第2个单行区域或单列区域中相同位置的数值。这里为向量形式，其语法格式：LOOKUP(lookup_value,lookup_vector,[result_vector])。其中，lookup_value为该函数在第1个向量中所要查找的数值，其形式可以为数字、文本、逻辑值或包含数值的名称或单元格引用；lookup_vector为只包含一行或一列的区域，其形式可以为文本、数字或逻辑值；result_vector，可选项，为只包含一行或一列的区域，其大小必须与lookup_vector相同。小提示！任务实施—计算员工考核表数据（6）向下拖动C3:H3单元格的填充柄到H22单元格后释放鼠标，计算出所有员工的年度出勤率、工作态度、工作能力、年度总成绩、排名和奖金，效果如图（f）所示。至此，员工考核表制作完毕，再次保存工作簿。

扫码播放“计算员工考核表数据”视频，确认步骤实践操作计算职工工资表数据计算数据任务四情境引入工资表是指按单位或部门编制的用于核算职工工资的表格。小罗在一家公司的财务部上班，领导给了他一张1月份的工资基础数据表，让他根据表格中的注释及表格内容的相互关系计算工资数据，并制作工资条，以便职工清楚本月的工资构成与工资水平。作品展示本任务计算后的职工工资表数据和工资条效果如图所示。作品展示制作思路打开素材工作簿后使用函数计算职称工资、应发工资和个人所得税，然后使用公式计算个人社保、应纳税所得额和实发工资，最后根据工资表制作工资条。任务实施—计算职工工资表数据一、使用函数计算职称工资、应发工资和个人所得税目前，个人所得税由公司代扣，且个税起征点为5000元，税率如表所示。任务实施—计算职工工资表数据（1）打开本书配套素材“项目七”/“任务四”/“职工工资表”工作簿。（2）计算职称工资。根据A22单元格中的注释（1），在H3单元格中输入公式“=IF(E3="高级",2000,IF(E3="副高",1000,IF(E3="中级",800,500)))”，确认后计算出第1名职工的职称工资，如图所示。任务实施—计算职工工资表数据（3）向下拖动H3单元格的填充柄，到H21单元格后释放鼠标，计算出所有职工的职称工资。（4）计算应发工资。单击I3单元格，然后单击“开始”选项卡“编辑”组中的“自动求和”按钮，按“Enter”键确认，计算出第1名职工的应发工资，如图所示。向下拖动I3单元格的填充柄，到I21单元格后释放鼠标，计算出所有职工的应发工资任务实施—计算职工工资表数据（5）

计算个人所得税。根据“个人所得税=应纳税所得额×税率-速算扣除数”等式及表中的规定，在M3单元格中输入公式“=IF(L3<=0,0,IF(L3<=3000,(L3*0.03),IF(L3<=12000,(L3*0.1-210),IF(L3<25000,(L3*0.2-1410)))))”，确认后向下拖动M3单元格的填充柄，到M21单元格后释放鼠标，计算出所有职工的应纳个人所得税（因为还没计算出应纳税所得额，所以该列的计算结果暂时为0）。任务实施—计算职工工资表数据在个人所得税的计算公式中，IF函数的嵌套层数取决于应纳税所得额，即L3单元格中数值的大小。数值越大，嵌套层数越多，反之则越少。这里只使用了税率表的前3级。小提示！任务实施—计算职工工资表数据二、使用公式计算个人社保、应纳税所得额和实发工资（1）计算个人社保。根据A22单元格中的注释（2），在J3单元格中输入公式“=F3*0.105”，确认后可计算出第1名职工要缴纳的个人社保，如图所示。任务实施—计算职工工资表数据（2）向下拖动J3单元格的填充柄，到J21单元格后释放鼠标，计算出所有职工要缴纳的个人社保。（3）计算应纳税所得额。按照“应纳税所得额=应发工资-个人社保-专项附加扣除-5000（起征点）”等式，在L3单元格中输入公式“=I3-J3-K3-5000”，确认后计算出第1名职工的应纳税所得额，如图所示。任务实施—计算职工工资表数据（4）

向下拖动L3单元格的填充柄，到L21单元格后释放鼠标，计算出所有职工的应纳税所得额，此时可看到“个人所得税”列数据自动更新。（5）

计算实发工资。按照“实发工资=应发工资-个人社保-个人所得税”等式，在N3单元格中输入公式“=I3-J3-M3”，确认后计算出第1名职工的实发工资任务实施—计算职工工资表数据（6）向下拖动N3单元格的填充柄，到N21单元格后释放鼠标，计算出所有职工的实发工资，如图所示。任务实施—计算职工工资表数据三、制作工资条在Excel表格中，每个工资条由3行数据记录构成，分别为标题行、数据行和裁剪行（空白行）。下面利用函数法制作工资条。（1）在“职工工资表”工作簿中新建“工资条”工作表。任务实施—计算职工工资表数据（2）在A1单元格中输入公式“=IF(MOD(ROW(),3)=1,1月份工资!$A$2:$N$2,IF(MOD(ROW(),3)=2,INDEX(1月份工资!$A$2:$N$21,(ROW()+4)/3,COLUMN()),""))”，确认后，单元格中会显示“编号”字样，如图所示。该公式的意思：被3除余1的行是工资标题行，被3除余2的行是包括各项工资数据的行，被3整除的行是空白行。也就是每3行构成一个工资条，分别是标题行、数据行和空白行。任务实施—计算职工工资表数据MOD函数的作用是返回两数相除的余数。其语法格式：MOD(number,divisor)。其中，number为被除数；divisor为除数。如果divisor为0，MOD函数将返回错误值“#DIV/0!”。参数必须都为数值类型，即数字、文本格式的数字或逻辑值。如果是文本，则返回错误值“#VALUE!”。结果的正负号与除数相同。小提示！任务实施—计算职工工资表数据

ROW函数的作用是返回引用单元格所在行的行号。其语法格式：ROW(reference)。reference为需要得到其行号的单元格或单元格区域。如果省略reference，则假定是对ROW函数所在单元格的引用。如果reference为一个单元格区域，并且ROW函数作为垂直数组输入，则ROW函数将reference的行号以垂直数组的形式返回。小提示！任务实施—计算职工工资表数据INDEX函数的作用是返回表格或单元格区域中的数值或对数值的引用，它有数组形式和引用形式两种。引用形式通常返回引用（这里为该形式），其语法格式：INDEX(reference,row_num,[column_num],[area_num])。其中，reference为对一个或多个单元格区域的引用；row_num为引用中某行的行号，函数从该行返回一个引用；column_num为引用中某列的列标，函数从该列返回一个引用；area_num为引用中的一个区域。

小提示！任务实施—计算职工工资表数据

使用INDEX函数时需要注意：row_num、column_num和area_num必须指向reference中的单元格，否则INDEX将返回错误值#REF!。如果省略row_num和column_num，将返回由area_num所指定的引用中的区域。小提示！任务实施—计算职工工资表数据COLUMN函数的作用是返回指定单元格引用的列号。其语法格式：COLUMN(reference)。reference为要返回其列号的单元格或单元格区域。如果省略reference，则假定为是对COLUMN函数所在单元格的引用；如果reference为一个单元格区域，并且COLUMN函数是以水平数组输入的，则COLUMN函数将reference中的列标以水平数组的形式返回。小提示！任务实施—计算职工工资表数据（3）向右拖动A1单元格的填充柄，至N1单元格后释放鼠标（拖动填充的长度与原表格长度相同），结果如图所示。任务实施—计算职工工资表数据（4）向下拖动A1:N1单元格区域的填充柄到第57行（共有19名职工，一个工资条占3行，19*3=57行）后释放鼠标，使所有职工的工资条信息都显示出来，如图所示。任务实施—计算职工工资表数据（5）设置A1:N57单元格区域内容的对齐方式为居中，并为工资条内容（不含空白行）添加边框线。（6）设置L:N列的小数位数为2位，然后调整各列的列宽为最合适，完成工资条的制作，效果如图所示。最后保存工作簿。

扫码播放“计算职工工资表数据”视频，确认步骤实践操作Excel高效办公技巧办公技巧任务实施—Excel高效办公技巧一、快速查看工作表中的所有公式要在工作表的单元格中显示计算的公式而不是结果值，可单击工作表中的任意单元格，然后单击“公式”选项卡“公式审核”组中的“显示公式”按钮，如图所示。任务实施—Excel高效办公技巧二、防止在编辑