动态命名范围

什么是动态命名范围？

顾名思义，动态创建范围以自动容纳或接受新值。在动态命名范围中，引用会响应变化。如果新值添加到列表中，它会以默认方式扩展。假设从列表中删除或移除了特定值。列表会自动收缩。动态命名范围中的引用或公式可能会根据用户输入或另一个单元格中的值而变化。为列表或数据创建动态命名范围有助于保存编辑的数据更改。在本教程中，将简要解释创建动态命名范围的各种方法。

动态命名范围的一些示例如下：

示例 1：为给定数据创建动态命名范围

在此示例中，将简要解释创建动态命名范围的分步过程。

步骤 1：在工作表中输入所需数据，即 A1:A5。

步骤 2.1：要创建动态命名范围，首先为相应数据创建命名范围。命名范围有助于用户在公式中更快、更轻松地寻址范围名称。此外，公式可以轻松识别范围。

步骤 2.2：选择工作表中的数据。从“公式”选项卡中的“定义名称”组中选择“定义名称”。

步骤 2.3：将出现“新建名称”对话框。在其中，在“名称”框中输入数据的名称。选定范围将出现在“引用位置”部分。

步骤 2.4：在此工作表中，范围被命名为“Numbers”。已为给定数据创建了命名范围。输入数据后，按 Enter。

另一种快速创建命名范围的方法如下：

选择数据中的相应范围，然后在“名称”框中键入范围名称。按 Enter 键，即可为数据创建命名范围。

通过命名“Numbers”创建了范围。

步骤 3：要对命名范围求和，请选择一个新单元格并输入公式 =SUM (Numbers)。

从工作表中，SUM 函数计算给定数据的总和值。

步骤 4：例如，如果向数据添加新值，Excel 不会更新新数据。结果保持不变。

从上面的工作表中，添加了名为 100 的新值，结果保持不变。

步骤 5：创建动态命名范围以自动更新命名范围中的新数据。

步骤 6：在“公式”选项卡中的“已定义名称”组中单击“名称管理器”。将显示“名称管理器”对话框。在其中，选择“编辑”选项。

步骤 7：将显示“编辑名称”对话框。在“引用位置”部分，键入公式 =OFFSET ($A$1, 0, 0, COUNTA ($A: $A), 1)。

步骤 8：按 OK。单元格 C1 中显示的结果将自动更新为新值。

步骤 9：在公式中，OFFSET 函数包含五个参数。这里 $A$1 称为引用，偏移行 -0，偏移列 -0，COUNTA ($A: $A) 称为高度，宽度称为 1。COUNTA ($A: $A) 计算列中存在的值，如果添加新值，COUNTA ($A: $A) 会增加。因此 OFFSET 函数会扩展。

步骤 10：现在，向 A 列添加任何值。它会自动更新选定单元格中的结果。

OFFSET 函数

一个熟悉的函数是 OFFSET 函数，用于创建动态命名范围。此函数表示从引用单元格开始的具有选择性行数和列数的范围。

语法

在公式中，

引用 - 公式中的引用指示用户希望基于偏移量。它指的是单元格或相邻单元格。

行 - 所需的行数，无论是向上还是向下，用户希望左上角单元格被引用。例如，如果行参数为 5，则表示有五行在引用之下。

列 - 列号指示用户希望结果的左上角单元格被引用。如果列号为正，则表示起始引用的右侧，如果为负，则表示起始引用的左侧。例如，如果列参数为 5，则引用中的左上角单元格在引用的右侧五列。

高度 - 高度值必须为正。这是一个可选参数，表示返回的引用。

宽度 - 宽度值必须为正，表示多列。这是一个可选参数，表示返回的引用。

Excel 中为什么需要 OFFSET 函数？

有一个问题：为什么需要 OFFSET 函数？因为直接写入单元格引用更容易。出于以下原因使用 OFFSET 函数：

创建动态范围 - 例如，写入单元格引用 A1:B5 是静态的，执行某些任务更容易。有时数据会发生变化，例如工作表中频繁添加新列或行。

范围地址 - 有时，范围的地址未知。因此使用 OFFSET 函数。

以下是 OFFSET 函数的示例：

示例 2： 对每周添加新数据的单元格中的数据求和。

在此示例中，数据包含月份名称和每月奖金。在源数据中，每个新数据都添加到总值之上。用户可以手动更新 SUM 公式中的总计，或者使用 OFFSET 函数。OFFSET 函数用于汇总数据，因为每月都会添加新数据。要遵循的步骤是：

步骤 1：在工作表中输入数据，即 A1:B7，包括月份和奖金。

步骤 2：要对 B7 之前的数据求和，选择一个名为“Total”的新单元格，并输入公式 =SUM (B2:B7)。结果将如下所示：

