数组公式指南和示例

数组公式指南和示例

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的英文版本以便参考。

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

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

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

  • 快速创建示例数据集。

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

  • 仅对满足特定条件的数字求和, 例如范围中的最小值或位于上下边界之间的数字。

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

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

下载我们的示例

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

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

  • 多单元格数组公式

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

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

    enter时, 你会看到结果溢出到单元格 H10: H19。 请注意, 当你选择溢出区域中的任意单元格时, 溢出范围将以边框突出显示。 你可能还会注意到单元格 H10 中的公式是灰显的。 它们只是为了引用, 因此, 如果你想要调整公式, 则需要选择单元格 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)。

    • 超过    动态数组公式将自动溢出到输出区域中。 如果源数据位于 Excel 表中, 则动态数组公式将在您添加或删除数据时自动调整大小。

    • #SPILL!时发生    动态数组引入了#SPILL! 错误, 表示由于某种原因, 预期溢出范围被阻止。 解决此问题时, 该公式将自动溢出。

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

= {1, 2, 3, 4, 5}= {"一月份", "二月", "三月"}

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

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

  • 创建水平常量

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

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

  • 创建垂直常量

    选择下方有聊天室的任何空白单元格, 然后按 enter = SEQUENCE (5)= {1; 2; 3; 4; 5}。 将显示以下结果:

    创建带有 = SEQUENCE (5) 的垂直数组常量, 或 = {1; 2; 3; 4; 5}

  • 创建二维常量

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

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

    您也可以输入: or = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, 但您希望在放置分号和逗号的位置上格外关注。

    正如你所看到的, 序列选项比手动输入数组常量值具有显著的优势。 它主要为您节省时间, 但也有助于减少手动输入的错误。 它也更易于阅读, 尤其是在半冒号很难区分逗号分隔符。

下面是一个在较大公式中使用数组常量的示例。 在示例工作簿中, 转到公式工作表中的常量, 或者创建新的工作表。

在单元格 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 (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) * 序列 (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)}。 另外,数值不能包含百分号、货币符号、逗号或圆括号。

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

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

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

该对话框现在应如下所示:

从公式中添加命名数组常量 > 定义的名称 > 名称管理器 > 新增

单击"确定", 然后选择带有三个空白单元格的任何行, 然后按 Enter = 第1季度

将显示以下结果:

在公式中使用命名数组常量, 例如 = 第1季度, 其中第1季度已定义为 = {"一月份", "二月", "三月"}

如果希望结果在垂直方向 (而不是水平) 溢出, 可以使用=换位(第1季度)

如果想要显示12个月的列表, 例如在构建财务报表时可能使用的列表, 则可以使用 SEQUENCE 函数将其与当前年份基本。 此函数的整洁之处在于, 即使只显示月份, 也有一个有效的日期, 您可以在其他计算中使用它。 您将在示例工作簿中的命名数组常量快速示例数据集工作表中找到这些示例。

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

使用文本、日期、年份、今天和序列函数的组合来构建12个月的动态列表

这将使用DATE 函数基于当前年份创建日期, 序列将在1月到12月之间创建一个从1到12的数组常量, 然后文本函数将显示格式转换为 "mmm" (Jan、二月、三月等)。 如果想要显示完整月份名称 (如一月), 请使用 "mmmm"。

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

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

  • 数组中的多个项目

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

    您也可以用 (/) 进行除法运算, 加上 (+), 然后用 (-) 进行减法运算。

  • 对数组中的各项求平方

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

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

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

  • 转置一维行

    Enter = 换位 (SEQUENCE (1, 5)), 或= 换位 ({1, 2, 3, 4, 5})

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

  • 转置一维列

    Enter = 换位 (SEQUENCE (5, 1)), 或= 换位 ({1; 2; 3; 4; 5})

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

  • 转置二维常量

    Enter = 换位 (SEQUENCE (3, 4))= 换位 ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12})

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

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

  • 从现有值创建数组

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

    Enter = SEQUENCE (3, 6, 10, 10), 或= {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    请确保在键入10之前键入 {(左大括号), 然后在键入180后键入 "}" (右大括号), 因为您要创建一个数字数组。

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

    使用溢出的区域运算符 (#) 引用现有数组

  • 从现有值创建数组常量

    你可以获取溢出的数组公式的结果, 并将其转换为其组件部分。 选择单元格 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 (C9: C13)), LEN (C9: C13), 0), 1)

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

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

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

    MAX (LEN: C13)

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

    LEN (C9: C13)

    此例中的 "匹配类型" 参数为0。 Match 类型可以是1、0或-1 值。

    • 1-返回小于或等于 lookup val 的最大值

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

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

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

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

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

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

    此示例显示了如何查找单元格区域中的三个最小值, 其中数组是单元格 B9 中的示例数据数组: = INT (RANDARRAY(10, 1) * 100)。 B18has 已创建。 请注意, RANDARRAY 是可变函数, 因此每次 Excel 计算时你将收到一组新的随机数字。

    用于查找第 n 个最小值的 Excel 数组公式: = SMALL (B9 #, SEQUENCE (D9))

    Enter = small (b9 #, 序列 (D9), = SMALL (b9: B18, {1; 2; 3})

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

    = SUM (小号 (B9 #, 序列 (D9))

    = AVERAGE (SMALL (B9 #, 序列 (D9))

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

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

    Enter = 大型 (b9 #、ROW (间接 ("1: 3"))) 或= 大型 (b9: B18、row ("1: 3") )

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

    =ROW(1:10)

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

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

    间接函数将文本字符串用作其参数 (这就是区域1:10 围绕引号括起的原因)。 当插入行或移动数组公式时,Excel 不会调整文本值。 因此,此 ROW 函数总是生成所需的整数数组。 您可以同样轻松地使用序列:

    = SEQUENCE (10)

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

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

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

    使用数组处理错误。 例如, = SUM (ISERROR (Data), "", 数据) 将对名为数据的区域进行求和, 即使它包含错误 (如 #VALUE! 或 #NA!。

  • =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 (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) * (销售))

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

还可以创建使用 OR 条件的数组公式。 例如, 你可以将大于 0小于2500的值相加:

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

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

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

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

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

此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。 如果这两个区域中的内容完全相同,此公式将返回 0。 若要使用此公式, 单元格区域必须具有相同的大小和相同的维度。 例如, 如果我是3行的范围5列, 则数据还必须是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 将为你添加第二个集。

    将显示以下结果。

    公式中的水平数组常量

创建垂直常量

  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 将忽略没有对应单元格的值。

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

乘以数组中的各项

  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

    大约

    40

    50

    60

    70

    80

    90

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

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

    =C8:E10

    通过3x3 单元格数组, 单元格 C1 到 E3 中显示的值与在 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 , 以查看单元格 A2: A6 (单元格 A3) 中最长的内容。

在单元格 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})

此公式使用数组常量来计算函数三次, 并返回单元格 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:

    = 大 (A1: A10, ROW ("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 函数总是生成所需的整数数组。

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

与前面的示例一样, 你可以将此公式与其他函数 (如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 (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 列, 并与 Excel 2002 公式的第14和第15章 (由 John Walkenbach 编写) 是一个以前的 Excel MVP。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

动态数组和溢出数组行为

动态数组公式与旧 CSE 数组公式对比

FILTER 函数

RANDARRAY 函数

SEQUENCE 函数

SINGLE 函数

SORT 函数

SORTBY 函数

UNIQUE 函数

Excel 中的 #SPILL! 错误

公式概述

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

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×