Excel引用函数(OFFSET)产生的多维引用及其应用(1)

1、浅谈在引用函数中使用数组参数生成的多维引用及其应用（第二部分）本文分为三部分：1、认识引用和区域及其维数2、引用函数产生的多维引用3、111111111111111111111222222222222222222222333333333333333333333444444444444444444444555555555555555555555用OFFSET INDIRECT对单元格和区域进行引用时，如果部分或全部参数使用数组，就会产生 下表在下面的举例中会多次用到引用函数产生的多维引用友情提示：本工作公式文本。为了使公式文本能被正确 示出来，请确认您的ExceI中的宏安 是否允许执行宏。什么是

2、多维的引用如果将多个单元格或多个连续单元格区域分别放在不同的二维平面上，那么就构成了多维引用。若 用；若各平面同时在纵横两个方向上扩展，呈面状排列，则是四维引用。多维引用的每个平面都是 三维和四维引用可看作是以平面的单元格区域引用（单一引用）为元素的一维和二维“数组” 以一个整体参与计算的。Excel现有函数仅支持最多到四维的引用。引用函数产生的多维引用的维数变化单个引用、区域引用和合并区域都是在一个平面上的三维引用的各平面在纵向或横向上叠加单个引用2222233333区域引用合并区域2222233333123344上图是下列公式#VALUE!按F9可以看到公 由于三维数组无22331122注

3、：把参数改为四维引用各平面同时在纵横两个方向上扩展，呈面状排列Row为一维纵向数组使得引用向纵11,2222OO11222223311122222223344444Col为一维横向数组使得引用向横向延伸只在一个参数中使用二维数组产生的4维引用212221122323332233333333444444444555二维数 组纵向 影响11223333334444455二维数组横向的影响Offet第一参数为四维引用产生的新四维引用Referenee 参数四维引用纵向影22222333334444455555222222222333333333444444444555555555Referenee参

4、数四维引用横向影响引用函数产生的多维引用中每一区域的尺寸变化引用区域高度和宽度产生的变化112233445右图是由Height参数使用一维#NAME?1111111222222233333322右图是由Height和width参数使 7#NAME?按F9可以看到公式返回1;#VA下面的公式，宽度参数中的数组在维度方向和上式不同，高度和宽度的值多对多对应后，形成了一1 上 #NAME?请注意这里“；”和“，”号的使用按 F9可以看到公式返回=1,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE注：由于第一个区域只有一个单

5、元格，所以上面的三个公式都能正确显示该单元格的值。在OFFSE函数的参数中使用数组，需要注意以下规则：（1） 参数rows、cols、height和width既可单独，也可部分或全部使用数组。（2）在两个以上参数中使用数组，所有数组的维数必须一致。（3）在两个以上参数中使用相同维度的数组，则数组的尺寸必须相同。（4）在两个以上参数中使用一维数组，若数组的维度不同，则尺寸可以不同。（5）所有参数中的数组都呈纵向或横向排列，则函数返回的以各区域为元素的“数组”也呈纵向（6）所有参数中的数组都呈二维排列，则函数返回的以各区域为元素的“数组”也呈二维排列。以上规则中，如果符合第（5）条，则函数结果生成

6、三维引用；如果符合第（4）或（6）条，则函数In direct函数第一个参数为二维数组产生的四维引用Ref_text参数中二1111113333333333331444444444444533334444Ref text参数中二维数组纵向影响33333333334444444444111111334433334444Ref text参数中二维数组横向影响引用函数产生的跨多表多维引用In direct的Ref text参数使用二维数组产生的跨多表的四维引用Ref text参数中二维数组纵向影响学年上的变化姓名语文数学英语张三857280李四996490王五956897第一学期成绩表第二学期成绩表

