偏移功可以(偏移功可以是哪几个意思)

啊，今天来分享一篇关于筛选状态下的计算套路的文章。

1.筛选后添加序列号。

如下图所示，为了在过滤状态下保持连续的序号，我们可以够先取消过滤，在单元格D2中输入如下公式，然后下拉:

=小计(3，E:E2)-1

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

第一个参数使用3，表示COUNTA函数的计算规则，即第二个参数计算可见单元格的个数。

第二个参数使用动态扩展的范围E:E2，它将变成E:E3，E:E4，E:E5，…

公式始终计算从E列的第一行到公式所在行的可见状态的非空单元格的数量。结果减1，算出的结果和序号一样，筛选后可以够保持连续。

注意，注意，如果这个公式改成=SUBTOTAL(3，E:E2)，也就是从公式所在的行开始，序号结果没问题，但是最后一行在过滤时候会一直被Excel显示为汇总行。

2.筛选后倍增。

如下图所示，过滤E列后，需要计算数量乘以单价的总金额。

单元格E2的公式为:

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

要计算过滤后的乘积，关键问题是数据是否可见。

怎样判断这种可见的状态？

您需要将偏移和小计功可以结合起来。

首先使用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列内容。

最终目标是在过滤后提取最后显示的单元格的内容。

将提取的内容与&amp；”统计”链接，成为自动更新的表格标题。

好了，今天就到这里。这有点困难，请多包涵…