Excel函数式编程:不用VBA,实现数据验证(数据有效性)的多选下拉列表 |
您所在的位置:网站首页 › indirect函数数据验证 › Excel函数式编程:不用VBA,实现数据验证(数据有效性)的多选下拉列表 |
![]() 使用Excel的数据有效性(数据验证)实现下拉列表,甚至级联下拉列表,相信大家都很熟悉了。 很多时候我们希望下拉列表可以多选,这是普通的数据验证做不到的。 之前我们介绍过两种方法:一种是使用VBA(参见这篇文章),另一种是使用透视表(参见这篇文章)。 这两种方法都有各自的缺点,VBA比较麻烦,有些环境中还不能使用。另外,很多场景中也不适合(或者是不想)使用透视表。 这里介绍一种纯粹使用Excel函数的方法。 首先来看实际的效果: ![]() 选择的项目都会出现在后面的列表中。 再次选择某个项目时,会从后面的列表中去掉该项目: ![]() 下面介绍实现步骤。 Step 1:创建基本的数据验证我们需要选择的值在B列,所有首先得到所有的不重复列表: =UNIQUE(B2:B11)![]() 然后在K2单元格中设置数据验证, ![]() 选择“序列”,来源为:J3#, ![]() 现在就可以进行下拉选择了, ![]() 不过这个选择只是基本的数据验证选择,不能实现多选。 Step 2:循环引用在文件 → 选项中,选择公式, ![]() 勾选“启用迭代计算”,并将最多迭代次数设置为1。 Step 3:公式在L2中使用公式: =IF(K2 = "", "", IF(ISERROR(SEARCH(K2,L2)), TEXTJOIN(",",1, K2:L2), LET( a, TEXTSPLIT(L2, , ","), IFERROR( TEXTJOIN(",", 1, FILTER(a, a K2)), "" ) ) ))解释如下: 这是一个IF公式,条件是K2(也就是下拉列表的选择)的值等于空白,如果满足,就设置L2的值为空白、否则的话(表示已经选择了某个选项),就需要使用内层嵌套的IF函数分情况处理(即3~12行): 判断K2(选择的值)以前是否选择过(第3行), 如果不是,就将当前的选择值和以前的选择值合并为一个字符串(第4行), 否则的话,就所有的选择值(L2)中去掉该选项(5~10行): 其中,首先将L2(所有选中的选项)拆分为数组(第6行), 然后筛选那些不等于K2当前选择项的内容,并合并为一个字符串(第8行), 这里需要考虑一种特殊情况,即如果最后一个选择项被去掉了,那么第8行的公式会报错,所以我们使用IFERROR函数(第7行)进行处理。 大功告成做到这里就大功告成了!你可以针对这个字符串代表的选项进行各种后续处理了。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |