数组公式指南和示例

数组公式指南和示例

数组公式是一种公式,可以针对数组中的一个或多个项执行多个计算。 可以将数组视为值的行或列,或值行和列的组合。 数组公式可以返回多个结果或单个结果。

从 Microsoft 365 的 2018 年 9 月更新开始,任何可返回多个结果的公式都将自动向下或溢出到相邻单元格中。 此行为更改还伴随几个新的 动态数组函数。 无论动态数组公式是使用现有函数还是动态数组函数,都只需输入到单个单元格中,然后按 Enter 进行确认。 以前,旧数组公式需要先选择整个输出区域,然后使用 Ctrl+Shift+Enter 确认公式。 它们通常称为 CSE 公式。

可以使用数组公式执行复杂任务,例如:

  • 快速创建示例数据集。

  • 计算单元格区域中包含的字符数。

  • 仅对满足特定条件的数字求和,例如某个范围中的最低值,或介于上限和下限之间的数字。

  • 对一系列值中每个第 N 个值求和。

以下示例展示如何创建多单元格和单单元格数组公式。 在可能的情况下,我们包含了一些动态数组函数的示例,以及作为动态数组和旧数组输入的现有数组公式。

下载我们的示例

下载包含本文中所有数组公式示例的示例工作簿

本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。 第一组操作是使用多单元格公式计算一组小计。 第二组操作是使用单个单元格公式计算总计。

  • 多单元格数组公式

    单元格 H10 中的多单元格数组函数 =F10:F19*G10:G19,用于计算单价售出的汽车数

  • 此处,我们通过在单元格 H10 中输入 =F10:F19*G10:G19 来计算每位销售人员的双门和三门汽车的总销售额。

    Enter时,会看到结果溢出到单元格 H10:H19。 请注意,选择溢出范围内的任何单元格时,溢出区域会突出显示,并带边框。 您可能还注意到单元格 H10:H19 中的公式灰显。 它们只是供参考,因此,如果要调整公式,则需要选择主公式的单元格 H10。

  • 单单元格数组公式

    使用 =SUM 函数计算总计的单单元格数组公式 (F10:F19*G10:G19)

    在示例工作簿的单元格 H20 中,键入或复制并粘贴=SUM (F10:F19*G10:G19) ,然后按Enter。

    在这种情况下,Excel将数组中的 (F10 到 G19) 相乘,然后使用 SUM 函数将总计相加。 结果等于 $1,590,000 的总销售额。

    本示例演示了此类公式的强大功能。 例如,假定您有 1,000 行数据。 您可以通过在单个单元格中创建数组公式来对部分或全部数据求和,而不是将公式向下拖动以包括所有 1,000 行来求和。 另请注意,单元格 H20 中的单单元格公式与单元格 H10 (H19 单元格中的公式完全) 。 这是使用数组公式的另一个优点  - 灵活性。 可以更改列 H 中的其他公式,而不会影响 H20 中的公式。 拥有这样的独立总计也是一种不错的做法,因为它有助于验证结果的准确性。

  • 动态数组公式还提供以下优势:

    • 一致性    如果从 H10 向下单击任一单元格,则会看到相同的公式。 这种一致性有助于确保更高的准确性。

    • 安全    不能覆盖多单元格数组公式的组件。 例如,单击单元格 H11,然后按 Delete。 Excel不会更改数组的输出。 若要更改它,需要选择数组中左上方的单元格或单元格 H10。

    • 较小的文件大小    通常可以使用单个数组公式而不是多个中间公式。 例如,汽车销售示例使用一个数组公式来计算列 E 中的结果。如果使用了 =F10*G10、F11*G11、F12*G12 等标准公式,则应该使用 11 个不同的公式来计算相同的结果。 这不是一个大问题,但如果你总共有数千行,又如何呢? 然后,它就可以产生很大差异。

    • 提高效率    数组函数是构建复杂公式的一种有效方法。 数组公式 =SUM (F10:F19*G10:G19) 与以下公式相同:=SUM (F10*G10,F11*G11,F12*G12 ,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19) 。

    • 溢出    动态数组公式会自动溢出到输出范围。 如果源数据位于 Excel表中,则动态数组公式会在添加或删除数据时自动调整大小。

    • #SPILL!error    动态数组引入了 #SPILL! 错误,指示由于某种原因阻止了预期的溢出范围。 解决阻止时,公式将自动溢出。

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

={1,2,3,4,5} 或 ={"January","February","March"}

如果使用逗号分隔各个项,将创建水平数组(一行)。 如果使用分号分隔项,将创建垂直数组(一列)。 若要创建二维数组,请用逗号分隔每行中的项,然后用分号分隔每行。

下面将为你提供创建水平、垂直和二维常量的练习。 我们将展示使用 SEQUENCE 函数自动生成数组常量以及手动输入的数组常量的示例。

  • 创建水平常量

    使用上述示例中所用工作簿,或创建新的工作簿。 选择任何空单元格,然后输入 =SEQUENCE (1,5) 。 SEQUENCE 函数生成一个 1 行 5 列数组,与 ={1,2,3,4,5} 相同。 将显示以下结果:

    使用 =SEQUENCE (1,5) 或 ={1,2,3,4,5} 创建水平数组常量

  • 创建垂直常量

    选择其下具有空间的任何空白单元格,然后输入 =SEQUENCE (5) ={1;2;3;4;5}。 将显示以下结果:

    使用 =SEQUENCE (5) 或 ={1;2;3;4;5} 创建垂直数组常量

  • 创建二维常量

    选择右侧和下方具有空间的任何空白单元格,然后输入 =SEQUENCE (3,4) 。 将得到以下结果:

    使用 =SEQUENCE 创建 3 行 4 列数组常量 (3,4)

    还可以输入:或 ={1,2,3,4;5,6,7,8;9,10,11,12},但需要注意分号与逗号的位置。

    可以看到,与手动输入数组常量值相比,SEQUENCE 选项具有显著优势。 首先,它可节省时间,但也有助于减少手动输入错误。 它更易于阅读,尤其是因为分号难以与逗号分隔符区分。