步骤 3：如果每月向列表中添加新值，如何对值求和？这里使用 OFFSET 函数。例如，如果新值添加到单元格 B8 中，公式将类似于 =SUM (B2:(OFFSET(B9,-1, 0)))。在公式中，B9 称为单元格引用，-1 表示总值正上方，'0' 称为列号，因为无需更改列号。

从上面的工作表中，使用 OFFSET 函数会自动添加新值。

示例 2.1 - 显示给定数据中最后“N”个月的奖金总和，

步骤 1：在工作表中输入数据，如下所示：

步骤 2：在单元格 E4 中键入公式 =SUM (OFFSET (B1, COUNT (B:B)-E3+1, 0, E3,1))。

步骤 3：按 Enter。最后两个月的奖金总和将显示在单元格 E4 中。

上面的工作表将显示结果为 154，即最后两个月的总和。

公式解释如下：

引用 - 在公式中，B1 作为标题，需要对列中存在的值求和。

行 - 要计算 B 列中的单元格，使用 COUNT 或 COUNTA 函数。COUNT 和 COUNTA 之间的区别在于，COUNT 函数返回包含值的单元格数量，其中减去最后“N”个月，并添加值 1。COUNTA 函数返回列中的所有单元格。因此不添加 1。

列 - 要偏移的列数为零。

高度 - 值 'E3' 描述了要汇总的行数，如 E3 中所述。

宽度 - 这里，宽度指定为 1。

示例 2.2 - 如何使用 OFFSET 函数计算数据的平均值？

要计算选定数据的平均值，应遵循的步骤是：

步骤 1：在工作表中输入数据，如下所示：

步骤 2：在此，要计算“N”个月的平均值，在单元格 E4 中键入公式 =AVERAGE (OFFSET ($B$1, COUNT (B:B)-$E$3+1, 0, $E$3, 1))。

步骤 3：按 Enter。选定月份的平均值将显示在单元格 E4 中。

上面的工作表将在单元格 E4 中显示结果，即 4 月、5 月、6 月和 7 月的平均值。

示例 2.3 - 如何使用 OFFSET 函数计算数据的最大值？

要计算选定数据的最大值，应遵循的步骤是：

步骤 1：在工作表中输入数据，如下所示：

步骤 2：要查找给定数据中的最大值，在单元格 E5 中键入公式 =MAX (OFFSET (B1, COUNT (B:B)-$E$3+1, 0, $E$3, 1))。这里计算了 4 月、5 月、6 月和 7 月的最大值。

步骤 3：按 Enter。在四个月中，最大值显示在单元格 E5 中。

这里结果显示为 90，这是 4 月 89、5 月 90、6 月 76 和 7 月 78 的数据中的最大值。

示例 2.4 - 如何使用 OFFSET 函数计算数据的最小值？

要计算选定数据的最小值，应遵循的步骤是：

步骤 1：在工作表中输入数据，如下所示：

步骤 2：要查找给定数据中的最小值，在单元格 E5 中键入公式 =MIN (OFFSET (B1, COUNT (B:B)-$E$3+1, 0, $E$3, 1))。这里计算了 4 月、5 月、6 月和 7 月的最小值。

步骤 3：按 Enter。在四个月中，最小值显示在单元格 E5 中。

这里结果显示为 76，这是 4 月 89、5 月 90、6 月 76 和 7 月 78 的数据中的最小值。

从示例 2.2、2.3 和 2.4 可以看出，在 MAX、MIN 和 AVERAGE 函数中使用 OFFSET 函数的优点是，每次用户在编辑源表时无需修改公式。当工作表中添加或删除新行时，它会默认计算最后四个数据。

Excel VLOOKUP 和 HLOOKUP 中的 OFFSET。

顾名思义，VLOOKUP 和 HLOOKUP 分别称为垂直查找和水平查找。垂直查找返回查找列右侧的值。水平查找返回查找列左侧的值。

VLOOKUP

VLOOKUP 函数的示例如下：

示例 3： 给定数据显示了月份和奖金。使用 VLOOKUP 函数检索指定月份的数据。

步骤 1：在工作表中输入数据，如下所示：

步骤 2：选择单元格 E2，用户希望在此处显示结果，并键入公式 =VLOOKUP (E1, A2:B8, 2, FALSE)

步骤 3：按 Enter。VLOOKUP 函数返回查找列右侧的值。

上面的工作表将显示结果为 90，这是指定数据 MARCH 右侧的当前值。

如果数据互换会发生什么？

上面的工作表将显示结果为不适用，因为数据互换了，因为 VLOOKUP 仅返回查找列右侧的值。

示例 4： 如何在 Excel 中计算向上查找？

从示例 3 中，VLOOKUP 计算右侧的数据。返回上方数据的步骤如下：

步骤 1：在工作表中输入数据，如下所示：

步骤 2：选择一个新单元格并键入公式 =OFFSET(B3:H4,0, MATCH(B1, OFFSET(B3:H4,1,0,1, COLUMNS(B3:H4)),0)-1,1,1)。

