Excel图表中的条件格式化.doc

Excel图表中的条件格式化 标签： 条件格式 Excel图表 自定义格式 分类： Excel | 图表 2009-06-16 12:44图表中的条件格式化（文 / ExcelPro的图表博客）在PTS的高级图表培训课程中，有一个 Conditional formatting in charts 的内容，也就是如何在图表中应用条件格式。我们知道，excel中可以对单元格设置条件格式，但对图表却是没有这个功能的。PTS的技巧，其实也是模仿的条件格式罢了。今天这个日志整理一下这方面的技巧。1、对柱形图的柱子应用条件格式。这是最常见的图表条件格式需求和方法。比如我们用柱形图比较各分公司某一指标的完成情况，我们想对高于平均值（或计划进度等其他阀值）的柱子用绿色表示，低于平均值的柱子用红色表示。其制作思路是，a、根据条件判断，将原指标序列分离为高于平均和低于平均两个序列。b、用这两个序列做堆积柱形图，两个序列分别填充以不同的颜色。事实上，很多人为了做出如何设置互补色中正负数不同填充色的效果，就是运用的这个方法。此方法的应用还有：* 始终标出图表中的最大最小值，原理是从原序列分离出最大、最小值来作为辅助序列加入图表。* 标识图表中诸如周末等特定日期，原理是从原序列分离出周末的数据点来加入图表。以上方法同样适用于条形图、曲线图。2、对Y轴的数值标签应用条件格式。这个技巧利用数值的自定义格式，将Y轴的数值标签，高于某一值（如预算完成率，50）的显示为绿色，低于某一值的显示为红色。在 坐标轴格式 数字 自定义 类型，输入： 红色=50#,#0;蓝色50#,#0;G/通用格式其中50是我们设置的阀值，根据需要指定。这个技巧也可以用于图表中数据序列的数据标志，让每个数据点的数据标签显示为相应的颜色。3、对X轴的分类标签应用条件格式。X轴的分类标签本身无法设置条件格式，这一技巧不过是用辅助序列模拟罢了。下图中，高于平均线（或其他阀值）的X轴标签显示为绿色，低于的显示为红色。同前述1中的思路，a、根据条件判断，由原指标序列分离出两个辅助序列，只不过对应的数值均为0。b、用原序列做柱形图后，设置其x轴无分类标签。c、将这两个辅助序列加入图表，类型为曲线图，隐藏不可见。d、设置辅助序列的数据标志显示为类别名称，对齐为下方。两组数据标志模拟的x轴分类标签，对其分别设置不同的数据格式，实现条件格式化。我所知道的在图表中运用条件格式的做法就是这些了，是否还有其他形式的图表条件格式方法和应用，欢迎大家补充。现在，给出本博的互动话题3：下图中柱形图上的红绿色的上下箭头是如何做的？欢迎在评论中留下你的制作思路，或者发来你的制作文件。期待参与！20090617更新：bly3000朋友的做法，我做了些调整，以便动态看到条件变化演示，这里堆积柱形图显示百分比标签 标签： 堆积柱形图 百分比标签 xychartlabeler 分类： Excel | 图表 2009-11-14 21:27堆积柱形图显示百分比标签文 / ExcelPro的图表博客 一位网友在EH询问如下一个图表的做法，他的主要困惑是，这个堆积柱形图的数据标签是如何显示为百分比数据的。 从图中文字看，这个图表可能来自日本报刊，算是个有效的图表，并且有些新意。如果我们仔细观察这个图表，有几个地方值得注意： 堆积柱形图顶端显示了合计值，堆积部分显示了百分比数据，总量数据和构成数据都照顾到 百分比数据只有1个序列显示了%符号，其他的显示为整数，但读者也能看得明白，非常简洁 不使用图例，而是在堆积序列的对应位置显示标签，便于阅读 下面给出EP的做法，完成的图表效果如下。我们用一些图表技巧来完成这些特点，并提高作图效率。 首先，在原始数据的基础上，组织第1316行的百分比数据，用于标签显示。为使显示简洁，我们将百分比数据放大100倍，C13的公式为=C6/C$10*100，复制到其他单元格。 对第13行的数据使用自定义数据格式0.0%，使其带上%符号，作为百分比数据的提示。 然后开始作图。偷个懒，这里直接贴作图步骤的截屏图片，大家也可以从源文件中了解：补充说明： 小博的老读者应该对 xy chart labeler的应用很熟悉了，新读者可参考搞定散点图日志中的介绍。图表利器，必须掌握。 对于堆积柱形图，不使用图例而是在对应位置显示分类标签，极大提高了图表的可读性。若是手工使用自选图形绘制分类标签，繁琐且难于对齐，数据一变又得重来，简直不亚于绣花。我们使用辅助序列的方式，巧妙地实现了精确定位，效率极高。 散点图的数据组织技巧，G20的公式为：=SUM($G$6:G6)+G7*0.5，使分类标签显示在对应序列的垂直局中位置。F20的取值、以及误差量的大小，可根据最后柱形图的宽度调整至合适。 原图中将最大分类序列置于顶端，我们将其置于低端，这样图表显得平衡些。回顾一下，在本例的做法中我们运用了以下技巧： 1、用隐藏的曲线图显示汇总值，辅助序列的应用 2、堆积柱形图显示百分比标签，xy chart labeler的应用可记为“挂羊头卖狗肉”，现在天冷了，正是吃羊肉/狗肉的时候：） 3、只在1个序列显示%符号，自定义数据格式的应用 4、用隐藏的散点图显示分类标签，辅助序列的应用 5、自动显示小连线，误差线的应用6、辅助数据组织技巧实用性：制作难度：麦肯锡细分瀑布图 标签： 瀑布图 麦肯锡 分类： Excel | 图表 2009-12-30 12:53麦肯锡细分瀑布图文 /ExcelPro的图表博客在麦肯锡网站上看到如下的瀑布图。与一般瀑布图不同的是，它将首尾的柱子按分类项目进行细分显示，反映了首尾数据的构成。Excel中的实现方法：仍然是使用堆积柱形图，辅助序列占位形成悬浮效果。完成的效果是这样的：首先，在数据组织上需要考虑细分的形式，如下图。这是一种错列的排列方法，使不同分类项目的变化值显示在不同的柱子上。第13行的数据用于占位，其中I13的公式为：=$C$13-SUM($I8:I12)，复制到M13。然后开始作图，具体步骤截屏如下图。若比较看重麦肯锡在柱形图之间用阴影填充的效果（起到了连接比较的作用），可在数据组织上稍作变化：在原来的柱子之间插入辅助数据，取值等于左边柱子的高度即可。完成图表后，将辅助序列均填充为一个淡颜色，将柱形图连接起来便于比较。效果如下图。回顾一下，本例中用到的技巧： 数据组织技巧，通过错行错列形成分列和堆积效果 辅助序列占位形成悬浮效果 辅助序列做散点图显示分类标签这个方法在堆积柱形图显示百分比标签日志中也有类似运用 辅助序列做散点图显示堆积柱形图上的合计值 辅助数据做连接阴影效果实用性：制作难度：源文件下载：麦肯锡细分瀑布图基于地图选择的Excel动态图表（PPT演示版） 标签： 数据地图 动态图表 地图选择器 中国地图 分类： Excel | 图表 2010-02-24 13:00基于地图选择的Excel动态图表（PPT演示版）文 /ExcelPro的图表博客 在之前介绍的基于地图选择的动态图表，包括鼠标点击版、鼠标滑动版，都是在Excel中的动态演示。如果能在PPT演示中做出类似效果，一定非常激动人心。 要在PPT中演示动态图表，有一个方法是使用水晶易表，做出flash导入PPT中。简单，效果酷，但缺点是需要学习水晶易表，且该软件不易获得。最麻烦的是刷新数据时需要重新生成flash、重新导入PPT，对于日常工作并不实用。本日志介绍只用ExcelPPT的实现方法。 制作思路：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等）的对象（这是本步骤的主要目的），在右侧输入如下的代码：Private Sub CommandButton1_Click() 读取嵌入图表对象的名称，也为了启用slide对象 Dim i As Integer For i = 1 To Me.Shapes.Count If Me.Shapes(i).Type = msoEmbeddedOLEObject Then 为嵌入对象时，显示其名称 MsgBox 本页有 Me.Shapes.Count 个Shapes，第 i 个是嵌入图表对象，其名称是 Me.Shapes(i).Name End If Next iEnd Sub 现在放映PPT，点击按钮，宏会告诉我们嵌入对象的名称为“Object 4 ”。根据实际制作情况或软件版本，这个名称可能会不同，如本例在2010中为“对象 4”。 使用上面的做法是为了启用slide对象，并将后面的宏代码均放在这个对象中。而slide对象只有在插入控件对象时才会启用，我也不知道为什么。（通过将选取该嵌入对象的过程录制为宏，也可以知道它的名称。） 在本模型完成后，此按钮即没有用处了，可以删除或移至胶片显示区域之外的地方予以隐藏，以备后用。4、插入地图图形 借用之前地图工具中的图形，将其复制粘贴到PPT中，将其置于PPT页面左侧。粘贴前，请确认已在Excel中对图形进行命名，因为在PPT中我们也要利用到图形的名称。粘贴后，Excel中的命名在PPT中仍然有效。5、编写宏过程 若用户点击某省的图形，我们要在后台悄悄打开嵌入的xls文件，把省名填写到dashboard工作表中的A1单元格，这将触发动态图表模型自动切换图表的数据。编写的宏过程如下，非常简单：hubeiSub hubei_Click() Set Wb = Me.Shapes(Object 4).OLEFormat.Object Set Sh = Wb.worksheets(dashboard) Sh.Range(A1) = hubei 将选中图形的名称填入A1单元格End Sub 为其他省一一编写宏过程。这个过程仅需复制、粘贴、替换就可以了。6、为地图图形指定宏调用 选择某省的图形，如湖北，右键设置动作，进行以下设置：设置 单击鼠标下的运行宏，指定宏名为 ；勾选“单击时突出显示”；在鼠标移过下勾选“鼠标移过时突出显示”。 然后一一为其他省图形做相应的设置。不过，我们可以通过录制宏、修改代码，编制如下的宏代码，一次性批量完成指定宏的任务，省去繁琐的设置。Sub auto_add_run() 为本slide中的图形自动指定鼠标单击后的宏 Dim i As IntegerFor i = 1 To Me.Shapes.Count If Me.Shapes(i).Type = 5 Then 指定鼠标点击的动作 With Me.Shapes(i).ActionSettings(ppMouseClick) .Run = Slide1. Me.Shapes(i).Name Slide1为本页的名称 .Action = ppActionRunMacro .SoundEffect.Type = ppSoundNone .AnimateAction = msoTrue End With 指定鼠标移过的动作 With Me.Shapes(i).ActionSettings(ppMouseOver) .Action = ppActionNone .SoundEffect.Type = ppSoundNone .AnimateAction = msoTrue End With End If Next iEnd Sub7、测试模型 现在，激动人心的时候到了。进入PPT放映状态，用鼠标在地图上移动，地图会突出显示当前鼠标所在省的图形；若单击某省图形，右侧的图表就会切换为对应省份的数据！大功告成。8、其他注意事项： 首次单击某省图形时，由于后台要打开Excel程序，所以响应速度会稍慢。之后再选择其他省图形则没有此问题了。 在放映一次后再放映时，可能会出现点击无反应的不稳定情况。这时可单击一下Reset按钮，即可解决。 要更新嵌入文件中的数据或图表时，不要直接双击该对象，这样编辑窗口会非常小，不便于操作。可选中该对象后，单击右键工作表对象打开，将打开一个名为“工作表 在 xxx PPT”的正常Excel窗口供你编辑。编辑完保存并关闭该Excel窗口即可。 本文件在office2003中制作，在2010中需要在第5步对应修改嵌入对象的名称。我们也可以将嵌入对象更改一个非默认的名称，使之适用于20032010，此问题本文不谈。 美中不足的是2010中鼠标移动的突显效果不再是突出填色，而是显示一个矩形框，没有2003中效果好。 本日志参考资料：/dispbbs.asp?boardID=92ID=61867page=1。个别地方我也是知其然不知其所以然，若有可以改进的地方，望精通VBA的朋友能指出。实用性：用气泡图可视化表格 标签： 气泡图 表格 可视化 分类： Excel | 图表 2010-03-08 11:31用气泡图可视化表格文 /ExcelPro的图表博客 一位读者朋友来信询问，如何制作下图所示的图表。 这个图表在表格的基础上，以气泡图来可视化数值的大小。它来源于 Me, myself and BI 博客的一篇日志（这里），作者讨论了使用气泡图、条形图、微线图等形式可视化表格的做法。作者认为：Circles can be compared in two directions, vertically and horizontally or by rows and columns. 就是说使用气泡图便于在行、列两个方向同时进行比较，比较有道理。 我认可、也喜欢条形图的形式，也可以接受气泡图的形式。就这个表格而言，气泡图也清晰地告诉我哪里大哪里小了。不知道大家是何看法？ 这篇日志给出我考虑的气泡图形式的实现方法。 思路：Excel20032010中都不支持对表格进行汽泡图式的可视化。还记得令人耳目一新的表格日志中介绍的做法吗，这里我们还是同样的思路，只不过是把一个气泡图“放”在表格的上方、对齐，简单吧？ 下图是图表的完成效果，右侧为辅助数据。作图步骤：1、组织辅助数据。 x数据控制气泡在水平方向的位置，有4列，分别对应表格中的4栏；y数据控制气泡在垂直方向的位置；第N列的z数据是为了便于理解，临时放置的数据，最后的图表并不使用它。2、制作气泡图。 首先用L、M、N列的数据制作一个气泡图，这时在x轴的刻度4上出现一连串垂直的气泡图。选中该序列，根据表格行高，设置缩放气泡为40%左右。 然后，通过数据源对话框添加3个序列，这样图表共有4个序列。对这4个序列一一指定x、y、z值的引用位置，例如序列4（对应2008年数据）的引用设置如下图： 这时已经得到一个矩阵式的气泡图。去除图表的所有非数据元素，只留下气泡，设置图表透明，设置气泡的填充色。3、将气泡图放置到表格之上，并对齐。 这里的技巧是，不要直接用鼠标选中图表对象，而是先选取绘图工具栏上的一个鼠标箭头按钮，用它选取图表对象，拖动到表格上，并调整大小、位置到与表格匹配。这样做的时候，图表对象是透明的，便于观察与表格的对齐情况，操作很方便，否则的话会有些困难。 在2007和2010中，很多人找不到这个鼠标箭头按钮了，因为它不在绘图工具栏中了跑到了“开始编辑查找和选择选择对象”，一般人我不告诉他，呵呵。由于做图表经常要选区对象，建议将该按钮加入到快速访问工具栏。 下面这个图片，也是老外认为用气泡进行可视化不及条形图有效的例子。 要用条形图可视化表格，有以下方式： 1、在2007/2010中，可使用数据条。但数据条的宽度无法设置，我不喜欢这么宽的数据条； 2、在2003中没有数据条，可使用放置在表格上的条形图，其实这个做法非常灵活，有时比数据条还好用； 3、对大数据量、临时分析，可使用REPT函数模拟条形图； 4、使用sparklines插件。 由于2007/2010的数据条是从数据所在单元格的左侧到右侧，显示的效果并不好，如下图。 这时，我们可以将原数据复制链接到次列，在次列显示数据条，并将该列的数据隐藏，隐藏的方法是设置自定义格式为;。效果如下图。实用性：制作难度：文件下载：气泡图可视化的表格我的相关日志：2008-10-31|表格中的横向瀑布图2008-05-27|将表格图表化，让读者耳目一新（本文由ExcelPro的图表博客原创，转载请注明出处。ExcelPro的图表博客探讨专业有效的商务图表沟通之道，欢迎交流讨论！）给excel图表绘制个性化的数据标签 标签： 数据标签 数据标志 分类： Excel | 图表 2009-07-09 12:54给图表绘制个性化的数据“标签”（文 / ExcelPro的图表博客）上篇日志始终标记图表中的最大最小值 中，对例图中的个性化数据标签没有好的做法，乐土朋友给出了一个思路，就是利用图片去填充数据标签。今天试了试，果然可行。在PPT中用自选图形绘制的对象，可以方便的另存为图片。注意，由于excel的数据标签的高度比较高，显得多余，我们除了绘制一个箭头外，还要绘制一个比箭头略高的白色或透明矩形框，放在箭头下面，将二者组合，然后另存为图片。这样填充后箭头的高度将刚好与数字的高度相当。在做好的图表中，对其数据标签用刚保存的图片进行填充。为让标签中的数字往后退一点，可以设置其数据格式为： 0.0，即前面空1或2格，把箭头尖尖的位置空出来。有了这个思路，我们就可以做出很多个性化的数据标签，就看你的创意了。上图例子特别适合于需要强调最大最小值的情况。谢谢乐土朋友！优化簇状趋势比较 标签： 簇状图 小又多 平板图 分类： Excel | 图表 2009-07-15 21:39优化簇状趋势比较（文 / ExcelPro的图表博客）类似下图左边的数据表要做图表，一般都会做成右边的图表样子，这在实际工作中是非常常见的。数据产生在行或在列，表现为用公司做x轴，或者用年份做x轴。1、如用公司做x轴，图中着色是不妥当的，同一个公司的3年应使用相同的颜色，而公司之间倒可以用颜色区分，但这时要调整颜色却很繁琐。2、如用年份做x轴，则同一公司3年的图形又相距稍远，不利于阅读。看到商周下图的处理，相当于在一个图表中做了3个小图表，有人称作平板图，其实类似于以前说到的小又多图表，只不过做在一个图表中，阅读和比较都比较方便。其做法，在于数据组织技巧。我们将数据稍做调整，形成下图左边的作图数据，再做堆积柱形图即可。23、27行的留空，使3组数据之间分开来，显得清晰易读。上图使用了图例，仍需要阅读者目光移动来确认3组柱子都是谁，进一步优化至下图，阅读更方便。我们只是将作图数据包括了B列，使用双层的分类标签而已，如果显示x轴的线条，其实是下图这样的。还可以使用其他方法，将图表做成如下样式，就是一个很标准的平板图了。具体做法可以参见附件,包括所有做法在里面。这种做法适用于3组数据量纲相同、数量级相当，否则用小又多方式要好。最后，再给个商周上的类似例子参考。欢迎讨论你的看法。一份人力资源总览图的制作 标签： 人力资源图表 条形图 辅助序列 占位 分类： Excel | 图表 2009-07-20 12:59一份人力资源总览图的制作（文 / ExcelPro的图表博客）应朋友要求，周日在绿树知了咖啡HR聚会上做一个商务图表的分享，具体情况见Niko的笔记。因为参加的朋友都是做人力资源相关工作的，之前特意在网上搜索人力资源图表，还真发现一个不错的图表，就做了个实现案例。分析：这个图的主体是一个堆积的条形图，看到的有4组数据，其他的空白地方其实是隐藏的辅助序列，升降职的再用辅助序列加入。我完成的例子是这样的：实现方法说明：1、准备数据如下图，CN列一一与图表中对应，具体公式可参考附件设置。2、用BN列数据做堆积条形图。3、隐藏辅助的序列，图表的雏形就出来了。4、逐一显示各序列的数据标签。其中招聘人数、退出人数序列的数据标签，包括人数和比例，分别借用了它左右两边的辅助序列来显示，可谓“挂羊头卖狗肉”：），需要用到XY chart laber工具。5、组织升岗、降岗人数的数据，将以散点图方式加入图表。6、将两组xy数据分别加入图表，设置其标签分别为V和AA列。此步骤稍为复杂，可参考源文件仔细琢磨。7、隐藏散点图序列，给招聘、退出序列贴入箭头图案，去除不需要的坐标轴等，如下图。8、利用单元格做标题等，可至本文开头的图表样式。用到的技巧：数据组织，隐藏序列占位，改变数据序列的标签，复合图表，贴入图形（箭头），利用单元格，对齐单元格，单元格隔行填色，等等。源文件下载：这里，包括详细步骤说明，人力资源的朋友可以直接使用哦。SAS里面除了变量，还有宏变量，其用途也非常广泛。创建宏变量的方法最早有shiyiming总结，翻了翻Rick Aster的Professional SAS Programming Shortcuts Over 1,000 Ways To Improve Your SAS Programs，发现里面并没有总结这个问题，有点失望。这里转载并补充姚志勇的SAS书里面的内容，使得更加完整和充实，便于大家以后方便选择使用。1、通过直接赋值或通过宏函数创建宏变量 最基本最常用的%let mv = 100;%let dsid=%sysfunc(open(sashelp.class);%let nvars=%sysfunc(attrn(dsid,nvars);%let nobs=%sysfunc(attrn(dsid,nobs);%let dsid=%sysfunc(close(dsid);%put nvars.;%put nobs.;2、通过data步接口子程序call symputx与call symput（两者有区别）a, 创建单个宏变量data _null_;x = 100;call symput(x, x);run;data _null_;set sashelp.class nobs=obs;call symputx(m1,obs);call symput(m2,obs);Stop;run;%put m1.;%put m2.;b, 为某变量的每个值创建一个宏变量data _null_;set sashelp.class;suffix=put(_n_,5.);call symput(cats(Name,suffix), Name);run;c, 为表的每个值创建一个宏变量data _null_;set sashelp.class;suffix=put(_n_,5.);array xxx* _numeric_;do i =1 to dim(xxx);call symput(cats(vname(xxx),suffix),xxx);end;array yyy* $ _character_;do i =1 to dim(yyy);call symput(cats(vname(yyy),suffix),yyy);end;run;3、proc sql方法 这个用法有很多的灵活性a. 通过SQL过程用变量值创建一个宏变量proc sql noprint;select distinct sexinto :list_a separated by from sashelp.class;quit;%put list_a.;b.通过SQL过程创建多个宏变量proc sql noprint;select nvar,nobsinto:nvar , :