Excel咨询砖家|下拉菜单的“万能”公式，10级菜单，瞬间搞定【excel教程】
编按：
哈喽 ， 大家好！使用多级菜单录入数据不仅能保证录入数据的规范性 ， 还能大大提高我们的工作效率 ， 今天给大家分享一个使用OFFSET、MATCH、COUNTIF函数结合的公式套路 ， 不管是制作二级、三级、四级甚至更多级的菜单 ， 通通一个公式全搞定 ， 赶紧来看看吧！
传统的方法 ， 要做出二级三级菜单 ， 少不了定义名称这个步骤 ， 而且对于菜单内容（数据源）的排列方式要求比较高 ， 并且当不同选项下的内容数量不一样多时 ， 下拉选项中会出现空白项 。
今天要分享的多级菜单制作方法 ， 在操作上大大降低了难度 ， 而且不管制作多少级的下拉菜单 ， 都是一个公式套路搞定 。 还是用一个省、市、区的数据来做介绍 ， 数据源如下 。
本文插图
在进行下拉菜单的设置之前 ， 还是需要对这个原始数据源做点处理 ， 不过非常简单 。
第一步：将省这一列复制出来 ， 删除重复项 。
本文插图
第二步：将省市这两列复制出来 ， 删除重复项 。
本文插图
第三步：将市区这两列复制出来 ， 因为数据只有三级 ， 所以市区是不会有重复项的 。
如果还有四级五级菜单 ， 相信也知道该如何处理了吧 ， 至此 ， 数据源就处理完成了 。
接下来进入下拉菜单的设置 ， 同样非常简单 。
一级菜单设置 ， 直接使用数据验证（数据有效性）最基本的序列即可 。
本文插图
注意：一级菜单的内容相对比较固定 ， 所以直接选择数据源区域即可 。 这里下拉选项的位置和数据源的位置是为了动画演示方便才放置到一个sheet里 ， 实际使用中 ， 数据源可以单独存放在一个sheet里 。 下拉选项的位置根据自己的需要灵活设置即可 。
二级菜单设置 ， 这一步开始 ， 就要用到今天的主角了 ， 由OFFSET、MATCH和COUNTIF共同构造的一个公式套路 ， 公式为：
=OFFSET($R$1,MATCH(G2,Q:Q,0)-1,,COUNTIF(Q:Q,G2))
千万不要被这个公式吓住 ， 其实这个公式是很好理解的 ， 以下就为大家破解这个公式的秘密 。
首先我们要明白OFFSET这个函数是干什么的 。
简单来说 ， OFFSET是一个引用函数 ， 可以为我们得到一个特定的单元格区域（可以理解为得到该区域中的一组数据） ， 例如上面这个公式表面上得到的是一个错误值：
本文插图
其实当我们在编辑栏选中公式 ， 按F9键以后 ， 看到的是这样的结果：
本文插图
之所以显示错误值 ， 是因为在一个单元格里无法显示出一个区域（四个单元格）的内容 。
也就是说 ， 公式得到了福建省所对应的市所在的区域 ， 当省（G2单元格）的内容变化以后 ， 公式结果也会随之变化 ， 还是通过F9键来看看变化后的结果 。
本文插图
或许大家发现了 ， 这里的数据是智能调整的 ， 也就是说 ， 对应几个市就显示几个市 。
为什么会有这样的效果呢 ， 这就要从OFFSET的五个参数来说起了 。
OFFSET（起始位置 ， 行偏移量 ， 列偏移量 ， 高度 ， 宽度） ， 一般的教程里会这样解释OFFSET的五个参数 ， 本例中 ， 只用到了其中的1、2、4三个参数 。
推荐阅读
- 电脑使用技巧|Word文字技巧—如何将Excel转换成Word
- 考勤表|Excel怎么制作考勤表模板？能自动更新日期和统计考勤
- 电脑使用技巧|教你几招，分分钟让Excel不规范的数据井井有条！
- 电脑使用技巧|Excel函数——日期函数(日期、星期、工作日函数等)
- 会计凭证|Excel这样操作，会计凭证、工资条、考勤表模板（自动生成）.xls
- 行业互联网|Q3全球面板采购量排名来了 小米居首
- 互联网|使用excel制作四象限图，公司产品好坏一目了然
- 泛博瑞咨询|618荣耀初露锋芒 后市OLED竞争加剧
- 一只想飞的e|数据分析之-EXCEL初识
- 上游新闻|艾媒咨询：5年后共享电单车收入规模预计将达到200亿元