實例給出Excel級聯(lián)菜單

1、實例給出Excel級聯(lián)菜單（多個下拉選項）的實現(xiàn)大家知道，在Excel中可以通過設(shè)置數(shù)據(jù)的有效性來實現(xiàn)類似于網(wǎng)頁表單中的下拉菜單功能，這樣可以大大地提高數(shù)據(jù)的錄入速度和可靠性。但采用此方法要求下拉菜單中的選項不能過多，否則會影響錄入的速度。如果這些選項內(nèi)容之間可進(jìn)行分類、分組，那我們就可以用多個下拉選項（即級聯(lián)菜單）來解決此類問題。下面我們就從一個出發(fā)，給出在Excel中實現(xiàn)級聯(lián)菜單的兩種方法，希望能給使用Excel進(jìn)行辦公的朋友提供幫助。一、自定義數(shù)據(jù)的有效性這里有兩個工作表，其中Sheet1用來采集客戶資料，并將其名稱改為“客戶數(shù)據(jù)采集”，在Sheet2的A列中保存了所有的省份， B列中的

2、數(shù)據(jù)為各省份所屬的縣市并將工作表名稱改為“地區(qū)”（假設(shè)這里設(shè)置了三個省份，即江蘇省、浙江省和安徽省）。1 定義單元格區(qū)域名稱選中“地區(qū)”表中江蘇省所屬縣市的單元格區(qū)域B2B14，依次單擊“插入”菜單中的“名稱”，選擇“定義”，彈出“定義名稱”對話框，在“在當(dāng)前工作簿中的名稱”框中輸入所定義的名稱“江蘇”，單擊“確定”按鈕。類似地，我們可以將浙江省和安徽省所屬縣市的單元格區(qū)域名稱分別定義為“浙江”和“安徽”。2 設(shè)置數(shù)據(jù)的有效性切換到“用戶數(shù)據(jù)采集”表中，選擇要選擇輸入省份的D列，單擊“數(shù)據(jù)”菜單中的“有效性”命令，彈出設(shè)置“數(shù)據(jù)有效性”對話框，在有效性條件的“允許”列表中選擇“序列”，在“來源

3、”框中輸入“江蘇,浙江,安徽”（分隔符為英文狀態(tài)下的逗號），單擊“確定”按鈕。接下來選中“所屬縣市”所在的E列，再次打開“數(shù)據(jù)有效性”對話框，在有效性條件的“允許”列表中選擇“序列”，在“來源”框中輸入公式“=INDIRECT(D2)”，單擊“確定”按鈕Cico提示：在關(guān)閉設(shè)置E列數(shù)據(jù)有效性對話框時，如系統(tǒng)彈出警告提示“源目前包含錯誤，是否繼續(xù)？”，只需確認(rèn)就可以了。如果指定的D2單元格中已經(jīng)選擇了一個省份再設(shè)置E列數(shù)據(jù)的有效性就不會出現(xiàn)此提示了。3 數(shù)據(jù)錄入錄入客戶資料時，首先選擇客戶所在的省份，然后再打開其右側(cè)“所屬縣市”所在的單元格時，我們就可以從下拉菜單中找到我們所需的選項了。二、用組

4、合框工具實現(xiàn)該方法是借助控件工具箱和VBA代碼來實現(xiàn)的。1設(shè)計組合框在工作表Sheet1中我們借助“控件工具箱”設(shè)計了錄入界面，將工作表名稱改為“客戶數(shù)據(jù)采集”，在采集客戶的所屬地區(qū)時我們設(shè)計了兩個組合框，第一個組合框ComboBox1用來存放省份，第二個組合ComboBox2用來存放對應(yīng)于第一個組合框的下屬的縣市。Sheet2（“地區(qū)”表）仍保留和原來一樣的內(nèi)容。2編寫組合框事件代碼雙擊“客戶數(shù)據(jù)采集”表中的組合框ComboBox1，進(jìn)入VBE編輯器，在代碼窗口中輸入其GotFocus事件代碼：Private Sub ComboBox1_GotFocus( )清除組合框中的數(shù)值ComboBo

