excel数组公式创建无重复项的数据有效性下拉列表
发布时间:2023-07-02 08:15:33
有时需要以一列数据为来源制作数据有效性下拉列表,但当该列数据包含重复值时,下拉列表中也会包含这些重复的项目,如下图中A列包含一些水果名称,在C3单元格中直接用数据有效性创建的下拉列表就包含了多个重复项,显得不够简洁。
要在数据有效性下拉列表中仅显示这列数据中的唯一值,可以先将该列数据中的唯一值提取到辅助列中,再创建下拉列表,具体方法如下:
1.用数组公式提取唯一值。
从一列数据中获取唯一值的公式有很多,如下面的数组公式。假如数据在A2:A21区域,以B列为辅助列,在B2单元格输入:
=INDEX($A$2:$A$21,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$21),0))
公式输入完毕后按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充公式,直到出现“#N/A“为止,这样就将A列中的唯一值提取到B列。
2.定义名称。
由于B列中还包含错误值“#N/A“,用下面的数组公式可动态引用B列中不包含错误值的区域B2:B9:
=OFFSET($B$2,,,MATCH(TRUE,ISERROR($B$2:$B$21),0)-1,1)
但如果直接将该公式用于数据有效性,会出现重新打开工作簿后无法打开下拉列表的问题,因而需将其定义为一个名称后再应用于数据有效性。
Excel 2003:单击菜单“插入→名称→定义”,弹出“定义名称”对话框,在“在当前工作簿中的名称”下的文本框中输入一个名称,如“weiyizhi”,在“引用位置”下输入上述公式,单击“确定”。
Excel 2007/2010:在“公式”选项卡的“定义的名称”组中单击“名称管理器”,分别输入一个名称和上述公式,如图:
单击“确定”,关闭“名称管理器”。
3.设置数据有效性。
假如需要在C3单元格中设置数据有效性下拉列表,选择C3单元格:
Excel 2003:单击菜单“数据→有效性”;
Excel 2007/2010:在“数据”选项卡的“数据工具”组中单击“数据有效性→数据有效性”;
在弹出的“数据有效性”对话框中选择“设置”选项卡,在“允许”下选“序列”,在“来源”下的文本框中输入公式:
=weiyizhi
单击“确定”。这样C3单元格中的数据有效性下拉列表就只显示A列中的唯一值。


猜你喜欢
- excel表格中怎么制作物品使用记录单?想制作一个物品使用记录的表单,该怎么设计你额?下面我们就来看看excel中记录表的制作方法,需要的朋
- 当Win8开始屏幕应用程序越来越多的时候,界面看起来非常杂乱,这时可对开始屏幕的程序进行命名分组,让开始屏幕界面看起来井井有条,那么具体如何
- windows优化大师怎么设置垃圾检测周期长短?我们可以用windows优化大师给电脑进行垃圾检测,那么有小伙伴知道该怎么给windows优
- 为了节省纸张,我们在打印wps文档的时候,一般都会通过设置装订线的方式来实现,那么,怎样设置呢?下面就让小编告诉大家wps文字怎样设置装订线
- Excel宏是什么意思?宏的作用是将操作步骤录制成宏, 然后再执行宏, 可以批量实现一些重复操作。Excel程序开启宏功能的步骤如下。1,打
- WPS软件中怎么将文字设置成倒影效果教学分享。有用户想要学习在WPS软件中设置文字倒影效果的方法,因为有时候可以将重要的标题设置倒影效果,更
- 经常会复制粘贴一些文本,粘贴后会粘贴到各种空格,如果一个一个空格去删除,是不是特别麻烦,此时我们便可以利用表格中的TRIM 函数,利用TRI
- Windows Hello的出现,让微软的Win10以及搭载Windows10的硬件产品拥有了生物识别功能。但是在很长一段时间内,人们只能透
- 饼图99%的人都会做,插入选饼图,选择数据,写好区域,就搞定了。这样的源数据,出来结果类似这结果,但这并不完美,只是入门级水平。执行“插入/
- iOS 13 已经发布有一个半月了,但不少应用并不适配,可能会出现闪退、导致设备发热的问题,所以需要及时进行更新。iOS 13 如何更新应用
- 我们都知道Nvidia控制面板能够对显卡进行设置,提高显卡性能,Win7开启Nvidia控制面板的时候突然打不开了,也不知是哪里出了问题,遇
- 最大值函数:=MAX(起始单元格:结束单元格),最小值函数:=MIN(起始单元格:结束单元格)。(函数名MAX、MIN要大写)。使用方法如下
- 上期内容我们介绍了快速完成批量隔行添加空白单元格的便捷方法,今天,再来带大家了解一下繁琐竖表变简洁横表的小技巧吧~首先,在需要转换的表格右侧
- 拥有电脑是一件很高兴的事,对电脑的设置也是非常好奇,很多人由于是新手不了解电脑的相关知识,这不有用户在清理电脑桌面的时候,小心把“我的电脑”
- 鲁大师是一款专业的硬件检测与防护的软件,平常也会在电脑中安装鲁大师来检测硬件各方面信息,在前面教程中也介绍过无法运行鲁大师以兼容模式运行设置
- PPT主题色如何换色呢?这个恐怕是很多朋友在制作PPT烦恼的问题,本文图文详解了实现PPT主题色一键换色的四种方法。方法一比如说我插入一个矩
- Wps有一个切片器的功能可以在数据筛选时使用,那么我们要如何去插入切片器并修改切片器名称呢?一起来了解一下吧Wps作为国产的办公软件,使用的
- 怎么在Excel中使用数据透视表快速汇总?应该还有很多小伙伴还不知道Excel数据透视表快速汇总的小技巧吧,让我们一起来看看具体的操作吧怎么
- 有些童鞋可能还不了解创建恢复驱动器是什么意思,又或者说是什么意思。创建恢复驱动器是指将一些系统关键东西创建到U盘介质中进行保存,可以用于恢复
- 最近有用户反映,电脑硬盘灯一直亮着,而且电脑也变的非常的卡,这让用户非常苦恼。其实,出现这一问题,很可能是硬盘一直处于工作的状态,重复的在读