吊打Vlookup!这才是Excel的多表查找之王!

挖数网精选
挖数网精选
挖数网精选
355
文章
0
评论
2020-07-2913:07:00 评论 239 1026字
摘要

VLOOKUP已经被介绍的够多了,是时候换家族其他成员上场,并没有万能函数,都是根据实际问题灵活变动。

VLOOKUP已经被介绍的够多了,是时候换家族其他成员上场,并没有万能函数,都是根据实际问题灵活变动。

将明细表按项目名称、月份查找引用到汇总表的E列。

明细表

吊打Vlookup!这才是Excel的多表查找之王!

汇总

吊打Vlookup!这才是Excel的多表查找之王!

两个表的项目名称顺序是一样的,这样处理起来会简单点。

现在要引用2月的金额,2月在明细表F列,可以直接用最简单的公式解决。

=明细表!F3

吊打Vlookup!这才是Excel的多表查找之王!

现在问题来了,当A1单元格的月份变动的时候,希望能够自动引用到相应月份的金额。

当然,这里用最原始的方法也行,每次改变月份,区域就重新引用。

如果要自动改变的话,可以先用MATCH函数判断月份所在列数,2月在第6列。

=MATCH($A$1,明细表!$2:$2,0)

吊打Vlookup!这才是Excel的多表查找之王!

再嵌套OFFSET函数,引用数据。

=OFFSET(明细表!A3,0,MATCH($A$1,明细表!$2:$2,0)-1)

吊打Vlookup!这才是Excel的多表查找之王!

OFFSET函数语法:

=OFFSET(起点,向下几行,向右几列)

起点是明细表!A3,没有加美元符号,这样下拉的时候就自动会改变单元格,就不用向下,也就是向下0行,从列号为1到列号为6,其实只需向右5列,也就是MATCH-1。

到这里,问题本来已经解决了,但要在A1输入公司名称还有年份月份的情况下查找引用。一输入这些,公式就用不了,该如何改进?

吊打Vlookup!这才是Excel的多表查找之王!

其实,有些内容可以采用自定义单元格格式,这样表面看起来一样,实际上还是原来的月份,就可以保留原来的公式。

"Excel不加班2020年"@

吊打Vlookup!这才是Excel的多表查找之王!

既然聊到自定义单元格格式,A1如果只是输入纯数字2,其他都用自定义,公式会更加简单。

"Excel不加班2020年"0月

吊打Vlookup!这才是Excel的多表查找之王!

纯数字,就不用再用MATCH函数判断第几列。

=OFFSET(明细表!D3,0,$A$1)

吊打Vlookup!这才是Excel的多表查找之王!

现在要引用3月的金额,只需将A1的数字改成3即可。单元格看起来什么内容都有,实际在编辑栏只有3这个数字而已。方便输入和查找引用金额。

吊打Vlookup!这才是Excel的多表查找之王!

再进行扩展,实际上,有很多人是将明细表分开成很多工作表,每个月份一张表。

吊打Vlookup!这才是Excel的多表查找之王!

对于这种,最原始的方法就是直接引用某个月份的单元格。

="3月"!E3

吊打Vlookup!这才是Excel的多表查找之王!

A1是纯数字的月份,这里可以嵌套INDIRECT间接引用,这时会出现点小问题,结果全部一样。

=INDIRECT($A$1&"月!E3")

吊打Vlookup!这才是Excel的多表查找之王!

再嵌套ROW函数,可以获取数字3、4、5……

=INDIRECT($A$1&"月!E"&ROW(A3))

吊打Vlookup!这才是Excel的多表查找之王!

好,就到这里,不再继续扩展了。写着写着,好几个函数,其实压根就没有查找引用之王,只有更适合案例的函数而已。

End.

作者:卢子

来源:Excel不加班

本文获得作者授权发布,转载请联系原作者

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: