|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
用数组基本知识浅析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 编辑 ] |
评分
-
6
查看全部评分
-
|