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

胡剑0227

            用数组基本知识浅析OFFSET数组化应用

导语：

        OFFSET函数的返回值是引用区域，当其参数为数组时得到的返回值也是数组，数组的每个元素即是对应的引用区域-----参数驱动

        数组的各引用区域元素是分时隔离的-----分时特性

        使"array"形式的参数数组化成为可能-----参数驱动

时间紧的看导语就够了...
========================================================================================


1.参数驱动

        当函数的参数，或者运算符的操作数由单值升级为对应的数组时，就得到对应函数或运算的数组用法。

         这是一个指导性思想，有例行自然也会有例外，重要的是我们根据这个原理可以直接发开很多函数的数组用法。以lookup为例，对lookup第一参数进行数组化的结果如下图所示。
                       


                以上作为参数驱动的基础知识，下面我们就来探究OFFSET的数组化应用。先上一个表，作为讲述材料，大家可以直接下载附件以更好的理解。
                       
OFFSET基本用法

                       
数组用法（对行偏、列偏组成一个数组）
                       

             上图中，通过对OFFSET第2、第3参数的数组化得到一个数组，在单元格中显示的值为#VALUE，那么究竟这个#VALUE是否就代表了所代表的引用区域呢？
            通过SUM函数验证，在数值上是完全匹配的，也即证实了各#VALUE就是代表了不同的引用区域。因此，整个数组返回值的逻辑关系如下所示。
                       

我们不妨称之为 引用数组的双平面结构 现在想想用“大颗粒数组”应该更为合适

由于平时我们习惯了返回值对应一个单元格颗粒的数据，因此思维上受到禁锢，我们要挣脱这个束缚，让思想自由的飞翔！
                  

2.分时特性

        从上面的SUM验证我们还得到一个信息，数组的各个引用区域元素是分时独立的，也即他们并不是同时存在的。如果同时存在，那么依照SUM函数的机制，我们有理由相信它会将所有的数据进行汇总，而不会像现在这样进行隔离汇总。
            这个特性也不是OFFSET所独有的，在INDEX函数的数组特性用法中就有出现，如下图所示。当行参数是数组，列参数为0时就能分时取第一参数的多行，外套一个SUM函数时就成了按行汇总。                           
                           
                     直接按F9只能得到第一行的数据，但公式赋值到单元格的过程远比按F9要复杂。我们可以简单认为一个F9就是得到了一次计算，在落入多个单元格中时进行了多次计算。       

           我们通过下面的例子可以看的更加清楚一些。
                     
                      打开附件，在“分时特性”工作表，我们可以直接用F9来测试返回值，返回值为{1,2;3,4}，正好对应OFFSET第一组参数说指向的引用区域，即公式 =IF(1,OFFSET($A$11,0,0,2,2)) 所指向的区域。我们可以理解为OFFSET的第一节拍返回值；虽然我无法通过F9等手段在时间上切片查看第二节拍、第三节拍、第四节拍的状态，但是通过整个IF函数的返回值状态我们有理由相信它确实是分了四个节拍来返回的。因为我们可以很合理的进行如下解释：

第一节拍：C14单元格
                       时间切片上，OFFSET返回值为{1,2;3,4}

                                        空间对应上，C14处于第1行、第1列位置

                       于是返回：1
第二节拍：D14单元格
                       时间切片上，OFFSET返回值为{10,20;30,40}

                                        空间对应上，D14处于第1行、第2列位置

                       于是返回：20

第三节拍：C15单元格
                       时间切片上，OFFSET返回值为{100,200;300,400}

                                        空间对应上，C15处于第2行、第1列位置

                       于是返回：300

第四节拍：D15单元格
                       时间切片上，OFFSET返回值为{1000,2000;3000,4000}

                                        空间对应上，D15处于第2行、第2列位置

                       于是返回：4000


在数组班第一课中我讲过这样一句话：数组公式，它在时域上是一个遍历循环，在空间上是一个并行输出。

常规的函数，这些时间上的节拍是觉察不到的，而像OFFSET数组化的情况，时间上的节拍是以公式宿主单元格为分割的。这一点可能是因为EXCEL的工作表是二维的，因此直接把多个引用区域结成一片并没有多大意义的关系。        

OFFSET函数参数有五个，各个参数都可以进行数组话，上述例子只是将行偏、列偏进行了数组化处理，但本质是一样的。即得到一个返回值数组，其元素都是引用区域！

分时隔离的猜想更接近本质，我们要从本质入手才能更清晰地理解某些特性。


     

3.array数组化

根据参数驱动原理，当参数升级为对应的数组时就得到了数组用法。OFFSET函数的数组化返回值使得array级别的参数数组化成为可能。我们简要给一个例子。
              

这是一个将VLOOKUP函数的第二参数数组化的例子，大家可以自己去尝试更多的应用。

为了让大家更接受 引用区域数组 的概念，我们再演练一个数组运算中常用的十字交叉法。
              


OFFSET数组化应用产生的数组返回值就是论坛中被称为的多维引用，在空间排布上这样称呼非常合适，它在时间上是分时隔离的，当然它的本质应该是一个数组，其元素是引用区域。只有这样在观念上改变过来，结合参数驱动的思想才能为数组公式打开一块新的天地。

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

sunbin200388

胡版又要出精品了，还没看，先下载，顶下先。

lhx120824

胡版是个勤奋多产的热心版主，讲授浅显易懂，切合实际。是我们初学者的福分。

MicrosoftEH

多谢版主！！
能听懂一小部分，大部分因自身水平低下没有听懂

胡剑0227

原帖由 MicrosoftEH 于 2010-9-10 15:51 发表
多谢版主！！
能听懂一小部分，大部分因自身水平低下没有听懂

有些东西自己去测试一下就会深刻很多。
对于上过数组特辑班的同学只要将上课讲到的 参数驱动，十字交叉，进行活用就可以了。根据这两个数组最基本的法则自动的去推演就是了。不仅理解OFFSET可以这样，开发其他函数的数组用法也是相同的。比如数组第三课（飞跃）就是把这些简单的原理用到了IF、CHOOSE函数而已。

ymiybpdcdp

谢谢胡版，好好学习一下。

胡剑0227

看这个帖子需要一定的数组公式基础，看的时候把握总体思路...

[ 本帖最后由 胡剑0227 于 2010-9-10 20:56 编辑 ]

A羽飞A

谢谢胡版~

刚刚接触数组，很是头疼

慢慢学吧。

9lee

offset这个函数在上次课程中有同学用过,很有用,要仔细学学.
教师节学生没有礼物可送,反到是老师又给学生送了一份大礼.谢谢老师,

zzmxy

嗯，OFFSET确实是个很好用的函数~~郑重学习一下！