站长家园(原代码之家)(www.adminjie.com)网站源码,微信源码,游戏源码,商业源码分享平台。
当前位置:网站首页 技术文章 网络编程 正文

Excel筛选状态下的计算总结

时间:2022-06-09 [网络编程]作者:fabuyuan 浏览:5 次

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

手机如何做表格:点击查看

相关学习推荐:excel教程

1、筛选后添加序号

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

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

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)

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

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

需要让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的个数。

4、筛选后自动更正标题

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

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

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筛选状态下的计算总结的详细内容,更多请关注站长家园其它相关文章!

本文标签:  Excel

转载请注明来源:Excel筛选状态下的计算总结

本文永久链接地址:https://www.adminjie.com/post/13269.html

免责声明:
本站所发布的一切资源仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。

附:
二○○二年一月一日《计算机软件保护条例》第十七条规定:为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬!鉴于此,也希望大家按此说明研究软件!

版权声明:
一、本站致力于为软件爱好者提供国内外软件开发技术和软件共享,着力为用户提供优资资源。
二、本站提供的部分源码下载文件为网络共享资源,请于下载后的24小时内删除。如需体验更多乐趣,还请支持正版。
三、我站提供用户下载的所有内容均转自互联网。如有内容侵犯您的版权或其他利益的,若有侵犯你的权益请:提交版权证明文件到邮箱 2225329873#qq.com(#换为@) 站长会进行审查之后,情况属实的会在三个工作日内为您删除。

  • 站长家园(原代码之家)会员升级
  • 最新文章
    • git怎么撤回刚删除的分支

      git怎么撤回刚删除的分支

      git撤回刚删除分支的方法:1、利用“gitremoteprune”命令查看被删除的分支;2、利用“gitreflog”命令找到被删除分支的最后一次提交记...

    • 如何安装并管理多版本node?方法介绍

      如何安装并管理多版本node?方法介绍

      如何安装并管理多版本node?下面本篇文章给大家介绍一下多版本node的安装与切换详细操作,希望对大家有所帮助!安装多版本node的原因:在项目开发过程中,不同...

    • git为什么会产生冲突

      git为什么会产生冲突

      在git中,冲突产生的原因是因为在合并文件时同一个文件的同一个位置都修改了,并且内容不同;也即两个已经提交的分支的相同文件相同位置的不同操作进行了合并,所以产生...

    • git分离头指针是什么

      git分离头指针是什么

      git分离头指针是一种HEAD指针不再指向分支,而是直接指向某个commit的状态;一般情况下HEAD指针指向分支,而分支是指向提交,分离头指针指的是变更没有基...

    • git bash做什么的

      git bash做什么的

      gitbash是用于git版本控制,上传下载项目代码用的;gitbash是Windows下的一个命令行工具,是基于“msysGNU”环境,有git分布式版...

    热门文章