免费试用
banner

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

作者: afenxi来源: afenxi时间:2017-02-25 09:42:270

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

Excel实战:自动排序的表格式图例-数据分析网

其显著特点是,在右侧图例的位置上不是通常的图例,而是一个起到图例作用的表格,或者说是一种表格式的图例。并且: 表格中各项目的顺序,与各曲线最后一个数据点的大小顺序一致,填充色也与曲线图一致,这种对应增强了阅读体验。 表格中提供了各序列的变化幅度,图表表达的信息更加丰富,方便了读者。

既然是表格式的图例,当然要利用单元格来做。如果手动进行格式化设置,固然可以,但如果数据刷新了,又得辛苦一番重新设置。因此,需要找到智能化自动一致的办法。关键在于准备图例的数据。

 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列,查出该序列在原数据区域的顺序。这个顺序,会是插入曲线图时默认配色的顺序,我们将利用这个顺序来设置图例表格的填充色。

 Excel实战:自动排序的表格式图例-数据分析网

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

Excel实战:自动排序的表格式图例-数据分析网 3、为T14:U17设置条件格式,也就是表格式图例的智能填充色。 选中T14:U17区域,依次添加4个条件格式规则:

当=$S4=1,填充色为颜色面板第1行第5个格子 当=$S4=2,填充色为颜色面板第1行第6个格子 当=$S4=3,填充色为颜色面板第1行第7个格子 当=$S4=4,填充色为颜色面板第1行第8个格子 Excel实战:自动排序的表格式图例-数据分析网

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

4、拍照图例。 将T13:U17区域拍照,设置拍照图片无边框线,放置到曲线图的右侧位置,并对齐到例图样式。必要时可将其组合起来。

现在试着改变原数据取值,测试确认表格式图例能正确地排序和着色,非常智能。 如果你在页面布局中更改颜色主题,曲线和图例的颜色都将同步变化且保持一致!这就是在2007以上直接使用颜色面板中格子配色的好处。

 Excel实战:自动排序的表格式图例-数据分析网

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

作者:刘万祥老师

来自:http://excelpro.blog.sohu.com/269353425.html

banner
看过还想看
可能还想看
热点推荐
Yonghong的价值观:以卓越的数据技术为客户创造价值,实现客户成功。
免费试用