数据有效性 动态选择

EXCEL有两列数据栏，A列和B列，都是通过下来框来选择，要求A列选择完成后，B列动态调整可选择的内容。例如：第一例选择“AA”，第二列可供选择的范围是“a-d”；第一例选择“BB”，第二列可供选择的范围是“e-h”；第一例选择“CC”，第二列可供选择的范围是“i-w”；第一例选择“DD”，第二列可供选择的范围是“w-z”；

 

AA	a	b	c	d
BB	e	f	g	h
CC	i	j	k	w
DD	w	x	y	z

首先设置第一列的数据有效性：

图一

接着设置第二列的数据有效性

图二

公式比较复杂：

=OFFSET($F$13:$I$16,VLOOKUP(INDIRECT("A"&ROW()),$D$14:$E$17,2,0),0,1,4)

 

解读如下：

先看一下基础数据区

基础数据区的信息，范围为：D14：I17

AA	1	a	b	C	d
BB	2	e	f	G	h
CC	3	i	j	K	w
DD	4	w	x	Y	z

公式中用了两层嵌套，在OFFSET函数中嵌套了VLOOKUP函数，在VLOOKUP函数中嵌套了INDIRECT函数。INDIRECT函数用来读取同行第一列选择的数值，VLOOKUP用来返回偏移量，

OFFSET实现偏移。

新增一列作为辅助列，填入序号“1-4”。基础数据区域的第一行作为起始行。

运行结果：