Excel图表中的大多数选项是针对整个图表或一个系列群组的设定,如果需要针对某一系列进行异于其他系列的设定,往往非常困难。图8.3-1就是这样一个案例。

一组数据的分拆使用[调虎离山]-Excel22

图8.3-1 一组数据的分拆使用

案例介绍

图8.3-1的案例包含两个折线系列,该图表所引用的数据如图8.3-2所示,源数据中包含:

一组数据的分拆使用[调虎离山]-Excel22

图8.3-2 原始数据表

1)公式引用结果为错误值;

2)包含有文本或空值。

图8.3-1的案例中:系列1需要将上述类型数值设置为“0”值;系列2则需要将上述类型的数值忽略,不在图表中显示。

提示


关于图8.3-1案例:

1)为了学习的方便,图8.3-1只是一个非常简单的案例,在真实的使用情况下,可能要比这个复杂许多。

2)本案例文档中附带了一个交替出现数值和非数值的案例。


案例分析

对于Excel图表中的点线类图表系列,空值可以使用“不绘制(留空距)”、“以零值代表”和“以内差值替换”选项来处理。该选项属全局性设定,即当选中其中任意选项,所有的点线类图表系列均会遵循;该选项也无法处理非空值,在多数情况下,往往面对非空值的处理要比空值概率更高。

在另外一些情况下,数据的来源可能是基于SQL的查询、数据透视表、外部数据等,无法直接使用NA()函数来强制内差值的替换。

案例实现

基于以上分析,图8.3-1案例的实现采用定义名称配合“以内差值替换”选项或NA()函数来实现,以下的实现过程以图8.3-2所示左侧数据列表为参考来实现。

:以下方法只是为了契合本节内容,并非是实现图8.3-1案例最简单的做法。

1.直接选中“Test 1”和“Test 2”所在的数据列,制作折线图,如图8.3-3所示。

一组数据的分拆使用[调虎离山]-Excel22

图8.3-3 8.3-1案例图表实现第1步

2.依次定义两个名称“DataA”和“DataB”,名称函数的结构类似:

=OFFSET(列标头,1,,COUNTIF(数据区域,">0"),1)

=OFFSET(列标头,COUNTIF(数据区域,">0")+1,1,数据个数-COUNTIF(数据区域,">0"),1)

提示


本案例中使用的定义名称采用了工作表级的定义名称。

Excel 2003定义时需在名称前加工作表名称,例如“Test1A!DataA”;Excel 2007/2010则直接选取即可。


3.选中图表的“Test2”系列,在编辑栏中使用小括号将上述定义名称录入到数值位置,之间加逗号进行隔离,需注意两个名称的录入格式,如图8.3-4所示。

一组数据的分拆使用[调虎离山]-Excel22

图8.3-4 8.3-1案例图表实现第3步

4.整理修饰后的最终效果如图8.3-5所示。

一组数据的分拆使用[调虎离山]-Excel22

图8.3-5 8.3-1案例图表最终实现

示例文档


本书所附案例文档8.3是上述案例的具体实现。

案例文档8.3中包含了针对非空值、空值以及交叉空值3种不同的实现;另外一个案例则完全采用辅助数据列来实现,该案例是最为简便的实现方法。本节未有详细述及的案例,请感兴趣的读者参照案例文档演练学习。