使用Excel的新功能Power Query或Power Pivot來解決多表之間的匹配合並問題,對很多人來說可能是一個全新的思路!十分簡單有效!
EXCEL中實現兩個表格之間的資料自動匹配、補全、合併成一個表格,通常是將類似於一個主表(比如訂單表)和一個明細表(如訂單專案明細表)之間的匹配、補全和合並。
在以前,這個問題可以用vlookup等函式來解決,或者如果是需要匹配的列很多,用vlookup會很麻煩或者因為大量的公式計算以致Excel很卡時,也經常使用VBA來解決。
但是,現在,隨著Excel2016的超級強大新功能Power Query和Power Pivot(Excel2010或Excel2013可到微軟官方下載相應的外掛)的推出,這個問題已經不需要透過vlookup函式或VBA來解決了,並且,透過Power Query和Power Pivot的解決方法不僅十分簡單,而且可以隨著資料來源的更新而一鍵重新整理得到最新結果。
以下分別從Power Query和Power Pivot兩個角度來提供解決方案。
隨著資料化時代的來臨,現在公司的資料量越來越大,需要整合分析的要求也越來越高。比如現在有訂單表和訂單明細表,經常要將訂單表的一些資訊讀取到訂單明細表裡,給相關的部門去用,原來只要幾列數還好,vlookup讀一下就是了,但現在,經常要很多數,用vlookup就會很麻煩了。以下這個訂單表還算少的,我在某個專案上的合同表,差不多300列,而且這還不算真正多的。
這種情況,如果還用vlookup的確有點吃力了,雖然vlookup是Excel中極其重要的函式,但是,在大資料時代,已經很難承起資料關聯合並的重擔了,所以微軟才在Excel里加了PowerQuery功能,具體實現方法如下。
Step05-展開要接入表及所需要的列
透過以上簡單的5步,點幾下滑鼠,兩個表的資料就全部匹配合併到一起並可以返回Excel裡了,如下圖所示:
以上是透過Power Query實現的表間資料合併的方法,但是,實際上,在很多資料分析中,對於這類本身就有關係的表,如果資料合併到一起的話,會導致大量的資料重複和儲存量增大,而實際分析目的本身只需要可以按相關的資料進行分析即可,因此,Power Pivot提供了更進一步的解決方案——直接構建兩表之間的資料關係然後進行分析,不需要再整合資料,具體方法如下。
仍然使用訂單表和訂單明細表為例。
載入完畢後,Power Pivot中資料如下:
訂單表、訂單明細表、產品表之間的關係是:訂單表裡的每個訂單對應訂單明細表裡多個訂單(產品)專案,訂單明細裡的產品可以從產品表裡獲取更詳細的相關資訊。
結果如下:
按同樣的方法還可以建立訂單明細表和產品表之間的關係,最後結果如下:
這樣,3個表之間的關係就建好了,後續就可以直接從各個表裡拖拽需要的資訊進行資料透視等分析,如下所示:
比如,要分析各種產品類別的銷量:
以上介紹了Power Query在EXCEL中實現兩個表格之間的資料自動匹配、補全、合併成一個表格的方法以及透過Power Pivot透過構建多表之間的關係而直接進行統計分析的解決方案,可按實際需要選擇使用。
使用Excel的新功能Power Query或Power Pivot來解決多表之間的匹配合並問題,對很多人來說可能是一個全新的思路!十分簡單有效!
EXCEL中實現兩個表格之間的資料自動匹配、補全、合併成一個表格,通常是將類似於一個主表(比如訂單表)和一個明細表(如訂單專案明細表)之間的匹配、補全和合並。
在以前,這個問題可以用vlookup等函式來解決,或者如果是需要匹配的列很多,用vlookup會很麻煩或者因為大量的公式計算以致Excel很卡時,也經常使用VBA來解決。
但是,現在,隨著Excel2016的超級強大新功能Power Query和Power Pivot(Excel2010或Excel2013可到微軟官方下載相應的外掛)的推出,這個問題已經不需要透過vlookup函式或VBA來解決了,並且,透過Power Query和Power Pivot的解決方法不僅十分簡單,而且可以隨著資料來源的更新而一鍵重新整理得到最新結果。
以下分別從Power Query和Power Pivot兩個角度來提供解決方案。
一、Power Query的表格合併——vlookup雖好,然難承大資料之重隨著資料化時代的來臨,現在公司的資料量越來越大,需要整合分析的要求也越來越高。比如現在有訂單表和訂單明細表,經常要將訂單表的一些資訊讀取到訂單明細表裡,給相關的部門去用,原來只要幾列數還好,vlookup讀一下就是了,但現在,經常要很多數,用vlookup就會很麻煩了。以下這個訂單表還算少的,我在某個專案上的合同表,差不多300列,而且這還不算真正多的。
這種情況,如果還用vlookup的確有點吃力了,雖然vlookup是Excel中極其重要的函式,但是,在大資料時代,已經很難承起資料關聯合並的重擔了,所以微軟才在Excel里加了PowerQuery功能,具體實現方法如下。
Step01-獲取訂單表資料Step02-獲取訂單明細表資料Step03-合併查詢Step04-選擇要合併的表(如訂單表)及兩表之間用於匹配的列(支援多列組合匹配)Step05-展開要接入表及所需要的列
透過以上簡單的5步,點幾下滑鼠,兩個表的資料就全部匹配合併到一起並可以返回Excel裡了,如下圖所示:
以上是透過Power Query實現的表間資料合併的方法,但是,實際上,在很多資料分析中,對於這類本身就有關係的表,如果資料合併到一起的話,會導致大量的資料重複和儲存量增大,而實際分析目的本身只需要可以按相關的資料進行分析即可,因此,Power Pivot提供了更進一步的解決方案——直接構建兩表之間的資料關係然後進行分析,不需要再整合資料,具體方法如下。
二、Power Pivot構建資料模型——表間關係一線牽,不須大量公式拼資料仍然使用訂單表和訂單明細表為例。
Step01-依次將資料新增到資料模型載入完畢後,Power Pivot中資料如下:
Step03-構建表間關係訂單表、訂單明細表、產品表之間的關係是:訂單表裡的每個訂單對應訂單明細表裡多個訂單(產品)專案,訂單明細裡的產品可以從產品表裡獲取更詳細的相關資訊。
結果如下:
按同樣的方法還可以建立訂單明細表和產品表之間的關係,最後結果如下:
這樣,3個表之間的關係就建好了,後續就可以直接從各個表裡拖拽需要的資訊進行資料透視等分析,如下所示:
比如,要分析各種產品類別的銷量:
以上介紹了Power Query在EXCEL中實現兩個表格之間的資料自動匹配、補全、合併成一個表格的方法以及透過Power Pivot透過構建多表之間的關係而直接進行統計分析的解決方案,可按實際需要選擇使用。
【資料透視基礎精選10篇】【Power Query入門到實戰80篇】【Power Pivot 基礎精選15篇】我是大海,微軟認證Excel專家,企業簽約Power BI顧問讓我們一起學習,共同進步!