Excel电子表格通常包含单元格下拉列表，以简化和/或标准化数据输入。这些下拉菜单是使用数据验证功能创建的，以指定允许的条目列表。
要设置简单的下拉列表，请选择要在其中输入数据的单元格，然后单击数据验证（在数据选项卡上），选择数据验证，选择列表（在允许：下），然后在中输入列表项（用逗号分隔） >Source：字段（请参见图1）。
在这种类型的基本下拉列表中，指定了允许条目的列表在数据验证本身内；因此，要更改列表，用户必须打开并编辑数据验证。但是，对于没有经验的用户或在选择列表很长的情况下，这可能很困难。
另一种选择是将列表放置在电子表格中的命名范围 中，然后指定数据验证的Source：字段中的范围名称（以等号开头）（如图2所示）。
In_content_1全部：[300x250] / dfp：[640x360]->
第二种方法可以更轻松地编辑列表中的选项，但是添加或删除项目可能会出现问题。由于命名范围（在我们的示例中为FruitChoices）指的是固定范围的单元格（如所示，$ H $ 3：$ H $ 10），如果将更多选项添加到单元格H11或以下，它们将不会显示在下拉列表中（因为这些单元格不在FruitChoices范围内。）
例如，如果删除了Pears和Strawberries条目，它们将不再出现在下拉菜单中，而是包含两个“空”选项，因为下拉列表仍引用整个FruitChoices范围，包括空单元格H9和H10。
由于这些原因，当使用常规命名范围作为下拉列表的列表源时，命名范围如果从列表中添加或删除条目，则必须对其本身进行编辑以包含更多或更少的单元格。
此问题的解决方案是使用dynamic范围名称作为下拉选项的来源。动态范围名称是一种在添加或删除条目时自动扩展（或收缩）以完全匹配数据块大小的名称。为此，您可以使用公式而不是固定范围的单元格地址来定义命名范围。
如何设置动态Excel中的范围
正常（静态）范围名称是指单元格的指定范围（在我们的示例中为$ H $ 3：$ H $ 10，请参见下文）：
但是动态范围是使用公式定义的（请参见下文，该图片取自使用动态范围名称的单独电子表格）：
在开始之前，请确保您下载我们的Excel示例文件 （排序宏已被禁用）。
让我们详细研究这个公式。水果的选择位于标题（水果）正下方的一个单元格中。该标题也被分配了一个名称：FruitsHeading：
用于定义广告的动态范围的整个公式水果的选择是：
=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（水果标题，1,0、20、1）。这引用了20个单元的块（在FruitsHeading单元下面），可以在其中输入选择。此OFFSET函数基本上说：从FruitsHeading单元格开始，向下1行，超过0列，然后选择20行长和1列宽的区域。这样就给了我们20行的代码段，在其中输入了Fruits选项。
公式的下一部分是ISBLANK函数：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
此处，OFFSET功能（如上所述）已被替换为“ the above”（使内容更易于阅读）。但是ISBLANK函数在OFFSET函数定义的20行单元格范围内运行。
ISBLANK然后创建一组20个TRUE和FALSE值，指示20- OFFSET函数引用的行范围是否为空白（空）。在此示例中，集合中的前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 / FALSE值的数组。
INDEX通常用于从中选择某个值（或值范围）通过指定特定的行和列（在该块内）来构成数据块。但是将行和列输入设置为零（如此处所述）会使INDEX返回包含整个数据块的数组。
下一个公式是MATCH函数：
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCH函数返回由INDEX函数返回的数组中第一个TRUE值的位置。由于列表中的前8个条目不为空，因此数组中的前8个值将为FALSE，第九个值将为TRUE（因为范围中的第9 行为空）。
因此，MATCH函数将返回9的值。但是，在这种情况下，我们真的想知道列表中有多少个条目，因此公式从MATCH值中减去1（给出最后一个条目的位置）。因此，最终，MATCH（TRUE，rattle，0）-1返回8的值。
公式的下一部分是IFERROR函数：
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
如果指定的第一个值导致错误，则IFERROR函数将返回备用值。之所以会包含此函数，是因为如果整个单元格块（全部20行）都填充有条目，则MATCH函数将返回错误。
这是因为我们要告诉MATCH函数查找第一个TRUE值（在ISBLANK函数的值数组中），但是如果没有一个单元格为空，则整个数组将填充为FALSE值。如果MATCH在搜索的数组中找不到目标值（TRUE），则会返回错误。
因此，如果整个列表已满（因此MATCH返回错误），则IFERROR函数将而是返回值20（知道列表中必须有20个条目）。
最后，OFFSET（FruitsHeading，1,0，thera，1）返回我们实际上正在寻找的范围：从FruitsHeading单元格开始，向下移1行并超过0列，然后选择一个多行的区域，只要列表中有条目（宽1列）即可。因此，整个公式将一起返回仅包含实际条目的范围（向下到第一个空单元格）。
使用此公式定义作为下拉列表来源的范围，意味着您可以自由编辑列表（添加或删除条目，只要其余条目从顶部单元格开始并且是连续的），下拉列表将始终反映当前列表（请参见图6）。
已包含此处使用的示例文件（动态列表） ，并可从本网站下载。但是，宏不起作用，因为WordPress不喜欢其中包含宏的Excel书籍。
除了指定列表块中的行数之外，还可以为其分配列表块自己的范围名称，然后可以在修改的公式中使用。在示例文件中，第二个列表（名称）使用此方法。在此，为整个列表块（在“ NAMES”标题下，示例文件中的40行）分配了NameBlock的范围名称。定义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（行数）。
因此，对于易于编辑的下拉列表（包括可能没有经验的其他用户），尝试使用动态范围名称！并请注意，尽管本文着重于下拉列表，但动态范围名称可在需要引用大小可能有所不同的范围或列表的任何地方使用。享受！