圖/文 | 安偉星

早就承諾大家要寫一篇Excel制作下拉菜單的教程，一直拖了這麼久，這次用一篇文章讓你完全掌握！

下拉菜單，從制作方法上，可以分為數據有效性法、控件法；從功能上，可以分為一級下拉菜單、多級聯動下拉菜單、查詢下拉菜單。

下拉菜單有兩者制作方法，最常用的是我們熟知的數據有效性，其實Excel中還有一個工具可以制作下拉菜單，它就是控件。

由于控件靈活性非常強，篇幅有限，本文隻做簡要介紹，将主要精力放在數據有效性上面。

①數據有效性法

數據有效性在2016版Excel中叫做數據驗證。

如圖所示，需要為部門列設置一級下拉菜單，設置下拉菜單之後，不僅能夠提高錄入效率，而且可以有效防止不規範地輸入。

Step1： 選擇要添加下拉菜單的單元格C2:C7，切換到「數據」選項卡，點擊「數據驗證」

Step2：驗證條件中，「允許」中選擇「序列」

Step3：「來源」框内選擇已制作好的列表區域（也可手動錄入選項，選項之間用英文狀态下的逗号隔開）

GIF動圖演示

②控件法

控件是Excel中比較高級的一種功能，多用于VBA開發。它被集成在「開發工具」選項卡。控件法創建的下拉菜單，多數用于數值的選擇，一般創建的較少，不能批量創建。

Excel中的控件

如果你的Excel中，沒有開發工具這個選項卡，需要先在「自定義功能區」中将「開發工具」添加進來。

勾選如下圖中的開發工具即可。

創建方法：

Step1：切換到在「開發工具」選項卡，在「控件」分區，點擊「插入」，選擇「組合框」控件

Step2：在工作表的任意位置繪制生成控件，選中控件點擊「鼠标右鍵」→「設置控件格式」，在彈出的對話框中設置數據源區域，其他項保持默認即可。

GIF動圖演示

控件的使用非常靈活，它和OFFSET函數、CHOOSE函數、MTATCH函數、INDEX函數等結合，能制作出非常高效的動态圖表，這裡不詳細展開。

可以看出，不管是是用數據驗證還是控件，制作一級下拉菜單都非常簡單，其本質就是将下拉菜單中的數據作為數據源提前存儲在菜單中，我們要做的就是設置好數據源即可，Excel自身會生成菜單。

首先制作二級聯動菜單。

二級聯動菜單指的是，當我們選擇一級菜單之後，對應的二級菜單會随着一級菜單的不同而選項也不同。二級菜單的創建方法有很多種，這裡我們講最常用的：通過indirect函數創建。

如圖所示，我們要創建省份是一級下拉菜單，對應的市名是二級下拉菜單的聯動菜單。

①為省市創建“名稱”

名稱是一個有意義的簡略表示法，可以在Excel中方便的代替單元格引用、常量、公式或表。

比如将C20:C30區域定義為名稱：MySales，那麼公式=SUM（MySales）可以替代=SUM（C20:C30），可見名稱比單元格區域更具有實際意義。

Step1：按住Ctrl鍵，分别用鼠标選取包含省、市名的三列數據，要點是不要選擇空單元格。（也可以通過Ctrl G調出定位條件，設置定位條件為在常量來選取數據區域）

Step2：在菜單欄中切換到【公式】選項卡→選擇【定義的名稱】分區→點擊【根據所選内容創建】，在彈出的菜單中，勾選【首行】選項，如圖所示，這樣就創建了三個省份的“名稱”，“名稱”的值為對應着城市名。

②創建聯動菜單

Step1：創建一級菜單

為區域中的省份一列創建一級菜單，創建方法通過“引用區域”的方式，直接将第一個圖中的B1:D1區域作為數據來源，這裡不在贅述。

Step2：為上圖中的“市”創建二級菜單

選中【市】列需要設置的單元格區域→在驗證條件中選擇【序列】→【來源】中輸入公式=INDIRECT($C3)→點擊【确定】，此時會彈出錯誤提示，點擊【是】繼續下一步即可，如圖。

提示：這裡出錯的原因是此時C3單元格中為空，還未選擇省份的數據，找不到數據源，不影響二級菜單的設置。

完成之後，就實現了二級聯動菜單，如圖所示。

原理解析