上面的工作表将显示结果为 56，即指定月份 1 月的值。

HLOOKUP

HLOOKUP 函数是 VLOOKUP 的姊妹函数。它在表格的顶行或值数组中查找值，并在用户指定的行中返回相同的值，但位于相似的列中。

语法

查找值 - 它指的是要在表格顶行中搜索的值。该值可以是文本字符串或引用。

表格数组是引用或数组的范围，其中搜索或查找指定数据。

行索引号 - 行索引号存在于数组表格中，其中返回匹配值。

例如，如果行索引号为 1，它将返回表格数组中顶行的值，如果行索引号为 2，它将返回表格数组中第二行的值。

范围查找 - 范围查找函数接受布尔值。布尔值用于指示近似匹配或精确匹配。这里 TRUE 表示近似匹配，FALSE 表示精确匹配。

HLOOKUP 的示例如下：

示例 5： 使用 HLOOKUP 函数从表格中检索指定数据。

步骤 1：在工作表中输入数据，其中包含各种科目的学生分数。

步骤 2：要查找 Kajol 的科学分数，在单元格 C9 中输入公式 =HLOOKUP ("KAJOL", A1:D5, 4, FALSE)。

步骤 3：按 Enter。HLOOKUP 函数从给定数据中检索 Kajol 的科学分数。

公式 "KAJOL" 称为查找值，A1:D5 是表格数组。4 是行索引号，HLOOKUP 值从第四行获取数据，FALSE 表示精确匹配。

示例 5.1： 检索英语考了 100 分的学生的物理分数。

需要遵循的步骤如下：

步骤 1：在工作表中输入数据，其中包含各种科目的学生分数。

步骤 2：选择一个新单元格，用户希望在此处显示结果，并输入公式 =HLOOKUP (100, B2:D5, 4, FALSE)。

步骤 3：结果将显示为 67，即英语考了 100 分的学生。

从上面的公式中，100 称为查找值，表示物理考了 67 分的学生。B2:D5 是给定日期范围，4 是 HLOOKUP 函数返回值的行索引号。False 称为范围查找，只需要精确匹配。

示例 5.2：检索名字以 'S' 开头的学生的数学分数。

使用示例 5.1 中的相同表格，应遵循的步骤是：

步骤 1：在所需工作表中输入数据。

步骤 2：要查找名字以 'S' 开头的学生的数学分数，选择一个新单元格并键入公式 =HLOOKUP ("S*", A1:D5, 3, FALSE)。

步骤 3：按 Enter。结果将显示在选定单元格中，即 Sharma 的数学分数。

上面的工作表将显示结果为 60，即 Sharma 的数学分数，他的名字以 'S' 开头。

在公式中，字母 'S*' 称为查找值，表示名字以 S 开头。表格范围或数组是 A1:D5。3 表示行索引号。FALSE 描述精确匹配。

示例 5.3：使用 HLOOKUP 函数从两个表格中检索指定数据。

要从两个表格中检索数据，应遵循的步骤是：

步骤 1：在工作表中输入数据，如下所示：

步骤 2：要查找学生的印地语分数，在单元格中输入公式 =HLOOKUP (B1, $F$1:$H$2, 2, FALSE)。

步骤 3：结果将显示为 78，这是 Kajol 的印地语分数。

上面的工作表将显示结果为 78，这是 Kajol 的印地语分数。使用填充句柄，数据输入到第 6 行。像往常一样，在公式中，B1 称为查找值，$F$1:$H$2 称为单元格范围或数组范围，2 表示行索引号，FALSE 表示表格中的精确匹配。

示例 5.4： 从给定数据中，使用 HLOOKUP 函数查找氢的熔点。

要查找氢的熔点，应遵循的步骤是：

步骤 1：在工作表中输入数据，如下所示：

步骤 2：选择一个新单元格，即 C7，并输入公式 =HLOOKUP (B1, A1:F4, 4, FALSE)。

步骤 3：按 Enter。HLOOKUP 函数在单元格 C7 中返回氢的熔点。

氢的熔点是 -259，显示在单元格 C7 中。公式 B1 称为 HLOOKUP，A1:F4 称为单元格范围或表格数组。4 称为行索引号。FALSE 表示表格的精确数据匹配。

示例 5.5： 查找原子质量小于或等于 2 的元素的熔点。

要查找指定数据，应遵循的步骤是：

步骤 1：这里，数据类似于示例 5.4 中的数据。

步骤 2：选择一个新单元格并输入公式 =HLOOKUP (2, B3:F3, 2, TRUE)。

步骤 3：按 Enter。结果将显示在选定单元格中，即原子质量小于或等于 2 的数据的熔点。

氢的熔点显示在选定单元格中。从公式中，2 是查找值，B3:F4 是表格范围或数组。2 表示行索引号。TRUE 表示适当的值。

总结

上面的教程解释了在 Excel 中创建动态命名范围的各种函数和公式。