5、x1.ClearComboBox2.Clear讀出地區(qū)表A列中的不同省份并將其添加到組合框1中For i = 2 To Sheet2.a1.End(xlDown).Rowtarget = Sheet2.Cells(i, 1)利用設(shè)置標(biāo)志位要判斷數(shù)據(jù)是否重復(fù)flag = 0For j = 0 To ComboBox1.ListCount - 1If ComboBox1.List(j) = target Then flag = 1NextIf flag = 0 ThenComboBox1.AddItem targetEnd IfNextEnd Sub返回Excel工作表中，雙擊組合框ComboBo

6、x2，在代碼窗口中輸入其GotFocus事件代碼：Private Sub ComboBox2_GotFocus( )ComboBox2.Clear讀出地區(qū)表B列中屬于組合框1的縣市并將其添加到組合框2中For i = 2 To Sheet2.a1.End(xlDown).Rowtarget = Sheet2.Cells(i, 1)If target = ComboBox1.Value ThenComboBox2.AddItem Sheet2.Cells(i, 2)End IfNextEnd Sub3 運(yùn)行效果返回“客戶數(shù)據(jù)采集”表，單擊控件工具箱中的“退出設(shè)計模式”按鈕即可進(jìn)入運(yùn)行模式。在組合

7、框ComboBox1中選擇一個省份，此時再單擊組合框ComboBox2的下拉按鈕時，我們就可以方便地選擇該省份所屬的縣市了。Cico提示：除了在工作表中實現(xiàn)客戶的數(shù)據(jù)采集外，我們還可以通過窗體來實現(xiàn)此操作的錄入界面。如果我們需要更改組合框中的列表選項，只需在“地區(qū)”表（即Sheet2）中進(jìn)行相應(yīng)的更改就可以了。兩種方法的比較：相比之下，用本文提供的第一種方法比較簡單，更適合于初學(xué)者和一般用戶。而第二方法則更適合利用Excel進(jìn)行二次開發(fā)的讀者朋友，以便于設(shè)計人性化的數(shù)據(jù)錄入界面。另外，在本例中，“地區(qū)”表中的地區(qū)數(shù)據(jù)如果需要添加或刪除，若用第一種方法實現(xiàn)，要求添加的同類數(shù)據(jù)必須要連續(xù)且需要重新

8、定義單元格區(qū)域。如果用方法二實現(xiàn)，各省份和所屬縣市添加時可以連續(xù)也可以不連續(xù)，就這一點(diǎn)而言，比第一種方法要便捷多了。EXCEL級聯(lián)下拉框 167在輸入一些多級項目時，如果輸入前一級內(nèi)容后，能夠自動出現(xiàn)次一級內(nèi)容，就會減少出錯的機(jī)會并提高工作效率。例如我們常用的費(fèi)用科目有“管理費(fèi)用”、“經(jīng)營費(fèi)用”、“財務(wù)費(fèi)用”，這些屬于一級科目，在它們的下面還有二級科目，比如“財務(wù)費(fèi)用”的二級科目就包括“利息支出”、“手續(xù)費(fèi)”等。如果我們先輸入“財務(wù)費(fèi)用”后，希望有一個下拉框能列出只屬于它的二級內(nèi)容以供選擇，就會方便很多，這就要用到“級聯(lián)下拉框”了。下面介紹制做方法：第一步：準(zhǔn)備工作表先在一個工作簿中做兩個工作

9、表，一個名為“列表”，另一個名為“明細(xì)單”。（提示：這只是一個例子，工作表的名子可以自取，如果放到一個工作表中也可以的）下面要定義幾個“名稱”，以便在下拉框中調(diào)用。第二步：定義名稱先定義“一級科目”的名稱，選中“列表”中的A1:A4區(qū)域，按工具欄上的“插入名稱定義”.這時彈出一個對話框，如果與下圖的設(shè)置一樣，直接點(diǎn)“確定”按鈕即可。這樣一級科目的名稱就定義好了，以后只要引用這個名稱，EXCEL就會返回實際的數(shù)據(jù)區(qū)域。再根據(jù)一級科目中的項目，參照上面方法，分別定義它們的二級名稱。定義管理費(fèi)用二級名稱，選中列表的B2:F2區(qū)域，按“插入名稱定義”定義經(jīng)營費(fèi)用二級名稱，選中“列表”的B3:E3區(qū)域，

