前言

IBM大中华区总经理胡世忠曾说：数据构成了智慧地球的三大元素——智能化、互联化和物联化，而这三大元素又改变了数据来源、传送方式和利用方式，带来“大数据”这场信息社会的变革。

从上可见，时代的变革是源于对数据的利用，对企业而言，数据也同样是其发展、转型的命脉。在工作中，我的前辈不止一次地强调，数据是公司的资产，而且举足轻重。我们对待数据，一定要严谨，经得起考验，对自己的数据负责，这是一个数据人的基本要求。

数据资源

大数据时代，数据虽然很多，但是也不是随意得来的，需要利用各种渠道和方式获得。不管从哪个角度来说，数据可分为内部数据和外部数据。内部数据是企业在日积月累的经营中得来的，我们应该对这些数据挖掘、收集有价值的东西，形成企业的数据资产。内部数据重在后期的处理和分析上。

下面先说外部数据的获取方式，以及通过Excel操作来获取外部数据。

外部数据获取方式

1、专业网站看数据（某一个行业、某一件产品）2、通过收费渠道买数据（第三方数据平台等）3、通过特殊形式引数据（网站爬虫，统计网站等）4、自身积累数据（时间久、跨度长）

Excel获取外部数据

作为一个数据分析师以及想更进一步成长为数据科学家，熟练操作基本的办公软件以及SQL查询是很重要的。请看下面通过Excel获取外部数据的步骤。

第1步：打开“新建web查询”框。新建Excel工作簿，在打开的工作表中单击“数据”选项卡，然后在“获取外部数据”组中单击“自网站”按钮，如下图。

第2步：输入网址并选择要导入的表格数据。在弹出的“新建web查询”对话框中的“地址”文本框中复制粘贴上述网页的网址，然后单击“转到”，找到网站中的表格数据后单击表格左上角的箭头→，图标变成选中状态的复选框√。如下图。最后单击下方的“导入”按钮。

第3步：选择数据的放置区域。点击导入后，Excel会出现“导入数据”对话框，如下图，选中你想放置的单元格，单击“确定”开始导入。

第4步：美化导入的数据。由于导入的数据多且乱，要调整格式使数据规范，并启用冻结窗格功能方便浏览。如下图。

好了，上面就是通过Excel操作来获取网站上的外部数据，很简单吧，但网站中的数据并非都是以表格的形式呈现，现在大部分是以json格式呈现，Excel不是万能的，而且现在很多网站需要付费才能导数据（上面说过数据就是企业的资产）。

小结

希望通过上面的操作能帮助大家。如果你有什么好的意见，建议，或者有不同的看法，我都希望你留言和我们进行交流、讨论。

End.

1、一列数据同时除以10000

复制10000所在单元格，选取数据区域 - 选择粘性粘贴 - 除

2、同时冻结第1行和第1列

选取第一列和第一行交汇处的墙角位置B2，窗口 - 冻结窗格

3、快速把公式转换为值

选取公式区域 - 按右键向右拖一下再拖回来 - 选取只保留数值。

4、显示指定区域所有公式

查找 = 替换为“ =”（空格+=号） ，即可显示工作表中所有公式

5、同时编辑所有工作表

全选工作表，直接编辑，会更新到所有工作表。

6、删除重复值

选取数据区域 - 数据 - 删除重复值

7、显示重复值

选取数据区域 - 开始 - 条件格式 - 显示规则 - 重复值

8、把文本型数字转换成数值型

选取文本数字区域，打开左上角单元格的绿三角，选取 转换为数值

9、隐藏单元格内容

选取要隐藏的区域 - 设置单元格格式 - 数字 - 自定义 - 输入三个分号;;;

10、给excel文件添加密码

文件 - 信息 - 保护工作簿 - 用密码进行加密

11、给单元格区域添加密码

审阅 - 允许用户编辑区域 - 添加区域和设置密码

12、把多个单元格内容粘贴一个单元格

复制区域 - 打开剪贴板 - 选取某个单元格 - 在编辑栏中点击剪贴板中复制的内容

13、同时查看一个excel文件的两个工作表

视图 - 新建窗口 - 全部重排

14、输入分数

先后输入 0 ，再输入 空格， 再输入分数即可

15、强制换行

在文字后按alt+回车键即可换到下一行

