玩转excel下拉菜单自动更新、剔除重复项、空值全搞定.docx

玩转exce1下拉菜单，自动更新、剔除重复项、空值，全搞定在单元格中设置下拉菜单能有效提高表格录入和查找效率，还能规范数据，避免输入错误，是很受欢迎的实用功能。网上很多文章在讲解设置方法时，用的案例数据都很简单规范，实现起来比较容易。但在实际工作中，数据不一定规范，这样生成的菜单往往会存在一些瑕疵、甚至错误。本文针对实际工作数据生成菜单中遇到的问题，分享处理解决方法，和大家一起玩转exce1下拉菜单。一级菜单1、设置方法选中需要设置下拉菜单的单元格，点击"数据"菜单下的"数据有效性"，将"允许项设置为“序列"，单击一下"来源”输入框后,在表格里选取单元格区域作为菜单选项，或者手动在来源框输入单元格区域作为菜单选项，点击确定，菜单设置完成。2W1布i11瓜果熟食取果枝菜肉类面包卤夷感类菜臭牛肉器面包面点干果类花果英羊肉或面包苏菜水果柒根茎英猪肉法式面包排骨余粮面包二SR«Jjfi«aKxmMkrosoftExc开发工J1ABBYYFineReader12其中来源框输入公式为：=$A$1:$E$1,即将A1至E1单元格数据作为菜单的下拉选项。可一次性选中12至15单元格,一步设置多个单元格的下拉菜单。2、菜单自动更新、消除空值实际工作中，上面的案例可能会需要中途增加菜单选项的要求，比如要求增加F1单元格为菜单选项。这时我们就需要重新设置一次菜单，选择单元格修改来源看的输入公式为:=$A$1:$F$1O为避免每次增加选项都要重新进行菜单设置，可以在设置来源公式时，直接将预计可能会增加的菜单项单元格输入到公式里，比如上面的案例，虽然F1没有数据，但我们在设置来源公式时可以直接输入：=$A$1:$F$1O这样在后期FI单元格补充输入数据时，数据就会自动出现在菜单选项中，得到达到自动更新菜单的效果。但是，这样生成的菜单有个小瑕疵，即当F1还未填入数据时，菜单选项对应F1的位置会出现空白，不太合适。我们需要实现当FI有数据就显示在菜单选项中，当FI没数据时，空白区域不显示在菜单中。方法如下：修改来源位置的公式为：=OFFSET($A$1,1,MATCH(CHAR(1),$A$1:$F$1r-1),公式里的$八$1:$F$1为预设菜单的区域。3、消除菜单中间空值有时候，会遇到菜单来源单元格区域中间某个单元格为空值，比如上面的案例中D1单元格为空，E1格有数据，需要消除菜单中的空白。可以通过设置辅助数据的方式，先将A1至F1单元格区域进行重新排列，将空白值放到区域的末端，再设置来源位置公式，具体如下：SAABBYYAn*RMdr12'-H和顺序轴助勃据驾合弁计HSiSbWi熟食从果枝果肉类一缓果单熟食瓜果Zi菜面色肉类，$1$5:$Q$5,-D)15至Q5单元格为重新排列辅助区域，作为菜单选项来源区域。15单元格公式为=IFERROR(INDEX($A$1:$F$1,SMA11(IF($A$1:$F$1o"fCO1UMN($A$1:$F$1),49),CO1UMN(A1),"),shift+ctr1+enter三键同时按。右拉拖到到Q5完成辅助数据。数据来源公式：=OFFSET($1$5MATCH(CHAR(I)S1$5:$Q$5,-1)4、消除重复值有时候我们会遇到数据区域里面有重复值的情况，要求在菜单下拉项中只显示一次，不要重复显示。比如下面的情况，菜单下拉项要同时剔除重复值和空值：解决方法：VBA处理，在页面代码区粘贴下面的代码：PrivateSubWorksheet_Se1ectionChange(ByVa1TargetAsRange)IfTargetCoIumn<>9OrTargetCount>1OrTargetRow<2ThenExitSubSetsht=ActiveSheetDimarr,brerr,i&,j&ReDimarr(100)Fori=1To6Ifsht.Ce11s(1zi)<>"ThenForj=1Toi-1Ifsht.Ce11s(1,i)=sht.Ce11s(1,j)ThenGoTo10NextIfj=iThenn=n+1arr(n)=sht.Ce11s(1,i)EndIfEndIf1ONextIfn=0ThenExitSubReDimbrr(1Ton)Fori=1Tonbrr(i)=arr(i)s=Join(brr,z,)NextWithSe1ection.Va1idation.De1ete.AddTypei=X1VaIidate1ist,A1ertsty1ei=X1VaIidAIertStop,Operator:=_x1Between,Formu1a1:=sEndWithSetarr=NothingSetbrr=NothingSets=NothingEndSub二级菜单1、设置方法二级菜单的选项需要根据一级菜单的内容进行联动变化，下图案例中在I列将加粗的第一行内容设置为一级菜单，在J列设置二级联动菜单，设置过程共三步:ABCDEFI工1熟食瓜果蔬菜肉类面包一级菜单二级菜单2卤类瓜类菜类牛肉甜面包二3面点干果类花果类羊肉咸面包rd4凉菜水果类根茎类猪肉法式面包t1）按一级菜单设置方法在I列设置好一级菜单。2）选中基础数据区域A1至E5,点击F5或ctr1+G打开定位条件，选择常量，确定。点击“公式"菜单下“根据所选内容创建“，复选框只保留“首行”为选中状态，确定。完成第一步公式设置。3）选中J列想要设置菜单的单元格，点击“数据菜单下的数据有效性，将“允许"项设置为“序列，单击一下“来源”输入框后，输入公式:=indirect（$I2）,点击确定完成二级菜单设置。2、二级菜单自动更新、消除空值二级菜单也需要预留一些空格区域，同时要求菜单选项里空格不显示出来，比如需要设置第2行至第6行为二级菜单的预留区域，处理方法：D选中基础数据区域A1至E6,点击“公式”菜单下根据所选内容创建“，复选框只保留“首行”为选中状态，确定，完成公式设置。注意此处没有按F5设置常量的过程。2)将“来源”输入框公式“=indirect($I2)”修改为：=OFFSET(IND1RECT($12),0,0,MATCH(CHAR(I)JND1REcT($12),-1),1)。3、消除中间空值和重复值方法可参考一级菜单。多级菜单综合案例有时候我们会同时遇到有空值和重复值的情况，比如下面的超市品类管理案例，A列为一级菜单数据源区域，中间有空值，还有重复值，需要菜单选项里不要出现重复值和空值，同时要求A列数据修改后，菜单选项随之及时更新。这种情况设置起来要用到辅助数据，非常麻烦，我们可以用VBA代码，直接一次性解决。上图在I列点击单元格时出现的下拉菜单选项就剔除了重复项，空值，且能满足菜单及时更新。方法为：打开VBA界面，双击数据所在的表单，粘贴下面的代码。/MDM1)fia½)MVUO)Mt>MB)IMMMKA)*Q0*M½986-X.W1M10X3)SUt4JPSiEEIQ行6,御$MSnBaaAgK3)15ra)玛及ijTrktj1*<CXac离茶英中落二AB*S干红勒a红鬲大*电或才It洗衣机8*axntt*M除汁机电磁炉wasr扫嬉帆Vieeii决胸机的，2VwkU4ta14tt<fMOy*XTw<A»M)t«rc>A1aO>Or2<Jm>1Ck1«rctJ<3TUattaSt«htAet”rr.ter.«».MkRjwOOO)a【30】U4O)U1UbS，”30：MO)JJKCMC«U«G.BO"Xfw>IT»*IXfC>XtGI)CUtQ.I)MSGhI。'XtJ»Ta»»Irv3aCO1s(.I)tMUU4K10j50T2F«rIIT*rv<i)«r<i)eJs0rr.一)XqMoar<*A44TyW1Y54Q12AXrtf<,1333r6fwMr_mUv44kTeX1nBaU1k其中代码行中，下图的target,co1umn<>9代表在I列设置一级菜单，若要在其他列设置菜单，将9修改成列号在字母表对应的序号即可。×i!YorkcheetIS1ctionChanPub1icaAsIxdeg:一级、二蜃:Jxue1对象CheetDm代玛雷代确S法，6)api(x86)ss，二代谒点m理±rss犁助，PrvtSUbWorkshtjS1ctonCh*n(ByV«1T«rg“AxRanQ)fTarget.Co1mnO9getCount>1OrTarZQtRov<3ThenExitSub完整代码为:PrivateSubWorksheet-SctionChange(ByVa1TargetAsRange)IfTargetCoIumn<>9OrTargetCount>1OrTargetRow<3ThenExitSubSetsht=ActiveSheetDimarr,brr,err,i&,j&ReDimarr(100)Ifsht.a3000.End(3).Row<=1ThenExitSubFori=1Tosht.a3000.End(3).RowIfsht.Ce11s(i,1)<>"ThenForj=1Toi-IIfsht.Ce11s(i,1)=sht.Ce11s(j,1)ThenGoToIONextIfj=iThenn=n+1arr(n)=sht.Ce11s(i,1)EndIfEndIf1ONextReDimbrr(1Ton)Fori=1Tonbrr(i)=arr(i)s=Join(brr,)NextWithSe1ectionVaIidation.De1ete.AddType:=x1Va1idate1ist,A1ertSty1e:=x1Va1idA1ertStop,Operator:=_x1Between,FormuIa1i=SEndWithSetarr=NothingSetbrr=NothingSets=NothingEndSubSB