[office]excel中实现二级联动菜单

在Excel中，我们经常会有类似下面这样的二级联动的需求。

比如说：选择某个省份了以后，“城市”所对应的列表根据所选城市而变化。

基础信息：省份一览、各省份的城市一览

城市的下拉列表的内容，根据省份而变化

1、省份的下拉列表

下拉框的内容指向省份的一览

2、城市的下拉列表**

中间的公式如下：

=OFFSET($N$2,1,MATCH($C$2,$N$2:$P$2,)-1,COUNTA(OFFSET(N2,1,MATCH(C2,N2:P2)-1,10,1)))

有点长，用到了OFFSET、MATCH、COUNTA，分别解释一下。

详细的用法不介绍了，把参数的意义罗列一下。

• OFFSET：以参考点为基准，偏移若干后返回一个范围。

offset(参考原点,向下偏移，向右偏移，行数，列数)

例：OFFSET(C3,2,3,1,1)

• MATCH：MATCH(搜索项,范围)

例：MATCH(C2,N2:P2)

在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

• COUNTA：返回区域内不为空的cell的个数。

再一层一层地看一下具体的公式。

OFFSET($N$2,1,（1）,（2）):返回所选省份下的所有城市。如果C2选择的是山西省，则返回O3到O6的城市的范围。

各个参数的意义如下：

N2：数据区域的原点。

以N2为原点，向下偏移一行，也就是该省份的第一个城市。

(1)：向右偏移的列数。

      向右偏移（1）列，如果C2中选中的是河北省，则向右偏移0列，如果选中的是山西省，则向右偏移1列。

(2)：向下偏移的行数。

      该值为所选省份下城市的个数。

再看（1） 和（2）

（1）：MATCH($C$2,$N$2:$P$2,)-1

在N2到P2的范围内，匹配到C2的值的列数。-1的目的是从1开始计数，而offset需要的是从0开始计数。

比如C2选中了山西省，则（1）的值为1。

（2）：COUNTA(OFFSET(N2,1,MATCH(C2,N2:P2)-1,10,1))

先看里面 OFFSET(N2,1,MATCH(C2,N2:P2)-1,10,1)

MATCH(C2,N2:P2)-1也就是（1），目的是找到省份的列。

OFFSET(N2,1,MATCH(C2,N2:P2)-1,10,1)是以N2为原点，向下偏移1行，向右偏移到省份的列，取10行1列的范围。

这里面的10是不完美的地方。如果一个省超过10个城市的话，10个以后的城市将取不到了。这个值可以取大一些。这个不完美的地方，另文再议。

（2）的整个公式的意思是省份的列中，从第一个城市往下取10个单元格中，不为空的个数。

比如O列的山西省下，不为空的城市个数为4。

这样就可以实现了。

但是

1）由上面(2)的不完美的地方。

2）另外有个方法，另文再叙。