16、删除空行

选取A列 - Ctrl+g打开定位窗口 - 定位条件：空值 - 整行删除

17、隔行插入空行

在数据表旁拖动复制1~N，然后再复制序号到下面，然后按序号列排序即可。

18、快速查找工作表

在进度条右键菜单中选取要找的工作表即可。

19、快速筛选

右键菜单中 - 筛选 - 按所选单元格值进行筛选

20、让PPT的图表随excel同步更新

复制excel中的图表 - 在PPT界面中 - 选择性粘贴 - 粘贴链接

21、隐藏公式

选取公式所在区域 - 设置单元格格式 - 保护：选取隐藏 - 保护工作表

22、行高按厘米设置

点右下角“页面布局”按钮，行高单位即可厘米

23、复制时保护行高列宽不变

整行选取复制，粘贴后选取“保持列宽。

24、输入以0开始的数字或超过15位的长数字

先输入单引号，然后再输入数字。或先设置格式为文本再输入。

25、全部显示超过11的长数字

选数区域 - 设置单元格格式 - 自定义 - 输入0

26、快速调整列宽

选取多列，双击边线即可自动调整适合的列宽

27、图表快速添加新系列

复制 - 粘贴，即可给图表添加新的系列

28、设置大于72磅的字体

excel里的最大字并不是72磅，而是409磅。你只需要输入数字即可。

29、设置标题行打印

页面设置 - 工作表 - 顶端标题行

30、不打印错误值

页面设置 - 工作表 - 错误值打印为：空

31、隐藏0值

文件 - 选项 - 高级 - 去掉“显在具有零值的单元格中显示零”

32、设置新建文件的字体和字号

文件 - 选项 - 常规 - 新建工作簿时....

33、快速查看函数帮助

在公式中点击下面显示的函数名称，即可打开该函数的帮助页面。

34、加快excel文件打开速度

如果文件公式过多，在关闭时设置为手动，打开时会更快。

35、按行排序

在排序界面，点击选项，选中按行排序

36、设置可以打印的背景图片

在页眉中插入图片即要

来源：网络整理

百家号-【袁帅数据分析运营】运营者：袁帅，会展业信息化、数字化领域专家。新社汇平台联合创始人，永洪数据科学研究院MVP。认证数据分析师、网络营销师、SEM搜索引擎营销师、SEO工程师、中国电子商务职业经理人。畅销书《互联网销售宝典》联合出品人。

800万行数据，6秒完成统计，很多读者看完文章后非常惊讶，同时也留下了疑问：Excel最多只能容纳100万行，这数据究竟如何导入到Excel？

没错，100万行，就是Excel的极限。

一个表格可以容纳100万行，假设有N个100万的表格，加起来是不是有几百万行呢？

你别忘了Excel还有一个兄弟叫Access，这个可以轻松容纳几百万行数据。

除了Access外，还有各种数据库，别说几百万行，几千万行都可以容纳。

而Power Query除了可以从Excel工作簿导入数据，还可以轻松从Access还有各种数据库中导入数据。

目光放长远一点，学Excel不要只局限于Excel本身，适当结合数据库，Excel可以走得更远。

这里再介绍从Access将数据导入到Excel数据模型中。

Step 01单击数据→新建查询→从数据库→从Access数据库。

Step 02找到相应的Access，单击导入。

Step 03这里可以选择Access中的多个表，也可以选择一个表。现在选择Sheet1进行演示，单击编辑。

这样就将数据导入到Power Query编辑器中。

Step 04现在我们不需要对数据进行编辑，直接选择关闭并上载→关闭并上载至。

Step 05现在拿起笔来画重点，Excel只能存放100万行数据，所以这一步必须选择仅创建连接，勾选将此数据添加到数据模型，单击加载。

为什么加载后速度这么快呢？

其实加载以后，数据相当于存放在数据模型中，本身占用的Excel内存其实很小。800万行数据，也仅仅只是150KB，正因为如此，运行速度才如此之快。

看懂了没？

加载以后，剩下的就跟昨天文章一样，我复制过来。

如果你的菜单栏没有Power Pivot这个功能，可以单击开发工具→COM 加载项，勾选Microsoft Power Pivot for Excel，确定。

单击Power Pivot→管理数据模型。

6秒实现统计。

