Excel2007数组公式指南和示例.doc

1Excel 数组公式指南和示例 Excel 数组公式指南和示例 要成为一名 Excel 高级用户 您需要知道如何使用数组公式 它能执行非数组公式所不能执行的 计算 本文内容 了解数组公式 了解数组常量 使用基本数组公式 使用高级数组公式 了解数组公式 本节介绍数组公式并解释如何对数组公式进行输入 编辑并解答疑难问题 为什么要使用数组公式 如果您在 Excel 中使用过公式 想必知道利用公式可以执行某些相当复杂的操作 例如 可以基 于给定的年数计算贷款总成本 但是 如果您确实想精通 Excel 还需要掌握如何使用数组公 式 因为使用数组公式可以执行更多复杂的任务 例如 计算包含在某个单元格区域中的字符数 仅对满足特定条件的数字求和 例如某一区域中的最小值或介于上限和下限之间的数字 对一系列值中的每第 n 个值求和 注释 数组公式也被称为 CSE 公式 这是因为可以按 Ctrl Shift Enter 在工作簿中输入 它们 数组和数组公式简介 如果您有过一点编程经验 可能碰到过术语数组 在本文中 数组是项的集合 在 Excel 中 这些项可以位于一行 称为一维水平数组 中 也可位于一列 称为一维垂直数组 中或多行和多 列 二维数组 中 无法在 Excel 中创建三维数组或三维数组公式 2Excel 数组公式指南和示例 数组公式是指可以在数组的一项或多项上执行多个计算的公式 数组公式可以返回多个结果 也可 返回一个结果 例如 可以将数组公式放入单元格区域中 并使用数组公式计算列或行的小计 也 可以将数组公式放入单个单元格中 然后计算单个量 位于多个单元格中的数组公式称为多单元格 公式 位于单个单元格中的数组公式称为单个单元格公式 下节中的示例将演示如何创建多单元格和单个单元格数组公式 试一试 本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据 第一组操作是 使用多单元格公式计算一组小计 第二组操作是使用单个单元格公式计算总计 创创建建多多单单元元格格数数组组公公式式 1 打开一个新的空白工作簿 2 复制示例工作表数据 并将它粘贴到的新工作簿中 从单元格 A1 开始 如何复制示例工作表数据 创建一个空白工作簿或工作表 选择 帮助 主题中的示例 按 Ctrl C 在工作表中 选择单元格 A1 然后按 Ctrl V 销售人员汽车类型 销售数量单价总销售额 刘鹏四门轿车52200 双门轿车41800 尹歌四门轿车62300 双门轿车81700 林彩瑜四门轿车32000 双门轿车11600 潘杰四门轿车92150 双门轿车51950 施德福四门轿车62250 双门轿车82000 3 使用附近显示的 粘贴选项 按钮 以匹配目标格式 3Excel 数组公式指南和示例 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 中的公式或者删除该列 这都不会影响单个单元格公式 数组公式还具有以下优点 4Excel 数组公式指南和示例 一一致致性性 如果单击 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 可以移动或删除整个数组公式 但无法移动或删除其部分内容 换言之 要缩减数组公式 需先删除现有公式再重新开始 5Excel 数组公式指南和示例 提示 要删除数组公式 请选择整个公式 例如 C2 C11 D2 D11 按 Delete 再按 Ctrl Shift Enter 不能向多单元格数组公式中插入空白单元格或删除其中的单元格 扩扩展展数数组组公公式式 有时 可能需要扩展数组公式 记住不能缩减数组公式 这个过程不复杂 但必须记住上节中 列出的原则 1 在示例工作簿中 清除位于主表下的所有文本和单个单元格公式 2 将增加的数据行粘贴到工作簿中 从单元格 A12 开始 使用附近显示的 粘贴选项 按钮 以匹配目标格式 潭思琪潭思琪四门轿车四门轿车62500 双门轿车71900 王伟四门轿车42200 双门轿车32000 杨威四门轿车82300 双门轿车82100 3 选择包含当前数组公式 E2 E11 的单元格区域 以及新数据旁边的空单元格 E12 E17 也就是选择单元格 E2 E17 4 按 F2 切换到编辑模式 5 在编辑栏中 将 C11 更改为 C17 将 D11 更改为 D17 然后按 Ctrl Shift Enter Excel 会更新单元格 E2 到 E11 中的公式 并在新单元格 E12 到 E17 中放入该公式的实例 6Excel 数组公式指南和示例 使用数组公式的缺点 数组公式看起来似乎功能很神奇 但它们也存在某些缺点 您可能有时会忘记按 Ctrl Shift Enter 请记住每当输入或编辑数组公式时都要按此组 合键 其他用户可能不理解您的公式 数组公式相对复杂 因此如果其他人需要修改您的工作簿 您应避免使用数组公式或者确信这些用户知道如何更改您的公式 大型数组公式可能会降低计算速度 具体取决于计算机的处理速度和内存 返回页首 了解数组常量 本节介绍数组常量并解释如何对它们进行输入 编辑并解答疑难问题 数组常量简介 数组常量是数组公式的组成部分 可以通过输入一系列项然后手动用大括号 将该系列项 括起来创建数组常量 类似于 1 2 3 4 5 我们在本文前面强调过在创建数组公式时需要按 Ctrl Shift Enter 因为数组常量是数组公式 的组成部分 可以通过键入一对大括号手动将常量括起来 然后使用 Ctrl Shift Enter 输入 整个公式 7Excel 数组公式指南和示例 如果使用逗号分隔 隔开 各个项 将创建水平数组 一行 如果使用分号分隔项 将创建垂直 数组 一列 要创建二维数组 应在每行中使用逗号分隔项 并使用分号分隔每行 使用数组公式时 可以将数组常量用于 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 将得到以下结果 8Excel 数组公式指南和示例 创创建建二二维维常常量量 1 在工作簿中 选择一个宽四列高三行的单元格块 2 在编辑栏中输入下面的公式 然后按 Ctrl Shift Enter 1 2 3 4 5 6 7 8 9 10 11 12 将得到以下结果 在公式中使用常量 现在您已经熟悉如何输入数组常量 下面是一个使用我们讨论过的内容的简单示例 1 打开一张空白工作表 2 从单元格 A1 开始复制下表 使用附近显示的 粘贴选项 按钮 以匹配目标格式 9Excel 数组公式指南和示例 34567 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 输入公式 10Excel 数组公式指南和示例 因为 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 在 引用位置 框中 输入下面的常量 记住要手动键入大括号 一一月月 二二月月 三三月月 对话框中的内容应类似如下 11Excel 数组公式指南和示例 4 单击 确定 5 在工作表中 选择一行中的三个空单元格 6 键入下面的公式 然后按 Ctrl Shift Enter 第第 1 季季度度 将得到以下结果 将命名常量用作数组公式时 切记要输入等号 如果未输入等号 Excel 会将该数组解释为文 本字符串 最后 请记住可以使用文本和数字的组合 数组常量疑难解答 当数组常量不起作用时请检查下面的问题 某些元素可能未使用正确的字符分隔 如果遗漏了逗号或分号 或者如果将它们放错了位 置 将无法正确创建数组常量或者可能显示一条警告消息 12Excel 数组公式指南和示例 选择的单元格区域可能与常量中的元素个数不匹配 例如 如果在一列中选择六个单元格 用于要占用五个单元格的常量 则会在空单元格中显示 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 13Excel 数组公式指南和示例 即使输入的是垂直数组常量 TRANSPOSE 函数也会将该常量转换为行 转转置置二二维维常常量量 1 选择一个宽三列高四行的单元格块 2 输入下面的常量 然后按 Ctrl Shift Enter TRANSPOSE 1 2 3 4 5 6 7 8 9 10 11 12 TRANSPOSE 函数将各行转换为一系列的列 返回页首 使用基本数组公式 本节提供基本数组公式的示例 入门 使用本节中的数据创建两个示例工作表 1 打开一个现有工作簿或创建一个新工作簿 并确保其包含两个空工作表 2 复制下表中的数据 并将它粘贴到工作表中 从单元格 A1 开始 400 水调歌头 1234 1200 明月几时有 5678 3200 把酒问青天 9101112 475 不知天上宫阙 13141516 500 今夕是何年 2000 600 1700 800 2700 3 完成的工作表应如下所示 14Excel 数组公式指南和示例 4 5 将第一个工作表命名为数据 第二个空白工作表命名为数组 从现有值创建数组和数组常量 下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接 还演示如何使用同一 组值创建数组常量 从从现现有有值值创创建建数数组组 1 在示例工作簿中 选择数组工作表 2 选择单元格区域 C1 到 E3 3 在编辑栏中输入下面的公式 然后按 Ctrl Shift Enter 数数据据 E1 G3 将得到以下结果 该公式链接到数据工作表的单元格 E1 到 G3 中存储的值 执行此多单元格数组公式的另一个 方法是在数组工作表的每个单元格中放入唯一的公式 如下所示 数据 E1 数据 F1 数据 G1 15Excel 数组公式指南和示例 数据 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 16Excel 数组公式指南和示例 这组单元格将保留数组公式返回的结果 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 函数 此外 下面的示例 使用 ROW 和 INDIRECT 函数 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 函数总是生成所需的整数数组 17Excel 数组公式指南和示例 让我们以前面使用过的公式为例 LARGE A1 A10 ROW INDIRECT 1 3 从内层的 括号开始向外计算 INDIRECT 函数返回一组文本值 在这种情况下 为值 1 到 3 ROW 函数依次生成包含三个单元格的纵栏式数组 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 帮助 18Excel 数组公式指南和示例 返回页首 使用高级数组公式 本节提供高级数组公式的示例 对包含错误值的区域求和 当试图对包含错误值 例如 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 销售量 19Excel 数组公式指南和示例 IF 函数创建正值和 false 值数组 SUM 函数实际上将忽略 false 值 因为 0 0 0 在此 公式中使用的单元格区域可以由任意数量的行和列组成 还可以对满足多个条件的值求和 例如 下面的数组公式计算大于