Excel函数产生的多维引用及其应用

1、浅谈在引用函数中使用数组参数产生的多维引用及其应用（第一部分）通常我们所说的三维引用都是指Excel帮助中定义的跨多表的相同位置区域的引用。而此文重点要说明的是另一种由引用函数产生的三维以上（含）的引用。本文分为三部分：1、 认识引用和区域及其维数2、 引用函数产生的多维引用3、认识引用和区域及其维数下表为一张成绩表，在下面的举例中会多次用到姓名语文数学英语张三857280李四996490王五956897引用的类型引用是对工作表上单元格或单元格区域的标识。从引用的范围看一般有,单个单元格引用、多个连续单元格的区域引用和连续多表三维引用。从引用产生的方式上看，有直接输入标识的引用和引用函数产生的

2、引用。另外还有交叉引用、以及由引用构成的合并区域等形式，其中交叉引用不是我们要说明的重点。单个单元格引用是指对工作表中某个单元格的引用，如姓名=C8区域引用是指对一个连续单元格区域的引用，可以是一行多列的单元格区域，或多行一列的单元格区域，还可以是多行多列的单元格区域。单元格区域引用的结果会产生一个单元格值组成的数组，其中一行多列或多行一列的单元格区域引用产生的是一维数组，而多行多列的单元格区域引用产生的是二维数组。所以我们需要以数组公式的形式输入才能让其在单元格中正确显示。一行多列多行一列姓名语文数学姓名=C8:E8张三=C8:C11李四王五合并区域在介绍三维引用前，我们有必要先解释一个多区

3、域合并的概念，其和区域引用一起合称为区域。将多个单元格或区域引用，用逗号隔开并用括号（）合并起来表示，就是合并区域。合并区域是虽是平面二维的，但无法在一个连续的单元格区域中显示，也不能形成一个按行列整齐排列的二维的数组。和我们要说的连续多表三维引用和引用函数产生的多维引用不同，它只是分散在同一个工作表中的几个区域的集合。一个合并区域的例子#VALUE!=(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)合并区域在单元格中无法正确显示， 不论其实际合并后所代表的区域是否连续。但并影响我们将其作为一个参数用于可使用区域参数的函数中参与计算。返回引用区域

4、的地址$D$9,$D$10:$F$10,$E$10:$F$11,$F$9:$F$10,$D$11,$D$9:$E$9,$E$9:$F$9=CELL(address,(D9:D99,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)注意：合并区域中的第一个区域只有第一个单元格的地址出现在Cell(address,)的返回结果中,其他区域则是完整的。测试合并区域中的区域数7=AREAS(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)Areas（）函数可用来返回一个区域中包含几个单元格或区域引用。上例中的合并区域实际上是7

5、个区域组成的,其中有两个区域是做为一个合并区输入的，他们分别是：ref1ref2ref385=D9:D109964906499=D10:F1068ref4ref5ref680=F9:F1095857290=D11=D9:E9合并区域如何参与计算求和1330=SUM(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)1330=SUBTOTAL(9,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)计数16=COUNT(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F

6、9)16=SUBTOTAL(3,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)最大值99=MAX(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)99=SUBTOTAL(4,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)求85在区域中的排名8=RANK(85,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)使用Index返回合并区域中的第3个区域引用6490=INDEX(D9:D10,D10:F10,E

7、10:F11,F9:F10,D11,(D9:E9,E9:F9),3)6897使用Index返回合并区域中的第3个区域引用,然后求和319=SUM(INDEX(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9),3)使用Index返回合并区域中的第3个区域，第2行的值6897=INDEX(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9),2,3)使用Index返回合并区域中的第3个区域，第2行的值，第2列的值97=INDEX(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,

8、E9:F9),2,2,3)合并区域不是引用，它不能作为参数类型只为Range的函数的参数，我们不能在函数的range参数中输入合并区域，例如下例就出现参数类型错误：#VALUE!=COUNTIF(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9),90)合并区域中不能同时存在于有两张工作表以上的引用或区域或连续多表三维引用。#VALUE!=SUM(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9),成绩表1!B3:C4)连续多表三维引用连续多表三维引用是引用多张连续排列的工作表中相同行列位置的单元格或连续单

9、元格区域的表示方法。连续多表三维引用的例子#REF!=成绩表1:成绩表3!B3:D5由于多表三维引用是一个在表/行/列三个方向上的引用所以我们无法直接将在一张表的单元格区域中直接展示出来，但是大家可以透过下图去理解。把每张表的区域看是一个平面，那么多表三维引用就是在多个平面上的不同区域引用组成的，而且每个平面区域引用的尺寸是一样的。857280996490956897957582926888897098919981876299937293测试是否为引用，结果为否FALSE=ISREF(成绩表1:成绩表3!B3:D5)连续多表三维引用,不是真正的引用，它无法应用于Range参数类型的函数，例如#

