2. EXCEL常用技巧（二）|京商圈

EXCEL是绝大多数数据分析师都必须掌握的最初级也是最有用的工具之一，这篇将继续为大家分享在用EXCEL做数据分析过程中的常用技巧，要知道EXCEL用的6，可以极大的提高工作效率。

本文示例均使用的是WPS2019

7.智能填充功能（Ctrl +E）

一般的填充功能相信大多数人都已经掌握，如图所示直接往下拖拽即可，但一般填充功能仅适用于普通规律序列或相同函数公式的拉取

如果想提取如图中单元格的数值串，一般填充则没办法进行提取操作，这时候智能填充就可以派上用场了，如下所示，在第一个单元格输入想要的字符串后，选择第二个单元格后按CTRL+E，自动抓取前一列单元格的所有数字

当然，上例一般的提取也可以用分列操作，具体操作可查看上篇文章，但是如果想从下图没有明显规律的数据中抓取数据的话，一般的分列就很难做到了，但智能填充还是能从其中提取出你想要的数据，如图所示：

比如这样

又或者这样

提取数值和文本都不在话下，这就是所谓的智能填充，是数据处理、提取过程中一个非常好用的功能

除此之外也可以进行一些数据处理，例如把手机号码的部分号码隐藏成“*”，第一个单元格输入想要最终得到的格式，在第二个单元格按CTRL+E，结果如图所示

智能填充的功能可以非常便捷的根据前方连续数据区域和你提供的参照单元格的数据规律智能判定除你想要结果，感兴趣的可以自己去试试，强烈推荐

8.自定义名称（CTRL+F3）

自定义名称在Excel中是为了把一个某些有特殊意义的单元格或数据的区域用一个简单的名称的形式来替代，合理的使用自定义名称，可以更加快速的引用或进行单元格数据区域的计算，让数据分析处理更加高效

例如动态图表就可以用自定义名称简单化数据区域设置，并且用函数公式定义动态的数据区域从而实现图表的动态变化

自定义名称的快捷键是CTRL+F3(excel版本)，WPS可按照下面步骤：
第一步：

第二步：

第三步：

定义之后，只需要在数据使用区域填入对应名称及工作簿名称即可，下面会举例

9.数据验证

数据验证顾名思义，就是在单元格中事先设置好允许输入的内容，当你输入的内容于设置不符时，无法通过数据验证，出现错误提醒

那么为什么要做数据验证呢，在常规数据处理过程中需要做到同一类数据完全一致，例如这样一个数据，手机的型号，同样是苹果，有人会登记成iphone，有人会登记苹果，为了避免同样的数据出现不一致的体现数据验证就能派上用场了

先来说说简单的数据验证，也就是所谓的一级下拉菜单，如图所示
一级下来菜单设置后可以下拉选项，用选择替代输入，避免出现同一品牌不同的数据录入

操作过程只需在菜单栏选择数据—有效性，允许->序列，数据来源区域选择需要验证的数据即可

当输入的数值是iphone而不是苹果时就会出现如下数据错误的提示，提醒你输入非法值，直到你输入数值和设置一致才能录入成功

说完简单的我们再看看看稍微复杂一点的二级下拉菜单，先看效果

只需要选择品牌之后会提供对应的品牌的手机型号进行选择，不是该品牌型号则不做显示

制作二级下拉菜单就需要使用到自定义名称，逻辑是
●第一步，先做出如上图所示的一级下拉菜单

●第二步，按照上面讲过的方法创建自定义名称，打开名称管理器可以看到每个以品牌命名的名称内都包含每个对应品牌的手机型号

●第三步，在需要显示二级下拉菜单位置选择数据->有效性证，选择序列，在数据源位置输入函数，如图所示（INDIRECT含义：返回文本字符串所指定的引用）

●第四步，选择一级下拉菜单的品牌，二级下拉菜单自动显示品牌所对应的手机型号

三级甚至更多级的下拉菜单设置可以采用同样逻辑，只需要定义好名称再相对引用即可

10.数据透视表

数据透视表在EXCEL数据处理中是非常常用的数据分类汇总，筛选汇总的便捷性功能，它的强大之处在于不需要借助任何函数就可以实现按照类别，条件筛选进行绝对值，相对值的分类汇总统计，因为是入门篇，就说些简单的

如何插入数据透视表，选择需要透视的数据区域—选择插入—选择数据透视表即可弹出如图窗口，选择需要数据透视的所有数据（前面已经选择好数据区域，这里就直接默认不用修改了），选择数据透视表的放置位置即可

生成之后可以看到数据中所有包含的标题项都被提出出来作为维度筛选，只需要勾选需要作为分类汇总的指标到行或列的位置即可

如图所示，选择按照类目分类进行访客数以及成交金额的汇总计算

汇总的方式可以按照需求调整，无论是绝对值，相对值，会或者是计算有频次都可以

基础的数据透视到这里就完成了，如果需要筛选固定地区或只显示所选择品类的数据也有好几种办法
第一种，直接使用筛选器，把地区放入筛选器内，这时候就可以单独查看指定地区的商品品类成交情况及金额

第二种，使用切片器（分析->插入切片器），一种可视化的筛选工具，常常和数据透视图一起使用，也可以达到动态的效果，如图所示
注意：
A.切片器只能是用在数据透视表中；
B.格式需要是XLSX格式的，如果你是xls切片器会显示灰色，改好格式后，记得删了之前的数据透视表，然后再新建一个，这样就会看到切片器亮起来了。

以一级类目以及二级品类为筛选维度，查看各个大品类下面小品类商品的销售趋势，如图所示，插入切片器后会在下方出现两个选项框，只要选择其中的某一个一级分类和某一个二级品类，图表随即发生变化，自动生成该一级品类下该三级品类的成交金额汇总数据。