Excel就像一把天山寒铁淬炼而成的杀猪刀，本身已经很厉害，但具体有多厉害取决于用它的人。

Excel最牛逼的地方在于它不是小李飞刀也不是轩辕剑——需要练个10年8年才能用，它只是一把菜刀，老百姓可以用来切菜，高手可以用来刮胡子，绝世高手拿着直接从南天门一直砍刀蓬莱东路。

表格是什么？表格就是数据容器，对于非IT人士来说，这辈子可能都不会用数据库，但是！Excel让每个人都可以管理数据库了！其提供的基本功能足以完成大部分数据管理统计工作。

打个比方，同事拿到全国资料开始挨个数每个省有多少个客户数了半个小时，而你只是点了两下鼠标就完成了工作。没错，在同事眼里你就是那个百年难得一见的练武奇才（以后就可以承担更多工作了，可喜可贺）！

要你命2000：数据处理（函数）

别人向你扔屎，你可以躲。客户扔给你屎，你只能接住！比如这种屎：

这种乱七八糟表格是没有任何数据意义的，如果只有三坨，动手处理一下就好了，如果有100坨，怎么办？不要紧张，我们只需要处理一行，其余99行交给excel即可。。。

首先，数据-分列：

然后，直接查找替换，将没用的天字去掉：

最后这个毛比较难以处理，动用函数：先找出具体数字，如果里面有“毛”字，直接将结果乘以0.1，一个规范的表格就诞生了：

（如无特殊需要，最后带汉字的D列可以用E列覆盖掉）

Excel也可以为自己所用，再来举一个例子：

时间管理。与一般时间管理App不同，因为我们可以自己设计研发，做出最适合自己的版本！Go！

首先写两行：

拖动一下右下角的小圆点：

同理增加横轴：

填写数据，编写公式。这样计算当天任务量：

=COUNTA(B2:P2)

这样计算完成量

这样计算完成度

=Q3/Q2

最后完成度那里设置单元格格式-数字-百分比。最后填写数据。最终效果图（点击看大图）：

只要三个函数，每天的工作生活一览无遗！

P.s还可以拓展一些功能，比如当天完成度到达xx就有奖励/惩罚之类

总结：掌握了函数，只要是和数据相关的工作，就可以考虑用Excel来处理。

要你命3000：控制一切

著名篮球员赤木刚宪曾经说过，掌握代码就等于掌握了整个Excel，此言非虚。Excel自带编程功能，只有想不到，没有做不到！接下来就用解决吃饭问题做一个简单例子展示一下：

对于有选择困难症的人，让上天来决定吃啥是最好了，我们先填一点数据，如图：

然后选择开发工具-VisualBasic（为了做例子专门下了一个office365，我也是拼了= =），然后什么都不管，直接粘贴代码：

Dim a As Integer '定义公共变量Sub随机Dim x As IntegerDim y As Integera = 0Randomize '初始化reselect:x = Rnd * (3 - 1) + 1 '生成2至7的随机数，代表列数y = Rnd * (4 - 1) + 1 '生成2至6的随机数，代表行数Range("a1:d3").Interior.ColorIndex = xlNone '去掉填充色Cells(x, y).Interior.ColorIndex = 3 '填充为红色a = a + 1If a = 300 Then Exit SubGoTo reselectEnd Sub

然后保存回到excel，选择开发工具-插入-表单-按钮，画一个按钮在excel上，命名为“吃啥好”

在按钮上点右键，指定宏，选择我们刚才做的函数，然后点确定：

Excel制作的半即时战斗模拟（原文地址Excel潜能系列——Excel游戏（2v2战斗~5v5战斗模拟器）【更新V1.5】 Einsphoton_Einsphoton_新浪博客）

超牛的EXCEL版《超级玛丽》总结：掌握了代码，理论上可以用它来做任何小型项目！

用来做动画：

[Excel]Bad Apple!!

推荐书籍：

你早该这么玩Excel（数据管理、工作用）

Excel2013高级VBA编程宝典（装逼、开发用）

另外光有技巧是不够的，表格美化也很重要。（做人也一样，牛逼不够，还得帅。。。）

当数据区域比较大，数据太多的时候，按下鼠标拖动比较慢，浪费时间，也不方便，不灵活，我们可以选择起始单元格区域，按下shift键，鼠标指向单元格边框，等鼠标变成四向箭头，双击鼠标，可以选该方向的所有区域。

