【Excel Workshop】資料分析必學的常用函數與使用技巧

為了讓組成多元的 NTU DAC 社員能夠快速銜接未來的 Excel 課程，我們在 Excel 社課正式開始之前，進行一堂 Excel Workshop，並由社團內資歷豐富的幹部教導社員工作或未來專案中常運用到的 Excel 技巧。

而在社課的最後，幹部也與社員分享資料分析八大步驟，並教導社員其中的前兩步【資料清理】與【資料擷取】，並以團體面試的經驗提醒社員，千萬不要在拿到資料集後，就一股腦栽進去分析！

本篇文章 key takeaway：

1. 10 個常用的 Excel 快捷鍵
2. 其他重要函數與使用技巧
3. 資料分析的八大步驟簡介

10 個常用的 Excel 快捷鍵

1. 快速移動/選取

• Windows: Ctrl + (Shift) + 方向鍵
• MacOS: Command + (Shift) +方向鍵

以Windows為例
在 Excel 中選定任一儲存格，接著使用 Ctrl + 方向鍵，即可在不依靠滑鼠的狀況下，自由移動；另外，若想快速選定該行或列的儲存格，只是使用 Ctrl + Shift + 方向鍵，即可達成！

2. 輸入後向上/左移動

• Windows: Shift + Enter / + Tab
• MacOS: Shift + Enter / + Tab

也可以使用 Shift + Enter 向上方一個儲存格移動，或使用 Shift + Tab 向左方的儲存格移動唷！

3. 移動到其他試算表

• Windows: Ctrl + Page up / Down
• MacOS: Option + Left / Right

若是檔案中有多張試算表，即可用上方的快捷鍵快速切換。

4. 移動整頁

• Windows: Page up / Down
• MacOS: Fn + Up / Down

若檔案資料較多，使得版面篇幅較長，也可以透過快捷鍵快速切換整頁頁面，找到想要停留的區塊。

5. 快速加總

• Windows: Alt + =
• MacOS: Command + Shift + T

相信經常使用 Google Sheet 的人都會有一個想法，希望 Excel 也有自動偵測公式選取區段的功能，那你一定不能不知道最常用的公式 SUM() 的快速用法！

以 Windows 為例
只要在想要加總的儲存格按下 Alt + = ，系統即會自動選擇需要加總的區段。

不過這邊要特別注意的是，多欄橫向加總，可能會導致公式偵測錯誤，把前面加總的多列結果進行加總，這種時候在儲存格左上方會出現綠色三角警示，只要進入選擇「複製上方公式」，即可改善這個錯誤唷！

6. 切換參照

• Windows: F4
• MacOS: Command + T

看名稱好像很難理解，但其實這代表著函式中的固定項目，舉例來說，當我們撰寫公式，希望每一列儲存格都乘上 A1 這個儲存格，就需要在函式中將 A1 固定，避免拖曳複製函式的時，讓其隨之變動。

而操作方式則是對著函式中的儲存格按下快捷鍵，函式中會隨之出現錢字號($)，代表它是固定的項目，如: B1*$A$1。

另外，若我們僅固定單欄、單列，也可以對著函式中的項目重複點擊，單次點擊為固定列和欄、兩次點擊為僅固定列、三次點擊為僅固定欄。

7. 快速選擇函式

• Windows: Tab
• MacOS: Tab

在輸入函式的過程中，系統通常會根據輸入的關鍵字推薦函數，當我們使用方向鍵上下選定推薦函式後，需按 Tab 選定，而非 Enter 唷！

8. 快速轉換成百分比

• Windows: Ctrl + Shift + %
• MacOS: Ctrl + Shift + %

在計算變量時，我們經常使用百分比表示其變動的比率，而對儲存格使用Ctrl + Shift + % 即可快速切換儲存格類別為百分比唷！

9. 快速尋找

• Windows: Ctrl + F
• MacOS: Command + F

