二、Excel数据分析——数据处理

劉潤澤

在『一、Excel数据分析——数据输入』一文中，我们已经总结了四大数据输入方法，分别为录入、导入、识别、抓取，并且把数据输入后容易出现的问题如分列格式错误等问题也做了解答。那么到这步为止，我们就可以进入数据处理环节了。常规的Excel/Excel数据分析的教程或书籍，会有非常多琐碎的操作，我们依然是从数据分析的角度来说常用的操作及比较好的习惯。
这里分四点来讲：
1、数据清理，这一步是分析必备的，作用是在开始分析之前，把数据源调整到适合分析的状态。内容比较多，建议按照如下顺序操作：

• （按需）二维表转换为一维表、垂直数据改为水平、合并单元格拆分和填充、调整列顺序，调整列宽度
  
• 删除空白符、修正和调整数字格式、转换单位、识别与删除重复行
  

2、查找替换：高级应用——通配符
3、计算字段，主要是根据分析需要，通过Vlookup及其他常用函数，补充新列，辅助分析
4、排序筛选：按字体颜色或按单元格颜色排序和筛选、自定义序列、条件定位

数据清理

数据清理检查表

• 行列是否正确排布，行列是否以正确顺序或逻辑排布
  
• 列数是否符合预期，列名是否唯一，列宽度是否合适，是否有空列或空值较高的列，列公式引用是否正确且同列公式一致
  
• 列格式是否恰当，是否格式一致
  
• 行数是否符合预期，是否有空行，是否有重复
  
• 单元格数据是否包含多余字符
  

二维表转换为一维表

使用Excel的“逆向透视表”功能
字面意思，就是把向透视表那样的二维表逆向转变为一维表。



垂直数据改为水平

有时候从网站上复制数据到Excel中，原本X行Y列的表格到Excel中却变成了一列。还原为最初的二维结构，使用以下方法即可，核心是offset函数。
示例原表格是7行3列，那么在还原时先创建一个3列7行的数字标头，列数字标头即1到Y，行数据标头公式为1,1+Y，直到1+(X-1)*Y，在行1列1的单元格输入=OFFSET($E$2,$G3+H$2-2,0)即可。注意公式当中的相对及绝对引用。



合并单元格拆分和填充

很多时候复制数据粘贴到Excel中，会存在合并单元格的情况，我们知道，有合并单元格的数据表格非常不利于开展数据分析，需要拆分并填充成一个完整行列的数据表格。

• 粘贴时选择“值和数据格式”
  
• Ctrl+G定位空格单元格
  
• 在第一个选中单元格公式中输入按键"="和"↑"，即引用上一个单元格的值
  
• 按Ctrl+Enter输入同公式
  
• 全部复制并粘贴值，去掉公式，以免排序后发生错乱
  

删除空白符

删除多余空格，TRIM可以删除开头和结尾的空格，以及用一个空格替换连续多个空格
删除奇怪字符，CLEAN可从字符串中删除所有无法打印的字符。在导入某些特定类型的数据时，常常会出现这些“垃圾”字符。

char(160)是html中的非断开空格&nbsp
将删除7位ASCII码的前32个非打印字符

替换文本
SUBSTITUTE：替换字符串中的特定文本（第一个）。当知道需要替换的字符但不知道其具体位置时，可使用此函数。
REPLACE：替换字符串中特定位置的文本。当知道所要替换的文本的位置但不知道实际文本时，可使用此函数。
替换文本可以为空，为空时即等同于替换删除。
修正和调整数字格式

主要是常用的数字（含日期）和文本格式的相互转换。
填充文本数字至相同位数
当数字是文本时，会出现“9”要大于“10”的情况。把“9”变成“09”即可。使用文本函数text(A1,"00")


文本日期转数字日期格式
阿里数据常用8位日期作为ODPS表分区，yyyymmdd，转为日期数字格式的方式很简单，以下公式即可。
=--TEXT(A8,”0-00-00″)


使用TEXT函数，将这个字符串变成”0-00-00″的样式，结果为“2023-02-26”。
这个时候，只是有了日期的模样，但是本身还是文本型的，所以再加上两个负号或者乘1，将文本强制转换为数值，这么一折腾，就变成真正的日期序列（需设置日期格式）了。

TEXT函数格式代码中的0有特殊含义，通常表示第一参数本身的数值。

将日期格式转换为星期、月份、季度
提取日期中的数字或者参数，用text函数转换为文本，用dbnum1格式控制阿拉伯数字转中文小写。


dbnum1和dbnum2是中文数字代码，dbnum1表示中文小写，如一、二、三、四，dbnum2表示中文大写，如壹、贰、叁、肆。

