excel图表学习.docx

excel图表学习.docx

《excel图表学习.docx》由会员分享，可在线阅读，更多相关《excel图表学习.docx（48页珍藏版）》请在冰豆网上搜索。

excel图表学习

商业杂志图表的经典用色

1、《经济学人》常用的藏青色

经济学人上的图表，基本只用这一个颜色，或加上一些深浅明暗变化，再就是左上角的小红块，成为经济学人图表的招牌样式。

罗兰贝格也非常爱用这个色，有时也配合橙色使用。

各类提供专业服务的网站也多爱用此色。

更新20081031：

前几天看到一个色卡图片，把这个颜色叫藏青色应该是错误，应该是深青色、水鸭色或者孔雀蓝之类的。

本人对之前的以讹传讹表示抱歉，纯属色盲。

风格就是这样，即使很单调，只要你坚持，也会成为自己的风格，别人也会认同。

所谓以不变应万变，变得太多反而难以把握。

2、《商业周刊》常用的蓝红组合

早年的商业周刊上的图表，几乎都使用这个颜色组合，基本成为商业周刊图表的招牌标志了，应该是来源于其VI系统。

不过今年来好像很有些变化，更加轻快明亮。

3、《华尔街日报》常用的黑白灰

HSJ是一份报纸，所以图表多是黑白的，但就是这种黑白灰的组合，做出的图表仍然可以非常专业，配色也非常容易。

4、使用同一颜色的不同深浅

如果既想使用彩色，又不知道配色理论，可在一个图表内使用同一颜色的不同深浅/明暗。

这种方法可以让我们使用丰富的颜色，配色难度也不高，是一种很保险的方法，不会出大问题。

当然，最深/最亮的要用于最需要突出的序列。

5、《FOCUS》常用的一组色

这组颜色似乎是从组织的LOGO而来，比较亮丽明快，也不错。

6、设计师珍藏自用颜色：

橙＋灰

我发现，设计师们总喜好把这个颜色组合用于自己的宣传，似乎这样能体现设计师的专业性。

如Inmagine、Nordrio的LOGO就是这样。

7、暗红＋灰组合

这种红＋灰的组合给人很专业的印象，也经常出现在财经杂志上。

8、橙＋绿组合

这种橙＋绿的组合比较亮丽明快，充满活力，也经常出现在财经杂志上。

9、黑底图表

最为强烈的黑白对比，总是显得比较专业、高贵，黑底的图表其特点非常明显，但不要学麦肯锡的那一套，有些刺眼，也被它用滥了。

以上杂志图表案例用色，均为本人比较欣赏的类型。

其用色的RGB值，均为本人使用取色器从图表案例上拾取（够辛苦的哈，请给个支持），不一定完全准确，实际运用也可以有些微变化，效果还是差不多的。

通过这些专业的颜色组合，使我们的图表可以给读者的第一眼就留下专业的印象，将我们的专业与可信任传递给他们。

你还曾看到哪些令人印象深刻、彰显专业精神的图表用色呢？

告诉我，我会整理进来，与大家一起分享。

也希望有对色彩比较熟悉的朋友进行指导和补充！

[Excel图表]搞定散点图

、散点图的应用情景

1）相关性表现，看变量y与x之间是否存在相关性的变化趋势。

2）矩阵分析，看各分析对象在两个维度交叉的格子上如何分布，如：

      重要性－满意度矩阵，可分析哪些因素应优先解决。

      arpu值－增长率矩阵，可分析哪些分公司流失严重且影响大。

2、散点图的做法要点

   Excel提供了散点图类型，但一般初学者很难顺利做出想要的散点图，不知道微软为什么让一个散点图这么难做，2007版本也没有解决这个问题。

excel中做散点图需要注意两个要点：

   首先，做散点图时，与做其他图表不同的是，选择源数据范围时不能包含标签列，否则无法顺利做出。

如上图中，要仅选中xy列的填色区域，然后点击图表按钮，才可顺利出现散点图。

   其次，Excel无法让散点图的各个点的标签显示为该点的名称（标签列的文字），在数据标志里的几个选项均无法做到，很多人只好手动标上文字，很是麻烦，真不知道微软那些工程师怎么想的，SPSS中做散点图就很方便，显示的直接就是对应的名称。

解决办法：

   1）如果散点不多，如少于7个，可手动链接标签。

