Excel筛选状态下的计算总结？Excel筛选状态下的计算总结

　　有时候在Excel表格制作时，需要在筛选状态下做一个计算，那对于计算是不是大家都会很苦恼头疼那些数学公式呢？那么Excel筛选状态下的计算总结是什么样子的呢？还不清楚的小伙伴，接下来小编给大家带来筛选计算技巧，希望可以帮助到大家。

　　Excel筛选状态下的计算总结：

　　1、筛选后添加序号

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

　　=SUBTOTAL（3，E$1:E2）-1

Excel筛选状态下的计算总结？Excel筛选状态下的计算总结

　　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）

Excel筛选状态下的计算总结？Excel筛选状态下的计算总结

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

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

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

Excel筛选状态下的计算总结？Excel筛选状态下的计算总结

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

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

　　4、筛选后自动更正标题

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

　　=LOOKUP（1，0/SUBTOTAL（3，OFFSET（D1，ROW（1:15）-1，）），E:E）&”统计表”

Excel筛选状态下的计算总结？Excel筛选状态下的计算总结

　　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列的内容。

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

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