图片

使用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行）进行处理。

大功告成

做到这里就大功告成了！你可以针对这个字符串代表的选项进行各种后续处理了。

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。