OFFSET数组化 撞出 array 级别的参数数组化

胡剑0227

原帖由 laoshenji 于 2010-9-24 10:10 发表
能得到胡版主的关注和解答十分荣幸。为什么这样说呢？因为此前我也曾在本论坛求助过类似问题，然而要不是无人问津，就是避而不答。唯有您胡版主敢于挑战并热心解答这个被称之为“老大难”的问题，由此可见您的功底有 ...

呵呵，先谢谢你的恭维，说实在有点吃不消了，呵呵。

你说的那个 月份 什么写在后面，就....暴露了一个问题，你函数公式的思路还不是非常清晰。我之所以横向把月份列出来，然后用一个OFFSET来实现...稍后再续...

[ 本帖最后由 胡剑0227 于 2010-9-24 12:18 编辑 ]

laoshenji

胡版主过谦了，您说您吃不消也是大实话，因为你工作很忙！但您的会员所提出的问题咋说也要管到底啥？不是说我的“恭维”就能得到您的同情和答复，那是肺腑之言。可想而知，任何人在困难的时候都渴望能得到别人的帮助！还是谢谢您抽点时间帮我把那几个问题解决了，我将耐心等待您的佳音！

胡剑0227

然后 用 OFFSET来生成一个“有效工作表名”的水平数组只是为了看上去更加可读一点。OFFSET这块的功能就是生成一个 工作表名的水平数组，具体你想怎么实现都可以的。

这里的关键应该在于 SUMIF 对多表引用区域的汇总问题。
关于这个问题，不仅单条件可以简单实现，而且多条件的汇总也是可以用几乎相同的方式实现的，就是把里面的SUMIF换成SUMIFS（2007专用的函数，多表多条件汇总具有不可替代的优势）

具体例子可以下载

[ 本帖最后由 胡剑0227 于 2010-9-24 12:21 编辑 ]

胡剑0227

原帖由 laoshenji 于 2010-9-24 10:10 发表
一是，按您78楼的公式，将工作表名增至2010.12后，从2010.10就不能计算“累计”了（见附件1）

格式，自定义格式只是影响数据的显示，在编辑栏中可以查看真实的数据并没有改变。虽然你的 2010.10 ,看上去像是 2010.10，但是在编辑栏中依然是数据 2010.1，而工作表名称没有这个2010.1，只有2010.10，所以就引用无效了。#REF。

改变，你把对应的单元格进行修改就可以了，先输入半角逗号用于限定单元格数据类型为文本，然后再输入 2010.10 这样才是真正的文本 2010.10。
具体你看看附件吧。

[ 本帖最后由 胡剑0227 于 2010-9-24 13:12 编辑 ]

胡剑0227

原帖由 laoshenji 于 2010-9-24 10:10 发表
二是，由于我们是干财务工作的，您的“累计”公式需要横排工作表名进行辅助才能实现，这有点不符合财务核算要求的表格格式。我将工作表名改为竖排并修改公式后（见附件2），压根就不能求出“累计”了（至于“2010.10”不能常规输入，是否会影响公式运算尚不得知）。

格式的问题我上面提过了，会影响，但是跟你说的问题关系不大，你的问题还包括更基础的对OFFSET，数组的十字交叉的问题。听不懂也没有关系...

OFFSET有五个参数，依次可以理解为  参照点、行偏、列偏、区域高度（几行）、区域宽度（几列）。

我原来的表格中，工作表名是横排的，所以 MATCH返回的部分用做 区域宽度 。你目前将 工作表名 竖排，你觉得 是否应该把 MATCH 返回的部分 用做 区域高度 ？这个是一点。

另外，A:A 这个数据区域的方向是 垂直方向的，我要多表中引用这个区域，所以我的工作表名必须 水平方向的，因此 整个 OFFSET 返回的工作表名称 内容我必须 转置成 水平方向的。附件中我是通过 TRANSPOSE实现的。 这个是第二点。

第三点，我想可能是你的笔误，MMULT函数进行行汇总，那么第二参数是一个 长度合适的全1数组，而你在那个参数部分使用了 *0，而不是 ^0 （任何数的0次方得1），所以也是一个错误的地方。当然这个应该是笔误了，或键盘误了，呵呵。

具体你看看附件。

胡剑0227

第三点，虽然对易失性的影响传递没有认真研究过，但我觉得不是用了MMULT就能解除易失性影响的。MMULT的参数如果使用了易失性的函数，那么一般理解将难以根除。

目前当月的公式使用了多单元格的数组公式，应该比一般的公式有了较大提升，你先试试吧。

laoshenji

万分感谢胡版主牺牲休息时间，帮我做了个了完整的答案。我用的是2003，2007的公式用不上。您的详细讲解和纠错，使我收获不小，真可谓“听君一席话，胜读十年书”。说实话，OFFSET函数和MMUTL函数恰恰是我的弱项，所以在今天向您多讨教了一些这方面的知识。
再就是请教一个问题：您在86楼答案（竖排）中，把序号由原来的一列增加至两列，它有什么特殊含义和用途？如果没有，我拟将它删去，因为它影响了表格格式，请明示。谢谢！

胡剑0227

序号，应该我没有用到，也没有留意，呵呵。你删除好了。

另外不用那么客气。

foodorwater

原帖由 lhx120824 于 2010-9-10 15:42 发表
胡版是个勤奋多产的热心版主，讲授浅显易懂，切合实际。是我们初学者的福分。

深有同感啊。。。。。。。。。。

laoshenji

知道了，谢谢胡版主。
我也有苦衷啊，50多岁了才开始学习 Excel函数与公式，看到别人用VBA速度快，虽然也曾有过学习VBA的冲动却不敢涉足。毕竟大脑不够用了，为学习和钻研 Excel函数与公式，本人还患上了神经衰弱症，今年不得不换了一个小一点的公司打工。我要是再转去十几年就好了，岁月不饶人啦！所以这次给胡版主添这么多的麻烦，还望您多多包涵。若不是您亲自作答，可以说这些问题根本就解决不了，因为我只能用函数解决问题。