[原创]excel函数常用技巧浅析(2011-7-23更新技巧五)

haosp

原帖由 chenhh803 于 2011-7-6 13:29 发表
技巧解析一：

  楼主 在技巧解析二中 讲解了利用subtotal函数返回12个一维平面，在这步运算中   SUBTOTAL(9,OFFSET(C67,,,ROW(1:12))，这个公式返回的是1到12月的按月依次累计销售金额，我用sum函数计算了一下，也可以用数组公式返回1到12月的按月依次累计销售金额，公式是   =SUM(OFFSET(C67,,,ROW(1:12)))，这是否说明SUM函数也支持三维引用呢？但在下一步判断时 =IF(SUM(OFFSET(C67,,,ROW(1:12)))>=F67,ROW(1:12))，这个公式却无法发挥判断结果了，而SUBTOTAL却可以的 ，对此该做何理解呢？各位也可以自己计算一下啊，解释一下啊。。谢谢。。。

haosp

原帖由 chenhh803 于 2011-7-6 13:29 发表
技巧解析一：

刚才发帖用颜色标注了一下公式，结果看不清楚，所以又改了一下 把公式颜色去掉了，把字体放大，以便诸位观帖。

  楼主 在技巧解析二中 讲解了利用subtotal函数返回12个一维平面，在这步运算中   SUBTOTAL(9,OFFSET(C67,,,ROW(1:12))，这个公式返回的是1到12月的按月依次累计销售金额，我用sum函数计算了一下，也可以用数组公式返回1到12月的按月依次累计销售金额，公式是  =SUM(OFFSET(C67,,,ROW(1:12))) 这是否说明SUM函数也支持三维引用呢？但在下一步判断时 =IF(SUM(OFFSET(C67,,,ROW(1:12)))>=F67,ROW(1:12))，这个公式却无法发挥判断结果了，而SUBTOTAL却可以的 ，=IF(SUBTOTAL(9,OFFSET(C67,,,ROW(1:12)))>=F67,ROW(1:12))   对此该做何理解呢？各位也可以自己计算一下啊，解释一下啊。。谢谢。。。

clockqg

我对这个问题的理解理是:
SUM确实也支持三维引用的计算,但它算的结果返回形式不是数组,无法再进行下一步的运算,只可以用在多个单元格中显示出来.
而SUBTOTAL则不同,它对这12个平面分别求和,并且以数组的形式返回,可以带入下一步的运算.
分别用F9看一下就知道区别了.
SUM(OFFSET(C67,,,ROW(1:14)))用F9看到的是:#VALUE!
SUBTOTAL(9,OFFSET(C67,,,ROW(1:14))) 用F9看到的是:{314036;624137;961228;1328814;1720208;2065349;2439252;2836957;3179012;3530908;3893334;4208114;4208114;4208114}

[ 本帖最后由 clockqg 于 2011-7-18 20:33 编辑 ]

haosp

原帖由 haosp 于 2011-7-18 16:25 发表

刚才发帖用颜色标注了一下公式，结果看不清楚，所以又改了一下 把公式颜色去掉了，把字体放大，以便诸位观帖。

  楼主 在技巧解析二中 讲解了利用subtotal函数返回12个一维平面，在这步运算中   SUBTOTAL(9,OFF ...

谢谢楼上兄弟释疑，用F9查看一下，在本例中sum确实是无法显示数组，已解惑！谢了

chenhh803

原帖由 clockqg 于 2011-7-18 20:28 发表
我对这个问题的理解理是:
SUM确实也支持三维引用的计算,但它算的结果返回形式不是数组,无法再进行下一步的运算,只可以用在多个单元格中显示出来.
而SUBTOTAL则不同,它对这12个平面分别求和,并且以数组的形式返回,可 ...

确实是如此的,这一段时间比较忙,天天在外面跑,很少用电脑上,只是偶尔用手机看一下,没时间来回答一些网友的疑惑,谢谢您的帮忙!

clockqg

原帖由 chenhh803 于 2011-7-18 22:37 发表

确实是如此的,这一段时间比较忙,天天在外面跑,很少用电脑上,只是偶尔用手机看一下,没时间来回答一些网友的疑惑,谢谢您的帮忙!

楼主,不用太客气,助人仍快之本,您不也是有着这样的胸怀在帮我们大家进步吗,而且帮别人释疑,我自己也理解更深刻了.
haosp兄,让我们一起学楼主学习,一起进步.

clockqg

原帖由 chenhh803 于 2011-7-15 08:51 发表

你所说的方法我重新更新了附件,用INDEX函数是要加N(IF({1},)结构的,否则生成的不是内存数组,至于这种现像暂时无法解释,

楼主,有个问题还想请教一下,就是piny版主的N(IF(1,))结构和N(IF({1},)结构有没有什么区别呀? 我以前好发现了区别,最近又觉得没什么区别了!困惑!!

zhangzixin2008

支持楼主的帖子!!!!

tywx

感谢分享,很有启发

snoopy_cn

学习了，太有收获了，谢谢