登录  /  注册
首页 > 专题 > excel > 正文

Excel筛选状态下的计算总结

WBOY
发布: 2022-06-08 11:34:25
转载
4274人浏览过

本篇文章给大家带来了关于excel的相关知识,主要介绍了关于筛选后怎么添加序号、筛选后相乘、筛选后按条件计数等内容,下面一起来看一下,希望对大家有帮助。

Excel筛选状态下的计算总结

相关学习推荐:excel教程

1、筛选后添加序号

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

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

13.jpg

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

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

第二参数使用一个动态扩展的范围E$1:E2,随着公式的下拉,这个范围会依次变成E$1:E3,E$1:E4,E$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)

14.jpg

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

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

需要让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))

15.jpg

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

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

4、筛选后自动更正标题

如下图所示,对E列部门名称进行筛选后,希望D1单元格的标题自动变更为对应的部门名称,公式为:

=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&”统计表”

16.jpg

SUBTOTAL与OFFSET函数结合部分,目的仍然是判断D列的单元格是否为可见状态。得到由0和1组成的内存数组:

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

用0/这个内存数组,得到由0和错误值构成的新内存数组:

{#DIV/0!;0;#DIV/0!……;0;0;0;0;#DIV/0!;0;#DIV/0!;0;#DIV/0!}

LOOKUP函数以1作为查询值,在以上内存数组中查找最后一个0的位置,并返回对应位置的E列的内容。

最终目的就是实现筛选后,提取最后一个处于显示状态的单元格内容。

将提取到的内容与&”统计表”连接,变成可自动更新的表格标题。

相关学习推荐:excel教程

以上就是Excel筛选状态下的计算总结的详细内容,更多请关注php中文网其它相关文章!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
相关标签:
来源:Excel Home网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号