[數據分析#50] 數據分析師 Excel 考試一定要看的：45 個業界用法 (含免費練習考題)

數據分析網站第 50 篇文章！一定要來點不一樣的！

最近在跟學員聊數據分析工具的時候，有些人跟我說，他一開始就先學 Python、R，但是不會 Excel、或是試算表 (Google Sheet)，這樣會有機會找到工作嗎？

其實，Excel、或是試算表 (Google Sheet) 是最基本的數據處理工具，他有幾個一定要學的原因：好傳輸、好修改、對資料架構有一定的理解才會使用

接下來這一篇，我們就談談如果要做數據分析，一定要會的 30 個場景、45 個業界函數及應用，分享使用場景跟他們的用法！

為什麼數據分析師要學到 EXCEL 和試算表 (gOOGLE sHEET)？

1. 在公司之間好傳輸

• 如果要將資料、統計結果發給公司其他同事、客戶、或是老闆，EXCEL 和試算表是唯一通用的傳輸格式，因此才會說是必學、必會、最基本

2. 容易修改

• 如果在處理、分析資料的時候，想隨時更改資料清理的方式、分析的方式、圖表呈現的方式，EXCEL 和試算表是最容易修改的格式
• 不需要重新請其他人幫忙編輯，自己就可以簡易的修改裡面的參數

3. 理解資料架構的最好方法

• 如果要瞭解資料的架構，不需要一定去學 SQL，或是其他語言 (Python)
• EXCEL 和試算表就是最好的方式，去理解資料怎麼被記錄在資料庫中、怎麼用數據分析師的思維處理資料
• 學好 EXCEL 和試算表 的資料結構，絕對對之後進階到其他語言，會有超大的幫助！

10 大基礎公式：用途跟用法是什麼？

樞紐分析基礎

• 樞紐分析要做什麼？
  • 樞紐分析的目標是要：做到兩個維度以上的資料統計
• 樞紐分析要幹嘛？
  • 如果你手上有你每個月每個項目的支出
  • 你就可以用樞紐分析看出每個月每個項目的支出變化

IF 及巢狀公式

• IF 要做什麼？
  • IF 的目標是要：針對你給出的條件，符合 (是 TRUE) 就回傳一個答案；不符合 (不是 FALSE) 就回傳另外一個答案；這在數據分析中，也稱為分群
  • IF 除了查詢一個條件以外，也可以做多條件的查詢，我們稱為巢狀 IFS
• IF 要幹嘛？
  • 如果你手上有學生的成績
  • 小於 60 分的，可以回傳不及格；大於 60 分的，可以回傳及格

交集聯集公式 AND/OR

• AND 要做什麼？AND 的目的是聯集，如果同時符合兩個條件，就可以用 AND 連接
• OR 要做什麼？OR 的目的是交集，如果符合其中一個條件就可以，就可以用 OR 連接
• AND 跟 OR 的使用場景？
  • AND 跟 OR 可以跟 IF 搭配一起使用

下拉式選單 (資料驗證)

• 下拉式選單要做什麼？
  • 下拉式選單，是作者先將選單建好，如果使用者或是其他人輸入錯誤會提醒；在數據分析中，算是資料清洗的一種

篩選器基礎、FILTER 函數

• 內建篩選器要做什麼？
  • 內建篩選器可以依據公式篩選符合條件的值
• FILTER 函數要做什麼？
  • FILTER 函數可以你輸入的條件，回傳所有符合條件的欄位
  • FILTER 屬於陣列函數

基本統計函數：SUM/AVERAGE/COUNT

• 基本統計函數要做什麼？
  • 可以計算一整串資料的：加總、平均、計數，做基本的數據分析

條件統計函數：SUMIFS/COUNTIFS/AVERAGEIFS

• 條件統計函數要做什麼？
  • 條件統計函數不只要做運算，還要做「有條件的運算」，有條件的加總、平均、計數

無條件計數：COUNTA

• 無條件計數要做什麼？
  • 無條件計數可以統計有值的儲存格格數，包括錯誤值跟空白文字

統計函數：MIN/MAX/MEDIAN

• 其他統計函數要做什麼？
  • 其他統計函數可以計算一整串欄位中的：最小、最大、或是中位數；算是數據分析的一部分，更了解整串數字的輪廓

查找函數：VLOOKUP

• 查找函數要做什麼？
  • 如果我有目標，想要在一個表裡面搜尋，就要用查找函數來實現
  • 除了查找符合單一條件的值外，也可以「多條件查找」，只要把查找函數搭配 ARRAYFORMULA 使用就可以做到
  • 而且往右或是往左查找都可以

10 大資料處理基礎篇：用途跟用法是什麼？

做圖：圓餅圖/直方圖/折線圖/雙X軸做圖/SPARKLINE

• 為什麼要做圖？
  • 做圖是為了將數據分析的結果，用簡單的視覺化方式，讓使用者可以一目了然
  • 你除了可以使用內建的做圖功能外，也可以做出組合式圖表，也稱為儀錶板 (Dashboard)，還可以使用 SPARKLINE 做出儲存格內的圖表；提升一般表格的檔次

資料匯入：IMPORTRANGE

• 為什麼要把資料匯入？
  • 資料匯入可以整合很多個資料來源，把 A 表跟 B 表的資料整合在一起，之後再數據分析
  • IMPORTRANGE 可以將其他試算表的資料匯進同一個試算表

查找公式：INDEX/MATCH

• INDEX + MATCH 查找函數可以幹嘛？
  • 除了與 VLOOKUP 擇一使用外
  • INDEX+MATCH 在製作報表時，方便直接右拉及下拉公式；就不需要像 VLOOKUP 一樣，還要寫查找欄位是第幾行 (像：1, 2, 3 這樣寫)

日期處理：EOMONTH

• 如何以 EOMONTH 做日期處理？3 大場景分享：
  • 使用場景一：EOMONTH 可以計算該月的最後一天，例如：將 2022/1/4 轉換成 2022/1/31
  • 使用場景二：EOMONTH 還可以計算該月前幾個月的最後一天，例如：將 2022/1/4 轉換成 2022/12/31
  • 使用場景三：EOMONTH 還可以計算該月第一天，例如：將 2022/1/4 轉換成 2022/12/31，接著 +1，就變成 2022/1/1
• 為什麼要計算日期當月的第一天？
  • 因為資料分析常常會以一個月的加總來看，那這時候，如果你的資料維度如果是每日，那你就可以用 EOMONTH 將所有日期轉成當月第一天，就變以月為維度啦！

日期處理：TODAY/WEEKDAY/YEAR

• 其他實用的日期處理函數：
  • TODAY：可以輸出今天是幾號的函數
  • WEEKDAY：可以運算這個日期是禮拜幾的函數
  • YEAR：可以運算這個日期是幾年的函數 (西元年分)
• 為什麼要用這些日期函數？
  • 在做數據分析時，可能以各種不同維度 (以年；以月；以禮拜幾為維度) 來統計表現，這個時候，有日期函數，就可以幫我們快速統整出這些維度 (以年；以月；以禮拜幾為維度)

網路圖片插入：IMAGE

• 如果數據分析的來源，參考其他人的運算，可以以 IMAGE 插入網路上的圖片
• 或是任何需要以圖片輔助講解的，都可以用 IMAGE

文字處理：SPLIT/LEFT/RIGHT/CONCATENATE

• 為什麼要做文字處理？
  • 文字處理是數據分析中的資料清洗
• Excel 跟 Google Sheet 有哪些資料清洗的用法？
  • 第一個，SPLIT：可以將文字依照條件分割，例如 Lisa Wu，以空格來 SPLIT，就會變成 Lisa 跟 Wu 兩行
  • 第二個，LEFT：可以回傳從左邊數來第 N 個字元，例如 Lisa Wu，LEFT 跟 4，就會輸出 Lisa
  • 第三個，RIGHT：可以回傳從右邊數來第 N 個字元，例如 Lisa Wu，RIGHT 跟 2，就會輸出 Wu
  • 第四個，CONCATENATE：將兩個字串併在一起，例如 Lisa 跟 Wu 兩個字，可以用 CONCATENATE 串成 Lisa Wu

統計公式：UNIQUE/COUNTUNIQUE

• 為什麼要做 UNIQUE？
  • 資料分析最怕重複值，UNIQUE 可以幫我們取出一整列數列的不重複值
• 為什麼要 COUNTUNIQUE？
  • COUNTUNIQUE 可以幫我們計算，有幾個不重複的值
  • COUNTUNIQUE = UNIQUE 後，COUNT 個數

排序公式：RANK、SORT

• 為什麼要做 RANK？
  • RANK 可以依照這個值在這個數列中的排序，回傳他是第幾名
  • RANK 可以幫我們快速算出第一名是誰
• 為什麼要做 SORT？
  • SORT 可以依照數字大小做排序，讓所有的數列都自動依照大小排列

錯誤顯示公式：IFERROR

• 為什麼要用 IFERROR？IFERROR 要幹嘛？
  • 數據分析最怕缺失值、錯誤值，所以我們都需要做數據清理這個步驟
  • IFERROR 會自動辨別錯誤，如果錯誤，會回傳一個值，例如空白或是 0，錯誤的回傳值，我們可以自己設定

10 大資料處理進階篇：用途跟用法是什麼？

計算相關性：CORREL

• 為什麼要用 CORREL？
  • CORREL 可以計算兩個資料集之間的相關性。是統計函數中的相關係數 R
• 什麼是相關係數 R？會有什麼結果？
  • 以統計上的相關性，計算兩行數字 (X, Y) 的相關性，如果結果為 1 就是正相關，-1 就是負相關
  • 0 ~ +1 之間，就是落在正相關的區間
  • <0 ~ -1 之間，就是落在負相關的區間
  • 結論：正相關簡單來說，就是 X 越大，Y 越大；負相關簡單來說，就是 X 越大，Y 越小

統計公式：PERCENTILE

• 為什麼要用 PERCENTILE？
  • PERCENTILE 可以用來統計四分位數，也就是一個數列裡 25%、50%、75% 位置是哪些數字？
  • PERCENTILE 也可以看任何分位數，可以自行設定，例如：你想要看 10%、30% 也可以
  • 結論：PERCENTILE 可以用來看整串數據的分佈，不像平均一樣，只能看到一個平均的狀況

進階 FILTER + 統計函數 (PERCENTILE……)

• FILTER + 統計函數 可以幹嘛？
  • FILTER 可以依照條件篩選出你要的資料集，後面再加統計函數，就等於有條件的統計
  • 例如：FILTER + PERCENTILE，可以將特定資料集取出後，再看四分位數

陣列公式：SUMPRODUCT

• SUMPRODUCT 可以幹嘛？
  • SUMPRODUCT 可以計算兩列相乘，快速計算總和
  • 例如：單價 X 數量 = 可以快速計算出銷售總和

陣列公式：ARRAYFORMULA

• 陣列公式 ARRAYFORMULA 可以幹嘛？
  • 陣列公式 ARRAYFORMULA 可以搭配其他公式使用，一鍵完成整列更新，不需要下拉公式
  • 剛剛提到的 FILTER 也是屬於陣列公式的一種

SQL 公式：QUERY

• 為什麼數據分析一定要學 QUERY 公式？
  • 以數據分析最喜歡用的 SQL 查詢語法，設計出的 QUERY 公式
  • 一鍵 (一格公式) 處理資料、做出進階報表

反樞紐：FLATTEN

• 為什麼要做反樞紐？
  • 樞紐過後的資料形式，很難再做其他分析；因此數據分析師拿到樞紐後的資料格式，都會重新將他正規化 (正規化也是數據清洗的一種)
  • 反樞紐 = 將樞紐形式的資料，轉變回原來的格式

抽獎函數：RANDBETWEEN

• 為什麼要學 RANDBETWEEN？
  • RANDBETWEEN 可以回傳兩個值之間的隨機數，可以做為抽獎的函數
  • 不需要用其他抽獎軟體，用 Excel 或是 GOOGLE SHEET 就可以做到抽獎的應用

總結

• 數據分析師最重要的技能之一：就是 EXCEL、GOOGLE SHEET；也是最基本、最泛用的
• 如果你要面試相關職位、或是要考 EXCEL、GOOGLE SHEET，可以直接拿出這一份來幫助你回復對於 EXCEL、GOOGLE SHEET 的記憶

如何獲得免費檔案練習？

• 填下底下的資訊，我就會將攻略說明寄給你！
• 裡面除了包含講解、示範以外，也有練習題檔案跟答案