学习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相同。
