傳統上說到查找和匹配,,我們都會用Excel中的VLOOKUP函數。現在,,微軟又推出了XLOOKUP函數,功能更加強大,。我們也詳細介紹過這兩個函數的使用方法,。 但是,不是所有查找場景都應該使用這兩個函數來實現查找和匹配,。 實際上,,只有兩種場景才是使用這兩個函數的最佳場所: 1. 只給定一個條件,然后根據條件得到結果: 在上圖中,,我們需要根據姓名,,動態(tài)返回右側的各項屬性,。這個時候就是使用VLOOKUP和XLOOKUP正確場合。 2. 查找的結果是中間結果,,我們希望根據查找的結果進行統計,。比如下圖所示: 在這個圖中,我們需要根據給定產品表在今年銷售記錄中查找這些產品上所述的品牌,,即圖中1,,2兩步,然后根據得出的品牌表在歷史銷售記錄中統計這些品牌的銷售數量和金額,。 其中,,第一步就是一個查找。在新版本的Excel中,,我們可以使用一個公式完成這個計算,,這時,就可以使用XLOOKUP完成第一步,,并將其作為一個中間步驟,。 而對于大部分的查找場景來說,都是類似于下面的這種形式: 有兩個表,,需要對兩個表進行連接,,將其中一個表的信息補充完整。 對于這種場景,,最合適的是使用Power Query,。 下面我們分不同的場景介紹。 這是最常見的一種場景: 首先在Excel中,,將這兩個表都添加到Power Query中,。以左表(名稱為Stud)為例, 選中表格中任意單元格,,在數據選項卡中,,選擇“來自表格/區(qū)域”: 進入Power Query編輯器后,選擇“關閉并加載至”: 選擇“僅創(chuàng)建連接: 這樣,,我們就將這個表格加載到Power Query中,,但是結果并沒有上載到Excel。 如法炮制,,將右表“班主任”也加載到Power Query中,。 然后在數據選項卡中選擇合并查詢,合并: 按照下圖進行設置: 點擊確定,,回到Power Query編輯器 點擊班主任列右側的按鈕: 按上圖設置,,點擊確定, 班主任添加完成,。關閉PQ編輯器,,上傳至Excel, 完成,! 幾乎跟VLOOKUP一樣簡單,,而且完全自動,,只要刷新即可。并且,,對于匹配不成功的也不會顯示錯誤值,。省了處理錯誤值的步驟! 學生表同前一個場景,,但是每個班可能有兩位老師:班主任和語文老師,。 需要連接成為為下面的結果: 對于VLOOKUP/XLOOKUP來說,這是一個困難的問題,。 由于已經將左表Stud導入了,,所以只要將右表“老師”表導入PQ即可(方法同前)。 然后創(chuàng)建這兩個表的合并查詢(方法同前一場景相同): 點擊確定,,并展開連接后的每行的表格,,會得到這樣的結果: 每個匹配成功的學生都有兩行:班主任行和語文老師行。 很多場景中這樣的格式也是需要的,。 不過我們繼續(xù)往下做,。 選中職位列,并點擊轉換選項卡中的替換值: 按照下圖設置: 點擊確定,,完成替換,。 繼續(xù)選中職位列,在轉換選項卡中,,點擊透視列: 按下圖進行設置: 點擊確定,, 刪除最后一個空列,并上傳至Excel即可: 完成,!這個場景中Power Query完勝,! 在上圖中,我們需要為左表添加價格列,,但是從右表查詢時,,需要兩列作為條件。 這就需要多條件匹配,。 在VLOOKUP中我們是通過輔助列完成的,。在XLOOKUP中,我們是通過寫多列條件完成的,。 都比較復雜。 但是在Power Query中,,我們可以通過合并查詢直接得到,。步驟與前面兩個場景相同,只是在合并設置時,, 在每個表中將關鍵字選擇為兩列即可,。(按住Ctrl鍵點擊鼠標左鍵,,注意兩列的順序必須相同)。 這個場景中,,PQ比VLOOKUP/XLOOKUP公式簡單的多,。 在這個場景中,我們需要根據左表的成績進行等級評定,,標準在右表中規(guī)定,。 傳統上,這是一個VLOOKUP或XLOOKUP的近似匹配,。(這兩個函數的使用方式不完全相同,,請參閱以前的文章)。 使用PQ可以很簡單的完成,。 采用跟前面相同的方法,,將右表“成績”導入PQ中。 在數據選項卡中,,點擊獲取數據,,合并查詢,追加: 在下圖中,,將第一張表選擇“Stud”,,第二張表選為“成績”, 點擊確定,, 點擊選中成績列,,然后選擇排序,升序: 然后選中等級列,,點擊降序排列: 繼續(xù)選中等級列,,點擊填充,向下: 點擊選中姓名列,,篩選,,點擊“刪除空”, 得到結果: 上傳到Excel即可,。 完成,!PQ略勝一籌! 這其實是一個普通的匹配場景,。但是作為條件的列只是另外一個表的條件的一部分,,可以使用VLOOKUP/XLOOKUP的通配符匹配模式解決。 這完全可以通過PQ的模糊匹配解決,。 首先跟前面1,,2,3場景中創(chuàng)建合并查詢的步驟相同,但是在合并的設置中按照下圖設置: 得到結果,, 將功夫列展開: 上傳即可,。 使用PQ的模糊匹配比較簡單,但是對Exel版本有要求,。 如果你的Excel中的PQ沒有這個功能,,可以使用下面的查詢代碼創(chuàng)建查詢:
非常簡單即可得到結果。 完成,!這個場景中PQ稍微麻煩一點,,稍遜一籌。 綜合所有的場景,,PQ完成匹配查找更加高效,,而且是完全自動化的方案。強烈推薦,! 詳細解釋請看視頻 加入E學會,,學習更多Excel應用技巧 http://www./portal/learn/class_list Excel+Power Query+Power Pivot+Power BI 自定義函數 底部菜單:知識庫->自定義函數 面授培訓 底部菜單:培訓學習->面授培訓 Excel企業(yè)應用 底部菜單:企業(yè)應用 也可以在歷史文章中學習Excel,Power Query,,Power Pivot,,Power BI,Power Automate各種技巧,。 |
|