論壇首頁
-> 電子電玩
只會Vlookup函數Out了!Excel所有查找公式全在這兒(共16大類)轉帖 (發表於6年前)
|
|
|
|
閱讀上一個主題 :: 閱讀下一個主題 |
作者 |
正文 |
maohu (只看此人)
|
時間: 2018-4-15 22:16
|
|
|
來源:蘭色幻想-趙志東
找對比,你會首先想到Vlookup函數。但在Excel中只會Vlookup函數是遠遠不夠的。今天對查找公式進行一次全面的整理。(注:一題多解的只選取最優公式)
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)
_________________ 宅出風格,宅出水平,宅出安全
——————————————————————————————————
|
|
|
|
|
樓主 |
電梯直達
|
|
maohu (只看此人)
|
時間: 2018-4-15 22:17
|
|
|
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)
_________________ 宅出風格,宅出水平,宅出安全
——————————————————————————————————
|
|
|
|
|
沙發 |
返回頂端
|
|
maohu (只看此人)
|
時間: 2018-4-15 22:17
|
|
|
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)}
16、跨多文件查找
跨多個文件查找,估計你搜遍網絡也找不到,這也是蘭色首次編寫跨多文件查找公式。其實原理和跨多表查找一樣,也是借助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文件名稱,然後應用到公式中,具體的公式就不再寫了。
能想到的查找公式差不多都列出來了,其中有不少公式需要有一定基礎才能看懂。如果看不明白先學會套用以後再慢慢理解。
_________________ 宅出風格,宅出水平,宅出安全
——————————————————————————————————
|
|
|
板凳 |
返回頂端
|
|
|
|
論壇首頁
-> 電子電玩 |
所有的時間均為 美國太平洋時間
|
第1頁,共1頁 |
|
|
注: 以上論壇所有發言僅代表發帖者個人觀點, 並不代表本站觀點或立場, 加西網對此不負任何責任。 投資理財及買房賣房版面的帖子不構成投資建議。投資有風險,責任請自負對二手買賣中的虛假信息,買賣中的糾紛等均與本站無關。 |
|
您不能在本論壇發表新主題 您不能在本論壇回復主題 您不能在本論壇編輯自己的文章 您不能在本論壇刪除自己的文章 您不能在本論壇發表投票 您不能在這個論壇添加附件 您可以在這個論壇下載文件
論壇轉跳:
|
|
maohu, maohu, maohu
|
|
|