〖会计知识〗
只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类)
找对比,你会首先想到Vlookup函数。但在Excel中只会Vlookup函数是远远不够的。今天小K对查找公式进行一次全面的整理。(注:一题多解的只选取最优公式)
1、普通查找
查找李晓峰的应发工资
=VLOOKUP(H2,B:F,5,0)
2、反向查找
查找吴刚的员工编号
=INDEX(A:A,MATCH(H2,B:B,0))
3、交叉查找
查找3月办公费的金额
=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)
4、多条件查找
查找上海产品B的销量
=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)
5、区间查找
根据销量从右表中查找提成比率。
=LOOKUP(A2,$D$2:$E$5)
6、双区间查找
根据销量和比率完成情况,从表中查找返利。
=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))
7、线型插值
如下图所示,A列是数量,B列是数量对应的系数值。现要求出数字8所对应的系数值。
=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)
8、查找最后一个符合条件记录
如下图所示,要求查找A产品的最后一次进价。
=LOOKUP(1,0/(B2:B9=A13),C2:C9)
9、模糊查找
如下图所示,要求根据提供的城市从上表中查找该市名的第2列的值。
=VLOOKUP("*"&A7&"*",A1:B4,2,0)
10、匹配查找
如下图所示,要求根据地址从上表中查找所在城市的提成。
=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)
11、最后一个非空值查找
如下图所示,要求查找最后一次还款日期
=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)
12、多工作表查找
【例10】从各部门中查找员工的基本工资,在哪一个表中不一定。
方法1
=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))
方法2:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)
13、一对多查找
【例】根据产品查找相对应的所有供应商
A2 =B2&COUNTIF(B$1:B2,B2)
B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")
14、查找销量最大的城市
查找销量最大的城市(数组公式按ctrl+shift+enter三键输入)
{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}
15、最接近值查找
根据D4的价格,在B列查找最接近的价格,并返回相对应的日期
(数组公式按ctrl+shift+enter三键输入)
{=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}
15、跨多文件查找
跨多个文件查找,估计你搜遍网络也找不到,这也是兰色首次编写跨多文件查找公式。其实原理和跨多表查找一样,也是借助lookup等函数实现。
文件夹中有N个仓库产品表格,需要在“查询”文件完成查询
仓库表样式
在查询表中设置公式,根据产品名称从指定的文件中sheet1工作表查询入库单价

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a"),A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)。
vlookup函数的多文件查找,同样可以用iferror+vlookup的模式,公式虽然长,但容易理解且公式不容易出错。另外,如果excel文件有几十个或更多,就需要使用宏表函数用Files获取所有excel文件名称,然后应用到公式中,具体的公式这里就不再写了。
能想到的查找公式差不多都列出来了,其中有不少公式需要有一定基础才能看懂。如果看不明白先学会套用以后再慢慢理解。
相关文章
房地产税真要来了!现在知道这些还不晚!
财政部部长肖捷日前在《党的十九大报告辅导读本》中谈及房地产税,明确未来的房地产税将按照房屋评估值征收。要按照“立法先行、充分授权、分步推进”的原则,推进房地产税立法和实施。对工商业房地产和个人住房...
经典总结!更贴合实际的会计做账流程,还不收?
每月月末和月初是会计最为忙碌、最为重要的时间,一个月的工作结果都要在这几天进行归集、编制报表和纳税申报。越是忙乱的时候,越容易出现差错,因此会计应将每月的工作进行归类,区分轻重缓急,不要盲目的工作。大...
私车公用的税务处理并不难!一次性给你说明白!
私车公用烦死我了!随着这几年创业公司的增加,很多企业都面临这样一个问题:企业没法摇到车牌,没办法只能租用汽车;企业公车不够,市场部员工出去拓展业务,只能开着自己的车出去开展活动,然后拿着过路费发票和加...
以后做账可得小心了!一笔会计分录引发500万个税案
A公司的股东张某怎么也没有想到,一笔看似普通的会计分录,经北京市地税局第一稽查局检查后,需要其补缴个税500万元,在明确清晰的税收法规面前,张某哑口无言,只好如实补缴了个人所得税。案件经过还原:&nb...
行业巨变!会计成为2018“高危淘汰”型职业,每个人都需要这本证书!
2017年就剩最后一个月了,时间过得真快!过去的十一个月里,你都经历了什么呢?年初给自己的定的“小目标”实现了多少呢?比如,想去几个地方旅行,想要考几个证,虽然每一年的“小目标”都不见得能够完成,但是...
注意!公司资金往来存入个人账户,太容易被稽查了!
长期以来,公司老板为了避税、少缴税款,用自己的个人银行账户收支公司的资金往来,也是常有的事儿~但随着国家税务稽查力度加大,公司老板个人银行账户也纳入稽查范围,不再“安全”了!时时监控系统案例介绍案例一...
发表评论
