ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 6507|回复: 14

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2012-6-17 20:16 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:N
本帖最后由 hhjjpp 于 2012-6-17 20:17 编辑

公式=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也会得到正确的三数数组
测试结果详见附件

h.rar

4.41 KB, 下载次数: 116

点评

知识树索引内容位于9楼、10楼  发表于 2014-4-25 00:40

TA的精华主题

TA的得分主题

发表于 2012-6-17 20:21 | 显示全部楼层
OFFSET(A$9,,ROW(A1)-1,3)
它得出来的是个多维数组。

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

TA的精华主题

TA的得分主题

发表于 2012-6-17 20:23 | 显示全部楼层
想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组,降为一维数组,方法很多:
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这就是其中的一种.

评分

参与人数 1鲜花 +1 收起 理由
wswfzwswfz + 1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-6-18 14:23 | 显示全部楼层
丢丢表格 发表于 2012-6-17 20:23
想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组,降为一维数组,方法很多:
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这 ...

多谢,多谢!

TA的精华主题

TA的得分主题

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

TA的精华主题

TA的得分主题

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

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

TA的精华主题

TA的得分主题

发表于 2013-9-11 15:09 | 显示全部楼层
丢丢表格 发表于 2012-6-17 20:23
想把OFFSET(A$9,,ROW(A1)-1,3)这个多维数组,降为一维数组,方法很多:
OFFSET(A$9,,SUM(ROW(A1))-1,3) 这 ...

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

TA的精华主题

TA的得分主题

发表于 2013-9-11 15:20 | 显示全部楼层
丢丢表格 发表于 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))
复制代码
也可以達到降維的效果

TA的精华主题

TA的得分主题

发表于 2013-9-11 16:37 | 显示全部楼层
本帖最后由 Bodhidharma 于 2013-9-11 18:13 编辑
天地有雪 发表于 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, 下载次数: 90)

评分

参与人数 2鲜花 +4 收起 理由
micch + 2 感谢帮助
天地有雪 + 2 感谢帮助

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-9-11 23:29 | 显示全部楼层
本帖最后由 Bodhidharma 于 2013-9-11 23:52 编辑

剛剛發現了一個之前沒有注意過的現象:
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,而不是單值或是引用的關係。

评分

参与人数 2鲜花 +3 收起 理由
geiwoxingfu007 + 1
451856787 + 2

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-8-25 22:40 , Processed in 0.101713 second(s), 15 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表