用OFFSET函数构建二级联动下拉菜单

申明：本公众号下文章，均为小熙本人原创，每个字都是自已码，每张图都是亲自制作请尊重原创，如有方法上的异见，┆欢迎留言交流！

构建二级联动下拉菜单，可以用INDIRECT函数引用名称构建的区域来实现二级下拉菜单。可是如果数据量较多，或者数据源的数据的分布较麻烦时，这种方法就会较麻烦。

这时，我们可以的OFFSET函数来直接构建一个引用的区域实现二级下拉菜单。

怎么完成的呢？以下是数据源。

A列有很多所学校，每所学校的班级代码与班主任名字都列出来了，现在要做一个二级联动下拉菜单，实现快速查找。效果如下：

如果用以前的方法，也是可以完成二级下拉菜单制作。但是根据数据源数据情况，需要花很多时间来整理数据，并且还要定义名称。如何才能快速完成二级下拉菜单的制作呢？

1、制作一级下拉菜单

首先将学校分类下的所有数据复制后粘贴在右边空白单元格内，并全部选中，单击数据——删除重复值，得到如下结果：

选中F2单元格，单击数据——数据验证——数据验证，在允许下选择“序列”，在来源里选择L2：L5

确定后，回到工作表，就制作好了第一级的下拉菜单，此时我们随意选择一个数据。如“中为”

2、制作二级下拉菜单

在如图数据源排列情况下，用OFFSET函数来引用区域，是最好的方法。单击G2选中，单击数据——数据验证——数据验证。在允许下选择“序列”，在来源下框内输入公式：=OFFSET(B1,MATCH(F2,A:A,0)-1,0,COUNTIF(A:A,F2),1)，确定返回即可。

确定后返回到工作表内，选择一级菜单下的学校，你就会发现，二级菜单下的代码会自动显示对应的学校的班级代码。当然其与数据源里顺序与个数完全一致。

=OFFSET(B1,MATCH(F2,A:A,0)-1,0,COUNTIF(A:A,F2),1)这个公式如何理解呢？

OFFSET是一个引用函数，实际上就是引用的一个区域。这个区域会随F2选择不同对象而发生变化。它是如何变化的呢？

在数据源里，公式=OFFSET(B1,MATCH(F2,A:A,0)-1,0,COUNTIF(A:A,F2),1)的意思是：以B1（第一参数）为起点，向下移动MATCH(F2,A:A,0)-1行（MATCH函数是找到F2在A列的位置，其结果是一个数字，因为第一行是标题，所以最后减去1才是要移动的行数，如F2是中为，则中为在A列的位置是12，则以要向下移动的行数是11行才能显示出中为）。因为是以B1为起点，所以，第三参数是0，也就是不移动列数。第四参数是COUNTIF(A:A,F2)

表示要显示的个数，显示其个数就是F2在A列的个数。最后一个参数是默认数值1。最后两个参数的意思就是引用的区域。公式合起来意思可以这样说：以B1为起点，向下移动MATCH(F2,A:A,0)-1，向右移动0列，以此开始，总共COUNTIF(A:A,F2)行，总共是1列显示。

3、用VLOOKUP函数找到对应班主任

在H3单元格内输入公式可以直接显示出对应的班主任。因为代码与班主任是一一对应关系，所以直接用VLOOKUP函数可完成：=VLOOKUP(G2,B:C,2,0)。为防止G2空格时的错误显示，在此公式前嵌套一个IFERROR即可。结果如下 。

最后结果如下视频：

查看网友的精彩评论