欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-26 16:57:42
...

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

一、更新式下拉菜单

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

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

主要步骤:

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

选中A列,插入表格,在红框的部分修改名称,并回车(一定要回车!
Excel(三)之数据验证性——设置多功能下拉菜单
步骤二:查看名称框是否修改成功

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

二、联动式下拉菜单

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

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

主要步骤:

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

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

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

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

最终的实现效果如下:
Excel(三)之数据验证性——设置多功能下拉菜单

三、搜索式下拉菜单

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

主要内容:在输入栏输入关键词,即可出现对应的下拉菜单(类似搜索引擎的关键词提示功能)
如下图,输入"韩",则会出现"韩版风衣"、"韩版流行夹克"等
Excel(三)之数据验证性——设置多功能下拉菜单

主要步骤:

步骤一:根据笔画顺序,对商品进行归类
排序—选项—笔划排序
Excel(三)之数据验证性——设置多功能下拉菜单

步骤二:设置下拉菜单

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.修改出错警告,取消勾选"输入无效数据显示出错警告"
Excel(三)之数据验证性——设置多功能下拉菜单
演示效果如下:Excel(三)之数据验证性——设置多功能下拉菜单

相关标签: EXCEL