PMC函數(shù)系列001-XLOOKUP

leafcho 2022-06-20 發(fā)布于浙江

展開全文

最新版本的Excel更新了好多新函數(shù)，新函數(shù)在PMC排程中，可以用到地方非常多，特別是XLOOKUP函數(shù)，此函數(shù)可以向右查詢，向左查詢，批量查詢等；可以替代的函數(shù)有：

VLOOKUP、HLOOKUP、以及經(jīng)典的IFERROR+VLOOKUP 屏蔽錯誤的經(jīng)典用法

函數(shù)參數(shù)

因為功能強大，所以此函數(shù)的參數(shù)有多個，其中3個必需，3個可選，來自官方的解釋：

XLOOKUP 函數(shù)搜索區(qū)域或數(shù)組，然后返回與它找到的第一個匹配項對應(yīng)的項。如果不存在匹配項，那么 XLOOKUP 可以返回最接近 () 匹配項。

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

參數(shù)

說明

lookup_value

必需*

要搜索的

值 *如果省略，XLOOKUP 將返回在lookup_array中找到的空白單元格。

lookup_array

必需?

要搜索的數(shù)組或區(qū)域

return_array

必需??

要返回的數(shù)組或區(qū)域

[if_not_found]

可選

如果找不到有效的匹配項，請返回你提供的 [if_not_found] 文本。

如果找不到有效的匹配項，并且 [if_not_found] 缺失， 則返回#N/A 。

[match_mode]

可選

指定匹配類型：

0 - 完全匹配。如果未找到，則返回 #N/A。這是默認(rèn)選項。

-1 - 完全匹配。如果沒有找到，則返回下一個較小的。

1 - 完全匹配。如果沒有找到，則返回下一個較大的。

2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含義。

[search_mode]

可選

指定要使用的搜索模式：

1 - 從第一項開始執(zhí)行搜索。這是默認(rèn)選項。

-1 - 從最后一項開始執(zhí)行反向搜索。

2 - 執(zhí)行依賴于 lookup_array 按升序排序的二進(jìn)制搜索。如果未排序，將返回?zé)o效結(jié)果。

2 - 執(zhí)行依賴于 lookup_array 按降序排序的二進(jìn)制搜索。如果未排序，將返回?zé)o效結(jié)果。

上面需要注意的就是，沒有VLOOKUP、HLOOKUP 指定列號的參數(shù)了，變成直接要返回的數(shù)組或區(qū)域

經(jīng)典用法01-向右查找

錄入公式：=XLOOKUP(L2,B:B,F:F) ，這里為了簡單化公式錄入，只錄入了3個必選參數(shù)；和VLOOKUP一樣，注意查詢的工單號，一定要包含在搜索的區(qū)域。

和VLOOKUPR 區(qū)別如下：=VLOOKUP(L2,B:F,5,0)

對比下來，VLOOKUP需要時刻關(guān)注以搜索區(qū)域向右到返回區(qū)域的列號

