二、实训内容
（一）从外部获取数据并进行整理
1.从文本文档导入数据
步骤1：在桌面新建“子公司等级考评.xlsx”工作簿文件。
步骤2：单击【数据】选项卡｜【获取外部数据】组｜【自文本】按钮，打开【导入文本文件】对话框。
步骤3：选择“待处理数据.txt”后，单击对话框右下角“导入（M）”按钮，打开【文本导入向导】对话框。直接点击对话框中的【下一步（N）】按钮，进入下一步操作，如下图所示。
步骤4：进入“文本导入向导－第2步”，单击选中“Tab键（T）”和“其他（O）：”复选框，在其右侧的文本框中输入西文下画线“＿”，在“数据预览（P）”内能看到相关数据已被拆分到相邻列中，单击【下一步（N）】按钮。
步骤5：进入“文本导入向导－第3步”，单击“完成（F）”按钮，打开【导入数据】对话框。
步骤6：单击【导入数据】对话框中的【确定】按钮，如下图所示。数据将被导入当前工作表内。
2.删除工作簿连接
步骤1：单击【数据】选项卡｜【连接】组｜“连接”按钮，打开【工作簿连接】对话框。
步骤2：选中“待处理数据”选项，并单击右侧【删除（M）】按钮。在弹出的对话框中单击【确定】按钮，如下图所示。
步骤3：返回【工作簿连接】对话框，单击【关闭（C）】按钮，退出对话框。
3.通过条件格式识别重复项
步骤1：选择B列“子公司”数据。
步骤2：单击【开始】选项卡｜【样式】组｜【条件格式】按钮。
步骤3：从打开的下拉列表中选择“突出显示单元格规则”中的“重复值（D）”选项，打开【重复值】对话框。
步骤4：在对话框中将重复值格式设置为“浅红色填充深红色文本”，如下图所示。
步骤5：单击【确定】按钮，退出【重复值】对话框。此时B列中重复的子公司名称将被标出，如下图所示。
步骤6：保持B列为选中状态，单击【数据】选项卡｜【排序和筛选】组中的【排序】按钮，打开【排序】对话框。
步骤7：打开【排序提醒】对话框，保持“扩展选定区域（E）”为选中状态，单击【排序（S）】按钮，如下图所示。
步骤8：打开【排序】对话框，勾选右上角“数据包含标题（M）”。
步骤9：通过下拉列表依次将“主要关键字”设置为“子公司”，“排序依据”设置为“单元格颜色”，“次序”设置为刚才标出重复值使用的颜色选项且选中“在顶端”选项。
步骤10：单击【添加条件（A）】按钮，增加一行排序条件，依次指定“次要关键字”为“子公司”，“排序依据”为“数值”，“次序”设置为“升序”，如下图所示。
步骤11：单击【确定】按钮，所有被颜色填充的重复值单元格将被排列在最上方。查看重复值，发现“福建分公司”“辽宁分公司”和“浙江分公司”为重复记录，需要删除。
4.删除重复项
步骤1：继续选中B列，在【数据】选项卡｜【数据工具】组中单击【删除重复项】按钮，打开【删除重复项】对话框。
步骤2：在对话框右上角勾选“数据包含标题（M）”选项。
步骤3：保持对话框中的所有列均为选中状态，如下图所示。单击【确定】按钮。
步骤4：继续在弹出的提示对话框中单击【确定】按钮，删除重复项。
（二）使用公式添加带中文字符的“绩效排名”字段
1.通过rank函数生成绩效排名数值
步骤1：在H1单元格中输入字段名称文本“绩效排名”。
步骤2：在H2单元格中输入“＝RANK”。在弹出的列表中用鼠标左键双击“RANK”选项，如下图所示。
步骤3：通过单元格地址引用的方式，向RANK函数内依次输入“F2”和“F2：F13”，两项参数间用西文逗号间隔。
步骤4：选中RANK函数的第二项参数，按【F4】键将其设置为“绝对引用”状态，如下图所示。
2.使用文本连接符＆连接数值和文本
步骤1：在公式编辑栏内通过“＆”符号，将“”第””和“”名””两个文本分别连接在RANK函数的左右两侧，如下图所示。注意中文字符两侧应使用西文双引号。
步骤2：按【Enter】完成公式的输入。使用填充柄将H2单元格内的计算公式复制到H3：H13单元格区域，完成效果如下图所示。
（三）使用嵌套函数添加“考评等级”字段
1.分层次输入嵌套函数公式
步骤1：在I1单元格中输入字段名称文本“考评等级”。
步骤2：确定“五星”的判断逻辑。首先考虑子公司“利润”是否高于各子公司利润的平均值，若满足该条件则直接评定为“五星”。
步骤3：进一步确定“四星”和“三星”的判断条件。若不满足“五星”条件，则通过“子公司的运营成本是否低于各子公司运营成本的平均值”来判断其余情况下的考评等级。满足该条件的可确定为“四星”，不能满足的确定为“三星”。
步骤4：确定函数类型。使用IF函数实现上述逻辑判断，使用AVERAGE函数实现平均值计算。
步骤5：输入第一层逻辑判断公式。在单元格I2中输入“＝IF（F2>AVERAGE（＄F＄2：＄F＄13），"五星"，"其余情况"）”。注意公式中的所有标点符号都应使用西文状态输入，且将AVERAGE函数中的参数设置为“绝对引用”状态。
步骤6：输入完毕后按【Enter】键，将得到返回值“其余情况”，如下图所示。
步骤7：改写公式，实现第二层逻辑判断。选中公式内的“”其余情况””，将其改写为“IF（E2<AVERAGE（＄E＄2：＄E＄13），"四星"，"三星"）”，此时即完成了整个嵌套函数的输入，如下图所示。
2.根据错误提示纠正计算错误
步骤1：按【Enter】键，退出公式编辑状态。此时，会发现I2单元格返回了一个“＃DIV/0！”错误提示代码，如下图所示。
步骤2：根据错误提示确定报错原因。单元格返回值提示“＃DIV/0！”时，表明公式或函数的计算过程中存在“被零除”的情况。检查引用单元格内容，选中E2：E13单元格区域，发现该区域单元格格式为“常规”，且每个货币值前均带有一个文本字符“¥”，如下图所示。
说明因文本字符“¥”的存在，导致此区域数据在导入时被转换成了“文本型数值”。即参与均值计算的对象是“文本”而非“数值”，进而导致了“被零除”情况的发生。
步骤3：巧用替换功能纠正单元格格式错误。要解决这一问题，只需将单元格区域内的文本字符“¥”全部删除即可，使用EXCEL的替换功能可以快捷地实现这一操作。
（1）光标定位在E2单元格，在编辑栏选中“¥”文本，按【Ctrl＋C】组合键复制，如下图所示。
（2）继续选中E2：E13单元格区域，然后按【Ctrl＋F】组合键打开【查找和替换】对话框。
（3）切换到“替换”选项卡，使用【Ctrl＋V】组合键将复制的内容粘贴到“查找内容（N）：”右侧文本框内。
（4）按“全部替换（A）”按钮，将所选单元格区域内的全部“¥”文本对象替换为空值。
步骤4：打开【设置单元格格式】对话框，将E2：E13单元格区域重新设置为“货币，0位小数”格式，单击【确定】按钮。此时将观察到E列和D列内的数值格式有明显的不同，说明E2：E13单元格区域内的数值格式已被纠正，如下图所示。
步骤5：使用相同的方法，纠正D2：D13单元格区域内的数值格式。
步骤6：使用填充柄将I2单元格内的计算公式复制到I3：I13单元格区域，完成效果如下图所示。
（四）使用SUMIF函数计算总盈亏数据
步骤1：在A15和A16单元格依次输入文本“集团总盈利值（万元）：”和“集团总亏损值（万元）：”，并适当调整列宽。
步骤2：确定实现逻辑。集团总盈利值应等于G列为“盈利”状态所对应的F列内的相关数值的总和；集团总亏损值则等于G列为“亏损”状态所对应的F列内的相关数值的总和。要实现此类条件求和计算功能，需使用条件求和函数“SUMIF”。
步骤3：在B15单元格中输入“＝SUMIF（G2：G13，"盈利"，F2：F13）”，按【Enter】键退出编辑状态。
步骤4：继续在B16单元格中输入“＝SUMIF（G2：G13，"亏损"，F2：F13）”，按【Enter】键退出编辑状态。
步骤5：选中B15：B16单元格区域，在【开始】选项卡｜【数字】组中单击右下角对话框启动器，打开【设置单元格格式】对话框。
步骤6：在“数字”选项卡左侧的“分类”列表中，选择“自定义”选项。
步骤7：在“类型”下放的文本框中输入格式代码“0！.0，”，单击【确定】按钮完成设置，完成后的效果如下图所示。
（五）添加标题并输出为PDF文档
1.添加报表标题
步骤1：在行号位置拖动鼠标选中第1行，单击鼠标左键打开快捷菜单，选择其中的“插入（I）”选项，添加一个新行。
步骤2：选择单元格区域，在【开始】/【对齐方式】组中单击“合并后居中”按钮或单击该按钮右侧的下拉按钮，在打开的下拉列表中选择“合并后居中”选项。
步骤3：在合并后的单元格中输入标题文本“集团各子公司绩效考评报表”，并将字体格式设置为“黑体，18号”，将对齐方式设置为“居中”及“垂直居中”。
2.美化表格
步骤1：选中A1：I14单元格区域，将对齐方式设置为“居中”及“垂直居中”。
步骤2：继续保持当前选中状态，在【开始】选项卡｜【样式】组中单击“套用表格格式”按钮
，在打开的下拉列表中选择“表样式浅色1”选项。
步骤3：在打开的【套用表格式】对话框中单击【确定】按钮，然后在弹出的提示对话框中单击【是】按钮，如下图所示。
步骤4：单击【开始】选项卡｜【对齐方式】组右下角的对话框启动器，打开【设置单元格格式】对话框。
步骤5：在对话框中【对齐】选项卡｜【文本对齐方式】内，依次将“水平对齐（H）”和“垂直对齐（V）”均设置为“居中”，然后单击【确定】按钮，完成效果如下图所示。
3.将表格输出为PDF文档
步骤1：选择【页面布局】选项卡/【页面设置】组，在“纸张方向”下拉列表框中选择“横向”选项。
步骤2：在【文件】选项卡中单击【另存为】按钮，打开【另存为】对话框，将保存类型设置为“PDF（∗.pdf）”。
步骤3：单击【保存】按钮，将在当前保存路径下得到一份PDF文件格式的报表，打开效果如下图所示。
