您现在的位置是:网站首页 > 博客日记 >

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

作者:YXN-Excel 阅读量:50 发布日期:2023-04-25

效果图

一、3个函数1个符号

这个公式的构建还是比较复杂的,我们需要用到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)-1D2就是我们设置下拉菜单的单元格,如果我们在D2中输入小米match函数就会在A列中查找第一个小米型号出现的位置,减1是为了减去表头,在这里他的结果是 5
  • 第三参数:偏移列数,省略,因为仅仅只有一列数据,所以将其省略掉
  • 第四参数:新区域的高度,COUNTIF($A:$A,D2&"*"),这个函数的作用是计算小米一共有多少个型号,在这里他的结果是5
  • 第五参数:新区域的宽度,因为仅仅只有一列数据,所以宽度可以省略掉

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

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

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

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

 

原文: Excel从零到一

YXN-Excel

2023-04-25