Excel制表技巧外部函数数组公式.docx

Excel制表技巧外部函数数组公式.docx

《Excel制表技巧外部函数数组公式.docx》由会员分享，可在线阅读，更多相关《Excel制表技巧外部函数数组公式.docx（15页珍藏版）》请在冰豆网上搜索。

Excel制表技巧外部函数数组公式

【多年使用】Excel制表技巧－外部函数-数组公式

        Excel函数应用教程：

外部函数

       EUROCONVERT

　　用途:

将数字转换为欧元形式，将数字由欧元形式转换为欧盟成员国货币形式，或利用欧元作为中间货币将数字由某一欧盟成员国货币转化为另一欧盟成员国货币的形式（三角转换关系）。

　  语法:

EUROCONVERT（number，source，target，full_precision，triangulation_precision）

　　参数:

Number为要转换的货币值，或对包含该值的单元格的引用。

Source是由三个字母组成的字符串，或对包含字符串的单元格的引用，该字符串对应于源货币的ISO代码。

EUROCONVERT函数中可以使用下列货币代码:

国家/地区

基本货币单位

ISO代码

比利时

法郎

BEF

卢森堡

法郎

LUF

德国

德国马克

DEM

西班牙

西班牙比塞塔

ESP

法国

法郎

FRF

爱尔兰

爱尔兰磅

IEP

意大利

里拉

ITL

荷兰

荷兰盾

NLG

奥地利

奥地利先令

ATS

葡萄牙

埃斯库多

PTE

芬兰

芬兰马克

FIM

希腊

德拉克马

GRD

欧盟成员国

欧元

EUR

　　2.SQL.REQUEST

　　用途:

与外部数据源连接，从工作表运行查询，然后SQL.REQUEST将查询结果以数组的形式返回，而无需进行宏编程。

　　语法:

SQL.REQUEST（connection_string，output_ref，driver_prompt，query_text，col_names_logical）

　　参数:

Connection_string提供信息，如数据源名称、用户ID和密码等。

Output_ref对用于存放完整的连接字符串的单元格的引用。

Driver_prompt指定驱动程序对话框何时显示以及何种选项可用。

Column_names_logical指示是否将列名作为结果的第一行返回。

　　如果要将列名作为结果的第一行返回，请将该参数设置为TRUE。

如果不需要将列名返回，则设置为FALSE。

如果省略column_names_logical，则SQL.REQUEST函数不返回列名。

       Excel数组的使用

       数组就是单元的集合或是一组处理的值集合。

可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。

数组公式可以看成是有多重数值的公式。

与单值公式的不同之处在于它可以产生一个以上的结果。

一个数组公式可以占用一个或多个单元。

数组的元素可多达6500个。

       7.12.1了解数组

       首先我们通过几个例子来说明数组是如何工作的。

我们可以从图7-35中看到，在“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”中的任一格作任何单独处理，必须针对整个数组来处理。

        7.12.2使用数组常数

       我们也可以在数组中使用常数值。

这些值可以放在数组公式中使用区域引用的地方。

要在数据公式中使用数组常数，直接将该值输入到公式中并将它们放在括号里。

例如，在图7-39中，就使用了数组常数进行计算。

       常数数组可以是一维的也可以是二维的。

一维数组可以是垂直的也可以是水平的。

在一维水平数组中的元素用逗号分开。

下面是一个一维数组的例子。

例如数组：

{10,20,30,40,50}。

在一维垂直数组中的元素用分号分开。

在下面的例子是一个6×1的数组，{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}。

    对于数组常量的内容，可由下列规则构成：

    数组常量可以是数字、文字、逻辑值或错误值。

    数组常量中的数字，也可以使用整数、小数或科学记数格式。

    文字必须以双引号括住。

    同一个数组常量中可以含有不同类型的值。

    数组常量中的值必须是常量，不可以是公式。

    数组常量不能含有货币符号、括号或百分比符号。

    所输入的数组常量不得含有不同长度的行或列。

    7.12.3数组的编辑

    数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能单独编辑。

在编辑数组前，必须先选取整个数组。

    选取数组的步骤为：

（1）选取数组中的任一单元格。

（2）在“编辑”菜单中选择“定位”命令或者按下[F5]键，出现一个“定位”对话框。

按下“定位条件”按钮，出现一个定位条件对话框，如图7-40所示。

    选择“当前数组”选项，最后按下“确定”按钮，就可以看到数组被选定了。

    编辑数组的步骤为:

    选定要编辑的数组，移到数据编辑栏上按[F2]键或单击左键，使代表数组的括号消失，之后就可以编辑公式了。

编辑完成后，按下[Shift]+[Ctrl]+[Enter]键。

    若要删除数组，其步骤为：

