如何在Excel中创建动态定义的范围

Your Excel data changes frequently, so it’s useful to create a dynamic defined range that automatically expands and contracts to the size of your data range. Let’s see how.

Excel数据经常更改，因此创建动态定义的范围会自动扩展和收缩到数据范围的大小很有用。 让我们看看如何。

By using a dynamic defined range, you will not need to manually edit the ranges of your formulas, charts, and PivotTables when data changes. This will happen automatically.

通过使用动态定义的范围，当数据更改时，您将无需手动编辑公式，图表和数据透视表的范围。 这将自动发生。

Two formulas are used to create dynamic ranges: OFFSET and INDEX. This article will focus on using the INDEX function as it is a more efficient approach. OFFSET is a volatile function and can slow down large spreadsheets.

使用两个公式创建动态范围：OFFSET和INDEX。 本文将重点介绍如何使用INDEX函数，因为它是一种更有效的方法。 偏移量是一种易失性功能，可以减慢大型电子表格的速度。

在Excel中创建动态定义范围 (Create a Dynamic Defined Range in Excel)

For our first example, we have the single-column list of data seen below.

对于第一个示例，我们在下面看到了单列数据列表。

We need this to be dynamic so that if more countries are added or removed, the range automatically updates.

我们需要保持动态状态，以便在添加或删除更多国家/地区时，范围会自动更新。

For this example, we want to avoid the header cell. As such, we want the range $A$2:$A$6, but dynamic. Do this by clicking Formulas > Define Name.

对于此示例，我们要避免标题单元格。 因此，我们需要范围$ A $ 2：$ A $ 6，但要动态。 通过单击公式>定义名称来执行此操作。

Type “countries” in the “Name” box and then enter the formula below in the “Refers to” box.

在“名称”框中键入“国家”，然后在“引用到”框中输入以下公式。

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Typing this equation into a spreadsheet cell and then copying it into the New Name box is sometimes quicker and easier.

将该方程式键入到电子表格单元格中，然后将其复制到“新名称”框中有时会更快，更容易。

这是如何运作的？ (How Does This Work?)

The first part of the formula specifies the start cell of the range (A2 in our case) and then the range operator (:) follows.

公式的第一部分指定范围的起始单元格(在本例中为A2)，然后是范围运算符(:)。

=$A$2:

Using the range operator forces the INDEX function to return a range instead of the value of a cell. The INDEX function is then used with the COUNTA function. COUNTA counts the number of non-blank cells in column A (six in our case).

使用范围运算符会强制INDEX函数返回范围而不是单元格的值。 然后将INDEX函数与COUNTA函数一起使用。 COUNTA计算A列中的非空白单元格的数量(本例中为6)。

INDEX($A:$A,COUNTA($A:$A))

This formula asks the INDEX function to return the range of the last non-blank cell in column A ($A$6).

此公式要求INDEX函数返回列A中最后一个非空白单元格的范围($ A $ 6)。

The final result is $A$2:$A$6, and because of the COUNTA function, it is dynamic, as it will find the last row. You can now use this “countries” defined name inside a Data Validation rule, formula, chart, or wherever we need to reference the names of all the countries.

最终结果是$ A $ 2：$ A $ 6，由于有了COUNTA函数，它是动态的，因为它将找到最后一行。 现在，您可以在数据验证规则，公式，图表中或需要引用所有国家/地区名称的地方使用此“国家/地区”定义的名称。

创建双向动态定义范围 (Create a Two Way Dynamic Defined Range)

The first example was only dynamic in height. However, with a slight modification and another COUNTA function, you can create a range that is dynamic by both height and width.

第一个例子只是高度是动态的。 但是，只需稍加修改和另一个COUNTA功能，您就可以创建一个根据高度和宽度而动态变化的范围。

In this example, we will be using the data shown below.

在此示例中，我们将使用下面显示的数据。

This time, we will create a dynamic defined range, which includes the headers. Click Formulas > Define Name.

这次，我们将创建一个动态定义的范围，其中包括标题。 单击公式>定义名称。

Type ‘”sales” in the “Name” box and enter the formula below in the “Refers To” box.

在“名称”框中键入“ sales”，然后在“引用到”框中输入以下公式。

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

This formula uses $A$1 as the start cell. The INDEX function then uses a range of the entire worksheet ($1:$1048576) to look in and return from.

此公式使用$ A $ 1作为起始单元格。 然后，INDEX函数使用整个工作表的范围($ 1：$ 1048576)进行查找并从中返回。

One of the COUNTA functions is used to count the non-blank rows, and another is used for the non-blank columns making it dynamic in both directions. Although this formula started from A1, you could have specified any start cell.

COUNTA函数之一用于计数非空白行，另一函数用于非空白列，使其在两个方向上都具有动态性。 尽管此公式从A1开始，但是您可以指定任何起始单元格。

You can now use this defined name (sales) in a formula or as a chart data series to make them dynamic.

现在，您可以在公式中或作为图表数据系列使用此定义的名称(销售)使它们动态化。

翻译自: https://www.howtogeek.com/462153/how-to-create-a-dynamic-defined-range-in-excel/