【Excel】資料清理必學函數與技巧
非常榮幸邀請到 Tim 為我們上 Excel 專題社課，Tim 先利用大家熟悉的 DAC 團體面試的資料集，由淺入深的講解了資料清理的重要步驟，以及如何運用樞紐分析產出簡單易懂的圖表。之後再利用「臺灣各縣市人口密度」資料集，帶領我們認識 RANK、INDEX、MATCH、OFFSET、INDIRECT 等函數，一起進入 Excel 的奧妙學習殿堂吧！
本篇文章 key takeaway：
- 在資料分析前定義好問題以釐清目標
- 使用函數進行資料清理與資料核對
- 利用樞紐分析快速彙整大量的數據並視覺化
講師介紹：
郭俊東（Tim），臺大健康政策與管理研究所博士，曾任哈佛大學公衛學院博士後研究員，專長教學論文寫作、統計學及統計軟體，現為國防醫學院兼任教師。
資料清理
資料清理的第一步為加上明確標題，以便於事後分析的資料辨別。
但當想複製的目標標題欄位，與現有資料集的擺放方式不同該怎麼辦呢？
這時候可以複製目標標題欄位，再將滑鼠移到資料集標題欄位上，按右鍵選取貼上選項下的「轉置」就可以完成了。
接下來要整理資料集的擺放順序，我們已經有標題了，接下來要利用函數「vlookup」，搭配編號來整理，將原本凌亂且無邏輯的擺放順序，整理成跟副檔「變數註解」一樣，如此一來可以讓後人直接參照「變數註解」檔案，能更直觀了解資料集的內容，同時也能增加分析效率。
先來簡單介紹「vlookup」這個非常常用的函數，基本上在職場中「vlookup」為必備技能，能讓你事半功倍地核對資料。
特別注意 1：要用上堂課所教的「f4」或「$」固定範圍，或是直接把查閱範圍設定為整行，否則查閱範圍會跑掉喔！
特別注意 2：「vlookup」只能就查閱值於查閱範圍向右尋找唷，也就是說，如果把要找的東西放在目標範圍的最右邊，是完全找不到任何值的！
在查找完各個標題真正的順序後，接下來把整個資料集全選，點選「排序與篩選」中的「自訂排序」，再如圖，將排序方式依據設定為放置編號的第一列（列 1），順序為最小至最大，如此一來，就可以得到排序有邏輯的資料集囉！
接下來我們來處理資料集中電影名稱裡多餘的空格及問號。
首先我們可以利用「trim」這個函數刪除儲存內的空格，這個函數非常簡單，函數中只要填入去除空格的目標儲存格即可。
再來，我們可以利用三個方法去除「？」：
一是取代法，將問號直接取代成空白。要注意取代「?」時，需鍵入「~?」，才能成功取代問號。
二是資料剖析，將問號從其他字元分割出來，再將問號欄位刪除就大功告成。
三是利用兩個函數的合成出 =left(目標欄位,LEN(目標欄位)-1)，可以去除位於儲存格中最右邊的問號，先簡介一下要用到的兩個函數：「left」和「len」
但是有了「left」還不夠，因為每一個電影名稱長度不同，我們沒辦法直接輸入想取出的字數，所以要靠「len」的幫忙。
綜合以上，我們可以寫出 =left(目標欄位,LEN(目標欄位)-1) 這個合成函數，它代表取出目標欄位中自左數來，除了問號以外的字元，這樣就大功告成囉！
接下來，我們需要檢視這個資料集裡的極端值，因為極端值會影響最後的分析結果。找出極端值的方法非常簡單，使用「篩選」中的「排序」功能，就可以找查看最大或是最小值。
再來，要確定資料集中的資料都沒有重複項，否則會影響分析的準確性。我們可以利用「條件式格式設定」中的「醒目提示儲存格規則」列表中的「重複的值」來標示出重複項。
找出重複項後，可以利用先前提到的「排序」找出儲存格有顏色的欄位（也就是上面條件化格式設定的結果），再檢查看看是否有恰巧同電影名稱的情形。
都檢查完之後，我們要來刪除重複項，點選「資料」中的「移除重複項」，就完成整個資料清理過程啦！
定義問題
接下來，我們進入定義問題階段，這是非常重要的步驟，若定義不夠謹慎，可能會導致後面的分析都付之一炬囉！所以在分析之前，千萬要停下腳步再確定一次自己要找的目標定義。
這邊以「最賺錢的導演」做說明，最賺錢的定義絕非單單僅以票房決定，我們還必須考量拍片成本，因此，在分析「最賺錢的導演」之前，我們要再多一個步驟，將票房減成本來算出利潤。
樞紐分析
當以上都完成後，我們得到一個乾淨且整齊的資料集，接下來利用「樞紐分析」來製表及製圖，加強論點的說服力。
樞紐分析可以依照自己的需求做條件設定調整，比方說：看平均值就按右鍵、點選「摘要值方式」選取「平均值」；或是看排名就按右鍵進行數值大小的排序。若更進一步想看該條件表現前十名，則可以在列標籤按右鍵點選「值篩選」中的「前十項」。如果想進行條件篩選（例如只想分析美國的電影），可將 country 欄位拖拉到「篩選」中。
樞紐分析還有一個很棒的功能是視覺化，可以依據樞紐分析結果插入不同圖表，也可以點選「變更圖表類型」根據需求隨時更換圖表類型。除此之外，樞紐分析圖表還有雙軸的功能，可以點選「變更圖表類型」選取組合圖，再勾選「副坐標軸」就完成啦~
以上是我們拿到只有一個工作表的簡單資料集之初步資料清理步驟，接下來，我們要介紹多張工作表的資料集之處理方式。倘若工作表之間需要互相比較、查找資料的話，可以應用的函數有：RANK、INDEX、MATCH、OFFSET、INDIRECT。
資料核對與查找
比較資料排名：RANK
RANK 函數的功能是查找目標值位於指定範圍內的排名，所以我們可以利用它來查詢「各縣市人口密度」資料集中六都的人口數、土地面積和人口密度之排名。
RANK(查找值,查詢範圍)＊如果需要固定查詢範圍的話，可以善用前幾堂課講的：「$」、「f4」
（mac 使用者可以用「cmd + d」）
但是我們在實際處理時，於輸入查詢範圍遇到困難，就是這份檔案中有包含「台灣省」的資料，所以在所有縣市進行比較時，排名會錯誤。解決方法有兩個：
一、直接跳過「台灣省」來查找：
把被截開的上下兩個範圍以逗號分離並用括號框起來。
範例：=RANK(B6,($B$5:$B$10,$B$12:$B$30))
二、利用定義名稱：
（1）到「公式」內的「定義名稱」，將所有縣市名稱定義為「縣市」
（2）直接選取所有欄位，至左上角空格輸入「縣市」
建立查詢選單：INDEX、MATCH、OFFSET、INDIRECT
INDEX
INDEX 為一個可以回傳數值的函數，可以在有欄列組合的二維範圍內進行查找。
Index（查找範圍, 第幾列, 第幾欄）
查找範圍：希望搜尋的指定範圍
第幾列：於該範圍中第幾列
第幾欄：於該範圍中第幾欄MATCH
MATCH 可以搜尋出指定欄位於指定範圍內之位置，但僅可以用於單列/欄之一維範圍。
MATCH（查找值, 查找範圍, 比對方式）
查找值：搜尋目標
查找範圍：目標搜尋之指定範圍
比動方式：0—完全符合查找值/ 1—小於等於查找值/ -1—大於等於查找值MATCH 有個強大的搭配組合，就是「清單」。我們可以先設定儲存格有清單選取功能（於下一段介紹），再利用 MATCH 函數設定該清單儲存格為查找值，如此一來，我們便可以從清單內點選想看的主題，再直接顯示出該主題位於哪個位置。
清單製作方法非常簡單，點選「資料」中的「資料驗證」，再點選「清單」後，即可於指定儲存格製作出方便選取的選單囉！
介紹完 INDEX、MATCH 及選單後，我們可以實際將三者作結合，就會得到一個非常便捷的選單查詢欄位。只要點選 AREA 下面的儲存格，更改城市名稱，或是點選 VARIABLE 下的儲存格，更改想看的主題，都可以直接得到所求解答喔！
OFFSET
OFFSET 這個函數的功能其實跟 INDEX 有點像，都可以拿來回傳數值。不同的是，INDEX 是查找指定範圍中的指定列、指定欄，而 OFFSET 是以儲存格為基準點，再查找移動指定行數、指定列數後的欄位數值。
OFFSET（參照儲存格, 移動幾列, 移動幾行）
INDIRECT
INDIRECT 可以直接拿來做欄位參照，但必須要先有線索才能回傳數值。
INDIRECT（指定欄位）接下來，只要將 INDIRECT 嵌入前面所述的 INDEX 函數中，我們就可以設計跨工作表搜尋的功能（如跨年分搜尋，下圖為範例）。特別要提的是 INDIRECT 內的工作表的表示方法，「K4&“!B4:B30”」中的「K4」代表想要切換到的工作表名稱欄位，「&」連接變數和固定字元，「“!B4:B30”」則代表無論切到哪張工作表，查找範圍皆固定於 B4:B30 這個區間內，而「“”」內代表欲呈現的固定字元，「!」則是放在變數後，表示是以變數為名的那張工作表。
範例：=INDEX(INDIRECT(K4&”!B4:B30"),G5,H5)
