二维码 购物车
部落窝在线教育欢迎您!

史上最全的Excel下拉菜单制作教程,从简单到复杂全囊括!

 

作者:E图表述来源:部落窝教育发布时间:2022-06-23 17:10:58点击:1501

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

一说起下拉菜单,我相信大多数有点Excel基础的都会做。但今天我们来点不一样的下拉菜单,从青铜级别到王者级别,应有尽有,赶紧来看一看吧!

 

下面,我们将按照从简单到高级来给大家介绍如下几种下拉菜单。

1、最简单的下拉菜单

2、名称制作下拉菜单

3、动态下拉菜单(没有空白选项的)

4、多内容下拉菜单,填充时拆分内容

5、智能录入下拉菜单,填充时拆分内容

注意:今天主要说的内容是“数据验证(Validation)”的内容,不涉及控件使用,各个Excel版本都可以使用。

 

【正文】

1、青铜(最简单的下拉菜单)

图形用户界面, 应用程序, 表格, Excel

描述已自动生成

制作方法:

在工具栏中,点击“数据验证”功能;

 

 

在弹出的窗口中,按下图:

 

 

在【允许】中选择“序列”,在【来源】中输入“男,女”,按确定键完成操作。

注意:无论选填的内容是什么,都要用“英文的逗号间隔”,而且不需使用双引号。

 

2、白银

应用程序, 表格, Excel

描述已自动生成

 

方法1:在数据验证窗口中,如下设置

 

 

【来源】处可以直接选择单元格区域,即可把这部分的内容作为下拉菜单的内容。

 

方法2:我们也可以使用名称管理器来操作:

选择A2A17单元格,然后在【名称框】中编辑一个名称:例如我们输入“科学家”,然后【按回车结束编辑】

然后在数据验证窗口中如下图设置;

 

 

3、黄金——动态下拉菜单

如果我们的选择项数量不确定,随着添加或者删除,下拉选项也要随之增加减少,如下图所示:

 

图形用户界面, 应用程序, 表格, Excel

描述已自动生成

 

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))),"")}

这个函数是万金油,之前的教程中有解释的,就不在这里赘述了。然后下拉A2单元格填充至A18,就得到了去重后的内容,然后再使用名称即可引用到数据验证中。

但是要注意:表中从B8:B18都是IFERROR函数容错后的空格,那么刚才用COUNTA函数引出选项个数的方式就不正确了,我们换一个写法即可,如下:

 

 

函数如下:

=OFFSET(黄金!$B$2,,,COUNTA(黄金!$B$2:$B$18)-COUNTBLANK(黄金!$B$2:$B$18),)

COUNTA-COUNTBLANK的方式确定应该取多少个选项内容。

图形用户界面, 应用程序, 表格, Excel

描述已自动生成

 

截止到此,【下拉选填菜单】的操作方法,配合【函数】和【自定义名称】还可以适应工作,但是已经显得力不从心,因为随着数据的变化,对于函数和自定义名称的调整是在所难免的。那么【更高级别的下拉选填菜单】我们继续看。

 

4、钻石级别——多内容下拉菜单,填充时拆分内容

 

图形用户界面, 应用程序, 表格, Excel

描述已自动生成

 

下拉菜单选择项包含多个不同属性的内容,选填后自动填入多个单元格。这个方式是使用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, "") '''单元格扩展12列,然后把用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

这种方法对于简单的数据源还是可以采用的,但是对于比较多的内容就不是很方便,例如我们今天的这份数据源——“全国【省、市、县 三层】的智能下拉填充功能”

 

如果同学们用传统方式做的话,自定义名称要做多少呢?

下面看看作者E图表述制作后的效果吧:

 

表格

描述已自动生成

 

这也是用VBA做的,VBA可以做很多日常不敢想想的内容,也只有VBA才能做出高级的功能。不说废话,代码如下:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 7 And Target.Row = 2 Then '''如果被改变值的单元格72列【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 If

End Sub


不需自定义名称,不需多级联动的大量数据验证,这将极大的节省EXCEL的内存,大大提高了运行的效率,同时一级选择、多级录入的方式也节省了操作的环节。

 

写在最后:王者只是人间的强者,而这世间还有更高的存在。人外人、天外天,EXCEL之外有EXCEL,除开上面这种“下拉菜单”,我们还有更高级别的,大家想不想看呢?

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何制作智能的下拉菜单?输入数据模糊匹配对应的下拉选项

最简单的多级下拉菜单制作方法,不需要定义名称

别怕,VBA入门级教程来了,条件语句很简单!

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。