1、一般我们都是拖动鼠标在选择数据区域，这样数据量大了比较费事。

2、先选中区域，在按下shift键，双击单元格边框，会自动选择对应的方向区域。

3、选择行，选择一个单元格或多个单元格，双击鼠标对应的行方向。

数据验证这个功能，2013版之前的叫法是数据有效性。借助数据验证，我们可以让表格的数据信息输入更加的准确、规范和快速，大大提高我们的工作效率，妈妈再也不用担心我被扣钱啦

今天大家分享一下数据验证的6种经典应用。

1、 利用数据验证为单元格的数据输入设置条件限制

在表格内输入数据时，我们可以利用数据验证来规范数据的类型，甚至限制输入数值的大小范围。比如我们输入员工月薪资标准时，可以设置验证条件为“整数”，且这个整数需不小于3500；在设置员工身份证号码时，可以设置验证条件中的“文本长度”等于18。具体操作如下动图1和动图2：

此外，我们还可以利用数据验证设置日期或时间范围，具体操作如下：

2、 利用数据验证制作下拉菜单

在表格内输入数据信息时，我们可以利用数据验证制作下拉菜单，来保证信息输入的准确性和高效性。比如我们在下面表格内输入员工性别时，利用数据验证设置一级下拉菜单。具体操作如下 ：

3、 利用数据验证快速输入数据录入的日期和时间

在输入数据信息时，我们还可以结合公式：=NOW()进行数据验证设置 ，直接输入当前日期和时间。具体操作如下：

4、 利用数据验证防止重复录入相同的内容

结合公式=COUNTIF(区域,单元格)=1，数据验证还可以用来防止相同内容的重复输入。具体操作如下：

5、 利用数据验证给设置信息输入时的提醒功能，也可以设置信息输入错误时的错误警告

数据验证功能，不仅能限制、规范数据信息的录入，还可以在录入信息时进行提醒，相当于注释的作用。

当你信息录入错误时，甚至可以设置警告，提醒你重新校正输入。具体操作如下：

6、 利用数据验证制作二级下拉菜单

在前面第2点中我们已经简单讲了利用数据验证制作一级下拉菜单，这里我们再补充一下，如何利用数据验证制作二级下拉菜单。

首先我们要定义名称，选中要设置名称的区域，在【公式】选项卡下点击“根据所选内容创建名称”，勾选“首行”，确定即可。具体操作如下：

下面就是二级菜单具体操作，需要结合返回值的函数——Indirect：

以上就是今天介绍的关于数据验证的一些应用和操作，大家还可以尝试一下三级菜单制作哦，欢迎讨论和分享。

Hi，大家好，我是胖斯基

又是一个突如其来的周三傍晚，风雨交夹，狂风相随……

想想那许久未更新的公众号，再摸摸那胸前的一整块腹肌，愧色依旧，还是码字减肥吧！

如何在Excel中巧妙的提取你需要的数据？

举个例子：

由于从财务系统导出的数据中，会计科目是一种全级显示，用””来展示层级的划分，但是实际在数据统计或应用的时候，需要用到末级科目，故：如何在批量数据中，快速提取末级科目的名称？

也许猴急如你，一上来就想着函数或VBA，可行是可行，但是这种霸王硬上弓的模式，往往因用力过猛而不能快速得到预期效果……

聪明人，应该懂得循序渐进！

1

查找替换法

曾经有那么一个快捷键，它可以在茫茫人海中帮你寻觅“对象”，活好话不多，Ta就是Ctrl+F。

如果你想起了Ta，那这个问题便迎刃而解，效果如下：

注意这里查找的时候，用到的查询内容是 【*\】，查找内容替换为空，过程进度的处理基本上是秒级。

说明：由于要获取的是末级科目名称，故希望在科目信息中，保留的内容是最后一个【\】后面的内容，所以这里查找的时候采用了通配符【*】，组合起来就是【*\】，故：凡是最后一个【\】前的内容都查询出来，并替换为空

想想，这种温柔的方式，是不是比直接上函数和VBA的来的更和谐？

2

快速填充法

当然条条大路通罗马，何必单恋一种法？

