玩轉excel下拉菜單，自動更新、剔除重復項、空值，全搞定

在單元格中設置下拉菜單能有效提高表格錄入和查找效率，還能規范數據，避免輸入錯誤，是很受歡迎的實用功能。網上很多文章在講解設置方法時，用的案例數據都很簡單規范，實現起來比較容易。但在實際工作中，數據不一定規范，這樣生成的菜單往往會存在一些瑕疵、甚至錯誤。本文針對實際工作數據生成菜單中遇到的問題，分享處理解決方法，和大家一起玩轉下拉菜單。

一級菜單

1、設置方法

選中需要設置下拉菜單的單元格，點擊“數據”菜單下的“數據有效性”，將“允許”項設置為“序列”,單擊一下“來源”輸入框后，在表格里選取單元格區域作為菜單選項，或者手動在來源框輸入單元格區域作為菜單選項，點擊確定，菜單設置完成。

其中來源框輸入公式為：=$A$1:$E$1，即將A1至E1單元格數據作為菜單的下拉選項。

可一次性選中I2至I5單元格，一步設置多個單元格的下拉菜單。

2、菜單自動更新、消除空值

實際工作中，上面的案例可能會需要中途增加菜單選項的要求，比如要求增加F1單元格為菜單選項。這時我們就需要重新設置一次菜單，選擇單元格修改來源看的輸入公式為：=$A$1:$F$1。

為避免每次增加選項都要重新進行菜單設置，可以在設置來源公式時，直接將預計可能會增加的菜單項單元格輸入到公式里，比如上面的案例，雖然F1沒有數據，但我們在設置來源公式時可以直接輸入：=$A$1:$F$1。這樣在后期F1單元格補充輸入數據時，數據就會自動出現在菜單選項中，得到達到自動更新菜單的效果。

但是，這樣生成的菜單有個小瑕疵，即當F1還未填入數據時，菜單選項對應F1的位置會出現空白，不太合適。我們需要實現當F1有數據就顯示在菜單選項中，當F1沒數據時，空白區域不顯示在菜單中。方法如下：

修改來源位置的公式為：=($A$1,,,1,(CHAR(1),$A$1:$F$1,-1))，公式里的$A$1:$F$1為預設菜單的區域。

3、消除菜單中間空值

有時候，會遇到菜單來源單元格區域中間某個單元格為空值，比如上面的案例中D1單元格為空，E1格有數據，需要消除菜單中的空白。可以通過設置輔助數據的方式，先將A1至F1單元格區域進行重新排列，將空白值放到區域的末端，再設置來源位置公式，具體如下：

L5至Q5單元格為重新排列輔助區域，作為菜單選項來源區域。L5單元格公式為=(($A$1:$F$1,(IF($A$1:$F$1"",($A$1:$F$1),4^9),(A1))),"")，+ctrl+三鍵同時按。右拉拖到到Q5完成輔助數據。

數據來源公式：=($L$5,,,,(CHAR(1),$L$5:$Q$5,-1))

4、消除重復值

有時候我們會遇到數據區域里面有重復值的情況，要求在菜單下拉項中只顯示一次，不要重復顯示。比如下面的情況，菜單下拉項要同時剔除重復值和空值：

解決方法：VBA處理，在頁面代碼區粘貼下面的代碼：

Sub ( As ) If . 9 Or . > 1 Or .Row < 2 Then Exit Sub Set sht = Dim arr, brr, crr, i&, j& arr(100) For i = 1 To 6 If sht.(1, i) "" Then For j = 1 To i - 1 If sht.(1, i) = sht.(1, j) Then GoTo 10 Next If j = i Then n = n + 1 arr(n) = sht.(1, i) End If End If10 Next If n = 0 Then Exit Sub brr(1 To n) For i = 1 To n brr(i) = arr(i) s = Join(brr, ",") Next With . . .Add Type:=, :=, := _ , :=s End With Set arr = Set brr = Set s = Sub

二級菜單

1、設置方法

二級菜單的選項需要根據一級菜單的內容進行聯動變化，下圖案例中在I列將加粗的第一行內容設置為一級菜單，在J列設置二級聯動菜單，設置過程共三步：

1）按一級菜單設置方法在I列設置好一級菜單。

2）選中基礎數據區域A1至E5，點擊F5或ctrl+G打開定位條件，選擇常量，確定。點擊“公式”菜單下“根據所選內容創建”，復選框只保留“首行”為選中狀態，確定。完成第一步公式設置。

3）選中J列想要設置菜單的單元格，點擊“數據”菜單下的“數據有效性”，將“允許”項設置為“序列”,單擊一下“來源”輸入框后，輸入公式：=($I2)，點擊確定完成二級菜單設置。

2、二級菜單自動更新、消除空值

二級菜單也需要預留一些空格區域，同時要求菜單選項里空格不顯示出來，比如需要設置第2行至第6行為二級菜單的預留區域，處理方法：

1）選中基礎數據區域A1至E6，點擊“公式”菜單下“根據所選內容創建”，復選框只保留“首行”為選中狀態，確定，完成公式設置。注意此處沒有按F5設置常量的過程。

2）將“來源”輸入框公式“=($I2)“修改為：=(($I2),0,0,(CHAR(1),($I2),-1),1)。

3、消除中間空值和重復值方法可參考一級菜單。

多級菜單綜合案例

有時候我們會同時遇到有空值和重復值的情況，比如下面的超市品類管理案例，A列為一級菜單數據源區域，中間有空值，還有重復值，需要菜單選項里不要出現重復值和空值，同時要求A列數據修改后，菜單選項隨之及時更新。這種情況設置起來要用到輔助數據，非常麻煩，我們可以用VBA代碼，直接一次性解決。

上圖在I列點擊單元格時出現的下拉菜單選項就剔除了重復項，空值，且能滿足菜單及時更新。方法為：打開VBA界面，雙擊數據所在的表單，粘貼下面的代碼。

其中代碼行中，下圖的.代表在I列設置一級菜單，若要在其他列設置菜單，將9修改成列號在字母表對應的序號即可。

完整代碼為：

Sub ( As )If . 9 Or . > 1 Or .Row < 3 Then Exit sht = arr, brr, crr, i&, j& arr(100)If sht.[].End(3).Row