交叉報表、樞紐分析表轉換為資料列表(Crosstab to List Table / Pivot Table to Data Table / Unpivot Excel Data)

上週五下班前同事問了一個問題，他手上有一份Crosstab格式的Excel資料，需要轉換成List Data格式的銷售紀錄。該Excel檔案中，橫欄的商品數量為45筆，直列的客戶資料則有21,013筆。若是要轉出包含空白銷售數量的所有資料，總共有94萬5千多筆銷售紀錄，若是扣掉銷售數量為空白的部份，大概也有十幾或二十幾萬筆紀錄。這麼龐大的紀錄勢必無法採用人工作業，一般來說都會寫支小程式來處理，同事問我有沒有什麼可以不用寫程式的方法可以轉出想要的資料。

我完全能理解這樣的感受，畢竟程式寫多了，有時候真的會有點煩，自己從前還在寫程式的時候，也常常會有一種感覺，若是工作中從大到小的各種需求，全部都要靠自己寫程式來處理，會覺得就像是身處於工業革命之前的時代一樣。當時的人們連要用支榔頭都得靠自己手工打造，但是工業革命距今已有三百年了，要用榔頭直接跑趟特力屋就好，甚至在網路上按按鍵盤滑鼠就能買到了。所以資訊工作也應該要與時俱進，若能用現成工具或是別人寫好的Third Party元件的話，就要盡量採用，生命應該浪費在美好的事物上，例如走到隔壁部門跟年輕漂亮的女同事哈啦聊天（誤），而不是要把時間浪費在開發一些低價值的重複性工作上面（而且做完後老闆也不會因為完成了這份工作而誇獎你）。

話題扯遠了，回到原來的問題上。Excel功能這麼強大，用直覺來判斷，靠Excel內建的功能應該能夠做到這樣的需求。不過Excel是這樣，沒用過的功能，即使想破頭也不會知道藏在哪裡，這時候還是要靠咕狗大神給些指引。不過，Excel疑難雜症的中文資源實在不是很多，以本次問題為例，若是輸入「交叉報表、樞紐分析表轉換成資料列表」這樣的關鍵字，找到的都是一些單純樞紐分析表用法而已。建議要查詢Excel的疑難雜症一定要輸入英文關鍵字，搜尋英文資源才比較有可能找到我們想要的資訊。所以若是改成輸入「Crosstab to List Table」、「Pivot Table to Data Table」或「Excel Data Conversion」等字眼，搜尋結果中的其中一個連結就是我們要找的資訊：

https://www.youtube.com/watch?v=F0zNs-cyGqA

詳細的作法描述如下。

首先為了方便解說，先把原始資料改為一個10x10，最多100筆紀錄的Excel檔案。

首先把資料轉換成表格。

將Excel中的資料範圍反白，按下「插入」頁簽中的「表格」區段的「表格」按鈕。

系統會詢問要建立的資料來源範圍，按下「確定」。

完成表格的建立。

利用Power Query進行資料分析與處理。

按下「資料」頁簽中「取得及轉換」區段中的「從表格」按鈕，開啟Power Query編輯器（註）。

將要轉換的資料欄位反白，按下「轉換」頁簽中「任何資料行」區段中的「取消資料行樞紐」按鈕。

原本的交叉報表就被轉換成資料列表格式，總計45筆紀錄。

按下「檔案」頁簽中的「關閉並載入」按鈕。轉換後的資料就會被載入到原本Excel檔的新工作表中。

之後就可以針對轉出的資料進行分析處理了。

註：
Microsoft Power Query for Excel 是一套新的增益集，可為資訊工作者、BI 專業人員及其他 Excel 使用者在資料探索、資料轉換及擴充提供順暢的體驗。舊版 Excel 提供的 Power Query 功能在 Excel 2016 中有新的名稱。它們現在是一組稱為「取得及轉換資料」新功能的一部分，內建在Excel中。Excel 2010及2013則要額外安裝增益集才能使用本功能。可至以下連結下載：
https://www.microsoft.com/zh-TW/download/details.aspx?id=39379