曾经也有那么一个智能填充工具，Ta有那么一点人工智能的特效，能够想你所想，取你所需……

Ta就是在Excel2013版及之后推出的一个快速填充工具（Ctrl+E）。如果你想起Ta，那基本也就是万花丛中笑春风般的效果，如下：

注意：这里手工输入了2条记录（目的在于给Excel更多判断的选择），然后手工拖拽，选择快速填充后，所有末级科目数据自动呈现出来，怎么样？处理进度上是不是也是秒级？

So，循序渐进是不是很重要？

慢慢来，你会发现不一样的精彩

3

函数法

这种方法呢不是特别提倡，毕竟不是人人都能上手就来函数，当然，你要是Excel大神，无妨，怎么开心怎么来。

函数1：=TRIM(RIGHT(SUBSTITUTE(B3,"",REPT(" ",200)),200))

函数2：=MID(B3,COUNT(,FIND("",B3,ROW($1:$99))),20)

函数3：=VLOOKUP("*",RIGHT(B3,ROW($3:$24)-{0,1}),2,)

具体函数逻辑就不在重复累赘说明了

胖斯基|说

在Excel中提取有效的数据，是一项频率极高的操作，其处理逻辑基本都已成为套路，学会灵活应用就好。

当然，依旧要敲黑板说明的是：观察Ta，循序渐进，温柔对待，也许灵巧的技巧，能解决你很大的问题……

更多精彩，敬请关注Excel老斯基

（友情提醒，多图，请在wifi下观看！流量壕请无视本条）

天天都在跟Excel打交道，你的效率有多高？那些Excel里提升效率的神器，你会用多少？

说到提升Excel效率的神器，在小奚心中，神器榜排名第一的非数据透视表莫属了。

不用写公式，不用手工计算，数据透视表通过简单的拖拽就能完成各个维度你想要的数据分类汇总，可以说是基础的Excel操作里面最简单易上手，最实用，最常用的功能了。

话不多说，上目录。本文内容较长，干货较多，大家可以根据目录挑选自己想看的内容。

我是目录，目录是我

上篇：基础操作

01 创建数据透视表

【问】：老板给你公司今年的订单明细，让你告诉他每个销售今年的销售额是多少？

看见了吗？

创建透视表就是如此简单，通过拖拽，就能立马得到老板想要的结果。

有很多初次接触数据透视表的同学对透视表的四个字段（筛选器、行、列、值）的意思不太了解，小奚用一张图告诉大家：

字段拖放在不同的区域，就会以不同的显示方式显示汇总的结果，并且同一个区域内的顺序不同，在数据透视表内汇总的先后层次也会不同。这也就是为什么透视表能完成各个维度的数据汇总。

02 透视表转普通表格

【问】：小奚啊， 老板只需要最终的汇总结果，不需要把数据明细发给他，那么怎么把透视表转成普通表格发给老板呢？

这个很简单，只需要复制数据透视表，粘贴成值就行了，如果喜欢透视表的格式还可以像小奚一样粘贴一下格式。

03 更改值字段计算方式

【问】：老板说我不仅想看每个销售这一年销售的总金额，还想看他们这一年签了多少笔合同。

在前面我们用到的是求和的计算方式，对于这个问题，我们可能就需要用到计数的计算方式了。

订单编号是文本类型，我们可以看到将订单编号拖到值字段时Excel会自动计算，因为文本类型不能求和，而金额是数值，可以求和，也可以计数。

除此之外，值字段的计算方式还有以平均值，最大值，最小值等计算，但是在我们平时的运用中，最常用到的还是计数和求和这两种。

04 插入计算字段

【问】：老板说，我还想知道每个销售以现在的年销售额他们的提成能拿多少（按千分之七来算）？

这里我们有一种偷懒的做法，直接在透视表外面乘以0.007，对于这种方法会出现两个问题：

第一，有的同学会发现写完公式下拉后，数字并不会改变，仍然是第一个的计算结果，对于这种情况可以如此操作：【文件】-【选项】-【公式】-取消勾选【使用GetPivotData函数获取数据透视表引用】，如下图所示。

但即使是解决了第一个问题，也会发现一旦改变透视表的结构，我们的计算结果就不能使用了，因为它在透视表外面不会随着透视表的改变而改变。

所以，其实最好的方法是在Excel里面插入计算字段。

