轻松办公-OfficeExcel公式和名称基础(148)创建公式-数组

轻松办公-OfficeExcel公式和名称基础

（148）

创建公式-数组

一、数组与数组公式

在编程语言中，数组是指一系列数据的集合，这些数据包括数字、字母、中文字符等等，它也可以包含一个或多个子数组。数组有一维数组、二维数组、三维数组等，每个数组都有下标，从0开始至N-1.比如一个一维数组a{1,2，……，9}有9个数据，那么它的第一个数据下标就是a0,最后一个数据下标是a8,即：a0=1,a1=2,……,a8=9。

在EXCEL中，数组是项的集合，这些项包含一行，或者一列中的多个单元格，也可以是多行多列。EXCEL只支持最多二维数组。

EXCEL中的数组公式是指可以在数组的一项或多项上执行多个计算的公式。它也称为“CSE 公式”，是数组公式键盘输入键的首字母组成的，即Ctrl+Shift+Enter键，我们可以在工作簿中按这三个键输入。

数组公式可以返回多个结果，也可返回一个结果。例如，可以将数组公式放入单元格区域中，并使用数组公式来汇总列或行的值。也可以将数组公式放入单个单元格中，然后计算单个量。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。

图例：EXCEL中的数组：

二、创建单个单元格数组公式

例：在工作簿的单元格 A2：E12 中，键入相应数据。

在单元格 A14 中，键入下面的公式:

=SUM(B2:C12*D2:D12)

选中单元格，将光标移至公式栏的公式最后面，然后并按 Ctrl+Shift+Enter组合键，这时，Excel 会将数组（单元格区域 B2：B12 和D2: D12）中的值相乘，即B2*D2+B3*D3+……+B12*D12，然后使用再 SUM 函数将这些乘积相加。结果等于 ￥1089。

三、数组公式语法

EXCEL中的数组公式主要使用标准公式语法。它们都以等号开始，可以在数组公式中使用任何内置 Excel 函数。使用数组公式的主要不同之处在于，必须按 Ctrl+Shift+Enter 输入公式。执行此操作时，Excel 将用大括号将数组公式括起来（提示：按组合键得到的数组大括号跟你手动键入大括号是完全不同的，手动输入的大括号里面的公式EXCEL会将它转换为文本字符串显示，公式不会起任何作用。）。

在输入数组公式时，我们还需要注意的是：数组函数是一种简化形式。例如，前面使用的多单元格函数等效于：

=B2*D2

=B3*D3

……

=B12*D12

单元格 A14 中的单个单元格公式集中了所有这些乘法运算，另外还有将这些小计相加所需的算法：= B2*D2+ B3*D3+……+ B12*D12 等。

四、输入和更改数组公式原则

创建数组公式的基本原则：每当需要输入或编辑数组公式时都要按 Ctrl+Shift+Enter。该原则适用于单个单元格公式和多单元格公式。

在使用多单元格公式时，我们还需要遵循以下原则：

1、必须在输入公式之前选择用于保存结果的单元格区域。例如：如果要对下列数据进行汇总，先选中A14单元格，然后在单元格内输入数组公式并按数组公式组合键：

2、数组公式一但输入确认，是不能更改数组公式中单个单元格内容的。如果修改数组公式中单个单元格的内容，EXCEL将会提示错误内容：

3、可以移动或删除整个数组公式，但无法移动或删除其部分内容。如果要缩减数组公式，需先删除现有公式再重新开始。

4、不能向多单元格数组公式中插入空白单元格或删除其中的单元格，否则会提示错误。例如，欲向A列和B列中插入一列：

五、数组常量

数组常量是数组公式的组成部分。我们可以通过输入一系列项然后手动用大括号 ({ }) 将该系列项括起来创建数组常量，类似于：

={1,2,3,4}

在本文前面强调过在创建数组公式时需要按 Ctrl+Shift+Enter。因为数组常量是数组公式的组成部分，可以通过键入一对大括号手动将常量括起来。然后使用 Ctrl+Shift+Enter 输入整个公式。

上面使用的是逗号分隔（隔开）各个项，创建的是水平数组（一行）。如果使用分号分隔项，将创建垂直数组（一列）。

要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。

使用数组公式时，可以将数组常量用于 Excel 提供的所有内置函数中。下面我们在B6单元格中输入公式：

=SUM(B3:E5*{1,2,3})

当按数组组合键后，提示值错误。这是因为，单元格区域的列有4列（一行有四个数据常量），而后面乘一个3列（一行有三个数据常量）的数组，最后一个值等于乘以一个空值，所以就报错：

把数组改成{1,2,3，4}后就可以了：

如果把上面的例子写成普通的乘法公式，就很好理解了：

六、数组公式运用实例

1、在单元格区域中对字符计数

计算单元格区域中的字符数（包括空格）。

在数据工作表的单元格 A7 中输入下面的公式，然后按 Ctrl+Shift+Enter：

=SUM(LEN(A2:D5))

单元格 A7 中显示值 26。

LEN 函数返回该区域的每个单元格中的每个文本字符串的长度，如A2单元格中的1为1个字符，长度为1，C3单元格中的“ABC”有3个字符，长度为3，B2单元格为空字符串，长度为1。然后 SUM 函数将这些值相加，并在包含该公式的单元格 A7 中显示结果。

2、查找出区域内的3个最小值

在数据工作表上，选择单元格 J3 到 J5。这组单元格将保留数组公式返回的结果。

在编辑栏中输入公式=SMALL(H3:H20,{1;2;3})，然后按 Ctrl+Shift+Enter：

最小的三个值 25.00%、 30.00% 和 35.29%分别显示在单元格 J3 到 J5 中。

更多精彩内容将在以后的章节分享给朋友们，请添加好友至收藏，欢迎点赞并关注后期内容更新！