excel自动命名_动态Excel命名列表自动增长

excel自动命名

You can quickly create a named range in Excel, but it doesn't automatically expand to include new items that are added at the end of the list. However, a dynamic Excel named list grows automatically, so here's how to create that type of list.

您可以在Excel中快速创建一个命名范围 ，但不会自动扩展以包括添加在列表末尾的新项目。 但是，动态的Excel命名列表会自动增长，因此，这是创建这种类型的列表的方法。

创建动态命名范围 (Create a Dynamic Named Range)

If you plan to add new items to a list, create a dynamic named range, by using an OFFSET formula. In this example, the formula entered in the Refers to box is:

如果您打算将新项目添加到列表中，请使用OFFSET公式创建一个动态的命名范围 。 在本示例中，在“引用”框中输入的公式为：

=OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,2)

(！价格$ B $ 1，1，0，COUNTA(价格$ B！：$ B) - 1，2)= OFFSET

• Using cell B1 as the reference cell, the list will start 1 row down, and 0 columns to the right.

  使用单元格B1作为参考单元格，列表将从下一行1行开始，向右0列。

• It counts all the items entered in column B, and subtracts 1, because the list won't include the heading in B1.

  它计算在B列中输入的所有项目，并减去1 ，因为该列表将不包括B1中的标题。

• The list will be 2 columns wide.

  该列表将是2列宽。

Then, as new items are added to the list, the dynamic named range will automatically expand to include them.

然后，随着新项目添加到列表中，动态命名范围将自动扩展以包括它们。

视频：动态Excel命名列表 (Video: Dynamic Excel Named List)

To see the steps for creating a quick named range and a dynamic named ranged, you can watch this video tutorial.

要查看创建快速命名范围和动态命名范围的步骤，您可以观看此视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2010/04/26/dynamic-excel-named-list-grows-automatically/

excel自动命名