如何用数组公式快速计算出新人数

来源：蜘蛛抓取(WebSpider) 时间：2022-07-05 20:57 标签： 使用数组公式计算总分和平均分

根据条件判断返回指定的值

统计满足给定条件的单元格的个数

举报，一经查实，本站将立刻删除。

内容来源于互联网和用户投稿,文章中一旦含有联系方式务必识别真假，本站仅做信息展示不承担任何相关责任，如有侵权或涉及法律问题请联系我们删除()

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

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

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

  • 在这种情况下，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 个不同的公式来计算相同的结果。 这不是一个大问题，但如果你总共有数千行，又如何呢？ 然后，它就可以产生很大差异。

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

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

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

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

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

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

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

  • 选择右侧和下方具有空间的任何空白单元格，然后输入 =SEQUENCE (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 作为区分。

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

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

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

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

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

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

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

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

对话框现在应如下所示：

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

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

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

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

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

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

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

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

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

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

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

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

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

    在键入 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 函数将这些值相加并显示结果 (66) 。 如果要获取平均字符数，可以使用：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

在这种情况下，Excel单元格区域 C2 到 D11 (数组中的值相乘) 然后使用函数将总计相加。 结果等于 $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 将用大括号将数组公式括起来  - 如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。

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

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

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

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

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

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

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

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

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

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

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

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

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

现在，你知道创建数组公式时需要按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. 在这种情况下，你应该在{ } ({ }) 键入左大括号Excel右大括号，Excel将添加第二组。

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

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

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

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

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

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

    单元格 A3 中显示值 85。

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

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

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

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

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

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

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

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

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

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

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

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

  3. 在“引用位置”框中，输入下面的常量（记住要手动键入大括号）：

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

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

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

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

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

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

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

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

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

  2. 或者，输入下面的数组公式，它使用脱字符号 (^)：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    最长的单元格内容 (A3)

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

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

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

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

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

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

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

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

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

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

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

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

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

与前面的示例一样，可以将此公式与其他函数（如 SUM 和 AVERAGE）一同使用。

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

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

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

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

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

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

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

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

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

对包含错误值的区域求和

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

查找区域中最大值的位置

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

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

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

Excel函数，不仅仅是一个处理数据的工具，重要的是还能够建立数据思维的利器。掌握表格的函数试用技巧，不仅能提升工作质量，还把时间掌控得游刃有余。

我们常说，细节决定事情的成败，细节关系到目标和态度。老子说过：“天下大事，必作于细。”表现出一种工匠式的价值追求，追求精益求精，把一种事情做到极致的地步。现代办公都看重绩效，绩效离不开高效率和高质量。有的人过分关注绩效，却不关注从细节上把握效率和质量，经常做一些弄虚作假、不踏实的行为，最终走向失败。注重细节，把握高效，这是做事老到的表现，也是升职加薪的关键。

Excel公式是日常办公的一个重要细节，如果你在百度搜索“公式”会得到以下信息：公式，用数学符号表示，各个量之间的一定关系(如定律或定理)的式子。

Excel公式也就是用数学符号表示的式子，是一种数学运算式。只是Excel公式和数学运算式的书写方式略有区别，数学运算式的等号在最后，而Excel公式必须要以等号开头。比如在Excel中计算120和150的和，可输入公式“=120+150”，按Enter键后Excel会自动计算结果。

只要在单元格中输入以“=”符号开头的内容，Excel就会认为我们输入的是公式。至于平时在工作中会遇到哪些常用的公式，以及怎样输入公式才更快捷，职场君会在下文中为大家总结一些经验。

1.公式这样输入更省时

平时在Excel中进行的计算当然不会只是将120与150相加那么简单。我们要根据实际数据来进行计算。

下面以计算销售总额为例：“产品单价”乘“销售数量”可以得到销售总额，这个公式确实可以得到正确的结果。但问题是，这样输入公式比用计算器敲出了结果后，再输入到单元格还要麻烦。看来这么输入公式肯定是不对的，那么究竟应该怎样输入公式呢？

输入公式时直接引用单元格才是正道。输入“=”符号后，直接单击需要参与计算的单元格，即可将该单元格名称输入到公式中，运算符号需要手动输入。公式输入完成后按Enter键或者直接在编辑栏中单击“输入”按钮即可计算出结果。

当需要对同一单元格区域中的数据进行相同计算时，可以填充公式实现快速计算。填充公式的方法有很多，操作起来也都很简单。

下面职场君介绍几种常用的公式填充方法。

方法1： 选中包含公式的单元格，将光标放在单元格右下角，当光标变成十字形状时，按住鼠标左键，拖动鼠标，即可将公式填充到拖选区域。

方法2： 同样是将光标放置在单元格右下角，当光标变成十字形状时，双击鼠标，即可将公式向下填充到相同数据区域的最后一个单元格。

方法3： 将包含公式的单元格作为选中区域的第一个单元格，按Ctrl+D组合键可自动向下填充公式。按Ctrl+R组合键，自动向右填充公式。

自动填充的公式会根据公式现在所在的位置自动调整引用的单元格。比如，E2单元格中的公式“=C2*D2”，填充到E6单元格后，就自动变成了“=C6*D6”。

以上3种方法适合单元格数量不是很多的时候使用。如果数据非常多，有时会达到1000行或1000列，这时候需要进行大范围的复制操作。其方法为：先使用公式计算出首个单元格结果，然后将光标放在“名称框”方框内，并输入单元格区域名称，

例如输入“E2:E9”，随后按Enter键，将该区域选中，最后按Ctrl+D组合键即可完成公式的复制操作。

当有人第一次跟你说绝对引用、相对引用、混合引用时也许你是懵的，完全不知道是什么意思。但是如果要经常在Excel中用公式完成各种计算你就应该明白绝对引用、相对引用和混合引用的重要性，并熟悉这几种单元格引用的原则。下面都以引用A1单元格为例。

1. 首先来看看什么是相对引用

一般Excel公式中使用最多的是相对引用，“=A1”就是相对引用，A1是单元格的名称，指的是A列与第1行相交处的单元格。

当向下填充公式时，行号会自动发生变化，当向右填充公式时，列标会发生变化。由此可见相对引用时，公式所在单元格的位置改变，引用也随之改变。

应用实例： 相对引用时，公式随着单元格位置的变化自动调整引用位置。相对引用拥有相对的自由，根据表格数据灵活的引用单元格。所引用的单元格行号和列标不添加任何标志。

4. 下面来说说绝对引用

“=$A$1”这种引用形式是绝对引用。绝对引用时，无论公式所在单元格的位置发生怎样的改变，绝对引用的单元格始终保持不变。计算结果也不会发生改变。

知识加油站：绝对符号“$”的作用

把“$”符号想象成一把锁，在行号和列标前面添加“$”符号便可以将行和列锁定，这样公式在被复制到其他单元格时被锁定的行和列就不会更改了。了解“$”符号的作用后再学习混合引用就好理解了。

应用实例：无论将公式填充到哪里，对单元格D2的引用都不会发生改变。绝对引用将引用的单元格绝对的控制住，不允许随意发生改变。行号和列标前均添加“$”符号。

“=$A1”和“=A$1”都是混合引用，混合引用是既包含绝对引用又包含相对引用的单元格引用方式。“$A1”列标前添加了“$”符号，说明列被锁定了，处于绝对引用状态，而行则处于相对引用状态。这时无论将公式填充到任何单元格，对列的引用都不会变化，而对行的引用却会随着单元格的变化而变化。“A$1”被锁定的是行，将公式复制到其他单元格后列标会随着单元格变化，行号一直保持不变。

应用实例： 混合引用时，绝对引用的部分不会随着单元格位置的移动而变化，只有相对引用的部分随着单元格的移动发生变化。

知识加油站：F4键的妙用

借助F4键便可以快速输入绝对引用和混合引用不需要手动输入“$”符号。以“A1”为例，在公式中输入A1后按1次F4键显示“$A$1”，按2次F4键显示 “A$1”，按3次F4显示“$A1”，按4次F4恢复“A1”。

Excel默认的引用方式为“A1”，但是在日常工作中，却会遇到“R1C1”样式的单元格引用。该样式通过“R”+行数字和“C”+列数字来引用单元格。下图中公式中对单元格的引用形式即为R1C1形式。

下表可以对R1C1的引用形式进行详细说明。

如果大家不习惯该格式，可以在“Excel选项”对话框中重新设置Ａ1引用形式。先选中“公式”选项，然后在右侧“使用公式”选项组中，取消勾选“R1C1引用样式”复选框即可。

在Excel工作表中，可以对经常使用的或比较特殊的公式进行命名，提高公式的输入速度，和准确率。

第一步、打开“公式”选项卡，在“定义的名称”组中单击“定义名称”按钮。打开“新建名称”对话框。

第二步、在对话框中输入公式的名称，如果有需要备注的内容就输入备注，在“引用位置”选取框中输入公式。最后单击“确定”按钮关闭对话框。

第三步、在单元格中输入已命名的公式的第一个字，单元格下方即会出现整个名称，双击该名称，便可将该名称输入到单元格中。

当需要同时处理多个单元格中的数据时，可以使用数组公式，一个数组公式能够同时计算尺寸相同的单元格区域中数据的值。数组公式要加大括号{}来表示。

本例将会用到求和函数SUM，在下一小节中小德子会具体介绍该函数的作用。这里主要讲数组公式的输入。

在单元格中输入公式“SUM(B4:B6＊C4:C6)”，按Ctrl+Shift+Enter组合键完成数组公式的输入，计算出结果后，选中数组公式所在单元格，在编辑栏中可以发现公式自动添加了“{}”符号。

知识加油站：数组公式的优点

简洁性：数组公式可以同时对多个数据执行运算，一个复杂的问题，只需一个公式就可以解决；

一致性：多个单元格数组公式中，单击任意一个单元格，看到的公式内容都是相同的；

安全性：不能修改多单元格数组公式的某一部分，可以防止误操作；

文件小：可以使用单个数组公式，而不必用多个普遍公式。

数组公式的语法与普通公式的语法相同。它们都是以“=”开始，无论在普通公式或数组公式中，都可以使用任何内置函数。而数组公式唯一不同之处在于，必须要按Ctrl+Shift+Enter组合键完成公式的输入。

想学习更多Excel技巧，看看看——

《不一样的职场生活——Excel达人速成记+旅行手帐》

Excel达人速成记（全彩印刷）一书对Excel表格的应用干货进行了深入解析，让读者不再视表格为“路人”。同时，还附赠了手帐简笔画内容，以调节枯燥乏味的数据生活。外出旅行时，也可以与书为伴，既增长了知识，又丰富了生活，随时学习、记录心情，伴你快乐行一路！

我要回帖

更多关于 使用数组公式计算总分和平均分 的文章

 

随机推荐