|
对于数据分析师而言，数据处理的重要性不言而喻。小易今天为大家重点讲解下如何用Excel做数据处理。 众所周知，拿到的数据通常不是完美无瑕的，而是经常出现缺失值、错误值等，这时摆在数据分析师面前的两座大山，便是“数据清洗”和“数据加工”，翻过这两座大山之后才可以顺畅地进行接下来的具体的数据分析、数据可视化等操作。 导入数据是数据处理的先行军，而Excel中支持导入的数据类型可以分为三类： 文本类数据、网站类数据、数据库类数据。  大家简单浏览一下，小易在这里就不详细介绍啦，本次的重点是数据处理~ 数据处理的方法通常包含数据清洗、数据加工等，本篇文章将为大家介绍一下Excel中的数据处理技巧。 数据清洗通常包括：筛选并剔除多余的重复值；补充缺失值；修正或删除错误值。 数据加工通常包括：对清洗后的数据进行合并、提取、拆分、转换等。 01 数据清洗 数据清洗处理的数据包括：重复值、缺失值、错误值。 处理重复值 1 函数法 函数：COUNTIF(range,criteria)，对区域内满足单个指定条件的单元格进行计数。 range：计数范围； criteria：计数条件，可以指定为数字、文本或表达式。 计数1：在D2单元格输入=COUNTIF(C:C,C2)，计算每个订单编号总共出现的次数。 计数2：在E2单元格输入=COUNTIF(C$2:C2,C2)，计算当前单元格对应的订单编号是第几次出现。   针对“计数2”列，以E6单元格对应的C6单元格中的订单号为例，3表示在C2~C8中该订单号是第三次出现。  此时能够很清晰的通过D列和E列看出重复值的具体情况：重复值共计出现多少次以及对应重复值是第几次出现。 通过函数法完成对重复值的计数之后，接下来就要进行删除步骤了。 （1） 排序删除 针对函数法筛选出来的重复数据，可以对E列数据进行升序排序，选择并删除大于1的数据即可。  （2） 筛选删除 同样是针对函数法筛选出的数据，对E列数据进行筛选，取消显示数值为1的数据后将剩余的数据删除即可。   还可使用“数字筛选”功能，将“大于1”或“不等于1”的数据筛选出来后删除。  点击“大于”或“不等于”后，在弹出的窗口中，输入相应的数值进行筛选删除。   2 高级筛选法 选中准备筛选的数据，使用高级筛选功能并选中“选择不重复的记录”。  点击确定之后显示的就是不带重复值的数据。 高级筛选法的优点是操作简便，可以迅速得到去除重复值后的展现效果。 3 条件格式法 选中准备筛选的数据，点击“开始-条件格式-突出显示单元格规则-重复值”，即可对重复值进行标记。 此处还可以选择重复值被标记的颜色。 条件格式法相对其他方法就没有那么便捷了，它的缺点是只能标记出重复的数据，而当数据量较大的时候不能清晰的看出重复数据和重复次数。 4 数据工具法 使用“数据工具”中的“删除重复项”功能，可以直接删除数据中的重复值。 值得注意的是在点击“删除重复项”后弹出来的窗口中，需要先“取消全选”，然后选择想要删除重复项的列，再点确定。 因为弹出的窗口有时候会默认全选，此时不能直接点击确定，否则会将自己需要保留的数据也一并删除了。 这个方法的优点也是操作简单，十分便捷。 接下来介绍如何处理数据中的缺失值。 处理缺失值 处理缺失值通常有两个方法：定位条件和查找替换。 1 定位条件 定位条件功能的入口： 1）快捷键“CTRL+G”。 2）“开始”-“查找和选择”-“定位条件”。 选择需要处理的数据后，点击“定位条件”。 此时选择“空值”并确定即可标记缺失值。 图中的缺失值对应的内容是“Express Air”，正常输入需要填充的内容后，使用快捷键“CTRL+Enter”填充剩余的缺失值。 2 查找替换 查找替换功能的入口： 1）快捷键：“CTRL+H”。 2）“开始”-“查找和选择”-“替换”。 点击替换之后，输入需要查找的内容以及替换的内容，再点击全部替换即可。由于此次查找的为缺失值，所以查找内容保持空白即可。 小易在这里再补充几点通常用于处理缺失值的思路： 用样本统计量的值替换缺失值，比如样本均值； 用统计模型计算出来的值替换缺失值。比如回归模型、判别模型等； 删除包含缺失值的数据记录； 保留包含缺失值的数据记录，分析时按需排除。 以上就是关于Excel数据分析中处理缺失值的内容啦~接下来介绍常用的处理错误值的方法。 处理错误值 Excel中的常见错误值有8种，分别是：#DIV/0!、#REF!、#VALUE!、#NULL!、#NAME?、####、#NUM!、#N/A、。每一种错误值都有各自的特点，因此对应着不同的处理方法，接下来就介绍一下以上8种错误值都可以通过什么办法解决~ 错误类型1 #DIV/0! 出现#DIV/0!的原因是数据相除运算时，除数出现了0、空格或缺失值的情况。因为除数不能为0，所以当公式运算不符合要求时，会出现#DIV/0!的错误值形式。 如图所示，可以通过“公式-错误检查”功能查看错误原因和计算步骤。图中显示为“被零除”错误，此时修改除数内容即可。 错误类型2 #REF! 出现#REF!的原因是通过公式引用某一单元格或单元格区域时，当引用的区域被删除了，公式无法找到引用的区域，此时会出现#REF!错误值。 如图所示，删除“3月销售额列后”，“3月总销售额”数据显示#REF!错误值，错误检查功能显示原因为移动或删除单元格导致了无效的单元格引用。针对此类问题我们需要重新梳理公式计算逻辑，更新公式的计算区域。 错误类型3 #VALUE! 出现#VALUE!的原因是输入公式时将不同数据类型的值进行计算，比如将“小组1”与“15”相加时出现#VALUE!错误值。 图中为文本类型和数值类型的相加，导致出现了#VALUE!错误值，此时我们需要检查公式中计算区域的值是否为相同类型。 错误类型4 #NULL! 出现#NULL!错误值的原因是在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集。 如图所示，由于错误使用了区间运算符，将“，”替换成了“空格”，出现了#NULL!错误值。此时需要重新检查公式以及公式引用的区间。 错误类型5 #NAME? #NAME?出现的原因可能是公式中的名称拼写错误、公式的语法使用错误、公式引用了未定义的名称中的一种。 如图所示，将函数“SUMPRODUCT”输入成“SUNPRODUCT”时，出现了#NAME?错误值。此时需要重新检查错误值单元格中的公式拼写。 错误类型6 #### 出现####错误值的原因是列不够宽而无法显示单元格的所有内容。 如图所示，当列不够宽无法显示完整日期时会出现####错误值，此时调整列宽即可。 错误类型7 #NUM! 出现#NUM!错误值的原因是公式中包含无效数值。 如图所示，由于公式中“3的6785次方“计算的数值超出了Excel限定的数值计算范围导致出现了#NUM!错误值，此时只能将数据进行拆分或重新整理计算。 错误类型8 #N/A 出现#N/A错误值的原因是公式找不到要求查找的内容。 如图所示，因为表格中不存在公式中的F7-“小组5”的内容，所以显示#N/A错误值。此时需要重新检查查找范围，以及查找内容是否有误。 处理错误值的补充： 小易在这里再补充一个常用于处理错误值的函数： IFERROR(value, value_if_error) value：检查是否存在错误的参数； value_if_error：公式计算结果为错误时要返回的值。 以错误值#N/A为例，当由于公式查询的数据不存在时出现#N/A错误值，可以使用该函数返回设定的备注。 以上就是处理错误值的内容啦~接下来小易带大家进入数据加工的板块。 02 数据加工 数据加工通常包括数据合并、数据拆分和数据转换。下面先从数据合并开始。 数据合并 数据合并通常包括字段合并和字段匹配。 1 字段合并 字段合并常用到函数：CONCAT(text1,[text2],……)和&连接符。 text1（所需的）：要连接的文本项。字符串或字符串数组，如单元格区域。 text2（可选）：要连接的其他文本项。文本项最多可以有253个文本参数，每个文本参数可以是一个字符串或字符串数组，如单元格区域。 &：适用于需要拼接的字段较少的情况。 下图是对两个方法的使用示例。 2 字段匹配 跨表格的字段匹配通常需要使用函数： VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) lookup_value（必需参数）：要查找的值。要查找的值必须列于在参数参数中指定的单元格table_array列中； table_array（必需参数）：VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。单元格区域的第一列必须包含lookup_value。单元格区域还需要包含要查找的返回值； col_index_num（必需参数）：对于包含 (的列，列号table_array) 从 1 开始； [range_lookup]（可选参数）：一个逻辑值，该值指定希望 VLOOKUP 查找近似匹配还是精确匹配。 表格1 表格2 如上图所示，若要根据“订单号”将表格2中的“运送日期”数据匹配到表格1中，可根据下图的公式进行字段匹配。 上述两个方法都是用于数据合并的，接下来介绍数据拆分的方法。 数据拆分 数据拆分通常指字段分列，小易总结了两个方法来实现字段分列。 1 菜单法 功能入口：“数据-数据工具-分列”（如图所示） 确认下一步之后，还需要在弹出的窗口中确认分隔符号，案例中使用的分隔符号是“/”。 此处点击下一步后，可以在弹出的窗口中选择拆分的数据对应的格式，对于案例中的数值可直接默认选择，点击完成。 案例中由于被拆分的数据相邻的列已有内容，所以最后呈现的是将C列和D列数据替换后的效果。 2 函数法 函数法对应的有两个函数： LEFT(text,[num_chars])；RIGHT(text,[num_chars]) text（必需）：包含要提取的字符的文本字符串； num_chars（可选）：指定要由LEFT/RIGHT提取的字符数量。 下图以LEFT函数为例，对C列数据进行拆分。 以上是数据拆分的内容，接下来轮到数据转换啦~ 数据转换 数据转换通常包括行列转换和数据类型转换。 1 行列转换 行列转换比较简单，可直接选择需要转换的数据范围，进行“复制”、“粘贴”，唯一需要注意的是在粘贴时使用“粘贴选项-转置”即可。 2 数据类型转换 数据类型转换涉及到文本转数值、数值转文本、数值日期转日期等。可以通过函数VALUE()、TEXT()来实现。 VALUE(text)：将表示数字的文本数据转为数字。 text：用引号括起来的文本或包含要转换文本的单元格的引用。 TEXT(value, format_text)：数值数据转为文本数据。 value：要转换为文本的数值； format_text：一个文本字符串，定义要应用于所提供值的格式。 下面以TEXT()函数为例，对F列数据进行转换。将F列数字转换为文本类型数据。 下图示例中，我们还可以用VALUE()函数将H列的文本数据转换为数字。
|
|
|
