您的位置: 首页 > EXCEL技巧 > Excel函数 >

数组公式创建无重复项的数据有效性下拉列表

时间:2013-12-13 整理:docExcel.net

有时需要以一列数据为来源制作数据有效性下拉列表,但当该列数据包含重复值时,下拉列表中也会包含这些重复的项目,如下图中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提示“不能更改数组的某一部分”是怎么回事
回答:...格中的公式或修改公式后按回车键,Excel提示“不能更改数组的某一部分”是怎么回事? 答:该单元格中的公式数组公式,并且是多单元格数组公式,即该数组公式为位于多个单元格中的数组公式。如果要修改多单元格数...
用颜色标记包含数组公式的单元格 问题:用颜色标记包含数组公式的单元格
回答:当工作表中包含大量多单元格数组公式时,有时为了方便编辑这些数组公式,可能希望将工作表中的数组公式单独标记出来,以区分非数组公式,这时可以用下面的VBA代码来实现。 选择包含数组公式的工作表,按Alt+F11,打开VBA...
Excel筛选指定尾数手机号码三例 问题:Excel筛选指定尾数手机号码三例
回答:...机号在A2:A125区域中,以B列为辅助列,在B2单元格中输入数组公式: =OR(RIGHT(A2,2)=TEXT(11*(ROW($1:$10)-1), 00 )) 公式输入完毕按Ctrl+Shift+Enter结束,然后向下填充公式。这样,A列中凡是结尾两个数字相同的手机号,在B列中都显示为“TRU...
用数组公式求某个区域中最大的几个值 问题:用数组公式求某个区域中最大的几个值
回答:...出某个数值区域中最大的或最小的几个值,可以用下面的数组公式,假如数值在A1:B10区域中。 1.将公式返回的结果放在某一列中。 求出该区域中最大的3个值,并将其放在D1:D3区域中:先选择D1:D3,然后在编辑栏中输入数组公式...
数组公式获取自动筛选后第一行的行号 问题:数组公式获取自动筛选后第一行的行号
回答:...元格的内容,就需要先取得筛选后第一行的行号。下面的数组公式可以实现这个目的,假如标题在第4行,数据在A5:D500区域并进行了如图所示的自动筛选。 在A2中输入数组公式: =MATCH(1,SUBTOTAL(3,OFFSET(A5,ROW(A5:A500)-ROW(A5),0)),0)+ROW...
在公式中使用数组常量 问题:在公式中使用数组常量
回答:...l中有的函数有两种使用形式,如INDEX函数就有引用形式和数组形式。引用形式返回区域或单元格的引用,数组形式使用数组常量作为参数。数组常量是用大括号括起来的一组常量,可以包含多行和多列,同一行中不同列的数值用...
相关推荐: