Excel数据处理与分析：数组公式应用

数组公式是非常强大的公式，它可以代替公式中的辅助列直接在一个公式中执行多步计算，一次性处理多个操作。

（1）数组公式的形式

数组公式可以存在于一个单元格区域中，每个单元格中具有相同的数组公式，也可以像普通公式那样只存在于一个单元格中。

如图2.1.8-1所示，在D列中，D2到D5单元格中分别包含以下公式：

D2=B2*C2

D3=B3*C3

D4=B4*C4

D5=B5*C5

通过计算，D6单元格最后得出的合计金额为4505元。

除了上述方法，也可以同时在要计算金额的单元格区域中使用一个数组公式来代替这四个公式，选择F2：F5单元格区域，输入数组公式“=B2：B5*C2：C5”，按Ctrl+Shift+Enter组合键结束，Excel会自动为数组公式的最外侧添加一对大括号，通过计算，F6单元格最后得出的合计金额同样也是4505元，如图2.1.8-2所示。

图2.1.8-1　使用普通公式计算金额

图2.1.8-2　使用数组公式代替多个普通公式

在本例中，除了可以在多个单元格使用数组公式外，还可以在一个单元格中使用数组公式。例如，如果需要计算上图中所有产品的合计金额，在不使用数组公式的情况下，需要先分别计算出每件产品的金额，再对所有金额求和，分两步来计算；但如果使用数组公式，这两步操作就可以合并为一步进行，而且无须占用多个单元格，只在一个单元格中即可完成：

选择要输入数组公式的单元格如F7单元格，输入数组公式“=SUM（B2：B5*C2：C5）”，按Ctrl+Shift+Enter键结束，使Excel执行数组运算，即可一步得出计算结果为4505元，如图2.1.8-3所示。

图2.1.8-3　在一个单元格中输入数组公式

（2）数组的维数

数组的维数是指其在工作表的行和列中的分布。一维数组分为一维水平数组和一维垂直数组，位于一行或一列中。

一维水平数组

一维水平数组中的每个数组元素之间以逗号分隔，如{1，2，3，4，5}。要在工作表中输入一维水平数组，需要预先根据数组元素的个数，横向选择一个单元格区域，例如上面的数组包含五个元素，所以需要在一行中选择五个单元格的区域（如A1：E1），然后输入公式“={1，2，3，4，5}”，输入完成后按Ctrl+Shift+Enter组合键结束，即可将该数组输入到选中的单元格区域中，如图2.1.8-4所示。

如果要输入自动填充序列的水平数组，可以借用COLUMN函数，选择A2到E2单元格区域，输入公式“=COLUMN（A：E）”，输入完毕后按Ctrl+Enter组合键结束，同样可以得到该数组，如图2.1.8-5所示。其中，COLUMN函数用于返回单元格或单元格区域首列的列号，返回值为一个或一组数字。

图2.1.8-4　使用组合键输入一维水平数组

图2.1.8-5　使用COLUMN函数输入一维水平数组

如果数组元素是文本类型，那么必须在每个数组元素的两端添加英文半角的双引号。例如，要求在A3：E3单元格分别输入“生产部”“质检部”“销售部”“技术部”和“管理部”。(www.xing528.com)

选择A3：E3单元格区域，输入数组公式“={"生产部"，"质检部"，"销售部"，"技术部"，"管理部"}”，输入完毕后，按Ctrl+Shift+Enter组合键结束，即可将该数组输入到单元格中，如图2.1.8-6所示。

图2.1.8-6　输入文本类型的数组

一维垂直数组

一维垂直数组中的每个数组元素之间以分号分隔，如{1；2；3；4；5}。要在工作表中输入一维垂直数组，需要预先根据数组元素的个数，纵向选择一个单元格区域，例如上面的数组包含五个元素，所以需要在一列中选择五个单元格的区域（如A1：A5），然后输入公式“={1；2；3；4；5}”，输入完毕后按Ctrl+Shift+Enter组合键结束，即可将该数组输入到选中的单元格区域中，如图2.1.8-7所示。

如果要输入自动填充序列的垂直数组，可以借用ROW函数，选择B1到B5单元格区域，输入公式“=ROW（1：5）”，按Ctrl+Enter组合键结束，同样可以将该数组输入到选中的单元格区域中，如图2.1.8-8所示。其中，ROW函数用于返回单元格或单元格区域首行的行号，返回值为一个或一组数字。

图2.1.8-7　使用组合键输入一维垂直数组

图2.1.8-8　使用ROW函数输入一维垂直数组

二维数组

二维数组是由行和列组成的，水平方向的数组元素由逗号分隔，垂直方向的数组元素由分号分隔，如{1，2，3，4，5；6，7，8，9，10}。这个二维数组由两行五列组成，第一行包含1、2、3、4、5这五个数字；第二行包含6、7、8、9、10这五个数字。要在工作表中输入这样一个二维数组，首先要选择包含两行五列的单元格区域（如A1：E2），输入公式“={1，2，3，4，5；6，7，8，9，10}”，按Ctrl+Shift+Enter组合键结束，即可将数组输入到选中的单元格区域中，如图2.1.8-9所示。

如果用于输入数组的单元格区域大于数组元素的个数，那么多出来的部分将显示为错误值“#N/A”，如图2.1.8-10所示。

图2.1.8-9　使用组合键输入二维数组

图2.1.8-10　当所选区域大于数组元素个数时

（3）输入数组公式

前面两小节介绍了怎样输入数组公式，最重要的一点是必须使用Ctrl+Shift+Enter组合键来输入数组。

数组公式在输入完成后要按Ctrl+Shift+Enter组合键确认输入。按下该组合键后，可以在编辑栏中看到，公式的两侧包含了一对大括号。这对大括号是Excel自动添加的，由此可以区分出哪些公式是数组公式，如果用户自己手动添加了这对大括号则公式会出错。

（4）修改数组公式

我们无法单独对数组公式所涉及的单元格区域中的某一个单元格进行编辑。如果选择数组公式所在的区域的某个单元格，并尝试修改操作，则会弹出对话框提示“无法更改部分数组”，如图2.1.8-11所示。

图2.1.8-11　无法更改部分数组

如果用户希望修改这些数组公式，需要先选择数组公式所在的整个单元格区域，再在编辑栏或按F2功能键进入编辑状态后进行修改。修改完成后，按Ctrl+Shift+Enter组合键确认修改。

如果用户希望删除占有多个单元格的数组公式，同样需要先选择数组公式所在的整个区域，然后按Delete键删除。