《Excel财务数据分析与可视化》

2.4　数据预处理

通过前面4种方法发现问题数据后，都必须对问题数据进行处理。从实际工作中来看，数据规范化处理方法大概可以分为6种：删除、补全、替换、转换、拆分和分类，它们的应用范畴等特点如表2-19所示。

表2-19　数据规范化处理方法

2.4.1　删除

在数据处理中，我们需要删除表格中出错的、无效的、重复的、不需要的或无价值的数据。删除操作可以采用多种方式进行，例如指定删除特定数据，通过筛选条件进行删除，利用函数进行判断删除，或者直接删除重复数据等。

比如在某表格中有多个重复的数据，通过在“数据”选项卡中使用“删除重复值”功能，即可将重复的“客厅灯”数据删除，如图2-20所示。

图2-20

2.4.2　补充

在数据处理中，我们需要补全不完善或缺失的数据。这一过程涉及参照相似或近似的数据，或者利用均值数据、回归判断、经验数据等方法进行数据的填充。通过数据补充，我们可以减小数据分析过程中的误差，使数据集更为完整和准确。

数据补充的方法有多种，其中包括复制粘贴、手动填写数据以及批处理定位缺失数据单元格等。通过这些方法，可以快速、有效地将缺失的数据填补完整。

比如，在某公司考勤表中缺失了一些考勤记录。为了不影响统计，可以往缺失数据的单元格中填入近似的数据，如图2-21所示。

图2-21

提示使用Ctrl+G组合键来快速定位缺失数据的单元格。在数据量较大的表格中，这个方法比人工观察缺失数据更有效率。

2.4.3　替换

在数据处理中，针对现有的不规范、不标准和不统一的数据，我们可以采取一系列处理方式来对其进行规范化。这些处理方式包括手动修改、格式刷、复制粘贴、替换等。例如，可以使用整体替换功能将特定字母或数字批量替换为其他值，也可以将错误数据替换为0或空白，将数据中的空白替换为无数据，或者使用批处理功能来定位并处理特定对象。除此之外，还可以利用格式刷功能快速将一组数据的格式应用到其他数据上，或使用复制粘贴功能将数据从一个位置快速复制到另一个位置。这些处理方式能够提高数据的一致性和可读性，使得数据更易于理解和分析。

比如某表格中，员工姓名一栏本应该只有姓名，但却被添加上了手机号，这样的数据不符合要求。通过替换操作，将所有的手机号去掉，数据就符合要求了，如图2-22所示。

图2-22

提示使用Ctrl+H组合键弹出“替换”对话框，输入“（*）”作为查找内容，替换内容留空，再单击“全部替换”按钮，即可将所有括号以及括号中间的电话号码都删除掉。

2.4.4　转换

在数据处理中，为了让数据标准化并避免统计计算出错，我们需要转换数据的格式。这一步骤包括设置数据格式和自定义格式等操作。同时，我们也可以通过函数实现数字转文本、文本转数字、时间格式转换、大小写转换以及数据转置等功能。通过设置数据格式，我们可以确保数据在展示和计算时保持一致的样式，避免由于格式不统一而引发的错误。

比如，使用函数将一个日期转换为星期几，或转换为该年度第几周，如图2-23所示。

图2-23

2.4.5　拆分

拆分是指对数据源中的数据进行分解，通常用于处理包含多个信息项的复合数据。例如，对产品名称、产品型号、生产日期等文本或时间数据进行拆分，将它们分解为独立的部分，以便更好地进行数据分析和处理。通过拆分复合数据，我们可以获得更细粒度的信息，使得数据更易于理解和分析。

比如，某产品表格中，部分产品有子型号，而我们只需要所有产品的主型号，此时可以通过拆分来获取。拆分时使用的分隔符号根据实际情况而定，使用文本、字符等拆分，或进行固定宽度拆分，拆分为两列或多列都是可以的，如图2-24所示。

图2-24

当然，有拆必有合，有时我们可以对文本或时间数据进行合并，以便获得需要的数据。比如某表格中零件号中存在0开头的现象，为避免在数据处理中出错，可以在零件号前添加一个字母，如图2-25所示。

图2-25

提示添加字母可使用公式，核心运算符是“&”。比如上例中，假设第一个零件号单元格位置为G111，那么我们可以在F111单元格输入公式“="C"&G111”，即可得到添加了字母C的辅助零件号。

