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列中的唯一值。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
如何利用Office 365 Teams进行完整的在线会议生命周期管理
![](https://img.aspxhome.com/file/2023/7/16707_0s.jpg)
excel表格如何制作表格
Excel制作单元格下拉菜单
如何在微软Word中添加批注?
![](https://img.aspxhome.com/file/2023/1/23691_0s.jpg)
Excel2010中打印预览设置
![](https://img.aspxhome.com/file/2023/7/38547_0s.png)
Excel表格怎样多行合并居中
![](https://img.aspxhome.com/file/2023/3/a153753_0s.png)
Win10电脑声音有破音怎么办?
![](https://img.aspxhome.com/file/2023/0/51080_0s.png)
Word2019怎么更改文件图标? word2019图标更换方法
![](https://img.aspxhome.com/file/2023/2/30162_0s.jpg)
如何能在excel表格中出现下拉列表?
![](https://img.aspxhome.com/file/2023/1/38221_0s.jpg)
excel 2021如何按分隔符号将同一单元格中的文本快速拆分为多列
![](https://img.aspxhome.com/file/2023/8/a140058_0s.jpg)
Microsoft Word 2003打开里面多了横线怎么清除?
excel怎么复制公式
SUMIFS多字段多条件求和
![](https://img.aspxhome.com/file/2023/7/a142267_0s.png)
office2007安装过程出错1327怎么办?
![](https://img.aspxhome.com/file/2023/4/16004_0s.png)
Word怎么输入左小括号后输入右方括号自动匹配为小括号
![](https://img.aspxhome.com/file/2023/9/31889_0s.png)
如何阻止 Safari 浏览器请求访问数据权限?
![](https://img.aspxhome.com/file/2023/9/46129_0s.jpg)
word 如何快速输入上、下标动画教程
![](https://img.aspxhome.com/file/2023/6/33386_0s.gif)
Excel打印预览快捷键一览
在word中设置正规文档起始页的技巧
在Excel中如何将单元格区域的公式转换成数值?
![](https://img.aspxhome.com/file/2023/8/42268_0s.jpg)