(教程)引用函数产生的多维引用及其应用xls.xls

浅谈在引用函数中使用数组参数产生的多维引用及其应用 第一部分 通常我们所说的三维引用都是指Excel帮助中定义的跨多表的相同位置区域的引用 而此文重点要说明的是另一种由引用函数产生的三维以上 含 的引用 本文分为三部分 1 认识引用和区域及其维数2 引用函数产生的多维引用3 认识引用和区域及其维数 下表为一张成绩表 在下面的举例中会多次用到 姓名语文数学英语 张三857280 李四996490 王五956897 引用的类型 引用是对工作表上单元格或单元格区域的标识 从引用的范围看一般有 单个单元格引用 多个连续单元格的区域引用和连续多表三维引用 从引用产生的方式上看 有直接输入标识的引用和引用函数产生的引用 另外还有交叉引用 以及由引用构成的合并区域等形式 其中交叉引用不是我们要说明的重点 单个单元格引用 是指对工作表中某个单元格的引用 如 姓名 C8 区域引用 是指对一个连续单元格区域的引用 可以是一行多列的单元格区域 或多行一列的单元格区域 还可以是多行多列的单元格区域 单元格区域引用的结果会产生一个单元格值组成的数组 其中一行多列或多行一列的单元格区域引用产生的是一维数组 而多行多列的单元格区域引用产生的是二维数组 所以我们需要以数组公式的形式输入才能让其在单元格中正确显示 一行多列多行一列 姓名语文数学姓名 C8 E8张三 C8 C11 李四 王五 合并区域 在介绍三维引用前 我们有必要先解释一个多区域合并的概念 其和区域引用一起合称为区域 将多个单元格或区域引用 用逗号隔开并用括号 合并起来表示 就是合并区域 合并区域是虽是平面二维的 但无法在一个连续的单元格区域中显示 也不能形成一个按行列整齐排列的二维的数组 和我们要说的连续多表三维引用和引用函数产生的多维引用不同 它只是分散在同一个工作表中的几个区域的集合 一个合并区域的例子 VALUE D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 合并区域在单元格中无法正确显示 不论其实际合并后所代表的区域是否连续 但并影响我们将其作为一个参数用于可使用区域参数的函数中参与计算 返回引用区域的地址 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个区域组成的 其中有两个区域是做为一个合并区输入的 他们分别是 ref1ref2ref3 85 D9 D1099649064 99 D10 F1068 ref4ref5ref6 80 F9 F10958572 90 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 F9 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 E10 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 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 连续多表三维引用 连续多表三维引用是引用多张连续排列的工作表中相同行列位置的单元格或连续单元格区域的表示方法 连续多表三维引用的例子 REF 成绩表1 成绩表3 B3 D5 由于多表三维引用是一个在表 行 列三个方向上的引用所以我们无法直接将在一张表的单元格区域中直接展示出来 但是大家可以透过下图去理解 把每张表的区域看是一个平面 那么多表三维引用就是在多个平面上的不同区域引用组成的 而且每个平面区域引用的尺寸是一样的 857280 996490 956897 957582 926888 897098 919981 876299 937293 测试是否为引用 结果为否 FALSE ISREF 成绩表1 成绩表3 B3 D5 连续多表三维引用 不是真正的引用 它无法应用于Range参数类型的函数 例如 VALUE COUNTIF 成绩表1 成绩表3 B3 D5 80 下表为帮助中列出的支持连续多表三维引用的函数列表 此外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 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 函数虽然是求区域中区域引用的个数的 但其只适用于同一个工作表中的区域 即同一个平面上的区域 因此Areas不能用来统计三维引用区域的个数 通过引用函数产生的单个单元格 单元格区域引用 如果不直接输入引用的标识 我们还可以通过Index Offsett Indirect 函数来产生对单元格和单元格区域的引用 这里主要是要说明三个函数是如何返回单个单元格引用和多个单元格区域引用 所以对三个函数的用法就不作更详细地介绍了 Index产生的单个单元格 单元格区域引用 Index的第一参数为不连续区域时 可指定返回其中一个区域或区域中某一行列或某一单元格的引用 通过之前合并区域中的例子 我们已经了解了 当第一参数为连续区域时 除了不用使用第4个参数外 其他都是一样的 这里不再重复 要强调的是 Index第一参数为区域时 其返回的值的类型为单元格引用 857280 996490 956897 957582 926888 897098 919981 876299 937293 我们可以通过两个例子自来作进一步的说明 例 使用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 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 可以产生对另一个单元格或单元格区域的引用 例 引用成绩表格中第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 b4 例 引用成绩表格局部区域 9964 INDIRECT d10 e12 9568 以上三个引用函数中都不带数组参数 因此只能返回单个单元格引用或一个二维以内的区域引用 并且都能在单元格中直接显示出来 在合并区域中使用引用函数产生的单元格或区域引用 对引用函数产生的二维以内的引用进行区域合并 也同样能得到同在一个平面的二维区域引用的集合 但其也不是三维引用 VALUE OFFSET C 8 2 1 INDIRECT c10 d12 INDEX C 8 F 11 1 上述公式的结果无法正确显示 但实际上是返回了下面的各区域或引用的合并区域 999964 D10 E12姓名 D109568张三 李四 王五 测试此合并区域的区域引用数 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 小结 从上述内容 我们可以了解单个单元格引用 一维区域引用 二维区域引用 合并区域和连续多表三维引用的特点及用法 以及如何用函数来返回二维以内的单元格和区域引用 引用函数产生的多维引用的应用实例 多行多列 姓名语文 张三85 C8 D11 李四99 王五95 未经许可 请勿抄载 Apolloh 黄朝阳 友情提示 本工作簿中使用了宏表函数来显示 公式文本 为了使公式文本能被正确地显 示出来 请确认您的Excel中的宏安全性 是否允许执行宏 浅谈在引用函数中使用数组参数产生的多维引用及其应用 第一部分 通常我们所说的三维引用都是指Excel帮助中定义的跨多表的相同位置区域的引用 而此文重点要说明的是另一种由引用函数产生的三维以上 含 的引用 是指对一个连续单元格区域的引用 可以是一行多列的单元格区域 或多行一列的单元格区域 还可以是多行多列的单元格区域 单元格区域引用的结果会产生一个单元格值组成的数组 其中一行多列或多行一列的单元格区域引用产生的是一维数组 而多行多列的单元格区域引用产生的是二维数组 将多个单元格或区域引用 用逗号隔开并用括号 合并起来表示 就是合并区域 合并区域是虽是平面二维的 但无法在一个连续的单元格区域中显示 也不能形成一个按行列整齐排列的二维的数组 和我们要说的连续多表三维引用和引用函数产生的多维引用不同 它只是分散在同一个工作表中的几个区域的集合 合并区域在单元格中无法正确显示 不论其实际合并后所代表的区域是否连续 但并影响我们将其作为一个参数用于可使用区域参数的函数中参与计算 注意 合并区域中的第一个区域只有第一个单元格的地址出现在Cell address 的返回结果中 其他区域则是完整的 90 E10 F11 97 ref7 7280 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 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 成绩表1 B3 C4 连续多表三维引用是引用多张连续排列的工作表中相同行列位置的单元格或连续单元格区域的表示方法 由于多表三维引用是一个在表 行 列三个方向上的引用所以我们无法直接将在一张表的单元格区域中直接展示出来 但是大家可以透过下图去理解 把每张表的区域看是一个平面 那么多表三维引用就是在多个平面上的不同区域引用组成的 而且每个平面区域引用的尺寸是一样的 857280 996490 956897 957582 926888 897098 919981 876299 937293 857280 996490 956897 957582 926888 897098 919981 876299 937293 单击要输入函数的单元格 键入 等号 再输入函数名称 然后键入左圆括号 单击需要引用的第一个工作表标签 按住 Shift 单击需要引用的最后一个工作表的标签 选定需要引用的单元格或单元格区域 完成公式 再按 Enter 连续多表三维引用 虽然称作引用但是其不能用于引用类型Range为参数的函数 如Sumif Countif 等 对于大多数以reference或ref为参数的函数 也不能使用连续多表三维引用作为参数 但有一个例外 就是Rank函数 Areas 函数虽然是求区域中区域引用的个数的 但其只适用于同一个工作表中的区域 即同一个平面上的区域 因此Areas不能用来统计三维引用区域的个数 如果不直接输入引用的标识 我们还可以通过Index Offsett Indirect 函数来产生对单元格和单元格区域的引用 这里主要是要说明三个函数是如何返回单个单元格引用和多个单元格区域引用 所以对三个函数的用法就不作更详细地介绍了 Index的第一参数为不连续区域时 可指定返回其中一个区域或区域中某一行列或某一单元格的引用 通过之前合并区域中的例子 我们已经了解了 数学英语 Index对区域中某一列的引用 姓名 张三 INDEX C 8 F 11 1 李四 王五 C8 C11 Index对区域中某一行的引用 而对于不连续区域Index则可以返回其中的一整个连续区域 但也不能返回这个连续区域的多行或多列 通过指定一个单元格或区域引用 行列偏移量 区域的高度和宽度等参数 offset 可以产生对另一个单元格或单元格区域的引用 OFFSET 成绩表3 A 2 2 1 例 引用成绩表格中第3行第2列的单元格起 高为2 宽为2的单元格区域 这是多个单元格区域引用需要以数组公式的方式返回 以上三个引用函数中都不带数组参数 因此只能返回单个单元格引用或一个二维以内的区域引用 并且都能在单元格中直接显示出来 对引用函数产生的二维以内的引用进行区域合并 也同样能得到同在一个平面的二维区域引用的集合 但其也不是三维引用 从上述内容 我们可以了解单个单元格引用 一维区域引用 二维区域引用 合并区域和连续多表三维引用的特点及用法 以及如何用函数来返回二维以内的单元格和区域引用 将多个单元格或区域引用 用逗号隔开并用括号 合并起来表示 就是合并区域 合并区域是虽是平面二维的 但无法在一个连续的单元格区域中显示 也不能形成一个按行列整齐排列的二维的数组 浅谈在引用函数中使用数组参数产生的多维引用及其应用 第二部分 本文分为三部分 1 认识引用和区域及其维数2 引用函数产生的多维引用3 用OFFSET INDIRECT来对单元格和区域进行引用时 如果部分或全部参数使用数组 就会产生一个三维甚至三维以上的引用 下面我们将两个函数产生多维引用的情况进行解析 下表在下面的举例中会多次用到 111111111111111111111 222222222222222222222 333333333333333333333 444444444444444444444 555555555555555555555 引用函数产生的多维引用 什么是多维的引用 单个引用和区域引用及合并区域都是在一个平面上 其中合并区域 是使得平面上同时放置了多个独立的单元格或区域引用 而 维引用实际上是将各个引用区域放到了不同的平面的 其中每个平面只有一个单元格或区域引用 不同平面的引用形成一个空间 维引用实际上就是有 个以上象 维引用这样的空间 形成一个外套的空间 维 维 维 维 以此类推就是空间外再套空间 首先我们先来做几个推断 之后通过一些实例大家可以一起来证明这些推断是否正确 推断 函数产生的多维引用的基本元素是存放于每个平面上的单个单元格或区域引用 只有先将这些区域转换成常数 多维引用才能被应用于数组运算 推断 Offset函数的各参数及Indierect的第一参数都可以使用数组 但每个数组参数的维数最多不能超过2维 因为Excel函数不能正确处理3维以上的数组 推断 在Offset函数中任何一个参数中增加数组的维数 是否会对offset产生引用的维数产生影响 具体还需要看每个数组参数的维度方向是否一致 推断 在已经有其他参数是数组的情况下 增加另一个一元数组参数 不会对引用的维数产生影响 推断 一些参数 如offset的height和width中所使用的数组的行列方向是否一致会影响引用产生的各区域的尺寸大小 推断 Excel函数能处理的引用最多只有4维 可同过函数将其中2维平面先转换计算成常量 然后形成一个2维以内的数组参与数组运算 推断 超过3维的引用无法在单元格区域中展开直接显示出来 引用函数产生的多维引用的维数变化 单个引用 区域引用和合并区域都是在一个平面上的3维引用是一个空间包含一个以上的平面 每个平面上都是一个连续的区域引用 上图是下列公式的图解 其实质是Row参数变化产生了多个平面 VALUE 按F9可以看到公式返回 VALUE VALUE VALUE 由于三维数组无法显示出来所以返回了一个按行方向排列的一维3 1的区域数组 每个 VALUE 都代表一个区域 区域数组 把一个区域看成是一个元素 那么多个区域按一定排列顺序组成的数组叫区域数组 注 把参数改为一元数组如 1 这样的数组 结果也是3维引用 虽然其只产生一个平面 但仍然是一个三维空间 4维引用实际上是 多个三维引用构成的外套空间 单个引用 1 区域引用 22222 33333 合并区域 122222 33333 111 222 222 333 333 444 111 222 222 333 333 444 11111 22222 22222 33333 33333 44444 Row参 数变化 产生多 个平面 Col参数变化产生的多个空间 只在一个参数中使用二维数组产生的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维引用结果 由于函数的参数有多个 每个参数都支持二维数组 一一列举其多维引用的变化将是一项很庞大的工作 而且不是每种情况都有实用价值 所以维数变化就说这么多 大家可以从实例中去体会 引用函数产生的多维引用中每一区域的尺寸变化 引用区域高度和宽度产生的变化 右图是由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 OFFSET 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维引用 222 333 111 222 222 333 333 444 333 444 444 555 Row参 数行方 向变化 产生多 个平面 Row参数列方向变化产生的多个空间 1 1 2 1 2 3 1 2 3 4 1 2 3 4 5 1 111 222 111111 222222 333333 Reference参数 数组中数行方向 变化产生多个平 Reference参数列方向变化产生的多个空间 22222 33333 44444 55555 222222222 333333333 444444444 555555555 1111113333333 4444444 3333333333 4444444444 5 Ref text参数数组 中数行方向变化 产生多个平面 Col参数变化产生的多个空间 引用函数产生的跨多表多维引用 Indirect的Ref text参数使用二维数组产生的跨多表的4维引用 学年上的变化 这是跨多表的多维引用 其中Indirect的Reft text参数使用了二维数组产生了一个2 2的二维区域数组 VALUE INDIRECT 成绩表1 成绩表2 成绩表3 成绩表4 VALUE VALUE 一共2 2 4个区域 Offset与Indirect结合的跨多表引用 右图中Indirect产生一个引用维数的变化 offset的height参数产生了一个区域尺寸 高度 的变化 姓名 注意 Indirect中的Ref text参数同offset中的height参数是一一对应的 连续多表三维引用和引用函数产生的多维引用的区别 1连续多表三维引用是将整个引用作为一个结果返回给Excel 而引用函数产生的三维引用以及多维引用是将不同空间不同平面上的区域引用作为多个结果返回给Excel 2其支持的函数也大不相同 连续多表三维引用做为参数支持的函数只对该参数返回一个结果 而引用产生的多维引用做为参数的函数对其引用中的每个区域分别计算后返回多个结果 3连续多表三维引用是其三维结构是物理存在的 而引用函数产生的多维引用其结构是虚拟获得的 4连续多表三维引用每个平面区域的大小和行列位置时相同的 而引用函数产生的多维引用每个区域引用的大小和行列位置都可以不同 小结 1从上面的分析 我们可以了解引用函数产生的多维引用是如何形成的 数组参数的维数 维度 行列方向 值的变化会对引用结果的维数 引用区域的位置和尺寸产生影响 Ref text参数列方向变化产生的多个空间 Ref text 参数数组 中数行方 向变化产 生多个平 面 Ref text参数列方向变化产生的多个空间 第一学期成绩表 姓名语文数学英语 张三857280 李四996490 王五956897 第二学期成绩表 姓名语文数学英语 张三957582 李四926888 王五897098 第三学期成绩表 姓名语文数学英语 张三919981 李四876299 王五937293 第四学期成绩表 姓名语文数学英语 张三919981 李四876299 王五937293 英语 81 99 93 Indirect的 Ref text 参数数组 中数行方 向变化产 生多个平 面 Ref text参数列方向变化产生的多个空间 姓名语文 95 数学 99 62 Offset的height参数产生一个区域高度的变化 1111113333333 4444444 3333333333 4444444444 5 Ref text参数数组 中数行方向变化 产生多个平面 24维以下的多维引用可以返回一个二维以内的 区域数组 我们只要将 区域数组 中的每个区域用函数分别同步求值 就可以获得一个二维以内的数组 引用函数产生的多维引用的应用实例 OFFSET B 7 0 1 2 2 2 右图是由两个参数使用一维数组产生的不同方向上的变化 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中的宏安全性 是否允许执行宏 浅谈在引用函数中使用数组参数产生的多维引用及其应用 第二部分 用OFFSET INDIRECT来对单元格和区域进行引用时 如果部分或全部参数使用数组 就会产生一个三维甚至三维以上的引用 下面我们将两个函数产生多维引用的情况进行解析 单个引用和区域引用及合并区域都是在一个平面上 其中合并区域 是使得平面上同时放置了多个独立的单元格或区域引用 而 维引用实际上是将各个引用区域放到了不同的平面的 其中每个平面只有一个单元格或区域引用 不同平面的引用形成一个空间 函数产生的多维引用的基本元素是存放于每个平面上的单个单元格或区域引用 只有先将这些区域转换成常数 多维引用才能被应用于数组运算 Offset函数的各参数及Indierect的第一参数都可以使用数组 但每个数组参数的维数最多不能超过2维 因为Excel函数不能正确处理3维以上的数组 在Offset函数中任何一个参数中增加数组的维数 是否会对offset产生引用的维数产生影响 具体还需要看每个数组参数的维度方向是否一致 在已经有其他参数是数组的情况下 增加另一个一元数组参数 不会对引用的维数产生影响 一些参数 如offset的height和width中所使用的数组的行列方向是否一致会影响引用产生的各区域的尺寸大小 Excel函数能处理的引用最多只有4维 可同过函数将其中2维平面先转换计算成常量 然后形成一个2维以内的数组参与数组运算 3维引用是一个空间包含一个以上的平面 每个平面上都是一个连续的区域引用 上图是下列公式的图解 其实质是Row参数变化产生了多个平面 按F9可以看到公式返回 VALUE VALUE VALUE 由于三维数组无法显示出来所以返回了一个按行方向排列的一维3 1的区域数组 每个 VALUE 都代表一个区域 区域数组 把一个区域看成是一个元素 那么多个区域按一定排列顺序组成的数组叫区域数组 注 把参数改为一元数组如 1 这样的数组 结果也是3维引用 虽然其只产生一个平面 但仍然是一个三维空间 111 222 222 333 333 444 11111 22222 22222 33333 33333 44444 右图是由一个参数使用二维数组产生的不同方向上的变化 VALUE 按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参数使用二维数组产生的各区域尺寸大小的不规则变化 右图是由Ref text参数使用二维数组产生的各区域尺寸大小的不规则变化 因为Excel最多只能把4维引用处理成2维数组 再增加维数 Excel就无法进一步处理了 所以只能返回其中的第一个4维引用结果 由于函数的参数有多个 每个参数都支持二维数组 一一列举其多维引用的变化将是一项很庞大的工作 而且不是每种情况都有实用价值 所以维数变化就说这么多 大家可以从实例中去体会 右图是由Height参数使用一维数组产生的各区域引用的高度的变化 OFFSET B 7 1 2 3 4 5 右图是由Height和width参数使用一维数组产生的各区域引用的高度和宽度同时变化 OFFSET B 7 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的多维引用则不同 其每个区域的尺寸大小是可以任意变化的 可以无规律性 222 333 111 222 222 333 333 444 333 444 444 555 22222 33333 44444 55555 222222222 333333333 444444444 555555555 1111113333333 4444444 3333333333 4444444444 5 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 D2 1 2 3 4 1111113333333 4444444 3333333333 4444444444 5 这是跨多表的多维引用 其中Indirect的Reft text参数使用了二维数组产生了一个2 2的二维区域数组 右图中Indirect产生一个引用维数的变化 offset的height参数产生了一个区域尺寸 高度 的变化 注意 Indirect中的Ref text参数同offset中的height参数是一一对应的 连续多表三维引用是将整个引用作为一个结果返回给Excel 而引用函数产生的三维引用以及多维引用是将不同空间不同平面上的区域引用作为多个结果返回给Excel 其支持的函数也大不相同 连续多表三维引用做为参数支持的函数只对该参数返回一个结果 而引用产生的多维引用做为参数的函数对其引用中的每个区域分别计算后返回多个结果 连续多表三维引用是其三维结构是物理存在的 而引用函数产生的多维引用其结构是虚拟获得的 连续多表三维引用每个平面区域的大小和行列位置时相同的 而引用函数产生的多维引用每个区域引用的大小和行列位置都可以不同 从上面的分析 我们可以了解引用函数产生的多维引用是如何形成的 数组参数的维数 维度 行列方向 值的变化会对引用结果的维数 引用区域的位置和尺寸产生影响 第一学期成绩表 姓名语文数学英语 张三857280 李四996490 王五956897 第二学期成绩表 姓名语文数学英语 张三957582 李四926888 王五897098 第三学期成绩表 姓名语文数学英语 张三919981 李四876299 王五937293 第四学期成绩表 姓名语文数学英语 张三919981 李四876299 王五937293 Offset的height参数产生一个区域高度的变化 4维以下的多维引用可以返回一个二维以内的 区域数组 我们只要将 区域数组 中的每个区域用函数分别同步求值 就可以获得一个二维以内的数组 由于三维数组无法显示出来所以返回了一个按行方向排列的一维3 1的区域数组 每个 VALUE 都代表一个区域 注 把参数改为一元数组如 1 这样的数组 结果也是3维引用 虽然其只产生一个平面 但仍然是一个三维空间 按F9可以看到公式返回 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参数使用二维数组产生的各区域尺寸大小的不规则变化 其支持的函数也大不相同 连续多表三维引用做为参数支持的函数只对该参数返回一个结果 而引用产生的多维引用做为参数的函数对其引用中的每个区域分别计算后返回多个结果 按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 引用函数产生的多维引用的应用实例 可以使用引用函数产生的多维引用的作为参数的函数 1通过对引用函数产生的多维引用的讨论 我们已经得知 多维引用在没有使用函数将其各区域计算处理成常量前是无法显示 也不能作为内存数组直接参与计算 2目前已知的可以支持引用函数产生的多维引用 并将引用中的各区域计算后作为内存数组返回的函数有 COUNTBLANK COUNTIF D数据库函数 RANK SUBTOTAL SUMIF 等 3引用函数产生的多维引用经过上述函数计算后的结果值是一个二维以内的数组 其元素的个数和多维引用所能返回的区域个数相同 4其中 和 函数比较特殊 他们只能返回每个区域的第一个值 并将其转和为数值或文本 所以当多维引用的每个区域都是一个单元格时 使用这两个函数比较合适 5除了Rank函数和一些 数据库函数外其他函数的多维引用计算都有很高的实用价值 下面将以几种典型的多维引用方式来说明这些函数是如何进行多维引用计算 区域为单行单列的的多维引用 学生成绩表 语文数学英语 孙二836278 张三669857 李四976153 王五657665 赵六557064 钱七897773 例1 求表一中每个学生的总成绩 返回内存数组 例2 求表一中每个学生的最高单科成绩和平均成绩 Subtotal 9 SumifSubtotal 4 subtotal 1 孙二223223孙二8374 张三221221张三9874 李四211211李四9770 王五206206王五7669 赵六189189赵六7063 钱七239239钱七8980 例4 求表一中各科的最高成绩例5 求表一中各科的最低成绩 Subtotal 4 列方向 Subtotal 5 语文数学英语语文数学英语 979878556153 水果上市的销售数量水果单价 5号6号7号单价 苹果2933苹果1 5 1101AVERAGE 2102COUNT 3103COUNTA 4104MAX 5105MIN 6106PRODUCT 7107STDEV 8108STDEVP 9109SUM 10110VAR 11111VARP Subtal参数对照表 香蕉3521香蕉1 6 李子1430李子1 7 栗子153216栗子2 梨子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 00 1 1晚餐11119 00 1 2中餐1111127 00 1 2晚餐113 00 1 3中餐112 00 1 3晚餐1118 00 1 4中餐111122 00 1 4晚餐1115 00 这是我曾出过的一道测试题 也有不用多维引用的解法 其主要目的是要说明内存数组的概念的 具体见贴子链接 区域的尺寸递增或递减的多维引用 纸箱规格 厘米 例10 求表五中纸箱的体积 立方厘米 长框高Subtotal 6 L001803649L001141 120 L002923559L002189 980 L003653253L003110 240 L004672739L00470 551 L005673455L005125 290 L006932932L00686 304 水果清单例12 求表六中不重复的水果清单现金收入支出表 苹果 VALUE 收入 香蕉 VALUE 2005 4 15 000 李子 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函数对一个区域高度递增的三维引用计算 环比成长率表例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行为区域的多维引用 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多维引用计算来实现的