自动排序的表格式图例

mxbj

自动排序的表格式图例
文/ExcelPro的图表博客 http://excelpro.blog.sohu.com/269353425.html

在最近的华尔街日报上看到一个图表案例。

其显著特点是，在右侧图例的位置上不是通常的图例，而是一个起到图例作用的表格，或者说是一种表格式的图例。并且：
表格中各项目的顺序，与各曲线最后一个数据点的大小顺序一致，填充色也与曲线图一致，这种对应增强了阅读体验。
表格中提供了各序列的变化幅度，图表表达的信息更加丰富，方便了读者。
既然是表格式的图例，当然要利用单元格来做。如果手动进行格式化设置，固然可以，但如果数据刷新了，又得辛苦一番重新设置。因此，需要找到智能化自动一致的办法。关键在于准备图例的数据。


1、准备图例数据。
P列，填上1~4的序号，便于后面公式引用。
Q列，=LARGE($N$7:$N$10,P14)，按从大到小的顺序，取出各序列最后一个数据点的值。自动排序就是在这里实现的。
T列，=INDEX($B$7:$B$10,MATCH(Q14,$N$7:$N$10,0))，根据这些数据点的值，查出其所属序列的名称（这里认为没有相同值）。
R列，=INDEX($C$7:$C$10,MATCH(Q14,$N$7:$N$10,0))，同上，查出其01年时的值。
U列，=Q14/R14-1，计算各序列从2001年到2012年的变化幅度。并且对其使用自定义格式：+0.0%;-0.0%;0.0，使变化幅度前带上正负符号。
S列，=MATCH(Q14,$N$7:$N$10,0)，根据Q列或T列，查出该序列在原数据区域的顺序。这个顺序，会是插入曲线图时默认配色的顺序，我们将利用这个顺序来设置图例表格的填充色。


2、做曲线图。
用原数据区域做曲线图，此时各序列的线条色是依颜色面板中第一行第5个格子起往后的顺序自动配色的，不要去改动它。


3、为T14:U17设置条件格式，也就是表格式图例的智能填充色。
选中T14:U17区域，依次添加4个条件格式规则：
当=$S4=1，填充色为颜色面板第1行第5个格子
当=$S4=2，填充色为颜色面板第1行第6个格子
当=$S4=3，填充色为颜色面板第1行第7个格子
当=$S4=4，填充色为颜色面板第1行第8个格子

这里需要对照条件格式效果，好好体会“=$S4=1”中绝对相对地址的作用，这是本例做法的关键。


4、拍照图例。
将T13:U17区域拍照，设置拍照图片无边框线，放置到曲线图的右侧位置，并对齐到例图样式。必要时可将其组合起来。
现在试着改变原数据取值，测试确认表格式图例能正确地排序和着色，非常智能。
如果你在页面布局中更改颜色主题，曲线和图例的颜色都将同步变化且保持一致！这就是在2007以上直接使用颜色面板中格子配色的好处。


本例涉及知识点：
large函数，index+match函数组合，条件格式，绝对相对地址原理，自定义格式，拍照。


范例下载：
Hi，推荐文件给你 "自动排序的表格式图例.xlsx" http://vdisk.weibo.com/s/HtIeD

《用地图说话-在商业分析与演示中运用Excel数据地图》★★★★★
京东：http://t.cn/SXnjCR
卓越：http://t.cn/SXnCQH
当当：http://t.cn/SXnOMW
电子：http://t.cn/z0dQzx7
互动：http://t.cn/SXDb8U
淘宝：http://t.cn/SXDLjS
想成为锐利商务人士的你，怎可错过这本书？！