EXCEL通俗易懂讲公式(五)

这次讲讲vlookup的进阶使用，excel中函数经常会用来帮忙做动态图，所谓动态图就是通过调节一些参数实现图表的自动变换，变换表格内的数据或者变换图表默认的数值区域。

既然这段时间在写vlookup，那就先从最简单的动态图说起吧，用vlookup的方式实现动态柱状图

1、首先准备原始数据，我用的随机生成的1000内的数据 int(rand()*1000)

2、作图用表准备

找一个空行，我这里找的是20行开始的

在A21到M21写上月份的标签

在A22制作下拉菜单，用于控制动态图

通过数据有效性制作菜单

excel2007直接可以在数据菜单栏里找到，我用的是2016

制作菜单：选中A22后，打开数据验证

完成

3、柱状图数据提取

在B22输入公式 VLOOKUP($A$22,$A$1:$M$4,B20,0) 并向右拖到十二月

4、生成柱状图

点击菜单栏，自动完成城市间的数据切换

到此，excel中通过vlookup函数实现的简单动态图就完成了

你们想知道ppt2007如何把表格转簇状柱形图吗?操作步骤非常的简单易懂，下面想学习的小伙伴就来看看本篇文章吧。

PPT2007将表格转簇状柱形图的步骤

首先，打开PPT文件。点击开始选项卡中的“新建幻灯片”，如图所示。

PPT2007将表格转簇状柱形图的步骤图1

在“新建幻灯片”菜单中，点击“标题和内容”，如图所示。

PPT2007将表格转簇状柱形图的步骤图2

在幻灯片编辑区域点击“插入图表”，如图所示。

PPT2007将表格转簇状柱形图的步骤图3

在弹出的“插入图表”对话框中，单击插入“簇状柱形图”，如图所示。

PPT2007将表格转簇状柱形图的步骤图4

此时会自动打开一个相应的Excel表编辑区域。

PPT2007将表格转簇状柱形图的步骤图5

我们可以把原先准备的Excel表中的内容复制到这张表中，如图所示。

PPT2007将表格转簇状柱形图的步骤图6

簇状图雏形就出现了，如图

PPT2007将表格转簇状柱形图的步骤图7

选中图表，在“设计”选项卡中点击“选择数据”，如图所示。

PPT2007将表格转簇状柱形图的步骤图8

在弹出的窗口中选择“确定”，如图所示。

PPT2007将表格转簇状柱形图的步骤图9

完成效果图（簇状柱形图一目了然）：

PPT2007将表格转簇状柱形图的步骤图10

以上就是ppt2007把表格转簇状柱形图的方法，希望可以帮助到大家。

 柏拉图，也叫帕累托或者PARETO，一般使用专业工具来绘制，下面来讲讲如何用EXCEL2007制作出标准柏拉图，让OFFICE新手都可以轻松制作出来。

 先看看最终的效果，最终制作出来的应该是如下所示的：

 1.根据你要做的柏拉图的几个要点(反映到图中就是横坐标)列出来，每个要点对应的数量和累计百分比是多少，按照从大到小排列，如果要点很多，只需取前几个要点，如图：假设有10个问题点，我只列出前5项，后5项发生的数量很少这里并作其他问题点 

2.插入柱形图，选择2个系列，分别对应2个纵坐标轴，这个应该没问题吧

 3.选择第二个系列，就是红色那个柱状图，右键选择设置数据系列格式，选择次要坐标轴，就是我涂了黄色的那块，然后再选择红色柱状图，右键选择更改系列图标类型，选择XY散点图，如下图

 4.选择图表，在上面菜单栏里选择显示次要横坐标轴，并把坐标轴范围设置为最小值是0，最大值是6，(因为这里我列出了6个问题点)；然后再选择红色的折线图，设置X轴和Y轴的数据，注意X周选择0-6，Y周第一个数据选择0，然后按照顺序选择百分比的数据，如图

 5.选择哪个折线图，右键选择设置数据系列格式-系列选项-次坐标轴，就变成如下所示：把图例和次要横坐标轴删掉，更改左边和右边的纵坐标轴，左边改为求和的数值87，最小值是0；右边最小值是0，最大值是1，顺便改下数字显示类型为百分比；

 6.选择蓝色柱状图，设置数据系列格式，间距调为0，边框黑色，可以设置阴影这样效果要好点；选择红色的折线图，设置数据系列格式，选择数据标记选项，把小圆点调出来，然后把填充色改黄色或其他颜色，这样就基本成型了，

 7.接着做一些细节调整，首先把标题提出来；然后右键蓝色柱状图，添加数据标签，然后右键红色折线图，添加数据标签；点中间区域，把主要横网格线去掉（可以选个填充色）；再把折现图的数据标签的0和100%这2个标签分别拖到左上角和右上角，用于填写单位，最后再插入一个文本框输入日期和n，就大功告成了。

