我的大学

当前位置:主页 > 技术技巧 > 【Excel】Excel常用公式的用法

【Excel】Excel常用公式的用法

时间:2017-04-05 09:31:06 作者:我的大学 点击:收藏本站
查找对比,你会首先想到Vlookup函数。但在Excel中只会Vlookup函数是远远不够的。今天兰色对查找公式进行一次全面的整理。(注:一题多解的只选取最优公式)...
 

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

 

1、普通查找

查找李晓峰的应发工资

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

(常用于货品规划、清点查找等工作!)

 

Excel表格vlookup的用法

 

2、反向查找

查找吴刚的员工编号

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

 

excel表格INDEX方向查找的用法

 

3、交叉查找

查找3月办公费的金额

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

 

Excel数据表格中vlookup的交叉查找用法-我的大学

 

4、多条件查找

查找上海产品B的销量

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

 

Excel数据表格中vlookup的多条件查找用法-我的大学

 

5、区间查找

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

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

 

Excel数据表格中vlookup的区间查找用法-我的大学

 

6、双区间查找

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

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

 

Excel数据表格中index的双区间查找用法-我的大学

 

7、线型插值

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

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

 

Excel数据表格中trend的线型插值用法-我的大学

 

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

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

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

 

Excel数据表格中lookup的查找最后一个符合条件记录用法-我的大学

 

9、模糊查找

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

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

 

10、匹配查找

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

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

 

excel数据表格这种lookup匹配查找用法-我的大学

 

11、最后一个非空值查找

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

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

 

excel数据表格这种lookup最后一个非控制查找用法-我的大学

 

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)

excel数据表格多工作表查找用法-我的大学

excel数据表格vlookup多工作表查找的用法-我的大学

 

13、一对多查找

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

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

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

 

excel数据表格vlookup一对多查找用法-我的大学

 

14、查找销量最大的城市

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

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

 

excel数据表格index查找销量最大的城市用法-我的大学

 

15、最接近值查找

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

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

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

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

(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
------分隔线----------------------------
评论板块
发表评论
欢迎分享您的经验,一起交流!验证码输错得重新填写评论,字多的请务必先复制下!
用户名: 验证码:点击我更换图片
最新评论

相册动态


本月热点

Copyright ©2011 我的大学 All Rights Reserved
站长QQ:2310575866 感谢 DEDECMS