Excel 如何删除所有0值所在行?

在图4.21所示的工资表中,实发工资一栏中部分人员的工资为0,值为0的单元格可能有1个也可能有几百个,现要求删除所有值为0的单元格所在行,如何才能一次性删除呢?

解题步骤

删除0值所在行的重点在于找到所有0值,然后通过右键删除整行即可,具体步骤如下。

1.选择H列。

2.按组合键<Ctrl+F>,弹出“查找和替换”对话框。

3.将“查找内容”设置为0。

4.单击对话框右下角的“选项(T)>>”按钮,从而让对话框显示更多内容。

5.选择“单元格匹配”复选框。

6.将“查找范围”的默认值“公式”修改为“值”,操作界面如图4.22所示。

图4.22 设置替换选项

7.单击“查找全部”按钮,然后按下组合键<Ctrl+A>,表示选中所有找到的单元格。查找选项的设置界面与查找结果如图4.23所示。

图4.23 全选所有0值所在单元格

8.单击键盘上的右键,或者用鼠标单击 H7 单元格调出右键菜单,然后从右键菜单中选择“删除”,弹出“删除”对话框。

9.将删除选项由“下方单元格上移”修改为“整行”,然后单击“确定”按钮,H列的0值所在行将会一次性删除。图4.24是“删除”选项的设置界面,图4.25是删除结果。

图4.24 设置删除选项

图4.25 删除H列的0值所在行

知识扩展

1.手工输入单元格中的值可以替换成公式“=0/0”,但是通过公式计算出来的 0 则无法替换。因此本例没有采用将0替换公式“=0/0”,然后定位错误值再删除整行的思路。

2.Excel提供了3种查找方式,包含从单元格的值、批注和公式中查找,其中“值”是指在单元格中显示出来的值中查找,而“公式”表示不管单元格显示的值是什么,只在公式中查找。例如,A1 单元格的公式是“=153-103-20”,计算结果是 30,那么按公式查找 20,不选择“单元格匹配”复选框,A1单元格刚好符合条件,因为公式中包含数字20。如果改为按值查找,那么A1单元格不符合条件。正确理解“值”与“公式”才能用好查找与替换。

3.“查找与替换”对话框中的“单元格匹配”被选择时表示整段字符都与查找的对象一致才符合条件,不选择时表示部分字符与查找的对象一致就符合条件。例如,A1单元格中包含“张三丰”,那么未选择“单元格匹配”而查找“三”,A1 单元格是符合查找条件的,如果选择了“单元格匹配”,那么只有查找“张三丰”时A1单元格才符合查找条件。

4.“查找与替换”对话框中的“区分大小写”和“区分全/半角”仅用于查找字母,查找汉字和数字时仅需设置好“单元格匹配”选项即可。

5.当有多个单元格符合查找条件时,单击“查找下一个”按钮可以选中其中一个符合条件的单元格,单击“查找全部”时会在下方的列表中罗列出所有符合条件的单元格地址和单元格的值,但是Excel并不会选中这些符合条件的单元格。此时按下组合键<Ctrl+A>可以全选所有符合条件的单元格,要注意不能在关闭对话框后全选,而是单击“查找全部”后马上按下组合键<Ctrl+A>。

6.查找时Excel遵循一个规则:当选择了单个单元格后进行查找,Excel会在整个工作表中查找;当选择了多个单元格后进行查找,Excel 只在选区内查找。因此本例在查找前必须先选中H列,不能在选中单个单元格时执行查找操作。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

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

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

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

1)VBA删除法

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


程序代码:6.5-2 VBA循环判断删除”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所示:

定义名称及具体公式

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

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

烦人的饼图“0”值[趁火打劫]

不论何种面积类图表类型,因为占位的关系,Excel处理图表中“0”值时均会如实绘制,同时面积类图表在处理空单元格时采用“以零值代替”。但这在饼图的处理上,有时会让人非常烦躁,因为零值的数据点标签和引导线并不会因为是“0”值,Excel就忽略它们的存在。事物往往具有两面性,图6.5-1就是一个利用饼图处理“0”值特点的典型案例。

图6.5-1 烦人的饼图“0”值

案例介绍

