查看: 9|回复: 0

【原】Excel高手必学5种技巧:筛选状态下的计算套路,都是工作中学用的

[复制链接]

26万

主题

26万

帖子

80万

积分

论坛元老

Rank: 8Rank: 8

积分
808951
发表于 5 天前 | 显示全部楼层 |阅读模式
 

 

【温馨提示】亲爱的朋友,阅读之前请您点击【关注】,您的支持将是我最大的动力!

  
筛选功能在Excel表格中是应用比较多的,毕竟方便我们查询、汇总表格数据,但有些数据的汇总计算,在使用筛选后就变的不是那很容易了,用普通的技巧、函数公式难以解决。今天小编就分享几个筛选后的数据处理方法,都是工作中经常用到的,再用时直接套用就好。
1、筛选后填充序号
表格中的序号我们一般利用拖动或双击鼠标来填充,但在使用筛选功能后,筛选出的部分数据,序号是不连续的,如何让序号在筛选后也是从1开始递增显示呢?
在A2单元格输入公式:=SUBTOTAL(3,B$1:B2)-1  再双击填充公式即可

这样设置的序号,不管如何筛选,序号都是从1开始递增,效果如下:

2、筛选后的加法计算——求和

上图表格中普通求和我们可以利用SUM函数或者快捷键都可以完成,但在筛选后,求和的结果还是保持原来计算结果。

这时我们可以把原求和公式换成:=SUBTOTAL(9,D2:D9),这样再无论怎么筛选,都是筛选出来的数值的和。

3、筛选后的乘法——计算总金额

上图表格中的总金额计算可以用公式:=SUMPRODUCT(C4:C13,D4:D13),但在筛选后,总金额还是保持原数不变

我们可以把公式换成:=SUMPRODUCT(SUBTOTAL(3,OFFSET(C3,ROW(1:10),))*C4:C13*D4:D13),公式比较复杂,小伙伴们可以直接套用。

4、筛选后的条件计数
统计筛选后数量大于400的个数:=SUMPRODUCT(SUBTOTAL(3,OFFSET(C3,ROW(1:10),))*(C4:C13>400))

5、筛选后自动更正标题
表格中的标题可以根据筛选的门店结果显示
公式:=LOOKUP(1,0/SUBTOTAL(3,OFFSET(B1,ROW(1:22)-1,)),B:B)&"一季度销售统计表"

小结:上面的公式中都用到了SUBTOTAL函数
SUBTOTAL函数
【用途】返回数据清单或数据库中的分类汇总。如果用户使用“数据”菜单中的“分类汇总”命令创建了分类汇总数据清单,即可编辑SUBTOTAL函数对其进行修改。
【语法】SUBTOTAL(function_num,ref1,ref2…)
【参数】Function_num为1到11之间的自然数,用来指定分类汇总计算使用的函数(1是AVERAGE;2是COUNT;3是COUNTA;4是MAX;5是MIN;6是PRODUCT;7是STDEV;8是STDEVP;9是SUM;10是VAR;11是VARP)。Ref1、ref2…则是需要分类汇总的1到29个区域或引用。
小伙伴们,在使用Excel中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞转发就是对小编最大的支持。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ| Archiver|手机版|小黑屋| 师哈哈 |网站地图

Copyright © 2019-2025 Www.biiyy.Com.   All Rights Reserved.

Powered by Discuz! X3.4( 苏ICP备14049462-3号 )