1，柱形图&折线图组合分析各月销售收入和增长率。

动图演示：

2，柱形图分析计划与实际的销售差异。

3，分离饼图分析各产品的销售利润百分比。

4，柱形图和圆饼图组合比较产品销售额。

5，复合饼图分析销售收入构成。

6，堆积条形图表比较顾客拥有量。

如何更好地把销售工作人员的业绩状况展现出来？动态图表来展示业绩与其他对比情况是最好不过的，将每个月都可以单独的进行选项展示，下面就来学习一下这个Excel使用教程吧。

Excel使用教程第一、打开开发工具中的列表框窗体控件，右键，弹出控件格式中的数据区域，设置选择月份，然后在{单元格链接}指定一个空白单元格，系统它会给出一个数据，我们选择的内容是列表框中排列的第几项。

Excel使用教程第二、复制表头一份，然后使用引用函数“OFFSET”，具体公式：“=OFFSET(A3,$H$1,0)”继续填充。

Excel使用教程第三、选择引用区域插入图表。

IT之家小白课堂，小白也能看懂的教程，欢迎收藏 :)

Excel的函数公式里，VLOOKUP是经典的查找引用函数。而CP组合INDEX+MATCH，操作上更灵活，很多时候能替代VLOOKUP。

举例，你有一份客户资料表，包括客户名称、区域、省份等。现在需要在销售明细表里，填入客户名称，自动输出对应的区域、省份。效果图如下。

怎么操作？可以通过VLOOKUP函数实现，不过IT之家下面讲解的案例，主要采用INDEX+MATCH。首先看下各个函数的基本含义，以及通俗语法。

1、INDEX函数：返回表中的值。

=INDEX（在哪儿找，第几行）

2、MATCH函数：返回指定数值在指定区域中的位置。

=MATCH（找谁，在哪儿找，匹配方式）

3、VLOOKUP函数：纵向查找返回表中的值。缺点：查阅值需要位于查找区域的第一列。

=VLOOKUP（找谁，在哪儿找，第几列，匹配方式）

下面看看销售报表案例里，INDEX+MATCH嵌套函数的写法。

A3处的公式如下

=INDEX(G:G,MATCH(C3,H:H,0))

这是一个嵌套函数，先计算MATCH函数，再将结果作为参数，计算INDEX函数。

首先，MATCH在H列查找匹配，结果为4，即C3的值在H列里位于第4行。

然后将MATCH的结果4作为参数，INDEX在G列查找返回结果G4“广东”。

在销售明细表里，把这个公式往下拉，那么每次输入客户名称，就会自动输出客户所在省份。这样可以避免手动输错的问题，也能节省一些时间。

同样的案例，用VLOOKUP函数怎么写？需要将案例中的G列、H列顺序对调，保证要查找的客户名称列，在查找区域首列。

A3处的公式如下。

=VLOOKUP(C3,G:H,2,0)

如果销售表、客户表不在同一张表里呢？很简单，公式前加入“工作表名称!”即可，如下。

=INDEX(客户!C:C,MATCH(D2,客户!D:D,0))

报表里还有一个非常重要的功能：数据验证（早期Office版本里叫数据有效性）。有什么作用？可以规范输入的数值格式，同时，还能提供下拉框来选择输入数值。

如果输入的数值不符合规定，则弹窗提示无法输入。本案例中，就能避免出现“中国联通”“联通”“联通公司”这样的多种写法。当多人维护同一份表格，再也不怕大家乱输数值了。同理，品名规格列也可以做数据验证。

操作路径：销售表里全选D列，菜单栏-数据-数据验证，允许选择“序列”，来源输入下面的公式。

=OFFSET(客户!$D$2,,,COUNTA(客户!$D:$D)-1)

公式含义：销售表里的客户名称列，只能输入客户表里已记录的客户名称。注意要加入绝对引用符号“$”，保证参数数值不变。

销售报表案例表格：点此下载

每天晨六点，准时与您相约

问题来源

昨天，韩老师讲了：Excel198 | N多产品月销售报表，提取销售量最大的月份，

有朋友立马问：有没有提取销量前三位月份的方法？

当然有，方法和昨天的类似，只是换一个函数。

