Excel实战技巧82： 排序的数据有效性列表

学习Excel技术，关注微信公众号：

excelperfect

如下图1所示，是一个水果名称列表，我们想让其列表项作为数据有效性下拉列表的内容，且按顺序排列，并且当列表中添加或修改项目时，数据有效性列表会自动更新。

方法1：利用表和公式

如下图2所示，使用了4个辅助列，构建了一个名称为“水果表”的表。

在步骤1对应的列中，使用公式：

=COUNTIFS([水果],"<="&[@水果])

计算该行对应的水果在水果名中的排位（按从低到高的顺序）。

在步骤2对应的列中，使用公式：

=ROW([@水果])-ROW(水果表[[#标题],[水果]])

生成顺序号，即当前行相对于标题行的位置。

在步骤3对应的列中，使用公式：

=MATCH([@计数],[次序],0)

按顺序号找到对应的次序的位置，也就是水果名按次序排列的位置。

在步骤4对应的列中，使用公式：

=INDEX([水果],[@匹配位置])

获取该次序位置对应的水果名，完成排序操作。

将步骤4中表的列命名为：fruit_sorted

选择要设置数据有效性的单元格，对其进行如下图3所示的设置。

结果如下图4所示。

此时，在“水果表”中添加或修改水果名时，上图4中的下拉列表会自动更新。

方法2：使用公式排序

如下图5所示，在列A中是水果名列表，列B中是排序后的水果名列表。

在单元格B2中的公式为：

=LOOKUP(2,1/(COUNTIF(fruits,">="&fruits)=MAX(INDEX(COUNTIF(fruits,">="&fruits)*(COUNTIF(B$1:B1,fruits)=0),0))),fruits)

下拉至单元格B7。

其中，使用了定义的名称fruits，即：

名称：fruits

引用位置：=$A$2:INDEX($A:$A,COUNTA($A:$A))

为确保在添加水果名后，数据有效性列能自动更新，定义名称fruitsbyalpha，即：

名称：fruitsbyalpha

引用位置：=$B$2:INDEX($B:$B,COUNTA($B:$B))

与方法1一样，选择要设置数据有效性的单元格，设置如下图6所示。

结果与上图4相同。