[Excel] 公式法设置Excel多级下拉菜单

之前已经发过一篇多级下拉菜单的设置方法，到第三或跟多级下拉菜单时，可能会比较麻烦，现在再给大家介绍一种方法，两种方法也可以相互配合使用。还是以之前的表格为例，只是表格的形式做了些修改。

首先，设置一级下拉菜单。选中B5，打开数据-数据有效性，允许一栏选择序列，来源一栏可以直接输入公式，也可以通过输入框后边的按钮选择范围，最后确定。返回到工作表就会发现第一级的下拉菜单已经完成了。

下边重点介绍下第二级下拉菜单的设置方法。第二级下拉菜单同样需要一个数据有效性的范围。这个范围需要根据第一级的内容来确定，而不是像第一级下拉菜单那样，有一个固定的范围。所以像这样，动态确定一个单元格范围的方法，可以通过OFFSET()函数实现。下边先给出完整的函数，然后再做解释。

先选择C5，打开数据有效性，其他设置都一样，只是在来源一栏输入公式，然后确定即可：

=OFFSET(INDEX($F$5:$F$11,MATCH(B5,$F$5:$F$11,0)),0,1,COUNTIF($F$5:$F$11,B5),1)

下边解释下上边的公式。

1、OFFSET: offset(reference, row, cols, [height], [width])， offset有3个必选参数和2个可选参数。offset函数是根据参考位置来进行偏移，从而索引到需要的单元格或单元格区域。第一个参数reference就是参考位置。第2,3个参数分别是偏移的行和列，第4,5个参数表示了单元格区域的高和宽。

假如我们商品类别里选择了手机数码，那么我们希望商品名称的有效性数据为手机数码里包含的项。这时我们将第一个手机数码设置为参考位置，那么通过将偏移量设置为偏移0行，1列，就可以找到“手机”那一项。将高和宽分别设置为3和1，就可以将“手机”“存储卡”“移动电源”全部包含到区域内。

那么怎么找到第一个手机数码呢？这就用到了index函数。index(array, row_num, [column_num])返回的是一个数组中特定行（行列）的值。在本例中数组就是下图的单元格区域。

但是我们怎么知道像“手机数码”“家用电器”等，在数列的第几行呢？这就需要用到match函数。MATCH(lookup_value, lookup_array, [match_type]) ，返回一个值在一个数组中的位置。所以在本例中MATCH(B5,$F$5:$F$11,0)的意思是查找B5在上图红框中的具体位置，返回的是一个数字。

这样，INDEX($F$5:$F$11,MATCH(B5,$F$5:$F$11,0))就根据match函数提供的位置找到了参考位置的索引。

我们发现最终的公式里还有一个countif函数，这个函数是统计给定区域内符合条件的值的个数。因为每一个商品目录下商品个数是不同的，所以我们在用offset索引时也要根据商品类型来指定区域的大小，这也是为什么在“手机”“存储卡”“移动电源”这些项前都要加一个”手机数码“的原因，就是为了来统计”手机数码“里包含多少个项。

第二级下拉菜单设置成功后，第三级下拉菜单就好说了。只需要将第二级下拉菜单里用到的那个公式里所有的B5改为C5,所有的单元格区域$F$5:$F$11改为第三极菜单的区域。

后边多级菜单也就比较简单了，方法是一样的。

可能我的表述不是很清楚，尤其是在函数介绍那一部分，还请各位看官多多包涵，感谢您的阅读，请批评指正。