公式实现

在N2单元格输入公式：

=OFFSET($A$1,,MATCH(LARGE($B2:$M2,COLUMN(A$1)),$B2:$M2,0))

公式向下向右填充，即得每种产品销量第一、第二、第三位的月份。

如图：

公式解析

COLUMN(A$1)：

A1位于第几列，返回值是1，公式向右填充，会自动变为COLUMN(B$1)、 COLUMN(C$1)，返回值是2、3；

LARGE($B2:$M2,COLUMN(A$1))

取B2:M2区域数据第一大的值； 公式向右填充，会自动变为取第二、第三大的值 ；

MATCH(LARGE($B2:$M2,COLUMN(A$1)),$B2:$M2,0)：

匹配B2:M2区域第一 大的值所在列，公式向右填充，会自动变为取 第二、第三大的值所在的列 ；

OFFSET($A$1,,MATCH(LARGE($B2:$M2,COLUMN(A$1)),$B2:$M2,0))

以A1为基准点，偏移0行，第一大值在数据区域第几列就偏移几列，即得到最大值所在的月份；公式向右填充，会自动变为第二、第三大的值所在的月份。

LARGE函数说明

功能：

返回数据集中第 k个最大值。 您可以使用此功能根据其相对位置选择一个值。 例如，您可以使用 LARGE 返回最高、第二或第三的分数。

语法：

LARGE(array,k)

参数：

Array 必需。 需要确定第 k个最大值的数组或数据区域。

K 必需。 返回值在数组或数据单元格区域中的位置（从大到小排）。

（OFFSET、MATCH函数解析，请参考昨天的文章）

素材下载

本次练习下载：

链接：http://pan.baidu/s/1boKfr23

密码：9snx

这样的动态销售仪表盘，可以多角度展现年度销售情况，包括每月总销售额的变动趋势、各种产品分类的销售占比、明星产品排行、销售员业绩排名，还可以按地区和城市进行动态筛选，想必通过这样的数据分析，一定会让领导看出销售整体状况的子丑寅卯，也为未来制定销售战略提供了可靠的数据依据，怎么样？对这样的实效强而颜值高的仪表盘，你心动了吗？那就随我一起来实现它吧。

首先，我们一定要做好一张最关键的表，就是源数据表，源数据相当于地基，地基不打牢，盖啥样的房子都是白搭！

下面，基于源数据表创建数据透视表，数据透视表是基于源数据表进行变表的最佳工具，拖拽之间即可完成炫酷报表，简单又百变，所有表亲们都值得拥有！

通过拖拽设置数据透视表的报表字段，对于月销售额分析，将“订单日期”拖到“行字段”中，将“金额”拖到“值字段”中，此时，在行字段中将自动出现“月”，由于我们仅按月份统计销售额，因此，将“订单日期”再从“行字段”中拖出去，仅保留“月”即可；

基于月销售额分析数据透视表，制作数据透视图并进行美化，这样的透视图表，即可实现随着源数据的改变而动态变化，保证了数据的正确性和一致性！

复制月销售额分析数据透视表，以制作Top5明星产品数据透视表，调整报表字段，将“月”从行字段中拖出去，将“产品名称”拖入到行字段中，点击“行标签”，选择“值筛选”中的“前10项”，更改为“最大5项”：

基于Top5明星产品数据透视表，插入条形图，以制作Top5明星产品排行表，并进行图表美化：

复制月销售额分析数据透视表，以制作产品分类占比数据透视表，调整报表字段，将“产品分类”设置为行字段，并设置“金额”值字段的显示方式为“列汇总的百分比”：

基于产品分类占比数据透视表，插入饼图，以制作产品分类占比图表，并进行图表美化：

复制月销售额分析数据透视表，以制作销售业绩汇总数据透视表，调整报表字段，将“销售员”设置为行字段，并设置行标签按“求和项：金额”进行降序排序：

基于销售业绩汇总数据透视表，插入簇状柱形图，以制作销售业绩汇总排名表，并进行图表美化：

最后插入两个切片器，一个是“城市”切片器，一个是“地区”切片器，设置切片器的样式，并设置切片器的报表连接，以实现切片器对多个透视图表的数据联动：

