项目十五 员工业务统计表

员工业务统计表Office办公软件实用案例教程项目十五在日常生活、工作中经常会产生大量的数据，对这些数据的登记和统计是一件费时费力的事情，利用Excel2016提供的函数计算功能，可以使繁杂的数据整理工作变成轻松的事情。图15.1.1就是利用Excel软件制作出来的员工业务统计表。项目简介知识点导入1.合并单元格：选取单元格区域，执行【开始】→【对齐方式】→【合并后居中】命令。2.函数的插入：单击【公式】→【函数库】→【插入函数】命令。3.单元格格式：单击【开始】→【单元格】→【格式】→【设置单元格格式】命令。4.调整单元格的行高或列宽：可按住鼠标左键手动拖拽，也可在【开始】→【单元格】→【格式】下拉列表中调整高度或宽度。5.设置条件格式：单击【开始】→【样式】→【条件格式】→【数据条】命令。任务实施样张重点步骤1.录入表格（1）在A1单元格中录入表格标题“员工业务统计表”；（2）在A2、B2、C2、F2、I2、L2单元格中分别录入“工号”、“性别”、“产品1”、“产品2”、“产品3”、“销售利润”；（3）在C3:E3、F3:H3、I3:K3单元格区域中分别录入“成本价”、“销售价”、“销售数量”；（4）在A4:K17单元格区域中录入相应数据，如图15.2所示；

图15.2录入数据重点步骤（1）分别选取A1:L1、A2:A3、B2:B3、C2:E2、F2:H2、I2:K2、L2:L3单元格区域，依次分别执行【开始】→【对齐方式】→【合并后居中】命令；（2）选取A1:L17单元格区域，执行【开始】→【对齐方式】→【垂直居中】和【居中】命令，使数据在单元格内居中对齐。1.核算“销售利润”（1）销售利润=(产品1的销售价-产品1的成本价)*产品1的销售数量+(产品2的销售价-产品2的成本价)*产品2的销售数量+(产品3的销售价-产品3的成本价)*产品3的销售数量，选中L4单元格，在编辑栏录入“=(D4-C4)*E4+(G4-F4)*H4+(J4-I4)*K4”，回车得到计算结果；（2）依次在L5:L17单元格区域中计算各员工销售利润。2.统计数据（1）选取A20：B20、A21:B21、A22:B22、A23:B23单元格区域分别进行【合并后居中】，分别录入“销售利润总值”、“销售利润平均值”、“最高个人销售利润”、“最低个人销售利润”；（2）计算“销售利润总值”：单击选中C20单元格，单击【开始】→【编辑】→【自动求和】按钮，在单元格中出现函数公式，修改计算范围为“L4:L17”，函数公式修改为“=SUM(L4:L17)”，回车得到计算结果；重点步骤（3）计算“销售利润平均值”：单击选中C21单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“平均值”按钮，在单元格中出现函数公式，修改计算范围为“L4:L17”，函数公式修改为“=AVERAGE(L4:L17)”，回车得到计算结果；（4）计算“最高个人销售利润”：单击选中C22单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“最大值”按钮，在单元格中出现函数公式，修改计算范围为“L4:L17”，函数公式修改为“=MAX(L4:L17)”，回车得到计算结果；（5）计算“最低个人销售利润”：单击选中C23单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“最小值”按钮，在单元格中出现函数公式，修改计算范围为“L4:L17”，函数公式修改为“=MIN(L4:L17)”，回车得到计算结果。（6）在E20、F20、G20单元格中分别录入“性别”、“男”、“女”；在E21、E22、E23、E24、E25单元格中分别录入“人数”、“产品1销售数量”、“产品2销售数量”、“产品3销售数量”、“个人销售利润平均值”；

（7）依据性别计算“人数”：单击选中F21单元格，执行【公式】→【函数库】→【插入函数】命令，出现“插入函数”窗口，如图15.3所示。在“搜索函数”项输入函数名称“COUNTIF”，点击“转到”选择函数，点击【确定】，出现“函数参数”窗口，在“Range”项中输入“B4:B17”，在“Criteria”项中输入“男”，如图15.4所示，点击【确定】得到男员工人数；在G21单元格中利用COUNTIF函数计算女员工人数；图15.3“插入函数”窗口重点步骤图15.4COUNTIF“函数参数”窗口重点步骤8）依据性别计算“产品1销售数量”：单击选中F22单元格，执行【公式】→【函数库】→【插入函数】命令，出现“插入函数”窗口。在“搜索函数”项输入函数名称“SUMIF”，点击“转到”选择函数，点击【确定】，出现“函数参数”窗口，在“Range”项中输入“B4:B17”，在“Criteria”项中输入“男”，在“Sum_range”项中输入“E4:E17”，如图15.5所示，点击“确定”得到男员工产品1销售数量；在G22单元格中利用SUMIF函数计算女员工产品1销售数量；

