嵌套于offset函数中等值的row和rows函数，为何在sumproduct运算时有截然相反的结果

hhjjpp

公式=SUMPRODUCT(F$3:F$5,OFFSET($A$9,,ROWS(A$1:A1)-1,3))是正确的，为何
公式=SUMPRODUCT(F$3:F$5,OFFSET(A$9,,ROW(A1)-1,3))得到错误结果
OFFSET(A$9,,ROW(A1)-1,3)的结果为何只有一个数，而前者是三个数
ROW(A1)-1改成常量0也会得到正确的三数数组
测试结果详见附件

丢丢表格

OFFSET(A$9,,ROW(A1)-1,3)
它得出来的是个多维数组。

OFFSET(A$9,,ROWS(A1：A1)-1,3)
是一维数组
全完不同的东东啊

丢丢表格

想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组，降为一维数组，方法很多：
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这就是其中的一种.

hhjjpp

丢丢表格 发表于 2012-6-17 20:23
想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组，降为一维数组，方法很多：
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这 ...

多谢，多谢！

EXCEL-ZYY

OFFSET(A$9,,SUM(ROW(A1))-1,3)   大侠们可以解释一下这个是麻意思嘛   读不懂哇

hhjjpp

EXCEL-ZYY 发表于 2013-9-11 14:48
OFFSET(A$9,,SUM(ROW(A1))-1,3)   大侠们可以解释一下这个是麻意思嘛   读不懂哇

看似画蛇添足的sum会将其降为一维数组

天地有雪

丢丢表格 发表于 2012-6-17 20:23
想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组，降为一维数组，方法很多：
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这 ...

问一下，同样有降维效果的N(),为什么就不行了呢？

Bodhidharma

丢丢表格 发表于 2012-6-17 20:23
想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组，降为一维数组，方法很多：
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这 ...

另外看過的用法是加個IF(1,...)

1. IF(1,OFFSET(A$9,,ROW(A1)-1,3))

复制代码

也可以達到降維的效果

Bodhidharma

天地有雪 发表于 2013-9-11 15:09
问一下，同样有降维效果的N(),为什么就不行了呢？

N可以「降維」的說法，其實並不精確，這點草板有過精彩的解說：
http://club.excelhome.net/thread-494347-1-1.html
http://www.exceltip.net/thread-17204-1-1-2.html

N是一個很神奇的函數，很多人都對他有所誤解，以下稍做解釋：(這裡A1=1,A2=2,A3=3)
閱讀時請注意「引用」及「數組」的差異，另外回傳值{1}和1也是不同的，這點多數人通常會忽略掉

公式	回傳值	說明
N(A1)	1	取引用最左上的值
N(A1:A3)	1	取引用最左上的值
N({1;2;3})	{1;2;3}	當N的參數為數組時，會分開計算
N(VALUE(A1:A3))	{1;2;3}	數組公式，VALUE將A1:A3這個「引用」轉換為{1;2;3}的「數組」
N({1})	{1}	當N的參數為數組時，會回傳數組
N(ROW(A1))	{1}	ROW(A1)返回的是數組{1}
N(OFFSET(A1,0,))	1	OFFSET(A1,0,)回傳A1
N(OFFSET(A1,,,3,))	1	OFFSET(A1,0,,,3,)回傳A1:A3
N(OFFSET(A1,{0},))	{1}	當OFFSET的參數為數組時，就已經是多維引用，可視為回傳{A1}這個「天上的」引用(或著說是以引用為元素的「區域數組」)，N支持多維引用，故仍返回最左上方的值
N(OFFSET(A1,{0},,{3}))	{1}	同上，OFFSET回傳{A1:A3}
N(OFFSET(A1,{0;1;2},))	{1;2;3}	同上，OFFSET回傳{A1;A2;A3}，這個是一般人「降維 」說法的來源
N(OFFSET(A1,{0;1;2},,3,))	{1;2;3}	OFFSET回傳{A1:A3;A2:A4;A3:A5}，N取各引用最左上的值
N(OFFSET(A1,,,{1;2;3},))	{1;1;1}	OFFSET回傳{A1;A1:A2;A1:A3}，N取各引用最左上的值

之所以用N不行，是因為當OFFSET的參數為數組時，就已經是多維引用，而N(ROW(A1))回傳的仍是數組
至於OFFSET和IF(1...)的關係，這又是一個非常複雜的議題，有空的話再解釋
(可以參考http://club.excelhome.net/thread-709510-1-1.html，不過胡板沒有另外說明，因此大多數人應該都看不太懂)

至於N用在一些多單元格數組公式的時候，就會變的更加複雜，已經超出我的能力
比方說http://club.excelhome.net/thread-781636-1-1.html我就無法清楚解釋
另外N(IF(1...))這個特殊用法的原理，我也無法清楚解釋…
http://club.excelhome.net/thread-681243-1-1.html
http://club.excelhome.net/thread-987388-1-1.html


以上是我的理解，未必完全正確，歡迎大家繼續討論
N_explanation.rar (6.6 KB, 下载次数: 112)

2013-9-11 16:37 上传

点击文件名下载附件

Bodhidharma

剛剛發現了一個之前沒有注意過的現象：
D9公式

1. =SUMPRODUCT(F$3:F$5,OFFSET(A$9,,ROW(A1)-1,3))

复制代码

若改為

1. =SUMPRODUCT(F$3:F$5,N(OFFSET(A$9,,ROW(A1)-1,3)))

复制代码

仍然返回錯誤值，跟我的預設有點不一樣

N(OFFSET(A$9,,ROW(A1)-1,3))返回的是A9，也就是{1}
(不是A9:A11，N函數達成的不是單單的降維，這個在前篇應該講得很清楚了)
我原本以為跟據自適應性，SUMPRODUCT(F$3:F$5,{1})會自動擴充為SUMPRODUCT(F$3:F$5,{1;1;1})
因此會回傳900(當然這個數字在邏輯上是錯誤的)
但是實際上的狀況卻返回錯誤值……


稍微試了一下

1. =SUMPRODUCT(F$3:F$5*N(OFFSET(A$9,,ROW(A1)-1,3)))

复制代码

就會返回我預設的答案(900)

也就是說，SUMPRODUCT的參數與參數間，並不存在行列擴充，這點跟OFFSET、COUNTIF、RANK的三維引用的情形有所不同
參見：http://club.excelhome.net/forum. ... =146260&pid=7124339
以及：http://club.excelhome.net/forum. ... d=98081&pid=7166973
只有在SUMPRODUCT(條件一*條件二*...)的用法中，才會出現行列擴充
這個倒是跟MMULT比較相似，MMULT是array1的列數一定要等於array2的行數
SUMPRODUCT則是array1,array2,...的行列數一定要完全相同
兩個函數都不會在函數內部(參數與參數間)自動進行行列擴充

补充内容 (2017-7-18 16:34):
sumproduct之所以不存在行列擴充，是因為其參數為array，而不是單值或是引用的關係。