【Excel】資料整理 × EDA × 自動化：進階函數、樞紐分析、巨集、VBA 介紹

多數人拿到資料時，面對茫茫數字大海經常不知所措。因此我們再度邀請到去年佳評如潮的講師 Tim 蒞臨 NTUDAC，帶來三堂 Excel 社課，幫助社員可以用 Excel 輕鬆搞定資料。本堂社課將介紹如何於拿到資料的第一步用函數、格式化條件、樞紐分析表等功能，初步清理資料，進行探索式資料分析（EDA），以及自動化好幫手巨集、VBA 的簡易演練，滑到文末還有 Tim 推薦的 Excel 學習用書喔！

本篇文章 key takeaway：

1. 三大類資料清理常用函數與實戰：文字處理、時間序列、查閱參照
2. 哪個部門平板電腦銷售金額最高？樞紐分析面面觀
3. 超強自動化功能：巨集與 VBA 實作

講師 Tim 郭俊東 簡介
臺大健康政策與管理研究所博士，曾任哈佛大學公衛學院博士後研究員、國防醫學院兼任教師，專長教學論文寫作、統計學及統計軟體。

探索式資料分析（EDA） 相關文章：手把手用R 實現Data Preprocessing

運用函數和格式化條件做資料清理

拿到資料第一件事情，要先看是否為標準結構化資料，即「每一個直的欄位是一個變項」、「第一列是變項名稱」、「每一列是觀察值」

如果這個銷售資料中的產品單價欄位為空白，可以使用熟悉的VLOOKUP來補上資料。

想了解VLOOKUP、其他實用快捷鍵、以及函數，歡迎先閱讀 【Excel Workshop】資料分析必學的常用函數與使用技巧

這邊 Tim 也特別提醒大家，如果有不熟悉的函數，只要輸入函數後，按下輸入欄位左方的「fx」，就可以叫出上圖這個函數設定介面來輔助了！

文字處理函數

=LEFT(擷取目標, 擷取幾個字)
擷取左邊數來特定幾個字

=MID(擷取目標, 從第幾個字開始, 到第幾個數字)
擷取中間特定幾個字

=RIGHT(擷取目標, 擷取幾個字)
擷取右邊數來特定幾個字

=REPLACE(目標, 起始位置, 取代幾個字, 取代成甚麼內容)
指定位置的文字替換

=SUBSTITUTE(目標, 要被取代的字, 取代成甚麼字)
特定文字的替換

=CONCACTENATE（”字串”, “字串”, …)
若要將很多欄位的文字串接起來，會比一直輸入「&」方便許多

文字處理函數實戰 ：要如何將「業務一」替換成「業務人員一」？
輸入 =CONCACTENATE( LEFT( B2, 2), “人員”, RIGHT( B2, 1))，或是=REPLACE( B2, 3, 1, “人員”) & RIGHT( B2, 1)。

時間序列函數

=WEEKDAY(日期, 日期格式)
求特定日期是星期幾

=TODAY()
按下 enter 後就會出現今天的日期

=NOW()
按下 enter 後就會出現今天的日期和時間（幾點幾分）

建議函數列表不會跳出來的隱藏版函數:
=DATEDIF(日期一, 日期二)
按下 enter 後會出現兩日期的差距

時間序列函數實戰 ：遇到下圖中不被 Excel 認可的日期資料該怎麼辦？

Step 1. 這時候就要運用 Excel Workshop 中提到的資料剖析功能啦！在【資料】索引標籤中點選【資料剖析】，呼叫出資料剖析精靈，選取「固定寬度」

Step 2. 手動將分欄線拉到想要的位置

Step 3. 點選「日期」，並選擇目標儲存格

Step 4. 按下確認後，原先的錯誤格式 1 就會被分派到圖中三個不同欄位。接著於 M2 儲存格輸入 =DATE( C2+1911, D2, E2 )，錯誤格式 1 就會被轉換成正確的西元日期格式了！

查閱參照函數

上週的 Excel Workshop 中介紹了包含 RANK、MATCH、INDEX、OFFSET、INDIRECT 等函數，這邊先簡單複習後，將進入本堂重頭戲：「建立查閱參照表」。

=RANK(查找值, 參照範圍)
欲尋找某一數值於特定範圍中的排名

