当Excel工作表中的数据很多时，查看数据是非常容易看串行，隔行填充颜色可以有效的解决这一问题。但是当隔行填充颜色后需要筛选或者隐藏一部分数据，那么可见的单元格区域就不会以隔行填充颜色的效果显示。今天就分享一种使用条件格式和SUBTOTAL函数制作的在筛选或隐藏单元格后实时更新隔行填充颜色效果的方法。
一.效果演示：
话不多少，先给想要学习的朋友们演示一下制作完成的操作效果。
二.SUBTOTAI函数简介：
1.功能：返回一个数据列表或数据库的分类汇总。
2.语法：SUBTOTAL(function_num,ref1,ref2, ...)
（1）function_num是不同功能所对的数值，数值和对应的功能如下图所示。
（2）ref1,ref2通常是对要分类汇总单元区域的引用。
3.说明：
Function_num 为 1 到 11计算结果包含手动隐藏的单元格区域；Function_num 为 101 到 111计算结果包含不包含手动隐藏的单元格区域。
4.应用举例：
在E1单元格输入下方的公式确定,在筛选或隐藏单元格后E1单元格就可以实时计算A列可见非空单元格个数。
三.操作过程：
1.插入并设置复选框：
（1）切换到开发工具选项下插入表单控件下的复选框，设置复选框的名称为“是否对可见单元格隔行填充颜色”。
（2）右键设置控件格式将单元格链接设置为E1单元格；属性设置为大小、位置均固定；最后填充一个控件的颜色。
（3）按住Alt键调整控件的大小使控件对其单元格、美观的显示。
2.使用条件格式设置对可见单元格隔行填充颜色：
选择要设置隔行填充颜色的数据区域→切换到开始选项下→条件格式→新建规则→使用公式确定要设置格式的单元格→输入下方的公式→点击格式，在填充选项下设置一个颜色→确定。
=AND(MOD(SUBTOTAL(103,$A$1:$A1),2),$E$1)
3.公式解析：
（1）SUBTOTAL(103,$A$1:$A1)的功能是统计A1:A1单元格可见非空单元格的个数；注意单元格区域引用方式、第二个A1要相对引用行，这样向下填充时会依次统计A1:A1、A1:A2、A1:A3......单元格区域可见非空单元格的个数。可见单元格返回结果依次是1、2、3、4......数字数列。
（2）用MOD函数求解（1）所得结果除以2的余数，可见单元格返回的结果为1、0、1、0......重复的数字序列。1表示逻辑值TRUE,0表示FALSE。
（3）E1单元格当插入的复选框打钩是返回逻辑值TRUE,否则返回FALSE。
（4）最后用AND函数判断（2）和（3）的返回的结果；只有结果都为TRUE是返回TRUE,否则返回FALSE。
总结，筛选和隐藏单元格后实时更新隔行填充颜色效果的操作方法你学会了吗？
评论 (0)