2.4.6　分类

在数据处理中，我们需要将杂乱的数据进行识别和分类，以便更好地组织和分析。这个过程包括将具有共同特征或特性的数据进行归类，从而实现更有序的数据管理。手动分类的效率较低，因此我们通常利用函数来加快分类的过程，使用诸如IF、LEFT、RIGHT、MID、YEAR、MONTH、DAY等函数进行自动分类，从而提高数据处理的效率和准确性，例如从日期中提取年份、月份、季度，根据分类库将数据按照省份、区域进行分类，或者按照部门、科室进行分类等。

比如，某厂需要对产品的销量进行分级，使用函数IF即可完成，如图2-26所示。

图2-26

提示在企业中，分类常常用于对价格、产品档次、盈亏、索赔率等数据进行分析。

2.4.7　上机实战：采购价格分析预处理

【案例】某公司的总经理收到了一封举报信，内容称该公司采购部的一名领导可能与某供应商存在某种关系，总经理认为有必要对采购价格进行审查。数据见附赠文档“第2章数据-采购价格分析预处理.xlsx”的“源文件”表单，如表2-20所示。

表2-20　采购价格分析预处理

在进行数据分析之前，需要对数据进行预处理。尽管许多公司已经实施了系统化管理，但数据的录入可能存在缺失的情况，这会对最终的分析结果产生影响。举例来说，我们可能会在数据中遇到错误符号（#N/A），或者某些年份有数据，而其他年份的数据则缺失了，表2-20中很多单元格都存在这样的情况。我们在分析时，首先要对数据进行预处理，使之规范化以后，才能进行分析。

注意，不要在原始数据表单上进行操作，应该先建立一个副本，然后在副本上进行操作，这样即使操作失误，原始数据也不会丢失。

在“源文件”表单上右击，在弹出的菜单中单击“移动或复制”选项，如图2-27所示。

在弹出的对话框中选择“建立副本”复选框，然后单击“确定”按钮，如图2-28所示。

图2-27

图2-28

将新建的表单名称修改为“修改文件”，然后删除不需要的“自制件标识”列（即B列），操作完成后如图2-29所示。

图2-29

接下来要删除所有的错误值“#N/A”。选中F列到J列数据，按Ctrl+G组合键，弹出“定位”对话框，单击“定位条件”按钮，如图2-30所示。

在“定位条件”对话框中选择“常量”单选按钮，并取消对“数字”“文本”“逻辑值”三个复选框的选择，只保持“错误”复选框处于被选中状态，然后单击“确定”按钮，如图2-31所示。

图2-30

图2-31

可以看到，所有错误值都被选中了。为了方便识别，将所有错误值所在的单元格都加上底色，并按Delete键将错误值一次性删除掉，如图2-32所示。

图2-32

提示在本案例中，有的在某一年之后就没有价格了（如第4行和第14行的商品），这是正常现象，通常是因为该商品在那一年之后就不再采购了；同理，有的商品在某一年之前没有价格（如第28行和第29行的商品），也是正常现象，原因是该商品在那一年之前还没有采购。只要数据是连续的，即使前后有所缺失，在本案例中都认为是正常的数据。

接下来需要将不连续的进价补上，方便统计。补齐的方法是在上一年度与下一年度之间取一个估计值，也可以直接使用上一年度或下一年度的值，比如在第21行缺失了2021年的数据，可以使用2022年的数据来补齐。

接下来分析表格中的第L列，即“2023/2019价格变化”列。这一列中还有不少错误值“#N/A”。这一列的错误值不能按照之前的方法进行删除和补齐，因为这一列的值是用2023年的数据减去2019年的数据，然后除以2019年的数据，以计算出其变化率，但由于某些商品缺失2019年或2013年的数据，因此计算出现了错误。所以，我们要修改计算逻辑，把每个商品的第一个和最后一个数据找出来进行计算即可，而不是一定要2019年或2023年的数据。

接下来在M2单元格输入“第一次出现价格”，在M3单元格输入公式“=INDEX(F3:J3,MATCH(0,0/(F3:J3),0))”，然后按Ctrl+Shift+Enter组合键，即可得到该商品第一次出现的价格，然后将该公式复制到该列的其他单元格，即可得到所有商品第一次出现的价格，如图2-33所示。