放棄vlookup吧,這3種EXCEL多表關聯方法“強得很”

思邁特Smartbi 發表於 2021-12-14
Excel

相信很多人對Excel都是又愛又恨的,這個我們日常工作中頻繁用到的工具,功能雖然強大,但缺點也很明顯。如在效能方面,當資料量較大時Excel執行就會變得非常緩慢,嚴重降低了工作效率。在討論如何解決這個問題之前,我們先來看一個真實的案例:

 

一、案例:

小楊是一家醫療器材公司的銷售助理,整理公司的銷售報表並定時將資料傳送給領導是他的日常工作之一,但往往小楊要在這個工作上花費很多時間,導致其他工作沒法按時完成,因此每週都要加班,小楊苦不堪言。小楊公司的資料來源儲存在ERP系統,將資料匯入Excel,再對資料來源進行加工、整理、分析。但這個資料來源不僅資料量巨大而且維度非常多,十分複雜。如產品品類就有400多個,銷售150多位、客戶更是多達了1500多位,每個月的資料量接近可以到達一百萬行。不僅如此,小楊還要按維度整理資料,如公司的銷售部主要有3個,但裡面又包含有2個小部,部門裡又要按區域劃分。分完大區、小區,還要區分連鎖、鄉鎮等。除了有巨大的資料量和複雜的維度,小楊還需要把這些報表運用公式進行關聯。

 

vlookup.png 

 

二、業務場景

1、痛點/需求

為了對上述的案例進行場景模擬,並找到最優的解決方案,我找來了3個資料來源檔案,先看一下在Excel裡做多表關聯會遇到什麼問題:

 

大資料.png 

 

第一個是汽車銷售資料來源,資料量是100萬行:


汽車銷售.png

 

第二個是產品的維度表:


產品維度.png 

 

第三個是地區的維度表:


地區維度.png

 

由於報表中光汽車銷售的資料來源就多達100萬行,不用說執行,只是開啟Excel都已經帶不動了,非常緩慢。

 

excel.png 

 

我們再嘗試一下對這幾個報表進行關聯,Excel的多表關聯最常用的方法是利用vlookup把兩個表格連線起來,每關聯一個欄位,就要多寫一次公式,特別麻煩,如果資料大的時候,就會變得非常卡,有時候根本就跑不動,讓人崩潰。一波操作後,小編選擇了放棄,還是另尋他法吧!

 

報表1.png 

 

2、解決過程

根據上面的案例,小編認為如果想要解決上述Excel的問題,可以從2個方面進行考慮。一是資料量,二是多表關聯。經過對目前市場上知名度較高的工具進行多番嘗試後,終於給小編找到了3種比較適合的方法,至於哪種方法更適合自己,這個答案就交給明智的你自己去選擇吧。

 

1)資料庫

運用資料庫的語句做多表關聯是一種不錯的方式,學過資料庫的人都知道,sql語句中提供了多種連線的方式,如左連線、右連線、外連線、內連線。先在我們來實操一下,開啟資料庫軟體,在資料庫中將表結構設計好,然後把這3個表格都導進資料庫中:

 

報表2.png 

 

接著建一個查詢,將語句寫好,然後再將這三個表格進行關聯,最後一步——點選“執行”,就能夠得到一個新的關聯表:

 

報表3.png 

 

這種方法是有一定的門檻,要求有一定的資料庫語句基礎,如果完全沒有基礎的話,可以忽略此方法。

 

2)Powerpivot

這是Excel中的建模元件,功能非常強大,利用這個功能也能夠協助你完成報表關聯。如何快速找到這個選單介面?操作方式如下,檢視Excel工具欄上:

 

報表4.png 

 

進入到powerpivot的介面後,點選“從其他源”,選擇Excel匯入,再分別將這3個資料來源匯入:

 

報表5.png 

 

待資料來源匯入後,滑鼠點選“關係圖檢視”,觀察可發現這3個表格沒有任何關聯,我們可以運用連線方式對這幾個表格進行關聯:

 

報表6.png 

 

關聯完成之後,滑鼠點選“透視表”,可見3個表格已經關聯起來了,且能夠自由關聯查詢:

 

報表7.png

 

但是用powerpivot也有一個很大的缺點,那就是資料效能太差勁,如果資料量太大,很容易會被卡死。

 

3)智分析

智分析的資料處理能力非常強大,資料清洗、資料視覺化等都能完美應對,這個大資料分析工具屬於雲端saas。智分析處理Excel檔案非常方便,對Excel使用者非常友好。綜上所述,做多表關聯的話小編更建議大家選擇智分析,與前2種多表關聯方法相比,智分析的處理效能、操作步驟等方面都是更具優勢的。下面給大家介紹一下如何通過智分析做多表關聯:

 

資料匯入

老規矩,先將3個報表匯入到智分析系統,匯入完成後,在資料連線的介面裡找到這3個檔案:

 

報表8.png 

 

然後開啟資料準備裡的自助資料集:

 

報表9.png 

 

進入到自助資料集的介面後,在資料連線裡找到您的資料來源,點選資料來源後,便可以重新整理出明細資料:

 

報表10.png 

 

然後用滑鼠雙擊維度表,剛匯入的兩份報表就會實現自動關聯,這時可以對關聯關係進行設定,例如左連線、右連線等等,這裡我們設定為左連線:

 

報表11.png 

 

重複以上操作,滑鼠點選剩下的那個維度表,也與資料來源進行關聯,這樣3個資料來源就已經關聯好了:

 

報表12.png 

 

現在需要對關聯後報表中存在的重複欄位進行處理,需要對其進行可見性的設定:

 

報表13.png 

 

設定完成後,我們可以對資料進行預覽,現在3個報表已經合併為同1個報表了,有了這個資料集,我們就可以去做其他的資料分析了:

 

報表14.png

 

三、總結

怎麼樣?這3個方法是不是還是挺實用的,前2個方法相對來說對小白不是太友好,需要一定的技術基礎。所以小編還是比較推薦第3個方法,因為只要你會用Excel,就能很快上手,輕鬆完成操作。另外,第3個方法的操作步驟完全不需要打程式碼,且都在視覺化得介面進行操作,能承受的資料量也能大,算得上是目前解決多表關聯的最好方法了。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69965912/viewspace-2847624/,如需轉載,請註明出處,否則將追究法律責任。