Excel面积类图表中的“0”值处理

Excel技巧 2023-01-08

往往大家更加关心如何在面积类图表中剔除“0”值，尤其是饼图和环形图。当然最为直接的方法就是一个个手工去设置标签，但这样非常令人沮丧，烦琐且没有效率。此处总结一些去除“0”值的方法。

1．仅对数据标签进行设置，使其在视觉上不可见。

1）VBA删除法

通过一个个标签的循环判断来删除标签，等于模拟手工删除。

程序代码：6.5-2　VBA循环判断删除"0"值标签

面积类图表中的“0”值处理

2）数字格式法

该方法主要针对标签内容为数值的情况下，使用数据标签格式>数字>自定义：0.00;;;，若要剔除分类内容的标签，必须和辅助数据配合使用。使用该方法时需要说明：饼形图不可以使用引导线，标签间的分隔符设置必须为空格和新行模式。

2．一劳永逸的方法是：直接将“0”值项从图表数据源引用中剔除的数据整理法，该方法主要针对饼形图和环形图。

1）数组函数辅助区域＋定义名称法

该方法需使用数组公式来将“0”值项从原始数据集中剔除，使用起来有一些难度。定义名称可以采用：

=OFFSET(初始单元格,0,0,MATCH(“”,数据所在单元格区域,0)－1,1)

2）排序＋定义名称法

这是笔者推荐的方法，相较而言简单、直接。对于饼形图和环形图，可以很方便地了解谁的占比最大。定义名称可以采用：

=OFFSET(初始单元格,0,0,MATCH(0,数据所在单元格区域,0)－1,1)

3）完全定义名称法

这是所有方法中最为复杂的方法，由于图表引用定义名称的限制，需将定义名称进行多步分拆处理，该方法其实就是“数组函数辅助区域＋定义名称法”的函数封装形式。以下定义名称具有较好的通用性，使用时仅需修改“A原始分类”和“A原始数据”两个定义名称的单元格引用即可，如表6.5-10所示：

面积类图表中的“0”值处理

表6.5-10　定义名称及具体公式

使用高级筛选功能和数据透视表也可实现。高级筛选也需和定义名称配合使用，每次变更数据源数据后，需要手工操作完成筛选作业；数据透视表生成的图表是数据透视图，这类图表在外观管理上有一定局限性。