Excel(三)之数据验证性——设置多功能下拉菜单

在Excel中,为了录入数据方便,通常会设置下拉菜单。今天,就给大家分享三种设置多功能下拉菜单的方法。

一、更新式下拉菜单

主要方法:超级表+数据验证性

主要内容:通过更新表中的值,下拉菜单中的值也会随之更新

主要步骤:

步骤一:插入表格,修改名称框

选中A列,插入表格,在红框的部分修改名称,并回车(一定要回车!
在这里插入图片描述
步骤二:查看名称框是否修改成功

公式——用于公式——省份(如果上一步没有回车,用于公式是灰色的!
在这里插入图片描述
步骤三:设置下拉菜单
数据——数据验证性——允许(序列)——来源:"=省份" (这里省份就是指的就是A列中的数据,根据选中框也可以看出)
在这里插入图片描述
更新数据:在A列下添加"河南省"、“黑龙江省”,对应的下拉菜单中也会增加,演示图如下
在这里插入图片描述

二、联动式下拉菜单

主要方法:名称管理器+数据验证性

主要内容:不同的下拉菜单之间存在着联系,后者会因为前者的不同选择,呈现出不同的内容(如省—市—区)

主要步骤:

步骤一:建立"省份—市"的名称管理

1.选中A、B列,公式—根据所选内容创建—最左列
在这里插入图片描述
2.打开名称管理器,修改名称指代值
公式—名称管理器—引用位置(选中宣城市至合肥市区域)
在这里插入图片描述
3.设置下拉菜单
在G2单元格,设置下拉菜单:来源设置为安徽省,河北省
在H2单元格,设置下拉菜单:来源设置为=indirect($G$2)

步骤二:建立"市—县"的名称管理

同理,选中B、C两列,将每个市所对应的县建立名称管理器,最后在I单元格设置下拉菜单时,来源设置为=indirect($H$2)即可

最终的实现效果如下:
在这里插入图片描述

三、搜索式下拉菜单

主要方法:offset函数+数据验证性

主要内容:在输入栏输入关键词,即可出现对应的下拉菜单(类似搜索引擎的关键词提示功能)
如下图,输入"韩",则会出现"韩版风衣"、"韩版流行夹克"等
在这里插入图片描述

主要步骤:

步骤一:根据笔画顺序,对商品进行归类
排序—选项—笔划排序
在这里插入图片描述

步骤二:设置下拉菜单

A.数据验证性—允许:序列—来源设置,输入如下公式:

==OFFSET($A$1,MATCH(D2&"*",$A$2:$A$13,0),0,COUNTIF($A$2:$A$13,D2&"*"),1)

公式解释:
1.OFFSET(起始单元格,向下偏移量,向右偏移量,目标单元格行,目标单元格列)

2.MATCH(查询值,查询区域,匹配方式),其中匹配方式0是精确查询,最终返回结果为查询值在查询区域中的位置

3 COUNTIF(区域,条件),返回值为在区域中满足条件的值的个数

综合解释:
以"商品名称"作为起始单元格,向下偏移量肯定是取决于输入的关键词。所以,通过MATCH函数进行匹配,*代表任意字符。向右偏移量为0。COUNTIF所起的作用即是返回同类型的个数n,根据输入的关键词,返回该类型的总个数。由于,返回的单元格必是n行1列,故最后一个参数为1

B.修改出错警告,取消勾选"输入无效数据显示出错警告"
在这里插入图片描述
演示效果如下:在这里插入图片描述

  • 6
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
Excel 级 联动 下拉框 宏代码 实例 代码注释 先在第一个下拉框加入一个valiation, 内容是 =$A$2:$A$5 Private Sub Worksheet_Change(ByVal Target As Range) ' Call back function which defined within according worksheet Dim i As Integer Dim tempStr As String Dim firstDrawBoxRowCount As Integer Dim firstDrawBoxColumn As Integer firstDrawBoxRowCount = 4 'Define the row number of first draw box firstDrawBoxColumn = 1 'Define the column number of ifrst draw box Dim secondDrawBoxRowCount As Integer Dim secondDrawBoxColumn As Integer secondDrawBoxRowCount = 33 'Define the row number of second draw box secondDrawBoxColumn = 4 'Define the column number of second draw box If Target.Column = 1 Then 'This defines the first column of draw box list, you can also define the row number of draw box list Cells(Target.Row, Target.Column + 1) = "" ' Do the clean first Cells(Target.Row, Target.Column + 1).Validation.Delete Cells(Target.Row, Target.Column + 2) = "" Cells(Target.Row, Target.Column + 2).Validation.Delete For i = 2 To firstDrawBoxRowCount + 1 'Enter the cycle to find out the content for column 2 If Trim(Cells(Target.Row, Target.Column)) = Trim(Cells(i, firstDrawBoxColumn)) Then tempStr = Trim(Cells(i, firstDrawBoxColumn + 1)) 'Find out the options for second draw box, it is seperated by , Cells(Target.Row, Target.Column + 1).Select ' Fill the validation to second draw box With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=tempStr .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True
对于Excel中的下拉菜单,您可以按照以下步骤操作: 1. 创建数据源:在工作表中创建个列,分别代表一级、二级和级菜单的数据。 2. 命名区域:选中每个列的数据,点击"公式"选项卡中的"定义名称",为每个列的数据范围分别命名。 3. 创建下拉菜单:选择您希望第一个下拉菜单出现的单元格,然后点击"数据"选项卡中的"数据验证"。在弹出的对话框中,选择"列表"作为验证条件,然后在"来源"输入框中输入第一级菜单的命名区域名称。 4. 设置二级下拉菜单:选择第二个下拉菜单应该出现的单元格,然后再次点击"数据"选项卡中的"数据验证"。在对话框中,选择"列表"作为验证条件,并在"来源"输入框中使用类似于下面这样的公式: =INDIRECT(第一级菜单单元格地址) 这将根据第一级菜单的选择动态确定第二级菜单的范围。 5. 设置下拉菜单:选择第下拉菜单应该出现的单元格,然后再次点击"数据"选项卡中的"数据验证"。在对话框中,选择"列表"作为验证条件,并在"来源"输入框中使用类似于下面这样的公式: =INDIRECT(第二级菜单单元格地址) 这将根据第二级菜单的选择动态确定第级菜单的范围。 6. 测试:现在,您可以通过选择不同的菜单选项来测试您的下拉菜单。 请注意,以上步骤假设您已经了解如何创建命名区域和设置数据验证。如果您对这些基本操作不熟悉,建议您参考Excel的相关文档或教程。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值