編按：

一說起下拉菜單，我相信大多數(shù)有點(diǎn)Excel基礎(chǔ)的都會(huì)做。但今天我們來點(diǎn)不一樣的下拉菜單，從青銅級(jí)別到王者級(jí)別，應(yīng)有盡有，趕緊來看一看吧！

下面，我們將按照從簡(jiǎn)單到高級(jí)來給大家介紹如下幾種下拉菜單。

1、最簡(jiǎn)單的下拉菜單

2、名稱制作下拉菜單

3、動(dòng)態(tài)下拉菜單（沒有空白選項(xiàng)的）

4、多內(nèi)容下拉菜單，填充時(shí)拆分內(nèi)容

5、智能錄入下拉菜單，填充時(shí)拆分內(nèi)容

注意：今天主要說的內(nèi)容是“數(shù)據(jù)驗(yàn)證（Validation）”的內(nèi)容，不涉及控件使用，各個(gè)Excel版本都可以使用。

【正文】

1、青銅（最簡(jiǎn)單的下拉菜單）

制作方法：

在工具欄中，點(diǎn)擊“數(shù)據(jù)驗(yàn)證”功能；

在彈出的窗口中，按下圖：

在【允許】中選擇“序列”，在【來源】中輸入“男,女”，按確定鍵完成操作。

注意：無論選填的內(nèi)容是什么，都要用“英文的逗號(hào)間隔”，而且不需使用雙引號(hào)。

2、白銀

方法1：在數(shù)據(jù)驗(yàn)證窗口中，如下設(shè)置

【來源】處可以直接選擇單元格區(qū)域，即可把這部分的內(nèi)容作為下拉菜單的內(nèi)容。

方法2：我們也可以使用名稱管理器來操作：

選擇A2：A17單元格，然后在【名稱框】中編輯一個(gè)名稱：例如我們輸入“科學(xué)家”，然后【按回車結(jié)束編輯】

然后在數(shù)據(jù)驗(yàn)證窗口中如下圖設(shè)置；

3、黃金——?jiǎng)討B(tài)下拉菜單

如果我們的選擇項(xiàng)數(shù)量不確定，隨著添加或者刪除，下拉選項(xiàng)也要隨之增加減少，如下圖所示：

按CTRL+F3組合鍵，打開【名稱管理器】窗口，新建一個(gè)名稱“選項(xiàng)”，按下圖設(shè)置名稱內(nèi)容：

引用位置的函數(shù)：=OFFSET(黃金!$A$2,,,COUNTA(黃金!$A$2:$A$997),)

使用COUNTA函數(shù)確定區(qū)域中的內(nèi)容數(shù)量，然后在使用數(shù)據(jù)驗(yàn)證創(chuàng)建下拉菜單即可。

如果A列數(shù)據(jù)是使用函數(shù)后得到的，例如把某列數(shù)據(jù)去重，如下圖：

A2單元格的函數(shù)為：

{=IFERROR(INDEX($A$2:$A$38,SMALL(IF(MATCH($A$2:$A$38,$A$2:$A$38,0)=ROW($1:$37),ROW($1:$37),9^9),ROW(B1))),"")}

這個(gè)函數(shù)是萬金油，之前的教程中有解釋的，就不在這里贅述了。然后下拉A2單元格填充至A18，就得到了去重后的內(nèi)容，然后再使用名稱即可引用到數(shù)據(jù)驗(yàn)證中。

但是要注意：表中從B8:B18都是IFERROR函數(shù)容錯(cuò)后的空格，那么剛才用COUNTA函數(shù)引出選項(xiàng)個(gè)數(shù)的方式就不正確了，我們換一個(gè)寫法即可，如下：

函數(shù)如下：

=OFFSET(黃金!$B$2,,,COUNTA(黃金!$B$2:$B$18)-COUNTBLANK(黃金!$B$2:$B$18),)

用COUNTA-COUNTBLANK的方式確定應(yīng)該取多少個(gè)選項(xiàng)內(nèi)容。

截止到此，【下拉選填菜單】的操作方法，配合【函數(shù)】和【自定義名稱】還可以適應(yīng)工作，但是已經(jīng)顯得力不從心，因?yàn)殡S著數(shù)據(jù)的變化，對(duì)于函數(shù)和自定義名稱的調(diào)整是在所難免的。那么【更高級(jí)別的下拉選填菜單】我們繼續(xù)看。

4、鉆石級(jí)別——多內(nèi)容下拉菜單，填充時(shí)拆分內(nèi)容

下拉菜單選擇項(xiàng)包含多個(gè)不同屬性的內(nèi)容，選填后自動(dòng)填入多個(gè)單元格。這個(gè)方式是使用VBA操作的，代碼如下：

