☰

如何在 Excel 2010 中为不断变化的值的列创建下拉列表？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
如何在 Excel 2010 中为不断变化的值的列创建下拉列表？

我有一个 Excel 电子表格，其中有一列表示年份，其标题为：

Years
1993
1993
1994
1994
1994
...
2011
2011

年份有重复的值，并且随着时间的推移会添加额外的行。

我有另一个单元格需要显示年份的下拉列表，但仅显示唯一年份。我尝试使用 Excel 2011 中的数据验证功能，但存在 2 个问题：

  1. 它显示重复的年份。
  2. 我告诉它使用整个列，并且它在下拉列表中包含空单元格。

如何获取仅显示唯一值的年份下拉列表，同时在添加其他行时自动更新？

编辑：更多信息。下拉列表用于单独的工作表中，以显示计算数据，就像 Access 表单一样。用户可以选择年份范围，数据将相应更新。原始工作表只是所有数据的列表。

答案1

对于这种验证，我使用 VBA + 一个肮脏的技巧：

首先，使用 Alt+F11 进入 VBA 编辑器然后，我将我的“动态列表验证代码”(tm) :) 放入相应的工作表中。

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

此代码使用在数据->验证->错误消息->标题中输入的公式生成的列表更新单元格验证列表。这样，每个具有列表验证的单元格都可以有自己的公式。

然后，我添加一个模块（插入->模块），然后将此代码放入新模块中：

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

此函数返回范围内所有没有空白或重复的单元格。然后，在每个具有列表验证的单元格中，我在数据验证的错误消息标题中添加 GenDynList(range)。

答案2

很乱。没有内置的自动更新方法。创建一个单独的列表，列出您可能感兴趣的所有年份，而不是试图将其限制在您的数据集中，这样会更简单。

答案3

从另一张表上的数据添加数据透视表。使用年份作为行，表格的其余部分无关紧要。根据需要对行进行排序和过滤（例如，明确过滤“[空白]”）。将行标签所在的单元格设为数据验证的目标。

使用扩展命名范围来始终使用所有新标签： http://www.ozgrid.com/Excel/DynamicRanges.htm

对国家/地区、州等其他列重复此操作。如果您基于同一数据范围创建所有数据透视表，则当您刷新其中任何一个时，它们将一起刷新。您的流程现在将是：添加新数据、刷新数据透视表、使用更新的数据验证。

进一步思考：使用表格作为源数据将使更新数据透视表变得容易，因为它们将始终使用整个表格。或者，使用扩展命名范围作为数据源，以避免添加更多行时出现问题。

请注意，如果您通过定义命名范围来执行此操作，则可以使用来自不同工作表的范围作为数据验证的来源。您不能使用明确引用另一张工作表的普通范围。

相关内容