1、打开Excel建立一个表格如下图所示

2、然后点击图，复制图，打开word，如下图所示，

3、当我们点击右键的时候会发现粘贴下有5种选择性粘贴的图标。如下图所示，第一种和第二种是不带有数据链接的，第三种和第四种有原始数据链接，有原始数据链接表示的是假如你在最开始的Excel表格中修改了数据，那么word中的数据也会自动修改，假如删掉了原来的Excel表，那么word中的数据也没有了。然后这两种里面又分别有原格式和目标格式只说，原格式就是Excel表中的格式，目标格式就是word中的格式。而经常为了防止图因为数据修改而出现问题，可以选择第五种选择性粘贴的方式，粘贴为图片。

4、现在还有一个问题，假如word是2007版以前的怎么办，也就是我们打开的word是兼容模式的，那么这个时候可以对照一下，都将Excel表中的图复制为没有链接的目标文件。会发现在兼容模式下就没有了在图上可以进行直接修改的标记了。如下图。

5、但是在兼容模式下只是不能对数据进行修改，然而对于其他的我们可以通过一种方式让图回复它原来的模样。如下图，兼容模式下的图，可以点击以下会有虚线框出来，然后点击两下复制过来的图，会发现图标比原来的清楚多了，然后可以对图标的标题，横纵坐标轴等进行修改，但是不能修改数据。

6、综上，虽然另存为图片的形式比较不会改变数据，但是可以选择不链接数据的方式进行复制，既可以保留图的清晰度，又保证图不会随意被修改。

7、完成。

（本文内容由百度知道网友火云匪贡献）

在《老外的Excel表格为什么这么漂亮》一文中，模拟老外用Excel做了一个动态图表：

很多同学非常好奇是怎么做出来的。今天就分享一下动态图表的制作和美化方法。

数据源表

1、生成饼图

选取B、C列并插入饼图。然后填充背景色为浅蓝色或灰色，图例右侧双列显示。

2、添加顶部标题

插入 - 图形：矩形 - 设置填充色 - 输入标题文字

3、插入控件

开发工具 - 插入 - 表单控件 - 组合控件。在打开的“设置控件格式”窗口中进行以下设置：

数据源区域

：选取已输入的待选项区域。

单元格的链接

：选取P3单元格。下拉选单选取后会以数字的形式显示选取的内容。

三维阴影

：选中

注：各版本显示开发工具方法

2003版：工具栏右键 - 窗体

2007版：Office按钮 - 选项 - 常规 - 显示开发工具

2010、2013版：文件 - 选项 - 自定义功能区

4、定义名称

公式管理器 - 定义名称 - 名称：季度，引用位置输入公式：

=OFFSET($B$5:$B$12,,$P$3)

5、数据源引用名称

选取饼图系列，把公式的倒数第2个参数替换为定义的名称

=SERIES(动态图表!$C$4,动态图表!$B$5:$B$12,动态图表!

$C$5:$C$12

,1)

修改为:

=SERIES(动态图表!$C$4,动态图表!$B$5:$B$12,动态图表.xlsx!季度,1)

6、调整格式

把所有文字和数字调整为合适大小，字体设置为

微软

雅黑

字体

7、组合图表

按CTRL键分别选取图表、矩形和组合框控件，右键-组合。组合到一起后就可以一齐移动了。

好看又好用的动态图表制作完成

Excel制作动态图表有很多种做法，方法不重要，重要的是最终展现的效果是不是你想要的。

点击上方蓝字 ▲关注订阅会计从业

了解会计证书信息

