玩转excel下拉菜单,自动更新、剔除重复项、空值,全搞定
在单元格中设置下拉菜单能有效提高表格录入和查找效率,还能规范数据,避免输入错误,是很受欢迎的实用功能。网上很多文章在讲解设置方法时,用的案例数据都很简单规范,实现起来比较容易。但在实际工作中,数据不一定规范,这样生成的菜单往往会存在一些瑕疵、甚至错误。本文针对实际工作数据生成菜单中遇到的问题,分享处理解决方法,和大家一起玩转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没数据时,空白区域不显示在菜单中。方法如下:
修改来源位置的公式为:=OFFSET($A$1,,,1,MATCH(CHAR(1),$A$1:$F$1,-1)),公式里的$A$1:$F$1为预设菜单的区域。
3、消除菜单中间空值
有时候,会遇到菜单来源单元格区域中间某个单元格为空值,比如上面的案例中D1单元格为空,E1格有数据,需要消除菜单中的空白。可以通过设置辅助数据的方式,先将A1至F1单元格区域进行重新排列,将空白值放到区域的末端,再设置来源位置公式,具体如下:
L5至Q5单元格为重新排列辅助区域,作为菜单选项来源区域。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三键同时按。右拉拖到到Q5完成辅助数据。
数据来源公式:=OFFSET($L$5,,,,MATCH(CHAR(1),$L$5:$Q$5,-1))
4、消除重复值
有时候我们会遇到数据区域里面有重复值的情况,要求在菜单下拉项中只显示一次,不要重复显示。比如下面的情况,菜单下拉项要同时剔除重复值和空值:
解决方法:VBA处理,在页面代码区粘贴下面的代码:
二级菜单
1、设置方法
二级菜单的选项需要根据一级菜单的内容进行联动变化,下图案例中在I列将加粗的第一行内容设置为一级菜单,在J列设置二级联动菜单,设置过程共三步:
1)按一级菜单设置方法在I列设置好一级菜单。
2)选中基础数据区域A1至E5,点击F5或ctrl+G打开定位条件,选择常量,确定。点击“公式”菜单下“根据所选内容创建”,复选框只保留“首行”为选中状态,确定。完成第一步公式设置。
3)选中J列想要设置菜单的单元格,点击“数据”菜单下的“数据有效性”,将“允许”项设置为“序列”,单击一下“来源”输入框后,输入公式:=indirect($I2),点击确定完成二级菜单设置。
2、二级菜单自动更新、消除空值
二级菜单也需要预留一些空格区域,同时要求菜单选项里空格不显示出来,比如需要设置第2行至第6行为二级菜单的预留区域,处理方法:
1)选中基础数据区域A1至E6,点击“公式”菜单下“根据所选内容创建”,复选框只保留“首行”为选中状态,确定,完成公式设置。注意此处没有按F5设置常量的过程。
2)将“来源”输入框公式“=indirect($I2)“修改为:=OFFSET(INDIRECT($I2),0,0,MATCH(CHAR(1),INDIRECT($I2),-1),1)。
3、消除中间空值和重复值方法可参考一级菜单。
多级菜单综合案例
有时候我们会同时遇到有空值和重复值的情况,比如下面的超市品类管理案例,A列为一级菜单数据源区域,中间有空值,还有重复值,需要菜单选项里不要出现重复值和空值,同时要求A列数据修改后,菜单选项随之及时更新。这种情况设置起来要用到辅助数据,非常麻烦,我们可以用VBA代码,直接一次性解决。
上图在I列点击单元格时出现的下拉菜单选项就剔除了重复项,空值,且能满足菜单及时更新。方法为:打开VBA界面,双击数据所在的表单,粘贴下面的代码。
其中代码行中,下图的target.column<>9代表在I列设置一级菜单,若要在其他列设置菜单,将9修改成列号在字母表对应的序号即可。
完整代码为:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Column <> 9 Or Target.Count > 1 Or Target.Row < 3 Then Exit SubSet sht = ActiveSheetDim arr, brr, crr, i&, j&ReDim arr(100)If sht.[a3000].End(3).Row <= 1 Then Exit SubFor i = 1 To sht.[a3000].End(3).RowIf sht.Cells(i, 1) <> '' ThenFor j = 1 To i - 1If sht.Cells(i, 1) = sht.Cells(j, 1) Then GoTo 10NextIf j = i Thenn = n + 1arr(n) = sht.Cells(i, 1)End IfEnd If10 NextReDim brr(1 To n)For i = 1 To nbrr(i) = arr(i)s = Join(brr, ',')NextWith Selection.Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=sEnd WithSet arr = NothingSet brr = NothingSet s = NothingEnd Sub