excel一组数据的分拆使用［调虎离山］

Excel技巧 2023-01-08

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

一组数据的分拆使用［调虎离山］

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

案例介绍

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

一组数据的分拆使用［调虎离山］

图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所示。

一组数据的分拆使用［调虎离山］

图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所示。

一组数据的分拆使用［调虎离山］

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

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

一组数据的分拆使用［调虎离山］

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

示例文档

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

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