玩轉(zhuǎn)excel下拉菜單自動(dòng)更新、剔除重復(fù)項(xiàng)、空值全搞定

玩轉(zhuǎn)excel下拉菜單，自動(dòng)更新、剔除重復(fù)項(xiàng)、空值，全搞定在單元格中設(shè)置下拉菜單能有效提高表格錄入和查找效率，還能規(guī)范數(shù)據(jù)，避免輸入錯(cuò)誤，是很受歡迎的實(shí)用功能。網(wǎng)上很多文章在講解設(shè)置方法時(shí)，用的案例數(shù)據(jù)都很簡(jiǎn)單規(guī)范，實(shí)現(xiàn)起來(lái)比較容易。但在實(shí)際工作中，數(shù)據(jù)不一定規(guī)范，這樣生成的菜單往往會(huì)存在一些瑕疵、甚至錯(cuò)誤。本文針對(duì)實(shí)際工作數(shù)據(jù)生成菜單中遇到的問(wèn)題，分享處理解決方法，和大家一起玩轉(zhuǎn)excel下拉菜單。一級(jí)菜單1、設(shè)置方法選中需要設(shè)置下拉菜單的單元格，點(diǎn)擊“數(shù)據(jù)”菜單下的“數(shù)據(jù)有效性”，將“允許”項(xiàng)設(shè)置為“序列”,單擊一下“來(lái)源”輸入框后，在表格里選取單元格區(qū)域作為菜單選項(xiàng)，或者手動(dòng)在來(lái)源框輸入單元格區(qū)域作為菜單選項(xiàng)，點(diǎn)擊確定，菜單設(shè)置完成。其中來(lái)源框輸入公式為：=$A$1:$E$1，即將A1至E1單元格數(shù)據(jù)作為菜單的下拉選項(xiàng)?？梢淮涡赃x中I2至I5單元格，一步設(shè)置多個(gè)單元格的下拉菜單。2、菜單自動(dòng)更新、消除空值實(shí)際工作中，上面的案例可能會(huì)需要中途增加菜單選項(xiàng)的要求，比如要求增加F1單元格為菜單選項(xiàng)。這時(shí)我們就需要重新設(shè)置一次菜單，選擇單元格修改來(lái)源看的輸入公式為：=$A$1:$F$1。為避免每次增加選項(xiàng)都要重新進(jìn)行菜單設(shè)置，可以在設(shè)置來(lái)源公式時(shí)，直接將預(yù)計(jì)可能會(huì)增加的菜單項(xiàng)單元格輸入到公式里，比如上面的案例，雖然F1沒(méi)有數(shù)據(jù)，但我們?cè)谠O(shè)置來(lái)源公式時(shí)可以直接輸入：=$A$1:$F$1。這樣在后期F1單元格補(bǔ)充輸入數(shù)據(jù)時(shí)，數(shù)據(jù)就會(huì)自動(dòng)出現(xiàn)在菜單選項(xiàng)中，得到達(dá)到自動(dòng)更新菜單的效果。但是，這樣生成的菜單有個(gè)小瑕疵，即當(dāng)F1還未填入數(shù)據(jù)時(shí)，菜單選項(xiàng)對(duì)應(yīng)F1的位置會(huì)出現(xiàn)空白，不太合適。我們需要實(shí)現(xiàn)當(dāng)F1有數(shù)據(jù)就顯示在菜單選項(xiàng)中，當(dāng)F1沒(méi)數(shù)據(jù)時(shí)，空白區(qū)域不顯示在菜單中。方法如下：修改來(lái)源位置的公式為：=OFFSET($A$1,,,1,MATCH(CHAR(1),$A$1:$F$1,-1))，公式里的$A$1:$F$1為預(yù)設(shè)菜單的區(qū)域。3、消除菜單中間空值有時(shí)候，會(huì)遇到菜單來(lái)源單元格區(qū)域中間某個(gè)單元格為空值，比如上面的案例中D1單元格為空，E1格有數(shù)據(jù)，需要消除菜單中的空白。可以通過(guò)設(shè)置輔助數(shù)據(jù)的方式，先將A1至F1單元格區(qū)域進(jìn)行重新排列，將空白值放到區(qū)域的末端，再設(shè)置來(lái)源位置公式，具體如下：L5至Q5單元格為重新排列輔助區(qū)域，作為菜單選項(xiàng)來(lái)源區(qū)域。L5單元格公式為=IFERROR(INDEX($A$1:$F$1,SMALL(IF($A$1:$F$1<>'',COLUMN($A$1:$F$1),4^9),COLUMN(A1))),'')，shift+ctrl+enter三鍵同時(shí)按。右拉拖到到Q5完成輔助數(shù)據(jù)。數(shù)據(jù)來(lái)源公式：=OFFSET($L$5,,,,MATCH(CHAR(1),$L$5:$Q$5,-1))4、消除重復(fù)值有時(shí)候我們會(huì)遇到數(shù)據(jù)區(qū)域里面有重復(fù)值的情況，要求在菜單下拉項(xiàng)中只顯示一次，不要重復(fù)顯示。比如下面的情況，菜單下拉項(xiàng)要同時(shí)剔除重復(fù)值和空值：解決方法：VBA處理，在頁(yè)面代碼區(qū)粘貼下面的代碼：PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)IfTarget.Column<>9OrTarget.Count>1OrTarget.Row<2ThenExitSubSetsht=ActiveSheetDimarr,brr,crr,i&,j&ReDimarr(100)Fori=1To6Ifsht.Cells(1,i)<>''ThenForj=1Toi-1Ifsht.Cells(1,i)=sht.Cells(1,j)ThenGoTo10NextIfj=iThenn=n+1arr(n)=sht.Cells(1,i)EndIfEndIf10NextIfn=0ThenExitSubReDimbrr(1Ton)Fori=1Tonbrr(i)=arr(i)s=Join(brr,',')NextWithSelection.Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=sEndWithSetarr=NothingSetbrr=NothingSets=NothingEndSub二級(jí)菜單1、設(shè)置方法二級(jí)菜單的選項(xiàng)需要根據(jù)一級(jí)菜單的內(nèi)容進(jìn)行聯(lián)動(dòng)變化，下圖案例中在I列將加粗的第一行內(nèi)容設(shè)置為一級(jí)菜單，在J列設(shè)置二級(jí)聯(lián)動(dòng)菜單，設(shè)置過(guò)程共三步：1）按一級(jí)菜單設(shè)置方法在I列設(shè)置好一級(jí)菜單。2）選中基礎(chǔ)數(shù)據(jù)區(qū)域A1至E5，點(diǎn)擊F5或ctrl+G打開(kāi)定位條件，選擇常量，確定。點(diǎn)擊“公式”菜單下“根據(jù)所選內(nèi)容創(chuàng)建”，復(fù)選框只保留“首行”為選中狀態(tài)，確定。完成第一步公式設(shè)置。3）選中J列想要設(shè)置菜單的單元格，點(diǎn)擊“數(shù)據(jù)”菜單下的“數(shù)據(jù)有效性”，將“允許”項(xiàng)設(shè)置為“序列”,單擊一下“來(lái)源”輸入框后，輸入公式：=indirect($I2)，點(diǎn)擊確定完成二級(jí)菜單設(shè)置。2、二級(jí)菜單自動(dòng)更新、消除空值二級(jí)菜單也需要預(yù)留一些空格區(qū)域，同時(shí)要求菜單選項(xiàng)里空格不顯示出來(lái)，比如需要設(shè)置第2行至第6行為二級(jí)菜單的預(yù)留區(qū)域，處理方法：1）選中基礎(chǔ)數(shù)據(jù)區(qū)域A1至E6，點(diǎn)擊“公式”菜單下“根據(jù)所選內(nèi)容創(chuàng)建”，復(fù)選框只保留“首行”為選中狀態(tài)，確定，完成公式設(shè)置。注意此處沒(méi)有按F5設(shè)置常量的過(guò)程。2）將“來(lái)源”輸入框公式“=indirect($I2)“修改為：=OFFSET(INDIRECT($I2),0,0,MATCH(CHAR(1),INDIRECT($I2),-1),1)。3、消除中間空值和重復(fù)值方法可參考一級(jí)菜單。多級(jí)菜單綜合案例有時(shí)候我們會(huì)同時(shí)遇到有空值和重復(fù)值的情況，比如下面的超市品類(lèi)管理案例，A列為一級(jí)菜單數(shù)據(jù)源區(qū)域，中間有空值，還有重復(fù)值，需要菜單選項(xiàng)里不要出現(xiàn)重復(fù)值和空值，同時(shí)要求A列數(shù)據(jù)修改后，菜單選項(xiàng)隨之及時(shí)更新。這種情況設(shè)置起來(lái)要用到輔助數(shù)據(jù)，非常麻煩，我們可以用VBA代碼，直接一次性解決。上圖在I列點(diǎn)擊單元格時(shí)出現(xiàn)的下拉菜單選項(xiàng)就剔除了重復(fù)項(xiàng)，空值，且能滿(mǎn)足菜單及時(shí)更新。方法為：打開(kāi)VBA界面，雙擊數(shù)據(jù)所在的表單，粘貼下面的代碼。其中代碼行中，下圖的target.column<>9代表在I列設(shè)置一級(jí)菜單，若要在其他列設(shè)置菜單，將9修改成列號(hào)在字母表對(duì)應(yīng)的序號(hào)即可。完整代碼為：PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)IfTarget.Column<>9OrTarget.Count>1OrTarget.Row<3ThenExitSubSetsht=ActiveSheetDimarr,brr,crr,i&,j&ReDimarr(100)Ifsht.[a3000].End(3).Row<=1ThenExitSubFori=1Tosht.[a3000].End(3).RowIfsht.Cells(i,1)<>''ThenForj=1Toi-1Ifsht.Cells(i,1)=sht.Cells(j,1)ThenGoTo10NextIfj=iThenn=n+1arr(n)=sht.Cells(i,1)EndIfEndIf10NextReDimbrr(1Ton)Fori=1Tonbrr(i)=arr(i)s=Join(brr,',')NextWithSelectio