Excel电子表格通常包含单元格下拉列表，以简化和/或标准化数据输入。这些下拉列表是使用数据验证功能创建的，以指定允许的条目列表。
要设置一个简单的下拉列表，请选择要输入数据的单元格，然后单击Data Validation（在Data选项卡上），选择Data Validation，选择List（在Allow : 下），然后输入列表项（用逗号分隔) 在Source : 字段中（参见图 1）。
在这种类型的基本下拉列表中，允许的条目列表在数据验证本身中指定；因此，要对列表进行更改，用户必须打开并编辑数据验证。但是，对于没有经验的用户，或者在选择列表很长的情况下，这可能会很困难。
另一种选择是将列表放在电子表格中的命名范围中，然后在数据验证的(named range within the spreadsheet)Source : 字段中指定该范围名称（以等号开头） （如图 2(Figure 2)所示）。
第二种方法可以更轻松地编辑列表中的选项，但添加或删除项目可能会出现问题。由于命名范围（在我们的示例中为FruitChoices）指的是固定范围的单元格（如图所示的 $H$3:$H$10），如果将更多选项添加到单元格H11或以下，它们将不会显示在下拉列表中（因为这些单元格不属于FruitChoices范围）。
同样，例如，如果梨(Pears)和草莓(Strawberries)条目被删除，它们将不再出现在下拉列表中，而是下拉列表将包含两个“空”选项，因为下拉列表仍然引用整个 FruitChoices 范围，包括空单元格 H9 和H10。
由于这些原因，当使用正常的命名范围作为下拉列表源时，如果从列表中添加或删除条目，则必须编辑命名范围本身以包含更多或更少的单元格。
解决此问题的方法是使用动态(dynamic)范围名称作为下拉选项的来源。动态范围名称是在添加或删除条目时自动扩展（或收缩）以完全匹配数据块大小的名称。为此，您使用公式(formula)而不是固定范围的单元格地址来定义命名范围。
如何在Excel中设置(Excel)动态范围(Dynamic Range)
正常（静态）范围名称是指指定的单元格范围（在我们的示例中为 $H$3:$H$10，见下文）：
但是动态范围是使用公式定义的（见下文，取自使用动态范围名称的单独电子表格）：
在我们开始之前，请确保您下载了我们的Excel 示例文件 （排序宏已被禁用）。
让我们详细研究一下这个公式。Fruits 的选项位于标题 ( FRUITS(FRUITS) )正下方的单元格块中。该标题也被分配了一个名称：FruitsHeading：
用于定义水果(Fruits)选择的动态范围的整个公式是：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeading指的是列表中第一个条目上方一行的标题。数字 20（在公式中使用了两次）是列表的最大大小（行数）（可以根据需要进行调整）。
请注意，在此示例中，列表中只有 8 个条目，但在这些下方也有空单元格，可以添加其他条目。数字 20 是指可以进行条目的整个块，而不是实际的条目数。
现在让我们将公式分解为多个部分（对每个部分进行颜色编码），以了解它是如何工作的：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
“最里面”的部分是OFFSET(FruitsHeading,1,0,20,1)。这引用了可以输入选择的 20 个单元格块（在FruitsHeading单元格下方）。(FruitsHeading)这个OFFSET函数基本上说：从FruitsHeading单元格开始，向下 1 行和 0 列以上，然后选择一个 20 行长和 1 列宽的区域。这给了我们输入水果(Fruits)选择的 20 行块。
公式的下一部分是ISBLANK函数：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
这里，OFFSET函数（上面解释过）已经被替换为“the above”（为了让事情更容易阅读）。但是ISBLANK函数在(ISBLANK)OFFSET函数定义的 20 行单元格范围内运行。
ISBLANK然后创建一组 20 个TRUE和FALSE值，指示(FALSE)OFFSET函数引用的 20 行范围内的每个单独的单元格是否为空白（空）。在此示例中，集合中的前 8 个值将为FALSE，因为前 8 个单元格不为空，而后 12 个值将为TRUE。
公式的下一部分是INDEX函数：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
同样，“上述”是指上述ISBLANK和OFFSET函数。INDEX函数返回一个数组，其中包含由 ISBLANK 函数创建的 20 个TRUE /(ISBLANK) FALSETRUE / FALSE。
INDEX通常用于通过指定特定的行和列（在该块内）从数据块中选择特定值（或值范围）。但是将行和列输入设置为零（如此处所做的）会导致INDEX返回一个包含整个数据块的数组。
公式的下一部分是MATCH函数：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCH函数返回第一个TRUE值(TRUE)在INDEX函数返回的数组中的位置。由于列表中的前 8个条目不为空，因此数组中的前 8 个值将为FALSE，第九个值为TRUE（因为范围中的第 9 行为空）。
因此MATCH函数将返回9的值。然而，在这种情况下，我们真的想知道列表中有多少条目，所以公式从MATCH值中减去 1（它给出了最后一个条目的位置）。所以最终，MATCH ( TRUE , the above,0)-1 返回值8。
公式的下一部分是IFERROR函数：
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
如果指定的第一个值导致错误，IFERROR 函数将返回一个替代值。(IFERROR)包含此函数是因为，如果整个单元格块（所有 20 行）都填充了条目，则MATCH函数将返回错误。
这是因为我们告诉MATCH函数查找第一个TRUE值（在ISBLANK函数的值数组中），但如果没有(NONE)一个单元格为空，则整个数组将填充FALSE值。如果MATCH在它正在搜索的数组中找不到目标值 ( TRUE )，则返回错误。
因此，如果整个列表已满（因此MATCH返回错误），IFERROR函数将改为返回值 20（知道列表中必须有 20 个条目）。
最后，OFFSET(FruitsHeading,1,0,the above,1)返回我们实际寻找的范围：从FruitsHeading单元格开始，向下 1 行和 0 列以上，然后选择一个区域，该区域的行数为列表中有条目（和 1 列宽）。因此，整个公式将一起返回仅包含实际条目的范围（直到第一个空单元格）。
使用此公式定义作为下拉列表来源的范围意味着您可以自由编辑列表（添加或删除条目，只要剩余条目从顶部单元格开始并且是连续的）并且下拉列表将始终反映当前列表（参见图 6(Figure 6)）。
此处使用的示例文件（动态列表）已包含在内，可从该网站下载。但是，宏不起作用，因为WordPress不喜欢其中包含宏的Excel书籍。
作为指定列表块中行数的替代方法，可以为列表块分配其自己的范围名称，然后可以在修改后的公式中使用该名称。在示例文件中，第二个列表 ( Names ) 使用此方法。在这里，整个列表块（在“NAMES”标题下，示例文件中的 40 行）被分配了NameBlock的范围名称。定义NamesList(NamesList)的替代公式是：
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
其中NamesBlock替换OFFSET ( FruitsHeading,1,0,20,1 )，ROWS(NamesBlock)替换前面公式中的 20（行数）。
因此，对于可以轻松编辑的下拉列表（包括可能没有经验的其他用户），请尝试使用动态范围名称！请注意，尽管本文主要关注下拉列表，但动态范围名称可用于您需要引用大小不同的范围或列表的任何地方。享受！
Use Dynamic Range Names in Excel for Flexible Dropdowns
Exсel spreadsheеts often include cell dropdowns to simplify and/or standardize data entry. These droрdownѕ are created uѕing the data validation feature to spеcify a list of allowable entrieѕ.
To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).
In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation. This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.
Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).
This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic. Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).
Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.
For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.
A solution to this problem is to use a dynamic range name as the source for the dropdown choices. A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed. To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.
How to Setup a Dynamic Range in Excel
A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):
But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):
Before we get started, make sure you download our Excel example file (sort macros have been disabled).
Let’s examine this formula in detail. The choices for Fruits are in a block of cells directly below a heading (FRUITS). That heading is also assigned a name: FruitsHeading:
The entire formula used to define the dynamic range for the Fruits choices is:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeading refers to the heading that is one row above the first entry in the list. The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).
Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added. The number 20 refers to the entire block where entries can be made, not to the actual number of entries.
Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1). This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered. This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide. So that gives us the 20-row block where the Fruits choices are entered.
The next piece of the formula is the ISBLANK function:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read). But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.
ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not. In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.
The next piece of the formula is the INDEX function:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Again, “the above” refers to the ISBLANK and OFFSET functions described above. The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.
INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block). But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.
The next piece of the formula is the MATCH function:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function. Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9th row in the range is empty).
So the MATCH function will return the value of 9. In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry). So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.
The next piece of the formula is the IFERROR function:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
The IFERROR function returns an alternate value, if the first value specified results in an error. This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.
This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values. If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.
So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).
Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide). So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).
Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).
The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.
As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula. In the example file, a second list (Names) uses this method. Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock. The alternate formula for defining the NamesList is then:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.
So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names! And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!
