资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

如何在 Excel 中自动更新下拉列表：3 种方法

创作时间:

作者:

@小白创作中心

如何在 Excel 中自动更新下拉列表：3 种方法

引用

来源

https://cn.windows-office.net/?p=36572

在Excel中创建下拉列表可以提高数据输入的效率和准确性。本文将介绍三种自动更新下拉列表的方法，帮助用户在添加新选项时无需手动调整列表范围。

我们有一个付款类型列表，我们要为其创建一个下拉列表。但是，要插入任何其他付款类型，我们希望通过以下 3 种方法自动更新下拉列表。

图1：示例付款类型列表

方法1 – 使用OFFSET和COUNTA函数自动更新Excel中的下拉列表

我们将使用OFFSET和COUNTA函数的组合来自动更新包含付款类型的下拉列表。

步骤：

选择单元格D5，然后转到数据选项卡>>数据验证组>>数据验证。

数据验证向导将打开。

从允许下的不同选项中选择列表，然后在来源中选择范围。


=$B$5:$B$10

这是付款选项的范围。

按确定。

您将看到一个下拉列表，如果您为另一个付款选项添加另一行，比特币，您可以看到下拉列表不会自动更新。这是一个新创建的选项，未包含在我们的列表中。

为了解决这个问题，我们将通过以下方式创建列表。

打开数据验证对话框后，从允许下的不同选项中选择列表，然后在源中键入以下公式>盒子。


=OFFSET($B$5,0,0, COUNTA(B: B)-1)

$B$5是范围的起始单元格，后面的2零表示单元格引用不会移动任何行号或列号。最后，COUNTA(B: B)-1表示范围的高度数字，即包含文本或数字的行数。

按确定。

输入数据验证公式后，我们得到以下下拉列表：

如果我们添加另一个名为比特币的付款选项，该选项将自动添加。

方法 2 – 定义范围名称并创建表以自动更新下拉列表

在Excel中创建自动更新下拉列表还有另一种方法：定义名称并使用表格。

图2：定义名称并创建表

步骤：

转到公式选项卡>>定义名称组>>定义名称。

图3：定义名称对话框

在“名称”文本框中输入名称。我们将输入“Payment_Types”并在参考中选择我们的付款范围。
单击确定。

图4：定义名称完成

创建一个表。

转到插入选项卡>>表格。

图5：插入表格

选择数据范围，然后选中我的表格有标题选项。
按确定。

我们创建了下表。

图6：创建的表格

选择单元格D5（我们想要下拉列表的位置），然后转到数据选项卡>>数据验证组>>数据验证。

图7：数据验证位置

数据验证向导将打开。

从允许下的不同选项中选择列表，然后在来源框中输入命名范围。


=Payment_Types

这是付款选项的范围。

按确定。

输入数据验证公式后，我们得到以下下拉列表。

图8：完成的下拉列表

如果我们添加另一个名为比特币的付款选项，那么该选项将自动添加。

图9：自动添加新选项

方法 3 – 应用ExcelINDIRECT 函数与表格自动更新下拉列表

在“源”框中使用间接函数并引用表名称。

图10：使用INDIRECT函数

步骤：

将数据范围转换为表格，表格名称为Table3。

图11：创建表格

打开数据验证对话框，在单元格D5中添加下拉列表。
从允许下的不同选项中选择列表，然后在源中键入以下公式。


=INDIRECT(“Table3”)

表3是支付选项的范围。

按确定。

输入数据验证公式后，我们得到以下下拉列表：

图12：完成的下拉列表

添加另一个名为比特币的付款选项，然后该选项将自动添加到我们的列表中。

图13：自动添加新选项

如何在Excel中自动填充下拉列表

在 VBA 代码的帮助下，我们将自动填充单元格D5中下拉列表中的文本。

图14：自动填充示例

步骤01：创建下拉列表、组合框

选择单元格D5，然后转到数据选项卡>>数据验证组>>数据验证。

图15：数据验证位置

数据验证向导将打开。

从允许下的不同选项中选择列表，然后在来源中选择范围。


=$B$5:$B$10

这是付款选项的范围。

按确定。

我们已经成功创建了下拉列表。

图16：完成的下拉列表

插入组合框。

转到开发人员选项卡>>插入下拉菜单>>组合框（ActiveX 控件）。

图17：插入组合框

将出现一个加号符号。

将加号符号向下并向右拖动。

图18：拖动组合框

我们创建了一个组合框；记下其名称以便在代码中使用（组合框的名称是ComboBox1）。
转到开发人员选项卡>>设计模式以停用设计模式。

图19：停用设计模式

步骤02：编写VBA代码

右键单击工作表名称，然后单击查看代码。

Visual Basic 编辑器窗口将打开以插入我们的代码。

在VBE中输入以下代码


Sub Worksheet_SelectionChange(ByVal P_val As Range)
Dim DList_box As OLEObject
Dim Ptype As String
Dim Dsht As Worksheet
Dim P_List As Variant
Set Dsht = Application.ActiveSheet
On Error Resume Next
Set DList_box = Dsht.OLEObjects("ComboBox1")
DList_box.ListFillRange = ""
DList_box.LinkedCell = ""
DList_box.Visible = False
If P_val.Validation.Type = 3 Then
P_val.Validation.InCellDropdown = False
Cancel = True
Ptype = P_val.Validation.Formula1
Ptype = Right(Ptype, Len(Ptype) - 1)
If Ptype = "" Then Exit Sub
DList_box.Visible = True
DList_box.Right = P_val.Right
DList_box.Bottom = P_val.Bottom
DList_box.Width = P_val.Width + 90
DList_box.Height = P_val.Height + 10
DList_box.ListFillRange = Ptype
If DList_box.ListFillRange = "" Then
P_List = Split(Ptype, ",")
Me.ComboBox1.List = P_List
End If
DList_box.LinkedCell = P_val.Address
DList_box.Activate
Me.ComboBox1.DropDown
End If
End Sub

代码分解

我们选择子过程名称为Worksheet_SelectionChange，因为Worksheet表示工作表，而SelectionChange表示发生该事件的事件。将运行代码。我们将P_val分类为范围。
将DList_box声明为OLEObject，Ptype声明为String，Dsht声明为Worksheet，以及P_List作为变体。
将活动工作表分配给Dsht，并将名为ComboBox1的组合框分配给DList_box。
数据验证类型的值选择3，表示下拉
Ptype变量将存储用于活动工作表中数据验证的公式。
我们修复了下拉列表框的位置并提到了大小。