全文2300字,预计8分钟读完 下拉菜单,我相信有点Excel基础的都会做。 但今天我们来归纳总结一下,从简单到高级,从青铜到王者,应有尽有,一起看看你属于哪个等级吧? 1、最简单的下拉菜单 2、名称制作下拉菜单 3、动态下拉菜单 4、多内容下拉菜单,填充时拆分内容 5、智能录入下拉菜单,填充时拆分内容 全文有点长,建议收藏慢慢看。 注意:今日内容,所有Excel版本皆适用。 编辑|六姑娘 作者 | E图表述 1、青铜(最简单的下拉菜单) 在工具栏中,点击“数据验证”功能,在弹出的窗口中,按下图进行操作。 注意:无论选填的内容是什么,都要用“英文的逗号间隔”,而且不需使用双引号。 2、白银(下拉菜单的内容很多) 方法1:在数据验证窗口中,按下图所示设置,【来源】处直接选择单元格区域。 方法2:使用名称管理器 选择A2:A17单元格,然后在【名称框】中编辑一个名称:例如我们输入“科学家”,然后回车。 注意:如果是多列数据,我们可以再点击【公式】-【名称管理器】,修改【科学家】的引用范围。 然后在数据验证窗口中如下图设置; 3、黄金——动态下拉菜单 如果选择项数量不确定,随着添加或者删除,下拉选项也要随之改变。 按CTRL+F3组合键,打开【名称管理器】窗口,新建一个名称“选项”,按下图设置名称内容: 引用位置的函数:=OFFSET(黄金!$A$2,,,COUNTA(黄金!$A$2:$A$997),) 使用COUNTA函数确定区域中的内容数量,然后在使用数据验证创建下拉菜单即可。 提取唯一值后再制作下拉菜单 如果A列数据是使用函数后得到的,例如把某列数据去重,如下图: A2单元格的函数为: {=IFERROR(INDEX($A$2:$A$38,SMALL(IF(MATCH($A$2:$A$38,$A$2:$A$38,0)=ROW($1:$37),ROW($1:$37),9^9),ROW(B1))),"")} 然后下拉至A18,得到了去重后的内容,然后再使用名称即可引用到数据验证中。 但是要注意:表中从B8:B18都是IFERROR函数容错后的空格,那么刚才用COUNTA函数引出选项个数的方式就不正确了,我们换一个写法即可,如下: 函数如下: =OFFSET(黄金!$B$2,,,COUNTA(黄金!$B$2:$B$18)-COUNTBLANK(黄金!$B$2:$B$18),) 用COUNTA-COUNTBLANK的方式确定应该取多少个选项内容。 4、钻石级别——多内容下拉菜单,填充时拆分内容 下拉菜单选择项包含多个不同属性的内容,选填后自动填入多个单元格。这个方式是使用VBA操作的,代码如下: 代码太长,请向上滑动阅览 Private Sub Worksheet_Change(ByVal Target As Range) '''单元格值改变,触发事件 If Target.Column = 4 And Target.Count = 1 Then '''判断是否在D列,选择一个单元格 With Target '''使用这个单元格 s = .Text '''单元格的值赋值给一个变量s .Resize(1, 2) = Split(s, ":") '''单元格扩展1行2列,然后把用Split函数按冒号拆分的数组赋值到单元格区域 End With '''with结束语句 End If '''结束判断 End Sub '''工程结束 Private Sub Worksheet_SelectionChange(ByVal Target As Range) '''选择单元格改变,触发事件 If Target.Column = 4 And Target.Count = 1 Then '''判断是否在D列,选择一个单元格 With Sheets("钻石") s = Join(Application.Transpose(.Range("A2:A" & .[A65000].End(3).Row)), ",") '''将A列从A2开始,有值的单元格Join成一个字符串,用英文逗号间隔 End With With Selection.Validation '对所选择的单元格,创建数据有效性 .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=s .IgnoreBlank = True '设置单元格 允许空值 .InCellDropdown = True '提供下拉列标 End With End If '结束判断 End Sub '''工程结束 按下图粘贴或者编写代码亦可。 5、王者——智能录入下拉菜单,填充时拆分内容 多级联动的下拉菜单,你是如何做的呢? 按首行批量创建自定义名称,然后再INDIRECT? 这种方法对于简单的数据源还是可以采用的,但是对于比较多的内容就不是很方便。 例如我们今天的这份数据源——全国【省、市、县 三层】的智能下拉填充。 下面看看效果吧: 代码太长,请向上滑动阅览 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.Row = 2 Then '''如果被改变值的单元格7行2列【G2】单元格时 With Sheets("王者") '''使用《王者》工作表 arr = .Range("A2:C" & .[A100000].End(3).Row) '''将从A2单元格开始到A列已使用的末行结束的单元格区域赋值数值arr s = Target.Text '''将被操作单元格的值赋值给变量s If s <> "" Then '''如果s不为空值 For i = 1 To UBound(arr) '''循环数组arr st = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) '''将省市县内容合并,并且用|间隔 If st Like "*" & s & "*" Then '''如果st字符串包含s变量的内容 svd = svd & st & "," '''将st赋值给svd变量,并用英文逗号间隔,作为下拉菜单的内容 End If Next i End If With .Range("G3").Validation '对G3单元格创建数据有效性 .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=svd .IgnoreBlank = True '设置单元格 允许空值 .InCellDropdown = True '提供下拉列标 End With .[G3] = "" '''G3单元格初始值为空 End With End If If Target.Column = 7 And Target.Row = 3 And Target.Text <> "" Then '''如果G3单元格的值被改变,且值不为空时 With Sheets("王者") a = .[H100000].End(3).Row + 1 '''确定H列中为空的第一行 .Cells(a, 8) = Split(Target.Text, "|")(0) '''Split方法拆分下拉菜单的内容,将第1个值放入第8列的末行空白行 .Cells(a, 9) = Split(Target.Text, "|")(1) '''同上,放入第2个值 .Cells(a, 10) = Split(Target.Text, "|")(2) '''同上,放入第3个值 '''注意:Split方法拆分后,是一个一维数组。vba中的内存数组序号默认是从0开始计数的 .[G3] = "" '''G3单元格再归零 End With End IfEnd Sub |
|