在Excel中製作下拉列表的3種方法

07-05

下拉列表在Excel中的用途十分廣泛。在Excel中製作下拉列表可以通過數據有效性、使用窗體控制項和VBA控制項工具箱中的組合框來製作。下面我們用一個具體的例子來進行說明(在文章結尾處可下載xls格式的示例文件)。假如我們每個月都有一個工資表，其中每個員工的工資按照其出勤天數每個月都不相同。

　　我們需要製作一個「個人工資表」來查看每個人每個月的工資情況，這時就可以製作一個包含員工姓名下拉列表，在其中進行選擇來查看指定員工每個月的工資情況。

　　方法一：使用數據有效性

　　通過數據有效性可以在單元格中提供一個下拉箭頭，單擊下拉箭頭會彈出下拉列表。因為員工姓名都在每月的工資表中，而「個人工資表」中沒有這些人員姓名，所以我們必需先定義名稱，以便在「個人工資表」中設置數據有效性時進行引用。

　　定義名稱的方法是單擊菜單「插入→名稱」，在定義名稱對話框中進行定義，這裡將「1月工資」表中的姓名區域B3:B14定義為「姓名」，如圖。

　　1.假如下拉列表放在「個人工資表」的C1單元格，選擇C1單元格，然後單擊菜單「數據→有效性」，選擇「設置」選項卡，在「有效性條件」區域中「允許」下方的下拉列表中選擇「序列」。

　　2.在「來源」下方的文本框中輸入「=姓名」。單擊「確定」。

三聯推薦：點擊免費下載最新版WPS辦公軟體》》查看更多辦公軟體教程

　　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.在菜單欄上右擊，在彈出的菜單中選擇「窗體」，將彈出「窗體」浮動工具欄，單擊「組合框」控制項。

2.將游標放到表格中，這時游標變成細黑十字形，在表格中畫一個組合框。

　　3.右擊組合框，在彈出的菜單中選擇「設置控制項格式」。

　　在「設置控制項格式」對話框中選擇「控制」選項卡，設置「數據源」區域為「"1月工資"!$B$3:$B$14」(或「姓名」)，設置「單元格鏈接」為$J$1，如圖。

　　單擊「確定」回到表格中，按ESC鍵或在任一單元格單擊一下取消組合框的編輯狀態。這樣設置以後，我們在下拉列表中選擇一個姓名後，在J1單元格中將出現該姓名在姓名列表中的相對位置。例如我們選擇第3個姓名「王霞」，J1單元格中返回數值3。

　　4.在數據區C3:H14中用VLOOKUP函數對工資數據進行關聯，利用工資表中的序號數字返回工資數據，方法同上。

　　方法三：用VBA控制項工具箱中的組合框控制項。

　　1.在菜單欄上右擊，在彈出的菜單中選擇「控制項工具箱」，將彈出「控制項工具箱」浮動工具欄，單擊「組合框」控制項。

　　2.將游標放到表格中，這時游標變成細黑十字形，在表格中畫一個組合框。

　　3.右擊組合框，在彈出的菜單中選擇「屬性」。

　　4.在「屬性」窗口中，將ListFillRange屬性設置為「"1月工資"!B3:B14」。關閉「屬性」窗口。

　　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，而不是工作表名稱。這樣，每次打開工作簿時，姓名列表會自動添加到組合框中。