二师姐在 《老外的Excel表格为什么这么漂亮》 一文中，模拟老外用Excel做了一个动态图表，小伙们在后台一直不停的问我怎么做到的？那么，今天就给你们整理了出来。

数据源表

1、生成饼图

选取B、C列并插入饼图。然后填充背景色为浅蓝色或灰色，图例右侧双列显示。

2、添加顶部标题

插入 - 图形：矩形 - 设置填充色 - 输入标题文字

3、插入控件

开发工具 - 插入 - 表单控件 - 组合控件。在打开的“设置控件格式”窗口中进行以下设置：

数据源区域 ：选取已输入的待选项区域。

单元格的链接 ：选取 P3 单元格。下拉选单选取后会以数字的形式显示选取的内容。

三维阴影 ：选中

注：各版本显示开发工具方法

2003版：工具栏右键 - 窗体

2007版：Office按钮 - 选项 - 常规 - 显示开发工具

2010、2013版：文件 - 选项 - 自定义功能区

4、定义名称

公式管理器 - 定义名称 - 名称：季度，引用位置输入公式：

=OFFSET($B$5:$B$12 ,, $ P$3)

5、数据源引用名称

选取饼图系列，把公式的倒数第2个参数替换为定义的名称

=SERIES(动态图表!$C$4,动态图表!$B$5:$B$12,动态图表!$C$5:$C$12 ,1)

修改为:

=SERIES(动态图表!$C$4,动态图表!$B$5:$B$12,动态图表.xlsx!季度 , 1)

6、调整格式

把所有文字和数字调整为合适大小，字体设置为 微软 雅黑 字体

7、组合图表

按CTRL键分别选取图表、矩形和组合框控件，右键-组合。组合到一起后就可以一齐移动了。

好看又好用的动态图表制作完成

Excel制作动态图表有很多种做法，方法不重要，重要的是最终展现的效果是不是你想要的。

本文转自Excel精英培训（ID：excelpx-tete) 。由会计从业（ID：gaoduncy）整理发布。

推荐阅读

"你们财务工资多少？""三万多一点！"

1900、160、30、85、90……身为会计人，你知道这些数字的含义吗？

清华毕业生：我奋斗了18年不是为了和你一起喝咖啡

你认真贴发票的样子，好像天桥下贴膜的啊

2017年高盛只招了7名实习生，月薪38000！

50句《我的前半生》台词文案，句句扎心！可怎么就大结局了...哎

为什么老外的Excel表格这么漂亮？

外卖小哥被暴晒：底层人士的悲哀，有钱人不会懂

最近因为工作关系有一段时间没能上，今天继续我们EXCEL图表的分享，今天主要分享的是双因图，如下：最佳雇员人数与销售额及利润之间的关系图，首先我们来看，图表分为左右2个部分，左边是雇员与利润之间的关系，最初是随着人员的增加，利润是直线上升，当人员到达一定是，利润增加开始缓慢，最后随当达到1.5万之的，利润开始负增长。右边是销售额与利润之间的关系，销售额的增加利润的增加。综合左右，雇员在合理的人员配置销售额达到一定时，利润是最高的。这就是双因决策图，利润由雇员与销售额两方面来决定。

可能更多的人会采用如下图来表达之间的关系，这样也能比较清晰的表达。

操作方法：原始数据如下：人员与利润的对应表，及销售额与利润的对应表。

接下来是对数据整理：

插入散点图：如下 我们需要调整X轴及Y轴，

辅助数据，雇员X轴，及销售额X轴

添加数据源

按：上次《这个动态图表竟然是用Excel做的？！》帖子发出后，读者杨小府发来了自己的做法，把柱形图做成了堆积的小方块，更接近原例。我请他写了个教程，供大家参考。谢谢小府童鞋分享！欢迎大家打赏，给小府发红包：）

上次在Excel图表之道公众号看过刘万祥老师题目为《这个动态图表居然是用 Excel 做的？！》之后，再一次被刘老师的技艺折服，翘课的同学可以再学习下，原文链接是：http://mp.weixin.qq/s/FtRS1bwg2yRSKHFFxqRm3Q。

下面的两张图就分别是原《财新》杂志的范例和刘老师做的模仿图。

1.《财新》杂志范例

2.Excel图表之道模仿图