选定要删除的数组，按[Ctrl]+[Delete]或选择编辑菜单中的“清除”命令。

    7.12.4数组的扩充

    在公式或函数中使用数组常量时，其它运算对象或参数应该和第一个数组具有相同的维数。

必要时，Microsoft Excel 会将运算对象扩展，以符合操作需要的维数。

每一个运算对象的行数必须和含有最多行的运算对象的行数一样，而列数也必须和含有最多列数对象的列数一样。

    例如:

=SUM（{1,2,3}+{4,5,6}）内的第一个数组为1×3,得到的结果为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数组公式实现条件统计

　　前两天遇到一位教师朋友，他向我请教一个问题：

学校为了研究男生和女生在学习上是否存在差别，在初二年级随机选定了100名学生（男生、女生各50名）作为研究对象，分男、女组统计他们每次考试成绩的最高（低）分、平均分、总分等，成绩表是用Excel制作的（如图1）。

其实这种统计可以用数组公式来实现。

文章末尾提供原文件供大家下载参考。

　　1、启动Excel2003（其他版本操作相似），打开成绩表。

　　2、在表格的下部，依照图2的样式，制作保存统计结果的表格。

（图片较大，请拉动滚动条观看）

　　3、选中D104单元格，输入公式：

=MAX（IF（$C$3:

$C$102=$C104,D$3:

D$102）），输入完成后，按下“Ctrl+Shift+Enter”组合键对公式进行确认。

　　注意：

我们这里输入的是一个数组公式，数组公式输入完成后，不能直接按“Enter”键进行确认，必须按“Ctrl+Shift+Enter”组合键进行确认。

数组公式被确认后，会在公式两端出现数组公式的标志符号——一对大括号{}（如图3）。

　　4、再次选中D104单元格，将鼠标移至该单元格右下角成细十字线状（填充柄）时，按住左键向下拖拉至D111单元格中。

　　5、然后将D106与D107、D108与D109、D110与D111单元格中第一个函数名称（MAX）分别修改为“MIN、AVERAGE、SUM”，每个公式修改完成后，均需要按“Ctrl+Shift+Enter”组合键进行确认。

　　注意：

统计最高分也可以用下述数组公式：

=MAX（（$C$3:

$C$102=$C104）*（D$3:

D$102））（D104单元格）；

　　统计总分也可以用下述数组公式：

=SUM（（$C$3:

$C$102=$C110）*（D$3:

D$102））（D110单元格），或者用非数组公式：

=SUMIF（$C$3:

$C$102,$C111,D$3:

D$102）（D111单元格）。

　　6、同时选中D104至D111单元格，用“填充柄”将上述公式拖拉复制至E104至K111单元格区域中，所有统计数据即刻呈现在我们面前（参见图2）。

     Excel函数的输入

      在工作表中，对于函数的输入我们可以采取以下几种方法，下面我们分别给予介绍。

      7.11.1手工输入函数

    手工输入函数的方法同在单元格中输入一个公式的方法一样。

我们需先在输入框中输入一个等号“=”，然后，输入函数本身即可。

例如，我们在单元格中输入下列函数：

   =SQRT（B1）

    =SUM（B2:

B6）

    提示：

手工输入方法输入函数，适用于一些单变量的函数，或者一些简单的函数。

对于参数较多或者比较复杂的函数，建议使用粘贴函数来输入。

       7.11.2使用粘贴函数输入

       使用粘贴函数是我们经常用到的输入方法。

利用该方法，可以指导我们一步一步地输入一个复杂的函数，避免我们在输入过程中产生键入错误。

其操作步骤如下：

（1）选定要输入函数的单元格。

例如，选定单元格“C3”。

执行“插入”菜单中“函数”命令，或者按下工具栏上的“

”粘贴函数按钮。

之后，系统在屏幕上出现一个“粘贴函数”对话框，如图7-32所示。

（2）从函数分类列表框中选择要输入的函数分类，例如，选择“统计”。

当选定函数分类后，再从“函数名”列表框中选择所需要的函数。

例如，选择求平均数函数“AVERAGE”。

按下“确定”按钮。

    当按下“下一步”按钮后，会看到在单元格“C3”中，粘贴函数将等号（=）紧跟的选定函数粘贴到插入点，并自动将函数输入到选定的单元格中，如图7-33所示。

表7-9列出了在使用“粘贴函数”时出现的项目或者按钮的说明。

     例如，在本例中当我们输入完第二个参数后，我们会看到出现第三个参数输入框，依次类推，还会出现第四个，如图7-34所示。

直到我们按下“确定”按钮。

