Excel中如何作特大值数据的条形图

本文根据Broken Y Axis(http://www.peltiertech.com/Excel/Charts/BrokenYAxis.html)修改而成。

在作柱形图或条形图时,数据中若有特大值,作出的图表会因为数据分布差距太大,导致比例不协调,取值小的数据被淹没了。如下图。

 

May  

June  

July 

London  

1,234,565 

1,452,369 

1,478,852 

Paris  

2,363,645 

24,568,876 

5,562,413

Madrid  

23,645,254 

3,211,654 

5,857,421 

Brussels  

5,914,753 

5,544,221 

3,620,015 

Lisbon  

5,824,676 

4,541,258 

4,015,876 

Munich  

2,501,478 

6,325,698 

4,569,872 

解决的办法有两条,1.使用对数刻度;2.使用第二坐标轴。不过这两种方法总不是令人那么满意。对数刻度不太让人容易理解,而第二坐标轴却失去了直观性,不易比较。

当然也可以从某个地方将y轴截断,其最终效果如文末所示。

作法步骤如下:

1.将上表中的数据,如果大于8000000,则减去13000000,不然,数据不变。其公式形如=IF(B3>8000000,B3-13000000,B3)。表变为    

 

May  

June  

July 

London  

1,234,565 

1,452,369 

1,478,852 

Paris  

2,363,645 

11,568,876 

5,562,413 

Madrid  

10,645,254 

3,211,654 

5,857,421 

Brussels  

5,914,753

5,544,221 

3,620,015 

Lisbon  

5,824,676 

4,541,258 

4,015,876 

Munich  

2,501,478 

6,325,698 

4,569,872 

 注意y轴将在8000000处截断。图中数据的差距不再那么大了。 

 2.新建一个表格如下,假设是从F1到G13.

 

F  

G  

H 

1 

 

Markers  

Labels 

2 

0 

0 

0 

3 

0 

1,000,000 

1,000,000 

4 

0 

2,000,000 

2,000,000 

5 

0 

3,000,000 

3,000,000 

6 

0 

4,000,000 

4,000,000 

7 

0 

5,000,000 

5,000,000 

8 

0 

6,000,000 

6,000,000 

9 

0 

7,000,000 

7,000,000 

10 

0 

9,000,000 

22,000,000 

11 

0 

10,000,000 

23,000,000 

12 

0 

11,000,000 

24,000,000 

13 

0 

12,000,000 

25,000,000 

选中从F1到G13的数据并进行复制,再选中表格,然后点选编辑->复制性粘贴,在弹出的选择性沾贴菜单上,将添加单元格为新建系列,数值(Y)轴在列,首行为系列名称选中,首列为分类x标志。如下图。

得到下列所示的图。

3.在上图新添加的数据系列上,点选右键,将图表类型改为散点图。得到如下图表。此时数据分布在y轴上。

4.去掉坐标轴。在上述图表的第二坐标轴的x轴上,点选右键,在弹出的坐标轴格式上,将坐标轴及刻度线标签均取消,同样的方法,将右边的y轴也去掉。同时将y轴的刻度值也去掉。即成如下的图表。

5.将y轴上点添加刻度标签。本步需要用到一个Excel的插件XYChartLabeler(http://appspro.com/Utilities/ChartLabeler.htm),可以对点添加标签。下载安装后在工具栏上如图所示。

选择y轴的点后,再选择Add Chart Labels 后,出现下列图示

在Select a Label Range中选择第2步表格的H1:H13的数据作为点的标签。其位置设为左边,得到下示图表。

6.最后一步,添上波浪形的图形,最终成图。

完成上述图表的核心思想,是用点的标签代替y轴上的刻度上的标签,达到以假乱真的目的。这种方法似乎有点繁杂,简单的做法就是先去掉刻度上的标签,然后再一个一个地添加文本框,来代替刻度上的标签。

posted on 2009-02-11 18:55  zgw21cn  阅读(13218)  评论(6编辑  收藏  举报

导航