是不是吊吊哒？这张图的特点我就不讲了，上次刘老师讲的已经很详尽了。但是呢，对比下来，感觉刘老师的模仿图做到了神似，形上还有些地方可以改进，这么说希望老师别介意:-)。

为什么这么说呢，细心的同学应该可以发现，《财新》原图是用不同的圆点元素代表特定单元值，所以大多数数据表的顶端是不平齐的，而刘老师的模仿图采用的是针对堆积柱形图进行图案填充，也就没有了这种特点，在精细、美观两点尚有待提高。

作为一个不逼死自己不罢休的强迫症患者，我就会想，用Excel是不是可以做到更逼真的模仿呢？经过一些尝试，确实是可以的。接下来我就跟大家分享下我的小方法——用条件格式模仿中国对外房地产投资动态图。

分享形式呢，就模仿老师的方式吧，分为作图思路、制作步骤、知识回顾三部分，一方面更容易比较两种方法的异同，一方面也是致敬刘老师。

作图思路

选择器。图表之道用的是单选按钮控件来做的，这种方法更接近原图，起初我也尝试着用这种方法，但是按钮控件位置的锁定上总是有些小问题，所以就改用了比较简单的组合框控件。条件格式“柱形图”。要实现单元点的方式绘制柱形图表暂时没想到什么可行的方法，之前尝试过一种通过多组柱形图并列组合，针对每组柱形图采用色块图片层叠填充的方式做过类似的图，但是效果一般，有兴趣的可以自己尝试下。这里我采用的是缩小excel界面显示比例，并通过条件格式控制单元格填充的方式，做出类“柱形图”的效果。柱形图切换。通过更改组合控件，改变作图区的值，通过条件格式控制对应单元格填充色，即可针对三种不同分类进行区别呈现。3个柱形图的类别标签，这里采用的比较笨拙的方式，插入文本框，并对文本框进行单元格引用，这样就可以实现切换分类时显示对应标签的效果，当然也可以按照刘老师的方法使用散点图，我这里只是提供一种思路。以上就是我们的作图思路，下面就是具体的实现方法了。

制作步骤

1. 首先，制作选择器。

我们的选择器比较简单，插入组合框控件即可，在calc!$AL$7:$AL$9分别输入按整体年份、按国家及地区、按房产类型，并将其作为控件的数据源区域，控件的单元格链接我们选择calc!$H$1。calc!$H$1这个单元格很重要，后面的数据设置基本跟它相关。

2. 第二大步骤是组织作图数据。

数据源我们采用刘老师图表中的数据，处理方法有一部分基本类似，类似的部分我偷个懒直接复制下来，版权为刘老师所有。

先把这个原数据，通过选择性粘贴、粘贴链接的方式引用到新的工作表。这样做是为了方便我们后期好更新源数据。

按国家和按房产类型的堆积柱形图，它是需要悬空以后水平对齐的，所以说我们就要在每行数据下面插入一个空行，组织占位数据。

图中，D16的公式：=$E$2+MAX($D15:$N15)-D15

公式的意思是说我们取上1行的最大值减去上一格的值，然后再加上一个留空值。这个留空的间隔$E$2，我们把它做成一个参数方便调整，暂时取值为整个分类数据里的最大值除以6。

这个公式注意写法，锁定列号，行号不锁定。向右复制到整行，然后能把这一行复制粘贴到余下的所有占位行。检查公式和结果正确。

然后，我们要根据用户的选择，分别引用这3块里面的1块，到作图数据区域。

由于作图方式不同，所以我们的作图数据区域会有所调整，见上图红框部分，这里有2个注意点：

① 合计部分为0是因为设置条件格式的作图区域使用了vlookup的模糊引用，在这里vlookup相当于“>=”的作用，具体的用法后面介绍。

②受vlookup的影响，季度前一列的数字与左边的相比统一向上平移一个单元格，左边序列下空出的单元格采用较大的数字进行填充，这里采用的是100-。

3个区域的公式相应进行调整：

第一块区域：S9：=IF($H$1=1,SUM(D$9:D9),NA())，复制到满区域；

第二块区域：S15：=IF($H$1=2,D15,NA())，

S16：=IF($H$1=2,INT((D16+S15)/($E$1*10))*$E$1*10,NA())，

S17：=IF($H$1=2,D17+S16,NA())，

