[數據分析#37] Google Sheet 試算表 20 個必學功能 (公式教學/外掛及工具分享)

近期，很多企業 (尤其是新創或是跨國企業) 都開始購買 Google Cloud 的服務，讓員工可以在 Google 空間裡協作：傳送 Email、共同編輯文檔或是資料等等，一起來學習 Google 工具的實用功能吧！

而 Google Sheet 比起 Excel，除了免費之外，還有太多只有他才有的功能，接下來會分享 20 個對於上班族、企業、或是個人來說最實用的必學功能！

為什麼要用 Google Sheet？

1. Google Sheet 免費：比起 Excel 的價格，Google Sheet 在幾乎跟 Excel 永遠相同功能的前提下，他是免費的
2. 連網公式跟外掛都超好用：Google Sheet 上有非常多開發者，在開發免費或是付費工具，像是連網公式、爬蟲、其他實用的外掛工具等等
3. 共同編輯：不像 Excel 需要互傳檔案，Google Sheet 只要傳網誌、開啟權限給對方，就可以開始共同編輯
4. 工作表之間可以互相引用：如果有兩份 Google Sheet 工作表，可以用公式連接兩者之間的資料，不用再「複製貼上」了

Google Sheet 公式教學

1. Google Sheet 工作表之間互相引用資料：IMPORTRANGE()、ARRAYFORMULA()

在日常工作應該常常會碰到，A 工作表想要跟 B 工作表互相比對，最簡單的方式當然是把 B 的資料複製，然後貼上到 A，但 Google Sheet 中有公式可以支援，不用再「複製貼上」了！

1. 方法一：IMPORTRANGE() 把 B 的工作表自動連動到 A 裡面，這樣即使是 B 移動了，A 也會跟著連動
2. 推薦方法二：如果想要在自己的試算表裡面，在不同分頁裡面互相複製也可以用 ARRAYFORMULA() 陣列公式連動，ARRAYFORMULA() 還有很多其他進階的實用使用情境，詳細教學可以看：Google Sheet Arrayformula 5 大超實用使用情境教學

2. 除了內建篩選功能，還有篩選公式：FILTER()

如果你不想看到所有的資料，可以使用篩選功能來設定你要的條件

1. 方法一：內建篩選功能，直接篩選原始資料，限制是每個欄位一次只能篩選一組條件
2. (推薦) 方法二：使用篩選公式 FILTER()：如果用篩選公式，可以彈性的變換條件，也不會動到原始的數據，詳細教學可以看：Google Sheet FILTER() 使用方法
   • FILTER() 還能跟其他函數，像是查找函數：VLOOKUP()、統計函數：PERCENTILE()、MEDIAN() 等一起組合，讓數據分析變得更多元且方便，詳細教學可以看：Google Sheet FILTER() 使用方法

3. 財經公式，用來自動更新股票報價：GOOGLEFINANCE()

• 如果你有投資台股或是美股，想要自動獲得台股或是美股報價，可以用 GOOGLEFINANCE() 取得每一天的報價外，除此之外，還可以查到
  • 每天的價格變化：收盤價、最高價、最低價
  • 52 週期間的最高價、52 週期間的最低價
  • 交易量、在外流通的股數
  • 如果你查的是共同基金，還有很多財務相關資訊，例如：最近的現金配息金額、最近的現金配息日期等等
• GOOGLEFINANCE() 也可以用來追蹤匯率
• GOOGLEFINANCE() 可以用來製作個人投資報酬率計算表，詳細可以參考我製作的範例：股票投報率計算機免費下載 (再教你算投資組合有沒有贏過被動投資)

4. 除了內建排序功能，還有排序公式：QUERY()

如果你想對資料做排序，例如說由大到小

1. 方法一：內建排序功能：直接排序原始資料，會「直接」改變原始資料
2. (推薦) 方法二：使用 QUERY() 公式：可以用 Google Sheet 才有的 QUERY() 公式排序資料，不會改變原始資料的格式
   • QUERY() 公式以 ORDER BY 排序資料：ASC 就是從小到大，DESC 就是從大到小，詳細公式教學可以參考：Google Sheet Query 9 語法大全及練習檔案

• QUERY() 除了排序以外，還能以 SQL 語言的形式處理 Google Sheet 的資料，詳細教學我會在：Google Sheet Query 9 語法大全及練習檔案 這篇文章中教大家如何使用

5. 自動抓圖功能：用 Image() 抓網路上的菜單

• 用 Image() 公式，只要將圖片網址跟想要的大小設定好，就可以直接在 Google Sheet 中出現，詳細教學：Image() – 抓取網路上的圖片菜單

6. 自動翻譯多國語言：GOOGLETRANSLATE()

• 不管是單字或是句子都可以用 GOOGLETRANSLATE() 翻譯
  • 句子翻譯範例：「google sheet 超好用」翻成英文「Google Sheet is super easy to use」

Google Sheet 技巧

1. 資料驗證：製作下拉式選單、輸入錯誤時提醒

• 資料驗證的好處是，節省大家打字時間，直接讓大家用選的，即使大家打錯，也可以跳出錯誤訊息，讓輸入者可以更正
• 如何做資料驗證？可以在：資料 → 資料驗證 這裡完成下拉式表單、資料驗證
  • 下拉式選單範例：辦公室飲料表單，對於飲料來說：大小/冰/糖，是最不會變的，所以我們可以把這三欄做成下拉式表單讓大家選取，詳細可以參考：辦公室飲料表單教學跟範本

2. 版本回復、編輯記錄查看：是誰把我的資料刪掉了？

• 在共同編輯的環境下，我們很怕有人不小心刪掉重要的資訊，導致工作表異常
• Google Sheet 提供版本回復功能：直接回復到某個時間點的檔案
  • 如何做版本回復？點選編輯最後時間，進行版本回復
• Google Sheet 也提供編輯紀錄查看：每一格的編輯紀錄都可以查看，包括誰進行改動？跟改動的內容是什麼？
  • 如何查看編輯紀錄？點按右鍵 → 編輯紀錄

3. Google Sheet 自動作圖

• 方法一：除了可以將 Google Sheet 的資料，自己以 插入 → 圖表 做圖
• 方法二：Google Sheet 右下角的探索功能：Google Sheet 會建議你最適合的圖，點選之後，就自動作好圖了！

4. 文字分隔：將文字分隔成不同欄

• 方法一：如果有一長串字由文字、空格、或是符號組成，可以在：資料 → 將文字分隔成不同欄，做資料分割
• 方法二：如果想要用公式完成，可以用 SPLIT()、RIGHT()、LEFT() 這類的分割函數

5. 讓其他人直接建立副本

• 只要將 Google Sheet 中後面 edit 以後的刪掉，改成 copy，就可以變成直接建立副本的分享模式

6. 保護工作表：部分不給其他人編輯

• 如何保護工作表使用？資料 → 保護工作表和範圍
• 可以保護到什麼程度？
  1. 可以設定一個範圍 (一個工作表、或是工作表裡的某個範圍) 有哪些人可以擁有權限編輯
  2. 也可以設定所有人都沒有權限編輯
• 我通常會在，這個工作表需要大家一起共同編輯，但是規則、範例不希望其他人動到的時候，會去使用「保護工作表」的功能

不用 Google Sheet API 就能用的外掛

點擴充功能中的外掛程式，裡面有非常多第三方製作好的公式、工具可以用！以下會介紹幾個很實用、而且完全免費的隱藏外掛跟公式！

1. 自動生成短連結：PICSEE_UTM()

• 如何取得？
  • 擴充功能 → 外掛程式 → 取得外掛程式 → PICSEE
• 如何使用？
  • PICSEE_UTM()：可以自行輸入 utm 後產生短連結
  • PICSEE_CLICK()：可以即時的查詢各個短網址所帶來的點擊成效
• 如何除了產生短連結，你還需要「批次」產生短連結或是還原短連結，可以參考：Google Sheet 批次產短網址、產生與還原超連結

2. 虛擬貨幣追蹤：CRYPTOFINANCE()

• 除了用 GOOGLEFINANCE() 查詢台股、美股報價外，可以用 CRYPTOFINANCE() 追蹤虛擬貨幣的報價

3. 自動寄送 Email：YAMM、Mergom mail merge

• 最新的個人 Gmail 一天可以發 500 封個人化設定的 Email：外掛程式 -> 取得外掛程式 -> Mergom mail merge，可以客製化訊息、可以設計電子報 (Google Doc 設計)、可以觀察開啟率
• 一天可以發送 100 封，可以使用 Create a mail merge using Gmail and Google Sheets，或是你也可以 點此複製一份模板 來使用
• YAMM：YAMM 外掛免費版一天只能發 50 封，但是也是滿容易使用的，還可以知道對方有沒有打開信，確認開啟率

4. 甘特圖範例

• 如何取得？
  • 擴充功能 → 外掛程式 → 取得外掛程式 → Gantt Chart
  • 擴充功能 → 外掛程式 → 取得外掛程式 → ProjectSheet Planning
  • 這裡有 Gantt Chart：教學影片

5. 串接 GA (Google Analytics) 或是 Google Search Console

• 外掛如何取得？
  • 擴充功能 → 外掛程式 → 取得外掛程式 → Google Analytics
• 為什麼要用這個外掛？GA 不就可以分析數據了嗎？
  • 如果需要跟公司同事一起分享 GA 成效，不一定要開啟 GA 權限給他們，可以直接串接外掛程式後，用 Google 試算表製作簡單報表，分享給其他員工或是同事

6. Google Sheet 爬蟲工具：Instagram 數據分析工具

• 工具說明：他可以幫我搜索 instagram 上的搜索結果，例如說我想知道 Top 100 「美食」的搜索結果，他會幫我把帳號、追蹤人數等等都放到 Google Sheet 上，讓我方便的去查看搜索結果

7. 方程式求最佳解：NLSOLVE()

• 算出售價以及折扣之間的最佳解：之前有寫過一篇，行銷折扣分析的文章，用 Google Sheet NLSOLVE 計算機 我可以算出不同折扣門檻下，我的折扣率要設在多少以下才可以確保我賺的利潤是一樣的

總結

• 以上分享的公式、技巧跟外掛都是不需要特別學會程式，一般人就能使用的實用功能
• 這些功能，基本上在 Excel 中不太好找，或是找不到，因此 Google Sheet 真的非常適合一般人或是上班族們學習

如何練習 Google Sheet 實用公式、技巧、外掛？

1. 複製一份我的 Google Sheet 教學文件
2. 在我整理好的工作表中，試用看看這些功能

更多學習資源推薦

• Google 官方教學：Getting Started with Google Sheets
• Lisa 的超白話實用教學：Lisa 的 Google Sheet 公式教學
• Google Data Analytics Professional Certificate：Google 認證的資料分析師證照課程有幫助嗎？ 誰最適合上？可以參考我的學習心得，證照班適合想要全方位學習數據分析工具跟技巧的人！

如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用 Google Sheet 教學：本站數據分析文章導覽 及 更多 Google Sheet 實用文章教學