|
跟李銳學(xué)Excel， 高效工作，快樂生活。 我才是Excel的查找引用之王，甩Vlookup幾條街！ 今天我要講的這個函數(shù)，80%以上的白領(lǐng)還不會用，但這個函數(shù)的含金量很高，建議看到一半看不懂的同學(xué)可以先收藏，以免以后想找時找不到！ 前面教程中介紹的VLOOKUP函數(shù)的功能就已經(jīng)很給力了，但威力十分強(qiáng)大的查找引用函數(shù)LOOKUP卻很少有人會用，其實LOOKUP函數(shù)比VLOOKUP函數(shù)強(qiáng)大不止10倍！ 凡工作中涉及到查找引用的問題，都可以用LOOKUP函數(shù)來解決。就連一些逆向查找，涉及合并單元格的查找，LOOKUP函數(shù)也能發(fā)揮出意想不到的強(qiáng)大作用，無疑屬于職場辦公必備函數(shù)。 為了讓大家認(rèn)識LOOKUP函數(shù)那些不為人知的強(qiáng)大功能，本文貼合辦公實際場景，整理了多種LOOKUP函數(shù)的應(yīng)用方法。 01 LOOKUP函數(shù)語法解析及基礎(chǔ)用法 LOOKUP 函數(shù)是 Excel 中威力十分強(qiáng)大的查找引用函數(shù)，當(dāng)需要查詢一行或一列并查找另一行或列中的相同位置的值時，就要用到這個函數(shù)了。 LOOKUP 函數(shù)有兩種使用方式：向量形式和數(shù)組形式。 第一種形式：向量形式LOOKUP的向量形式在單行區(qū)域或單列區(qū)域中查找值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的值。 語法 LOOKUP(lookup_value,lookup_vector, [result_vector]) LOOKUP函數(shù)向量形式語法具有以下參數(shù)： lookup_value必需。 LOOKUP在第一個向量中搜索的值。 Lookup_value可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/span> lookup_vector必需。 只包含一行或一列的區(qū)域。 lookup_vector中的值可以是文本、數(shù)字或邏輯值。 重要: lookup_vector 中的值必須按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否則，LOOKUP可能無法返回正確的值。 文本不區(qū)分大小寫。 result_vector可選。只包含一行或一列的區(qū)域。result_vector參數(shù)必須與 lookup_vector參數(shù)大小相同。其大小必須相同。 備注 如果 LOOKUP函數(shù)找不到 lookup_value，則該函數(shù)會與lookup_vector中小于或等于lookup_value的最大值進(jìn)行匹配。 如果 lookup_value小于 lookup_vector中的最小值，則 LOOKUP會返回 #N/A 錯誤值。 下面結(jié)合一個實際案例講解LOOKUP函數(shù)的向量形式用法。 下圖左側(cè)是數(shù)據(jù)源，需要在右側(cè)的黃色區(qū)域輸入公式，根據(jù)員工編號調(diào)用對應(yīng)的銷售額。 在F2輸入以下公式，并向下填充。 =LOOKUP(E2,$A$2:$A$12,$C$2:$C$12) 第二種形式：數(shù)組形式LOOKUP的數(shù)組形式在數(shù)組的第一行或第一列中查找指定的值，并返回數(shù)組最后一行或最后一列中同一位置的值。當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時，請使用LOOKUP的這種形式。 語法 LOOKUP(lookup_value,array) LOOKUP函數(shù)數(shù)組形式語法具有以下參數(shù)： lookup_value必需。 LOOKUP在數(shù)組中搜索的值。 lookup_value參數(shù)可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/span> 如果 LOOKUP找不到 lookup_value的值，它會使用數(shù)組中小于或等于 lookup_value的最大值。 如果 lookup_value的值小于第一行或第一列中的最小值（取決于數(shù)組維度），LOOKUP會返回 #N/A 錯誤值。 Array必需。 包含要與 lookup_value 進(jìn)行比較的文本、數(shù)字或邏輯值的單元格區(qū)域。 LOOKUP的數(shù)組形式與 HLOOKUP和 VLOOKUP函數(shù)非常相似。 區(qū)別在于：HLOOKUP在第一行中搜索 lookup_value的值，VLOOKUP在第一列中搜索，而 LOOKUP根據(jù)數(shù)組維度進(jìn)行搜索。 如果數(shù)組包含寬度比高度大的區(qū)域（列數(shù)多于行數(shù)）LOOKUP會在第一行中搜索 lookup_value的值。 如果數(shù)組是正方的或者高度大于寬度（行數(shù)多于列數(shù)），LOOKUP會在第一列中進(jìn)行搜索。 使用 HLOOKUP和 VLOOKUP函數(shù)，您可以通過索引以向下或遍歷的方式搜索，但是 LOOKUP始終選擇行或列中的最后一個值。 重要: 數(shù)組中的值必須按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否則，LOOKUP可能無法返回正確的值。 文本不區(qū)分大小寫。 下面結(jié)合一個實際案例講解LOOKUP函數(shù)的數(shù)組形式用法。 下圖左側(cè)是數(shù)據(jù)源，需要在右側(cè)的黃色區(qū)域輸入公式，根據(jù)員工編號調(diào)用對應(yīng)的銷售額。 在F2輸入以下公式，并向下填充。 =LOOKUP(E2,$A$2:$C$12) （更詳細(xì)的公式原理解析和說明請點擊本文底部的“閱讀原文”獲?。?/span> 02 LOOKUP函數(shù)單條件查找 在LOOKUP函數(shù)的基礎(chǔ)用法中，單條件查找的前提是數(shù)據(jù)源按升序排列，但實際工作中很多數(shù)據(jù)員都是亂序排列的。 這種情況下，如何使用LOOKUP函數(shù)來實現(xiàn)查找引用功能呢？ 下面結(jié)合一個實際案例，介紹LOOKUP函數(shù)單條件查找的用法。 上圖中，左側(cè)是數(shù)據(jù)源區(qū)域，右側(cè)是要輸入公式進(jìn)行查找調(diào)用的區(qū)域。其中黃色單元格的位置是要輸入公式的單元格。 其中的尺碼為亂序排列，需要根據(jù)尺碼調(diào)用對應(yīng)的價格。 F2輸入以下公式，并向下填充。 =LOOKUP(1,0/($B$2:$B$7=E2),$C$2:$C$7) （更詳細(xì)的公式原理解析和說明請點擊本文底部的“閱讀原文”獲取） 之前的教程中，我們學(xué)習(xí)了LOOKUP函數(shù)單條件查找的用法，這次來介紹一下LOOKUP函數(shù)多條件查找的用法。 還是先來看實際案例，方便幫助大家理解與記憶。 在H2輸入以下公式，并將公式向下填充。 =LOOKUP(1,0/(($B$2:$B$13=F2)*($C$2:$C$13=G2)),$D$2:$D$7) （更詳細(xì)的公式原理解析和說明請點擊本文底部的“閱讀原文”獲?。?/span> 介紹完LOOKUP函數(shù)單條件查找和多條件查找的方法，下面來介紹LOOKUP函數(shù)逆向查找的方法，包括從下向上查找和從右向左查找。 這節(jié)教程，我們先來學(xué)習(xí)從下向上查找，即查找符合條件的最后一個數(shù)據(jù)。 還是引入一個實際案例，便于大家理解。 上圖左側(cè)是數(shù)據(jù)源區(qū)域，右側(cè)是輸入公式查找調(diào)用的區(qū)域，黃色單元格輸入公式。 由圖可見每個產(chǎn)品的出庫都有多個人經(jīng)辦，現(xiàn)在的需求是根據(jù)產(chǎn)品查找最后一個經(jīng)辦人。 G2輸入以下公式，并將公式向下填充。 =LOOKUP(1,0/($B$2:$B$13=F2),$D$2:$D$13) （更詳細(xì)的公式原理解析和說明請點擊本文底部的“閱讀原文”獲取） 上一節(jié)教程中我們學(xué)會了LOOKUP函數(shù)從下向上查找的方法，下面來介紹LOOKUP函數(shù)從右向左查找的方法。 來看實際案例，便于大家理解。 上圖是某企業(yè)的出庫記錄表，其中記錄了各個產(chǎn)品的出庫明細(xì)，包括出庫日期，出庫產(chǎn)品、數(shù)量和經(jīng)辦人，現(xiàn)在需要根據(jù)產(chǎn)品查找其對應(yīng)的最后一次出庫的日期。 每個產(chǎn)品對應(yīng)著多個出庫日期，而出庫日期又是由遠(yuǎn)到近升序排列，所以對于某個產(chǎn)品對應(yīng)的多個出庫日期，我們需要提取最后一個，這里要從下向上查找。 同時，產(chǎn)品在B列，要查找的出庫日期位于A列，我們不但要從下向上查找，還要從右向左逆向查找。 G2輸入以下公式，并將公式向下填充。 =LOOKUP(1,0/($B$2:$B$13=F2),$A$2:$A$13) （更詳細(xì)的公式原理解析和說明請點擊本文底部的“閱讀原文”獲?。?/span> 06 LOOKUP函數(shù)多層級區(qū)間條件查找 之前的教程，無論是單條件查找、多條件查找，還是從下向上查找、從右向左查找，介紹的都是LOOKUP函數(shù)根據(jù)條件查找具體結(jié)果的案例。 這節(jié)教程，咱們學(xué)習(xí)一個LOOKUP函數(shù)根據(jù)區(qū)間條件進(jìn)行判斷數(shù)據(jù)所對應(yīng)的等級的案例。 上圖左側(cè)A和B兩列是數(shù)據(jù)源區(qū)域，包括姓名和成績，C列要輸入公式，根據(jù)成績來自動判斷所處的等級。 判斷依據(jù)為：
C2輸入以下公式，并將公式向下填充。 =LOOKUP(B2,$E$2:$F$5) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> 工作中經(jīng)常會遇到包含合并單元格的表格，很多人對于合并單元格帶來的困擾不知如何解決，比如無法直接用函數(shù)公式提取數(shù)據(jù)，無法直接創(chuàng)建數(shù)據(jù)透視表等。 其實，只要靈活運用一些函數(shù)對數(shù)據(jù)源進(jìn)行處理和轉(zhuǎn)換，就可以正常使用那些工具來批量操作了。 這節(jié)課，咱們就來結(jié)合一個實際案例來學(xué)習(xí)根據(jù)合并單元格智能提取對應(yīng)的數(shù)據(jù)的方法。 上圖中左側(cè)A至C列是數(shù)據(jù)源區(qū)域，需要根據(jù)A列的合并單元格，在D列提取對應(yīng)的組別信息。 D2輸入以下公式，并向下填充。 =LOOKUP(1,0/(A$2:A2<>""),A$2:A2) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> 08 LOOKUP函數(shù)返回最后一個非空數(shù)值 利用LOOKUP函數(shù)逆向查找的功能，我們可以查找某列最下方的非空數(shù)值。 A列數(shù)據(jù)源中包含數(shù)值、文本、空單元格、錯誤值 在C2單元格輸入以下公式 =LOOKUP(9E+307,A:A) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> 利用LOOKUP函數(shù)逆向查找的功能，我們還可以查找某列最下方的非空文本。 在C2單元格輸入以下公式 =LOOKUP("々",A:A) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲取） 10 LOOKUP函數(shù)返回本季度首天日期 靈活運用LOOKUP函數(shù)，可以提取與日期相關(guān)的一些數(shù)據(jù)，比如返回當(dāng)前季度的第一天日期。
在A2單元格輸入以下公式： =LOOKUP(NOW(),--({1,4,7,10}&"-1")) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> 11 LOOKUP函數(shù)返回本月首天日期 靈活運用LOOKUP函數(shù)，可以提取與日期相關(guān)的一些數(shù)據(jù)，比如返回當(dāng)前月份的第一天日期。 =LOOKUP(NOW(),--(ROW(1:12)&"-1")) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲取） 12 LOOKUP函數(shù)判斷日期的上中下旬 靈活運用LOOKUP函數(shù)，可以通過自行構(gòu)建常量數(shù)組，實現(xiàn)判斷日期上中下尋的需求。
在B2單元格輸入以下公式： =LOOKUP(DAY(A2),{1,11,21},{"上旬","中旬","下旬"}) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> 13 LOOKUP函數(shù)按區(qū)域統(tǒng)一查找 工作中有時需要多列數(shù)據(jù)匹配查找，這時靈活使用LOOKUP函數(shù)能達(dá)到意想不到的效果。
上圖中A列和B列是數(shù)據(jù)源，需要在黃色單元格輸入公式，根據(jù)D列和E列的英文調(diào)用對應(yīng)的中文。 選中F2:G7單元格區(qū)域，輸入以下數(shù)組公式，按<Ctrl+Shift+Enter>組合鍵。 =LOOKUP(D2:E7,A2:B13) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲取） 14 LOOKUP函數(shù)提取數(shù)值 LOOKUP函數(shù)不光是可以查找調(diào)用數(shù)據(jù)，還可以從混雜的字符串中提取數(shù)值，這在某種情況下能為我們節(jié)省大量的時間和精力，準(zhǔn)確性還比手動有保障得多！
上圖中A列的數(shù)據(jù)中既有文本也有數(shù)值，而且數(shù)值的位置不固定，有的在中間，有的在前面，還有的在后面。 這種數(shù)據(jù)源導(dǎo)致我們無法通過直接用文本函數(shù)截取相應(yīng)的數(shù)值位置，看看LOOKUP函數(shù)怎樣搞定這個問題吧！ 在B2單元格輸入以下數(shù)組公式，按按<Ctrl+Shift+Enter>組合鍵。 =-LOOKUP(0,-MID(A2,MIN(FIND(ROW($1:$9),A2&56^7)),ROW(INDIRECT("1:"&LEN(A2))))) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> 15 LOOKUP函數(shù)按關(guān)鍵詞歸類 在工作中靈活運用LOOKUP函數(shù)，還可以處理很多比較復(fù)雜的問題。 比如在電商行業(yè)中，經(jīng)常需要將關(guān)鍵詞按行業(yè)大詞歸類，這時可以使用LOOKUP函數(shù)結(jié)合其它函數(shù)嵌套實現(xiàn)。
上圖中A列是數(shù)據(jù)源區(qū)域，記錄了關(guān)鍵詞列表，需要我們在黃色區(qū)域輸入公式，按照D列的行業(yè)大詞，從關(guān)鍵詞中尋找對應(yīng)行業(yè)大詞的歸屬。 歸屬規(guī)則： 1、 要提取關(guān)鍵詞中首位出現(xiàn)的行業(yè)大詞 2、 當(dāng)關(guān)鍵字中包含多個行業(yè)大詞時，優(yōu)先歸屬為更大的行業(yè)大詞 3、 數(shù)據(jù)源中的行業(yè)大詞順序是從上到下依次變大 在B2單元格輸入以下公式，并將公式向下填充。 =LOOKUP(1,0/FIND("@"&$D$2:$D$13,"@"&A2),$D$2:$D$13) （更詳細(xì)的公式原理解析和說明，請點擊本文底部的“閱讀原文”獲?。?/span> ps. 這只是《史上最全系列》函數(shù)教程的其中之一，從vlookup、sum、if、countif、sumif、sumproduct、index、match、datedif、frequency到lookup，每一篇都極耗時間和精力，我力求用最適合的示例，最全的方法、結(jié)合更多的場景展現(xiàn)Excel的各種應(yīng)用技術(shù)，方便大家工作中能夠根據(jù)自己的場景快捷上手，早日實現(xiàn)高效工作，快樂生活！ 在公眾號后臺回復(fù)這些函數(shù)的名稱，即可獲取對應(yīng)的教程資源。 與此同時，我更要感謝能看到這里的你們，正是你們一如既往的支持，讓我能在知識原創(chuàng)的道路上走的更遠(yuǎn)！衷心祝一直在努力的你們鵬程萬里，心想事成！ 如果你覺得有用，就點右上角分享給朋友們看看吧~ 回到公眾號首頁，點右上角置頂此公眾號，不再錯過精彩的技巧！ 更多的Excel應(yīng)用技巧，可以點擊文尾的“閱讀原文”，獲取更多教程。 今天就到這里，大家記得收藏此教程，明天見~ 李 銳 微軟全球最有價值專家MVP 新浪微博Excel垂直領(lǐng)域第一簽約自媒體 百度名家，百度閱讀認(rèn)證作者 每日分享職場辦公技巧原創(chuàng)教程 高效工作，快樂生活！ 微博 @Excel_函數(shù)與公式 微信公眾號（ExcelLiRui） >>推薦閱讀 << （點擊藍(lán)字可直接跳轉(zhuǎn)） 2016合集 丨vlookup丨sum丨if丨條件計數(shù)丨條件求和 快捷鍵丨格式轉(zhuǎn)換秘技丨sumproduct丨計算日期間隔 多表匯總丨工資條丨批量導(dǎo)出圖片丨頻率統(tǒng)計丨lookup 批量創(chuàng)建文件夾丨智能填充合并單元格丨更新報表必備 |
|
|