先设置数据标志显示系列名称，这时每个标签都显示一样的值，可选中一个标签，然后将数鼠标定位到公式输入栏，输入＝，鼠标点击对应数据点的标签名字的单元格，此时数据标签即链接到该单元格的值，显示正确。

逐一对其他标签作类似处理即可。

   2）如散点比较多，不宜手动修改，可使用工具宏。

下面提供了两个工具宏的地址，实是数据分析人员的必备工具之一，都可完成这一任务，笔者一般使用的是后者。

   做法：

先选中散点数据序列，点击标签修改宏，（如用后者是：

菜单－》工具－》宏－》宏，在宏名中选中标签修改的宏，执行），出现对话框要求输入标签引用的区域，用鼠标指定名称的区域即可。

这是最好的方法，非常方便灵活，很多地方都经常要用到，推荐使用。

3、添加拟合线

   在利用散点图做相关分析时，可以添加拟合线。

选中图表－》右键－》添加趋势线，一般使用线性即可，如果不是统计专业人士，尽量不要使用其他函数。

一般同时显示回归方程和R平方。

   顺便说明，R是相关系数，R平方是决定系数，例中的意思是x的变化可以解释y56.5％的变化。

没有统计书明确说R平方多少以上才可以，我觉得0.7以上比较好，当然要先看回归方程的显著性是否达到要求，p值<0.5。

扯远了。

4、两组数据的散点图

   以上方法做出的是一组数据的散点图，有时候需要做包含多组数据的散点图，如果按常规经验选中多组数据做图，并不能做出想要的散点图。

要做包含多组数据的散点图，方法是，先完成一组数据的散点图，然后选中图表－》右键－》源数据－》系列－》添加，分别指定另一组数据的x和y的引用位置，即可将其加入图表，两组数据可以分别格式化，如设置不同的形状、颜色。

5、象限图

   将散点图扩展，可形成象限图、矩阵图，特征是绘图区被划分为4个象限。

实现方法有3种：

  1）设置坐标交叉。

分别在x、y轴的格式－》刻度－》交叉于输入框，填入需要的刻度，使xy轴相交在图表中间的位置。

缺点是无法对象限分别填色。

下图。

（此外，还可以用辅助数据的误差线来画象限，这里不整理了。

）

   2）用带象限的图片填充绘图区。

绘制一个有4个象限的图片，将其填充到图表的绘图区中，则实现了象限的绘制，比较巧妙。

下图。

   3）用辅助的堆积柱形图模拟象限。

将2×2的数据加入图表，设置图表类型为堆积柱形图，设在次x、次y轴，设置坐标刻度和柱子间距，使之成矩阵。

实现方式也比较巧妙。

下图。

6、波士顿矩阵图，气泡图

   第5点的后两种方法，可进一步扩展到绘制9个格子的波士顿矩阵图。

使用气泡图替代散点图，则可在矩阵图上表现另一个度量，如规模。

7、杂志散点图案例

   下面给出几个商业杂志上的散点图、矩阵图的例子，以作参考。

风险矩阵分析图

一位读者来信询问，如何制作如下的图表。

   Google了一下，这是一种风险分析矩阵图，管理学上用来分析各风险因素的可能性和可能导致的影响。

经常表现为如下的定性分析形式，使用单元格或自选图形绘制即可。

   若对风险的可能性和影响进行了定量估计，可采用散点图来制作这个矩阵图，这个应该不成问题。

为了绘制一条风险承受线，可添加一个辅助的数据序列来做。

如下图。

（关于如何绘制散点图，如何让散点图的标签引用风险名称，如何添加辅助系列来作图，可参见《Excel图表之道》一书中的相关章节内容，这里不再细述。

）

   该读者还提出了进一步的要求，希望用气泡的大小来反映风险值，风险值=可能性*影响。

这时则可以采用气泡图来做这个矩阵图，做法与散点图类似。

   需要注意的是，由于Excel不容许气泡图与其他图表类型混合，因此无法再用散点图来辅助绘制风险承受线。

这时可用另一组辅助数据来绘制一个气泡图，然后利用其误差线来绘制风险承受线。

最后将辅助序列的气泡图设置无填充色、无边框线，隐藏起来。

注意辅助数据的z数据不能为0，否则无法做误差线。

   该读者还有一个要求，希望用气泡图的填色深浅来反映其风险可管控程度。

这时可对气泡图的各个气泡，根据其风险可管控程度数值大小逐一填色，然后再增加一个图例说明。

至于如何用vba来做这个过程，我也不会，有兴趣的读者可自己研究，不过我觉得已没有必要了。

