Excel从入门到表格分析（二）

拆分列数据

为什么要拆分列数据呢？当某一维度存在多个细分列表的时候，我们往往需要对每个细分项进行统计计算操作，这是就需要拆分列数据。假设我们有以下类别数据：

image.png

我们发现，类别数据栏中的细分数据都是有|分隔开来。所以，我们可以使用Excel的分列功能对这一列进行分列：

image.png
image.png

这里选择的分隔符是|。
经过分列之后，每一个细分项会变成单独一栏：

image.png

除了对现有数据进行拆分以外，我们还可以在导入数据的时候进行拆分处理。EXCEL提供了多种格式的导入选择，CSV格式是一种常见的格式。CSV格式是除数据库、EXCEL表格以外最常用的数据格式，它兼顾可读性和便利性，是爬虫最常用的存储方式之一。CSV格式的数据一般是用特定的符号将每一列进行分隔的，常见的符号包括,逗号，\ttab符号，空格等。CSV的导入和现有数据的导入类似，同样利用分列功能，使用特定符号进行分隔即可。

数据排序和筛选

为什么我们需要对数据进行排序呢？当我们拿到数据时，它极有可能是杂乱的，或者是按照数据录入的时间或序号进行排列。如果我们想对数据有一个直观的了解，比如对于工资表单数据，我们想了解最低工资到最高工资的具体走势，对工资进行排序则是首先需要进行的操作。

EXCEL本身提供了排序功能。需要注意的是，如果想要按照某一列的顺序进行排序，应该先将此列的某一单元格选中，然后再使用排序功能，选择排序顺序的列进行排序。

image.png

需要注意的是，有的时候选择的排序顺序列为文本格式，即使将列的属性改为数字也无法使其变成数字格式。文本格式的问题在于2会排在10后面，因为根据文本排序，10的第一位是1，小于2。对于文本列的情况，一个小窍门是将此列的值复制到一个辅助列，然后对此辅助列进行分列，分列的符号可以使用在文本中不出现的任何符号，比如-，分列的结果对应的列的格式就会变成数字格式。之后，我们可以根据新生成的辅助列对数据进行排序。还有一种对于文本列的排序方法是创建一个自定义的排序序列。比如我们想根据职务的大小进行排序：董事长、总经理、副总经理、组长。如何自定义排序序列呢？进入文件-选项，在弹出窗口中，点击左侧的“高级”，拖动到“常规”项目，然后点击“编辑自定义序列”：

image.png
image.png

填写好自定义序列后点击“添加”即可创建对应的序列。
在之后的排序中，可以选择顺序为自定义序列进行排序：

image.png

在上述招聘信息表中，如果我们只想要了解与数据分析相关的岗位，需要对“职位名称”这一列进行筛选。EXCEL提供了强大的筛选功能。点击数据菜单栏中的筛选功能，选中栏的列头会出现三角形状的下拉筛选菜单，随后我们可以根据关键字“数据分析”对职位名称进行筛选：

image.png

如果想要进行精准搜索关键字“数据分析”，可以使用筛选菜单中的文本过滤器（Text Filters），然后选择“等于”（Equals）条件:

image.png

之后，会弹出自定义文本过滤器，输入精准查询“数据分析”，即可得到筛选后的数据：

image.png image.png

逻辑函数

前面几个章节主要涉及对数据预处理的操作，比如文本函数、数学函数、数据的去重分列、排序与筛选。这一章节主要介绍逻辑函数，主要涉及逻辑值与逻辑判断（与AND、或OR、非NOT）。逻辑值主要涉及真、假，可以使用英文（True, False）或者数字（0，1）来表示。逻辑运算中的与（AND）主要涉及数据的交集（1&&1=1， 1&&0=0， 0&&0=0），逻辑或（OR）主要涉及数据的并集（1||1=1， 1||0=1， 0||0=0），逻辑非（NOT）主要针对求反（!0=1， !1=0）。

image.png

上面这张表格列出了常见的逻辑函数的作用及其实现。其中的IF函数是逻辑判断非常重要的函数。IF函数涉及到三个参数，第一个参数是逻辑判断值（可以是根据数据单元格产生的逻辑值），第二个和第三个参数分别对应逻辑判断为TRUE或者FALSE的结果。下图为微软官方文档给出的描述：

image.png

下面我们举一个具体的例子来看如何灵活使用IF函数。

我们有一列为数据收集时间，现在的需求是要将时间换成时间区间：10点前算作早上，10点后12点前算作上午，12点后算作下午。如何通过逻辑函数IF将每一行的时间转换成时间区间呢？

image.png

注意到，这里需要使用嵌套条件：如果时间中的小时部分小于10，打印早上；大于10小于12，打印上午；大于12，打印下午。由于时间列的值均为2位+冒号+2位，所以我们首先需要提取小时部分，使用INT函数和MID函数：INT(MID(H2,1,2))可以获得前两位的数值，然后使用嵌套IF函数：IF(INT(MID(H2,1,2))<10, "早上",IF(INT(MID(H2,1,2))<12, "上午", "下午"))：

image.png

最后打印出：

image.png

条件聚合函数

前面我们介绍了逻辑函数，这一章节我们介绍条件聚合函数。什么时候我们需要使用条件聚合函数呢？假设我们有对应不同工作地点的职位数量表：

image.png

现在我们想要统计一线城市的职位总数（一线城市包括北京、上海、广州、深圳）。一个简单的想法是直接使用SUM函数，将这四个城市对应的职位数量单元格进行累加：

image.png

可以发现，这种方法的弊端在于我们必须定位四个城市对应的职位数量单元格。由于SUM函数对于输入的单元格相对固定，如果我们改变四个城市的职位数量单元格的位置，就会导致最终SUM结果计算错误。条件聚合函数SUMIFS就是用来解决SUM函数对于输入单元格位置固定的问题。其实，我们不需要固定输入单元格的位置，而是通过匹配条件自动判断单元格位置。使用SUMIFS计算一线城市的职位总数，可以使用公式：SUMIFS(C23:C34,B23:B34,{"北京","上海","广州","深圳"})，但我们发现得到的结果只是北京的职位数量，这是因为SUMIFS的实际输出为{125, 93, 48, 66}四个值，对应于四个城市的职位数量，所以我们需要在外面嵌套一个SUM函数从而得到四个城市的职位总数：SUM(SUMIFS(C23:C34,B23:B34,{"北京","上海","广州","深圳"}))

image.png

最后得到结果：

image.png

除了SUMIFS函数，COUNTIF函数也是经常使用的。从字面上可以看出，COUNTIF主要针对给定条件进行计数。假设我们现在需要统计数据分析相关的职位在不同城市的总个数，部分数据如下：

image.png

首先，我们需要统计一共有多少城市。可以先将城市这一列复制，然后使用数据栏中的去重，得到所有城市名称。

image.png

然后，我们可以使用COUNTIF函数，对于每个城市统计职位个数。COUNTIF函数的最简单形式为：COUNTIF(要检查哪些区域? 要查找哪些内容?)，在我们这个例子中，区域为原始表格“城市”这一列，查找内容为去重后的每一个城市名称：

image.png
最后，COUNTIF可以算出每个城市有多少职位数：
image.png

总结： 这一章节我们主要介绍了两个常见的条件聚合函数。条件聚合函数主要是对符合特定条件的数据项进行统计，它可以是求和（SUM）或者计次（COUNT）。我们可以把它看成是数学函数与逻辑函数的结合，通过判断是否符合某一个条件或某几个条件来决定是否计入统计值。