与 30万 粉丝一起学Excel
VIP学员的问题,上面是原始表格,下面是修改后的效果,在测试用哪种效果查询金额最好?
布局不同,公式可以相差很大,跟卢子来看看。
1.查询每个月的工资
查询1月的工资,1月在区域中第2列,因此第三参数写2。
=VLOOKUP($B$6,$A$1:$N$4,2,0)
同理,2月就写3,3月就写4,依次类推。现在是向下拖动公式,因此可以用ROW来生成数字。
=ROW(A2)
将公式组合起来,就是最终的。
=VLOOKUP($B$6,$A$1:$N$4,ROW(A2),0)
2.查询项目对应2月的金额
2月是变动的,不能直接写3,可以通过MATCH获取排位。
=MATCH($E$6,$A$1:$N$1,0)
也可以将月字替换掉,再加1。
=SUBSTITUTE($E$6,"月",)+1
综合起来,就得到最终公式。
=VLOOKUP(D7,$A$1:$N$4,MATCH($E$6,$A$1:$N$1,0),0)
或
=VLOOKUP(D7,$A$1:$N$4,SUBSTITUTE($E$6,"月",)+1,0)
3.查询工资对应2月的金额
这个跟案例2用法一样。
=VLOOKUP($H$7,$A$1:$N$4,MATCH($H$6,$A$1:$N$1,0),0)
MATCH跟INDEX、OFFSET结合的情况更多。这里就可以用INDEX+MATCH组合。
=INDEX($A$1:$N$4,MATCH($H$7,$A$1:$A$4,0),MATCH($H$6,$A$1:$N$1,0))
INDEX的语法:
=INDEX(区域,第几行,第几列)
如果事先知道行列数字,就直接写数字。不知道的情况下,都是通过MATCH来获取的,这就有了刚刚那个长公式。
=INDEX($A$1:$N$4,2,3)
这里也可以用OFFSET,不过另一个学员的案例更合适。
4.查询12/27这个日期的前5天的平均价
查询12/27这个日期的前5天的平均价,类似于直接用AVERAGE对区域进行平均值。
=AVERAGE(B8:B12)
日期是变动的,因此无法用固定区域,需要借助函数判断区域。跟上面的案例一样,通过MATCH判断日期在第几行。
=MATCH(D2,A:A,0)
接下来看OFFSET的语法:
=OFFSET(起点,向下几行,向右几列,多少行,多少列)
假如起点是A1。
12/27这个日期是第12行,只需向下11行就行,也就是MATCH减去1。
引用单价,向右1列。
向下引用5行用5,向上引用5行用-5,也就是正数就是向下多少行,负数就是向上多少行。
总共1列,也可以省略不写。
将这些全部结合起来,最终公式就出来了。
=AVERAGE(OFFSET(A1,MATCH(D2,A:A,0)-1,1,-5))
其实,每天的微信文章,就是学员的答疑教程。会将有代表性的问题,整理起来,详细说明,多花点时间来学习,自然能明白各种函数的意思。
陪你学Excel,一生够不够?
推荐:VLOOKUP函数滚一边去,我才是Excel真正的查找之王!
上篇:凭证自动生成,太难了?
请把「Excel不加班」推荐给你的朋友
联系客服