利用条件格式色阶制作数据地图

 在2007版本以后，Excel对条件格式进行了增强，提供了数据条、色阶、图标3种可视化形式。

其中，色阶就是一种heatmap，用填充色的深浅代表单元格数值的大小。

我们可否利用这种色阶来制作一个数据地图呢？

   制作思路：

对数据源设置色阶条件格式，将条件格式所产生的填充效果，对应填充到相应的地图图形。

但条件格式所生成的填充背景色，似乎还无法用vba读出，所以考虑手工转换一下。

（如有读者朋友知道如何获知条件格式的背景色，万望告知）

   整个模型如下图所示，因要利用色阶，所以需要基于2007或2010版本。

   准备工作同以往的数据地图做法，地图图形按B列的省份名称命名。

新读者要了解如何制作可编辑的地图图形，如何为图形命名，请参见《Excel图表之道》一书的数据地图一节内容，或本博客中的相关帖子。

   制作步骤：

1、对数据源设置色阶条件格式。

   选中源数据B11：

B41区域，并同时选中E11：

E20（此区域将作为图例，下面再说），在“开始->条件格式->色阶”，选择相应的色阶样式，即可在选中区域中生产heatmap的填色效果。

对色阶的样式，可以在“条件格式->管理规则”中作进一步的自定义设置，如最大值、最小值以及相应的填充色。

   E11：

E20区域是为地图准备的图例，分别提取了原数据的最小值、最大值以及9个分档值，可看具体的公式设置。

也可根据需要，手工输入最大最小值，来控制颜色。

与源数据、区域一起应用条件格式后，生成的效果正好像个图例，后面会引用到地图上作为图例。

该区域还使用了自定义格式;;;的技巧，来隐藏数值的显示。

2、将条件格式复制为单元格格式。

   经过反复测试，我还是没有找到如何用vba读出条件格式所生成的单元格填充色，所以只好采用手工的方法。

如有知道方法的读者朋友，望告知我，谢谢。

   我们要将源数据B11：

B41的填充色效果，复制到H列的相应位置，成为vba可以读取的静态格式。

要将条件格式产生的填充效果复制为静态的单元格格式，直接复制粘贴或选择性粘贴都无法实现，需要一种特别的操作方法，如下：

1）选中B11：

B41区域，ctrl+c复制；

2）将鼠标定位到需要粘贴到的目标区域的第一个格子，这里是H11；

3）点击“剪贴板”功能组右下角的箭头，打开“剪贴板”任务窗格；

4）选择最上面的刚才复制项目右侧的下拉框，点击“粘贴”。

   这时，H列的格式与B列完全一样，但条件格式已被转换为单元格格式了，我们通过vba可以读出其填充色。

（另一个奇怪的事，录制以上操作所产生的宏，无法插入下面的代码运行？

不知为何，望知道原因的读者告知）

3、为地图图形填色。

   编写一小段宏，用H列的填充色，填充对应省份的地图图形。

代码如下，其实只有1行：

Subfill_color（）

   Fori=11To41 '为数据源的起始和结束行号

     ActiveSheet.Shapes（Range（"DataMap!

B"&i）.Value）.Fill.ForeColor.RGB=Range（"DataMap!

H"&i）.Interior.Color

     '对各省的图形使用复制后的H列单元格的颜色填充

   Nexti

EndSub

   将这个宏指定给一个矩形框，作为一个填色按钮。

点击按钮，各省地图图形已被填充对应的颜色，与B列的填充色一致，一个heatmap的地图已经生成。

4、整合地图图形及图例，便于导出。

   原始的地图图形在U11:

AB31区域，将E11：

E20拍照引用到U列处作为图例，再将U11:

AB31拍照引用到L11处，作为最后完工的地图，以后导出地图只需要复制这个拍照对象即可。

新读者要了解拍照操作，可参见《Excel图表之道》一书的“照相机”一节内容。

   这个模型建立完成后，下次使用只需要如下操作，即可生成一个专业的heatmap数据地图：

1、填入数据；

2、设置条件格式；

3、复制条件格式；

4、生成地图；

5、复制导出地图。

优点：

   1、可以充分利用Excel2007条件格式的特性，灵活设置出自己想要的各种heatmap效果，如单色渐变、双色渐变，操作也很方便。

   2、色阶是无级渐变的，精确对应数值大小。

也省去了设置分档的麻烦。

缺点：

   1、需要一个手工环节，将条件格式复制为单元格背景。

