Excel实用主义之Power Pivot

大家日常工作都会用到Excel处理数据，制作表格和图标，进行数据分析。在纷繁冗杂的数据面前，如何使我们的工作更高效？从Excel表格的特点开始，我在这里给先给大家分享一些Excel的日常小技巧，再用实例操作带着大家初识Excel最强大最棒的改进：Power Pivot。

什么是表格？

是不是打开一个Excel的sheet, 输入表头，数据，就是一个表格呢？我们来看一个对比：

图1-Range和Table

左边的是表格吗？看起来是的。对，只是看起来。当你选中左边任意一个数据的时候，请观察一下菜单栏，是否有Design这个选项卡？并没有对吗。左边的部分我们在Excel中对应的名字是Range。

真正的表格(Table)是右边这组。那表格有什么特点呢？

首先，Design选项卡里面，你可以给这个表格定义名称。这一点很重要，定义一个清晰并便于理解的表格名称，有助于我们稍后进行数据透视及引用。

图2-Table的Design选项卡

第二．表格里面不能有合并的单元格，合并的按钮是灰色显示的。

第三，当数据量很大的时候，上下拖动表格，表头会自动固定在第一行。这个时候如果需要再左右拉动的时候，可以只冻结某一列。好了，现在是不是解决了有时候想同时冻结行和列的问题。😊

第四，最重要的，当你进行数据透视创建了Pivot Table的时候，你在表格数据源里面增加/删除数据，透视图刷新后是可以自动增加/删除相应的数据的。这点在用Range做的透视图里面是不行的，需要手动更新数据源范围。

第五，表格可以自动应用公式，并可以应用表格格式。

Tip1：如何快速更改并统一日期格式？

如图，有时候我们收到的数据由于填写人的不规范，会有用带斜杠、点、短划线混合的日期格式，比如2017/6/18, 2017.6.18, 2017-6-18。怎么修改成统一的2017/6/18 这种呢？用日期格式吗？

图3-日期格式

即使选择了日期格式，也会发现没有什么作用。如果数据量小，只有几条，可以一个一个找出来修改吧。可如果有上千条的数据，怎么办？

解决办法：

选中日期数据列，Data-> Text to Columns-> Original data type (选默认的Delimited)-> Delimiters（取消所有勾选）->Column data format （选择Date YMD）-> Finish

图4- 统一日期格式

好了，所有的日期都统一成按斜线/表示的日期格式了。

Tip2: 获取交叉值：

图5-1 获取交叉值

如上图，假设数据源是1-12月产品A-E的销售量。假如老板问5月卖了多少个D商品？

查询的方法很多，比如可以用Vlookup函数。那么当老板想随机查看某个月，某个产品的时候，怎么破？

解决方法：

1.     创建Name Range:

选中数据->Formulas-> Create from selection-> 同时选中 Top Row, Left Column

创建好后，可查看用月份和产品名字创建的Name Range

图5-2 创建Name Range

2.     设置数据验证：

在Data Validation里面分别为Product 和Month的选择范围，为对应的数据单元格

图5-3 数据验证Data Validation的设置

3.     查询Value的时候，用到Sum函数和Indirect函数

Value =SUM(INDIRECT(B10) INDIRECT(B11))

因为此时是需要调用产品或者月份的Name Range，所以不是直接选择B11或者B12的值，需要用Indirect函数。

Indirect(B10) 对应选择的Product的数据， Indirect(B11) 对应选择Month的数据。

SUM函数的特殊用法，两个indirect函数中是一个空格区分，取两个Name Range的交叉值

图5-4 用SUM函数和Indirect函数获取交叉值

好了，回头看看。根据数据验证的下拉列表，选择不同的产品、月份，立即可以获取到对应的数值了。

Tip3: 关联性选择

举例说明关联性选择的应用场景：当我们填表选择了四川以后，在城市一栏，我们只希望在四川的城市里面选择而排除掉其他的不相关城市。这就是一个关联性选择。

设置关联性选择需要复习到上面提到的Name Range, 数据验证，Indirect函数。

图6-1 关联性选择数据源

例子：有A-F 六家公司可以提供不同的语言翻译服务。此时有一个翻译的需求分发到各公司进行询价。我们期望各公司提供他们可以支持的语言的报价并返回。为了避免统计错误，需要限制可选的语言为各自能提供服务的语言。比如，CompanyD 只能选择Japanese，Simplified Chinese。