〖会计知识〗

〖会计知识〗

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

3年前 热度:833 ℃



找对比,你会首先想到Vlookup函数。但在Excel中只会Vlookup函数是远远不够的。今天小编对查找公式进行一次全面的整理。(注:一题多解的只选取最优公式


1、普通查找

查找李晓峰的应发工资

=VLOOKUP(H2,B:F,5,0)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第1张

2、反向查找

查找吴刚的员工编号

=INDEX(A:A,MATCH(H2,B:B,0))

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第2张

3、交叉查找

查找3月办公费的金额

=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第3张

4、多条件查找

查找上海产品B的销量

=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第4张

5、区间查找

根据销量从右表中查找提成比率。

=LOOKUP(A2,$D$2:$E$5)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第5张

6、双区间查找

根据销量和比率完成情况,从表中查找返利。

=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))


只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第6张

7、线型插值

如下图所示,A列是数量,B列是数量对应的系数值。现要求出数字8所对应的系数值。

=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)


只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第7张

8、查找最后一个符合条件记录

如下图所示,要求查找A产品的最后一次进价。

=LOOKUP(1,0/(B2:B9=A13),C2:C9)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第8张

9、模糊查找

如下图所示,要求根据提供的城市从上表中查找该市名的第2列的值。

=VLOOKUP("*"&A7&"*",A1:B4,2,0)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第9张

10、匹配查找

如下图所示,要求根据地址从上表中查找所在城市的提成。

=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第10张

11、最后一个非空值查找

如下图所示,要求查找最后一次还款日期

=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第11张


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)

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第12张

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第13张

13、一对多查找

【例】根据产品查找相对应的所有供应商

A2 =B2&COUNTIF(B$1:B2,B2)

B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第14张

14、查找销量最大的城市

查找销量最大的城市(数组公式按ctrl+shift+enter三键输入

{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第15张

15、最接近值查找

根据D4的价格,在B列查找最接近的价格,并返回相对应的日期

数组公式按ctrl+shift+enter三键输入

{=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第16张


15、跨多文件查找

跨多个文件查找,估计你搜遍网络也找不到,这也是小编首次编写跨多文件查找公式。其实原理和跨多表查找一样,也是借助lookup等函数实现。

文件夹中有N个仓库产品表格,需要在“查询”文件完成查询


只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第17张


仓库表样式

只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第18张


在查询表中设置公式,根据产品名称从指定的文件中sheet1工作表查询入库单价


只会Vlookup函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第19张

=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函数Out了!Excel所有查找公式全在这儿(共16大类) 〖会计知识〗 第20张

vlookup函数的多文件查找,同样可以用iferror+vlookup的模式,公式虽然长,但容易理解且公式不容易出错。另外,如果excel文件有几十个或更多,就需要使用宏表函数用Files获取所有excel文件名称,然后应用到公式中,具体的公式小编就不再写了。

分享给朋友:

相关文章

主营业务收入、其他业务收入和营业外收入的区别,就是皇后、令妃和夏雨荷的区别

主营业务收入、其他业务收入和营业外收入的区别,就是皇后、令妃和夏雨荷的区别

今天用轻松的方式,让大家来更好的理解枯燥乏味的会计用语,看完全文保证你记全了!主营业务收入 / 其他业务收入 / 营业外收入主营业务收入就是做正事的收入,其他业务收入就是搞副业的收入,营业外收入就是“...

值得所有会计收藏!企业发放工资薪金应避免这些常见6大问题

值得所有会计收藏!企业发放工资薪金应避免这些常见6大问题

关注多练会计,点击上方蓝字▲多练会计,满满的会计实务干货哦! 前言: 近日,北京市通州区地税局在日常检查中发现,纳税人在进行工资薪金个人所得税申报时存在一些突出问题。本文结合实例分析,提出企业在为员工...

【会计知识】不能开具的六种发票和不得计提折旧扣除的固定资产!

【会计知识】不能开具的六种发票和不得计提折旧扣除的固定资产!

国际社会普遍认为,营改增展示了中国财税部门高效的执行力,不仅直接降低了企业税收负担,而且助推了供给侧结构性改革和企业转型升级,鼓励了大众创业、万众创新,对经济稳增长和市场增活力起到了重要促进作用。营改...

这样一份文件,填补科研项目财务审计领域“空白”

这样一份文件,填补科研项目财务审计领域“空白”

会计师事务所开展国家科研项目结题财务验收审计工作将会有真正的技术指导。近日,中国注册会计师协会发布《国家科研项目结题财务验收审计指引(征求意见稿)》(下称《征求意见稿》),拟对国家科研项目结题财务验收...

做财务的没注意8件小事,害企业进了黑名单都不知道!

做财务的没注意8件小事,害企业进了黑名单都不知道!

工作中总有一些事情微不足道,然而忽略掉,企业会悄悄被列入异常经营名单,法人莫名其妙受牵连,信用评级突然被降……究竟是什么“小事”那么重要?下面让我们一起来看一看。1电话无法取得联系实例: 申...

月薪5万的财务面试题:90%的人都做错,你敢试下吗?(文末超大福利)

月薪5万的财务面试题:90%的人都做错,你敢试下吗?(文末超大福利)

题目:一个人花8块钱买了一只鸡,9块钱卖掉了,然后他觉得不划算,花10块钱又买回来了,11块钱卖给另外一个人了。问:他最后的收益是多少?即他赚了多少钱?一般人的四种算法1. 9-8=1,11...

发表评论

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