10、按“插入名稱定義”定義財務(wù)費(fèi)用二級名稱，選中“列表”的B4:C4區(qū)域，按“插入名稱定義”這樣所有名稱都定義好了，下面就可以用“數(shù)據(jù)有效性”來添加下拉框了。第三步：添加數(shù)據(jù)有效性打開“明細(xì)單”工作表，選中B5:B7區(qū)域，按工具欄上的“數(shù)據(jù)有效性”這時會彈出“數(shù)據(jù)有效性”對話框，在“設(shè)置”選項卡中按下圖設(shè)置。其中“來源”框中就是我們在上面定義的“一級科目”名稱。（注意：“來源”框中的等號一定要在英文狀態(tài)下輸入）點(diǎn)“確定”后，一級科目的有效性下拉框設(shè)置完畢，下面再設(shè)置二級科目的下拉框，也就是我們在文章開頭說的“級聯(lián)下拉框”。步驟類似，選中“明細(xì)單”的C5:C7區(qū)域，按“數(shù)據(jù)有效性”，并按下面圖片設(shè)置

11、。細(xì)心的朋友會發(fā)現(xiàn)，“來源”框中輸入的并不是定義的二級科目名稱，而是一個公式。公式：=INDIRECT(B5)公式含義：根據(jù)B5單元格中的內(nèi)容，決定應(yīng)用哪個二級科目名稱。這個公式中用到了INDIRECT函數(shù)，它的作用是返回由文本字符串指定的引用，其中括號中的B5就是它的引用字符串所在單元格。比如B5單元格中的內(nèi)容是“管理費(fèi)用”，公式返回的結(jié)果就是“=管理費(fèi)用”，就會在下拉框中應(yīng)用上面定義的“管理費(fèi)用”名稱；如果B5單元格中的內(nèi)容是“財務(wù)費(fèi)用”，公式返回的結(jié)果就是“=財務(wù)費(fèi)用”，就會在下拉框中應(yīng)用上面定義的“財務(wù)費(fèi)用”名稱。對話框設(shè)置完成后按“確定”按鈕，這時會彈出下面一個“錯誤”對話框：不用理

12、會，直接點(diǎn)“是”即可。第四步：使用所有的設(shè)置完成后就可以使用了，點(diǎn)擊“明細(xì)單”工作表的B5格就會出現(xiàn)一個向下的三角按鈕，點(diǎn)這個按鈕就會出現(xiàn)一個下拉框，從中可以選擇一級科目。再點(diǎn)C5格，同樣也出現(xiàn)按鈕，也有一個下拉框，這個下拉框中的內(nèi)容會根據(jù)前一個下拉框的不同而改變。Excel使用技巧：6使用Excel創(chuàng)建級聯(lián)菜單1234567方法/步驟1. 1框選省份，然后點(diǎn)“公式”-“根據(jù)所選內(nèi)容創(chuàng)建”，在新彈出的窗口選擇“首行”，然后點(diǎn)擊確定。這個時候再點(diǎn)“名稱管理器”，就可以看到以“省份”命名的組合列表。2同樣的方法，分別創(chuàng)建市列表和區(qū)/縣列表，如下圖。3創(chuàng)建完后，進(jìn)“名稱管理器”，建成的列表如下。2. 4在要建立級聯(lián)菜單的地方點(diǎn)一下鼠標(biāo)左鍵選中，然后點(diǎn)工具欄中“數(shù)據(jù)”-“數(shù)據(jù)有效性”。將“允許”選擇成“序列”，來源輸入“=省份”，然后點(diǎn)確定，這個時候看