[求助]offset应用

山菊花

函数公式在运用三维引用中，如本例，可以说是Excel的一个BUG，至少也可以说它发育不健全。

1、一厢情愿的三维引用

一点都不奇怪，我们认为，OFFSET(A1,{1,3,8,14},,,2)它返回一个三维引用，外加SUM()应该求出所有蓝色区域的和:

=SUM(OFFSET(A1,{1,3,8,14},,,2))

但事实上不是这样，选择该公式所在单元格，点击菜单“工具—公式审核—公式求值”，点击“求值”按钮，可以看到，公式中的数组“{1,3,8,14}”莫名其妙地变成了“1”，仅保留第一个值而丢弃了其它，使得公式的结果相当于：

=SUM(A2:B2)

2、半推半就的三维引用

在进行SUM()计算前运用SUMIF()计算出各“层”的和：

=SUM(SUMIF(OFFSET(A1,{1,3,8,14},,,2),"<>0"))

这时，同样打开“公式求值”工具，点击“求值”按钮，依次看到如下结果：

=SUM(SUMIF({#VALUE!,#VALUE!,#VALUE!,#VALUE!},"<>0"))

=SUM({30,25,42,78})

#VALUE!是因为不能按正常方式显示三维数据，但它已经按我们的意愿返回了三维引用，后面的结果可以证明。

除了SUMIF()外，SUBTOTAL()函数也可以达到同样的效果：

=SUM(SUBTOTAL(9,OFFSET(A1,{1,3,8,14},,,2)))

记住，遇到类似尴尬的时候，试试SUMIF()、SUBTOTAL()函数，会有意外的惊喜。

再举一例，求Sheet1、Sheet2、Sheet3三个工作表A1:A10区域中的最小值。

如果三个工作表连续排列，可用公式：

=MIN(Sheet1:Sheet3!A1:A10)

如果三个工作表不连续，自然会想到，使用公式：

=MIN(INDIRECT("sheet"&{1,2,3}&"!a1:a10"))

但得到的结果只是Sheet1!A1:A10中的最小值，正确的公式是：

=MIN(SUBTOTAL(5,INDIRECT("sheet"&{1,2,3}&"!a1:a10")))

3、异曲同工的多维引用

如果对本话题感兴趣，你一定得关注一下关于N()、T()函数的讨论与应用，下面是其中一个有代表性的帖子：

[函数应用系列讨论14] 关于N()/T()函数的返回值问题

山菊花

SUBTOTAL的三维引用
  
作者：apolloh   浏览：   回复：  →   Excel 函数与公式
  2005-4-28 11:43:00  [精华]
 

N函数是个擦鞋的

[此贴子已经被作者于2008-2-14 10:56:27编辑过]

BobPan

谢谢山菊花老师再次讲解！
以前真不知道=MIN(Sheet1:Sheet3!A1:A10)这种写法，又学到了一招!

lytton_lee

谢谢山版深入的讲解，相信我们一定会努力汲收消化，不辜负版主们的耐心与执着！辛苦啦，山版主！！

白雪飘

谢谢版主的详细讲解！

linyiyyj

还是不明白为什么三维引用会达不到想要的效果，山版说的BUG，发育不健全是什么意思，能否解答一下。

yoka

解释的够详细,谢谢山版!

wanglei569

谢谢山大叔

wddn

修改一下7楼的公式，也不知是否正确：

=SUM(SUBTOTAL(9,OFFSET(A1:B1,{1,3,8,14},)))

山菊花

原帖由 linyiyyj 于 2008-1-23 14:07 发表 还是不明白为什么三维引用会达不到想要的效果，山版说的BUG，发育不健全是什么意思，能否解答一下。

BUG就是Excel程序存在缺陷或问题，是程序设计得不够好，用起来不够如意。发育不健全是强调的意思。 D608的旅行充，也是一个存在BUG的产品。插入电池后，红灯不会亮，加上一根牙签后，问题解决：

[求助]offset应用