如何将这个步骤加入后面的宏代码而自动化？

有无更好的改进方法，欢迎大家讨论。

基于地图选择的Excel动态图表（PPT演示版）

在之前介绍的基于地图选择的动态图表，包括鼠标点击版、鼠标滑动版，都是在Excel中的动态演示。

如果能在PPT演示中做出类似效果，一定非常激动人心。

   要在PPT中演示动态图表，有一个方法是使用水晶易表，做出flash导入PPT中。

简单，效果酷，但缺点是需要学习水晶易表，且该软件不易获得。

最麻烦的是刷新数据时需要重新生成flash、重新导入PPT，对于日常工作并不实用。

本日志介绍只用Excel＋PPT的实现方法。

   制作思路：

PPT中的自选图形可以设置动作，如当用户单击时运行一个宏。

我们通过编写一小段宏，在用点击某省图形时，将对应的区域名称填入到嵌入在PPT中的工作表，工作表中的动态图表模型就会自动切换数据，实现动态交互效果。

完成的模型效果如下图。

制作步骤：

1、制作Excel中的动态图表模型

   这个比较容易。

一个简单的动态图表模型如下，使用数据有效性和Vlookup函数构造。

A2的数据有效性引用为A5：

A36，B2：

N2使用Vlookup函数根据A2的值，从$A$5:

$N$36区域查找并返回对应的数据。

图表以B2：

N2为数据源，若A2变化，B2：

N2则变化，那么图表也就变化。

   本例中，考虑到会有多个动态图表，data1、data2等的A2均引用dashboard的A1的值，而dashboard的A1的值将由用户的选择填充值。

   建议将图表放在单独的dashboard工作表中，避免后续更新数据时影响PPT中的显示。

为避免宏提示，这个Excel中不要包含宏。

2、将Excel文件嵌入PPT

   在Excel文件中，复制包含图表对象的单元格区域，转到PPT中，插入一个空页，菜单编辑－选择性粘贴－工作表对象，即将该Excel文件嵌入到PPT中，并显示为所复制单元格区域的内容。

将其置于PPT页面右侧。

   注意，嵌入后，PPT中的显示将与原文件再无关系，一切修改都要在嵌入文件中进行。

因此，嵌入前应考虑充分，尽量把Excel文件做完善，避免后续修改麻烦。

3、读出嵌入对象的名称

   由于我们要操作这个嵌入对象，因此需要先知道它的名称。

   在视图－工具栏－控件工具箱，在PPT中绘制一个按钮对象，双击，进入VBA界面。

左侧会出现名为slide1（或slide2等）的对象（这是本步骤的主要目的），在右侧输入如下的代码：

PrivateSubCommandButton1_Click（）'读取嵌入图表对象的名称，也为了启用slide对象

  DimiAsInteger

  Fori=1ToMe.Shapes.Count

    IfMe.Shapes（i）.Type=msoEmbeddedOLEObjectThen '为嵌入对象时，显示其名称

      MsgBox"本页有"&Me.Shapes.Count&"个Shapes，第"&i&"个是嵌入图表对象，其名称是"&Me.Shapes（i）.Name

    EndIf

  Nexti

EndSub

   现在放映PPT，点击按钮，宏会告诉我们嵌入对象的名称为“Object4”。

根据实际制作情况或软件版本，这个名称可能会不同，如本例在2010中为“对象4”。

   使用上面的做法是为了启用slide对象，并将后面的宏代码均放在这个对象中。

而slide对象只有在插入控件对象时才会启用，我也不知道为什么。

（通过将选取该嵌入对象的过程录制为宏，也可以知道它的名称。

）

   在本模型完成后，此按钮即没有用处了，可以删除或移至胶片显示区域之外的地方予以隐藏，以备后用。

4、插入地图图形

   借用之前地图工具中的图形，将其复制粘贴到PPT中，将其置于PPT页面左侧。

粘贴前，请确认已在Excel中对图形进行命名，因为在PPT中我们也要利用到图形的名称。

粘贴后，Excel中的命名在PPT中仍然有效。

5、编写宏过程

   若用户点击某省的图形，我们要在后台悄悄打开嵌入的xls文件，把省名填写到dashboard工作表中的A1单元格，这将触发动态图表模型自动切换图表的数据。

编写的宏过程如下，非常简单：

'hubei

Subhubei_Click（）

   SetWb=Me.Shapes（"Object4"）.OLEFormat.Object

   SetSh=Wb.worksheets（"dashboard"）

   Sh.Range（"A1"）="hubei" '将选中图形的名称填入A1单元格

