關注星標★不迷路
紙上得來終覺淺,絕知此事要躬行
將兩個資料表合併以增加資料分析維度是我們使用 Excel 經常會面對的問題。過去,我們只能用 VLOOKUP函式,複雜一點就甚至需要使用 INDEX 及 MATCH 函式,然而很多人很難理解函式的使用邏輯。
參考閱讀 :
/ XLOOKUP 還沒出現時,VLOOKUP 就已經被它幹掉了 /
學會 Excel 的 PQ 功能以後,點選滑鼠就可以完成複雜函式組合才能實現的資料豐富功能。我們有兩張表,一張庫存資訊表,一張銷售明細。兩張表透過 SKU Number 進行關聯。
要分析每一筆交易的收入、成本就要將 Sale Price 和 Unit Cost 匹配到銷售明細表。
要分析不同產品型別、不同分機構的銷量情況就要將 Type、Brand 匹配到銷售明細表。
庫存資訊表
銷售明細表
我們可以使用 PQ 的「合併查詢」功能進行匹配。
一、僅建立連線
為了使用 PQ 的「合併查詢」,需要將兩個資料表都載入進 PQ 編輯器。我們都知道將資料載入到 PQ 以後關閉並上載到 Excel 以後,會複製一份原始資料表到 Excel 的新工作表中,這樣就顯得多餘。
所以我們需要將查詢僅建立成連結。
1、使用資料選項卡下「自表格/區域」功能,將庫存資訊表載入進 PQ 編輯器。
2、庫存資訊表載入到 PQ 編輯器,這時你可以對資料進行轉換和清洗,當然本例中不需要執行其他操作。
3、點選「主頁」>「關閉並上載」下拉彈出選單中,選擇「關閉並上載至」。
4、在匯入資料視窗中選擇「僅建立連線」。
這時建立的查詢會顯示在「查詢 & 連線」中,而不會新建一個工作表。
同樣的方式將銷售明細表也載入到 PQ 編輯器中。這時兩個資料表都僅僅以連結的形式存在於 Excel 中,而不會重複載入。
使用僅建立連結的方式載入資料,可以讓我們的Excel工作簿簡約,不冗餘。一般情況下,我們會將原始連結資料建立成連結,而把最終的資料清洗結果載入到工作表中。
二、單條件合併查詢
將兩個表格載入到 PQ 以後,就可以使用合併查詢功能。
1、將滑鼠懸停在查詢上方,點選滑鼠右鍵彈出的選單中選擇「合併」。
2、在彈出的視窗中,上方表格選擇為 Sales 表,下方的表格選擇為 Inventory 表。
3、滑鼠點選 Inventory 表的 SKU Number 列,同樣點選 Sales 表的 SKU Number 列。選擇兩列作為兩個表的關聯列。
4、點選確定就完成了資料匹配,下面只需要展開合併後的列,選擇需要的列並載入到 Excel 就可以了。
幾次滑鼠點選就替代了複雜的函式組合才能實現的功能。這就是 PQ 的強大之處。短時間的練習就可以在資料處理上取得突破性的進展。
三、多條件合併查詢
PQ 的 合併查詢功能也很簡單,只要選擇關聯欄位時按一致的順序選擇好欄位,點選確定就可以實現多條件合併了。選擇多個欄位按住「Ctrl」鍵點選即可。PQ 介面欄位上方的小數字代表了關聯欄位的順序。
四、模糊查詢
如果我們的需求是進行模糊匹配的話,最新版的 PQ 還給我們提供了模糊匹配的選項。勾選「模糊匹配執行合併」,然後設定相似性閾值,預設的閾值是0。8。閾值設定的越小,對於匹配欄位相似性要求就越低。所以我們需要嘗試查找出最合適的閾值進行模糊匹配。
最後,雖然 PQ 提供了模糊匹配功能,但是這種匹配方式有時並不能準確的提供答案。因此還是必須得重視資料的規範性。
一般人都不知道的三個 Excel 隱藏函式
「 JaryYuan 」 原創文章小合集
Excel Tips and Tricks 使用子彈圖表達目標完成情況