当前位置:首页 > 微软office > Excel > 正文内容

筛选后的汇总计算

zyhwxm2年前 (2023-05-17)Excel139

小伙伴们好啊,今天咱们分享一篇关于筛选状态下的计算套路。


1、筛选后添加序号

如下图所示,要在筛选状态下也能保持连续的序号,咱们可以先取消筛选,在D2单元格输入以下公式,然后下拉:

=SUBTOTAL(3,E$1:E2)-1

SUBTOTAL函数只统计可见单元格内容。

第一参数使用3,表示执行COUNTA函数的计算规则,也就是对第二参数统计可见单元格的个数。

第二参数使用一个动态扩展的范围E$1:E2,随着公式的下拉,这个范围会依次变成E$1:E3E$1:E4E$1:E5……

公式始终计算E列从第一行至公式所在行这个区域中,处于可见状态的非空单元格个数。用结果减1,计算结果就是和序号一样了,而且筛选后也能保持连续。

注意,注意,这个公式如果换成从=SUBTOTAL(3,E$2:E2),也就是从公式所在行开始的话,序号结果虽然没问题,但是筛选时最后一行会被Excel当成汇总行而始终显示。


2、筛选后相乘

如下图所示,在对E列执行筛选后,需要计算数量乘以单价的总额。

E2单元格公式为:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

要计算筛选后的乘积,问题的关键是判断数据是不是处于可见状态。

这个可见状态怎么判断呢?

需要让OFFSET和SUBTOTAL函数来结合一下。

首先使用OFFSET函数,以E3单元格为基点,依次向下偏移1~13行,得到一个多维引用。这个多维引用中包含13个一行一列的引用区域,也就是对E4~E16的单个单元格分别进行引用。

接下来使用SUBTOTAL函数,第一参数使用3,即依次统计E4~E16每个单元格中的可见单元格个数,如果单元格处于显示状态,则对这个单元格的统计结果为1,否则统计结果为0。得到类似以下效果的内存数组:

{1;0;1;1;1;1;0;0;1;1;0;1;0}

再使用以上结果乘以F列的数量和G列的单价,如果单元格处于显示状态,则相当于1*数量*单价,否则相当于0*数量*单价。

最后使用SUMPRODUCT函数对乘积进行求和。



3、筛选后按条件计数

如下图所示,对E列部门执行筛选后,要计算工龄大于3的人数。

E2单元格公式为:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

前半部分计算原理与上一个示例相同,核心也是判断是单元格否处于可见状态。

公式后半段的统计条件(G4:G16>3)与前半段的判断结果相乘,表示两个条件同时符合,也就是处于可见状态、并且G列大于3的个数。


好了,今天的内容就是这些吧,祝大家一天好心情!


练手文件在此:

链接: https://pan.baidu.com/s/1VdE6WcvnXoW3XLnXEBDb-A 

提取码: fcrc


图文制作:祝洪忠


本篇文章来源于微信公众号:Excel之家ExcelHome


版权声明:本文版权归原作者所有,仅供技术参考;

                 若有违反您个人权益,请留言反馈删除相关信息;


本文链接:http://zyh-c.myds.me:8086/post/267.html

返回列表

上一篇:数据求和,只会SUM可不够

没有最新的文章了...

相关文章

Excel常用函数公式20例

Excel常用函数公式20例

下面是一组常用Excel函数公式的用法,学会这些套路,让工作效率再高一丢丢。1、IF函数条件判断IF函数是最常用的判断类函数之一,能完成非此即彼的判断。如下图,考核得分的标准为9分,要判断B列的考核成绩是否合格。=IF(B4>=9,&...

手把手教你,学会提取不重复值

手把手教你,学会提取不重复值

一维区域筛选不重复记录示例26-7    提取客户代表姓名图26-8展示的是某单位销售记录表的部分内 容,需要提取不重复的客户代表姓名。1. MATCH函数去重法在F2单元格中输入以下数组公式,按<Ctrl+Shi...

COUNTIF函数经典用法合集

COUNTIF函数经典用法合集

小伙伴们好啊,今天老祝和大家分享一组COUNTIF函数的经典用法。COUNTIF函数主要用于统计满足某个条件的单元格的数量,基本用法为:COUNTIF(统计区域,指定的条件)其中指定的条件可以是数字、文字,或者是一...

TEXT函数,这些用法请收好

TEXT函数,这些用法请收好

TEXT函数是使用频率非常高的文本函数之一,她只有两个参数,参数1是要处理的数字,参数2用于指定格式代码,与单元格数字格式中的大部分代码都基本相同。接下来咱们一起看看TEXT函数的常见用法:1、简单的条件判断下图展...

学会这些小技巧,加班照样少不了

学会这些小技巧,加班照样少不了

小伙伴们好啊,今天老祝为大家准备了一组简单实用的小技巧,虽然简单,却能解决很多实际问题,咱们一样一样的看:1、用分列计算文本算式就像下图中,B列的文本算式如何计算出正确的结果呢?其实很简单,先依次单击【文件】【 选...

动态折线图,其实很简单

动态折线图,其实很简单

今天老祝和大家分享一个图表有关的知识。先来看这样一组数据,是某餐饮公司各连锁店1~6月份的销售情况:咱们用这组数据来制作一个折线图,用来展示各连锁店各月份的销售变化趋势,先来看效果:要实现这样的效果其实非常简单,咱...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。