将S16、S17对应的复制到满区域；

第三块区域：S40：=IF($H$1=3,D40,NA())，

S41：=IF($H$1=3,INT((D41+S40)/($E$1*10))*$E$1*10,NA())

S42：=IF($H$1=3,D42+S41,NA())，

将S41、S42对应的复制到满区域；

3.第三步骤，填充作图区域辅助表数据及填写作图区域数据，并设置条件格式

① 填充作图区域辅助表数据

新建一个sup插页，作为作图区域的辅助内容。

其中D120=0，E120=D120+calc!$E$1，D119 =M120+calc!$E$1，calc!$E$1代表一个单元值，按照这种方式将D1：M120填满，这样就构成了10*120个单元数据，每个单元数据比相邻左方单元格大一个单元值。

这就构成了2007年图的辅助区域，将D1：M120依次向右复制10次，作图区域的辅助表数据我们就完成了。

② 填写作图区域数据

新建一个sub2插页，图片将在这个插页生成！

在sub2插页中中我们选用和上述等宽等高的区域N12：EC131（最开始其实选用的是D1：M120，只不过后期作图存放标签需要空间，进行了相应的调整），将该区域等分成10份10*120的间隔小区域。

第一块区域，N131：=IFERROR(VLOOKUP(sup!D120,IF(calc!$H$1=1,IF({1,0},calc!$S$8:$S$12,calc!$Q$8:$Q$12),IF(calc!$H$1=2,IF({1,0},calc!$S$14:$S$37,calc!$Q$14:$Q$37),IF({1,0},calc!$S$39:$S$50,calc!$Q$39:$Q$50))),2,1),0)，复制到满区域；

第二块区域，Y131：=IFERROR(VLOOKUP(sup!O120,IF(calc!$H$1=1,IF({1,0},calc!$T$8:$T$12,calc!$Q$8:$Q$12),IF(calc!$H$1=2,IF({1,0},calc!$T$14:$T$37,calc!$Q$14:$Q$37),IF({1,0},calc!$T$39:$T$50,calc!$Q$39:$Q$50))),2,1),0)，复制到满区域；

第三块区域，AJ131：=IFERROR(VLOOKUP(sup!Z120,IF(calc!$H$1=1,IF({1,0},calc!$U$8:$U$12,calc!$Q$8:$Q$12),IF(calc!$H$1=2,IF({1,0},calc!$U$14:$U$37,calc!$Q$14:$Q$37),IF({1,0},calc!$U$39:$U$50,calc!$Q$39:$Q$50))),2,1),0) ，复制到满区域；

依次对后续区域进行填充即可，最后使用自定义数字格式“;;;”将数字隐藏。

注：加粗部分代表的是之前calc插页中框住的三个区域不同日期对应的数据。

③设置条件格式

选中N131单元格，通过条件格式>>新建规则>>使用公式确定要设置格式的单元格的方法设置填充色。这里共设置了22次条件格式，举例如下：

.

具体格式见范例文件。

4.美化与收尾

经过上述步骤得到的图仅仅只有柱状部分，但是每个单元的长宽是不一样的，如果想要将每个小单元调成正方形，我们可以通过视图中选择页面布局使行高列宽均变成cm为单位，然后进行调整。

接下来我们还需要使用文本框来做标签，首先添加横坐标标签（年度），这部分恒久存在，直接在文本框中添加年份，放置相应位置码齐即可。其他部分标签因为不同分类显示不同，所以需要使用文本框的动态引用。在AI6：AJ37区域我们分别使用公式对文本的显示进行了定义，通过将文本框引用这单元格后进行字体、字号、位置调整整个图表就算完成了！

由于整个作图区占用区域过大，为了方便呈现，我们使用选择性粘贴中链接图片的方式将其粘贴至data插页，并与筛选框进行组合。

最终的动态图效果如下图：

当然，最后不得不说，这种做法的通配性相比插入式图表要差很多，调整起来会比较麻烦，在这里仅给大家提供一种以后的做图参考思路。

知识回顾

总结一下，以上一共应用了以下几个知识点：

组合框控件的应用；vlookup模糊匹配的应用；自定义数字格式“;;;”隐藏数字；使用公式的方法设置单元条件格式；调整单元格行高列宽一致；选择性粘贴中的图表链接粘贴方法。

