关于一个subtotal和offset数组公式的讲解

roych

前几天，群友彼德仔提出了一个关乎subtotal和offset的数组的问题，冒昧揣测，我来简单解答下吧。如有不对，请指正。

原公式：
=OFFSET(G2,MATCH(1,SUBTOTAL(3,OFFSET(G1,ROW(G3:G8)-1,)),),)【见附件的F1单元格】

先简单说下内层OFFSET部分吧。ROW(G3:G8)得到一个行标的内存数组：{3;4;5;6;7;8}，减去1自然得到{2;3;4;5;6;7}

我们知道，OFFSET是一个基于起点进行偏移而获取区域的函数。因此忙这就得到这样一个区域（或者说是内存数组）：
G1往下移动2-7行的区域。即：G3:G8的内存数组（当然是）。也就是：{"广东";"云南";"广东";"云南";"江西";"江西"}
需要注意的是，严格意义上，这并不是一个连续区域，而是6个相对独立的元素构成的集合。——这里涉及到多维数组，就不展开了，有兴趣的话可以去EH搜索下这方面的资料。
我们知道，SUBTOTAL只对显示的行求和。那么只要显示行不为空，那么SUBTOTAL就会得到一个值。
接下来，我们可以做个小测试。不筛选时使用SUBTOTAL，看看计算结果：
我们发现，全部是1。为什么呢？因为没有筛选，所以SUBTOTAL对每个显示的单元格都进行了计数。
那么，在筛选后，没有选上的单元格就会显示为0，例如：


上面解释了为什么会有0和1的问题了。

再来解释下match的作用，这里的match采用了缺省参数，表示的是精确匹配，并返回第一次出现的行号。这里用1来匹配，也就是说获取第一个匹配值的位置，结合上面来看，可以说是显示行的行标。如上图，得到的值是5（前面4个是0）。

而再用G2来偏移……这就不必解释了吧（请参考第一部分）。于是得到G2偏移5行的位置，即G7。当然，如果手动删除G7，则会匹配到G8。如果再删除的话，那就返回NA了……因为前面match不到。

讲到这里，照理应该算是比较完整了。有兴趣的还可以往下看：

我是分割线
--------------------------------------------------------------------------------



SUBTOTAL(3,OFFSET(G1,ROW(F3:G5)-1,))按F9为什么结果会是{1;0;1;0;0;0}想不明白？
我也想不明白，ROW得到是含有3个元素的集合，为什么彼德仔给的结果会是5个？【确定没输错？】
想明白的不妨回复下。
--------------------------------------------------------------------------------

pureshadow

目前没有更简单的了

彼德仔

SUBTOTAL就是显示数字

pureshadow

天下那么多公式不用，非要去折腾多维引用，那都是没法子在一个平面正常显示的东东，看不清就对了。
至于SUBTOTAL(3,OFFSET(G1,ROW(F3:G5)-1,))的结果为什么是{1;0;1;0;0;0}，我只能说，肉一你用的Office大概不是地球版的，这公式在我那地球版的Office上显示的结果就是三个值{1;0;1}

roych

pureshadow 发表于 2017-11-22 16:14
天下那么多公式不用，非要去折腾多维引用，那都是没法子在一个平面正常显示的东东，看不清就对了。
至于SU ...

那个贴图用的是：SUBTOTAL(3,OFFSET(G1,ROW(G3:G8)-1,))
这个才是原贴。

楼主想不明白，我也想不明白啊

pureshadow

如果是别人发的，那一定是爪误打错字了；如果是你发的，我就直接怀疑你用的不是地球版的Office

彼德仔

roych 发表于 2017-11-23 11:40
那个贴图用的是：SUBTOTAL(3,OFFSET(G1,ROW(G3:G8)-1,))
这个才是原贴。

是的，搞错，详细看附件数据为准。不好意思麻烦了大家

彼德仔

pureshadow 发表于 2017-11-22 16:14
天下那么多公式不用，非要去折腾多维引用，那都是没法子在一个平面正常显示的东东，看不清就对了。
至于SU ...

这公式是学习别人的，所以不明白。这个例子还有更好的、简单点的公式吗？