VLOOKUP已经被介绍的够多了,是时候换家族其他成员上场,并没有万能函数,都是根据实际问题灵活变动。
将明细表按项目名称、月份查找引用到汇总表的E列。
明细表
汇总
两个表的项目名称顺序是一样的,这样处理起来会简单点。
现在要引用2月的金额,2月在明细表F列,可以直接用最简单的公式解决。
=明细表!F3
现在问题来了,当A1单元格的月份变动的时候,希望能够自动引用到相应月份的金额。
当然,这里用最原始的方法也行,每次改变月份,区域就重新引用。
如果要自动改变的话,可以先用MATCH函数判断月份所在列数,2月在第6列。
=MATCH($A$1,明细表!$2:$2,0)
再嵌套OFFSET函数,引用数据。
=OFFSET(明细表!A3,0,MATCH($A$1,明细表!$2:$2,0)-1)
OFFSET函数语法:
=OFFSET(起点,向下几行,向右几列)
起点是明细表!A3,没有加美元符号,这样下拉的时候就自动会改变单元格,就不用向下,也就是向下0行,从列号为1到列号为6,其实只需向右5列,也就是MATCH-1。
到这里,问题本来已经解决了,但要在A1输入公司名称还有年份月份的情况下查找引用。一输入这些,公式就用不了,该如何改进?
其实,有些内容可以采用自定义单元格格式,这样表面看起来一样,实际上还是原来的月份,就可以保留原来的公式。
"Excel不加班2020年"@
既然聊到自定义单元格格式,A1如果只是输入纯数字2,其他都用自定义,公式会更加简单。
"Excel不加班2020年"0月
纯数字,就不用再用MATCH函数判断第几列。
=OFFSET(明细表!D3,0,$A$1)
现在要引用3月的金额,只需将A1的数字改成3即可。单元格看起来什么内容都有,实际在编辑栏只有3这个数字而已。方便输入和查找引用金额。
再进行扩展,实际上,有很多人是将明细表分开成很多工作表,每个月份一张表。
对于这种,最原始的方法就是直接引用某个月份的单元格。
="3月"!E3
A1是纯数字的月份,这里可以嵌套INDIRECT间接引用,这时会出现点小问题,结果全部一样。
=INDIRECT($A$1&"月!E3")
再嵌套ROW函数,可以获取数字3、4、5……
=INDIRECT($A$1&"月!E"&ROW(A3))
好,就到这里,不再继续扩展了。写着写着,好几个函数,其实压根就没有查找引用之王,只有更适合案例的函数而已。
End.
作者:卢子
来源:Excel不加班
本文获得作者授权发布,转载请联系原作者
- 我的微信公众号
- 微信扫一扫
- 我的微信公众号
- 微信扫一扫
评论