EXCEL破冰之旅

1     背景

EXCEL用于日常数据分析的工具中，最便利并且最强大的莫属透视表了。因为透视表对原始数据有一定的要求，所以本次的破冰之旅也将把焦点放在如何整理基础数据这个方面。

1.1  初识透视表

如果有人告诉你，下面这两个看起来很高大上的报表，其实只需要在EXCEL里面用鼠标点几下就完成了。你是不是就已经感受到透视表的强大了？

1.2  透视表的数据要求

其实数据透视表对原始数据的要求并不苛刻，只要不是奇形怪状的数据就已经满足要求了。比如刚才的两个透视表，原始数据片段如下图。

简单的说，原始数据在格式上需要满足：

Ø  第一行必须是标题，且不能有空白标题或重复标题；

Ø  单元格格式正确，特别是日期和数字，不能用文本类型替代；

Ø  横平竖直，不能有合并单元格等异形样式；

Ø  一般情况下，所有数据都包含在一个工作表内；且使用简单的一维表格式；

 

那么，如果你的原始数据不满足上面的要求，就需要进行一些转换。下面，我们就介绍一些常用的数据转换方法。

2     格式化时间

使用Excel处理数据的一个基本原则是，“单元格格式正确”。当我们需要分析的数据中包括日期或者时间的时候，这个原则尤其重要，一定不要使用文本或者数字格式来替代。因为Excel内部提供了很多针对日期的函数，可以极大的简化数据分析工作。但是，如果我们用了文本格式来保存日期，那么这些函数将无法使用。

 

理想是丰满的，现实是骨感的。在我们处理数据的过程中，大量的原始数据因为各种各样奇葩的原因，选择了用文本或者数字的方式保存日期数据。常见的几种格式如下图所示：

2.1  数据分列

如果只是需要对日期部分进行格式化，那简单的数据分列功能就可以实现我们的需求。在选中原始数据列的前提下，依次点击数据 – 分列 – 分隔符号 – 下一步，如下图：

 

在文本分列向导第3步，选中日期YMD格式，然后点击完成，如下图：

 

 

 

最终结果已经成功转换为日期格式，如下图：

2.2  公式转换

数据分列的方法，仅能处理日期格式的数据。如果我们需要格式化日期时间的数据，可以使用公式来实现格式化。下面是一个常用的公式，可以把yyyymmddhhmiss格式的原始文本(或科学计数法)转换成yyyy-mm-dd hh:mi:ss日期时间格式，效果如下图：

=TEXT(G2,"0000-00-00 00!:00!:00")+0

 

为了能够正确的显示日期时间格式，还需要把单元格的格式设置成我们需要的日期时间格式，如下图：

3     拆分合并单元格

有时候，数据录入人员为了视觉效果会进行单元格的合并。这种合并过的数据表格，是没办法直接用来生成数据透视表的。

 

如上图，左边的业务员字段进行了合并单元格处理，我们必须把这个字段转换成右边这种格式才方便进行进一步分析。

 

怎么破？难道取消合并单元格后一个一个的拷贝吗？当然不用，只需要简单几个操作，就能实现这个要求。

 

在保证G列（业务员字段）被选中的前提下，依次点击开始 – 取消单元格合并，如下图

 

 

取消单元格合并之后的数据如下图，每个业务员的名字仅在第一行内出现，其他行是空白。

 

在保证G列（业务员字段）被选中的前提下，按快捷键F5，弹出“定位”窗口，点击“定位条件。。。”按钮，在弹出的窗口中，依次点击空值 – 确定，如下图

 

不要点击任何其他地方，直接在公式编辑框里面输入G3单元格的公式 =G2，最关键的步骤，同时按下 Ctrl + Enter，如下图

 

       空白单元格自动复制了对应的数据。如下图，完成数据转换。

 

4     查找单个值（VLOOKUP函数）

有如下图的原始订单信息，以及业务人员的ID/名称对应关系表。

 

希望得到下图的结果，要求是：

1)        根据业务员ID查找对应的名字；

2)        把销售金额进行分段标示；

 

这样的需求，可不能傻乎乎的一个一个人肉查找替换。用一个函数简单搞定。

 

VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为：

      VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

4.1  精确查找

如图，实现对业务员ID和名称的精确查找

公式C2=VLOOKUP(B2,$J$1:$K$4,2,0)

参数说明：

Ø  查找目标VLOOKUP(B2,$J$1:$K$4,2,0)：就是你指定的查找的内容或单元格引用。图中B列的业务员ID就是查找目标。我们要根据“业务员ID”在J列进行查找。

Ø  查找范围VLOOKUP(B2,$J$1:$K$4,2,0)：指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要在J1:K4这个区域中进行查找，并且使用了$J$1这样的绝对引用方式，保证公式在纵向进行复制的时候保持不变。

Ø  返回值的列数VLOOKUP(B2,$J$1:$K$4,2,0)：这是VLOOKUP第3个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“业务员”，它是第二个参数查找范围J1:K4的第2列。这里一定要注意，列数不是在工作表中的列数（不是第K列），而是在查找范围区域的第几列。