經(jīng)典用法02-向左查找 (反向查找）

錄入公式：=XLOOKUP(J2,B:B,A:A)，和向右查找一樣的原理，就是需要搜索的內(nèi)容和搜索區(qū)域分別在第1、2參數(shù)，返回在第3參數(shù)

VLOOKUP 反向查找的話，需要用到嵌套

=VLOOKUP(J2,CHOOSE({1,2},B:B,A:A),2,0)

配合 Choose 強制生成一個新排序的數(shù)組，再返回。

經(jīng)典用法03-向下查找

錄入公式：=XLOOKUP(J1,$A$1:$D$1,$A$2:$D$2)，這里是多列引用，所以搜索區(qū)域和返回區(qū)域要鎖死，不然向右填充公式的時候，引用區(qū)域會偏移；向下查找是行的概念，不是列了，要特別注意；

HLOOKUP 這里也是注意鎖定錄入公式 =HLOOKUP(J1,$A$1:$D$12,2,0)，

如果要返回第多行，可以用ROW(A2)組合一下 =HLOOKUP(J$1,$A$1:$D$12,ROW(A2),0)

XLOOKUP 這邊更改一下鎖定方式 =XLOOKUP(J$1,$A$1:$D$1,$A2:$D2)

經(jīng)典用法04-向上查找

向上查找用得非常少，為了演示XLOOKUP 四個方向都可以勝任，這里也演示一下

經(jīng)典用法05-屏蔽錯誤

在經(jīng)典用法01的基礎(chǔ)上，錄入第4個參數(shù)，如果搜索不到返回結(jié)果是“找不到“

=XLOOKUP(L2,B:B,F:F,'找不到')

=IFERROR(VLOOKUP(L2,B:F,5,0),'找不到')

如果用VLOOKUP的話，還要用多一個函數(shù)，所以說XLOOKUP 這里完勝；

經(jīng)典用法06-雙條件查詢及多條件查詢

兩個條件的查詢怎么查？如下圖，查詢方正電飯煲的數(shù)量是多少？

此時用XLOOKUP，只需要用到文本連接符號&就可以了

錄入公式：=XLOOKUP(F2&G2,B2:B5&C2:C5,D2:D5)

相當(dāng)于重新建立一個合并連接區(qū)域；

VLOOUP 如果需要多條件查詢，需要再次用到CHOOSE 或者 INDEX+MATCH 的經(jīng)典組合

=VLOOKUP(F2&G2,CHOOSE({1,2},B2:B5&C2:C5,D2:D5),2,0)

經(jīng)典用法07- 返回多列

錄入公式 =XLOOKUP(A9,A2:A5,B2:H5) ，把第三個參數(shù)更改為多列，就可以實現(xiàn)多列的批量引用，對于需要連續(xù)引用的需求，避免了多次錄入公式；

注意結(jié)果數(shù)組，不能更改，公式為灰色；

如果是VLOOKUP的話，需要配合COLUMN函數(shù)來實現(xiàn)，錄入公式

=VLOOKUP($A8,$A$2:$H$5,COLUMN(B1),0)

結(jié)果如下：

經(jīng)典用法08- 通配 * 符號查找

有時候我們需要找以什么開頭，或者包含什么的時候，XLOOKUP 支持通配符號來查詢的，此時我們需要把第5個參數(shù) 錄入為2

如下圖：

錄入公式：=XLOOKUP(A9,A2:A5,B2:B5,,2)

這里要注意，重復(fù)值只能返回第1個

經(jīng)典用法09- 查詢最大值或最小值

有時候我們想知道第1個值和最后一個值的時候，比如工序數(shù)據(jù)庫中各種加工產(chǎn)品，第1道工序和最后一道工序是什么？

此時用XLOOKUP可以快速解決這個問題

=XLOOKUP($E2,$A$2:$A$10,$C$2:$C$10,,,1)

=XLOOKUP($E2,$A$2:$A$10,$C$2:$C$10,,,-1)

分別下拉填充公式就可以得到結(jié)果

理解第6個參數(shù) 就可以了

1 - 從第一項開始執(zhí)行搜索。這是默認(rèn)選項。

-1 - 從最后一項開始執(zhí)行反向搜索。

我是古哥：
從事制造行業(yè)18年，在企業(yè)運營、供應(yīng)鏈管理、智能制造系統(tǒng)等方面具有豐富的實戰(zhàn)經(jīng)驗。企業(yè)智能化，柔性化計劃運營管理專家，擅長通過企業(yè)流程優(yōu)化規(guī)范，企業(yè)管理、導(dǎo)入計劃運營提升企業(yè)效率；對提高企業(yè)準(zhǔn)時交貨率，降低企業(yè)庫存，輸出智能制造人才有豐富的經(jīng)驗。關(guān)注古哥計劃，每日分享制造行業(yè)，特別是生產(chǎn)計劃方面的一些職場干貨。