参数框的数量，由函数决定。

       在输入参数的过程中，会看到对于每个必要的参数都输入数值后，该函数的计算结果就出现。

最后，按下“确定”按钮，将完成的函数输入到单元格中。

在输入过程中要使用[Tab]键而不是通常的[Enter]键。

       7.11.3在公式中输入函数

        在实际工作中，我们不仅要作各种简单的运算，而且有时需要进行非常复杂的运算。

这就需要在一个公式中输入函数。

例如，要输入下面一个公式：

    =A1-A3/（SUM（B2:

C3）*100）+100

    则其输入步骤如下:

    在编辑栏输入“=A1—A3/（”；执行粘贴函数2步骤之1，选择函数SUM，按下“下一步”按钮，进入到粘贴函数2步骤之2，单击工作表，然后选定单元格区域“B2:

C3”,我们会看到选定的区域出现在参数的输入框中；按下“确定”按钮，看到光标停留在编辑栏上，编辑栏的内容会变成“=A1-A3/（SUM（B2:

C3）”。

再在其后输入“*100）+100”，按下[Enter]键，即完成了对混合公式的输入。

        理解Excel函数

       Excel使用预先建立的工作表函数来执行数学、正文或者逻辑运算，或者查找工作区的有关信息。

只要有可能，我们应当尽可能地使用Excel系统提供的函数，而不是自己编写公式，利用函数不仅能够提高效率，同时，也能够减少我们的错误和工作表所占的内存空间，提高Excel的工作速度。

       若要使用函数，则将它们输入到工作表中的公式内。

在公式中使用的字符次序称为语法。

所有的函数都有相同的基本语法。

若不遵守这种语法，则MicrosoftExcel 将显示一条信息，指出在公式中有错误。

如果公式以一个函数开始，则应该像其它公式一样，在函数前面加一个等号。

括号告诉Microsoft Excel参数开始和结束的位置。

记住：

左右两个括号必须成对出现，括号前后都不能有空格。

请在括号中指定参数。

参数可以是数字、文字、逻辑值、数组、误差值或者引用位置。

指定的参数必须能产生一个有效值。

有些函数也接受计算时并不需要的可选参数。

        参数也可以是常量或者公式。

这些公式本身可以包含其他的函数。

如果一个函数的参数本身也是一个函数，则称为嵌套。

在Microsoft Excel中，一个公式最多可以嵌套七层函数。

       在Microsoft Excel中，工作表函数是能运用于工作表中以自动地实现决策、执行以及数值返回等操作的计算工具。

Microsoft Excel提供了大量能完成许多不同计算类型的函数。

       7.10.1有关函数的约定

       在语法行中，必选参数是粗体字而任选参数用纯文字（非粗体）表示。

       在函数和参数所定义的文字中，所有参数都是纯文字（非粗体），而不论是必选还是任选。

使用语法行中的格式来决定一个参数是必选还是任选。

关于必选和任选参数的详细信息，请参看本部分下面的“函数语法”。

参数名通常在词与词之间使用下划线字符；    函数显示时不用等号（＝）。

请记住在每一个公式的开头键入一个等号，但在嵌套公式中的函数前不要键入。

例如，PRODUCT在=SUM（3,（PRODUCT（2,4）））中是一个嵌套函数。

        7.10.2函数语法

        每一个函数描述都包括一个语法行。

例如，CELL函数的语法行如下：

CELL（info_type,reference）在语法行中，必选参数是粗体字；任选参数是非粗体字。

若用户没有给函数提供必选参数，则不能在单元格中输入函数。

在前面的例子中，参数Info_type是粗字体，因此它是必选参数。

Reference不是粗体，因而是任选参数。

因此，下列情况都是允许的：

    CELL（"format",B12）

    CELL（"format"）

    CELL（）是不允许的，因为Info_type是必选参数。

    如果一个参数后面跟有省略号（…），用户可以使用多个该种数据类型的参数。

某些函数可以使用多达30个参数，只要公式中的字符总数不超过1024个。

同样，单个字符串不能大于255个字符。

例如，下面是MAX函数的语法：

    MAX（number1,number2,...） 

    下列公式都是允许的：

    MAX（26）MAX（26,31）

    MAX（26,31,29） 

       名称后带有一组空括号的函数不需任何参数，但是使用时函数必须带括号，以使Microsoft Excel能识别该函数。

       7.10.3参数名

       许多参数名告诉了用户参数所具有的信息类型。

如果在参数名中出现了缩写num、ref或logical，则该参数必定分别是数字、引用或逻辑值。

例如，在函数ROUND（number,num_digits）中，第一个参数一定是一个数，第二个参数也是一个数。

