excel数组函数

excel数组函数内容摘要：

excel数组函数 1 的数组 数组 （ 体版称作阵列），是由数据元素组成的集合，数据无素可以是数值 ,文本 ,日期 ,逻辑 ,错误值等。 数据元素以行和列的形式组织起来，构成一个数据矩阵。 在 ，根据构成元素的不同，可以把数组分为 常量数组 和 单元格区域数组。 常量数组可以同时包含多种数据类型。 它用 将构成数组的常量括起来，行中的元素用逗号“，”分隔，行之间用分号“ ;”分隔。 数组常量不能包含其他数组、公式或函数。 当输入如下所示的公式时， 显示警告消息： 1,2,4 或 1,2,2:。 另外，数值不能包含百分号、货币符号、逗号或圆括号。 例如， 2,"N/A, 是一个 1 行 4 列的常量数组，而1,2,3;4,5,6则为一个 2 行 3 列的常量数组。 单元格区域数组则是通过对一组连续的单元格区域进行引用而得到的数组。 在数组公式中 4是一个 4 行 2 列的单元格区域数组。 组的维数 数组作为数据的组织形式本身可以是多维的，而且众多编程都支持多维数组，但是 公式中最高只 支持 2 维数组 （也就是行列构成的数组）。 关于这个结论，在网络搜索不到明确的答案，做这个结论基于以下两点： 2 支持形如 = , 或 = ; 的书写格式，所以无法表示 3 维或多维数组。 在 提供对数组引用的函数为 ，它的格式为它只有行列两个参数，而没有引用高于 2 维数组的参数。 数 也 有 这 样 的 格 式它提供了第三个引用参数，是不是说明 供了对 3 维数组的引用呢。 答案是否定的， 以使用多个区域（区域之间有逗号隔开）构成的参数，第三个参数 用于指定 第几个区域的，如6,11),1,1,2)中第三个参数只是指定区域 11 而已。 持多维数组。 在运算过程中，可能需要引用数组中的某一个元素、某一行或某一列。 下面介绍 元素、行、列的引用方法。 元素的引用： 对于 1 维数组 c)，对于 2 维数组 r, c)。 其中 r 表示第几行， c 表示第几列，一般为正整数。 当 r 为小数时，会舍弃小数点后的部公（非四舍五入），如 同于 1, 2)。 编程语言一般默认以截取的方式处理小数转整数的操作，这样可以提高效率， 这样的处理也 3 是在意料之内的。 行引用 r, 0)返回第 r 行数据构成的数组。 列引 用 0 ,c)返回第 c 列数据构成的数组。 特殊的， 0, 0) 将返回整个 2 维 数 组。 而r, 0),c)、 0, c), r)和r, c)相同，都可以返回第 r 行第 c 列的元素。 和 数组的扩展 当数组进行运算（加、减、乘、除、幂）时，要求两个数组具有相同的维数，如果是 2 维数组还要求两数组的行数相同，列数也相同。 满足这个条件的两个数组的算术运算（加、减、乘、除、幂）等于对应元素之间进行数学运算。 如两个具有 3 行 3 列的 2 维数组之间的加法运算为： 如果参与运算的两个数据的维数不同、行数不同或列数不同， 对数据的维数和行列进行 扩展 ，以满足上述的要求而进行运算。 扩展的规则如下： 维数扩展 ：如果两数据的维数不同时，维数低的数据会被扩展为和维数高的数据的维数相同。 维 数的扩展有两种类型，常数向 1 维数组或 2 4 维数组的扩展、 1 维数组向 2 维数组扩展。 行列扩展 ：如果两数据的行数不同，行数低的数据会扩展为和行数高的数据的行数相同。 如果列数不同，列扩展的规则和行扩展相同。 如果行列均不相同，那么行列都需要扩展。 根据这些原则，一个 、 的数组和一个 、 的数组运算后，得到的是 1, 、 1, 的数组。 这个结论包含了所有类型数据的运算。 例如 1,2,3;4,5,6和 7,8,9,10运算，前者 ,，后者 ,，结果为 2 行 4 列的数组。 数据扩展后，出现了一此数据空位，如计算下列数组时 首先要把 (1 2)扩展为 这时出现了一些数据空位，那么 何填充这些空位的呢。 这里也有规则可循，个 人用实验的方法总结如下： 对常数所有的扩展，空位都填写该常数。 当行数组进行行扩展时，扩展出来的每一行的数据和首行相同；当行数组进行列扩展时，扩展列的数据填写错误值 #N/A。 当列数组进行列扩展时，扩展出来的每一列的数据和首列相同；当列数组进行行扩展时，扩展行的数据填写错误值 #N/A。 5 当 2维的数组进行行扩展或列扩展时，扩展出的数据都填写错误值 #N/A。 因此，上边那个例子， (1 2)应该扩展为： 因此计算的结果为： 有了这些规则，就可以进行任意数组、常数间的算术运算了。 是数组公式 数组公式 是指可以在数组的一项或多项上执行多个计算的公式。 数组公式可以返回多个结果，也可返回一个结果。 输入数组公式后，同时按下 组公式的外面会自动加上大括号 以和普通公式区分。 不是手工输入的，那样 组公式每次编辑过后，都要同时按下完成输入。 如果把数组公式的返回值放入到一个单元格区域内可以呈现返回数组的各个元素的值，这样的公式称为 多单元格公式。 而位于单元格中的数组公式称为 单元格公式。 多单格公式在使用时，需遵循以下原则： 6 必须在输入公式之前选择用于保存结果的单元格区域，这个区域最好和返回的数组尺寸相同，否则在超出数组尺寸范围外的单元格填写#N/A。 不能更改多单元格数组公式中部分单元格的内容，但可以移动或删除整个数组公式。 换言之，要编辑数组公式，需先删除现有公式再重新开始。 不能向多单元格数组公式中插入空白单元格（包括插入新行、列、单元格） 或删除其中的单元格。 这和“不能更改多单元格数组公式中部分单元格的内容”是一致的。 简洁性 ：数组公式可以同对多个数据执行多个运算。 解决一个复杂的问题可以只需要一个公式，而用普通公式可能需要多步运算，甚至要填加辅助列。 不过，数组公式可以用几个一般公式和一些过渡数据来代替，从而达到同样的目的，所以千万不要迷信数组公式，以免走火入魔。 一致性 ：多单元格数组公式中，点击任一个单元格，看到的公式内容都是相同的。 这种一致性可以保证更高的准确性。 安全性 ：不能覆盖多单元格数组公式的组成部分，可 以防止误操作。 文件小 ：通常可以使用单个数组公式，而不必用多个中间公式。 这在数据量很大的时候效果才会明显。 数组公式主要使用标准公式语法。 它们都以等号开始，可以在数组公 7 式中使用任何内臵 数。 使用数组公式的主要不同之处在于 : 必须按 入公式 输入多单元格公式时，必须首先选择用于保存结果的单元格区域 1. 计算一个区域内，不重复的数据的个数。 （个人认为这是数组函数最精彩的例子，正是这个例子激发了我学习数组公式的兴趣） = / 1:100) ，这里假设区域为 分析： 围内查找 返回它的个数。 假设 元格内的数据为 X 在 100 范围内共有。 那么数组公式对这 100 个单元格逐个计算 1 / 1:某一单元格 )，当对 算时，返回结果 1/N，在这 100 次计算里，每遇 到数据 X 就回返回一个 1/N，共返回 N 个 1/N，求和为 1。 也就是说，有多少个不同的数据就返回多少个 1，恰好就是不重复的数据的个数。 这个公式是多么简洁优美。 不过再美好的事物也是有缺点的，统计区域内不得有空单元格，否则返回 #!错误。 可以用下面的数组公式解决这个问题： =F(100<>"",1/1:1: 当然这个问题也可以用 、 或 实现。 2. 計 算 1+2+3+ .+100 的和。 （假 設 不知道等差求和公式） =1: 或 =:100) 注意： 的运算结果可以做很多函数的参数。 3. 條 件求和 8 A B C D 1 产品编号 生产日期 产品单价 产品数量 2 009/6/15 1 100 3 009/6/20 1 125 4 009/6/30 2 150 5 009/7/10 2 175 6 009/6/15 3 200 7 009/7/20 3 225 8 009/7/30 1 250 9 009/8/10 1 275 10 009/8/15 2 300 11 009/8/20 2 325 12 009/10/10 3 350 13 009/10/15 3 400 14 009/10/20 4 425 15 009/10/30 4 500 求 產 品 8 月份 產 量 =F(15="*(2:8), 15, 0) 注意：这里 的判断条件为 (15="*(2:8)， 9 如果把这部分改为 2: 2:8)，则不能工作。 起初这另我很不解，但仔细研究 发现， 处理常量数组和单元格区域数组的方式是不同的。 当处理常量数组时，运算结果还是数组。 如 =2)结果为 处理受单元格区域数组时，会把数组内的所有的数据都取出来作为多个参数，运 算 最 终 结 果 不 是 一 个 数 组 ， 而 仅 仅 是 一 个 逻 辑 值。 如=2: 2:8),1)会得到 # 因此， =F(2: 2:8), (15),。