EndSub

   为其他省一一编写宏过程。

这个过程仅需复制、粘贴、替换就可以了。

6、为地图图形指定宏调用

   选择某省的图形，如湖北，右键－设置动作，进行以下设置：

设置单击鼠标下的运行宏，指定宏名为  ；勾选“单击时突出显示”；在鼠标移过下勾选“鼠标移过时突出显示”。

   然后一一为其他省图形做相应的设置。

不过，我们可以通过录制宏、修改代码，编制如下的宏代码，一次性批量完成指定宏的任务，省去繁琐的设置。

Subauto_add_run（）'为本slide中的图形自动指定鼠标单击后的宏

  DimiAsInteger

   Fori=1ToMe.Shapes.Count

    IfMe.Shapes（i）.Type=5Then'

      '指定鼠标点击的动作

      WithMe.Shapes（i）.ActionSettings（ppMouseClick）

        .Run="Slide1."&Me.Shapes（i）.Name 'Slide1为本页的名称

        .Action=ppActionRunMacro

        .SoundEffect.Type=ppSoundNone

        .AnimateAction=msoTrue

      EndWith

      '指定鼠标移过的动作

      WithMe.Shapes（i）.ActionSettings（ppMouseOver）

        .Action=ppActionNone

        .SoundEffect.Type=ppSoundNone

        .AnimateAction=msoTrue

      EndWith

    EndIf

  Nexti

EndSub

7、测试模型

   现在，激动人心的时候到了。

进入PPT放映状态，用鼠标在地图上移动，地图会突出显示当前鼠标所在省的图形；若单击某省图形，右侧的图表就会切换为对应省份的数据！

大功告成。

8、其他注意事项：

∙首次单击某省图形时，由于后台要打开Excel程序，所以响应速度会稍慢。

之后再选择其他省图形则没有此问题了。

∙在放映一次后再放映时，可能会出现点击无反应的不稳定情况。

这时可单击一下Reset按钮，即可解决。

∙要更新嵌入文件中的数据或图表时，不要直接双击该对象，这样编辑窗口会非常小，不便于操作。

可选中该对象后，单击右键－工作表对象－打开，将打开一个名为“工作表在xxxPPT”的正常Excel窗口供你编辑。

编辑完保存并关闭该Excel窗口即可。

∙本文件在office2003中制作，在2010中需要在第5步对应修改嵌入对象的名称。

我们也可以将嵌入对象更改一个非默认的名称，使之适用于2003－2010，此问题本文不谈。

∙美中不足的是2010中鼠标移动的突显效果不再是突出填色，而是显示一个矩形框，没有2003中效果好。

∙本日志参考资料：

基于地图选择的Excel动态图表

在《华尔街日报》这样的财经网站，我们经常会看到一种通过地图选择的交互式图表。

当读者用鼠标在地图上选择某个地区，右侧的图表就会自动切换为该地区的数据。

这种做法既可分析大量数据，又提供了地理位置信息，且交互性强，非常具有吸引力。

我们可以利用热力地图中的图形和动态图表技巧，在Excel中实现这种效果。

   作图思路：

地图由一个个自选图形绘制而成，读者点击某个地区的图形，会触发一个宏过程，这个宏会记录下用户选择的地区名，并修改图形的填充效果。

动态图表模型则会根据宏所记录的地区名变换图表的数据，产生动态交互的效果。

完成的模型如图1所示。

 图1基于地图选择的动态图表模型。

1.准备地图图形

   还是利用热力地图做法中的地图图形，并且采用相同的命名方法。

将地图图形放置在名为dashboard的工作表中。

2.编写公共宏过程

   单击菜单“工具→宏→VisualBasic编辑器”（或Alt+F11键），进入如图2界面。

图2用户选择后调用的公共宏过程。

在左侧窗口中选择ThisWorkbook，在右侧窗口中输入如下代码：

--------------------------------------------------------------------------

Subuser_click（region_name）

  ActiveSheet.Shapes（Range（"A1"）.Value）.Fill.ForeColor.SchemeColor=48

'1、取A1单元格值，将上次选择的地图版块填充黄色，即还原填充色

   Range（"A1"）.Value=region_name

'2、将当前选择的地图版块名称填写到A1

   ActiveSheet.Shapes（region_name）.Fill.ForeColor.SchemeColor=52

   '3、将当前选择的地图版块填充红色

E