第1个回答 2022-06-29
对于希望精通Excel函数与公式的用户来说，数组运算和数组公式是必须跨越的门槛。通过本文的介绍，让用户能够对数组公式和数组运算有更深刻地理解，并能够利用数组公式来解决实际工作中的一些疑难问题。
一、理解数组
1. Excel中数组的相关定义
在Excel函数与公式应用中，数组是指按一行、一列或多行多列排列的一组数据元素的集合。数据元素可以是数值、文本、日期、逻辑值和错误值。
数组的维度是指数组的行列方向，一行多列的数组为横向数组，一列多行的数组为纵向数组。多行多列的数组则同时拥有纵向和横向两个维度。
数组的维数是指数组中不同维度的个数。只有一行或一列在单一方向上延伸的数组，成为一维数组；多行多列同时拥有两个维度的数组成为二维数组。
数组的尺寸是以数组各行列上的元素个数来表示的。一行N列的一位横向数组，其尺寸表示为1*N；一列N行的一维纵向数组，其尺寸表示为N*1；对于M行N列的二维数组，其各行或各列的元素个数必须相等，呈矩形排列，其尺寸表示为M*N。
2. Excel中数组的存在方式
（1）常量数组
在Excel函数与公式应用中，常量数组是指直接在公式中写入数组元素，并用大括号{}在首尾进行识别的文字串表达式。其不依赖单元格区域，可直接参与公式的计算。
顾名思义，常量数组的组成元素只可为常量元素，决不能是函数、公式或单元格引用。常量元素中不可以包含美元符号、逗号、圆括号和百分号。
一维纵向常量数组（通常称为“行数组”）的各元素用半角分号“;”间隔，如下式，表示尺寸为5行*1列的数值型常量数组：
={1;2;3;4;5}
一维横向量数组（通常称为“列数组”）的各元素用半角逗号“,”间隔，如下式，表示尺寸为1行*3列的文本型常量数组：
={“张三”,”李四”,”王五”}
文本型常量元素必须用半角双引号“””将首尾标识起来。
二维常量数组的每一行上的元素用半角逗号“,”间隔，每一列上的元素用半角分号“;”间隔。如下式，表示尺寸为4行*3列的二维混合数据类型的数组，包含数值、文本、日期、逻辑值和错误值。
={1,2,3:#N/A,5,TRUE;”田径”,”2008-8-8”,”股市”;#VALUE!,FALSE,12}
提示：如果用户在手工输入数组的过程中感觉非常繁琐，可以借助单元格引用来转换为常量数组。
例如当用户在单元格A1:A7中分别输入“A到G”的字符后，再在B1中输入：=A1:A7，并选中公式段中的A1:A7，同时按下键，Excel会自动将单元格引用转换为常量数组。
（2）区域数组
如果在公式或函数参数中引用工作表的摸个单元格区域，且其中函数参数不是单元格引用或区域类型（reference、ref或range），也不是向量（vector）时，Excel会自动将该区域引用转换成由区域中各单元格的值构成的同维数同尺寸的数组，可称之为区域数组。
区域数组的维度和尺寸与常量数组完全一致，而在公式运算中会自动将“区域引用”进行转换，这类区域数组也是用户在利用“公式求值”查看公式运算过程时常看到的。
（3）内存数组
内存数组是指某一公式通过计算，在内存中临时返回多个结果值构成的数组。而该公式的计算结果，不必存储到单元格区域中，便可作为一个整体直接嵌套入其他公式中继续参与计算。该公式本身则称之为内存数组公式。
内存数组与区域数组的主要区别在于，区域数组通过引用而非通过公式计算获得，但其不是通过公式计算在内存中临时获取的，而是作为常量直接输入的。
可以用一句话概括内存数组的特点，即内存数组生于内存，存于内存。
（4）命名数组
命名数组是指，使用命名公式（即名称）定义的一个常量数组、区域数组或内存数组。该名称可在公式中作为数组来调用。在数据有效性（有效性序列除外）和条件格式的自定义公式中，不接受常量数组，但可将其命名后，直接调用名称进行运算。
二、数组公式与数组运算
（1）认识数组公式
简单地说，数组公式是指区别与普通公式，并以按下组合键来完成编辑的特殊公式。作为标识，Excel会自动在编辑栏中给数组公式的首尾加上大括号“{}”。数组公式的实质是单元格公式的一种书写形式，用来显式地通知Excel计算引擎对其执行多项计算。
所谓的多项计算是指，对公式中有对应关系的数组元素同步执行相关计算，或在工作表的相应单元格区域站宏同时返回常量数组、区域数组、内存数组或命名数组中的多个元素。
但是，并非所有执行多项计算的公式，都必须以数组公式的输入方式来完成编辑。一些函数在其array数组类型或vector向量类型的参数中使用数组，并返回单一结果值时，Excel不需要获得通知就可以直接对其执行多项计算。例如，Excel
2010中SUMPRODUCT、LOOKUP、MMULT以及新增的MODE.MULT函数。
（2）多单元格联合数组公式
如果一个函数或公式返回多个结果值，并需要存在单元格区域中，那么额借助多单元格数组公式来实现。
例 1 ： 多单元格数组公式计算销售额
如图1所示，选择G3:G11单元格区域后，输入如下数组公式后，并按下结束编辑：{=E3:E11*F3:F11}（注：输入公式时不包括外层大括号）
此公式将各种商品的销售数量分别乘以各自的单价，获得一个内存数组{44;200;36;112;125;54;28;60;21}，将其写入指定的G3:G11单元格区域中显示出来（在本例中生成的内存数组与写入的单元格区域尺寸完全一致）。
本示例只是为了说明数组公式的用法，本应用其实相当于选择G3:G11单元格区域后，在G3单元格中使用相对引用输入如下普通公式，并按下结束编辑：
=E3*F3
多单元格数组公式在每个单元格中显示相同的公式，并按一个公式执行计算，可提高运算效率。
在某些时候，用户编写公式的运算结果无法生成内存数组，但后续的公式有需要对结果进行再处理，也可以考虑使用多单元格公式来协助处理。
例 2 ： 利用多单元格数组公式计算最小值
如图2所示，列举了3列随机数值，下面的公式将分别从各列中取得最小值，并写入指定的单元格中，可以输入多单元格数组公式如下。
例如在E2:G2单元格中输入多单元格联合数组公式如下：
{=MIN(INDEX($A$3:$C$10,,{1,2,3}))}
由于INDEX函数地2、3个参数都不支持数组元素来生成内存数组，因此该公式的结果只能放置于多单元格中才能显示。
当然，如果该示例需要生成内存数组，可以使用以下两个公式：
公式1：{=CHOOSE({1,2,3},MIN($A$3:$A$10),MIN($B$3:$B$10),MIN($C$3:$C$10))}
公式2：{=SUBTOTAL(5,OFFSET($A$3:$A$10,,{0,1,2}))}
注意：数组公式首尾的大括号{}是由组合键自动生成，千万不要试图手工输入，否则Excel只能识别其为文本字符，而无法被当成公式正确地运算。
（3）单个单元格数组公式
例 3 ：单个单元格数组公式
同样沿用例1的饮品销售数据，下面可以使用一个公式来完成对所有饮品总销售利润进行统计，如图3所示。
本例中G13单元格的数组公式如下：
{=SUM(E3:E11*F3:F11)*G1}
该公式先在内存中执行计算，将各商品的销量和单价分别相乘，然后再将数组中的所有元素用SUM函数汇总，得到总销售额，最后再乘以G1单元格的利润率，即可得出最终结果。
由于SUM函数的参数不能直接支持数组，所以在输入该公式时，必须以数组公式的形式输入，来通知Excel执行多重计算。这样就可以不借助任何辅助单元格，直接完成计算，并返回结果值。
本例中的公式还可用SUMPRODUCT函数来替代：
=SUMPRODUCT(E3:E11*F3:F11)*G1
SUMPRODUCT函数的所有参数都是array数组类型参数，直接支持多项计算，因此该公式不需要以数组公式的形式输入公式，也能够正常返回结果。
（4）数组公式的编辑
与Excel
2003一样，在Excel 2010中同样对多单元格数组公式有如下限制。
a. 不能单独改变公式区域某一部分单元格的内容；
b. 不能单独移动公式区域的某一部分单元格；
c. 不能单独删除公式区域的某一部分单元格；
d. 不能在公式区域插入新的单元格。
如果需要修改多单元格数组公式，操作步骤如下。
步骤 1 选择公共区域，按F2键进入编辑模式。
步骤 2 修改公式内容后，再次按下组合键结束编辑。
如果希望删除原有的多单元格数组公式，操作步骤如下。
步骤 1 选择任意一个多单元格数组公式单元格，按F2进入编辑状态。
步骤 2 删除该单元格公式内容后，再次按下组合键结束编辑。
另外，读者还可以先按下组合键，选择多单元数组公式后，再按下键进行删除。
��8����