搜索
浅谈在引用函数中使用数组参数产生的多维引用及其应用（第一部分）通常我们所说的三维引用都是指Excel帮助中定义的跨多表的相同位置区域的引用。而此文重点要说明的是另一种由引用函数产生的三维以上（含）的引用。本文分为三部分：1、认识引用和区域及其维数2、引用函数产生的多维引用3、认识引用和区域及其维数下表为一张成绩表，在下面的举例中会多次用到姓名语文数学英语张三857280李四996490王五956897引用的类型引用是对工作表上单元格或单元格区域的标识。从引用的范围看一般有,单个单元格引用、多个连续单元格的区域引用和连续多表三维引用。从引用产生的方式上看，有直接输入标识的引用和引用函数产生的引用。另外还有交叉引用、以及由引用构成的合并区域等形式，其中交叉引用不是我们要说明的重点。单个单元格引用是指对工作表中某个单元格的引用，如姓名#NAME?`区域引用是指对一个连续单元格区域的引用，可以是一行多列的单元格区域，或多行一列的单元格区域，还可以是多行多列的单元格区域。单元格区域引用的结果会产生一个单元格值组成的数组，其中一行多列或多行一列的单元格区域引用产生的是一维数组，而多行多列的单元格区域引用产生的是二维数组。所以我们需要以数组公式的形式输入才能让其在单元格中正确显示。一行多列多行一列姓名语文数学姓名#NAME?张三#NAME?李四王五合并区域在介绍三维引用前，我们有必要先解释一个多区域合并的概念，其和区域引用一起合称为区域。将多个单元格或区域引用，用逗号隔开并用括号（）合并起来表示，就是合并区域。合并区域是虽是平面二维的，但无法在一个连续的单元格区域中显示，也不能形成一个按行列整齐排列的二维的数组。和我们要说的连续多表三维引用和引用函数产生的多维引用不同，它只是分散在同一个工作表中的几个区域的集合。一个合并区域的例子#VALUE!#NAME?合并区域在单元格中无法正确显示，不论其实际合并后所代表的区域是否连续。但并影响我们将其作为一个参数用于可使用区域参数的函数中参与计算。返回引用区域的地址#NAME?注意：合并区域中的第一个区域只有第一个单元格的地址出现在Cell(address,)的返回结果中,其他区域则是完整的。$D$9,$D$10:$F$10,$E$10:$F$11,$F$9:$F$10,$D$11,$D$9:$E$9,$E$9:$F$9友情提示:本工作簿中使用了宏表函数来显示公式文本。为了使公式文本能被正确地显示出来，请确认您的Excel中的宏安全性是否允许执行宏。测试合并区域中的区域数7#NAME?Areas（）函数可用来返回一个区域中包含几个单元格或区域引用。上例中的合并区域实际上是7个区域组成的,其中有两个区域是做为一个合并区输入的，他们分别是：ref1ref2ref385#NAME?9964906499#NAME?68ref4ref5ref680#NAME?95857290#NAME?#NAME?合并区域如何参与计算求和1330#NAME?1330#NAME?计数16#NAME?16#NAME?最大值99#NAME?99#NAME?求85在区域中的排名8#NAME?使用Index返回合并区域中的第3个区域引用6490#NAME?6897使用Index返回合并区域中的第3个区域引用,然后求和319#NAME?使用Index返回合并区域中的第3个区域，第2行的值6897#NAME?使用Index返回合并区域中的第3个区域，第2行的值，第2列的值97#NAME?合并区域不是引用，它不能作为参数类型只为Range的函数的参数，我们不能在函数的range参数中输入合并区域，例如下例就出现参数类型错误：#VALUE!#NAME?合并区域中不能同时存在于有两张工作表以上的引用或区域或连续多表三维引用。#VALUE!#NAME?连续多表三维引用连续多表三维引用是引用多张连续排列的工作表中相同行列位置的单元格或连续单元格区域的表示方法。连续多表三维引用的例子#REF!#NAME?由于多表三维引用是一个在表/行/列三个方向上的引用所以我们无法直接将在一张表的单元格区域中直接展示出来，但是大家可以透过下图去理解。把每张表的区域看是一个平面，那么多表三维引用就是在多个平面上的不同区域引用组成的，而且每个平面区域引用的尺寸是一样的。测试是否为引用，结果为否FALSE#NAME?连续多表三维引用,不是真正的引用，它无法应用于Range参数类型的函数，例如#VALUE!#NAME?下表为帮助中列出的支持连续多表三维引用的函数列表,此外Rank函数也支持连续多表三维引用SUM将数值相加AVERAGE计算数值的平均值（数学方法）AVERAGEA计算数值（包括字符串和逻辑值）的平均值（数学方法）COUNT计算包含数字的单元格个数COUNTA计算非空白单元格个数MAX查找一组数值中的最大值MAXA查找一组数值中的最大值（包括字符串和逻辑值）MIN查找一组数值中的最小值MINA查找一组数值中的最小值（包括文本和逻辑值）PRODUCT将数字相乘STDEV估算基于给定样本的标准偏差STDEVA估算基于给定样本（包括字符串和逻辑值）的标准偏差STDEVP计算基于给定的样本的总体的标准偏差STDEVPA计算样本（包括文本和逻辑值）总体的标准偏差VAR估计样本的方差VARA估算给定样本（包括文本和逻辑值）的方差VARP计算基于给定的样本的总体的方差VARPA计算样本（包括文本和逻辑值）总体方差连续多表三维引用的输入方式单击要输入函数的单元格。键入=（等号），再输入函数名称，然后键入左圆括号。单击需要引用的第一个工作表标签。按住Shift单击需要引用的最后一个工作表的标签。选定需要引用的单元格或单元格区域。完成公式，再按Enter。连续多表三维引用如何参与计算例，求97在三张表中的名次,因为三张表中有3个99和1个98，所以97名列第5名5#NAME?例，求三个学期所有成绩的平均值85#NAME?例，求三个学期所有成绩的最大值99#NAME?注意：1连续多表三维引用，虽然称作引用但是其不能用于引用类型Range为参数的函数，如Sumif()，Countif()等；2对于大多数以reference或ref为参数的函数，也不能使用连续多表三维引用作为参数，但有一个例外，就是Rank函数;3Areas不能用来统计连续多表三维引用区域的个数。857280996490956897通过引用函数产生的单个单元格、单元格区域引用如果不直接输入引用的标识，我们还可以通过Index()、Offsett()、Indirect()函数来产生对单元格和单元格区域的引用。这里主要是要说明三个函数是如何返回单个单元格引用和多个单元格区域引用,所以对三个函数的用法就不作更详细地介绍了。Index产生的单个单元格、单元格区域引用Index的第一参数为不连续区域时，可指定返回其中一个区域或区域中某一行列或某一单元格的引用，通过之前合并区域中的例子，我们已经了解了。当第一参数为连续区域时,除了不用使用第4个参数外，其他都是一样的，这里不再重复。要强调的是，Index第一参数为区域时，其返回的值的类型为单元格引用。我们可以通过两个例子自来作进一步的说明：例，使用Isref()函数测试是否为引用Index对区域中单个单元格的引用的返回值类型的判断Index对区域中某一行的引用TRUE#NAME?姓名语文Index引用区域中某一行的引用的返回值类型的判断#NAME?TRUE#NAME?判断结果是引用有点遗憾，对于一个连续区域，Index最多只能返回其中的一行或是一列。而对于不连续区域Index则可以返回其中的一整个连续区域，但也不能返回这个连续区域的多行或多列。Index返回的引用如何参与计算：例，将Index函数返回的引用用于区域引用中483#NAME?例，将Index函数返回的引用用于Index的一个参数,再让其返回一个引用姓名#NAME?Offest产生的单个单元格、单元格区域引用通过指定一个单元格或区域引用，行列偏移量，区域的高度和宽度等参数，offset()可以产生对另一个单元格或单元格区域的引用。例,引用成绩表格中第3行第2列的单元格例，引用成绩表3的第3行第2列单元格99#NAME?87#NAME?例,引用成绩表格中第3行第2列的单元格起,高为2,宽为2的单元格区域,这是多个单元格区域引用需要以数组公式的方式返回9964#NAME?9568注意：offset的第一个参数不可以是合并区域。Indirect产生的单个单元格、单元格区域引用例,引用成绩表格中第3行第2列的单元格例，引用成绩表3的第3行第2列单元格李四#NAME?87#NAME?例,引用成绩表格局部区域例,引用成绩表格局部区域，R1C1模式9964#NAME?9964#NAME?95689568以上三个引用函数中都不带数组参数，因此只能返回单个单元格引用或一个二维以内的区域引用，并且都能在单元格中直接显示出来。在合并区域中使用引用函数产生的单元格或区域引用对引用函数产生的二维以内的引用进行区域合并，也同样能得到同在一个平面的二维区域引用的集合，但其也不是三维引用。#VALUE!#NAME?上述公式的结果无法正确显示，但实际上是返回了下面的各区域或引用的合并区域999964#NAME?姓名#NAME?9568张三李四王五测试此合并区域的区域引用数3#NAME?求区域中的最大值，注意区域中的文本会被忽略99#NAME?求区域中的数值和，注意区域中的文本会被忽略425#NAME?小结从上述内容,我们可以了解单个单元格引用、一维区域引用,二维区域引用,合并区域和连续多表三维引用的特点及用法，以及如何用函数来返回二维以内的单元格和区域引用。浅谈在引用函数中使用数组参数产生的多维引用及其应用（第一部分）通常我们所说的三维引用都是指Excel帮助中定义的跨多表的相同位置区域的引用。而此文重点要说明的是另一种由引用函数产生的三维以上（含）的引用。引用函数产生的多维引用的应用实例另外还有交叉引用、以及由引用构成的合并区域等形式，其中交叉引用不是我们要说明的重点。是指对一个连续单元格区域的引用，可以是一行多列的单元格区域，或多行一列的单元格区域，还可以是多行多列的单元格区域。单元格区域引用的结果会产生一个单元格值组成的数组，其中一行多列或多行一列的单元格区域引用产生的是一维数组，而多行多列的单元格区域引用产生的是二维数组。多行多列姓名语文张三85#NAME?李四99王五95在介绍三维引用前，我们有必要先解释一个多区域合并的概念，其和区域引用一起合称为区域。将多个单元格或区域引用，用逗号隔开并用括号（）合并起来表示，就是合并区域。合并区域是虽是平面二维的，但无法在一个连续的单元格区域中显示，也不能形成一个按行列整齐排列的二维的数组。和我们要说的连续多表三维引用和引用函数产生的多维引用不同，它只是分散在同一个工作表中的几个区域的集合。合并区域在单元格中无法正确显示，不论其实际合并后所代表的区域是否连续。但并影响我们将其作为一个参数用于可使用区域参数的函数中参与计算。注意：合并区域中的第一个区域只有第一个单元格的地址出现在Cell(address,)的返回结果中,其他区域则是完整的。未经许可 请勿抄载Apolloh 黄朝阳友情提示:本工作簿中使用了宏表函数来显示公式文本。为了使公式文本能被正确地显示出来，请确认您的Excel中的宏安全性是否允许执行宏。上例中的合并区域实际上是7个区域组成的,其中有两个区域是做为一个合并区输入的，他们分别是：90#NAME?97ref77280#NAME?合并区域不是引用，它不能作为参数类型只为Range的函数的参数，我们不能在函数的range参数中输入合并区域，例如下例就出现参数类型错误：连续多表三维引用是引用多张连续排列的工作表中相同行列位置的单元格或连续单元格区域的表示方法。由于多表三维引用是一个在表/行/列三个方向上的引用所以我们无法直接将在一张表的单元格区域中直接展示出来，但是大家可以透过下图去