offset函数 offset函数什么意思

偏移函数(偏移函数是什么意思)

好了，今天分享一篇关于筛选状态下计算例程的文章。

1.筛选后添加序列号。

如下图所示，为了在筛选状态下保持连续的序列号，我们可以先取消筛选，在D2单位的深圳生活网格中输入以下公式，然后下拉:

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

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

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

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

公式始终计算从第一行到公式所在行的区域中处于可见状态的非空单元格的数量。如果从结果中减去1，计算结果将与序列号相同，并且在筛选后将保持连续。

注意，请注意，如果将此公式从=SUBTOTAL(3，E: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 & gt；3))

前半部分的计算原理与上例相同，核心是判断单元格是否可见。

公式后半部分的统计条件(G4: G16 >: 3)将其与前半部分的判断结果相乘，表示两个条件同时满足，即可见状态的G列数大于3。

4.过滤后自动更正标题。

如下图所示，对E列的部门名称进行过滤后，希望单元格D1的标题自动改为对应的部门名称，公式为:

=LOOKUP(1，0/SUBTOTAL(3，OFFSET(D1，ROW(1:15)-1))，E:E)amp；\"统计表”

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列在相应位置的内容。

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

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

好了，今天就到这里。有点难，忍忍吧...

　　免责声明：本文由用户上传，与本网站立场无关。财经信息仅供读者参考，并不构成投资建议。投资者据此操作，风险自担。 如有侵权请联系删除！