急求_offset函数出错...

丢丢表格

tshaer 发表于 2013-10-23 10:48
既然OFFSET返回的是多维数组,那么它的运算结果就不应该在二维表的单元格中显示出来啊
那怎么最后的结果却 ...

这东西是很抽像的，真正要讲清，必须从MACTH 函数模块语言来分析，我们手中没有这个资料，只不过是个人的领悟吧了。
别急，在论坛中学习的时间长了，碰到问题多了，自然会开巧的…………
呵呵

Bodhidharma

這個問題涉及到的知識點很多，因此乍看之下會很難理解，以下分別解釋
1.OFFSET的參數中出現數組，則變成多維引用，是「天上」的東西
首先把你的公式改成

1. =MATCH(2,OFFSET($A$1:$E$1,ROW(1:1)-1,),)

复制代码

下拉，得到的依然是{2;1;#N/A;1}，不過本質上，OFFSET回傳的是「天上」的東西
用一般的函數是無法取得其值的，因此若按F9，得到的會是#N/A，不過因結果剛好為單一值，因此可在儲存格中顯示
主要是因為ROW(1:1)返回的是{1}，使OFFSET變成多維引用
一般的做法是將OFFSET的部分改為OFFSET($A$1:$E$1,SUM(ROW(1:1))-1,)
或是IF(1,OFFSET($A$1:$E$1,ROW(1:1)-1,))，則整個函數返回的內存值就會變成正常
相關的討論有幾個比較值得參考的：
[求助] 嵌套于offset函数中等值的row和rows函数，为何在sumproduct运算时有截然相反的结果
函数的深入理解(已将头三个函数附件找到) 中OFFSET的部分(附件可在http://club.excelhome.net/forum. ... d=31546&pid=7211551下載)
[函数讨论系列3] SUM的三维引用计算
2.多單元格數組公式，遇到不支援「數組形式/多維引用」的函數時，其內存會以『「不被支援的數組」最左上的值』為公式參數返回
match的第二參數不支援多維引用，因此

1. =MATCH(2,OFFSET($A$1:$E$1,ROW(1:4)-1,),)

复制代码

返回的內存會是=MATCH(2,OFFSET($A$1:$E$1,ROW(1:1)-1,),)的結果
因此按F9的話會是#N/A，同理若將公式改為

1. =MATCH(2,IF(1,OFFSET($A$1:$E$1,ROW(1:4)-1,)),)

复制代码

則返回的內存會是=MATCH(2,IF(1,OFFSET($A$1:$E$1,ROW(1:1)-1,)),)，按F9會顯示2
另舉一例，=INDEX(A1:E4,{1;2;3;4},)，因為INDEX的第二參數不支援數組形式
因此返回的內存會是=INDEX(A1:E4,{1},)，按F9顯示{1,2,23,67,42}

3.在上述情況中，多單元格數組公式各單元格顯示的值，會依其單元格所在位置與「不被支援的數組」一一對應
你原本的公式=MATCH(2,OFFSET($A$1:$E$1,ROW(1:4)-1,),)，輸入在任意4行1列的單元格
第一個單元格會顯示=MATCH(2,OFFSET($A$1:$E$1,ROW(1:1)-1,),)，第二個顯示=MATCH(2,OFFSET($A$1:$E$1,ROW(2:2)-1,),)……
也就是4行分別與ROW(1:4)一一對應
=INDEX(A1:E4,{1;2;3;4},)輸入到4行n列的單元格，則第1行會對應到=INDEX(A1:E4,{1},)，第2行對應到=INDEX(A1:E4,{2},)
不同列中，發生的是行列擴充，因此第1行每個單元格的內存都會是{1,2,23,67,42}(INDEX第二參數的1往右擴充)
顯示在單元格上就會是1，因此第一行第二列的顯示值仍然是1，不會變成B1的2
如果是=INDEX(A1:E4,{1;2;3;4},{1,2})的話，則第一行第一列對應到=INDEX(A1:E4,{1},{1})，第一行第二列對應到=INDEX(A1:E4,{1},{2})
第二行第一列對應到=INDEX(A1:E4,{2},{1})……以此類推，不過按F9的話永遠都是顯示第一行第一列的內存，也就是1
這個問題在論壇中討論的比較少，我所知道唯一的探討文章可見
[分享] OFFSET数组化 撞出 array 级别的参数数组化
在該篇中胡板以「array級別的參數數組化」來稱呼你感到疑惑的現象
不過閱讀的時候要注意裡面有許多「胡式語言」，有些東西我並不贊同，可搭配下篇閱讀
http://club.excelhome.net/forum. ... =987388&pid=7130337
(以上兩篇比較複雜，請小心服用)

INDEX和VLOOKUP的參數不支援數組的問題，現在已經可用piny發現的N+IF(1...)解決
但是像match這種不支援多維引用為參數的問題，目前基本上無解，無法直接轉為想要的內存數組
因此如果一定要構造你想要的內存數組的話，必需要換思路
比方說丟丟表格就提供了以「附加行列訊息」的方式構造內存數組的方法

以上回答，同時也解釋了你先前問的
[求助] index函数参数的横向一维数组...中，
INDEX第二參數為數組時，儲存格顯示與F9為何不同的問題
希望我講的夠清楚…如果有其它問題歡迎繼續提出討論~

tshaer

Bodhidharma 发表于 2013-10-25 11:19
這個問題涉及到的知識點很多，因此乍看之下會很難理解，以下分別解釋
1.OFFSET的參數中出現數組，則變成多 ...

谢谢Bodhidharma的耐心解答
还在慢慢解读帖子中.. 键盘坏掉 用的屏幕键盘打字太费劲  另回

毕释理

Bodhidharma 发表于 2013-10-25 11:19
這個問題涉及到的知識點很多，因此乍看之下會很難理解，以下分別解釋
1.OFFSET的參數中出現數組，則變成多 ...

好帖子，收藏慢慢消化

yqdaqinqin

Bodhidharma 发表于 2013-10-25 11:19
這個問題涉及到的知識點很多，因此乍看之下會很難理解，以下分別解釋
1.OFFSET的參數中出現數組，則變成多 ...

茅塞顿开，非常精辟