Excel+Power BI数据分析电子课件第2章

第2章数据的清洗及美化教师：Office助手Datacleaningandbeautification2.1数据的清洗2.2数据美化2.1数据清洗Datacleaning实例20批量删除多余的行从系统中导出的数据经常出现多余的行，包括多余的标题行和空白行。多余的行影响数据统计和分析，需要将其删除，删除方法如下：1.删除多余的标题行从系统导出的员工登记表如图所示，其中有3个标题行，要求只保留第一个标题行，其余多余的2个标题行删除，操作步骤如下：实例20批量删除多余的行步骤1：在任意标题行上右键单击，弹出的快捷菜单中选择“筛选”|“按所选单元格值筛选”命令，如左图所示，筛选出所有的标题行，如右图所示。实例20批量删除多余的行步骤2：选中第2个和第3个标题行，在选中的标题行上右键单击，弹出的快捷菜单中选择“删除行”命令，如左图所示，即可将多余的标题行删除，效果如右图所示，此时标题行处于筛选状态。实例20批量删除多余的行步骤3：单击B列右侧的按钮，打开的列表框中选中“全选”复选框，如左图所示，单击“确定”按钮，显示全部数据，如右图所示，此时每个字段名的右侧仍有按钮。实例20批量删除多余的行步骤4：打开“数据”选项卡，单击“筛选”按钮，取消字段名右侧的按钮，得到规范数据表，如图所示。实例20批量删除多余的行2.删除多余的空白行如果系统导出的数据包含多余的空白行，如图所示，将数据区域间隔为多个区域，不利于数据的分析。现要求删除多余的空白行，将数据存放在连续的区域中。操作步骤如下：实例20批量删除多余的行步骤1：选中任何一列，例如C列，打开“数据”选项卡，单击“筛选”按钮，如左图所示。此时，该列处于筛选状态。步骤2：单击C1单元格右侧的按钮，打开的下拉列表框中，只选中“空白”复选框，如右图所示，单击“确定”按钮，即可筛选出所有的空白行。实例20批量删除多余的行步骤3：选中所有空白行并右键单击，弹出的快捷菜单中选择“删除行”命令，如图所示，删除所有空白行。实例20批量删除多余的行步骤4：单击C1单元格右侧的按钮，打开的下拉列表框中选中“全选”复选框，如左图所示，单击“确定”按钮，将全部数据进行显示，如右图所示。实例20批量删除多余的行步骤5：按快捷键Ctrl＋Shift＋L或者单击“数据”选项卡中的“筛选”按钮，取消C1右侧的按钮，得到规范的数据表，如图所示。实例21批量删除重复记录如果数据源中有重复的记录，如左图所示，可利用Excel提供的删除重复项功能，将重复的记录删除。操作步骤如下：步骤1：选中A1:A10单元格区域，打开“数据”选项卡，单击“删除重复项”按钮，如右图所示。实例21批量删除重复记录步骤2：弹出的对话框中，若要删除标题行中的重复值，则选中“数据包含标题”复选框，反之，不选，单击“确定”按钮，如左图所示。步骤3：弹出的提示框中显示删除的数据，单击“确定”按钮，完成删除，效果如右图所示。实例22批量转换无法正常统计的数字数据源中有些数字在使用SUM函数进行汇总运算时，结果为0，如图2-21所示，原因是这些数字是文本格式，所以在使用SUM函数汇总运算时结果为0。若要对这些数字进行汇总，首先要将文本格式的数字转换为数值，转换方法有2种。实例22批量转换无法正常统计的数字方法1：利用“转换为数字”命令转换步骤1：选中数字所在的单元格区域，在单元格区域的左侧出现一个黄色的提示符号，单击此符号，打开的下拉列表中选择“转换为数字”命令，如左图所示。步骤2：将文本格式的数字转换为数值后，汇总结果随之发生变化，如右图所示。实例22批量转换无法正常统计的数字方法2：利用选择性粘贴功能步骤1：在任意一个空白单元格上右键单击，弹出的快捷菜单中选择“复制”命令，然后选中数字区域并右键单击，弹出快捷菜单中选择“选择性粘贴”命令，如图所示。实例22批量转换无法正常统计的数字步骤2：弹出的对话框中，依次选中“数值”和“加”单选按钮，如左图所示，单击“确定”按钮，将选中区域的文本格式数字转换为数值，汇总结果随之发生变化，如右图所示。若将大量文本格式数字转换为数值，优先推荐使用方法2进行转换。相比于方法1先行后列逐个转换，方法2高效快捷瞬间完成转换。实例23智能拆分合并单元格并填充数据在实际工作中，如果数据源中含有合并单元格，如图所示，会导致排序、筛选、创建数据透视表等无法进行，给数据处理工作带来一系列麻烦。解决此类问题的方法是先取消单元格的合并，填上相关数据后才能进行后续的数据处理。实例23智能拆分合并单元格并填充数据步骤1：选中合并单元格区域C2:C13，打开“开始”选项卡，单击“合并后居中”按钮，如左图所示，取消单元格的合并。步骤2：按快捷键Ctrl＋G或F5键，弹出“定位”对话框，单击“定位条件”按钮，如右图所示。实例23智能拆分合并单元格并填充数据步骤3：弹出的对话框中，选中“空值”复选框，如左图所示，单击“确定”按钮，效果如右图所示。实例23智能拆分合并单元格并填充数据步骤4：此时，当前活动单元格为C3,在编辑栏中输入公式“=C2”(即活动单元格上方的单元格)，然后按快捷键Ctrl＋Enter批量填充公式，填充效果如图所示，完成拆分合并单元格并填充数据。实例23智能拆分合并单元格并填充数据步骤5：因为批量填充是通过公式完成的，为了避免后续的操作(如排序等)破坏填充结果，需要将公式结果转为实际值。选中C2:C13单元格区域并右键单击，弹出的快捷菜单中选择“复制”命令，然后在选中的区域上再次右键单击，弹出的快捷菜单中选择粘贴选项中的“值”，如左图所示。即可将选中区域中的公式清除，并将公式结果转为实际值，效果如右图所示。实例24批量转换不规则的日期数据在Excel中日期的规范格式是2023/3/15或2023-3-15的形式，而在如图所示中，日期格式不规范，需要将其转换为规范格式，才能进行后续的数据处理，如使用函数进行计算或者创建数据透视表等。下面通过实例介绍将不规范日期批量转换为规范日期的方法。实例24批量转换不规则的日期数据步骤1：选中日期所在的列A列，打开“数据”选项卡，单击“分列”按钮，弹出的对话框中单击“下一步”按钮，如图所示。实例24批量转换不规则的日期数据步骤2：弹出的对话框中单击“下一步”按钮，进入文本分列向导第3步，选中“日期”单选按钮，如左图所示，单击“完成”按钮，效果如右图所示。实例24批量转换不规则的日期数据步骤2：弹出的对话框中单击“下一步”按钮，进入文本分列向导第3步，选中“日期”单选按钮，如左图所示，单击“完成”按钮，效果如右图所示。在本例中，使用了分列功能将不规范日期批量转换为规范日期，操作简单快捷。对于大批量不规范日期的转换，此功能更为便捷。实例25批量隐藏数据工作表中的某些信息如果不希望被他人看到，例如价格、工资、成绩等，可将单元格中的这些信息隐藏起来，隐藏的信息同样可以参与计算，不影响计算结果。如图所示是成绩统计表，现要求将“写作”列的信息隐藏，隐藏方法如下：实例25批量隐藏数据步骤1：选中要隐藏信息的单元格区域D2:D12，在选中的单元格上右键单击，在弹出的快捷菜单中选择“设置单元格格式...”命令，如图所示，弹出“设置单元格格式”对话框。实例25批量隐藏数据步骤2：在“数字”选项卡的“分类”列表框中单击“自定义”选项，“类型”由默认字符“G/通用格式”改为“；；；”，如左图所示，单击“确定”按钮，选中单元格的信息被隐藏，效果如右图所示。实例26快速固定标题行当工作表中的记录几十条甚至是几万条时，窗口不能完全显示，需要向下拖动滚动条才能浏览其他数据，如图所示。在向下拖动滚动条浏览数据时，顶端的标题行随之滚动导致不可见，不利于对数据的理解。为了方便对数据理解，可让标题行自动置顶，使其在上下移动滚动条浏览数据时始终显示在窗口的顶端。设置标题行自动置顶的方法如下：实例26快速固定标题行步骤1：选中数据源中的任意一个单元格，按快捷键Ctrl＋T,弹出的对话框中选择默认设置，单击“确定”按钮，如左图所所示。步骤2：此时，上下移动滚动条浏览数据时标题行自动显示在窗口的顶端始终可见，如右图所示。实例26快速固定标题行步骤3：若要取消标题行自动置顶，打开“表格工具”中的“设计”选项卡，单击“转换为区域”按钮，如图所示，将其转为普通的单元格区域即可。2.2数据美化Databeautification实例27利用快捷键隔行填充颜色隔行填充颜色不仅使表格美观、易读，而且醒目便于区分内容，以免看串行。如在图2-47所示的数据表格中设置隔行填充颜色，操作步骤如下：实例27利用快捷键隔行填充颜色步骤1：单击数据区域中的任意一个单元格，然后按快捷键Ctrl＋T，弹出的对话框中，选择默认设置，单击“确定”按钮，如左图所所示，即可瞬间自动隔行填充颜色，效果如右图所示。实例27利用快捷键隔行填充颜色步骤2：若对填充的颜色或样式不满意，打开“表格工具”中的“设计”选项卡，在“表格样式”组中可以选择其他颜色或样色，如图所示，直到满意为止。实例27利用快捷键隔行填充颜色步骤3：隔行填充颜色后，在每个字段名右侧有一个下拉按钮，这是因为将填充区域转换为了表。若要取消字段名右侧的下拉按钮，打开“表格工具”中的“设计”选项卡，单击“工具”组中的“转换为区域”按钮，如左图所示，弹出的提示框中单击“是”按钮，将表转换为普通的单元格区域，转化后的效果如右图所示。实例2快速输入超长文本步骤1：利用自动更正功能将某一字母(例如b)设置为商品名称“DellIns15CR-4528B15.6英寸笔记本电脑”，设置方法如图所示。(注：输入的字母或数字不能与工作表中的其他内容重复，以免误替换。)实例28自定义隔行填充颜色隔行填充颜色除了使用快捷键一键填充外，也可以自定义颜色进行填充，此填充方式使表格颜色更丰富更具有个性化。下面结合实例讲解自定义隔行填充颜色方法，具体要求如下：①标题行设置为深色背景②其余数据行用浅色隔行填充步骤1：选中标题行所在的单元格区域A1:E1，打开“开始”选项卡，单击“填充颜色”右侧下拉按钮，打开的下拉列表中单击“其他颜色”，弹出的对话框中，拖动滑块或输入RGB值自定义标题行填充颜色，如图所示。实例28自定义隔行填充颜色步骤2：在第3行中选中A3:E3单元格区域，按照步骤1的设置方法，将其设置为如左图所示的填充颜色，效果如右图所示。实例28自定义隔行填充颜色步骤3：将第2行和第3行填充颜色复制给剩余数据行。选中A2:E3单元格区域，打开“开始”选项卡，单击“格式刷”按钮，如左图所示。步骤4：鼠标指针指向A4单元格，按住鼠标左键拖动至E13单元格，如图所示，批量复制填充格式，得到隔行填充效果，如右图所示。实例29设置边框默认情况下，工作表无边框，工作表中的网格线是为了方便输入、编辑而预设的，打印时网格线并不显示。为了使工作表美观和易读，可通过设置工作表的边框改变其视觉效果，使数据的显示更加清晰直观。例如，在图2-59中，将左图表格设置为如右图所示的边框，操作方法如下：实例29设置边框步骤3：选中A2:A13单元格区域，单击“框线”下拉按钮，打开的下拉列表中单击“右框线”，如左图所示，为选中区域添加右框线。若要更改边框线的颜色，打开“框线”下拉列表，从“线条颜色”中选择需要的颜色即可，如右图所示。实例29设置边框方法2：使用笔绘制框线步骤1：选中A2:E13单元格区域，打开“开始”选项卡，单击“框线”下拉按钮，打开的下拉列表中单击“无框线”，删除原有的框线。步骤2：单击“框线”下拉按钮，打开的下拉列表中从“线型”中选择一种实线，如图2-65所示。此时光标变成笔形，在标题行的下方按住鼠标左键进行拖动即可绘制框线。按照相同的方法分别在最后一行的下方、首列的右侧按住鼠标左键拖动绘制框线。步骤3：框线绘制结束后，单击“开始”选项卡中的“框线”按钮，取消笔形即可。实例30绘制斜线表头斜线表头为了说明行、列、数据区域的含义。在Excel工作表中，斜线表头分为2种，一是单斜线表头，二是多斜线表头，如图所示。绘制斜线表头有多种方法，本例主要介绍利用插入直线和文本框的方法绘制多斜线表头，该方法同样适用于单斜线表头的绘制。实例30绘制斜线表头例如，为左图所示的表格绘制多斜线表头，绘制后的效果如右图所示，操作步骤如下：步骤1：设置表头的行高和列宽，将第1行的行高设置为50，A列的宽度设置为23。实例30绘制斜线表头步骤2：选中A1单元格，打开“插入”选项卡，单击“形状”按钮，选择“直线”，如左图所示。步骤3：在A1单元格中按住鼠标左键进行拖动，