之前已经发过一篇多级excel下拉菜单設置公式的设置方法到第三或跟多级excel下拉菜单设置公式时,可能会比较麻烦现在再给大家介绍一种方法,两种方法也可以相互配合使鼡还是以之前的表格为例,只是表格的形式做了些修改
首先,设置一级excel下拉菜单设置公式选中B5,打开数据-数据有效性允许一栏选擇序列,来源一栏可以直接输入公式也可以通过输入框后边的按钮选择范围,最后确定返回到工作表就会发现第一级的excel下拉菜单设置公式已经完成了。
下边重点介绍下第二级excel下拉菜单设置公式的设置方法第二级excel下拉菜单设置公式同样需要一个数据有效性的范围。这个范围需要根据第一级的内容来确定而不是像第一级excel下拉菜单设置公式那样,有一个固定的范围所以像这样,动态确定一个单元格范围嘚方法可以通过OFFSET()函数实现。下边先给出完整的函数然后再做解释。
先选择C5打开数据有效性,其他设置都一样只是在来源一栏输入公式,然后确定即可:
下边解释下上边的公式
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(B5,$F$5:$F$11,0)的意思是查找B5在上图红框中的具体位置返回的是一个数字。
我们发现最终的公式里还有一个countif函数这个函数是统计给定区域内符合条件的值的个数。因为每一个商品目录下商品个数是不同的所以我们在用offset索引时吔要根据商品类型来指定区域的大小,这也是为什么在“手机”“存储卡”“移动电源”这些项前都要加一个”手机数码“的原因就是為了来统计”手机数码“里包含多少个项。
第二级excel下拉菜单设置公式设置成功后第三级excel下拉菜单设置公式就好说了。只需要将第二级excel下拉菜单设置公式里用到的那个公式里所有的B5改为C5,所有的单元格区域$F$5:$F$11改为第三极菜单的区域
后边多级菜单也就比较简单了,方法是一样的
可能我的表述不是很清楚,尤其是在函数介绍那一部分还请各位看官多多包涵,感谢您的阅读请批评指正。
|