=MATCH(搜尋值, 搜尋範圍, 搜尋類型)
搜尋儲存格範圍中的指定項目，結果會是一個數字(第幾欄)

=INDEX(表格範圍, 索引值(第幾列), 索引值(第幾欄) )
根據輸入的索引值取出表格中指定位置的資料。

=OFFSET(起點, 向下移動幾格, 向右移動幾格)
傳回根據所指定列數及欄數，上下移動幾格不包含起點格位置

=INDIRECT( ref_text )
傳回文字串所指定的參照

查閱參照函數實戰：如何根據下圖的資料，查詢各個縣市的年底人口數、面積、人口密度？

Step 1. 建立含有年度、縣市、指標、數值之表格

Step 2. 利用上週 Excel Workshop 所學由資料驗證中的清單來建立「下拉選單」，建立縣市（A列）以及指標（第3欄）的清單

Step 3. 從縣市右方儲存格方建立的清單選擇「總計」，並在其右方儲存格輸入 =MATCH(H3,A4:A25,0)，按下 enter 後便會得到「1」，意思是「總計」位於選取範圍（A4:A25，直排縣市）的第 1 位。

Step 4. 接著在「年底人口數」右方輸入=MATCH(H4,B3:D3,0)，按下 enter 後也會得到「1」，意思是「年底人口數」位於選取範圍（B3:D3，橫排參數）的第 1 位。

Step 5. 在「數值」右側的儲存格輸入=INDEX( B4:D25, I3,I4)，意思是選取範圍（B4:D25）中的第 1 列、第 1 欄，即得到「年底人口數總計」的值。

Step 6. 在「數值」右側的儲存格輸入=OFFSET( A3, I3, I4)，意思是從「區域別」（A3）開始，向下走 1 列、再向右走 1 欄，便會到達「年底人口數總計」的儲存格，求得與 INDEX( ) 同樣的值。

此外，我們也可以在「數值」右側的儲存格輸入=OFFSET( 109!A3, I3, I4)，意思是參照「109」這個工作表中的 A3 儲存格，那如果想要查閱 108 年的資料自然就得輸入=OFFSET( 108!A3, I3, I4)。
不過呢，從 Step 6. 圖可以看到下方有許多工作表，這些工作表中的格式除了年份之外皆相同，於是 Tim 傳授了一個新的方法。首先，將「年度」右方的儲存格（H2）同樣建立一項包含下方工作表年份（103–109）的清單；接著將「數值」右側的儲存格輸入=OFFSET( INDIRECT( H2&”!A3”), I3, I4)，如此一來，不論在 H2 的年份清單選擇哪個年份，Excel 都會透過 INDIRECT() 函數，自動理解為 =OFFSET( 年分!A3, I3, I4)，藉此參照要求該年份工作表的資料，不需要再一個個點選不同工作表中的參照值了！

Step 7. 現在我們在「縣市」、「指標」右方清單中選取「臺北市」、「土地面積」，下方就會出現臺北市的土地面積「272」。做到這裡時，就代表一個查詢參照表已經完成啦！

樞紐分析面面觀

當我們看到下面這張表格時，也許會想要知道「何種產品銷售金額最高？」、「筆記型電腦中，哪種型號銷售金額最高？」、「哪個部門的平板電腦銷售金額最高？」等問題。這些問題如果自己慢慢用肉眼搜尋和加總就太慢了！Excel 的樞紐分析表可以幫助我們迅速加總特定變項的數值，可說是最實用的一項功能呢！

而在進行樞紐分析表製作前，可以先選取【資料】中的【從表格／範圍】，將這些資料變為表格形式。當然不一定要變成表格形式，但表格形式有兩個好處，一是插入樞紐分析表時可以直接輸入該表格名稱；二是表格內容新增資料或異動時，樞紐分析表可以使用重新整理功能來自動更新。

樞紐分析表相關介紹：【Excel】資料清理必學函數與技巧

樞紐分析表實戰：哪個部門平板電腦銷售金額最高？

Step 1. 選取【插入】中的【樞紐分析表】，在「表格／範圍」中填入「表格1」（剛才由這筆資料所建立之表格的名稱）

Step 2. 將「產品」放到欄，「業務部門」放到列，「銷售金額」放到值

Step 3. 上一中步驟已可以看出業務四有最高的平板電腦銷售金額，但如果有 100 個部門時，我們可以從【常用】中的【設定格式化的條件】中選取「色階」，讓最高金額的儲存格呈現出一目了然的紅色！