7、姓名语文张三95李四92王五89数学756870姓名语文数学英语张三919981李四876299王五937293第三学期成绩表Ref text参数中二维数组横向影响第四学期成绩表姓名|语文数学张三9199李四8762王五9372#VALUE!这是跨多表的多维引用，其中In direct的Reft_text参数使用了二维数组产生了一个2*2的二维区域 #NAME?按 F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE! 共 2*2=4 个区域Ref text参数中二维数组横向影响连续多表三维引用和引用函数产生的多维引用的区别referenee、range和口 re

8、f 类前者是将整个“引用”作为一个结果返回给 Excel;而后者是将对不同平面上的各区域的引用作为多个结 两者支持的参数类型也大不相同。前者由于不是真正的引用，不能在绝大多数 以前者为参数的函数只返回一个结果值；而以后者为参数的函数对每个被引用的区域分别计算，同时返回 前者的三维结构是物理存在的；而后者的多维结构是通过引用函数虚拟生成的。前者所“引用”的各工作表必须是相邻的，且各工作表中被“引用”区域的大小和行列位置也必须相同；小结（行列方向）、 中的每个区域用1 从上面的分析，我们可以了解引用函数产生的多维引用是如何形成的，数组参数的维数、维度4维以下的多维弓|用可以返回一个二维以内以平面区

9、域为元素的“数组"，我们只要将该“数组其应用（第二部分）<CA引用函数产生的多维引用的应用实例未经许可请勿抄载Apolloh黄朝阳5生一个三维甚至三维以上的引用。下面我们将两个函数产生多维引用的情况进行解析。提示：本工作簿中使用了宏表函数来显示 公式文本。为了使公式文本能被正确地显 示出来，请确认您的ExceI中的宏安全性 是否允许执行宏。用。若各平面在纵向或横向上叠加，呈线状排列，就是三维引 面都是对某个单元格或区域的单一引用。数组”。其中各单元格区域作为该“数组”的元素，是的各平面在纵向或横向上叠加，呈线状排列。Row为一维纵向维引用数组生成纵向三55列公式的图解，其实质是

10、Ro彥数变化产生了多个平面#NAME?看到公式返回=#VALUE!;#VALUE!;#VALUE®数组无法显示岀来所以返回了一个按行方向排列的一维3*1的区域数组，每个#VALUE都代表一个区域。数改为一元数组如1这样的数组，结果也是3维引用，虽然其只产生一个平面，但仍然是一个三维空间。333444222333111222右图是由两个参数使用一维数组产生的不同方向上的变化#NAME?注意这里；和，号的区别按 F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!由于4维数组无法显示岀来所以返回了一个按 3*2的二维区域数组

11、，每个#VALUE都代表一个区域。#VALUE!2233332233 |_4444右图是由一个参数使用二维数组产生的不同方向上的变化 #VALUE!#N AME?55按 F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!由于4维数组无法显示岀来所以返回了一个按 3行2列方向排列的二维区域数组，每个#VALUE都代表222223333344444'右图是由Referenee参数使用四维引用产生的引用区域起点不同的变化。 #VALUE! J #NAME?按 F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!

12、,#VALUE! 共 2*2=4 个区域55555555用一维数组产生的各区域引用的高度的变化参数使用一维数组产生的各区域引用的高度和宽度同时变化1;#VALUE!;#VALUE!,这里的高度和宽度的值一对一对应，产生了 3*1=3个区域成了一个3*3的区域数组。VALUE!, 共9个区域呈纵向或横向排列。 排列。则函数结果生成四维引用33333334444444右图是由Ref_te #NAME? #VALUE门 #N AME?533344433334444按 F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE! 共 2*2=4 个区域语文数学英语957582926888897098语文数学英语919981876299937293二学期成绩表姓名张三李四王五四学期成绩表姓名张三李四王五上下半学期的变化维区域数组irect产生一个引用维数的变化，offset的height参数产生了一个区域尺寸（高度）的变化 #NAME?irect中的Ref_text参数同offset中的height参数是一一对应的。参数产生一个区域高度的变化不同平面上的各区域的引用作为多个结果返回。能在绝大多数reference、range和ref类型的参数