图6.5-1的案例是某公司营业部门月度销售业绩KPI的达标状况。该案例中达标得分:65%以下为差;65%~90%为良;90%以上为优。希望通过图表可以直观获得当前指标得分落在了那个区间范围。

根据上述内容:由于仅仅是单个指标的图表,考虑到直观性,此处使用仪表盘来实现。仪表盘为圆形结构,结合Excel的极坐标图表,最佳的选择是使用饼图,因为饼形图可以有效利用扇区进行区间的分割布局。

案例分析

实现图6.5-1案例图表的主要挑战来自以下几方面:

  • 饼图为圆形,而仪表盘为半圆形;
  • 饼图的扇区起始位置默认在0°,为顺时针12点方向,而仪表盘是顺时针9点方向;
  • 实现仪表盘指针较为不易。

以上3点,1)完全可以使用占位扇区,无色无边框实现;2)可以使用饼形图的“第一扇区起始角度”选项来实现。实现的关键在3),传统的方案是使用次坐标饼形图或次坐标雷达图来实现,使用这两个方案有以下困扰:

  • 次坐标饼形图:需要使用3个经过计算处理的数据来完成指针的定位;
  • 次坐标雷达图:需要使用的辅助数据区域至少要360列/行。

案例实现

由于饼形图处理数值为“0”值的扇区数据点时并不忽略该数据点,如图6.5-2所示,该图表使用两个数据点,其中第2个数据点为“0”值,不论图表单元格设置是否为空距,但在图表中依然具有线和面的视觉属性,鼠标也可对其进行格式设定,图例的数据点标签也说明了这一点。

图6.5-2 饼形图处理数值为“0”值的扇区

提示


1)Excel 2003/2007中,饼形图对0值和空值的图形绘制,并非真正意义上的为空或为0;

2)Excel 2010对于空值的解释要较Excel 2003/2007有所进步,凡是引用单元格为空值或公式结果为“#N/A”的扇区数据点,该扇区数据点所设置的边框和填充均不会被显示。


综上,该案例的仪表指针部分仅使用一个空单元格为引用数据的次坐标饼形图。以下为该案例的详细实现过程:

1.整理原始数据,如图6.5-3所示。

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

2.以“所处位置”为系列标签,0值为数值引用制作饼图,如图6.5-4所示。

图6.5-4 6.5-1案例图表实现第2步

系列引用公式:=SERIES(Test!$F$15,,Test!$F$17,1)

3.在数据源中添加以“区域划分”为系列标签,辅助标签列数据为饼图扇区分类标签,区域划分的数据列为数值引用的新系列,如图6.5-5所示。

图6.5-5 6.5-1案例图表实现第3步

系列引用公式:=SERIES(Test!$B$15,Test!$D$16:$D$19,Test!$C$16:$C$19,2)

4.选中“所处位置”系列,将其设置到次坐标系中,同时设置该系列的分类标签为“83%”,实现效果如图6.5-6所示。

图6.5-6 6.5-1案例图表实现第4步

系列引用公式:=SERIES(Test!$F$15,Test!$F$16,Test!$F$17,1)

5.选中“区域划分”系列,将第一扇区起始角度设置为270°,并将占位扇区设置为无边框无填充,如图6.5-7所示。

图6.5-7 6.5-1案例图表实现第5步

6.选中“所处位置”系列,将第一扇区起始角度设置为辅助作图的角度值,并勾选设置图表数据标签为类别名称,如图6.5-8所示。

图6.5-8 6.5-1案例图表实现第6步

此处仪表指针的显示位置通过计算第一扇区旋转角度来获得。这个辅助作图的单元格并没有实际参与图表的绘制,只起辅助计算作用。

7.整理润色修饰后的最终效果如图6.5-9所示。

图6.5-9 6.5-1案例的最终实现

这个案例的实现相较而言比较简单,但当数据变化后,该图的指针部分需手工重新设置第一扇区起始角度,通过录制一个简单的宏即可解决。


程序代码:6.5-1 VBA调整饼图第一扇区起始角度


示例文档


本书所附案例6.5-1中提供了上述案例的两个不同实现方法。

对于如何利用3个数据点的饼形图来制作KPI仪表板的详细制作步骤,感兴趣的读者可参照案例文档来学习。