〖会计知识〗

〖会计知识〗

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类)

2018-08-28 热度:1240 ℃


找对比,你会首先想到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文件名称,然后应用到公式中,具体的公式这里就不再写了。

能想到的查找公式差不多都列出来了,其中有不少公式需要有一定基础才能看懂。如果看不明白先学会套用以后再慢慢理解。


相关文章

重大消息!会计职称新增一本证书!含金量秒杀初级!

重大消息!会计职称新增一本证书!含金量秒杀初级!

人社部近日印发的《关于在部分系列设置正高级职称有关问题的通知》,明确目前未设置正高级的职称系列均设置到正高级。这一决定其实早有伏笔:早在16年11月,《关于深化职称制度改革的意见》便指出:会计专业技术...

超全汇总:会计高手口口相传的心法要诀,就你没背了!

超全汇总:会计高手口口相传的心法要诀,就你没背了!

会计高手口口相传的心法要诀,快背熟了!会计工作管理体制中国是个大家庭,管理体制不能省;财政统一来管理,分级管理到县级。全国制度财政定,具体办法有不同;特殊行业要审批,军队备案就可行;单位领导管内部,真...

告诉你一个真实的私企会计工作!!会计行业有什么潜规则?血泪经验,长篇总结

告诉你一个真实的私企会计工作!!会计行业有什么潜规则?血泪经验,长篇总结

随笔者拥有多间私企的从业经历,而且不仅限于会计工作,因此对私企环境、工作经验非常了解。从自身的所见所闻所感出发,本文设计私企的方方面面,包括私企的老板、财务实际操作、做账环境、内帐外帐等等各方面。我在...

【会计知识】会计工作的4张流程表,收好不谢!

【会计知识】会计工作的4张流程表,收好不谢!

会计人不仅仅需要和各种各样的人打交道,还需要和各类报表打交道,随着会计工作的深入,越来越多的事情占据了会计人的大部分时间,总感觉手忙脚乱?4张表帮助大家高效工作。会计新手需要了解的会计各类岗位工作流程...

2018年,税务加大力度重点稽查个人银行账户!!

2018年,税务加大力度重点稽查个人银行账户!!

长期以来,公司老板为了避税、少缴税款,用自己个人银行账户收支公司的资金往来,是常有的事儿~但2018年,随着国家税务稽查力度加大,公司老板个人银行账户也纳入稽查范围,不再“安全”了!2018清查开始!...

【会计知识】财务预算编制6大方法

【会计知识】财务预算编制6大方法

企业财务预算可以根据不同的预算项目,分别采用固定预算、弹性预算、增量预算、零基预算、定期预算和滚动预算等方法进行编制。1固定预算固定预算又称静态预算,是把企业预算期的业务量固定在某一预计水平上,以此为...

发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。