05 透视表排序与筛选

【问】：老板说，把每个销售以销售额降序排列，同时，给我筛选出总销售额排名前10的销售。（老板，您的需求不能一次说完吗？）

降序排列其实比较好办，只需要选中金额所在的地方，右键选择降序排列即可。

如果存在多个字段的情况下，怎么筛选出老板想要的呢？直接使用行标签去筛选是行不通的，解决方法是：将鼠标点在列标签外面一格，然后使用筛选功能，具体操作请看下图。

06 行列百分比汇总

【问】：前面说的都是数值，但是我想看百分比怎么办？

想要值以百分比的形式显示可以：【右键】-【值显示方式】-可选择相应的百分比选项。

数据透视表有多种数据百分比的显示方式，下面我们只挑选最常用的：总计百分比、行/列汇总百分比和父级汇总百分来看。

总计百分比

当我们分析各个项目占总值的百分比时，就可以用总计百分比。例如：老板想看销售1部在服装这个产品占整个公司的销售额的多少。

行/列汇总百分比

当我们想看某个数据在行字段或者列字段的垂直维度上的占比时，就需要用到行/列汇总百分比。

列汇总百分比，例如：老板想看每个销售在单个产品上的占比是多少，我们就需要拉列汇总百分比给他看。

行汇总百分比，例如：老板想知道单个销售在每个产品的上售卖金额占比是多少，我们就需拉行汇总百分比给他看。

父级汇总百分比

当我们不想看某个数据占全部的占比，只是想看它在细分维度的占比的时候就需要用到父级汇总占比。

例如：老板说我想知道王麻子的销售额在他们团队占比是多少。

07 修改行列字段顺序

【问】：小奚，透视表自己出来的行列字段的顺序有的时候并不是我们想要的顺序，是不是只能【右键】-【移动】-【上移/下移/移至开头或结尾】？

其实小奚曾经也是这么干的，当时字段还特别多，小奚拿着鼠标点右键，上移下移了无数次，差点没崩溃。

这时领导从小奚身后飘过，实在看不下去了，说了一句：“我都是直接拖就可以了。”此刻小奚的内心戏是：“领导，我错了，是我学艺不精，你就当我是在练手指的灵活度吧！”

恩，忍住笑，严肃脸。下面来看领导是怎么拖的，上动图（前部分为上下移动的操作方式，后部分为直接拖动的操作方式）。

丢了这么大个人，不扳回一城不是小奚的风格啊，所以小奚又潜心学习，一个关于修改字段的更高阶技能出现啦！

这个技能对于反复使用的字段比较方便，平时若用得少，直接拖动会更方便一些。

【先做一个辅助表】-【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【选择最开始建立的辅助表】-以后就只需要排序就能按我们希望的字段顺序出现了。

08 刷新与更改数据源

刷新

【问】：如果我想修改源表的数据，透视表会自动更新吗？

默认是不会自动更新的，需要手动刷新，如果是在原基础上修改，不增加行列的话，我们只需要刷新就可以了，如果有多个数据透视表可以选择全部刷新。如果害怕自己忘记刷新，也可以设置【打开文件时刷新数据】

更改数据源

【问】：我的数据源表修改了，刷新了也没有出现我修改的东西怎么办？

这种情况，就是我上面说的增加了行或者列啦，只是刷新是不行的，还需要更改数据源。

更高级的用法

有的时候我们并不想每次都去修改数据源，那太过于麻烦。又怎么办呢？

把数据源把设置成“表格”就可以解决这个问题。

只要把数据源表设置成“表格”，不管增加行还是列都不需要再去更改数据源，只需要刷新即可（注意，只针对将数据源更改为“表格”之后建立的透视表有效，这也是为什么在动图的例子里小奚要重新建透视表的原因）。

并且通过动图可以看到，设置成“表格”后，如果在首行输入公式都不需要下拉，会自动匹配。

09 透视表的复制与删除

【问】：我想复制或者删除透视表怎么办？删除必须要把整个sheet删除掉吗？

复制透视表的情况其实蛮常见的，因为有的时候选取的数据源是相同的，需要做不同维度的汇总分类，如果不想重新新建sheet，那么复制透视表后在这基础上更改字段是最好的方法。

只需要全选透视表，复制粘贴即可。

