和兴图书馆 / 自我创新 / 一脸蒙懵!一招三步教你解决筛选处理过的E...

分享

   

一脸蒙懵!一招三步教你解决筛选处理过的EXCEL序列号填充自动更新

2020-05-26  和兴图书馆


有朋友说,序列号填充,太简单啦,也拿来分享?不信,你来试试?哈哈,我来了,好好听着,在A2单元格中输入数字“1”,选中A2单元格,光标和填充柄重叠,光标由空十字型变成黑实体光标,按住CTRL键不放,按住鼠标左键向下拖动就行了。仔细一看,全是“1”啊,怎么不变?是电脑出问题啦?以前也是这样操作的,也是处在筛选的状态下。灵机一动,再来神操作一下,文件选项卡EXCEL选项高级标签打钩“启用填充柄和单元格施放功能”,确定,再操作上面的程序,又全是“1”,没有变。告诉你,操作一百遍,还是同样的结果。告诉你关键点,以前你是处在筛选的状态下,没有进行筛选,也就是说没有隐藏的行,所以可以按上面的程序操作。有没有办法解决呢?答案是肯定的,只是要用到SUBTOTAL函数来实现 。即公式:SUBTOTAL(103,$P$N1:PN2)-1。

公式解释:SUBTOTAL函数有两个参数,第一参数计算方式,第二参数计算的区域。计算方式有以下几种:

Function-num

(包含隐藏值)

Function-num

(忽略隐藏值)

函数

              1      

101

AVERAGE

              2      

102

COUNT

              3      

103

COUNTA

              4      

104

MAX

              5      

105

MIN

              6      

106

PRODUCT

              7      

107

STDEV

              8      

108

STDEVP

              9      

109

SUM

             10     

110

VAR

公式=SUBTOTAL(103,$P$N1:PN2)-1,这里用了103,统计单元格区域PN1:PN2里非空单元格的个数,包含隐藏的行。

第一参数用3和103的区别?用3就相当于COUNTA,SUBTOTAL的结果包括通过“隐藏行”命令所隐藏的行中的值;用103,也有COUNTA函数的作用,但是它会把隐藏行的不参数计入计算,SUBTOTAL的结果不包括通过“隐藏行”命令所隐藏的行中的值,而使用自动筛选时,无论用103还是3,对SUBTOTAL的结果没有影响。当然,如果要兼顾手动隐藏行的情况,最好用103,这就是我要推荐第一参数用103的原因。

对于公式=SUBTOTAL(103,$P$N1:PN2)-1,要特别说明一下,P代表的是被筛选处理过的列号,即列号B、C、D、E、F…… , N1代表的是序号栏的行号,即行号2、3、4……,PN2代表的是序号栏的下一个行号,即行号3、4、5……

下面,我们结合实例来介绍一它的妙用之处。

如上图,瓮安县村(社区)18—40周岁在村人员排查统计表。A列都有一个序号,且按顺序排序。现在我们对C列的性别栏进行筛选处理,筛选出18—40周岁,性别为女的在村人员。如下图:

上图序号变为2、5、6、8、10、12、14……成为间断的序号,如何将序号变为不间断、自动更新的序号?主要有以下几步:

第一步:在上图序号2中输入“=SUBTOTAL(103,$C$3:C5)-1”,结合函数公式=SUBTOTAL(103,$P$N1:PN2)-1进行说明,函数公式中的P变成了被筛选处理后的C列,N1变成了序号所在的行号3,PN2变成了序号行的下一行号5。

第二步:输入完=SUBTOTAL(103,$C$3:C5)-1后,按回车键ENTER,原序号2变成了1。特别强调的是结果必须是1,如果结果不是1,说明函数公式输入有误。

第三步:点击新序号1,将鼠标移至新序号1右下角,向下拉动填充,新序号就这样产生了,而且删除或增加行,序号自动更新。如下图所示。

好了,记住并理解函数公式,动动手操作一下加深印象。如有不理解不明白的地方,敬请留言讨论,同时不要忘记点赞关注哦。                     

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>