Excel中用公式解决关联下拉框的问题

曾经多次研究过关联下拉框的问题,以前一直没找到恰当的方法,现在总算可以比较方便地实现了.

 

在第一个单元格中实现下拉框的方法很简单,在菜单"数据有效性设置"中,将"允许"设置为"序列",在"来源"中可以直接输入序列的数值,中间以半角逗号间隔,如"a,b,c",也可以输入参照其它单元格的地址,如:"=$A$5:$C$5",这里要实现关联下拉框,所以设成地址.因为关联下拉框的内容放在其它单元格里的.

 

那么在与此关联的第二个单元格的"数据有效性"的设置中,也可以将"来源"设成地址,不过这个地址用公式的方法也实现,因为要将第一个单元格中的值作为参数,这样当第一个单元格中选中不同的值的时候,就可以得到不同的地址,从而实现关联下拉框的功能.

 

这个公式就是实现关联下拉框的关键,这个公式是:"=OFFSET(INDEX(A5:C5,MATCH(A1,A5:C5)),1,0,3,1)",其中A1就是参数,关联着第一个单元格,A5:C5是第一个单元格下拉框中的内容,再后面的1,0,3,1是OFFSET的参数,我就不一一解释了,在EXCEL的帮助中有如下的描述:

 

语法

OFFSET(reference,rows,cols,height,width)

Reference    作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。

Rows    相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

Cols    相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

Height    高度，即所要返回的引用区域的行数。Height 必须为正数。

Width    宽度，即所要返回的引用区域的列数。Width 必须为正数。

 

如下图,A1的下拉框里是a,b,c.当A1中选中a时,B1的下拉框里就是1,2,3;当A1中选中b时,B1的下拉框里就是4,5,6;当A1中选中c时,B1的下拉框里就是7,8,9;这正是需要达到的效果,所有下拉框中的内容也显示在画面当中.