删除透视表只需要全选透视表，直接按detele键就能全部删除。

中篇：美观与布局

10 透视表的三种布局

【问】：做为一个有追求的员工，只会Excel透视表默认的呆板展示方式简直不能忍，好吗？小奚呀，为什么别人的透视表和自己的就是长得不一样呢？

那我们就要从透视表的三类展示姿势开始说起了，这三类布局分别是：压缩形式、大纲形式、表格形式。在哪里找到这三类布局呢？

【设计】选项卡-【布局】菜单栏-【报表布局】（注意哟，给透视表穿上美丽外衣的大多数功能都是在【设计】选项卡实现，这里也是我们今天的主要阵地，大家可以自己研究研究这个选项卡的内容噢!）

压缩形式

有眼尖的同学已经发现啦，其实压缩形式就是我们Excel默认的透视表格式，它主要的特点呢就是：

无论叠加多少个行字段，都只占一列。如果对这个概念还不是特别明白，可以多和下面讲到的两个布局方式做对比，相信你很快就能明白啦。

大纲形式

大纲形式与压缩形式最重要的区别就是：大纲形式有几个行字段就会占几列，即行字段会并排显示，就如我们例子中的行字段有三个，那么大纲形式的布局就会占三列而不像压缩形式只占一列。

另外，大纲形式的分项汇总显示在每项的上方。

表格形式

表格形式的透视表是小奚最常用的一种形式。它的主要特点呢是：

1、与大纲形式一样，行字段会并排显示，有几个行字段会占几列；

2、与大纲形式不同的是，表格形式是有表格的（好像在说废话，记得看图找区别噢）；

3、与大纲形式第二个不同是表格形式的分项汇总是在每项的下方，而大纲形式是在上方。

以上的三种布局形式的特点，你都了解了吗？

11 显示和隐藏分类汇总

【问】：小奚啊，你的例子里面，我只想看每个销售细分到各省份客户的销售额，并不想看销售汇总的销售额，并且那些销售汇总放在里面看得我眼花，怎么解决呢？

嗯，确实是这样，有的时候我们并不需要看分类汇总，但是透视表会自动显示，有的时候我们甚至都不需要看总计，那么怎么隐藏和显示分类汇总和总计呢？还是在【设计】选项中哦！

看完动图大家应该比较清楚在哪里显示和隐藏分类汇总和总计了吧？

不过细心的同学应该已经发现动图里一个小问题了吧？在这里小奚要考一考大家哟！

【提问】：为什么小奚选择的是在组的顶部显示分类汇总，最后Excel还是在组的底部显示的分类汇总呢？要结合我们前面讲的内容哦，知道答案的同学请大声在留言区告诉我吧！

12 标签项重复显示

【问】：对于大纲形式布局和表格形式布局，因为行字段是并排显示，特别是前面的行字段，常常一个就对应了后面多个字段，让表格不好看，怎么优化呢？

哇！这个问题好，在这里小奚要告诉大家两个优化的方法：第一个是让我们的标签项重复显示，第二个是合并行标签。

先讲让标签项重复显示：【设计】选项卡- 【布局】-【报表布局】-【重复所有项目标签】。（注意，标签重复项只对大纲式和表格式有效，对压缩式无效，想想为什么？）

13 合并行标签

合并行标签也是回答上面的提问，这是另外一个优化行字段并排显示的方式。

当然，大多数的同学可能会对合并行标签更感兴趣一点，因为小奚发现，大多数的同学在操作Excel的时候，对合并单元格尤为热衷。（虽然合并单元格一直被称为Excel处理数据时的一大杀手）

好了，废话不多说，直接上操作：【右键】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】（注意：合并行标签只对表格形式布局有效，对大纲式和压缩式无效，不信你可以试试哟！）

14 插入空行间隔

【问】：小奚啊，我们公司的透视表数据很多，老板说看得他密集恐惧症都要犯了，该怎么办呀？（同学，你是认真的吗？）

我们在每一项之间插入空行，对于透视表数据很多的情况可能会有所帮助。

具体操作：【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】

对于插入空行，在展示的时候会比较好，因为看起来让人不那么累。但是在日常处理数据的时候，空行会带来一些麻烦，所以处理数据时还是建议大家不插入空行哟。

15 取消字段前+/-符

