星期五的Excel函数：动态范围的INDEX

Last Friday, there was an HLOOKUP example, and it used a dynamic lookup range -- as rates were added to the lookup table, it automatically expanded to include them.

上周五，有一个HLOOKUP示例 ，它使用了动态查找范围-将费率添加到查找表后，它会自动扩展为包括它们。

Today, we'll take a closer look at that dynamic range, and see how the INDEX function is used to set the last cell in the range.

今天，我们将仔细研究该动态范围，并了解如何使用INDEX函数设置该范围中的最后一个单元格。

计数细胞 (Count the Cells)

To find the last column for the INDEX function, you can use the COUNT function to count the rates that are entered in row 2.

要查找INDEX函数的最后一列，可以使用COUNT函数计算在第2行中输入的汇率。

=COUNT($2:$2)

= COUNT($ 2：$ 2)

创建单元格引用 (Create a Cell Reference)

The INDEX function can return a cell reference, based on a row number and column number in a reference.

INDEX函数可以根据引用中的行号和列号返回单元格引用。

INDEX(reference,row_num,column_num,area_num)

INDEX(reference ，row_num， column_num ，area_num )

Using row 2 as the reference, the COUNT function can provide the column number. There is only 1 row in the reference, so 1 is used as the row number:

使用第2行作为参考，COUNT函数可以提供列号。 参考中只有1行，因此将1用作行号：

=INDEX($2:$2,1,COUNT($2:$2))

= INDEX($ 2：$ 2,1， COUNT($ 2：$ 2) )

This formula creates a reference to cell D2, and its value is shown in cell B7.

此公式创建对单元格D2的引用，其值显示在单元格B7中。

创建动态范围 (Create the Dynamic Range)

The same INDEX formula can be used in a defined name, to create a dynamic range. The range starts in cell A1 on the Rates sheet, and ends in the cell reference created by the INDEX function.

可以在定义的名称中使用相同的INDEX公式来创建动态范围。 范围开始于“比率”表上的单元格A1，结束于由INDEX函数创建的单元格引用。

=Rates!$A$1:INDEX(Rates!$2:$2,1,COUNT(Rates!$2:$2))

= Rates！$ A $ 1： INDEX(Rates！$ 2：$ 2,1，COUNT(Rates！$ 2：$ 2))

If a rate is added or removed in row 2, the COUNT function result will change, and the INDEX function will return a different ending cell for the dynamic range.

如果在第2行中添加或删除了汇率，则COUNT函数的结果将更改，并且INDEX函数将为动态范围返回不同的结束单元格。

下载INDEX动态范围文件 (Download the INDEX Dynamic Range File)

To see the defined name and the INDEX examples, you can download the INDEX dynamic range sample file. ______________

要查看定义的名称和INDEX示例，可以下载INDEX动态范围示例文件 。 ______________

翻译自: https://contexturesblog.com/archives/2011/02/25/excel-function-friday-index-for-dynamic-range/