范例下载

仍然是在 iamExcelPro 发送 “caixin1” 获得下载链接。

【点评】：小府使用单元格条件格式来实现小方块堆积的条形图，更接近财新的原例，不过实现起来更麻烦，性价比不足，但仍是很好的练习材料。比如，大量练习了多条件的公式型条件格式，可以深入理解和熟练掌握。

前两篇文章里有有这种图表，有小伙伴问是怎么做的，我是用 excel做的，没用到图表功能，用的是条件格式，只用了一分钟就做成了。

1. 选择数据列，然后点条件格式-数据条（只有Excel2007及其以上版本才有这个功能）

2. 在实心填充里选择想要的颜色（我这里是橙色），这样数据列就自动会按照值的大小生成柱状图，如下图。

2. 另外一列数据同理，设置成蓝色数据条。

3. 增加标题，把标题改成黑底白字，增大，加粗。

4. 把字体全部改成微软雅黑，然后把数据区域的背景改成最浅的灰色。

5. 最后细节地方微调一下（数据列居左排列，表头加粗，增加数据来源），就变成最终效果如下。

视频教程如下（为了演示方便，实际视频教程有2分30秒）。

芬哥，湖南人士，网易云课堂认证讲师，曾在500强外企、大型跨国公司担任项目负责人，现某知名美资消费品牌高级工程师。已经写了30万字关于工作中实用英语以及真实职场经验的文章。说一些一般人不会告诉你的东西，用心写每一篇文章，对读者负责，欢迎关注我的头条号。

你一定看过老外的Excel动态图表：

很多同学非常好奇是怎么做出来的。今天小编就分享一下动态图表的制作和美化方法。

数据源表

1、生成饼图

选取B、C列并插入饼图。然后填充背景色为浅蓝色或灰色，图例右侧双列显示。

2、添加顶部标题

插入 - 图形：矩形 - 设置填充色 - 输入标题文字

3、插入控件

开发工具 - 插入 - 表单控件 - 组合控件。在打开的“设置控件格式”窗口中进行以下设置：

数据源区域：选取已输入的待选项区域。

单元格的链接：选取P3单元格。下拉选单选取后会以数字的形式显示选取的内容。

三维阴影：选中

注：各版本显示开发工具方法

2003版：工具栏右键 - 窗体

2007版：Office按钮 - 选项 - 常规 - 显示开发工具

2010、2013版：文件 - 选项 - 自定义功能区

4、定义名称

公式管理器 - 定义名称 - 名称：季度，引用位置输入公式：

=OFFSET($B$5:$B$12,,$P$3)

5、数据源引用名称

选取饼图系列，把公式的倒数第2个参数替换为定义的名称

=SERIES(动态图表!$C$4,动态图表!$B$5:$B$12,动态图表!$C$5:$C$12,1)

修改为:

=SERIES(动态图表!$C$4,动态图表!$B$5:$B$12,动态图表.xlsx!季度,1)

6、调整格式

把所有文字和数字调整为合适大小，字体设置为微软雅黑字体

7、组合图表

按CTRL键分别选取图表、矩形和组合框控件，右键-组合。组合到一起后就可以一齐移动了。

好看又好用的动态图表制作完成

另：

肯定有同学说用数据透视图+切片器是不是更简单些？是简单，但表格需要进行转换，然后才能用数据透视图+切片器达到动态图表的效果。

表格转换演示：

按Alt+d后快速按P键会弹出数据透视表向导，然后添加区域，最后双击右下角的合计数会自动生成想要的格式表。

生成数据透视图

（格式调整不再详细演示）

Excel制作动态图表有很多种做法，方法不重要，重要的是最终展现的效果是不是你想要的。

正如小编所说，Excel方法其实不是很重要，重要的是通过技巧达到自己想要的效果和目的。如果想一劳永逸地偷懒，不如来系统学下专为财务人定制的Excel精通班课程。

EXCEL财务技巧零基础到精通【直播班】

（第12期2018年1月9日开课）

双旦优惠抢购价，

仅198元！

财务会计excel实战运用技巧

财务/金融/管理类人员必修课程

让excel减少你的工作负担

让excel提高你的工作效率

让新的一年老板对你刮目相看！