用Excel绘制动态图表

1 动态图表的概念

Excel所绘制的图表，一般绘图的来源数据都是指定位址，也就是图表上的数据数据都已经被固定。但有些情况，例如绘制一张管制图，时间不断地经过而累积了30天的资料，而绘制管制图时只希望画出最后25天的数据就好；或者在月度不良率推移图上，只要画出最后12个月的数据，在这种情况下Excel的图表必须逐次去更改绘图来源数据的指定位址而麻烦，解决这个问题困扰的方法就是采用动态图表绘制法，透过Excel的Offset函数来动态地指定绘图来源数据的位址。



2 动态图表的作法例

从月度不良率的数据库上，以最后12个月的数据绘制推移图(运行图、折线图)，数据库每月都会增加数据

1) 资料布署

在工作表sheet1上







A


B


C


D




1


月


不良率










2


1


0.012


预定显示


12




3


2


0.022


数据总数


16




4


3


0.025


实际显示


12




5


4


0.018


若不满则


显示全部




6


5


0.020










7


6


0.018










8


7


0.025










9


8


0.024










10


9


0.019










11


10


0.022










12


11


0.021










13


12


0.020










14


13


0.026










15


14


0.023










16


15


0.021










17


16


0.015










储存格D3是以函数CountA来计算数据库的数据数，储存格D4是函数If来决定实际显示数目，若小于预定显示的12时则将所有数据全部显示，若大于预定显示数时，则只显示预定的数(12)。

2) 设定动态的绘图来源数据

将欲绘制推移图的数据来源地址建立名称，譬如纵轴数据数据地址命名为cht_range，横轴数据数据地址命名为cht_label，实际作法是[插入]
> [名称] > [定义] 对话框中<名称>填入
cht_range，<引用位置>填入

=OFFSET($B$1,$D$3-$D$4+1,0,$D$4,1) 然后按[新增]钮将名称输入工作表

再次启用 [插入] > [名称] > [定义] 对话框中<名称>填入
cht_label，<引用位置>填入 =OFFSET(cht_range, 0, -1) 然后按[新增]钮将名称输入工作表

上述动作已经命名 cht_range与cht_label二个名称

注：Excel 2007版本建立名称请不可使用chart开头

3) 绘推移图

[图表精灵] > [折线图] > [系列] > [新增] 填入

名称：B1

値y：sheet1!cht_range

分类x：sheet1!cht_label

即可出现推移图，可进行必要图的润饰

4) 动态检查

可以数据底端加入一数据，简是推移图是否自动更新