图15.5SUMIF“函数参数”窗口重点步骤（9）依据性别计算“产品2销售数量”：在F23、G23单元格中利用SUMIF函数分别计算男女员工产品2销售数量；（10）依据性别计算“产品3销售数量”：在F24、G24单元格中利用SUMIF函数分别计算男女员工产品3销售数量；（11）依据性别计算“个人销售利润平均值”：单击选中F25单元格，执行【公式】→【函数库】→【插入函数】命令，出现“插入函数”窗口。在“搜索函数”项输入函数名称“AVERAGEIF”，点击“转到”选择函数，点击“确定”，出现“函数参数”窗口，在“Range”项中输入“B4:B17”，在“Criteria”项中输入“男”，在“Average_range”项中输入“L4:L17”，如图15.6所示，点击“确定”得到男员工个人销售利润平均值；在G25单元格中利用AVERAGEIF函数计算女员工个人销售利润平均值；

图15.6AVERAGEIF“函数参数”窗口重点步骤（1）设置A1单元格字体为华文楷体、20磅、加粗；A2:L17单元格区域字体为宋体、12磅；

（2）设置A1单元格行高30磅；C2:K3单元格区域行高为15磅；A4:L17单元格区域行高为15磅、列宽为10磅；

A20:G25单元格区域行高为30磅、数据水平垂直居中；（3）为A1:L17单元格区域添加内部细实线、外部粗实线的边框；为A2:L3单元格区域添加“橄榄色，个性色3，淡色60%”的底纹。（4）选取L4:L17单元格区域，执行【开始】→【样式】→【条件格式】→【数据条】下拉列表中“渐变填充-绿色数据条”命令，如图15.7所示。

图15.7条件格式拓展项目竞赛成绩统计表

图15.8竞赛成绩统计表拓展项目制作行政文档样张重点步骤1.

启动Excel2016，新建空白工作薄。2.将新建的工作薄保存在桌面上，文件名为“竞赛成绩统计表”。3.在A1:G23单元格区域中相应位置录入数据，如图15.9所示。4.设置A2:G23单元格区域的列宽为18磅、行高为15磅，数据水平垂直居中。5.数据统计（1）选中F3单元格，单击【开始】→【编辑】→【自动求和】按钮，将函数公式修改为“=SUM(C3:E3)”，回车得到计算结果，利用同样的方法，计算F4:F11单元格区域中各选手总成绩；（2）选中G3单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“平均值”按钮，将函数公式修改为“=AVERAGE(C3:E3)”，回车得到计算结果，利用同样的方法，计算G4:G11单元格区域中各选手单场平均成绩；重点步骤（3）选中D15单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“最大值”按钮，将函数公式修改为“=MAX(F3:F11)”，回车得到计算结果；（4）选中D16单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“最小值”按钮，将函数公式修改为“=MIN(F3:F11)”，回车得到计算结果；（5）选中D17单元格，单击【开始】→【编辑】→【自动求和】下拉列表中“计数”按钮，将函数公式修改为“=COUNT(B3:B11)”，回车得到计算结果；（6）选中D18单元格，执行【公式】→【函数库】→【插入函数】命令，利用COUNTIF函数“函数参数”窗口，在“Range”项中输入“A3:A11”，在“Criteria”项中输入“K”，如图15.10所示点击，“确定”得到计算结果；在D19单元格中利用COUNTIF函数计算结果；

图15.10COUNTIF“函数参数”窗口重点步骤（7）选中D20单元格，执行【公式】→【函数库】→【插入函数】命令，利用SUMIF函数“函数参数”窗口，在“Range”项中输入“A3:A11”，在“Criteria”项中输入“K”，在“Sum_range”项中输入“F3:F11”，如图15.11所示，点击“确定”得到计算结果；在D21单元格中利用SUMIF函数计算结果；

图15.11SUMIF“函数参数”窗口重点步骤（8）选中D22单元格，执行【公式】→【函数库】→【插入函数】命令，利用AVERAGEIF函数“函数参数”窗口，在“Range”项中输入“A3:A11”，在“Criteria”项中输入“K”，在“Average_range”项中输入“F3:F11”，如图15.12所示，点击“确定”得到计算结果；在D23单元格中利用AVERAGEIF函数计算结果。

图15.12AVERAGEIF“函数参数”窗口6.选取A1:G1单元格区域合并后居中，行高30磅，字体设为黑体、16磅、加粗，添加底纹12.5%灰色；选取A1:G11单元格区域，添加内部细实