以下示例使用数组常量作为较大公式的一部分。 在示例工作簿中,转到公式 工作表中的 常量,或创建新工作表。

在单元格 D9 中, 我们输入了 =SEQUENCE (1,5,3,1) ,但您也可以在单元格 A9:H9 中输入 3、4、5、6 和 7。 对于该特定数字选择没有任何特殊之处,我们仅选择 1-5 作为区分。

在单元格 E11 中,输入=SUM (D9:H9*SEQUENCE (1,5) ) ,或=SUM (D9:H9*{1,2,3,4,5}) 。 公式返回 85。

在公式中使用数组常量。 本示例使用 =SUM (D9:H (*SEQUENCE (1,5) )

SEQUENCE 函数生成数组常量 {1,2,3,4,5} 的等效项。 由于Excel对括在括号中的表达式执行运算,因此,接下来的两个生效的元素是 D9:H9 中的单元格值和乘法运算符 (*) 。 此时,公式将存储数组中的值与常量中对应的值相乘。 它等价于:

=SUM (D9*1,E9*2,F9*3,G9*4,H9*5) , 或 =SUM (3*1,4*2,5*3,6*4,7*5)

最后,SUM 函数将值相加,并返回 85。

若要避免使用存储的数组,并且将操作完全保存在内存中,可以使用另一个数组常量替换它:

=SUM (SEQUENCE (1,5,3,1) *SEQUENCE (1,5) ) , 或 =SUM ({3,4,5,6,7}*{1,2,3,4,5})

可以在数组常量中使用的元素

  • 数组常量可以包含数字、文本、逻辑 (值(例如 TRUE 和 FALSE) )以及错误值(如 #N/A)。 可以使用整数、小数和科学格式的数字。 如果包含文本,则需要在文本周围加上引号 ("text") 。

  • 数组常量不能包含其他数组、公式或函数。 换言之,它们只能包含以逗号或分号分隔的文本或数字。 当您输入如下所示的公式时,Excel 将显示警告消息:{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。 另外,数值不能包含百分号、货币符号、逗号或圆括号。

使用数组常量的一种最佳方法就是命名它们。 命名的数组常量更易于使用,并且对于其他人来说,它们可以降低数组公式的复杂性。 若要命名数组常量并在公式中使用它们,请执行以下操作:

转到"公式">定义的名称>"定义名称"。 在"名称 "框中 ,键入"Quarter1"。 在“引用位置”框中,输入下面的常量(记住要手动键入大括号):

={"一月","二月","三月"}

对话框现在应如下所示:

从"公式"中添加命名数组常量>"名称">名称管理器>"新建"

单击 "确定",然后选择包含三个空白单元格的任何行,然后输入 =Quarter1

将显示以下结果:

在公式(如 =Quarter1)中使用命名数组常量,其中 Quarter1 已定义为 ={"January","February","March"}

如果希望结果垂直溢出而不是水平溢出,可以使用=TRANSPOSE (Quarter1) 。

如果要显示一个包含 12 个月的列表,就像构建财务报表时可能使用的列表一样,可以使用 SEQUENCE 函数将当前年份作为基准。 此函数的简洁性是,即使只显示月份,它后面还有一个可用于其他计算的有效日期。 在示例工作簿的"命名数组常量"和"快速示例数据集"工作表上可以找到这些示例。

=TEXT (DATE (YEAR (TODAY () ) ,SEQUENCE (1,12) ,1) ,"mmm")

结合使用 TEXT、DATE、YEAR、TODAY 和 SEQUENCE 函数生成 12 个月的动态列表

这会使用 DATE 函数基于当前年份创建日期,SEQUENCE 为 1 月到 12 月创建从 1 到 12 的数组常量,然后 TEXT 函数将显示格式转换为"mmm" (Jan、Feb、Mar 等 ) 。 如果要显示完整的月份名称(如 January),请使用"mmmm"。

使用命名常量作为数组公式时,请记得输入等号,如 =Quarter1,而不只是 Quarter1。 如果没有输入等号,Excel 将数组解释为文本字符串,并且公式不会按预期工作。 最后,请记住,可以使用函数、文本和数字的组合。 这取决于希望获得多少创意。

下面的示例演示可以将数组常量用于数组公式的几种方式。 一些示例使用 TRANSPOSE 函数 将行转换为列,反之亦然。

  • 数组中每个项的多个项

    输入 =SEQUENCE (1,12) *2,或 ={1,2,3,4;5,6,7,8;9,10,11,12}*2

    您也可以使用 (/) 相除,使用 (+) 相加,使用 (-) 。

  • 对数组中的各项求平方

    输入 =SEQUENCE (1,12) ^2,或 ={1,2,3,4;5,6,7,8;9,10,11,12}^2

  • 查找数组中平方项的平方根

    输入=SQRT (SEQUENCE (1,12) ^2) ,或=SQRT ({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • 转置一维行

    输入 =TRANSPOSE (SEQUENCE (1,5) ) ,或 =TRANSPOSE ({1,2,3,4,5})

    即使输入的是水平数组常量,TRANSPOSE 函数也会将该数组常量转换为列。

  • 转置一维列

    输入 =TRANSPOSE (SEQUENCE (5,1) ) ,或 =TRANSPOSE ({1;2;3;4;5})

    即使输入的是垂直数组常量,TRANSPOSE 函数也会将该常量转换为行。

  • 转置二维常量

    输入 =TRANSPOSE (SEQUENCE (3,4) ) ,或 =TRANSPOSE ({1,2,3,4;5,6,7,8;9,10,11,12})

    TRANSPOSE 函数将各行转换为一系列的列。

本节提供基本数组公式的示例。

  • 从现有值创建数组

    以下示例说明如何使用数组公式从现有数组创建新数组。

    输入 =SEQUENCE (3,6,10,10,10) ,或 ={10,20,30,40,50,60;70 ,80,90,100,110,120;130,140,150,160,170,180}

    在键入 10 之前 (键入 { (左大括号) ,键入 180 后键入 } (右大括号) ,因为要创建数字数组。

    接下来,在空白单元格中输入 =D9#=D9:I11。 将显示一个 3 x 6 的单元格数组,其值与 D9:D11 中的值相同。 # 符号称为溢出范围运算符 ,Excel引用整个数组范围的方式,而无需键入它。

    使用溢出范围运算符 (#) 引用现有数组

  • 从现有值创建数组常量

    可以使用溢出数组公式的结果,并将其转换为其组成部分。 选择单元格 D9,然后按 F2 切换到编辑模式。 接下来,按F9将单元格引用转换为值,Excel转换为数组常量。 按 Enter时,公式 =D9#现在应为 ={10,20,30;40,50,60;70,80,90}。

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

    以下示例演示如何计算单元格区域的字符数。 这包括空格。

    计算一个范围中的字符总数,以及用于处理文本字符串的其他数组

    =SUM (LEN (C9:C13) )

    在这种情况下 ,LEN 函数 返回区域每个单元格中每个文本字符串的长度。 然后 SUM 函数将这些值相加并显示结果 (66) 。 如果要获取平均字符数,可以使用:

    =AVERAGE (LEN (C9:C13) )

  • 区域 C9:C13 中最长单元格的内容

    =INDEX (C9:C13,MATCH (MAX (LEN (C9:C13) ) ,LEN (C9:C13) ,0) ,1)

    本公式仅在数据区域包含单列单元格时适用。

    让我们从内部元素开始,由内而外深入了解此公式。 LEN 函数返回单元格区域 D2:D6 中每个项的长度。 MAX 函数计算这些项中的最大值,该值对应于单元格 D3 中最长的文本字符串。

    下面的计算稍微有点复杂。 MATCH 函数计算包含 (字符串) 单元格相对位置的偏移量。 为此,需要三个参数:分别是查阅值、查阅数组和匹配类型。 MATCH 函数在查阅数组中搜索指定的查阅值。 在这种情况下,查阅值为最长的文本字符串:

    MAX (LEN (C9:C13)

    并且该字符串位于此数组中:

    LEN (C9:C13)

    在这种情况下,匹配类型参数为 0。 匹配类型可以是 1、0 或 -1 值。

    • 1 - 返回小于或等于查找 val 的属性值

    • 0 - 返回完全等于查找值的第一个值

    • -1 - 返回大于或等于指定查找值的最小值

    • 如果未指定匹配类型,Excel 会采用值 1。

    最后 ,INDEX 函数 采用这些参数:数组,以及该数组中的行号和列号。 单元格区域 C9:C13 提供数组,MATCH 函数提供单元格地址,最后一个参数 (1) 指定该值来自数组的第一列。

    如果要获取最小文本字符串的内容,请将上述示例中的 MAX 替换为MIN。

  • 查找出区域内的 n 个最小值

    此示例演示如何查找单元格区域中的三个最小值,其中单元格 B9:B18has 中的示例数据数组的创建方式为:=INT (RANDARRAY (10,1) *100) 。 请注意,RANDARRAY 是一个易失性函数,因此每次计算时,你Excel随机数。

    Excel数组公式来查找第 N 个最小值:=SMALL (B9#,SEQUENCE (D9) )

    输入 =SMALL (B9#,SEQUENCE (D9) ,=SMALL (B9:B18,{1;2;3})

    此公式使用数组常量计算 SMALL 函数三次,并返回单元格 B9:B18 中包含的数组中最小的 3 个成员,其中 3 是单元格 D9 中的变量值。 若要查找更多值,可以增大 SEQUENCE 函数中的值,或向常量添加更多参数。 还可以对此公式使用其他函数,例如 SUMAVERAGE。 例如:

    =SUM (SMALL (B9#,SEQUENCE (D9) )

    =AVERAGE (SMALL (B9#,SEQUENCE (D9) )

  • 查找出区域中的 n 个最大值

    若要查找范围中的最大值,可以将 SMALL 函数替换为 LARGE 函数。 此外,下面的示例使用 ROWINDIRECT 函数。

    输入 =LARGE (B9#,ROW (INDIRECT ("1:3") ) ) , 或 =LARGE (B9:B18,ROW (INDIRECT ("1:3") ) )

    现在,了解一点 ROW 和 INDIRECT 函数可能会有所帮助。 可以使用 ROW 函数创建连续的整数数组。 例如,选择一个空值并输入:

    =ROW(1:10)

    此公式创建由 10 个连续整数组成的一列。 为了查看可能的问题,请在包含数组公式的区域上面插入一行(即第 1 行上)。 Excel调整行引用,公式现在生成从 2 到 11 的整数。 要修正该问题,可以向该公式添加 INDIRECT 函数:

    =ROW(INDIRECT("1:10"))

    INDIRECT 函数使用文本字符串作为参数 (正因如此,1:10 区域被引号括) 。 当插入行或移动数组公式时,Excel 不会调整文本值。 因此,此 ROW 函数总是生成所需的整数数组。 你可以轻松地使用 SEQUENCE:

    =SEQUENCE (10)

    让我们检查前面使用的公式 -=LARGE (B9#,ROW (INDIRECT ("1:3") ) ) - 从内括号开始向外工作:INDIRECT 函数返回一组文本值,在这种情况下,值 1 到 3。 ROW 函数反过来生成一个三单元格列数组。 LARGE 函数使用单元格区域 B9:B18 中的值,对 ROW 函数返回的每个引用计算三次。 如果要查找更多值,请向 INDIRECT 函数添加更大的单元格区域。 最后,与 SMALL 示例一样,可以将此公式与其他函数(如 SUM 和 AVERAGE)一同使用。

  • 对包含错误值的区域求和

    尝试对包含错误值的范围求和时,Excel SUM 函数不起作用,例如#VALUE! 或 #N/A。 此示例演示如何对包含错误的名为"数据"的值求和:

    使用数组处理错误。 例如,=SUM (IF (ISERROR (Data) ,"",Data) 将求名为 Data 的范围的总和,即使它包含错误,#VALUE! 或 #NA!。

  • =SUM(IF(ISERROR(数据),"",数据))

    该公式创建一个新数组,包含除错误值以外的原始值。 从内层函数开始向外运算,ISERROR 函数在单元格区域 (数据) 中搜索错误。 IF 函数在指定的条件计算结果为 TRUE 时返回指定值,在计算结果为 FALSE 时返回另一个值。 在此处,它为所有错误值返回空字符串 (""),因为它们的计算结果为 TRUE,并且返回该区域(数据)中的其他值(因为这些值计算结果为 FALSE,表示它们不包含错误值)。 接着 SUM 函数计算筛选出的数组的总和。

  • 计算区域中错误值个数

    此示例与前面的公式类似,但它返回名为 Data 的范围中的错误值数,而不是将其筛选掉:

    =SUM(IF(ISERROR(数据),1,0))

    该公式创建一个数组,它为包含错误的单元格包含值 1,为不包含错误的单元格包含值 0。 可以简化该公式,并达到相同的结果,方法是移除 IF 函数的第三个参数,如下所示:

    =SUM(IF(ISERROR(数据),1))

    如果未指定该参数,IF 函数在单元格不包含错误值时返回 FALSE。 可以进一步简化该公式:

    =SUM(IF(ISERROR(数据)*1))

    此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。

可能需要根据条件对值求和。

可以使用数组根据特定条件进行计算。 =SUM (IF (Sales>0,Sales) ) 将求和名为 Sales 的范围中大于 0 的所有值。

例如,此数组公式仅对名为 Sales 的单元格 E9:E24 中的正整数进行总和,如上例所示:

=SUM(IF(Sales>0,Sales))

IF 函数创建正值和 false 值的数组。 SUM 函数实际上将忽略 false 值,因为 0+0=0。 在此公式中使用的单元格区域可以由任意数量的行和列组成。

还可以对满足多个条件的值求和。 例如,此数组公式计算大于 0 且小于 2500 的值:

=SUM ( (Sales>0) * (Sales<2500) * (Sales) )

请注意,如果区域中包含一个或多个非数字单元格,此公式将返回错误。

还可以创建使用 OR 条件的数组公式。 例如,可以求和大于0 或小于 2500 的值:

=SUM (IF ( (Sales>0) + (Sales<2500) ,Sales) )

不能在数组公式中直接使用 AND 和 OR 函数,因为这些函数返回单一结果,TRUE 或 FALSE,而数组函数需要结果数组。 可以通过使用上一公式中显示的逻辑来解决这一问题。 换言之,可以执行数学运算,例如,对满足 OR 或 AND 条件的值执行加法或乘法运算。

本示例演示当您需要对区域中的值求平均值时,如何从该区域中移除零。 下面的公式使用名为“销售量”的数据区域:

=AVERAGE(IF(Sales<>0,Sales))

IF 函数创建不等于 0 的值数组,然后将这些值传递给 AVERAGE 函数。

此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。 如果这两个区域中的内容完全相同,此公式将返回 0。 若要使用此公式,单元格区域的大小和维度必须相同。 例如,如果 MyData 是 3 行 5 列的范围,则 YourData 也必须是 3 行 5 列:

=SUM(IF(我的数据=你的数据,0,1))

此公式创建与正比较的区域大小相同的新数组。 IF 函数使用值 0 和值 1 填充数组(0 表示单元格不匹配,1 表示单元格匹配)。 然后 SUM 函数返回该数组中的值的和。

可以如下所示简化该公式:

=SUM (1* (MyData<>YourData) )

与计算区域中的错误值的公式相似,此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。

此数组公式返回名为“数据”的单列区域中的最大值所在的行号:

=MIN(IF(数据=MAX(数据),ROW(数据),""))

IF 函数创建与名为“数据”的区域对应的新数组。 如果对应的单元格包含区域中的最大值,则此数组包含该行号。 否则,此数组包含空字符串 ("")。 MIN 函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。 如果名为“数据”的区域包含完全相同的最大值,则该公式返回第一个值的行号。

如果要返回最大值的实际单元格地址,请使用下面的公式:

=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),"")),COLUMN(数据))

在示例工作簿的"数据集之间的差异"工作表上, 可找到类似的 示例。

本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。 第一组操作是使用多单元格公式计算一组小计。 第二组操作是使用单个单元格公式计算总计。

  • 多单元格数组公式

复制下面的整个表格,并将其粘贴到空白工作表的单元格 A1 中。

销售人员

汽车 类型

销售 数量

销售额

刘鹏

四门轿车

5

33000

双门轿车

4

37000

尹歌

四门轿车

6

24000

双门轿车

8

21000

林彩瑜

四门轿车

3

29000

双门轿车

1

31000

潘杰

四门轿车

9

24000

双门轿车

5

37000

施德福

四门轿车

6

33000

双门轿车

8

31000

公式(总计)

总计

'=SUM(C2:C11*D2:D11)

=SUM(C2:C11*D2:D11)

  1. 若要查看每个销售人员的双门汽车和三门汽车的总销售额,请选择单元格 E2:E11,输入公式=C2:C11*D2:D11,然后按Ctrl+Shift+Enter。

  2. 若要查看所有销售额的总计,请选择单元格 F11,输入公式 =SUM (C2:C11*D2:D11) ,然后按Ctrl+Shift+Enter。

当你按Ctrl+Shift+Enter时,Excel用大括号 ({ }) 将公式括在选定区域的每个单元格中。 因为执行速度很快,所以你在 E 列中看到的是每位销售人员每种轿车类型的总销售额。 如果你选择 E2,然后选择 E3、E4 等,你将看到相同的公式 {=C2:C11*D2:D11}。 

E 列总计是由数组公式计算的

  • 创建单个单元格数组公式

在工作簿的 D13 单元格中,键入以下公式,然后按Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

在这种情况下,Excel单元格区域 C2 到 D11 (数组中的值相乘) 然后使用SUM函数将总计相加。 结果等于 $1,590,000 的总销售额。 本示例演示了此类公式的强大功能。 例如,假定您有 1,000 行数据。 您可以通过在单个单元格中创建数组公式来对部分或全部数据求和,而不是将公式向下拖动以包括所有 1,000 行来求和。

另请注意,单元格 D13 中的单单元格公式完全独立于单元格 E2 (E11 单元格中的公式中的多单元格) 。 这是使用数组公式的另一个优点  - 灵活性。 您可以更改列 E 中的公式或删除该列,而不会影响 D13 中的公式。

数组公式还具有以下优点:

  • 一致性    如果单击 E2 下的任意单元格,您将看到相同的公式。 这种一致性有助于确保更高的准确性。

  • 安全    您不能覆盖多单元格数组公式的组件。 例如,单击单元格 E3,然后按Delete。 您必须选择整个单元格区域(E2 到 E11),然后更改整个数组的公式,否则只能让数组保留原样。 作为一项额外的安全措施,必须按 Ctrl+Shift+Enter 确认对公式的任何更改。

  • 较小的文件大小    通常可以使用单个数组公式而不是多个中间公式。 例如,工作簿使用一个数组公式来计算 E 列中的结果。如果使用标准公式 (例如 =C2*D2、C3*D3、C4*D4...) ,则应该使用 11 个不同的公式来计算相同的结果。

通常,数组公式使用标准公式语法。 它们都以等号开始,可以在数组公式中使用大部分内置 Excel 函数。 主要区别是,使用数组公式时,按 Ctrl+Shift+Enter 输入公式。 执行此操作时,Excel 将用大括号将数组公式括起来  - 如果您手动键入大括号,公式将转换为文本字符串,并且不起作用。

数组函数是构建复杂公式的一种有效方法。 数组公式 =SUM(C2:C11*D2:D11)=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11) 相同。

重要: 每当需要输入数组公式时,按 Ctrl+Shift+Enter。 这适用于单单元格公式和多单元格公式。

使用多单元格公式时,还需记住以下原则:

  • 必须在输入公式之前选择用于保存结果的单元格区域。 如果选定了 E2 到 E11 单元格,在创建了多单元格数组公式后,执行此操作。

  • 不能更改数组公式中单个单元格的内容。 要试试是否真的如此,可以选择工作簿中的单元格 E3 再按 Delete。 Excel 将显示一个消息,告知您无法更改数组中的一部分。

  • 可以移动或删除整个数组公式,但无法移动或删除其部分内容。 换言之,要缩减数组公式,需先删除现有公式再重新开始。

  • 若要删除数组公式,请选择整个公式区域 (例如E2:E11) ,然后按Delete。

  • 不能将空白单元格插入到多单元格数组公式中或删除单元格。

有时,可能需要扩展数组公式。 选择现有数组区域的第一个单元格,然后继续,直到选择了要扩展公式的整个区域。 按 F2 编辑公式,然后在调整公式区域后按 Ctrl+Shift+Enter 确认公式。 键是选择整个区域,从数组中左上方的单元格开始。 左上方的单元格是进行编辑的单元格。

数组公式很出色,但它们也有一些缺点:

  • 有时你可能忘记按Ctrl+Shift+Enter。 甚至最有经验的 Excel 用户也会发生这种情况。 请记住每当输入或编辑数组公式时都要按此组合键。

  • 你的工作簿的其他用户可能不理解你的公式。 在实践中,工作表中通常不解释数组公式。 因此,如果其他人需要修改您的工作簿,您应该避免使用数组公式,或者确保这些用户了解任何数组公式并了解如何根据需要更改它们。

  • 大型数组公式可能会降低计算速度,具体取决于计算机的处理速度和内存。

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

={1,2,3,4,5}

现在,你知道创建数组公式时需要按Ctrl+Shift+Enter。 因为数组常量是数组公式的组成部分,可以通过手动输入一对大括号将常量括起来。 然后使用 Ctrl+Shift+Enter 输入整个公式。

如果使用逗号分隔各个项,将创建水平数组(一行)。 如果使用分号分隔项,将创建垂直数组(一列)。 若要创建二维数组,应在每行中使用逗号分隔项,并使用分号分隔每行。

下面是一行中的数组:{1,2,3,4}。 下面是单列中的数组:{1;2;3;4}。 还有一个两行四列的数组:{1,2,3,4;5,6,7,8}。 在两行数组中,第一行是 1、2、3 和 4,第二行是 5、6、7 和 8。 单个分号在 4 和 5 之间分隔两行。

使用数组公式时,可以将数组常量用于 Excel 提供的大部分内置函数中。 下面几节将解释如何创建各种类型的常量以及如何将这些常量用于 Excel 中的函数。

下面将为你提供创建水平、垂直和二维常量的练习。

创建水平常量

  1. 在空白工作表中,选择单元格 A1 到 E1。

  2. 在编辑栏中输入以下公式,然后按Ctrl+Shift+Enter:

    ={1,2,3,4,5}

    在这种情况下,你应该在{ } ({ }) 键入左大括号Excel右大括号,Excel将添加第二组。

    将显示以下结果。

    公式中的水平数组常量

创建垂直常量

  1. 在工作簿中,选择一列中的五个单元格。

  2. 在编辑栏中输入以下公式,然后按Ctrl+Shift+Enter:

    ={1;2;3;4;5}

    将显示以下结果。

    数组公式中的垂直数组常量

创建二维常量

  1. 在工作簿中,选择一个宽四列高三行的单元格块。

  2. 在编辑栏中输入以下公式,然后按Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    将得到以下结果:

    数组公式中的二维数组常量

在公式中使用常量

下面是一个使用常量的简单示例:

  1. 在示例工作簿中创建一个新工作表。

  2. 在单元格 A1 中键入 3,然后在 B1 中键入 4,在 C1 中键入 5,在 D1 中键入 6,并在 E1 中键入 7

  3. 在单元格 A3 中,键入以下公式,然后按Ctrl+Shift+Enter:

    =SUM(A1:E1*{1,2,3,4,5})

    请注意,Excel 用另一对大括号将常量括起来,这是因为您是以数组公式的形式输入该常量。

    包含数组常量的数组公式

    单元格 A3 中显示值 85

下节将讨论此公式的计算方法。

刚才使用的公式包含若干部分。

包含数组常量的数组公式的语法

1. 函数

2. 存储数组

3. 运算符

4. 数组常量

括号内的最后一个元素是数组常量:{1,2,3,4,5}。 请注意,Excel 不会用大括号将数组常量括起来,您必须自己添加大括号。 另请记住,向数组公式添加常量后,按 Ctrl+Shift+Enter 输入公式。

因为 Excel 首先对括号括起来的表达式执行运算,接下来参与运算的两个元素是存储在工作簿 (A1:E1) 中的值以及运算符。 此时,公式将存储数组中的值与常量中对应的值相乘。 它等价于:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最后,SUM 函数将这些值相加,总和 85 显示在单元格 A3 中:

要避免使用存储数组并让运算完全位于内存中,可用另一个数组常量来替换存储数组:

=SUM({3,4,5,6,7}*{1,2,3,4,5})

若要尝试此操作,请复制函数,选择工作簿中的空白单元格,将公式粘贴到编辑栏中,然后按Ctrl+Shift+Enter。 将得到与上述练习中使用以下数组公式相同的结果:

=SUM(A1:E1*{1,2,3,4,5})

数组常量可以包含数字、文本、逻辑值(例如 TRUE 和 FALSE)和错误值(例如 #N/A)。 可以使用整数、小数和科学计数格式表示的数字。 若使用了文本,您需要用引号 (") 将文本括起来。

数组常量不能包含其他数组、公式或函数。 换言之,它们只能包含以逗号或分号分隔的文本或数字。 当您输入如下所示的公式时,Excel 将显示警告消息:{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。 另外,数值不能包含百分号、货币符号、逗号或圆括号。

使用数组常量的最佳方法之一是命名它们。 命名的数组常量更易于使用,并且对于其他人来说,它们可以降低数组公式的复杂性。 若要命名数组常量并在公式中使用它们,请执行以下操作:

  1. “公式”选项卡上的“定义的名称”组中,单击“定义名称”
    将显示 "定义名称 "对话框。

  2. 在“名称”框中,键入第 1 季度

  3. “引用位置”框中,输入下面的常量(记住要手动键入大括号):

    ={"一月","二月","三月"}

    对话框中的内容现在类似于以下内容:

    编辑带公式的“名称”对话框

  4. 单击“确定”,然后选择一行中的三个空单元格。

  5. 键入以下公式,然后按Ctrl+Shift+Enter。

    =第 1 季度

    将显示以下结果。

    作为公式输入的命名数组

将命名常量用作数组公式时,切记要输入等号。 如果没有输入等号,Excel 将数组解释为文本字符串,并且公式不会按预期工作。 最后,请记住可以使用文本和数字的组合。

当数组常量不起作用时请检查下面的问题:

  • 某些元素可能未使用正确的字符分隔。 如果省略逗号或分号,或者将逗号或分号放在错误的位置,数组常量可能未正确创建,或者可能会看到警告消息。

  • 选择的单元格区域可能与常量中的元素个数不匹配。 例如,如果在一列中选择六个单元格用于要占用五个单元格的常量,则会在空单元格中显示 #N/A 错误值。 反过来,如果选择的单元格太少,Excel 将忽略没有对应单元格的值。

下面的示例演示可以将数组常量用于数组公式的几种方式。 一些示例使用 TRANSPOSE 函数 将行转换为列,反之亦然。

乘以数组中的各项

  1. 新建一个工作表,然后选择一个宽四列高三行的空单元格块。

  2. 键入以下公式,然后按Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

对数组中的各项求平方

  1. 选择一个宽四列高三行的空单元格块。

  2. 键入以下数组公式,然后按Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    或者,输入下面的数组公式,它使用脱字符号 (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

转置一维行

  1. 选择一列中的五个空白单元格。

  2. 键入以下公式,然后按Ctrl+Shift+Enter:

    =TRANSPOSE({1,2,3,4,5})

    即使输入的是水平数组常量,TRANSPOSE 函数也会将该数组常量转换为列。

转置一维列

  1. 选择一列中的五个空白单元格。

  2. 输入以下公式,然后按Ctrl+Shift+Enter:

    =TRANSPOSE({1;2;3;4;5})

即使输入的是垂直数组常量,TRANSPOSE 函数也会将该常量转换为行。

转置二维常量

  1. 选择一个宽三列高四行的单元格块。

  2. 输入以下常量,然后按Ctrl+Shift+Enter:

    =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

    TRANSPOSE 函数将各行转换为一系列的列。

本节提供基本数组公式的示例。

从现有值创建数组和数组常量

下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。 还演示如何使用同一组值创建数组常量。

从现有值创建数组

  1. 在 Excel 工作表上,选择单元格 C8:E10,并输入此公式:

    ={10,20,30;40,50,60;70,80,90}

    确保在输入 10 之前输入 {(左大括号),在输入 90 之后输入 }(右大括号),因为你要创建数字数组。

  2. Ctrl+Shift+Enter,使用数组公式在单元格区域 C8:E10 中输入此数字数组。 在工作表上,C8 到 E10 应如下所示:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. 选择单元格区域 C1 到 E3。

  4. 在编辑栏中输入以下公式,然后按Ctrl+Shift+Enter:

    =C8:E10

    单元格 C1 到 E3 中将显示一个 3x3 单元格数组,其值与在 C8 到 E10 中所看到的值相同。

从现有值创建数组常量

  1. 选中单元格 C1:C3 后,按 F2 切换到编辑模式。 

  2. F9 将单元格引用转换为值。 Excel 将这些值转换为数组常量。 公式现在应为 ={10,20,30;40,50,60;70,80,90}

  3. Ctrl+Shift+Enter 以数组公式输入数组常量。

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

下面的示例演示如何计算单元格区域中的字符数(包括空格)。

  1. 复制此整个表并粘贴到工作表的单元格 A1 中。

    数据

    这是

    很多单元格

    组合

    构成了

    一个句子。

    A2:A6 中的总字符

    =SUM(LEN(A2:A6))

    最长的单元格内容 (A3)

    =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

  2. 选择单元格 A8,然后按 Ctrl+Shift+Enter 查看单元格 A2:A6 中的字符总数 (66) 。

  3. 选择单元格 A10,然后按 Ctrl+Shift+Enter 以查看单元格 A3 中最长单元格 A2:A6 (的内容) 。

单元格 A8 中使用的公式计算单元格 A2 (A6) 66 个字符的总字符数。

=SUM(LEN(A2:A6))

这样,LEN 函数返回该区域的每个单元格中的每个文本字符串的长度。 然后 SUM函数将这些值相加并显示结果 (66) 。

查找出区域内的 n 个最小值

本示例演示如何查找单元格区域内的三个最小值。

  1. 在单元格 A1:A11 中输入一些随机数字。

  2. 选择单元格 C1 到 C3。 这组单元格将保留数组公式返回的结果。

  3. 输入以下公式,然后按Ctrl+Shift+Enter:

    =SMALL (A1:A11,{1;2;3})

此公式使用数组常量计算 SMALL 函数三次,并返回单元格 A1:A10 中包含的数组中最小的 (1) 、第二小 (2) 和第三个最小的 (3) 成员。若要查找更多值,请向该常量添加更多参数。 还可以对此公式使用其他函数,例如 SUMAVERAGE。 例如:

=SUM (SMALL (A1:A10,{1,2,3})

=AVERAGE (SMALL (A1:A10,{1,2,3})

查找出区域中的 n 个最大值

要找出区域中的多个最大值,可以使用 LARGE 函数替代 SMALL 函数。 此外,下面的示例使用 ROWINDIRECT 函数。

  1. 选择单元格 D1 到 D3。

  2. 在编辑栏中输入此公式,然后按Ctrl+Shift+Enter:

    =LARGE (A1:A10,ROW (INDIRECT ("1:3") ) )

现在,了解一点 ROWINDIRECT 函数可能会有所帮助。 可以使用 ROW 函数创建连续的整数数组。 例如,在练习工作簿中选择包含 10 个单元格的空列,输入此数组公式,然后按Ctrl+Shift+Enter:

=ROW(1:10)

此公式创建由 10 个连续整数组成的一列。 为了查看可能的问题,请在包含数组公式的区域上面插入一行(即第 1 行上)。 Excel 调整行引用,并且此公式生成从 2 到 11 的整数。 要修正该问题,可以向该公式添加 INDIRECT 函数:

=ROW(INDIRECT("1:10"))

INDIRECT 函数使用文本字符串作为参数(这是区域 1:10 由双引号括起的原因)。 当插入行或移动数组公式时,Excel 不会调整文本值。 因此,此 ROW 函数总是生成所需的整数数组。

让我们看一下前面使用的公式 — =LARGE (A5:A14,ROW (INDIRECT ("1:3") ) ) — 从内部括号开始向外工作 :INDIRECT 函数返回一组文本值,在这种情况下,值 1 到 3。 ROW函数反过来生成一个三单元格纵栏数组。 LARGE函数使用单元格区域 A5:A14 中的值,对ROW函数返回的每个引用计算三次。 值 3200、2700 和 2000 将返回到三个单元格纵栏数组。 如果要查找更多值,请向 INDIRECT 函数添加更大的 单元格 区域。

与前面的示例一样,可以将此公式与其他函数(如 SUMAVERAGE)一同使用

查找单元格区域中的最长文本字符串

返回到早期的文本字符串示例,在空单元格中输入以下公式,然后按Ctrl+Shift+Enter:

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

将显示文本"一组单元格"。

让我们从内部元素开始,由内而外深入了解此公式。 LEN函数返回单元格区域 A2:A6 中每个项的长度。 MAX函数计算这些项中的最大值,该值对应于单元格 A3 中最长的文本字符串。

下面的计算稍微有点复杂。 MATCH 函数计算包含最长文本字符串的单元格的偏移量(相对位置)。 为此,需要三个参数:分别是查阅值查阅数组匹配类型MATCH 函数在查阅数组中搜索指定的查阅值。 在这种情况下,查阅值为最长的文本字符串:

(MAX (LEN (A2:A6) )

并且该字符串位于此数组中:

LEN (A2:A6)

匹配类型参数为 0。 匹配类型可以包含值 1、0 或 -1。 如果指定 1,MATCH 返回小于或等于查阅值的最大值。 如果指定 0,MATCH 返回正好等于查阅值的第一个值。 如果指定 -1,MATCH 查找出大于或等于指定查阅值的最小值。 如果未指定匹配类型,Excel 会采用值 1。

最后,INDEX 函数采用这些参数:数组以及该数组内的行号和列号。 单元格区域 A2:A6 提供数组 ,MATCH 函数提供单元格地址,最后一个参数 (1) 指定该值来自数组的第一列。

本节提供高级数组公式的示例。

对包含错误值的区域求和

当您尝试对包含错误值(例如 #N/A)的区域求和时,Excel 中的 SUM 函数不起作用。 本示例显示如何对包含错误的命名为“数据”的区域中的值求和。

=SUM(IF(ISERROR(数据),"",数据))

该公式创建一个新数组,包含除错误值以外的原始值。 从内层函数开始向外运算,ISERROR 函数在单元格区域 (数据) 中搜索错误。 IF 函数在指定的条件计算结果为 TRUE 时返回指定值,在计算结果为 FALSE 时返回另一个值。 在此处,它为所有错误值返回空字符串 (""),因为它们的计算结果为 TRUE,并且返回该区域(数据)中的其他值(因为这些值计算结果为 FALSE,表示它们不包含错误值)。 接着 SUM 函数计算筛选出的数组的总和。

计算区域中错误值个数

本示例与上面的公式相似,但它返回名为“数据”的区域中的错误值个数,而不是将错误值筛选掉:

=SUM(IF(ISERROR(数据),1,0))

该公式创建一个数组,它为包含错误的单元格包含值 1,为不包含错误的单元格包含值 0。 可以简化该公式,并达到相同的结果,方法是移除 IF 函数的第三个参数,如下所示:

=SUM(IF(ISERROR(数据),1))

如果未指定该参数,IF 函数在单元格不包含错误值时返回 FALSE。 可以进一步简化该公式:

=SUM(IF(ISERROR(数据)*1))

此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。

条件求和

可能需要根据条件对值求和。 例如,此数组公式仅对名为“销售量”的区域中的正值求和:

=SUM(IF(Sales>0,Sales))

IF 函数创建正值和 false 值数组。 SUM 函数实际上将忽略 false 值,因为 0+0=0。 在此公式中使用的单元格区域可以由任意数量的行和列组成。

还可以对满足多个条件的值求和。 例如,下面的数组公式计算大于 0 并且小于等于 5 的值:

=SUM((Sales>0)*(Sales<=5)*(Sales))

请注意,如果区域中包含一个或多个非数字单元格,此公式将返回错误。

还可以创建使用 OR 条件的数组公式。 例如,可以对小于 5 和大于 15 的值求和:

=SUM(IF((Sales<5)+(Sales>15),Sales))

IF 函数查找所有小于 5 和大于 15 的值,然后将这些值传递给 SUM 函数。

不能在数组公式中直接使用 ANDOR 函数,因为这些函数返回单一结果,TRUE 或 FALSE,而数组函数需要结果数组。 可以通过使用上一公式中显示的逻辑来解决这一问题。 也就是,对满足 OR 或 AND 条件的值执行加法或乘法等算术运算。

计算零以外的平均值

本示例演示当您需要对区域中的值求平均值时,如何从该区域中移除零。 下面的公式使用名为“销售量”的数据区域:

=AVERAGE(IF(Sales<>0,Sales))

IF 函数创建不等于 0 的值数组,然后将这些值传递给 AVERAGE 函数。

计算两个单元格区域中的不同值个数

此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。 如果这两个区域中的内容完全相同,此公式将返回 0。 要使用此公式,单元格区域的大小必须相同,其维度也必须相同(例如,如果 MyData 是一个 3 行 5 列区域,YourData 也必须是 3 行 5 列):

=SUM(IF(我的数据=你的数据,0,1))

此公式创建与正比较的区域大小相同的新数组。 IF 函数使用值 0 和值 1 填充数组(0 表示单元格不匹配,1 表示单元格匹配)。 然后 SUM 函数返回该数组中的值的和。

可以如下所示简化该公式:

=SUM (1* (MyData<>YourData) )

与计算区域中的错误值的公式相似,此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。

查找区域中最大值的位置

此数组公式返回名为“数据”的单列区域中的最大值所在的行号:

=MIN(IF(数据=MAX(数据),ROW(数据),""))

IF 函数创建与名为“数据”的区域对应的新数组。 如果对应的单元格包含区域中的最大值,则此数组包含该行号。 否则,此数组包含空字符串 ("")。 MIN 函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。 如果名为“数据”的区域包含完全相同的最大值,则该公式返回第一个值的行号。

如果要返回最大值的实际单元格地址,请使用下面的公式:

=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),"")),COLUMN(数据))

确认

本文的一部分基于 Colin Wilcox 撰写的一系列 Excel Power User 列,改编自 2002 年 Excel 年 14 和 15 日的公式(前 Excel MVP)撰写的一本书。

需要更多帮助吗?

你始终可以在 Excel 技术社区中咨询专家或在 Answers 社区获取

另请参阅

动态数组和溢出数组行为

动态数组公式与旧版 CSE 数组公式

FILTER 函数

RANDARRAY 函数

SEQUENCE 函数

SORT 函数

SORTBY 函数

UNIQUE 函数

Excel 中的 #SPILL! 错误

绝对交集运算符: @

公式概述

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

×