在工作的過程中，常常需要大海撈針，這時候一定要善用快捷鍵，透過搜尋關鍵字的方式，快速尋找到需要的項目；另外，在此處也可以搭配替代功能，讓找尋到的目標儲存格內容更換成指定的內容。

10. 重複上個動作

• Windows: F4
• MacOS: Command + Y

以 Windows 為例
在 Excel 當中，可以透過 F4 重複上個動作，快速合併儲存格、新增上下方列等動作，幾乎沒有其無法重複的功能。

不僅如此，這個功能在 PowerPoint 和 Word 也都是適用的唷！可以協助使用者，快速調整多個項目的格式，如: 粗體、斜體、顏色等，真的是職場必備技能呢！

其他重要函數與使用技巧

1. 變數命名

在需命名的儲存格上點右鍵，選擇定義名稱，這樣做的好處是，未來打公式時，可以直接打被定義的變數名稱，尤其需要跨工作表計算時，可以縮減許多撰寫尋找儲存格、撰寫公式的時間。

以上方的表格為例
一開始先為各國匯率的比率設定好名稱，像是 33 的儲存格為歐元匯率、30 的儲存格為美元匯率等。接著在下方計算 300 元台幣等於多少外幣時，即可於欄位中撰寫，= 台幣金額 (變數儲存格) / 歐元匯率 (被定義變數名稱)，讓公式更加一目了然！

2. 隔行取值

=OFFSET(參照的儲存格, 列, 欄)
根據設定的列與欄條件，從參照的儲存格開始移動，並傳回最終位置儲存格的值。

以上方的表格為例，我們在 A3 輸入=OFFSET(D3,3,-2)，會回傳 B6 儲存格的值，代表從參數位置 D3 開始，下移動 3 列，向前移動 2 欄，最終到 B6 。

而隔行選取即可結合這個觀念，搭配ROW()函數創造等差數列。
以上方的表格為例，我們可以發現價目表的金額成等差數列，若我們需要在右方表格的 K 欄填入各品項的金額，即可在 K67 撰寫函式 =OFFSET($H$65,(ROW(1:1)-1)*2))
利用 ROW(1:1)-1)*2 創造一個 0, 2, 4, 6… 的數列，整個函數將從 H65 開始回傳儲存格 (H65, H67, H69…) 的數值。

｜補充｜
1. 此處使用ROW()的原因，是為了使常數下拉時不會隨著更動，若直接撰寫數字1，則會在下拉的過程中隨之變動為2、3、4...。
2. 公式內將參照儲存格使用【切換參照】的固定功能，是為了避免在拖曳複製的過程中，參照儲存格隨之變動。
3. 另外，此撰寫方式僅適用於固定跳列的情況下，更複雜的取值方式就留給大家自行研究拉！

3.累加

累加的技巧，需搭配上方所介紹到的【切換參照】，使我們在計算的過程中，不會影響到前面所輸入的公式或數值。

4. Dashboard 製作

=INDIRECT(參照儲存格)

可以告訴使用者儲存格內的值，以上方的表格為例，當我們在 A139 儲存格輸入=INDIRECT(D139)，A139 則會顯示 D139 儲存格中的值 — D139。

若有多張工作表，則可以先在 INDIRECT() 函式中輸入工作表名稱，利用該函是快速回傳各表單內指定範圍的值，再進行後續運算。

以上方的表格為例，該檔案中具有:
- 命名為 202001, 202002, 202003, 202004 多張工作表，如同上圖的 G 欄之命名
- 每張工作表中均具有交貨成本、利潤、單價、折扣、訂單數量等相關資訊