Ø  精确OR模糊查找VLOOKUP(B2,$J$1:$K$4,2,0)：，最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。

4.2  模糊查找

模糊查找最主要的一个场景是，对金额这一类的数据进行分段。如下图，要把销售金额分成几个区间，然后对区间内的数据进行汇总。

公式E2=VLOOKUP(D2,$M$1:$N$6,2,1)。

最后一个参数指定为1 或TRUE时表示模糊查找。

重要：在进行模糊查找时，查找范围必须进行升序排列。

 

5     查找多个值（JOIN操作）

如下图，原始数据存放在两个sheet里面。左边的是订单数据，只记录了产品ID；右边的是产品信息，包含了产品的详细信息。

 

 

现在想得到如下图的结果，即两个sheet的数据组合在一起，把产品的详细信息追加在订单信息之后。如果是在数据库里面用SQL语句，这个是很简单的任务，但是如果是在Excel里面又如何实现？

 

5.1  VLOOKUP的扩展

回忆一下VLOOKUP函数的第三个参数是什么？第三个参数是“返回值在查找范围内的列数”。

 

一般情况下，我们用一个固定的数字来指定这个列数。但是在我们这个案例中，如果用固定数字的话，就需要编写多个VLOOKUP函数才能返回多个值。有没有变通的办法，只写一次公式？有，具体思路是用变量代替常量。

如上图，获得产品名称的公式写成这样D2=VLOOKUP($B2,join_2!$A$1:$C$6,COLUMN(B1),0)

 

       上述公式的两个变动，解释如下

Ø  $B2是查找目标（产品ID），因为这个公式要在横向和纵向都进行复制，所以用了$符号指定B列的绝对引用，表示在横向复制的时候B列引用不变；

Ø  COLUMN函数用于返回指定的列数，因为产品名称在查找范围的B列（第2列），所以用B1来指示。当进行横向复制的时候，这个相对引用会自适应变化；

 

把D2的函数公式进行横向和纵向复制之后，结果如下，满足了这个案例的需求。

5.2  Microsoft Query

刚才的办法很好很强大，但是有点麻烦并且不够灵活，有没有更方便的办法？既然这样问，那就肯定是有的啦。一个可能你没有听说过，但是非常强大的EXCEL功能，名字就很高大上。Microsoft Query，主要作用就是在EXCEL里面实现SQL语句的功能。

 

这个功能藏在哪里？请按图索骥，依次点击：数据 –- 自其他来源 – 来自Microsoft Query

 

在弹出的窗口中选择Excel作为数据源，如图依次点击：Excel Files* -- 确定

 

在弹出的窗口中选择包含了原始数据的Excel文件，然后确定

 

在弹出的窗口中，依次把包含原始数据（订单信息，产品信息）的sheet添加到查询中，如下图。

 

把两个sheet都添加完成后，点击关闭按钮，如下图

 

订单信息和产品信息，是通过产品ID来进行关联的，所以我们把这两个字段关联起来。先选择左边的“产品ID”然后拖到右边的“产品ID”上，如下图

 

把我们需要保留的字段拖到下面的窗口，这个例子中，我们选择左边的所有字段，和右边的产品名称和单价字段，完成之后如下图

 

这里可以对join进行调整，EXCEL提供了left join, right join, inner join 三种连接模式。本例中使用的是默认的inner join模式。查询完成之后，可以选择把查询结果返回Excel中，操作如下图：

 

新工作表的内容如下图：

6     多维表转换一维表

数据录入人员很多时候为了视觉效果，会使用二维/多维结构的数据表格。这种格式的数据，肉眼看起来比较清晰，但是不利于进一步数据处理。如下图，业务人员录入的多个运营商提供的不同套餐在不同地区的费率信息表。

 

       上图的多维表必须转换成简单的一维表，才能用于后续的数据处理中。一维表格式如下图：

 

数据量比较大的时候，人肉拷贝耗时耗力，这个时候就要用下面介绍的“多重合并计算数据区域”技巧来实现自动转换了。

 

依次按下组合键 Alt + D + P，打开数据透视表向导。然后选中“多重合并计算数据区域”，如下图

 

接下来选中“自定义页字段”，如下图

 

在向导第三步中，依次进行这些操作：选定区域（选中具体数据部分） – 添加（把选定区域添加到所有区域） -- 设置页字段数目为1 – 给这个区域设置页字段为“移动”，如下图：

 

重复上面的步骤，把其他两部分原始数据都加入，并且分别设置页字段为“联通”和“电信” ，如下图

 

       选择在新工作表中生成一个数据透视表，如下图

 

新生成的数据透视表如下图。如果对透视表的结构进行一下调整，看起来就更像是原始数据的结构了。最关键的步骤，双击红色的总计单元格，如下图：

或者

 

双击总计单元格之后，会在新的工作表中生成一维表的数据，对表头内容和位置进行调整之后，就得到我们需要的最终格式。

 

7     参考资料

《Excel 2010数据透视表应用大全》http://item.jd.com/11154634.html

《谁说菜鸟不会数据分析》http://item.jd.com/11123672.html