表格外觀的選擇

點選樞紐分析表格內任一儲存格後，會出現【設計】，當中可以自由選擇樞紐分析表樣式、帶狀欄、列等，幫助你的表格呈現更加美觀易懂。

重新整理

若小時候總是將墨水翻倒在數學課本上的小明長大之後，依然笨手笨腳，不小心把業務四販售平板電腦的部分資料輸錯，本文稍早提到的表格形式好處就出現了！當我們將原資料改回正確數值後，只要按下【分析】中的【重新整理】，業務四的平板電腦銷售金額就會從「426000」變為下張圖中的「342000」!

Excel 巨集與 VBA 實作

關於 Excel 巨集與 VBA 的先備知識歡迎閱讀此篇文章 【Excel】 VBA 與巨集介紹- NTU Data Analytics Club，這邊我們就直接開始實際演練吧！

Excel 巨集

Step 1. 開啟【開發人員】索引標籤方便使用巨集功能

Step 2. 按下【開發人員】索引標籤中的【錄製巨集】

Step 3. 可替該接下來要錄製的巨集命名，並且自行設定快速鍵。上圖中的巨集名稱為「範例」，巨集操作快速鍵為「Ctrl+Shift+C」。按下確定後，任何對於工作表的操作都會被自動記錄在「範例」這個巨集中，之後一按下「Ctrl+Shift+C」就會被召喚出來執行

Step 4. 這邊的巨集錄製進行了兩項動作：把「總計」和「台灣省」兩欄標記顏色、將工作表放大顯示比例。執行完預計操作後，按下【開發人員】中的【停止錄製】即可。

Tim 建議開始錄製巨集前，可以先想好要進行那些操作流程，以便錄製各項步驟，而且巨集的操作是無法簡單按上一步就可以回復的。

Step 5. 這時，只要在相同格式但不同年份的工作表「107」中按下剛才設定的快捷鍵「Ctrl+Shift+C」，工作表「107」的「總計」和「台灣省」兩欄就會被標記顏色、工作表的顯示比例也會被放大了！

VBA 實作

在【開發人員中】可以點選Visual Basic，可以檢視巨集之VBA（Visual Basic for Applications）指令，除了以錄製巨集方式建立自動整理資料的功能，也可以直接依需求修改VBA程式碼。

我們可以藉由簡單修改 VBA 中的幾行程式碼就改變巨集的運作方式，對於不擅寫程式的朋友可以說是相當友善。這邊就來簡單示範 VBA 程式碼的修改吧！

Step 1. 只要點選【開發人員】【巨集】「編輯」

Step 2. 就可以進入此圖的 VBA 程式碼。這當中記錄了先前所錄製的不同巨集操作，被長長的黑線所分隔，Sub 後面便是巨集名稱。

這邊我們以上圖中簡單的「小練習1哈囉」和「小練習2六都」示範，原先按照這樣的程式碼操作時，執行結果會變成下圖

小提醒：執行巨集時除了直接按快捷鍵外，也可以點選【巨集】，選擇想要執行的巨集並「執行」。

但如果我們將內容改成上圖這樣的話，「小練習1哈囉」和「小練習2六都」的執行結果就會變成下圖

如果需要整理跨工作表的資料，可以加上迴圈指令，即可在指定的工作表範圍執行重複動作，方便整理或整合多張工作表的資料。

以上就是 VBA 程式碼介面，以及編輯指令的簡單示範！

最後感謝 Tim 為我們帶來如此實用的內容，讓社員掌握了使用函數和樞紐分析表做資料清理和 EDA 的技巧，以及快速入門 Excel 巨集和 VBA，幫助社員日後若想持續鑽研植入健壯根基。此外，Tim 也推薦了幾本 Excel 學習用書，包含《Excel自學聖經》、《Excel VBA超效率工作術》、《Excel+Tableau成功晉升資料分析師》。希望閱讀到這裡的你收穫滿滿，也歡迎鎖定下篇社課精華，Tim 將為我們介紹 Excel 的統計操作！

歡迎各位拍手交流，NTUDAC 也會持續在 Medium、Facebook 與 LinkedIn 更新每週社課的資訊，歡迎對資料有興趣的大家持續關注，跟我們交流想法！！