浅谈excel函数引用.doc

浅谈excel函数引用、数组、内存数组我们编写函数公式的时候往往会遇到一些问题，而造成了我们认为正确的公式可是就是不能返回正确的结果，或者干脆EXCEL就不让我们输入。这是什么原因呢？举个例子比如我们输入 =COUNTIF(1,2,3,2)按回车， 呃？报错！ 为什么呢？ 我们输入 =SUM(INDEX(ROW(1:10),ROW(1:3) 按CTRL+SHIFT+ENTER 结果为1，应该为3啊！ 这又是为什么呢？ 等等这些让我们认为是莫名奇妙的问题，它们到底是什么问题呢？第一个问题是因为COUNTIF函数的第一个参数只能支持区域的引用，而不可以为数组，像这样的函数还有SUMIF（第一和第三个参数） SUBTOTAL（第二个参数）D大头的数据库函数（第一第二个参数） RANK（第二个参数）COUNTBLANK 等等。第二个问题就是SUM()里面的INDEX(ROW(1:10),ROW(1:3) 返回的不是一个内存数组。而这些就是我今天要和大家讲的关于区域引用，数组，内存数组这方面的问题。 数组我就不用讲了吧，想想那串糖葫芦就好了_。那么什么是区域引用呢？这个其实非常好理解，区域引用就是你可以直接通过它在EXCEL中找到属于它的位置。 比如A1 ，A1:A10 ,B2:D15.这中直接的引用另外就是常用的OFFESET INDIRECT这两个函数了，=OFFSET(A1,1,2,5,) =INDIRECT(A&ROW(1:5) 等等，这些你都可以直接在EXCEL找到它的位置。另外这里的判断是整个一个参数，比如我们经常想用的一个公式吧 =COUNTIF(A1:A10&B1:B10,F1&F2) 这也是不可以的，为什么呢？大家可能会问了A1:A10和B1:B10这不都是引用么，EXCEL判断A1:A10&B1:B10作为一个整体的 它是这两个区域每两个数值连接在一起的一个数组下面我们就要讲讲内存数组了。 那么什么是内存数组呢？简单一句话：内存数组可以进一步作为别的函数的参数的数组。比如：如果你在表中选取众向的3个单元格（A1:A3)输入INDEX(ROW(1:10),ROW(1:3) 可以显示1；2；3是数组没问题，但是把它作为SUM的参数时返回不了正确的结果。如果我们输入=SUM(SMALL(ROW(1:10),ROW(1:3) 按CTRL+SHIFT+ENTER得到结果6 没问题。这就是因为INDEX(ROW(1:10),ROW(1:3)不是内存数组，而SUM(SMALL(ROW(1:10),ROW(1:3) 是内存数组。 那么内存数组有什么作用呢？正因为内存数组可以进一步作为别的函数的参数，所以我们可以将其作为公式众的一个环节应用。生成的内存数组我们还可以直接用来作为图表的数据源（放到定义名中） 如何区别是否为内存数组呢？ 也用其可以被别的函数作为参数的特点，直接让其作为其他函数的参数，返回结果真确就是内存数组了，比如比较常用的就是 =INDEX(要判断的内存数组,ROW(1:1) 然后下拉看看是不是能正确返回。 至于要生成内存数组，就需要大家在不断的实践中慢慢积累经验了。多换角度去思考问题就可以了。比如返回一个区域每行和的内存数组的话，我们就别总见到求和就SUM,因为SUM基本不能生成内存数组的，而这种问题往往就用MMULT函数。另外就是什么MIN MAX 也避免用其返回内存数组，像INDEX我们也可以用lookup来代替它。数组就是单元的集合或是一组处理的值集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果每个结果显示在一个单元中。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。数组的元素可多达6500个。（1）了解数组首先我们通过几个例子来说明数组是如何工作的。我们可以从图中看到，在“B”列中的数据为销售量，在“C”列中的数据是销售单价，要求计算出每种产品的销售额和总的销售金额，一般的做法是计算出每种产品的销售额，然后再计算出总的销售额。但是如果我们改用数组，就可以只键入一个公式来完成这些运算。输入数组公式的步骤为：选定要存入公式的单元格，在本例中我们选择“D4”单元格。输入公式=SUM(B2:B4*C2:C4)，但不要按下Enter键（输入公式的方法和输入普通的公式一样），按下Shift+Ctrl+Enter键。我们就会看到在公式外面加上了一对大括号“”，如图 7-36所示。在单元格“D”中的公式“=SUM(B2:B4*C2:C4)”, 表示“B2: B4”范围内的每一个单元格和“C2：C4”内相对应的单元格相乘，也就是把每个地区的销售量和销售单价相乘，相乘的结果共有3个数字，每个数字代表一个地区的销售额，而“SUM”函数将这些销售额相加，就得到了总的销售额。下面我们再以使用数组计算3种产品的销售额为例，来说明如何产生多个计算结果。其操作过程如下：(1) 选择“D2：D4”单元格区域，该区域中的每个单元格保存的销售金额。如图7-37所示。(2) 在“D2”单元格中输入公式“=B2:B4*C2:C4”（不按Enter键）按下Shift+Ctrl+Enter”键，我们就可以从图7-38中看到执行后的结果。同时我们可以看到“D2”到“D4”的格中都会出现用大括弧“ ”框住的函数式，这表示“D2” 到“D4”被当作一个单元格来处理，所以不能对“D2”到“D4”中的任一格作任何单独处理，必须针对整个数组来处理。（2）使用数组常数我们也可以在数组中使用常数值。这些值可以放在数组公式中使用区域引用的地方。要在数据公式中使用数组常数，直接将该值输入到公式中并将它们放在括号里。例如，在图7-39中，就使用了数组常数进行计算。常数数组可以是一维的也可以是二维的。一维数组可以是垂直的也可以是水平的。在一维水平数组中的元素用逗号分开。下面是一个一维数组的例子。例如数组：10,20,30,40,50。在一维垂直数组中的元素用分号分开。在下面的例子是一个61的数组，100;200;300;400;500;600。对于二维数组，用逗号将一行内的元素分开，用分号将各行分开。下一个例子是“4 4”的数组（由4行4列组成）：100,200,300,400;110, ;130,230,330,440。注意：不可以在数组公式中使用列出常数的方法列出单元引用、名称或公式。例如：2*3,3*3,4*3因为列出了多个公式，是不可用的。A1,B1,C1因为列出多个引用，也是不可用的。不过可以使用一个区域，例如A1:C1。对于数组常量的内容，可由下列规则构成：数组常量可以是数字、文字、逻辑值或错误值。数组常量中的数字，也可以使用整数、小数或科学记数格式。文字必须以双引号括住。同一个数组常量中可以含有不同类型的值。数组常量中的值必须是常量，不可以是公式。数组常量不能含有货币符号、括号或百分比符号。所输入的数组常量不得含有不同长度的行或列。（3）数组的编辑数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能单独编辑。在编辑数组前，必须先选取整个数组。选取数组的步骤为：(1) 选取数组中的任一单元格。(2) 在“编辑”菜单中选择“定位”命令或者按下F5键，出现一个“定位”。按下“定位条件”按钮，出现一个定位条件对话框，如图7-40所示。选择“当前数组”选项，最后按下“确定”按钮，就可以看到数组被选定了。编辑数组的步骤为:选定要编辑的数组，移到数据编辑栏上按F2键或单击左键，使代表数组的括号消失，之后就可以编辑公式了。编辑完成后，按下Shift +Ctrl + Enter键。若要删除数组，其步骤为：选定要删除的数组，按Ctrl+Delete或选择编辑菜单中的“清除”。（4）数组的扩充在公式或函数中使用数组常量时，其它运算对象或参数应该和第一个数组具有相同的维数。必要时，Microsoft Excel 会将运算对象扩展，以符合操作需要的维数。每一个运算对象的行数必须和含有最多行的运算对象的行数一样，而列数也必须和含有最多列数对象的列数一样。例如: = SUM(1,2,3+4,5,6）内的第一个数组为13,得到的结果为1+4、2+5和3+6的和,也就是21。如果将公式写成 = SUM(1,2,3+4）,则第二个数据并不是数组，而是一个数值，为了要和第一个数组相加，Excel 会自动将数值扩充成1 3 的数组。使用=SUM(1,2,3+4,4,4)做计算，得到的结果为1+4、2+4和3+4的和, 即18。将数组公式输入单元格区域中时，所使用的维数应和这个公式计算所得数组维数相同。这样，Microsoft Excel 才能把计算所得的数组中的每一个数值放入数组区域的一个单元格内。如果数组公式计算所得的数组比选定的数组区域还小，则 Microsoft Excel会将这个数组扩展，以便将它填入整个数组区域内。例如：=1,2;3,4*2扩充后的公式就会变为=1,2;3,4*2,2;2,2，则相应的计算结果为“2,4,6,8”。再如：输入公式=1,2;3,4*2,3扩充后的公式就会变为=1,2;3,4*2,3;2,3 ，则相应的计算结果为“2,6,6,12”。如果 Microsoft Excel 将一个数组扩展到可以填入比该数组公式大的区域内，而没有扩大值可用的单元格内，这样就会出现#N/A错误值。例如：=1,2;3,4=1,2,3 扩充后的公式就会变为=1,2,#N/A;3,4,#N/A*1,2,#/A;1.2.#N/A ，而相应的计算结果为“2,4,#N/A,4,6,#N/A”。如果数组公式计算所得的数组比选定的数组区域还要大，则超过的值不会出现在工作表上使用 Excel 数组2007-11-14来源:网友评论0 条进入电脑报知道数组是一种计算工具，可用来建立对两组或更多组值进行操作的公式，这些值称为数组参数，数组公式返回的结果既可以是单个也可以是多个。数组区域是共享同一数组公式的单元格区域。数组公式是小空间内进行大量计算的强有力方法，它可以替代很多重复的公式。 一、数组公式的创建和输入 （1）如果希望数组公式返回一个结果，单击需要输入数组公式的单元格；如果希望数组公式返回多个结果，选定需要输入数组公式的单元格区域。 (2)键入公式的内容。 (3)按“Ctrl十Shift十Enter”组合键，锁定数组公式，Excel自动在编辑栏中公式的两边加上大括号，表明它是一个数组公式。 注意：不要自己键入大括号，否则Excel会认为输入的是一个正文标签。 例10.12 计算期末考试个人成绩总和。 (1)选定区域H4到H11，如图107所示。 (2)输入公式“B4:B11十C4：C11十D4：D11十E4：E11十F4：F11十G4：G11”。 (3)按“Ctrl十Shift十Enter”组合键结束输入，计算结果如图108所示。 输人数组函数的方法与输人数组公式的方法相同。 二、使用数组常量 在数组公式中，通常使用单元格区域引用，也可以直接键入数值数组，即数组常量。数组常量可由数、正文或逻辑值组成。使用数组常量时必须用大括号“”括起来，并且用逗号或分号分隔元素。逗号分隔不同列的值，分号分隔不同行的值。 例10.13 计算19九个数值的平方根并显示结果。 图10.7 选定数组公式结果输出区域 图10-8 数组公式的计算结果 (1)选择一块3行3列的单元格区域。 (2)链入“SQRT(1，2，3；4，5，6;7，8，9)”。 注意：必须键入大括号，表明括起来的值组成一个数组常量。 (3)按“Ctrl十Shift十Enter”组合键。计算结果如图10.9所示。 图10.9 数组常量计算结果 三、编辑数组公式或函数 编辑数组公式或函数时应注意以下几点： 在数组区域中不能编辑、清除和移动单个单元格，也不能插人或删除单元格。必须将数组区域的单元格作为一个整体然后同时编辑它们。 要移动数组区域的内容，需选择整个数组