利用Excel数据有效性中的“序列”功能,可以为单元格的数据输入提供可供选择的下拉列表(关于使用数据有效性功能的详细介绍可参阅:第4章),但是当“序列”所引用的数据源包含重复数据项时,下拉列表中也会出现重复的数据项,从而影响数据的选取。此外“序列”所引用的数据区域扩展变化后,下拉列表中的选择项通常不能随之自动更新。

利用技巧66中介绍的动态定义名称的方法结合辅助列,可以设置不含重复数据项的动态更新的数据有效性下拉列表。

如果希望在图所示的数据列表G列“品名”字段下的G2:G14空白单元格区域内创建数据有效性下拉列表,要求在下拉表中显示B列“品名”字段的数据内容,并且不包含其中的重复项,当 A:D 列区域数据增加时,下拉列表中的数据项也能随之自动增加,设置这样的动态不重复数据有效性下拉列表的方法如下。

excel设置多个下拉列表(Excel办公实操利用动态名称)(1)

选定A1:D14单元格区域并创建“表”,如图所示。

excel设置多个下拉列表(Excel办公实操利用动态名称)(2)

在J1单元格中输入“辅助列”字段标题,如图所示。

excel设置多个下拉列表(Excel办公实操利用动态名称)(3)

在【名称管理器】中新建两个名称,名称“a”的引用位置为

:“=Sheet1!$J$2:INDEX(Sheet1!$J:$J,MATCH(TRUE,ISNA(Sheet1!$J$2:$J$100),))”,

名称“品名”的引用位置为:“=Sheet1!$B$2:$B$14”,设置完成后如图所示。

excel设置多个下拉列表(Excel办公实操利用动态名称)(4)

在J2单元格中输入数组公式:

=INDEX(品名,MATCH(0,COUNTIF($J$1:J1,品名),0))

按<Ctrl Shift Enter>组合键完成数组公式的输入,并且将公式向下 填充至J14单元格。

在G2:G14单元格区域中设置【来源】为“=a”的数据有效性。

分别在F、H和I列输入公式,用以根据G列的内容从数据源表中查询和统计相应的数据信息。

在F2单元格中输入公式并向下填充至F14单元格。

=IF(G2=“”,“”,VLOOKUP(G2,IF({1,0},品名,$A$2:$A$14),2,0))

在H2单元格中输入公式并向下填充至H14单元格。

=IF(G2=“”,“”,VLOOKUP(G2,$B$1:$C$14,2,0))

在I2单元格中输入公式并向下填充至I14单元格。

=IF(G2=“”,“”,SUMIF($B$2:$D$14,G2,$D$2))

完成后如图所示。

excel设置多个下拉列表(Excel办公实操利用动态名称)(5)

在B15单元格中输入“CCS-356”,单击G2单元格的下拉箭头,可以发现在下拉表中已经出现了新增加的品名“CCS-356”,如图所示。

excel设置多个下拉列表(Excel办公实操利用动态名称)(6)

当用户使用“表”功能并在“表”区域添加数据时,单元格的右下角会出现【自动更正选项】按钮,单击这个按钮出现扩展列表,如果用户选择【停止自动扩展表】命令,再增加新的数据信息时,“表”将停止自动扩展,如图所示。

excel设置多个下拉列表(Excel办公实操利用动态名称)(7)

,