我们在制作各类表格的时候,为了方便我们查看数据量,通常会设置一列序号,但是在序号的设置中不同的人有不同的设置方法。在之前的教程中,我们已经给大家介绍过了一些序号的设置方法,今天我们再给大家讲一种比较实用的设置—当进行了隐藏单元格或者筛选操作后,可见单元格仍然按顺序进行排列。请看下面的动图演示: 下面,我们就来给大家讲解如何实现以上的效果吧。 要实现以上效果,我们需要使用到一个函数subtotal,这个函数能实现求和、求平均、计数等多个功能,同时还能按照是否计算隐藏单元格的值分为两大类。该函数的具体用法如下: 作用:返回一个数据列表或数据库的分类汇总。 语法:=subtotal(function_num,ref1,ref2……) 参数解释: 1.function_num。功能代码,代码分为1-11和101-111两组,其中第一组1-11是包含隐藏值的,第二组101-111是不包含隐藏值的。具体的功能代码如下: 1AVERAGE(算术平均值) 2COUNT(数值个数) 3COUNTA(非空单元格数量) 4MAX(最大值) 5MIN(最小值) 6PRODUCT(括号内所有数据的乘积) 7STDEV(估算样本的标准偏差) 8STDEVP(返回整个样本总体的标准偏差) 9SUM(求和) 10VAR(计算基于给定样本的方差) 11VARP(计算基于整个样本总体的方差) 101AVERAGE 数学平均值 102COUNT 数字的个数 103COUNTA 非空的个数 104MAX 最大值 105MIN 最小值 106PRODUCT 乘积 107STDEV 标准偏差 108STDEVP 标准偏差 109SUM 求和 110VAR 方差 111VARP 方差 2.ref。计算的数值区域。 看了以上的介绍,相信各位小伙伴已经能够猜出今天的功能如何实现了吧?没错,我们就是需要subtotal函数的103功能代码来实现。A2单元格的公式为:=SUBTOTAL(103,$B$2:B2)。 但是此时会出现一个小问题,请看下面的图片。 为什么在筛选的时候,最后一行总是会显示出来呢?这是因为有了SUBTOTAL且在自动范围的最后一行,数据范围就会自动排除这一行。那么如何解决该问题呢,其实方法也很简单,就是我们让subtotal的结果*1(或者+0,+1-1)操作。此时,A2单元格的公式变为:=SUBTOTAL(103,$B$2:B2)*1,再看一下效果: 这个时候就能正常显示了。同样的,subtotal可以实现在求和等操作中,只对看见单元格区域进行求和,这在某些场景下也是非常实用的哟,大家可以自行尝试。 |
|