健康资讯 关于【excel函数公式大全讲解筛选】，今天小编给您分享一下，如果对您有所帮助别忘了关注本站哦。
- 内容导航：
- 1、excel函数公式大全讲解筛选：筛选状态下的计算套路，难并实用着
- 2、excel函数公式大全讲解筛选，Excel中做常用的分类汇总函数SUBTOTAL
1、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列的内容。
最终目的就是实现筛选后，提取最后一个处于显示状态的单元格内容。
将提取到的内容与&”统计表”连接，变成可自动更新的表格标题。
2、excel函数公式大全讲解筛选，Excel中做常用的分类汇总函数SUBTOTAL
SUBTOTAL函数是日常中我们经常用到的一个分类汇总函数，他可以忽略隐藏值进行分类汇总，下面让我们一起来了解他把
SUBTOTAL函数以及参数SUBTOTAL：返回一个数据列表或者数据库的分类汇总
第一参数：我来看个动态图先
l 1 AVERAGE（算术平均值）
l 2 COUNT（数值个数）
l 3 COUNTA（非空单元格数量）
l 4 MAX（最大值）
l 5 MIN（最小值）
l 6 PRODUCT（括号内所有数据的乘积）
l 7 STDEV（估算样本的标准偏差）
l 8 STDEVP（返回整个样本总体的标准偏差）
l 9 SUM（求和）
l 10 VAR（计算基于给定样本的方差）
l 11 VARP（计算基于整个样本总体的方差）
l 101 AVERAGE 数学平均值
l 102 COUNT 数字的个数
l 103 COUNTA 非空的个数
l 104 MAX 最大值
l 105 MIN 最小值
l 106 PRODUCT 乘积
l 107 STDEV 标准偏差
l 108 STDEVP 标准偏差
l 109 SUM 求和
l 110 VAR 方差
l 111 VARP 方差
第二参数：单元格引用区域
SUBTOTAL的第一参数有22个之多，我们可以分为两大类来理解
编号为1-11的参数它们在分类汇总的时候会计算隐藏值
编号为101-111的参数它们在分类汇总的时候不计算隐藏值
下面我来看下是具体怎么操作的
一、当第一参数为1到11的时候1. 求和
2. 计数
我们可以看到我们仅仅是更改第一参数，就实现了不同的函数功能，这就说明可以通过更改不同的编号来实现不同的功能，这就是分类汇总函数的强大之处，更加便于我们去分析数据，而不用再从新输入一次函数
当我的第一参数为1-11时，是会计算隐藏之的，跟普通的函数没什么区别，比如当我们选择了编号9，就代表求和，他和单独使用sum函数求和的结果是一样的，下图是公式比较
二、当第一参数为101-111时候当第一参数为100时候，函数会忽略单元格中的隐藏值，下面就用一个动图对比下
我们可以看到当隐藏单元格后第一参数为109的函数会忽略掉隐藏值进行求和，而参数为9的函数却没有，除了这点区别之外，其他的都是一样的，
在日常工作中我们经常使用这个函数对筛选值进行求和计数等函数计算
本文关键词：excel函数公式大全详解，excel函数公式大全解释图表，excel 函数与公式应用大全，excel函数公式符号讲解，excel函数公式大全。这就是关于《excel函数公式大全讲解筛选，筛选状态下的计算套路》的所有内容，希望对您能有所帮助！
