Excel实战：自动排序的表格式图例

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

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

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函数组合，条件格式，绝对相对地址原理，自定义格式，拍照。

作者：ExcelPro