當我們要填入 1 – 4 月總訂單量於 H 欄時，即可於 H126 撰寫函式
=SUM(INDIRECT(G126&”!W2:W21"))
而函式中的 G126 為 202001 的儲存格，而 202001 的工作表中 W2:W21 為其每次交易訂單數量，也因此我們能透過 SUM 和 INDIRECT 函數，順利回傳各月份的總訂單量。

｜補充｜
此處需要特別注意的是，公式中間要使用&連接字串！

5. 進度表製作（下拉式選單）

• 下拉式選單
  使用【資料】中的【資料驗證】，選擇儲存格內允許清單，並於來源內輸入供選擇的選項，即可簡易建立下拉式選單唷！

｜補充｜
此處可以使用【常用】中的【設定格式化條件】，讓不同選項的儲存格呈現不同的顏色或樣貌，使得流程進度更加醒目唷！

• 資料驗證的日期
  若輸入的時間不在限制的範圍內，可以跳出提示訊息，例如: 提醒使用者填寫的項目完成時間不在區間內等。

6. 進度表製作（雙層下拉式選單）

• 適用情況：當選擇某個選項後，下一題就會出現對應的選項。
  例如: 在填答地址時，於縣市選擇台北市，則下一題鄉鎮區選項就只會有台北市的區域 。

• 操作說明(以上方的表格為例)：
  Step 1: 結合前面介紹過的【變數命名】，將表格的變數定義完成，例如:定義台北市的鄉鎮區名稱為台北市。
  Step 2: 點選城市下方的儲存格，使用【資料】中的【資料驗證】，設定來源為台北市至台中市的範圍。
  Step 3: 點選區下方的儲存格，一樣使用【資料】中的【資料驗證】，但此處的來源為=INDIRECT(城市下方的儲存格)，讓選項根據前面的結果變動。

一開始系統上方該則訊息，那是因為我們前方的選項尚未選擇，只要點選「是」繼續，即可如下圖正常顯示依據前題，而變動的雙層下拉式選單！

7. 條件加總

• =COUNTIF(檢驗範圍, 條件)
  條件計算，當檢驗範圍的儲存格，符合條件時，即計數一次。
  例如: =COUNTIF(A2:A5,”倫敦”)，計算儲存格 A2 到 A5 中值為倫敦的儲存格個數。
• =SUMIF(檢驗範圍, 條件, 符合條件時的加總範圍)
  條件式加總，當檢驗範圍符合條件時，即加總指定的加總範圍。
  例如: =SUMIF(A2:A5,”>160000",B2:B5)，假設檢驗範圍中的 A2 及 A4 儲存格值符合 >160000 時，即會加總 B2 和 B4 的數值。
• =COUNTIFS(檢驗範圍, 條件, 檢驗範圍, 條件…)
  適用於有多個檢驗條件時，同時符合多個條件時，才會計算一次。
• =SUMIFS(加總範圍, 檢驗範圍, 條件, 檢驗範圍, 條件…)
  適用於有多個檢驗條件時，同時符合多個條件時，才會進行加總。

｜補充｜
上方的公式均可以使用公式均SUMPRODUCT()函式計算，大家可以自行研究看看!!

資料分析的八大步驟簡介

在拿到資料集時，千萬不要急著一頭栽進資料裡！

以上方團體面試的資料集為例，建議大家先定義問題與釐清目標，需要釐清的目標可能像是資料背景是什麼、聆聽的受眾是誰…等，可以更快速掌握資料集中應該處理的重點項目，快速地產出一份有意義的報告唷！

接下來介紹過去課堂講師 David Huang 所教授的資料分析三部分與八大步驟，而此段落將著重介紹前兩步【資料清理】與【資料擷取】。
(來源出處：https://ntudac.pse.is/xageb)

1. 資料清理:

資料清理的內容主要為處理空值/刪除重複值、髒資料整理與建立新變數三個部分

• 資料剖析
  當原始資料中的變數內容複雜，或是欄位沒有對齊時，即可使用資料剖析的功能，根據符號或是固定寬度切割資料至不同的欄位中，進行第一步清理的動作。
• 篩選/尋找與取代
  【排序與篩選】和【尋找與選取】也是處理資料中常用的功能，可以透過【排序與篩選】挑出想要的資料，也可以使用【尋找與選取】的功能，快速尋找出含 NaN 的儲存格，並將其替代為平均值等。
• 移除空白列
  當資料中存在部分空值，我們可以選擇刪除含空值的整列或計算該欄平均數後填入。
  而刪除含空值的整列，可以在選取刪選範圍後，使用【尋找與選取】中的【特殊目標】，並選擇空格，快速選取多個包含空格的儲存格，並一次刪除列。

2. 資料擷取

• =RIGHT(儲存格或擷取之文字字串, 擷取字元數)
  根據指定的截取字元數，傳回目標中由右至左的字元或字元組。
  例如: A2 儲存格內為"台大數據分析與決策社"，則 =RIGHT(A2)，會回傳 A2 文字中的「社」字。
  
  =LEFT(儲存格或擷取之文字字串, 擷取字元數)
  根據指定的截取字元數，傳回目標中由右至左的字元或字元組。
  例如: =LEFT("台大數據分析與決策社", 2)，會回傳「台大」兩字。
  
  =LEN(儲存格或擷取之文字字串)
  文字字串中的字元數。
  例如: =LEN("台大數據分析與決策社")，會回傳 10。

若取值數量相同，大多數人會使用 RIGHT()、LEFT() 等函數，但若需要取值的目標數量不一致時，則可搭配 LEN() 使用。

以上方的表格為例，若我們想要獲取不包含「國立」二字的學校名稱，則公式撰寫為 =RIGHT(LEN(儲存格)-2) ，LEN(儲存格)表示整格內含文字總長，所以此公式可以得到我們由右至左獲取文字總長 -2 的儲存格值，而 -2 正好代表儲存格的「國立」二字；但需要注意的是，此應用仍局限於，要刪除的字數一致時。

• 計算不重複數:
  除了利用上方介紹的函數計算不重複數，這邊與大家分享一個好用的函數。
  
  =SUMPRODUCT(array1，[array2]，[array3]，…)
  協助欄相乘，並將各列的相乘結果相加。
  
  使用 SUMPRODUCT() 計算不重複數，公式如下
  =SUMPRODUCT(1/COUNTIF(F244:F261,F244:F261))
  在此公式中 COUNTIF(F244:F261,F244:F261) 代表在 F244:F261 範圍中計算重複的值，其結果會像是 {1, 5, 5, 3, 7, 2, 3…}，計算每一個值重複的次數；當然，若「台大」於資料中出現五次，則集合內就會有五個 5。
  
  而 1/COUNTIF(F244:F261,F244:F261) 的功能，則是將該集合通通換成倒數，才能使最終加總的數量為該範圍的不重複數。
  例如: 在一個五筆的資料中，「台大」於資料中出現兩次，「政大」於資料中出現三次，則集合為 {2, 3, 3, 2, 3} ，倒數加總後 1/2+1/3+1/3+1/2+1/3=2 ，即計算出五筆學校中的不重複數為 2，也就是台大與政大。
  
  為什麼不可以使用 SUM() 呢？
  這其實資料型態的問題，因為只有 SUMPRODUCT() 可以處理陣列(集合)，若真的要使用 SUM 函數計算，則需將資料型態為array。

｜補充｜
使用SUM函數時，若要將資料型態更改為array，可以在函式撰寫完畢時按Ctrl+Shift+Enter，而非原先的Enter，會發現函式中自動出現代表資料型態為array的{}!

• 描述性統計
  常用的函數像是計算標準差的 STDEV()、計算 PR 值的 PERCENTRANK() 等。
• 追蹤前導參照
  對著有使用公式的儲存格，點選【公式】中的【追蹤前導參照】即可了解該儲存格中的公式步驟，像是其加總或平均範圍，是先乘再除等。
  不僅是一個協助debug的方式，若一份檔案有多人協作，也可以透過此功能了解上一位使用者所撰寫的公式架構。

最後感謝幹部帶來如此豐富的課程內容，讓社員了解更多 Excel 的快捷鍵、函數式以及資料分析的方法論，相信這些技能將協助社員在接下來的企業合作專案中，更有效率的處理資料！

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