Excel技巧-31 数据验证之设置动态数据源

01插入表格法

这种方法只能使用同一工作表内的数据作为有效性序列来源，可以把这列数据源列在数据区域以外，等设置完后，隐藏数据源列即可。  

  

1、选择数据源任意单元格，依次执行插入-表格，弹出【创建表】对话框。Excel会自动判断数据区域，因为H1单元格是列标题，所以这里保留“表包含标题”的勾选，单击【确定】。    

2、选择需要设置数据验证的单元格区域，依次执行数据-数据验证，在弹出的【数据验证】对话框中，有效性条件选择“序列”，来源输入框中输入=$H$2:$G$8    

3、设置完数据验证后，如果在H列的数据源中增加数据，A列的有效性下拉列表就会自动更新。    

动画演示：

02自定义名称

1、在花名册工作表中自定义名称：data=OFFSET(花名册!$A$2,0,0,COUNTA(花名册!$A:$A)-1,1)，这里名称data可以换成任意字符，包括汉字。范围选择“工作薄”。    

2、在演示数据表中设置数据有效性。有效性条件设置为序列，来源输入框中写上自定义的名称。    

3、自定义名称这种方法可以跨工作表引用有效性序列来源。    

公式解释：

1）COUNTA(花名册!$A:$A)用于计算花名册工作表A列非空单元格的个数，所以A列不允许有空单元格。    

2）OFFSET函数的作用是以指定的引用为参照系，通过给定偏移量返回新的引用。整个公式的意思是，以花名册!$A$2单元格为基点，向下偏移0行，向右偏移0列，新引用的行数为A列非空单元格个数减去1（因为引用区域不包括列标题）。    

3)自定义名称、数据验证、条件格式都适用绝对引用、相对引用，所以单元格地址要加$符号。

动画演示：