【问】：透视表行标签前面总是有+/-符号，看起来有些丑，可以隐藏吗？

当然可以呀，上操作：【分析】选项卡-【显示】-【+/-按钮】

16 刷新后格式保持不变

【问】：前两天一个同事一脸悲痛地来找我，说透视表的排版布局我都做好了要给领导看，行高和列宽都需要固定，不能变，但是每次我一刷新透视表列宽和格式就全变了，要崩溃了。

不知道工作中你是否也遇到了这样的情况呢？不要心急，一招就能帮你搞定：

【右键】-【数据透视表选�...

如果有一张上千行的销售表，像下图（各个地区按天统计的1至12月的物品销量），要统计每个月，每个地区的数据，当看到上千行的数据，是不是发愁无从下手呢，利用数据透视表，轻松搞定。

1、选中数据表中任意单元格，点击工具栏插入——数据透视表。弹出创建透视表对话框，点击确定。

2、在右边数据透视表字段对话框添加字段，这里勾选订购日期、地区、分类、销售额和成本。字段勾选根据数据分析的要求勾选。

勾选后，左边单元格会生成下图所示数据表。

3、但是日期是按天来统计，我们需要的是按月统计，选中行标签统计的某一天的单元格，例如2015/1/24，右键创建组，在组合对话框中，选择月，确定。

4、确定后生成下图所示的统计表，但是原始数据没有统计利润，这里我们为了说明问题，我们简单统计利润。

5、增加字段，统计利润，假设利润为销售额减去成本，点击数据透视表工具的分析菜单选项。

6、点击字段、项目和集——计算字段。

7、在插入计算字段对话框中，名称填写利润，公式填写=销售额-利润，确定。

8、则上千行的数据按月份统计完成。

这里只是数据透视表的基本用法，数据透视表还可以排序、筛选，还可以转换称图表，还有很多更强大用途。

数据透视表是Excel中最为简单、实用、灵活的工具，没有之一。然而，很多人使用Excel时最开始接触的是函数公式（Excel另一个实用、灵活的功能）而对其着迷，因而对于数据透视表认识不足，从而导致其看轻其功能。本文将简单介绍数据透视表的创建技巧，让大家了解Excel数据透视表的特性。如果看完本文，大家能够被数据透视表的魅力深深折服，我的目的也就达到了。

为什么我很喜欢用数据透视表来分析数据？

下图是我公司二月份的仓库产品出库记录（有100多行记录），现在我们需要快速地统计出每个仓库各产品的出库数量，该如何做呢？

我们得到的结果将如下图所示：

很多没有接触过数据透视表的同学也许会用函数，但发现会非常难，函数掌握得不错的同学或许会得到下面的公式，完成了任务，但对于大多数Excel来说，完成这样的统计几乎不可能。

{=SUM(($B$2:$B$126=$I3)*($D$2:$D$126=J$2)*$E$2:$E$126)}

或者

=SUMIFS($E:$E,$B:$B,$I3,$D:$D,J$2)

当我们得到J3:K13区域的值，要求总计就很轻松，直接在使用sum函数即可。

虽然上述方法可以达到我们的目的，但我们也提到，它比较适合有一定函数基础的人员。不但如此，这种方法还非常不灵活。

为什么说数据透视表比函数灵活10倍？

假如我的需求改了，我不想知道每个产品在每个仓库的库存，而是想知道每个仓库每天的出库量?又或者是每天每个产品的出库量？当新的需求出现时，我们不得不重新写公式，甚至右侧的统计表格每次都得跟着改变，非常麻烦，然而一旦使用数据透视表，这些问题就都不存在了。以刚刚提到的需求来进行演示：

1.求每个仓库每个产品的出库量

操作技巧：选中源数据任意单元格，单击插入--数据透视表，弹出的对话框中单击确定，这时创建了一个数据透视表---数据透视表字段窗口中拖动品名至行，仓库名称至列，数量至值，即可完成。

2.求每天每个产品的出库数量

操作技巧：将之前的仓库字段前面的√去掉，然后将日期字段拖至行里品名字段的下方即可。

3.求每个仓库二月份的出库量

操作技巧：去掉品名、日期等字段前面的√，然后拖仓库名称至行即可。

通过这3个简单的演示，相信大家已经看明白了数据透视表的强大与灵活。