教你制作财务报表多级联动下拉菜单

2019年7月05日06时40分内容来源:Excel不加班

点击上方蓝字「Excel不加班」关注看下一篇



获得书籍人员,加卢子微信chenxilu2019


截止到下午5点,留言区前5名,每人赠送一本Excel效率手册 早做完,不加班


VIP学员的问题:要实现分类、科目代码、科目名称多级联动下拉菜单。负债类对应相应的科目代码和名称,权益类也对应相应的科目代码和名称,效果如gif动画。


科目代码表,有分类、科目代码、科目名称的所有数据。


卢子以前也分享过一二级下拉菜单的制作,而这种布局不适合用以前的方法,难度较大。


Step 01 将分类复制到F列,删除重复值。这一步必须做,要不然直接引用A列,就会导致分类的内容重复显示。


Step 02在下拉菜单这个表,选择A列的区域,点数据,数据验证(数据有效性),序列,引用科目代码F列的区域,确定。


Step 03在下拉菜单这个表,选择B列的区域,点数据,数据验证(数据有效性),序列,输入一条很长的公式,确定。

=OFFSET(科目代码!$B$1,MATCH($A2,科目代码!$A:$A,0)-1,0,COUNTIF(科目代码!$A:$A,$A2))


这条公式是二级下拉的核心公式。先来理解OFFSET函数语法,最后2个参数为可选。

=OFFSET(起点,向下几行,向右几列,总共多少行,总共多少列)


起点,科目代码!$B$1。


向下几行,以负债为例,就是向下1行。这里用MATCH判断第几行。


负债这里得到的是第2,而我们需要的是向下1行,所以得出来的数字再减去1。


向下几行就出来了。

=MATCH($A2,科目代码!$A:$A,0)-1


向右几列,这里不需要向右,也就是0。


总共多少行,也就是数一下负债有多少行,就是多少行。这个可以借助COUNTIF函数解决。


总共多少行,也出来了。

=COUNTIF(科目代码!$A:$A,$A2)


总共多少列,这里可以写1,也可以直接不写。


到这里,OFFSET函数就解释完。


Step 04科目代码跟科目名称是一一对应的,就不需要再做下拉菜单,用VLOOKUP函数查找即可。

=IFERROR(VLOOKUP(B2,科目代码!B:C,2,0),"")


平常看完文章多练习几遍,这样才能将知识记得牢固。


链接:

https://pan.baidu.com/s/1M6TzelJTKa0JkcTCZaBlXQ


提取码:xfl9


陪你学Excel,一生够不够?


一次报名成为VIP会员,所有课程永久免费学,仅需1300元,待你加入。


报名后加卢子微信chenxilu2019,发送报名截图邀请进群。


推荐:又能下拉选择,又能输入新增内容,这样的下拉菜单是怎么样做出来的?

上篇:别吓自己,INDIRECT函数跨表引用真的好简单

假如是你,工作多年工资不高,又上有老,下有小,你会怎么做?



作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

请把「Excel不加班」推荐给你的朋友

最值得关注的微信公众号