在Excel中制作下拉列表的三种方法

发布时间:2021-08-31 Excel技巧

  下拉列表在Excel中的用途十分广泛。在Excel中制作下拉列表可以通过数据有效性、使用窗体控件和VBA控件工具箱中的组合框来制作。

  下面用一个具体的例子来进行说明(在文章结尾处可下载xls格式的示例文件)。假如每个月都有一个工资表,其中每个员工的工资按照其出勤天数每个月都不相同。

  

在Excel中制作下拉列表的三种方法

 

  需要制作一个个人工资表来查看每个人每个月的工资情况,这时就可以制作一个包含员工姓名下拉列表,在其中进行选择来查看指定员工每个月的工资情况。

  

在Excel中制作下拉列表的三种方法

 

  本文图文介绍了在Excel中制作下拉列表的三种方法。

  方法一:使用数据有效性

  通过数据有效性可以在单元格中提供一个下拉箭头,单击下拉箭头会弹出下拉列表。因为员工姓名都在每月的工资表中,而个人工资表中没有这些人员姓名,所以必需先定义名称,以便在个人工资表中设置数据有效性时进行引用

  定义名称的方法是单击菜单插入名称,在定义名称对话框中进行定义,这里将1月工资表中的姓名区域B3:B14定义为姓名,如图。

  

在Excel中制作下拉列表的三种方法

 

  1、假如下拉列表放在个人工资表的C1单元格,选择C1单元格,然后单击菜单数据有效性,选择设置选项卡,在有效性条件区域中允许下方的下拉列表中选择序列。

  2、在来源下方的文本框中输入=姓名。单击确定。

  

在Excel中制作下拉列表的三种方法

 

  3、在数据区C3:H14中用VLOOKUP函数对工资数据进行关联。例如第3行为1月工资,可以在C3单元格中输入公式

  =VLOOKUP($C$1,1月工资!$B$3:$H$14,2,0)

  在D3单元格中输入公式:

  =VLOOKUP($C$1,1月工资!$B$3:$H$14,3,0)

  在C4单元格中输入公式:

  =VLOOKUP($C$1,2月工资!$B$3:$H$14,2,0)

  其余单元格依此类推。这样,只要在单击C1单元格右侧的下拉箭头选择员工姓名就可以查看其所有月份的工资情况。

  方法二:用窗体控件

  1、在菜单栏上右击,在弹出的菜单中选择窗体,将弹出窗体浮动工具栏,单击组合框控件。

  

在Excel中制作下拉列表的三种方法

 

  2、将光标放到表格中,这时光标变成细黑十字形,在表格中画一个组合框。

  3、右击组合框,在弹出的菜单中选择设置控件格式。

  

在Excel中制作下拉列表的三种方法

 

  在设置控件格式对话框中选择控制选项卡,设置数据源区域为1月工资!$B$3:$B$14(或姓名),设置单元格链接为$J$1,如图。

  

在Excel中制作下拉列表的三种方法

 

  单击确定回到表格中,按ESC键或在任一单元格单击一下取消组合框的编辑状态。这样设置以后,我们在下拉列表中选择一个姓名后,在J1单元格中将出现该姓名在姓名列表中的相对位置。例如选择第3个姓名王霞,J1单元格中返回数值3。

  4、在数据区C3:H14中用VLOOKUP函数对工资数据进行关联,利用工资表中的序号数字返回工资数据,方法同上。

  方法三:用VBA控件工具箱中的组合框控件

  1、在菜单栏上右击,在弹出的菜单中选择控件工具箱,将弹出控件工具箱浮动工具栏,单击组合框控件。

  

在Excel中制作下拉列表的三种方法

 

  2、将光标放到表格中,这时光标变成细黑十字形,在表格中画一个组合框。

  3、右击组合框,在弹出的菜单中选择属性。

  

在Excel中制作下拉列表的三种方法

 

  4、在属性窗口中,将ListFillRange属性设置为1月工资!B3:B14。关闭属性窗口 。

  

在Excel中制作下拉列表的三种方法

 

  5、按Alt+F11,打开VBA编辑器,在个人工资表中添加 下列代码:

  Private Sub ComboBox1_Change()

  Range(c1) = ComboBox1.Value

  End Sub

  6、在数据区C3:H14中用VLOOKUP函数对工资数据进行关联,方法同上。

  另外,也可以使用VBA代码将员工名单添加到组合框中,有两种方法供选择:

  在VBA编辑器的工程窗口中,双击ThisWorkBook,在右侧的代码窗口中输入下列代码:

  1、使用 AddItem 方法添加项目:

  Private Sub Workbook_Open()

  Dim vName As Variant

  Dim i As Integer

  创建列表

  vName = Array(张梅, 黄中, 王霞, 应军军, 郑枭, 刘梅波, 李飞, 吴燕)

  使用 AddItem 方法

  For i = LBound(vName) To UBound(vName)

  Sheet3.ComboBox1.AddItem vName(i)

  Next i

  End Sub

  2、使用 List 属性添加项目:

  Private Sub Workbook_Open()

  Dim vName As Variant

  Dim i As Integer

  创建列表

  vName = Array(张梅, 黄中, 王霞, 应军军, 郑枭, 刘梅波, 李飞, 吴燕)

  使用 List 属性

  Sheet3.ComboBox1.List = WorksheetFunction.Transpose(vName)

  End Sub

 

  其中Sheet3是VBA编辑器工程窗口中与个人工资表名称对应的工作表,这里直接引用的是Sheet3,而不是工作表名称。这样,每次打开工作簿时,姓名列表会自动添加到组合框中。