excel数组公式指南和示例

excel数组公式指南和示例 数组公式指南和示例 要成为一名 Excel 高级用户，您需要知道如何使用数组公式，它能执行非数组公式所不能执行的计算。下文是基于 Colin Wilcox 撰写的 Excel 高级用户专栏系列，并改编自 John Walkenbach 撰写的《Excel 2002 公式》(英文)一书中的第 14 章和第 15 章，John Walkenbach 是一位 Excel MVP。要了解 John 出版的其他书籍，请参见他的书页(英文)。 本文内容 , 了解数组公式 , 了解数组常量 , 使用基本数组公式 , 使用高级数组公式 了解数组公式 本节介绍数组公式并解释如何对数组公式进行输入、编辑并解答疑难问题。 为什么要使用数组公式, 如果您在 Excel 中使用过公式，想必知道利用公式可以执行某些相当复杂的操作。例如，可以基于给定的年数计算贷款总成本。但是，如果您确实想精通 Excel，还需要掌握如何使用数组公式。因为使用数组公式可以执行更多复杂的任务，例如: , 计算包含在某个单元格区域中的字符数。 , 仅对满足特定条件的数字求和，例如某一区域中的最小值或介于上限和下限之间的数字。 , 对一系列值中的每第 n 个值求和。 注释 数组公式也被称为“CSE 公式”，这是因为可以按 Ctrl+Shift+Enter 在工作簿中输入它们。 数组和数组公式简介 如果您有过一点编程经验，可能碰到过术语数组。在本文中，数组是项的集合。在 Excel 中，这些项可以位于一行(称为一维水平数组)中，也可位于一列(称为一维垂直数组)中或多行和多列(二维数组)中。无法在 Excel 中创建三维数组或三维数组公式。 数组公式是指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。也可以将数组公式放入单个单元格中，然后计算单个量。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。 下节中的示例将演示如何创建多单元格和单个单元格数组公式。 试一试 本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。 创建多单元格数组公式 1. 打开一个新的空白工作簿。 2. 复制示例工作表数据，并将它粘贴到的新工作簿中(从单元格 A1 开始)。 如何复制示例工作表数据 销售人员 汽车类型 销售数量 单价 总销售额 刘鹏 四门轿车 5 2200 双门轿车 4 1800 尹歌 四门轿车 6 2300 双门轿车 8 1700 林彩瑜 四门轿车 3 2000 双门轿车 1 1600 潘杰 四门轿车 9 2150 双门轿车 5 1950 施德福 四门轿车 6 2250 双门轿车 8 2000 3. 使用附近显示的“粘贴选项”按钮 以匹配目标格式。 4. 要将数组(单元格区域 C2 到 D11)中的值相乘，请选择单元格 E2 到 E11，然后在编辑栏中 输入以下公式: =C2:C11*D2:D11 5. 按 Ctrl+Shift+Enter。 Excel 使用大括号 ({ }) 将公式括起，并将一个公式实例放入所选区域的每个单元格中。因为执行速度很 快，所以您在 E 列中看到的是每位销售人员每种轿车类型的总销售额。 创建单个单元格数组公式 1. 在工作簿的单元格 A13 中，键入总销售额。 2. 在单元格 B13 中，键入下面的公式并按 Ctrl+Shift+Enter: =SUM(C2:C11*D2:D11) 这时，Excel 会将数组(单元格区域 C2 到 D11)中的值相乘，然后使用 SUM函数将这些乘积相加。结 果等于 ,111,800 的总销售额。本示例演示了此类公式的强大功能。例如，假定您有 15,000 行数据。您 可以通过在单个单元格中创建数组公式来对部分或全部数据求和。 另外，请注意单个单元格公式(单元格 B13 中)与多单元格公式(单元格 E2 到 E11 中的公式)完全无 关。这使得使用数组公式具有另一个优点 — 灵活性。您可以执行任意次数的操作，例如更改列 E 中的公 式或者删除该列，这都不会影响单个单元格公式。 数组公式还具有以下优点: , 一致性 如果单击 E2 下的任意单元格，您将看到相同的公式。这种一致性有助于确保更高的准 确性。 , 安全性 您不能覆盖多单元格数组公式的组成部分，例如单击单元格 E3 并按 Delete。您必须 选择整个单元格区域(E2 到 E11)，然后更改整个数组的公式，否则只能让数组保留原样。作为一种附 加安全措施，必须按 Ctrl+Shift+Enter 确认对公式的更改。 , 文件大小较小 通常可以使用单个数组公式，而不必用多个中间公式。例如，为本次练习创建的 工作簿使用单个数组公式在列 E 中计算结果。如果使用标准公式(例如 =C2*D2)，则要使用 11 个不 同的公式，而计算得出的结果并无不同。 数组公式语法简介 数组公式主要使用标准公式语法。它们都以等号开始，可以在数组公式中使用任何内置 Excel 函数。使用 数组公式的主要不同之处在于，必须按 Ctrl+Shift+Enter 输入公式。执行此操作时，Excel 将用大括号将 数组公式括起来 — 如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。 您还需要注意的是数组函数是一种简化形式。例如，前面使用的多单元格函数等效于: =C2*D2 =C3*D3 等。单元格 B13 中的单个单元格公式集中了所有这些乘法运算，另外还有将这些小计相加所需的算法: =E2+E3+E4 等。 输入和更改数组公式原则 再强调一下创建数组公式的基本原则:每当需要输入或编辑数组公式时都要按 Ctrl+Shift+Enter。该原则 适用于单个单元格公式和多单元格公式。 使用多单元格公式时，还需遵循以下原则: , 必须在输入公式之前选择用于保存结果的单元格区域。在多单元格数组公式练习的第 3 步，您通 过选择单元格 E2 到 E11 执行了此操作。 , 不能更改数组公式中单个单元格的内容。要试试是否真的如此，可以选择示例工作簿中的单元格 E3 再按 Delete。 , 可以移动或删除整个数组公式，但无法移动或删除其部分内容。换言之，要缩减数组公式，需先 删除现有公式再重新开始。 提示 要删除数组公式，请选择整个公式(例如，=C2:C11*D2:D11)，按 Delete，再按 Ctrl+Shift+Enter。 , 不能向多单元格数组公式中插入空白单元格或删除其中的单元格。 扩展数组公式 有时，可能需要扩展数组公式。(记住不能缩减数组公式。)这个过程不复杂，但必须记住上节中列出的 原则。 1. 在示例工作簿中，清除位于主表下的所有文本和单个单元格公式。 2. 将增加的数据行粘贴到工作簿中(从单元格 A12 开始)。使用附近显示的“粘贴选项”按钮 以 匹配目标格式。 潭思琪 四门轿车 6 2500 双门轿车 7 1900 王伟 四门轿车 4 2200 双门轿车 3 2000 杨威 四门轿车 8 2300 双门轿车 8 2100 3. 选择包含当前数组公式 (E2:E11) 的单元格区域，以及新数据旁边的空单元格 (E12:E17)。也就 是选择单元格 E2:E17。 4. 按 F2 切换到编辑模式。 5. 在编辑栏中，将 C11 更改为 C17，将 D11 更改为 D17，然后按 Ctrl+Shift+Enter。Excel 会更新 单元格 E2 到 E11 中的公式，并在新单元格 E12 到 E17 中放入该公式的实例。 使用数组公式的缺点 数组公式看起来似乎功能很神奇，但它们也存在某些缺点: , 您可能有时会忘记按 Ctrl+Shift+Enter。请记住每当输入或编辑数组公式时都要按此组合键。 , 其他用户可能不理解您的公式。数组公式相对复杂，因此如果其他人需要修改您的工作簿，您应 避免使用数组公式或者确信这些用户知道如何更改您的公式。 , 大型数组公式可能会降低计算速度，具体取决于计算机的处理速度和内存。 返回页首 了解数组常量 本节介绍数组常量并解释如何对它们进行输入、编辑并解答疑难问题。 数组常量简介 数组常量是数组公式的组成部分。可以通过输入一系列项然后手动用大括号 ({ }) 将该系列项括起来创建 数组常量，类似于: ={1,2,3,4,5} 我们在本文前面强调过在创建数组公式时需要按 Ctrl+Shift+Enter。因为数组常量是数组公式的组成部分， 可以通过键入一对大括号手动将常量括起来。然后使用 Ctrl+Shift+Enter 输入整个公式。 如果使用逗号分隔(隔开)各个项，将创建水平数组(一行)。如果使用分号分隔项，将创建垂直数组(一 列)。要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。 使用数组公式时，可以将数组常量用于 Excel 提供的所有内置函数中。下面几节将解释如何创建各种类型 的常量以及如何将这些常量用于 Excel 中的函数。 创建一维和二维常量 下面将为您提供创建水平、垂直和二维常量的练习。 创建水平常量 1. 使用前一列所在工作簿，或启动新的工作簿。 2. 选择单元格 A1 到 E1。 3. 在编辑栏中输入下面的公式，然后按 Ctrl+Shift+Enter: ={1,2,3,4,5} 注释 在这种情况下，应键入左大括号和右大括号 ({ })。 将得到以下结果。 您可能在想为什么不简单地手动键入这些数字。继续学习下去将得到答案，本文后面部分的在公式中使用 常量一节将演示使用数组常量的优点。 创建垂直常量 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 4 5 6 7 3. 在单元格 A3 中，输入下面的公式，然后按 Ctrl+Shift+Enter: =SUM(A1:E1*{1,2,3,4,5}) 请注意，Excel 用另一对大括号将常量括起来，这是因为您是以数组公式的形式输入该常量。 单元格 A3 中显示 85。下节将讨论此公式的计算方法。 数组常量语法简介 刚才使用的公式包含若干部分。 函数 存储数组 运算符 数组常量 括号内的最后元素是数组常量:{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. 在工作表中，选择一行中的三个空单元格。 6. 键入下面的公式，然后按 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 对数组中的各项求平方 , 选择一个宽四列高三行的空单元格块。 , 键入下面的数组公式，然后按 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. 打开一个现有工作簿或创建一个新工作簿，并确保其包含两个空工作表。 2. 复制下表中的数据，并将它粘贴到工作表中(从单元格 A1 开始)。 水调歌头 400 1 2 3 4 明月几时有 1200 5 6 7 8 把酒问青天 3200 9 10 11 12 不知天上宫阙 475 13 14 15 16 今夕是何年 500 2000 600 1700 800 2700 3. 完成的工作表应如下所示。 4. 5. 将第一个工作表命名为数据，第二个空白工作表命名为数组。 从现有值创建数组和数组常量 下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。还演示如何使用同一组值创 建数组常量。 从现有值创建数组 1. 在示例工作簿中，选择数组工作表。 2. 选择单元格区域 C1 到 E3。 3. 在编辑栏中输入下面的公式，然后按 Ctrl+Shift+Enter: =数据!E1:G3 将得到以下结果。 该公式链接到数据工作表的单元格 E1 到 G3 中存储的值。执行此多单元格数组公式的另一个方法是在数 组工作表的每个单元格中放入唯一的公式，如下所示。 =数据!E1 =数据!F1 =数据!G1 =数据!E2 =数据!F2 =数据!G2 =数据!E3 =数据!F3 =数据!G3 如果更改了数据工作表中的某些值，这些更改将显示在数组工作表中。请注意，更改数据工作表中的任何 值都必须遵循数组公式的编辑原则。有关这些原则的详细信息，请参见了解数组公式一节。 从现有值创建数组常量 1. 在数组工作表上，选择单元格 C1 到 E3。 2. 按 F2 切换到编辑模式。 3. 按 F9 将单元格引用转换为值。Excel 将这些值转换为数组常量。 4. 按 Ctrl+Shift+Enter 输入数组常量作为数组公式。 Excel 使用下面的数组常量替换 =数据!E1:G3 数组公式: ={1,2,3;5,6,7;9,10,11} 数据与数组工作表之间的链接已破坏，数组公式已为数组常量替代。 在单元格区域中对字符计数 下面的示例演示如何计算单元格区域中的字符数(包括空格)。 , 在数据工作表的单元格 C7 中输入下面的公式，然后按 Ctrl+Shift+Enter: =SUM(LEN(C1:C5)) 单元格 C7 中显示值 25。 这样，LEN 函数返回该区域的每个单元格中的每个文本字符串的长度。然后SUM 函数将这些值相加，并在 包含该公式的单元格 C7 中显示结果。 查找出区域内的 N 个最小值 本示例演示如何查找单元格区域内的三个最小值。 1. 在数据工作表上，选择单元格 A12 到 A14。 这组单元格将保留数组公式返回的结果。 2. 在编辑栏中输入下面的公式，然后按 Ctrl+Shift+Enter: =SMALL(A1:A10,{1;2;3}) 值 400、 475 和 500 将分别显示在单元格 A12 到 A14 中。 此公式使用数组常量计算 SMALL 函数三次，并返回单元格 A1:A10 中包含的数组中的最小值 (1)、次小值 (2) 和第三小值 (3)。要查找出更多的值，可以向该常量添加更多参数并向 A12:A14 区域添加同等个数的 结果单元格。还可以对此公式使用其他函数，例如 SUM 或 AVERAGE。例如: =SUM(SMALL(A1:A10,{1;2;3})) =AVERAGE(SMALL(A1:A10,{1;2;3})) 查找出区域中的 N 个最大值 要找出区域中的多个最大值，可以使用 LARGE 函数替代 SMALL 函数。此外，下面的示例使 INDIRECT 函数。 用 ROW 和 1. 在数据工作表上，选择单元格 A12 到 A14。 2. 按 Delete 清除已有公式但保持单元格处于选中状态。 3. 在编辑栏上输入下面的公式，并按 Ctrl+Shift+Enter: =LARGE(A1:A10,ROW(INDIRECT("1:3"))) 值 3200、2700 和 2000 分别显示在单元格 A12 到 A14 中。 现在，了解一点 ROW 和 INDIRECT 函数可能会有所帮助。可以使用 ROW 函数创建连续的整数数组。例如，在练习工作簿中选择一个包含 10 个单元格的空列，在单元格 A1:A10 中输入下面的数组公式，然后按 Ctrl+Shift+Enter: =ROW(1:10) 此公式创建由 10 个连续整数组成的一列。为了查看可能的问题，请在包含数组公式的区域上面插入一行(即第 1 行上)。Excel 调整行引用，并且此公式生成从 2 到 11 的整数。要修正该问题，可以向该公式添加 INDIRECT 函数: =ROW(INDIRECT("1:10")) INDIRECT 函数使用文本字符串作为参数(这是区域 1:10 由双引号括起的原因)。当插入行或移动数组公式时，Excel 不会调整文本值。因此，此 ROW函数总是生成所需的整数数组。 让我们以前面使用过的公式为例 — =LARGE(A1:A10,ROW(INDIRECT("1:3")))— 从内层的括号开始向外 1 到 3。ROW 函数依次生成包含三个单元计算:INDIRECT 函数返回一组文本值，在这种情况下，为值 格的纵栏式数组。 LARGE函数使用单元格 A1:A10 中的值，并且它计算三次，每次都对应于 ROW 函数返回的每个引用。值 3200、2700 和 2000 返回到这三个单元格纵栏式数组中。如果要查找更多值，可以向 INDIRECT 函数添加更多的单元格区域。 最后，可以将此公式与其他函数一起使用，例如 SUM 和 AVERAGE。 查找单元格区域中的最长文本字符串 本示例查找单元格区域中的最长文本字符串。本公式仅在数据区域包含单列单元格时适用。 , 在数据工作表上，清除单元格 C7 中的已有公式，并在该单元格中输入下面的公式，然后按 Ctrl+Shift+Enter: =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1) 值不知天上宫阙显示在单元格 C7 中。 让我们以此公式为例，从内层元素开始向外进行运算。LEN 函数返回单元格区域 C1:C5 中的每个项的长度。MAX 函数计算这些项中的最大值，它对应于最长文本字符串，位于单元格 C3 中。 下面的计算稍微有点复杂。MATCH 函数计算包含最长文本字符串的单元格的偏移量(相对位置)。为此，需要三个参数:分别是查阅值、查阅数组和匹配类型。MATCH 函数在查阅数组中搜索指定的查阅值。在这种情况下，查阅值为最长的文本字符串: (MAX(LEN(C1:C5)) 并且该字符串位于此数组中: LEN(C1:C5) 匹配类型参数为 0。匹配类型可以包含值 1、0 或 -1。如果指定 1，MATCH返回小于或等于查阅值的最大值。如果指定 0，MATCH 返回正好等于查阅值的第一个值。如果指定 -1，MATCH 查找出大于或等于指定查阅值的最小值。如果未指定匹配类型，Excel 会采用值 1。 最后，INDEX 函数采用这些参数:数组以及该数组内的行号和列号。单元格区域 C1:C5 提供该数组，MATCH 函数提供单元格地址，最后的参数 (1) 指定该值来自数组的第一列。 有关此处讨论的函数的详细信息，请参见 Excel 帮助。 返回页首 使用高级数组公式 本节提供高级数组公式的示例。 对包含错误值的区域求和 当试图对包含错误值(例如 #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(销售量>0,销售量)) IF 函数创建正值和 false 值数组。SUM 函数实际上将忽略 false 值，因为 0+0=0。在此公式中使用的单元格区域可以由任意数量的行和列组成。 还可以对满足多个条件的值求和。例如，下面的数组公式计算大于 0 并且小于等于 5 的值: =SUM((销售量>0)*(销售量<=5)*(销售量)) 请注意，如果区域中包含一个或多个非数字单元格，此公式将返回错误。 还可以创建使用 OR 条件的数组公式。例如，可以对小于 5 和大于 15 的值求和: =SUM(IF((销售量<5)+(销售量>15),销售量)) IF 函数查找所有小于 5 和大于 15 的值，然后将这些值传递给 SUM 函数。 要点 不能在数组公式中直接使用 AND 和 OR 函数，因为这些函数返回单一结果，TRUE 或 FALSE，而数组函数需要结果数组。可以通过使用上一公式中显示的逻辑来解决这一问题。也就是，对满足 OR 或 AND 条件的值执行加法或乘法等算术运算。 计算零以外的平均值 本示例演示当您需要对区域中的值求平均值时，如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域: =AVERAGE(IF(销售量<>0,销售量)) IF 函数创建不等于 0 的值数组，然后将这些值传递给 AVERAGE 函数。 计算两个单元格区域中的不同值个数 此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。如果这两个区域中的内容完全相同，此公式将返回 0。要使用此公式，单元格区域必须大小相同并且包含相同的维数: =SUM(IF(我的数据=您的数据,0,1)) 此公式创建与正比较的区域大小相同的新数组。IF 函数使用值 0 和值 1 填充数组(0 表示单元格不匹配，1 表示单元格匹配)。然后 SUM 函数返回该数组中的值的和。 可以如下所示简化该公式: =SUM(1*(我的数据<>您的数据)) 与计算区域中的错误值的公式相似，此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。 查找区域中最大值的位置 此数组公式返回名为“数据”的单列区域中的最大值所在的行号: =MIN(IF(数据=MAX(数据),ROW(数据),"")) IF 函数创建与“数据”区域对应的新数组。如果对应的单元格包含区域中的最大值，则此数组包含该行号。否则，此数组包含空字符串 ("")。MIN 函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。如果“数据”区域包含完全相同的最大值，该公式返回第一个值的行号。 如果要返回最大值的实际单元格地址，请使用下面的公式: =ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),"")),COLUMN(数据))