10、VALUE!=COUNTIF(成绩表1:成绩表3!B3:D5,80)下表为帮助中列出的支持连续多表三维引用的函数列表,此外Rank函数也支持连续多表三维引用SUM 将数值相加AVERAGE 计算数值的平均值（数学方法）AVERAGEA 计算数值（包括字符串和逻辑值）的平均值（数学方法）COUNT 计算包含数字的单元格个数COUNTA 计算非空白单元格个数MAX 查找一组数值中的最大值MAXA 查找一组数值中的最大值（包括字符串和逻辑值）MIN 查找一组数值中的最小值MINA 查找一组数值中的最小值（包括文本和逻辑值）PRODUCT 将数字相乘STDEV 估算基于给定样本的标准偏差STDEVA

11、估算基于给定样本（包括字符串和逻辑值）的标准偏差STDEVP 计算基于给定的样本的总体的标准偏差STDEVPA 计算样本（包括文本和逻辑值）总体的标准偏差VAR 估计样本的方差VARA 估算给定样本（包括文本和逻辑值）的方差VARP 计算基于给定的样本的总体的方差VARPA 计算样本（包括文本和逻辑值）总体方差连续多表三维引用的输入方式单击要输入函数的单元格。 键入 =（等号），再输入函数名称，然后键入左圆括号。 单击需要引用的第一个工作表标签。 按住 Shift 单击需要引用的最后一个工作表的标签。 选定需要引用的单元格或单元格区域。 完成公式，再按 Enter。 连续多表三维引用如何参与计

12、算例，求97在三张表中的名次,因为三张表中有3个99和1个98，所以97名列第5名5=RANK(97,成绩表1:成绩表3!B3:D5)例，求三个学期所有成绩的平均值85=AVERAGE(成绩表1:成绩表3!B3:D5)例，求三个学期所有成绩的最大值99=MAX(成绩表1:成绩表3!B3:D5)注意：1连续多表三维引用，虽然称作引用但是其不能用于引用类型Range为参数的函数，如Sumif()，Countif()等；2对于大多数以reference或ref为参数的函数，也不能使用连续多表三维引用作为参数，但有一个例外，就是Rank函数;3Areas()函数虽然是求区域中区域引用的个数的，但其只适

13、用于同一个工作表中的区域，即同一个平面上的区域。因此Areas不能用来统计三维引用区域的个数。通过引用函数产生的单个单元格、单元格区域引用如果不直接输入引用的标识，我们还可以通过Index()、Offsett()、Indirect()函数来产生对单元格和单元格区域的引用。这里主要是要说明三个函数是如何返回单个单元格引用和多个单元格区域引用,所以对三个函数的用法就不作更详细地介绍了。Index产生的单个单元格、单元格区域引用Index的第一参数为不连续区域时，可指定返回其中一个区域或区域中某一行列或某一单元格的引用，通过之前合并区域中的例子，我们已经了解了。当第一参数为连续区域时,除了不用使用第

