關(guān)于一個(gè)subtotal和offset數(shù)組公式的講解

roych

前幾天，群友彼德仔提出了一個(gè)關(guān)乎subtotal和offset的數(shù)組的問(wèn)題，冒昧揣測(cè)，我來(lái)簡(jiǎn)單解答下吧。如有不對(duì)，請(qǐng)指正。

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

先簡(jiǎn)單說(shuō)下內(nèi)層OFFSET部分吧。ROW(G3:G8)得到一個(gè)行標(biāo)的內(nèi)存數(shù)組：{3;4;5;6;7;8}，減去1自然得到{2;3;4;5;6;7}

我們知道，OFFSET是一個(gè)基于起點(diǎn)進(jìn)行偏移而獲取區(qū)域的函數(shù)。因此忙這就得到這樣一個(gè)區(qū)域（或者說(shuō)是內(nèi)存數(shù)組）：
G1往下移動(dòng)2-7行的區(qū)域。即：G3:G8的內(nèi)存數(shù)組（當(dāng)然是）。也就是：{"廣東";"云南";"廣東";"云南";"江西";"江西"}
需要注意的是，嚴(yán)格意義上，這并不是一個(gè)連續(xù)區(qū)域，而是6個(gè)相對(duì)獨(dú)立的元素構(gòu)成的集合�！�—這里涉及到多維數(shù)組，就不展開(kāi)了，有興趣的話(huà)可以去EH搜索下這方面的資料。
我們知道，SUBTOTAL只對(duì)顯示的行求和。那么只要顯示行不為空，那么SUBTOTAL就會(huì)得到一個(gè)值。
接下來(lái)，我們可以做個(gè)小測(cè)試。不篩選時(shí)使用SUBTOTAL，看看計(jì)算結(jié)果：
我們發(fā)現(xiàn)，全部是1。為什么呢？因?yàn)闆](méi)有篩選，所以SUBTOTAL對(duì)每個(gè)顯示的單元格都進(jìn)行了計(jì)數(shù)。
那么，在篩選后，沒(méi)有選上的單元格就會(huì)顯示為0，例如：


上面解釋了為什么會(huì)有0和1的問(wèn)題了。

再來(lái)解釋下match的作用，這里的match采用了缺省參數(shù)，表示的是精確匹配，并返回第一次出現(xiàn)的行號(hào)。這里用1來(lái)匹配，也就是說(shuō)獲取第一個(gè)匹配值的位置，結(jié)合上面來(lái)看，可以說(shuō)是顯示行的行標(biāo)。如上圖，得到的值是5（前面4個(gè)是0）。

而再用G2來(lái)偏移……這就不必解釋了吧（請(qǐng)參考第一部分）。于是得到G2偏移5行的位置，即G7。當(dāng)然，如果手動(dòng)刪除G7，則會(huì)匹配到G8。如果再刪除的話(huà)，那就返回NA了……因?yàn)榍懊鎚atch不到。

講到這里，照理應(yīng)該算是比較完整了。有興趣的還可以往下看：

我是分割線(xiàn)
--------------------------------------------------------------------------------



SUBTOTAL(3,OFFSET(G1,ROW(F3:G5)-1,))按F9為什么結(jié)果會(huì)是{1;0;1;0;0;0}想不明白？
我也想不明白，ROW得到是含有3個(gè)元素的集合，為什么彼德仔給的結(jié)果會(huì)是5個(gè)？【確定沒(méi)輸錯(cuò)？】
想明白的不妨回復(fù)下。
--------------------------------------------------------------------------------

pureshadow

目前沒(méi)有更簡(jiǎn)單的了

彼德仔

SUBTOTAL就是顯示數(shù)字

pureshadow

天下那么多公式不用，非要去折騰多維引用，那都是沒(méi)法子在一個(gè)平面正常顯示的東東，看不清就對(duì)了。
至于SUBTOTAL(3,OFFSET(G1,ROW(F3:G5)-1,))的結(jié)果為什么是{1;0;1;0;0;0}，我只能說(shuō)，肉一你用的Office大概不是地球版的，這公式在我那地球版的Office上顯示的結(jié)果就是三個(gè)值{1;0;1}

roych

pureshadow 發(fā)表于 2017-11-22 16:14
天下那么多公式不用，非要去折騰多維引用，那都是沒(méi)法子在一個(gè)平面正常顯示的東東，看不清就對(duì)了。
至于SU ...

那個(gè)貼圖用的是：SUBTOTAL(3,OFFSET(G1,ROW(G3:G8)-1,))
這個(gè)才是原貼。

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

pureshadow

如果是別人發(fā)的，那一定是爪誤打錯(cuò)字了；如果是你發(fā)的，我就直接懷疑你用的不是地球版的Office

彼德仔

roych 發(fā)表于 2017-11-23 11:40
那個(gè)貼圖用的是：SUBTOTAL(3,OFFSET(G1,ROW(G3:G8)-1,))
這個(gè)才是原貼。

是的，搞錯(cuò)，詳細(xì)看附件數(shù)據(jù)為準(zhǔn)。不好意思麻煩了大家

彼德仔

pureshadow 發(fā)表于 2017-11-22 16:14
天下那么多公式不用，非要去折騰多維引用，那都是沒(méi)法子在一個(gè)平面正常顯示的東東，看不清就對(duì)了。
至于SU ...

這公式是學(xué)習(xí)別人的，所以不明白。這個(gè)例子還有更好的、簡(jiǎn)單點(diǎn)的公式嗎？