Excel动态图表
A | B | C | |
1 | 成长能力 | ||
2 | 年度 | 收入增长率(%) | 利润增长率(%) |
3 | 1993 | 29.59 | 10.72 |
4 | 1994 | 6.15 | 15.86 |
5 | 1995 | 32.22 | 13.52 |
6 | 1996 | 2.98 | 47.68 |
7 | 1997 | 1.03 | 52.07 |
8 | 1998 | 12.49 | 57.87 |
9 | 1999 | 41.95 | 46.97 |
10 | 2000 | 54.01 | 53.22 |
11 | 2001 | 40.11 | 31.5 |
12 | 2002 | 31.46 | 38.58 |
13 | 2003 | 8.23 | 10.37 |
14 | 2004 | 14.82 | 12.05 |
15 | 2005 | 16.23 | 13.66 |
16 | 2006 | 16.54 | 21.68 |
17 | 2007 | 17.4 | 18.52 |
准备工作:
菜单:插入/名称/ 定义:年份。 引用位置=报表!$A$3:$A$17
在E1至H1中,输入如下数据:
E | F | G | H |
起始年 | 1993 | 终止年 | 2002 |
H1中同F1,用复制和粘贴就可以。
接下再插入3个名称定义:
年:引用位置=OFFSET(报表!$A$1,报表!$I$1,0,报表!$J$1,1)
收入:引用位置=OFFSET(报表!$B$1,报表!$I$1,0,报表!$J$1,1)
利润:引用位置=OFFSET(报表!$C$1,报表!$I$1,0,报表!$J$1,1)
这是三个动态定义,以“年”为例:
第1参数“报表!$A$1”:单元A1;
第2参数“报表!$I$1”:相对于A1偏移的行数,行数的取值在I1单元格中;
第3参数“0”:相对于A1偏移的列数,此处为0,表示不偏移;
第4参数“报表!$J$1”:高度,即所要返回的引用区域的行数,行数的取值在J1单元格中;;
第5参数“1”:宽度,即所要返回的引用区域的列数,此处为1,表示1列。
这样根据I1和J1的数据就可以得到一个动态定义。它的起始位置的长度都是变化的。
同理,可以将前面的“年份”也定义成动态的。
接下来,定义I1、J1、K1中的公式:
I1=MATCH(F1,A:A,0)-1
含意:查找F1的值在A列的位置-1
J1=H1-F1+1
含意:求两个年份之的个数。
K1=F1 & "年至" & H1 & "年" & A1
含意:XXXX年至XXXX年成长能力
然后开始做图:
选择A2-C17,点击图表工具做一图。
在图表上点击右键:源数据/系列。
在“分类(X)轴标志”输入:= 动态图表.xls!年
其中“动态图表.xls”为当前 Excel 文件名。
更改“系列”:
收入增长率(%):
名称:=报表!$B$2
值:=动态图表.xls!收入
利润增长率(%):
名称:=报表!$C$2
值:=动态图表.xls!利润
确定退出!
然后,再在图表上点击右键:图表选项/标题。
“图表标题”中输入:成长能力
确定退出!
点击图表上的标题:成长能力,在编辑栏中输入:=报表!$K$1
纵坐标 格式: 数字/自定义:##0"%"
最后一步,菜单:文件/保存
至此动态图表已经做好,选择F1和H1的年份,图表也会随之改变。
以上功能未使用 VBA,如果加上 VBA处理数据,能使图表更具多种变化的能力。
比如读取多家公司的数据等等。
还可以把I1、J1、K1的公式放在别的工作表中。或调成白色。
其实,我们在图上点击某一系列会在编辑栏中看到一个公式:
=SERIES(报表!$B$2,动态图表.xls!年,动态图表.xls!收入,1)
=SERIES(报表!$C$2,动态图表.xls!年,动态图表.xls!利润,2)
这是系列1和系列2的公式,现在您应该能看明白这个公式了。