OK，炫酷的仪表盘做好了，你学会了吗？

对于在职场工作的朋友，肯定有需要使用Excel制作各种产品报价单，财务报表，工资表等等，如果不熟练会很耽误时间，不如直接套用Excel模板，还可以提高工作效率呢，所以小编今天先分享几款产品报价单模板给大家，有喜欢的可以收藏哦！（文末注明模板来源）

以上就是今天为大家分享的产品报价单Excel模版啦，这些模板都是从“办公资源”网站下载的，大家如果需要的可以去找找看有没有你想要的。

好了，今天的分享就到这里了。

制作月份销售计划表，如下图：

AVERAGE函数最多30个

下一年计划公式

调整图，美化图。

简单吧！

（友情提醒，多图，请在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 刷新后格式保持不变

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

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

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

如要实用，至少要4张表联合起来用才顺手，

1. 以下是一个小型的进销存系统，

2. 要做到这种程度，也得花费几天时间，索性给展示一下，供参考。
   

示意图如下(共4张)

在<<产品资料>>表G3中输入公式:=IF(B3="","",D3*F3)  ,公式下拉.

在<<总进货表>>中F3中输入公式:=

IF(D3="","",E3*INDEX(产品资料!$B$3:$G$170,MATCH(D3,产品资料!$B$3:$B$170,0),3))  ,公式下拉.

在<<总进货表>>中G3中输入公式:=IF(D3="","",F3*IF($D3="","",INDEX(产品资料!$B$3:$G$170,MATCH($D3,产品资料!$B$3:$B$170,0),5)))  ,公式下拉.

在<<销售报表>>G3中输入公式:=IF(D3="","",E3*F3)  ,公式下拉.

在<<库存>>中B3单元格中输入公式:=IF(A3="",0,N(D3)-N(C3)+N(E3))  ,公式下拉.

在<<库存>>中C3单元格中输入公式:=IF(ISNUMBER(MATCH($A3,销售报表!$D$3:$D$100,0)),SUMIF(销售报表!$D$3:$D$100,$A3,销售报表!$E$3:$E$100),"")  ,公式下拉.

在<<库存>>中D3单元格中输入公式:=IF(OR(NOT(ISNUMBER(MATCH($A3,总进货单!$D$3:D$100,0))),A3=""),"",SUMIF(总进货单!$D$3:$D$100,$A3,总进货单!$F$3:$F$100))  ,公式下拉.

至此,一个小型的进销存系统就建立起来了.

当然,实际的情形远较这个复杂的多,我们完全可以在这个基础上,进一步完善和扩展,那是后话,且不说它.

（本文内容由百度知道网友shihoumacili贡献）

之前让你以现在的水平，评价一年前做的表格。其实，我自己本身就是经常这么做。不断的成长，复盘，认知才会升华。

这是我8年前做的订单管理表，这里涉及到5条公式。

1.获取动态日期，也就是每一次打开表格，都可以自动更新到当天的日期。

在O4单元格输入公式。

=TEXT(NOW(),"yyyy.mm.dd aaaa")

2.根据番号跨表查询俗称。

对应表

在C6单元格输入公式，下拉。

=IF(B6="","",VLOOKUP(B6,对应表!A:B,2,0))

3.统计出货的合计。

在M6单元格输入公式，下拉。

=IF(F6="","",SUM(F6:L6))

4.计算还剩下的尾数。

在N6单元格输入公式，下拉。

=IF(COUNT(D6,F6:L6)<=1,0,D6-M6)

5.判断完成情况。

在O6单元格输入公式，下拉。

=IF(N6="","",IF(N6=0,"OK","NG"))

完成情况还借助条件格式，让NG显示红色字体。

6.对数据进行最终汇总，统计每个俗称的订单数、出货合计数、尾数。

对数据源创建透视表，这里有一个注意点，区域要重新选择，从A5开始选，切记！

将字段拉到相应的位置。

以上，就是8年前我工作上经常使用的一份表。虽然那时水平一般，不过有一些初步的数据处理概念，比如三表理论。

三表指数据源（明细表）、对应表（参数表）、汇总表。