Private Sub Worksheet_Change(ByVal Target As Range) '''單元格值改變，觸發(fā)事件

  If Target.Column = 4 And Target.Count = 1 Then '''判斷是否在D列,選擇一個(gè)單元格

    With Target '''使用這個(gè)單元格

      s = .Text '''單元格的值賦值給一個(gè)變量s

      .Resize(1, 2) = Split(s, "：") '''單元格擴(kuò)展1行2列，然后把用Split函數(shù)按冒號(hào)拆分的數(shù)組賦值到單元格區(qū)域

    End With '''with結(jié)束語句

  End If '''結(jié)束判斷

End Sub '''工程結(jié)束

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '''選擇單元格改變，觸發(fā)事件

  If Target.Column = 4 And Target.Count = 1 Then '''判斷是否在D列,選擇一個(gè)單元格

    With Sheets("鉆石")

      s = Join(Application.Transpose(.Range("A2:A" & .[A65000].End(3).Row)), ",") '''將A列從A2開始，有值的單元格Join成一個(gè)字符串，用英文逗號(hào)間隔

    End With

    With Selection.Validation '對(duì)所選擇的單元格，創(chuàng)建數(shù)據(jù)有效性

      .Delete

      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:=s

      .IgnoreBlank = True '設(shè)置單元格 允許空值

      .InCellDropdown = True '提供下拉列標(biāo)

    End With

  End If '結(jié)束判斷

End Sub '''工程結(jié)束

按下圖粘貼或者編寫代碼亦可。

5、王者——智能錄入下拉菜單，填充時(shí)拆分內(nèi)容

同學(xué)們做過多級(jí)聯(lián)動(dòng)的下拉菜單嗎？你是如何做的呢，按首行批量創(chuàng)建自定義名稱，然后再INDIRECT？

這種方法對(duì)于簡(jiǎn)單的數(shù)據(jù)源還是可以采用的，但是對(duì)于比較多的內(nèi)容就不是很方便，例如我們今天的這份數(shù)據(jù)源——“全國【省、市、縣 三層】的智能下拉填充功能”

如果同學(xué)們用傳統(tǒng)方式做的話，自定義名稱要做多少呢？

下面看看作者E圖表述制作后的效果吧：

這也是用VBA做的，VBA可以做很多日常不敢想想的內(nèi)容，也只有VBA才能做出高級(jí)的功能。不說廢話，代碼如下：

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 7 And Target.Row = 2 Then '''如果被改變值的單元格7行2列【G2】單元格時(shí)

    With Sheets("王者") '''使用《王者》工作表

      arr = .Range("A2:C" & .[A100000].End(3).Row) '''將從A2單元格開始到A列已使用的末行結(jié)束的單元格區(qū)域賦值數(shù)值arr

      s = Target.Text '''將被操作單元格的值賦值給變量s

      If s <> "" Then '''如果s不為空值

        For i = 1 To UBound(arr) '''循環(huán)數(shù)組arr

          st = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) '''將省市縣內(nèi)容合并，并且用|間隔

          If st Like "*" & s & "*" Then '''如果st字符串包含s變量的內(nèi)容

            svd = svd & st & "," '''將st賦值給svd變量，并用英文逗號(hào)間隔，作為下拉菜單的內(nèi)容

          End If

        Next i

      End If

      With .Range("G3").Validation '對(duì)G3單元格創(chuàng)建數(shù)據(jù)有效性

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

          xlBetween, Formula1:=svd

        .IgnoreBlank = True '設(shè)置單元格 允許空值

        .InCellDropdown = True '提供下拉列標(biāo)

      End With

      .[G3] = "" '''G3單元格初始值為空

    End With

  End If

  If Target.Column = 7 And Target.Row = 3 And Target.Text <> "" Then '''如果G3單元格的值被改變，且值不為空時(shí)

    With Sheets("王者")

      a = .[H100000].End(3).Row + 1 '''確定H列中為空的第一行

      .Cells(a, 8) = Split(Target.Text, "|")(0) '''Split方法拆分下拉菜單的內(nèi)容，將第1個(gè)值放入第8列的末行空白行

      .Cells(a, 9) = Split(Target.Text, "|")(1) '''同上，放入第2個(gè)值

      .Cells(a, 10) = Split(Target.Text, "|")(2) '''同上，放入第3個(gè)值

      '''注意：Split方法拆分后，是一個(gè)一維數(shù)組。vba中的內(nèi)存數(shù)組序號(hào)默認(rèn)是從0開始計(jì)數(shù)的

      .[G3] = "" '''G3單元格再歸零

    End With

  End If

End Sub

不需自定義名稱，不需多級(jí)聯(lián)動(dòng)的大量數(shù)據(jù)驗(yàn)證，這將極大的節(jié)省EXCEL的內(nèi)存，大大提高了運(yùn)行的效率，同時(shí)一級(jí)選擇、多級(jí)錄入的方式也節(jié)省了操作的環(huán)節(jié)。

寫在最后：王者只是人間的強(qiáng)者，而這世間還有更高的存在。人外人、天外天，EXCEL之外有EXCEL，除開上面這種“下拉菜單”，我們還有更高級(jí)別的，大家想不想看呢？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

如何制作智能的下拉菜單？輸入數(shù)據(jù)模糊匹配對(duì)應(yīng)的下拉選項(xiàng)

最簡(jiǎn)單的多級(jí)下拉菜單制作方法，不需要定義名稱

別怕，VBA入門級(jí)教程來了，條件語句很簡(jiǎn)單！

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者E圖表述；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。