vv数组公式指南和示例

vv数组公式指南和示例 全部显示 全部隐藏 要成为一名 Excel 高级用户，您需要知道如何使用数组公式，它能执行非数组公式所不能执行的计算。下文是基于 Colin Wilcox 撰写的 Excel 高级用户专栏系列，并改编自 John Walkenbach 撰写的《Excel 2002 公式》（英文）一书中的第 14 章和第 15 章，John Walkenbach 是一位 Excel MVP。要了解 John 出版的其他书籍，请参见他的书页（英文）。 本文内容 l 了解数组公式 l 了解数组常量 l 使用基本数组公式 l 使用高级数组公式 了解数组公式 本节介绍数组公式并解释如何对数组公式进行输入、编辑并解答疑难问题。 为什么要使用数组公式？ 如果您在 Excel 中使用过公式，想必知道利用公式可以执行某些相当复杂的操作。例如，可以基于给定的年数计算贷款总成本。但是，如果您确实想精通 Excel，还需要掌握如何使用数组公式。因为使用数组公式可以执行更多复杂的任务，例如： l 计算包含在某个单元格区域中的字符数。 l 仅对满足特定条件的数字求和，例如某一区域中的最小值或介于上限和下限之间的数字。 l 对一系列值中的每第 n 个值求和。 注释   数组公式也被称为“CSE 公式”，这是因为可以按 Ctrl+Shift+Enter 在工作簿中输入它们。 数组和数组公式简介 如果您有过一点编程经验，可能碰到过术语数组。在本文中，数组是项的集合。在 Excel 中，这些项可以位于一行（称为一维水平数组）中，也可位于一列（称为一维垂直数组）中或多行和多列（二维数组）中。无法在 Excel 中创建三维数组或三维数组公式。 数组公式是指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。也可以将数组公式放入单个单元格中，然后计算单个量。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。 下节中的示例将演示如何创建多单元格和单个单元格数组公式。 试一试 本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。 创建多单元格数组公式 1. 打开一个新的空白工作簿。 2. 复制示例工作表数据，并将它粘贴到的新工作簿中（从单元格 A1 开始）。 如何复制示例工作表数据 l 创建一个空白工作簿或工作表。 l 选择“帮助”主题中的示例。 注释   不要选择行或列标题。 从“帮助”中选择示例 l 按 Ctrl+C。 l 在工作表中，选择单元格 A1，然后按 Ctrl+V。 销售人员 汽车类型 销售数量 单价 总销售额 刘鹏 四门轿车 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 中的公式或者删除该列，这都不会影响单个单元格公式。 数组公式还具有以下优点： l 一致性    如果单击 E2 下的任意单元格，您将看到相同的公式。这种一致性有助于确保更高的准确性。 l 安全性    您不能覆盖多单元格数组公式的组成部分，例如单击单元格 E3 并按 Delete。您必须选择整个单元格区域（E2 到 E11），然后更改整个数组的公式，否则只能让数组保留原样。作为一种附加安全措施，必须按 Ctrl+Shift+Enter 确认对公式的更改。 l 文件大小较小    通常可以使用单个数组公式，而不必用多个中间公式。例如，为本次练习创建的工作簿使用单个数组公式在列 E 中计算结果。如果使用 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 公式（例如 =C2*D2），则要使用 11 个不同的公式，而计算得出的结果并无不同。 数组公式语法简介 数组公式主要使用标准公式语法。它们都以等号开始，可以在数组公式中使用任何内置 Excel 函数。使用数组公式的主要不同之处在于，必须按 Ctrl+Shift+Enter 输入公式。执行此操作时，Excel 将用大括号将数组公式括起来 — 如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。 您还需要注意的是数组函数是一种简化形式。例如，都就前面使用的多单元格函数等效于： =C2*D2 =C3*D3 等。单元格 B13 中的单个单元格公式集中了所有这些乘法运算，另外还有将这些小计相加所需的算法：=E2+E3+E4 等。 输入和更改数组公式原则 再强调一下创建数组公式的基本原则：每当需要输入或编辑数组公式时都要按 Ctrl+Shift+Enter。该原则适用于单个单元格公式和多单元格公式。 使用多单元格公式时，还需遵循以下原则： l 必须在输入公式之前选择用于保存结果的单元格区域。在多单元格数组公式练习的第 3 步，您通过选择单元格 E2 到 E11 执行了此操作。 l 不能更改数组公式中单个单元格的内容。要试试是否真的如此，可以选择示例工作簿中的单元格 E3 再按 Delete。 l 可以移动或删除整个数组公式，但无法移动或删除其部分内容。换言之，要缩减数组公式，需先删除现有公式再重新开始。 提示   要删除数组公式，请选择整个公式（例如，=C2:C11*D2:D11），按 Delete，再按 Ctrl+Shift+Enter。 l 不能向多单元格数组公式中插入空白单元格或删除其中的单元格。 扩展数组公式 有时，可能需要扩展数组公式。（记住不能缩减数组公式。）这个过程不复杂，但必须记住上节中列出的原则。 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 中放入该公式的实例。 使用数组公式的缺点 数组公式看起来似乎功能很神奇，但它们也存在某些缺点： l 您可能有时会忘记按 Ctrl+Shift+Enter。请记住每当输入或编辑数组公式时都要按此组合键。 l 其他用户可能不理解您的公式。数组公式相对复杂，因此如果其他人需要修改您的工作簿，您应避免使用数组公式或者确信这些用户知道如何更改您的公式。 l 大型数组公式可能会降低计算速度，具体取决于计算机的处理速度和内存。 了解数组常量 本节介绍数组常量并解释如何对它们进行输入、编辑并解答疑难问题。 数组常量简介 数组常量是数组公式的组成部分。可以通过输入一系列项然后手动用大括号 ({ }) 将该系列项括起来创建数组常量，类似于： ={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} 注释   在这种情况下，应键入左大括号和右大括号 ({ })。 将得到以下结果。 您可能在想为什么不简单地手动键入这些数字。继续学习下去将得到 答案 八年级地理上册填图题岩土工程勘察试题省略号的作用及举例应急救援安全知识车间5s试题及答案 ，本文后面部分的在公式中使用常量一节将演示使用数组常量的优点。 创建垂直常量 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}