excel制作联想式的下拉菜单|智能下拉菜单|offset|match|countif

作者：YXN-Excel 阅读量：61 发布日期：2023-04-25

这个公式的构建还是比较复杂的，我们需要用到3个函数以及1个通配符，下面我们就来简单的了解下它们

1.match函数：查找数据的位置

语法：=MATCH（查找值，查找区域，匹配类型）

2.countif函数：条件计数

语法=COUNTIF（统计的区域，计数条件)

3.offset函数：偏移函数，函数会一个单元格为原点进行偏移，然后得到一个新的偏移区域

语法：OFFSET（偏移基点，偏移行数，偏移列数，新区域的高度，新区域的宽度）

4.通配符：*号

*号表示：任意多个字符，简单来说它可以代指Excel中的所有字符，可以一个都没有，也可以有无穷多个。我们经常使用连接符号将其与关键字连接在一起，用作数据匹配，在这里也是一样的用法

以上就是我们需要使用的所有函数，最关键的是OFFSET，主要是通过使用OFFSET函数来定义一个动态的区域，达到联想式下拉的效果

首先我们必须要将制作下拉菜单的数据放在一列中，并且排一下序，将一样的型号都放在一起，这一点非常重要。

随后点击一个空白单元格，然后点击【数据】找到【数据验证】选择为【序列】在来源中我们将公式设置为：=OFFSET($A$1,MATCH(D2&"*",$A:$A,0)-1,,COUNTIF($A:$A,D2&"*"))

随后点击【出错警告】找到【输入无效数据时显示出错警告】将它前面的对勾去掉，然后点击确定，至此就制作完毕了

三、原理讲解

=OFFSET($A$1,MATCH(D2&"*",$A:$A,0)-1,,COUNTIF($A:$A,D2&"*"))

第一参数：基点，$A$1，这个是偏移的原点，也就是这一列数据表头【手机型号】
第二参数：偏移行数，MATCH(D2&"*",$A:$A,0)-1，D2就是我们设置下拉菜单的单元格，如果我们在D2中输入小米，match函数就会在A列中查找第一个小米型号出现的位置，减1是为了减去表头，在这里他的结果是 5
第三参数：偏移列数，省略，因为仅仅只有一列数据，所以将其省略掉
第四参数：新区域的高度，COUNTIF($A:$A,D2&"*")，这个函数的作用是计算小米一共有多少个型号，在这里他的结果是5
第五参数：新区域的宽度，因为仅仅只有一列数据，所以宽度可以省略掉

这个就是函数的计算过程，下图灰色区域就是offset函数得到的结果区域，正好是小米星号对应的区域

说实话这个公式还是比较难理解的，大家如果实在看不懂，直接套用这个公式即可，只需更改2处

1.第一参数中的$A$1，更改为你表格中对应的表头位置

2.第二与第四参数中的D2，更改为你设置下拉的单元格位置即可

YXN-Excel

2023-04-25