|
二級下拉菜單可以很好地規(guī)范數(shù)據(jù)錄入，也可以用作動態(tài)交互式圖表的控件，今天就跟大家分享三種二級下拉菜單制作方法。 01 — 普通青年 第一步：整理A:B列省市類別為D:G列格式，便于后期定義名稱。 第二步：定義首行省份序列為名稱'省份'，用于數(shù)據(jù)有效性引用。 第三步：定義城市名稱-通過首行省份名批量定義，用于數(shù)據(jù)有效性中引用。這里面用到快捷鍵F5-定位常量，可以過濾掉無數(shù)據(jù)的區(qū)域。然后'根據(jù)所選內(nèi)容創(chuàng)建'，可批量定義城市序列名稱。 第四步：分別為省市對應(yīng)單元格設(shè)置數(shù)據(jù)有效性，這里設(shè)置省份一級菜單時(shí)，有效性條件選擇序列，來源=省份（之前定義的名稱）即可。設(shè)置城市二級下拉菜單數(shù)據(jù)有效性時(shí)用到了indirect函數(shù)（關(guān)于該函數(shù)使用方法，請仔細(xì)查看F1函數(shù)幫助）。按下圖操作，一個(gè)省市二級下拉菜單就做好啦。 02 — 文藝青年 文藝青年的做法與普通青年原理上和方式上大體相同，那么是如何展示文藝范的呢？ Step1：對省份列表按照升序或者降序排列（目的是讓包含相同內(nèi)容的省份彼此相鄰） Step2：對省份的列表進(jìn)行了一點(diǎn)加工，定義了如下名稱： 在定義省份序列的名稱時(shí)，用到的高頻函數(shù)簡單解釋下：
通過定義名稱，會為數(shù)據(jù)有效性引用的序列重新設(shè)定一個(gè)省份范圍。 Step3：與普通青年相類似，在進(jìn)行省份一級菜單制作時(shí)，在D18單元格的數(shù)據(jù)有效性設(shè)置對話框中，直接輸入已經(jīng)定義好的名稱'=文藝青年省份”即可。 Step4：取消出錯(cuò)警告中'輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告'的復(fù)選框。 Step5：城市二級下拉菜單制作方法與普通青年的第四步驟一致，也是在數(shù)據(jù)有效性中借助indirect函數(shù)，'允許'設(shè)置為序列，來源=INDIRECT($D$18) 效果如下： 這種類似于聯(lián)想式下拉菜單的方法，避免了一級菜單中幾十個(gè)省份的冗長名單，不便于選擇。只需輸入'廣'，下拉菜單中便只有'廣東'和'廣西'；若輸入'湖'，則下拉菜單中則只有'湖南'和'湖北'，非常方便和智能。 注：1）對于一級菜單，需要滿足排序后可包含相同關(guān)鍵字的內(nèi)容要相鄰，這是其最大的局限性。2）如果不是省市的使用場景，而是包含相同關(guān)鍵字的內(nèi)容有多個(gè)時(shí)，這種方式優(yōu)勢會很明顯。 03 — IT青年 IT青年可以通過VBA編程，直接用省市列表借助字典功能，實(shí)現(xiàn)省市二級下拉的效果。按住Ctrl+F3查看已定義的名稱時(shí)，也不會在名稱列表中出現(xiàn)幾十個(gè)被定義的名稱，以至于看起來非常混亂。就像下面這樣： 此外，可以避開繁瑣的基礎(chǔ)操作，省去前期數(shù)據(jù)整理的大量工作，不必再將數(shù)據(jù)整理成如下格式，保持省份和城市兩列即可。 通過以下代碼，可以取得與普通青年完全相同的省市二級下拉菜單效果。 劃重點(diǎn)：以下代碼需要與要定義的省市二級下拉菜單位于同一個(gè)工作表中，并且代碼寫好后需要保存工作簿后重新打開，這兩個(gè)工作表事件才能正常運(yùn)行，不知是否又是微軟的Bug。 Private Sub Worksheet_Activate() Application.ScreenUpdating = False '暫停刷新屏幕 Application.EnableEvents = False '禁用事件 Dim s As Object arr = Sheets('省市列表-IT青年').Range('A1').CurrentRegion If UBound(arr) < 2 Then MsgBox '數(shù)據(jù)庫中沒有數(shù)據(jù)可供檢索！', vbInformation: Exit Sub Set s = CreateObject('Scripting.Dictionary') For I = 2 To UBound(arr) If Not s.Exists(arr(I, 1)) Then s(arr(I, 1)) = '' End If Next With Range('D30').Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(s.keys, ',') On Error Resume Next End With Set s = Nothing Application.EnableEvents = True Application.ScreenUpdating = True '恢復(fù)刷新屏幕End SubPrivate Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False '暫停刷新屏幕 Application.EnableEvents = False Dim brr(), n As Integer If Target.Address = '$D$30' Then '選擇省份 arr = Sheets('省市列表-IT青年').Range('A1').CurrentRegion Range('E30') = '' For I = 2 To UBound(arr) If arr(I, 1) = Target Then n = n + 1: ReDim Preserve brr(n) brr(n) = arr(I, 2) End If Next With Range('E30').Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(brr, ',') On Error Resume Next End With End If Application.EnableEvents = True '啟用事件 Application.ScreenUpdating = True '恢復(fù)刷新屏幕End Sub這里省份一級菜單用到了Worksheet_Activate事件，城市二級下拉菜單用到了Worksheet_Change事件。 其中D30和E30單元格分別為省市下拉菜單所在位置，Range('A1').CurrentRegion代表與A1單元格相連的區(qū)域，也即被空行和空列隔開的單元格區(qū)域。這些均可以根據(jù)實(shí)際情況按需修改，其他代碼部分其實(shí)并沒有多大必要逐字逐句地去理解和記憶，拿來主義為我所用就好。 怎么樣，小伙伴們學(xué)會了嗎？你覺得自己是哪種青年呢？ |
|
|