實現二級聯動菜單的核心是：定義名稱和INDIRECT函數，理解這兩個核心是解題的關鍵。

原理①：根據“名稱”的作用，當我們定義名稱“江蘇省”時，那麼在函數引用中，“江蘇省”能夠代替“南京、蘇州……”

原理②：INDIRECT函數為間接引用，他可将文本轉化為引用。

如圖是間接引用于直接引用的不同。

将原理①和原理②結合起來，以江蘇為例，在來源中輸入的公式=INDIRECT（$C3）的意思是，首先C3單元格中的值是“江蘇省”，而INDIRECT可以将文本換成引用，而“江蘇省”已經定義為名稱，代表的是“南京、蘇州……”，所以二級下拉菜單中出現的南京市、蘇州市等。

多級下拉菜單的制作原理是完全一樣的，學會了二級下拉菜單，三級菜單甚至四級菜單應該也不成問題，自己動手試一試吧！

下拉菜單的目的之一是提高輸入的效率，但是，如果選項過多，那麼下拉列表勢必會很長，此時要想快速從下拉菜單中找到目标選項就非常困難。

我經常在想，如果能進行搜索下拉菜單該多好啊，這裡教給你的方法，雖然沒有搜索框，但是能模拟搜索的效果。

我把它稱為查詢式下拉菜單。

如圖，要根據A列的集團列表，在E2單元格創建查詢式下拉菜單，更方便地選擇集團。該下拉菜單可以根據E2單元格内輸入的第一個字來動态顯示所有以輸入漢字開頭的集團，即實現查詢作用。

Step1：對A列的集團進行升序排序。

Step2：選中E2單元格，打開「數據驗證」對話框。在“允許”中選擇“序列”，并在“來源”中輸入公式：

=OFFSET($A$1,MATCH($E$2&"*",$A$2:$A$15,0),,COUNTIF($A$2:$A$15,$E$2&"*"),1)

Step3：在「數據驗證」對話框，切換到「出錯警告」窗口，取消勾選「輸入無效數據時顯示出錯警告」，然後點擊确定，完成設置。

最終的效果如下動圖所示：

操作步驟同樣很簡單，難點是來源裡面設置的公式。

①為什麼要對集團數據列進行升序排序

排序之後，可以将第一個字相同的集團排在一起，這樣在後面的輸入首字進行查詢式，這些集團都能夠顯示出來。

②OFFSET函數

它的語法形式是 OFFSET(reference,rows,cols,height,width)，參數1為參照系，參數2為偏移行數，參數3為偏移列數，參數4為返回幾行，參數5為返回幾列。

總之，這裡主函數OFFSET的作用就是：當E2單元格内輸入首字時，找到以輸入的漢字開頭的集團名稱，并引用所有符合條件的集團作為下拉菜單的顯示内容。

③MATCH($E$2&"*",$A$2:$A$15,0)

在集團列表中查找以E2單元格字符開頭的集團名稱，返回找到的對應的第一個集團在列表中的序号；

④COUNTIF($A$2:$A$15,$E$2&"*")

在列表中統計以E2中字符開頭的集團的個數

這裡，MATCH函數作為OFFSET的第二個參數，即向下移動的行數；COUNTIF函數作為OFFSET的第4個參數，即從集團列表中返回的行數。

舉例：當E2中輸入“廣”時

MATCH($E$2&"*",$A$2:$A$15,0)返回以廣開頭的集團在$A$2:$A$15中的序号，即2（廣發集團排在第二位）。

此時COUNTIF($A$2:$A$15,$E$2&"*")統計出以廣開頭的集團共有三個，所以返回值為3。

主函數就變為OFFSET($A$1,2,,3,1)，即返回「以A1為參照，向下移動移動兩行（A3），行數總計為3行（A3:A5）的一個區域」，這個區域正是以廣開頭的三家集團：廣發集團、廣彙集團、廣汽集團。

⑤為什麼不能勾選出錯警告

數據驗證，要求輸入的内容和設置的源中的内容必須一緻，否則将提示警告，導緻無法正常輸入。我們因為是首字匹配，因此要取消警告。

最後，再次強調，函數是重點，理解了函數在本裡中充當的含義，才能靈活的設置查詢式下拉菜單。

·The End·

作者：安偉星，微軟Office認證大師，領英中國專欄作者，《競争力：玩轉職場Excel，從此不加班》圖書作者

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!