Managertoday 經理人

處理多張Excel報表一定要會!進階版的樞紐分析,不同工作表也能快速整合

2019-11-18 20:02:42
Managertoday
https://bnextmedia.s3.hicloud.net.tw/image/album/2017-03/img-1489764042-64603@900.png
樞紐分析是許多工作者最喜歡用的Excel工具之一,方便交叉分析、整理資料。但樞紐分析的限制是只能處理單一工作表的資料,而企業內部的報表通常分散於不同的檔案和不同的工作表,如果想要整合,那你一定需要學會進階版關聯式樞紐!

樞紐分析是許多工作者最喜歡用的Excel工具之一,方便交叉分析、整理資料,據Microsoft MVP微軟全球最有價值專家王作桓所說,只要學會運用樞紐,即可完成85%以上的公司報表。

延伸閱讀:Excel高手最愛用!3步驟學會超強大「樞紐分析」,資料處理再也不愁

但樞紐分析的限制是只能處理單一工作表的資料,而企業內部的報表通常分散於不同的檔案和不同的工作表,如果想要整合,就要學習樞紐分析的進階版: 關聯式樞紐 !如果了解關聯式樞紐分析的應用,肯定為你節省不少整理報表的時間。

假設小劉在經理人月刊擔任業務,他想知道2016年經理人月刊特賣會上哪些類別的書籍特別受人喜愛,方便未來推銷品時,可以有一個明確的行銷方向。因此,他從銷售部門資料庫拿到了 「訂購資料表」「書籍條目表」 ,前者將一筆筆訂單記錄下來,後者則是將各種產品編號的明細,記載每個編號對應的產品名稱。


訂購資料表


書籍條目表

小劉想運用樞紐分析,整理這兩個工作表的訂購資訊,才能看出哪一本書、哪種類別最暢銷。

首先要做的設定:設定表格格式

要使用關聯式樞紐,必須將每一個用到的資料都設定成 表格 ,設定方法如下:

STEP1 選取需要的資料範圍→點選「常用」裡的「格式化為表格」

STEP2 任選一個想要的表格樣式

當表單上方出現可篩選的按鈕,就表示已經設定為表格格式了。

建立關聯式樞紐

在「訂購資料表」中點選「插入」→「樞紐分析表」。只要資料表之間沒有空行或空欄,EXCEL會自動選取完整的表格範圍。之後就可以開始建立關聯式樞紐啦:


STEP1 點選其他表格,建立新的樞紐分析表

在右側的「樞紐分析表欄位」點選「其他表格」,Excel會再建立一個新的樞紐分析表。


STEP2 選擇兩個表單裡所需的欄位

小劉因為想了解哪些類別各出售了多少書,因此他在右側「書籍條目表」中的「類別」欄以及「訂購資料表」中的「數量」欄打勾, 讓這兩個數據出現在新的樞紐分析表中。點擊完後會出現一個黃色框框,請在「可能需要表格之間的關聯」訊息中按下建立。


STEP3 設定兩張資料表間的關聯

在兩張不同工作表間,可能會有重複的資料(某一欄),可以透過「建立關聯」讓兩份資料有所連結。

在這個範例中,「訂購資料表」裡的「產品編號」,與「書籍條目表」裡的「書籍識別碼」為相同資料,可以透過「建立關聯」來告訴EXCEL,藉由比對產品編號和書籍識別碼,將兩張表單的所有資料連結在一起。

剛剛按下建立關聯後,Excel會跳出新的視窗,詢問你要根據哪兩列資料做比對。在本範例中,我們是從「訂購資料表」中插入樞紐分析表,因此第一列的表格一欄,要點選「訂購資料表」,而右邊的欄(外部),要選擇此表單的「產品編號」這一欄。

第二列關聯表格就是你想要Excel建立關聯表格(書籍條目表),右邊關聯欄則是該表格中,與產品編號資訊相同、要用來比對的「書籍識別碼」這一欄。

按下確定後,表示你已經成功將兩張表單的資料整合在一起,能直接進行樞紐分析了!

STEP4 運用樞紐分析,得知你需要的資訊

點選右邊想要呈現在分析表的資訊,即可完成一份完整的報表。

當下次使用Excel的樞紐分析表時,別忘了運用看看關聯式樞紐分析,輕鬆的整合了不同工作表的海量資訊,也能讓工作更有效率。

延伸閱讀 /

1. 絕對用得到!6個Excel小訣竅讓報表更專業,還有14個超棒快速鍵

2. Excel基礎班看過來!最實用5大函數在這裡:IF、SUMIF、COUNTA、COUNTIF、MAX

圖片製作 / 劉珮琦