14、4个参数外，其他都是一样的，这里不再重复。要强调的是，Index第一参数为区域时，其返回的值的类型为单元格引用。857280996490956897957582926888897098919981876299937293我们可以通过两个例子自来作进一步的说明：例，使用Isref()函数测试是否为引用Index对区域中单个单元格的引用的返回值类型的判断Index对区域中某一行的引用TRUE=ISREF(INDEX($C$8:$F$11,1,1)姓名语文Index引用区域中某一行的引用的返回值类型的判断=INDEX($C$8:$F$11,1,)TRUE=ISREF(INDEX($C$8:$F$11

15、,1,)判断结果是引用有点遗憾，对于一个连续区域，Index最多只能返回其中的一行或是一列。而对于不连续区域Index则可以返回其中的一整个连续区域，但也不能返回这个连续区域的多行或多列。Index返回的引用如何参与计算：例，将Index函数返回的引用用于区域引用中483=SUM(D9:INDEX(D9:F11,3,2)例，将Index函数返回的引用用于Index的一个参数,再让其返回一个引用姓名=INDEX(INDEX($C$8:$F$11,1,),1)Offest产生的单个单元格、单元格区域引用通过指定一个单元格或区域引用，行列偏移量，区域的高度和宽度等参数，offset()可以产生对另一

16、个单元格或单元格区域的引用。例,引用成绩表格中第3行第2列的单元格例，引用成绩表3的第3行第2列单元格99=OFFSET($C$8,2,1)87=OFFSET(成绩表3!$A$2,2,1)例,引用成绩表格中第3行第2列的单元格起,高为2,宽为2的单元格区域,这是多个单元格区域引用需要以数组公式的方式返回9964=OFFSET($C$8,2,1,2,2)9568注意：offset的第一个参数不可以是合并区域。Indirect产生的单个单元格、单元格区域引用例,引用成绩表格中第3行第2列的单元格例，引用成绩表3的第3行第2列单元格李四=INDIRECT(c10)87=INDIRECT(成绩表3!b

17、4)例,引用成绩表格局部区域9964=INDIRECT(d10:e12)9568以上三个引用函数中都不带数组参数，因此只能返回单个单元格引用或一个二维以内的区域引用，并且都能在单元格中直接显示出来。在合并区域中使用引用函数产生的单元格或区域引用对引用函数产生的二维以内的引用进行区域合并，也同样能得到同在一个平面的二维区域引用的集合，但其也不是三维引用。#VALUE!=(OFFSET($C$8,2,1),INDIRECT(c10:d12),INDEX($C$8:$F$11,1)上述公式的结果无法正确显示，但实际上是返回了下面的各区域或引用的合并区域999964=D10:E12姓名=D109568

18、张三李四王五测试此合并区域的区域引用数3=AREAS(OFFSET($C$8,2,1),INDIRECT(D10:E12),INDEX($C$8:$F$11,1)求区域中的最大值，注意区域中的文本会被忽略99=MAX(OFFSET($C$8,2,1),INDIRECT(d10:e12),INDEX($C$8:$F$11,1)求区域中的数值和，注意区域中的文本会被忽略425=SUM(OFFSET($C$8,2,1),INDIRECT(d10:e12),INDEX($C$8:$F$11,1)小结从上述内容,我们可以了解单个单元格引用、一维区域引用,二维区域引用,合并区域和连续多表三维引用的特点及用

19、法，以及如何用函数来返回二维以内的单元格和区域引用。引用函数产生的多维引用的应用实例多行多列姓名语文张三85=C8:D11李四99王五95未经许可请勿抄载Apolloh黄朝阳友情提示: 本工作簿中使用了宏表函数来显示公式文本。为了使公式文本能被正确地显示出来，请确认您的Excel中的宏安全性是否允许执行宏。浅谈在引用函数中使用数组参数产生的多维引用及其应用（第一部分）通常我们所说的三维引用都是指Excel帮助中定义的跨多表的相同位置区域的引用。而此文重点要说明的是另一种由引用函数产生的三维以上（含）的引用。是指对一个连续单元格区域的引用，可以是一行多列的单元格区域，或多行一列的单元格区域，还可

20、以是多行多列的单元格区域。单元格区域引用的结果会产生一个单元格值组成的数组，其中一行多列或多行一列的单元格区域引用产生的是一维数组，而多行多列的单元格区域引用产生的是二维数组。将多个单元格或区域引用，用逗号隔开并用括号（）合并起来表示，就是合并区域。合并区域是虽是平面二维的，但无法在一个连续的单元格区域中显示，也不能形成一个按行列整齐排列的二维的数组。和我们要说的连续多表三维引用和引用函数产生的多维引用不同，它只是分散在同一个工作表中的几个区域的集合。合并区域在单元格中无法正确显示， 不论其实际合并后所代表的区域是否连续。但并影响我们将其作为一个参数用于可使用区域参数的函数中参与计算。注意：合

21、并区域中的第一个区域只有第一个单元格的地址出现在Cell(address,)的返回结果中,其他区域则是完整的。90=E10:F1197ref77280=E9:F9上例中的合并区域实际上是7个区域组成的,其中有两个区域是做为一个合并区输入的，他们分别是：=SUBTOTAL(9,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)=SUBTOTAL(3,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)=SUBTOTAL(4,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,

22、E9:F9)=RANK(85,(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)=INDEX(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9),3)=INDEX(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9),2,3)合并区域不是引用，它不能作为参数类型只为Range的函数的参数，我们不能在函数的range参数中输入合并区域，例如下例就出现参数类型错误：=SUM(D9:D10,D10:F10,E10:F11,F9:F10,D11,(D9:E9,E9:F9)

23、,成绩表1!B3:C4)连续多表三维引用是引用多张连续排列的工作表中相同行列位置的单元格或连续单元格区域的表示方法。由于多表三维引用是一个在表/行/列三个方向上的引用所以我们无法直接将在一张表的单元格区域中直接展示出来，但是大家可以透过下图去理解。把每张表的区域看是一个平面，那么多表三维引用就是在多个平面上的不同区域引用组成的，而且每个平面区域引用的尺寸是一样的。857280996490956897957582926888897098919981876299937293857280996490956897957582926888897098919981876299937293单击要输入函数的单

24、元格。 键入 =（等号），再输入函数名称，然后键入左圆括号。 单击需要引用的第一个工作表标签。 按住 Shift 单击需要引用的最后一个工作表的标签。 选定需要引用的单元格或单元格区域。 完成公式，再按 Enter。 连续多表三维引用，虽然称作引用但是其不能用于引用类型Range为参数的函数，如Sumif()，Countif()等；对于大多数以reference或ref为参数的函数，也不能使用连续多表三维引用作为参数，但有一个例外，就是Rank函数;Areas()函数虽然是求区域中区域引用的个数的，但其只适用于同一个工作表中的区域，即同一个平面上的区域。因此Areas不能用来统计三维引用区域的

25、个数。如果不直接输入引用的标识，我们还可以通过Index()、Offsett()、Indirect()函数来产生对单元格和单元格区域的引用。这里主要是要说明三个函数是如何返回单个单元格引用和多个单元格区域引用,所以对三个函数的用法就不作更详细地介绍了。Index的第一参数为不连续区域时，可指定返回其中一个区域或区域中某一行列或某一单元格的引用，通过之前合并区域中的例子，我们已经了解了。数学英语Index对区域中某一列的引用姓名张三=INDEX($C$8:$F$11,1)李四王五=C8:C11Index对区域中某一行的引用而对于不连续区域Index则可以返回其中的一整个连续区域，但也不能返回这个

26、连续区域的多行或多列。通过指定一个单元格或区域引用，行列偏移量，区域的高度和宽度等参数，offset()可以产生对另一个单元格或单元格区域的引用。=OFFSET(成绩表3!$A$2,2,1)例,引用成绩表格中第3行第2列的单元格起,高为2,宽为2的单元格区域,这是多个单元格区域引用需要以数组公式的方式返回以上三个引用函数中都不带数组参数，因此只能返回单个单元格引用或一个二维以内的区域引用，并且都能在单元格中直接显示出来。对引用函数产生的二维以内的引用进行区域合并，也同样能得到同在一个平面的二维区域引用的集合，但其也不是三维引用。从上述内容,我们可以了解单个单元格引用、一维区域引用,二维区域引用

27、,合并区域和连续多表三维引用的特点及用法，以及如何用函数来返回二维以内的单元格和区域引用。将多个单元格或区域引用，用逗号隔开并用括号（）合并起来表示，就是合并区域。合并区域是虽是平面二维的，但无法在一个连续的单元格区域中显示，也不能形成一个按行列整齐排列的二维的数组。浅谈在引用函数中使用数组参数产生的多维引用及其应用（第二部分）本文分为三部分：1、 认识引用和区域及其维数2、 引用函数产生的多维引用3、用OFFSET、INDIRECT来对单元格和区域进行引用时，如果部分或全部参数使用数组，就会产生一个三维甚至三维以上的引用。下面我们将两个函数产生多维引用的情况进行解析。下表在下面的举例中会多次

28、用到111111111111111111111222222222222222222222333333333333333333333444444444444444444444555555555555555555555引用函数产生的多维引用什么是多维的引用单个引用和区域引用及合并区域都是在一个平面上，其中合并区域，是使得平面上同时放置了多个独立的单元格或区域引用而维引用实际上是将各个引用区域放到了不同的平面的，其中每个平面只有一个单元格或区域引用，不同平面的引用形成一个空间。维引用实际上就是有个以上象维引用这样的空间，形成一个外套的空间。维，维，维，维,以此类推就是空间外再套空间。首先我们先来做几

29、个推断，之后通过一些实例大家可以一起来证明这些推断是否正确：推断：函数产生的多维引用的基本元素是存放于每个平面上的单个单元格或区域引用，只有先将这些区域转换成常数，多维引用才能被应用于数组运算；推断：Offset函数的各参数及Indierect的第一参数都可以使用数组，但每个数组参数的维数最多不能超过2维（因为Excel函数不能正确处理3维以上的数组）；推断：在Offset函数中任何一个参数中增加数组的维数，是否会对offset产生引用的维数产生影响，具体还需要看每个数组参数的维度方向是否一致；推断：在已经有其他参数是数组的情况下，增加另一个一元数组参数，不会对引用的维数产生影响；推断：一些参

30、数，如offset的height和width中所使用的数组的行列方向是否一致会影响引用产生的各区域的尺寸大小；推断：Excel函数能处理的引用最多只有4维，可同过函数将其中2维平面先转换计算成常量，然后形成一个2维以内的数组参与数组运算；推断：超过3维的引用无法在单元格区域中展开直接显示出来。引用函数产生的多维引用的维数变化单个引用、区域引用和合并区域都是在一个平面上的3维引用是一个空间包含一个以上的平面,每个平面上都是一个连续的区域引用上图是下列公式的图解，其实质是Row参数变化产生了多个平面#VALUE!按F9可以看到公式返回=#VALUE!;#VALUE!;#VALUE!。由于三维数组无

31、法显示出来所以返回了一个按行方向排列的一维3*1的区域数组，每个#VALUE！都代表一个区域。区域数组：把一个区域看成是一个元素，那么多个区域按一定排列顺序组成的数组叫区域数组。注：把参数改为一元数组如1这样的数组，结果也是3维引用，虽然其只产生一个平面，但仍然是一个三维空间。4维引用实际上是，多个三维引用构成的外套空间单个引用1区域引用2222233333合并区域12222233333111222222333333444111222222333333444111112222222222333333333344444Row参数变化产生多个平面Col参数变化产生的多个空间只在一个参数中使用二维数

32、组产生的4维引用Offet第一参数为二维数组产生的4维引用5维的引用#VALUE!=OFFSET($B$7,0,1;1,2;2,3,0,1,2,2)按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!其返回的区域维数，和#VAlUE！所代表的区域的数目都是和4位引用一样的。这是为什么呢？因为Excel最多只能把4维引用处理成2维数组，再增加维数，Excel就无法进一步处理了，所以只能返回其中的第一个4维引用结果。由于函数的参数有多个，每个参数都支持二维数组，一一列举其多维引用的变化将是一项很庞大的工作，而且不是每种情况都有实用价

33、值，所以维数变化就说这么多，大家可以从实例中去体会。引用函数产生的多维引用中每一区域的尺寸变化引用区域高度和宽度产生的变化右图是由Height参数使用一维数组产生的各区域引用的高度的变化1=OFFSET($B$7,1;2;3;4;5)右图是由Height和width参数使用一维数组产生的各区域引用的高度和宽度同时变化1=OFFSET($B$7,1;2;3,1;2;3)按F9可以看到公式返回1;#VALUE!;#VALUE!，这里的高度和宽度的值一对一对应，产生了*1=3个区域下面的公式，宽度参数中的数组在维度方向和上式不同，高度和宽度的值多对多对应后，形成了一个3*3的区域数组。1=OFFSE

34、T($B$7,1;2;3,1,2,3)请注意这里；和，号的使用按F9可以看到公式返回=1,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!，一共9个区域注：由于第一个区域只有一个单元格，所以上面的三个公式都能正确显示该单元格的值。上述Offset产生的多维引用中的每个区域在尺寸大小的变化是由height和width决定的，但都遵循了一定的运算规则，是有规律可循的。而下面要讲的Indirect的多维引用则不同，其每个区域的尺寸大小是可以任意变化的，可以无规律性。Indirect函数第一个参数为二维数组产生的4维引用22

35、2333111222222333333444333444444555Row参数行方向变化产生多个平面Row参数列方向变化产生的多个空间1121231234123451111222111111222222333333Reference参数数组中数行方向变化产生多个平Reference参数列方向变化产生的多个空间2222233333444445555522222222233333333344444444455555555511111133333334444444333333333344444444445Ref_text参数数组中数行方向变化产生多个平面Col参数变化产生的多个空间引用函数产生的跨多

36、表多维引用Indirect的Ref_text参数使用二维数组产生的跨多表的4维引用学年上的变化这是跨多表的多维引用，其中Indirect的Reft_text参数使用了二维数组产生了一个2*2的二维区域数组。#VALUE!=INDIRECT(成绩表1,成绩表2;成绩表3,成绩表4&!A1:D5)按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!，一共2*2=4个区域Offset与Indirect结合的跨多表引用右图中Indirect产生一个引用维数的变化，offset的height参数产生了一个区域尺寸（高度）的变化。姓名注意，Indirect中的Re

37、f_text参数同offset中的height参数是一一对应的。连续多表三维引用和引用函数产生的多维引用的区别1连续多表三维引用是将整个引用作为一个结果返回给Excel，而引用函数产生的三维引用以及多维引用是将不同空间不同平面上的区域引用作为多个结果返回给Excel2其支持的函数也大不相同，连续多表三维引用做为参数支持的函数只对该参数返回一个结果，而引用产生的多维引用做为参数的函数对其引用中的每个区域分别计算后返回多个结果。3连续多表三维引用是其三维结构是物理存在的，而引用函数产生的多维引用其结构是虚拟获得的。4连续多表三维引用每个平面区域的大小和行列位置时相同的，而引用函数产生的多维引用每个

38、区域引用的大小和行列位置都可以不同。小结1从上面的分析，我们可以了解引用函数产生的多维引用是如何形成的，数组参数的维数、维度(行列方向)、值的变化会对引用结果的维数、引用区域的位置和尺寸产生影响。Ref_text参数列方向变化产生的多个空间Ref_text参数数组中数行方向变化产生多个平面Ref_text参数列方向变化产生的多个空间第一学期成绩表姓名语文数学英语张三857280李四996490王五956897第二学期成绩表姓名语文数学英语张三957582李四926888王五897098第三学期成绩表姓名语文数学英语张三919981李四876299王五937293第四学期成绩表姓名语文数学英语张

39、三919981李四876299王五937293英语819993Indirect的Ref_text参数数组中数行方向变化产生多个平面Ref_text参数列方向变化产生的多个空间姓名语文95数学9962Offset的height参数产生一个区域高度的变化11111133333334444444333333333344444444445Ref_text参数数组中数行方向变化产生多个平面24维以下的多维引用可以返回一个二维以内的区域数组,我们只要将区域数组中的每个区域用函数分别同步求值，就可以获得一个二维以内的数组。引用函数产生的多维引用的应用实例=OFFSET($B$7,0;1;2,2,2)右图是由

40、两个参数使用一维数组产生的不同方向上的变化#VALUE!=OFFSET($B$7,0;1;2,0,1,2,2)注意这里；和，号的区别按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!由于4维数组无法显示出来所以返回了一个按3*2的二维区域数组，每个#VALUE！都代表一个区域。Row参数变化产生多个平面未经许可请勿抄载Apolloh黄朝阳友情提示: 本工作簿中使用了宏表函数来显示公式文本。为了使公式文本能被正确地显示出来，请确认您的Excel中的宏安全性是否允许执行宏。浅谈在引用函数中使用数组参数产生的多维引用及其应用（第二部

41、分）用OFFSET、INDIRECT来对单元格和区域进行引用时，如果部分或全部参数使用数组，就会产生一个三维甚至三维以上的引用。下面我们将两个函数产生多维引用的情况进行解析。单个引用和区域引用及合并区域都是在一个平面上，其中合并区域，是使得平面上同时放置了多个独立的单元格或区域引用而维引用实际上是将各个引用区域放到了不同的平面的，其中每个平面只有一个单元格或区域引用，不同平面的引用形成一个空间。函数产生的多维引用的基本元素是存放于每个平面上的单个单元格或区域引用，只有先将这些区域转换成常数，多维引用才能被应用于数组运算；Offset函数的各参数及Indierect的第一参数都可以使用数组，但每

42、个数组参数的维数最多不能超过2维（因为Excel函数不能正确处理3维以上的数组）；在Offset函数中任何一个参数中增加数组的维数，是否会对offset产生引用的维数产生影响，具体还需要看每个数组参数的维度方向是否一致；在已经有其他参数是数组的情况下，增加另一个一元数组参数，不会对引用的维数产生影响；一些参数，如offset的height和width中所使用的数组的行列方向是否一致会影响引用产生的各区域的尺寸大小；Excel函数能处理的引用最多只有4维，可同过函数将其中2维平面先转换计算成常量，然后形成一个2维以内的数组参与数组运算；3维引用是一个空间包含一个以上的平面,每个平面上都是一个连续

43、的区域引用上图是下列公式的图解，其实质是Row参数变化产生了多个平面按F9可以看到公式返回=#VALUE!;#VALUE!;#VALUE!。由于三维数组无法显示出来所以返回了一个按行方向排列的一维3*1的区域数组，每个#VALUE！都代表一个区域。区域数组：把一个区域看成是一个元素，那么多个区域按一定排列顺序组成的数组叫区域数组。注：把参数改为一元数组如1这样的数组，结果也是3维引用，虽然其只产生一个平面，但仍然是一个三维空间。111222222333333444111112222222222333333333344444右图是由一个参数使用二维数组产生的不同方向上的变化#VALUE!=OFF

44、SET($B$7,0,1;1,2;2,3,2,2)按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!由于4维数组无法显示出来所以返回了一个按3行2列方向排列的二维区域数组，每个#VALUE！都代表一个区域。右图是由Reference参数使用二维数组产生的引用区域起点不同的变化。#VALUE!=OFFSET(OFFSET($B$7,1;3,1,3),2,2)按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!，一共2*2=4个区域右图是由Ref_text参数使用二维数组产生的各区域尺寸大小的不规

45、则变化。右图是由Ref_text参数使用二维数组产生的各区域尺寸大小的不规则变化。因为Excel最多只能把4维引用处理成2维数组，再增加维数，Excel就无法进一步处理了，所以只能返回其中的第一个4维引用结果。由于函数的参数有多个，每个参数都支持二维数组，一一列举其多维引用的变化将是一项很庞大的工作，而且不是每种情况都有实用价值，所以维数变化就说这么多，大家可以从实例中去体会。右图是由Height参数使用一维数组产生的各区域引用的高度的变化=OFFSET($B$7,1;2;3;4;5)右图是由Height和width参数使用一维数组产生的各区域引用的高度和宽度同时变化=OFFSET($B$7,

46、1;2;3,1;2;3)按F9可以看到公式返回1;#VALUE!;#VALUE!，这里的高度和宽度的值一对一对应，产生了*1=3个区域下面的公式，宽度参数中的数组在维度方向和上式不同，高度和宽度的值多对多对应后，形成了一个3*3的区域数组。按F9可以看到公式返回=1,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!，一共9个区域上述Offset产生的多维引用中的每个区域在尺寸大小的变化是由height和width决定的，但都遵循了一定的运算规则，是有规律可循的。而下面要讲的Indirect的多维引用则不同，其每个区域

47、的尺寸大小是可以任意变化的，可以无规律性。2223331112222223333334443334444445552222233333444445555522222222233333333344444444455555555511111133333334444444333333333344444444445#VALUE!=INDIRECT(B5:D5,D7:E8;B7:E8,B9)按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!，一共2*2=4个区域上下半学期的变化=OFFSET(INDIRECT(成绩表1!A2,成绩表2!B2;成绩表3!C2,成绩表4!

48、D2),1,2;3,4)11111133333334444444333333333344444444445这是跨多表的多维引用，其中Indirect的Reft_text参数使用了二维数组产生了一个2*2的二维区域数组。右图中Indirect产生一个引用维数的变化，offset的height参数产生了一个区域尺寸（高度）的变化。注意，Indirect中的Ref_text参数同offset中的height参数是一一对应的。连续多表三维引用是将整个引用作为一个结果返回给Excel，而引用函数产生的三维引用以及多维引用是将不同空间不同平面上的区域引用作为多个结果返回给Excel其支持的函数也大不相同，

49、连续多表三维引用做为参数支持的函数只对该参数返回一个结果，而引用产生的多维引用做为参数的函数对其引用中的每个区域分别计算后返回多个结果。连续多表三维引用是其三维结构是物理存在的，而引用函数产生的多维引用其结构是虚拟获得的。连续多表三维引用每个平面区域的大小和行列位置时相同的，而引用函数产生的多维引用每个区域引用的大小和行列位置都可以不同。从上面的分析，我们可以了解引用函数产生的多维引用是如何形成的，数组参数的维数、维度(行列方向)、值的变化会对引用结果的维数、引用区域的位置和尺寸产生影响。第一学期成绩表姓名语文数学英语张三857280李四996490王五956897第二学期成绩表姓名语文数学英

50、语张三957582李四926888王五897098第三学期成绩表姓名语文数学英语张三919981李四876299王五937293第四学期成绩表姓名语文数学英语张三919981李四876299王五937293Offset的height参数产生一个区域高度的变化4维以下的多维引用可以返回一个二维以内的区域数组,我们只要将区域数组中的每个区域用函数分别同步求值，就可以获得一个二维以内的数组。由于三维数组无法显示出来所以返回了一个按行方向排列的一维3*1的区域数组，每个#VALUE！都代表一个区域。注：把参数改为一元数组如1这样的数组，结果也是3维引用，虽然其只产生一个平面，但仍然是一个三维空间。按F

51、9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!由于4维数组无法显示出来所以返回了一个按3*2的二维区域数组，每个#VALUE！都代表一个区域。按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!由于4维数组无法显示出来所以返回了一个按3行2列方向排列的二维区域数组，每个#VALUE！都代表一个区域。按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!，一共2*2=4个区域右图是由Ref_text参数使用二维数组产生的各区域尺寸大

52、小的不规则变化。其支持的函数也大不相同，连续多表三维引用做为参数支持的函数只对该参数返回一个结果，而引用产生的多维引用做为参数的函数对其引用中的每个区域分别计算后返回多个结果。按F9可以看到公式返回=#VALUE!,#VALUE!;#VALUE!,#VALUE!，一共2*2=4个区域=OFFSET(INDIRECT(成绩表1!A2,成绩表2!B2;成绩表3!C2,成绩表4!D2),1,2;3,4)浅谈在引用函数中使用数组参数产生的多维引用及其应用（第三部分）本文分为三部分：1、 认识引用和区域及其维数2、 引用函数产生的多维引用3、引用函数产生的多维引用的应用实例可以使用引用函数产生的多维引用

53、的作为参数的函数1通过对引用函数产生的多维引用的讨论，我们已经得知，多维引用在没有使用函数将其各区域计算处理成常量前是无法显示，也不能作为内存数组直接参与计算；2目前已知的可以支持引用函数产生的多维引用，并将引用中的各区域计算后作为内存数组返回的函数有：COUNTBLANK()、COUNTIF()、D数据库函数()、()、RANK()、SUBTOTAL()、SUMIF()、()等；3引用函数产生的多维引用经过上述函数计算后的结果值是一个二维以内的数组，其元素的个数和多维引用所能返回的区域个数相同；4其中()和()函数比较特殊，他们只能返回每个区域的第一个值，并将其转和为数值或文本，所以当多维引

54、用的每个区域都是一个单元格时，使用这两个函数比较合适；5除了Rank函数和一些数据库函数外其他函数的多维引用计算都有很高的实用价值，下面将以几种典型的多维引用方式来说明这些函数是如何进行多维引用计算。区域为单行单列的的多维引用学生成绩表语文数学英语孙二836278张三669857李四976153王五657665赵六557064钱七897773例1:求表一中每个学生的总成绩（返回内存数组）例2：求表一中每个学生的最高单科成绩和平均成绩Subtotal(9,)SumifSubtotal(4,) subtotal(1,)孙二223223孙二8374张三221221张三9874李四211211李四97

55、70王五206206王五7669赵六189189赵六7063钱七239239钱七8980例4:求表一中各科的最高成绩例5:求表一中各科的最低成绩Subtotal(4,) (列方向)Subtotal(5,)语文数学英语语文数学英语979878556153水果上市的销售数量水果单价5号6号7号单价苹果2933苹果1.51101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARPSubtal参数对照表香蕉3521香蕉1.6李子1430李子1.7栗子153216栗子2

56、梨子29梨子2.5荔枝143237荔枝2.3例6:求表二中各种水果有销售的天数例7:用表二、表三的数据求三日销售额最高的水果Subtotal(2,) Subtotal(3,) Countblank CountifSubtotal(9,)苹果2222荔枝香蕉2222李子2222例8：求1天以上没有销售的水果品种个数栗子3333subtotal(2,)梨子11114荔枝3333 用餐及餐费记录日期张三李四王五赵六钱七餐费1-1中餐11125.001-1晚餐11119.001-2中餐1111127.001-2晚餐113.001-3中餐112.001-3晚餐1118.001-4中餐111122.001

57、-4晚餐1115.00这是我曾出过的一道测试题，也有不用多维引用的解法，其主要目的是要说明内存数组的概念的。具体见贴子链接纸箱规格(厘米)例10:求表五中纸箱的体积(立方厘米)长框高Subtotal(6,)L001803649L001141,120L002923559L002189,980L003653253L003110,240L004672739L00470,551L005673455L005125,290L006932932L00686,304区域的尺寸递增或递减的多维引用水果清单例12:求表六中不重复的水果清单现金收入支出表苹果#VALUE!收入香蕉#VALUE!2005/4/15,0

58、00李子#VALUE!2005/4/21,000栗子#VALUE!2005/4/34,000李子#VALUE!2005/4/4栗子#VALUE!2005/4/56,000苹果#VALUE!2005/4/6=T(OFFSET($B$90,SMALL(IF(COUNTIF(OFFSET($B$91,ROW($B$91:$B$97)-ROW($B$90),$B$91:$B$97)=1,ROW($B$91:$B$97)-ROW($B$90),ROW($B$91:$B$97)-ROW($D$90),)T函数是对一个每个区域只有一个单元格的多维引用计算，Countif函数对一个区域高度递增的三维引用计算。

59、 环比成长率表例13：用表八数据求每年较第一年的定比成长率(要求为内存数组)年度环比成长率Subtotal(6,0)2001100.00%2001100.00% =SUBTOTAL(6,OFFSET($C$104,ROW($C$104:$C$108)-ROW($C$104)+1)2002110.00%2002110.00%2003125.00%2003137.50%2004130.00%2004178.75%2005150.00%2005268.13%表九项目投资与收益表例14：求表九中各项目的最大投资和项目投资收益43,000Dmax，第一参数为区域尺寸递减的多维引用，第三个参数返回每2行为

60、区域的多维引用A4,0002,000=SUM(IF(MATCH($B$114:$B$126,$B$114:$B$126,0)=ROW($B$114:$B$126)-ROW($B$113),DMAX(OFFSET($B$113,ROW($B$114:$B$126)-ROW($B$113)-1,ROWS($B$114:$B$126)-ROW($B$114:$B$126)+ROW($B$113)+2,2),2,OFFSET($B$113,ROW($B$114:$B$126)-ROW($B$113)-1,2)B5,0003,000这是我曾出过的一道测试题，chenjun版主给的用Dmax多维引用计算来实现的解