减少小数点
将字段格式改成百分比之后，虽然看起来显示没有小数位，但实际存储的时候是包含小数的，比如希望值分布是在0%-100%这101个数字当中，那小数点的存在会使得实际数字个数无限多。当把文字连接百分比时，百分数会显示成小数形式，不利于阅读，可用text函数控制显示的小数位数。


="进度"&TEXT(B34,"0%")
自定义数字格式
注意，几乎所有使用text函数的格式设置，都可以用自定义数字格式来设置，而不需要使用函数。
另外多说一句，一般一个公司都有自己的一套习惯，比如数据以什么单位、显示多少位小数、负数是用括号还是负号显示等，所以我们不需要把所有的自定义数字格式方法都学会，符合自己公司习惯的用熟练就好了。
自定义数字格式甚至还有类似条件格式的功能，符合不同条件的单元格应用不太的格式，但我并不建议使用，因为它有一个缺点就是，不能很直观知道哪些单元格应用了这个自定义数字格式，后文会讲，其实可以通过查找功能找到全部应用了某自定义格式的单元格，并且能够批量替换新的自定义格式，但总归不如条件格式直观，可以通过“管理规则”，查看和修改应用范围，修改规则和格式，条件格式的可视化格式也更为丰富一下（自定义数字格式只有字体颜色可以设置）。
转换单位

单位转换convert，能够转换最常见的测量单位：重量及质量、面积、距离、能量、力、比特和字节、磁、功率、压力、速度、温度、时间、体积、进制、角度弧度转换。
了解即可，一般也不会大量用到。
识别与删除重复行

删除重复行：重复值的判断是根据显示的值确定的，而不是存储的实际值确定。如果两个一样的数字被应用了不同的数字格式，那么去重是不会起到作用的。所以在去重前，应当把同一列整列先设置格式统一。
使用路径：数据-数据工具-删除重复值。比较常用，建议添加到“自定义快速访问工具栏”。
当我们只想知道有哪些重复，并不想直接删除时，可以用countif函数。
识别重复行：countif
=COUNTIF(A:A,A1)


结果值大于1的行，即为存在重复的行。
若数据为多列，那在应用countif时需用&符号或concat函数，把多列数字合并到一个字段。建议增加分隔符，否则会把不-够好和不够-好这种本来不同但合并后都成了不够好而识别为重复的情况。
查找替换

应用函数查找替换——FIND、SEARCH

FIND：可在一个文本字符串中查找子字符串，并返回其起始位置。可以设置要开始查找的起始字符位置。此函数支持区分大小写的比较，但不支持通配符比较。
SEARCH：可在一个文本字符串中查找子字符串，并返回其起始位置。可以设置要开始查找的起始字符位置。此函数支持不区分大小写的文本，并支持通配符。
利用 Excel“查找和替换”功能——Ctrl+F、Ctrl+H

“查找和替换”功能可以在整个工作簿搜索，不局限于指定的单元格。
“查找和替换”功能可以按格式查找（包括自定义数字格式），也可替换为其他格式



“查找和替换”功能支持通配符。
通配符
问号(?)：匹配任意的单个字符。
星号(*)：匹配任意连续的多个字符。
如果要查找实际的问号或星号字符，则可在问号或星号前加上一个波浪号(~)。

条件定位——Ctrl+G





排序筛选

按字体颜色、按单元格颜色筛选和排序

排序除了按字段值的升降序之外，还可以按字体颜色、按单元格颜色。




按照自定义序列排序

此外排序还可以应用自定义序列。


如下图城市按照北上广深顺序排序

• 添加自定义序列
  
• 排序选择自定义序列
  

怎么筛选出A或B？

• 方法1：找到A和B，勾选复选框
  
• 方法2：（适用于选项比较多的情况）筛选A，勾线复选框，筛选B，选择“将当前所选内容添加到筛选器”
  

• 方法3：高级筛选，这个比较重要，详细介绍如下
  

高级筛选

• 设置筛选条件
  
• 应用高级筛选，可以选择在原区域筛选或者复制到其他位置
  

根据筛选目的，有5类基础的筛选条件设置方式，如下图：







计算字段

主要是根据分析需要，通过Vlookup及其他常用函数，补充新列，多用于打标或者分类。
使用&符号或者concat函数连接多列

比如concat("发货城市","-","到货城市")，及新增了城市线路字段。
使用VLOOKUP打标分类

如下图示例中，vlookup函数将年龄分层，这里应用了vlookup函数第4个参数“近似匹配”（缺省即为近似匹配）的特性，vlookup会将被查找值和查找表格（必须升序排列）中的第一列做大小比较，匹配的结果为低于被查找值的最大那一行。即年龄为24时，匹配的结果为低于24的0和18中，最大的那个数即18所在的年龄分层（18-24）。



使用countif函数打标

前面讲过可以用countif函数识别重复行，根据结果是否大于1识别是否重复，同样的这个结果可以用于打标，意味着该值在另外一区域中出现的次数。