类似地，参数名中的wordsnumber、reference、logical、text和array也表明该参数一定是该种类型。

Value意味着该参数可以是任何单值结果。

该值可以是数字，文字，逻辑值或错误值。

        7.10.4使用参数

        参数是一个函数用以生成新值或完成运算的信息。

参数总是位于函数名的右侧，并用括号括起来。

大多数参数其数据类型都是确定的。

用户所给出的参数要么是一种正确的类型，要么是能被Microsoft Excel自动转换为正确类型的。

参数可以是能产生所需数据类型的任意值。

例如，对参数求和的函数SUM，可以取1至30个参数。

        7.10.5参数类型

        函数的参数可以是下列类型之一：

数、文字、逻辑值、错误值、引用数组。

也可以使用引用和数组型参数给函数提供数、文字、逻辑值和错误值。

    数：

例如5.003，0，150.286和-30.05都是数。

不带小数的数称作整数。

整数例如5，1，150和-30。

数可以精确到十进制的15位。

    文字：

例如“a”，“word”，“w/punc或“”都是文字。

公式中用到的文字值必须包含在双引号内。

若是文字本身包含引号，那么文字中的每个双引号就要用两个双引号表示。

例如，要查找"inthe""good""olddays"的字符长度，就要输入LEN（“inthe”“good”

    "olddays"）文字值最长为255个字符，其中包括引号。

不包含字符的文字常量写成""，称为“空文字”。

    逻辑值：

逻辑值即TRUE和FALSE。

逻辑参数也可以是一个语句，例如B10＞20，它可判别出TRUE或FALSE。

    错误值：

错误值如#D1V/0!

#N/A,#NAME?

#NULL!

#NUM!

#REF!

和#VALUE!

。

    引用：

例如$A$10，A10，$A10，A$10，R1C1或R［10］C［-10］。

引用可以指单元格、区域或是多重选择，并且可以是相对的、绝对的或混合的，用户使用引用作为参数时（其值可以是数、文字、错误值或逻辑值），引用所指定的单元格的内容就用作参数。

返回类型为引用的函数显示引用的取值而不是引用本身。

要把多重选择用作单独的引用参数，则要把引用括在另一组括号内，例如：

SUM（（E5:

E8,E10:

E18）,AVERAGE（A1:

A5））

    数组：

数组允许用户自定义怎样将参数和函数输入单元格。

数组可被用作参数，而且公式也可以数组的形式输入，如在公式={SUM（B2：

D2*B3：

D3）}中。

    注意：

如果用作参数的文字未包含在引号内，Microsoft Excel假定其为一个名称并试图用它所引用的值去替代。

如果未加引号的文字不是一个名称，因而没有相应的值，Microsoft Excel则返回#NAME?

错误值。

       7.10.6在参数表中使用逗号

       我们必须用逗号分隔单个参数，但应注意不要额外地键入逗号。

如果用逗号预留了一个参数的位置而未输入该参数，Microsoft Excel将用默认值替代该参数，除非该参数是一个必选参数。

例如，若用户输入（,arg2,arg3）作为一个具有三个参数的工作表函数的参数，Microsoft Excel就会给arg1取一个适当值。

若是用户输入（arg1,,）,则为arg2和arg3取适当值。

    在工作表函数中，尤其是在计算之前统计参数个数的函数中，多余的逗号会影响参数的个数，并进而影响函数计算的方式。

例如，AVERAGE（1，2，3，4，5）返回3，而AVERAGE（，，1，2，3，4，5）却返回2.14。

对大多数参数来说，替代省略参数的值是0，FALSE或""（空文字），这要依照参数应取的数据类型而定。

对于省略的引用参数，默认值通常是活动单元格或选定。

    例如，在函数CELL中，如果用户省略了第二个参数并指定"format"为第一个参数，则当前所选的单元格就被用作默认引用，如果省略参数是其它值。

参数的描述将告诉用户。

    注意：

如果将引用作为一个参数，而且这一引用使用逗号做合并运算，则用圆括弧将引用括起来。

例如AREAS函数，具有一个参数，即引用。

如果用户试图输入公式AREAS（A1，C1），Microsoft Excel认为A1和C1是两个独立的参数并且显示“参数太多”的信息。

正确形式是AREAS（（A1，C1））。

      Excel统计信息重复出现次数

　　考试在学校中是经常的事，监考人员通常也是不固定的，考试次数一多，监考工作量的统计就足够统计人员费心了。

要知道，津贴是按照监考次数发放的，算错了，可对不起老师们的劳动哟！

本人以前采用的是“传统”的笨办法，点着监考安排表逐一计数，头晕脑胀、眼花缭乱不说，稍不留神还容易出错。

直到在Excel