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

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

為什麼要用 Google Sheet？

1. Google Sheet 免費：比起 Excel 的價格，Google Sheet 在幾乎跟 Excel 永遠相同功能的前提下，他是免費的
2. 無限外掛超好用：Google Sheet 上有非常多開發者，在開發免費或是付費工具，像是連網公式、爬蟲等等

1. Google 問券結果自動導入 Google Sheet 做數據分析

• Google Form 免費：Google Form (Google 問券) 是蒐集問券最好用、又免費的一個方式
• Google Form 數據分析：填寫者的在回應 (Responses) 會用簡單的圖表呈現所有回應的統計，像是圓餅圖、直條圖等等。
• Google Form 串接 Google Sheet，方便數據分析：Google Form 的結果，會自動串到 Google Sheet 中，除了會記錄所有結果外，還會記錄填寫問券的時間戳記，因此後續分析資料的時候，可以很方便地整理跟取得資料

2. 3 大獨特的實用公式：countunique/filter/query

• 以下介紹的三個公式是 excel 沒有，只有 Google Sheet 有，但是又特別好用的公式！
• 使用情境一：快速計算整欄的不重複值：Countunique()：計算整列的不重複值有幾個
• 使用情境二：篩選一些條件後的資料做統計、我想要像寫 SQL 一樣輕鬆的用一行公式清理資料，那一定需要這三個公式！我唯愛的三個，分享給大家～

• Filter (Filter 詳細教學與常見應用場景)：依據所有想要篩選的條件，把一行或是多行資料回傳
• Query (Query 詳細教學與常見應用場景)：Query 算是 SQL 的入門，他可以選取所有你想要的欄位 (Select)、做篩選 (Where)、然後還可以按照你想要的方式排序 (Order by)

3. 無限的外掛可以下載使用，踩在巨人肩膀往上爬

最簡單也是最容易用到的外掛工具有兩種：一種是自創公式，一種是自創小工具。以下分享幾個我覺得很實用的自創公式跟自創小工具！

• 自創公式
  • PICSEE_UTM()：產生自定義短連結 Picsee 可以自行輸入 utm 後產生短連結，是一個系統性的埋碼跟追蹤工具 -> 部落客/行銷人必用
    • PICSEE_CLICK()：即時的查詢各個短網址所帶來的點擊成效
  • NLSOLVE()：方程式求最佳解
    • Google Sheet NLSOLVE 計算機：這邊介紹如果我們想要解多項式，算出不同優惠券門檻下，我的折扣率要設在多少以下才可以確保我賺的利潤是一樣的。你可以在範例檔案中輸入平常無折扣訂單的客單價、固定成本 %來算出利潤，再輸入門檻，NLSOLVE() 計算機會幫你自動算出折扣深度最多不可以超過幾折，讓你不賠錢
  • CRYPTOFINANCE()：虛擬貨幣追蹤
    • 外掛程式 -> 取得外掛程式 -> CRYPTOFINANCE()
• 自創小工具
  • 批次寄送 email
    • 推薦使用 👍 每天都在推陳出新的外掛，最新的個人 Gmail 一天可以發 500 封個人化設定的 Email：外掛程式 -> 取得外掛程式 -> Mergom mail merge，可以客製化訊息、可以設計電子報 (Google Doc 設計)、可以觀察開啟率
    • 一天可以發送 100 封，可以使用 Create a mail merge using Gmail and Google Sheets，或是你也可以 點此複製一份模板 來使用
    • YAMM：YAMM 外掛免費版一天只能發 50 封，但是也是滿容易使用的，還可以知道對方有沒有打開信，確認開啟率
  • 甘特圖工具
    • 推薦使用 👍 外掛程式 -> 取得外掛程式 -> Gantt Chart，這裡有 教學影片
    • 外掛程式 -> 取得外掛程式 -> ProjectSheet Planning，跟上面的甘特圖差不多，我個人覺得上面的 Gantt Chart 介面比較簡單好懂！
  • Google Analytics
    • GA 也有官方外掛可以跟 Google Sheet 串接，方便整理流量的資料 外掛程式 -> 取得外掛程式 -> Google Analytics
  • Instagram 數據分析工具
    • 工具說明：他可以幫我搜索 instagram 上的搜索結果，例如說我想知道 Top 100 「美食」的搜索結果，他會幫我把帳號、追蹤人數等等都放到 Google Sheet 上，讓我方便的去查看搜索結果

4. 共同編輯，免費又實用的協作工具

共同編輯絕對是上班族在做協作的時候，最需要的工具之一。講最簡單的例子，訂飲料！如果今天沒有要開啟 Ubereats 團購功能/Foodpanda 團購功能，那 Google Sheet 絕對是最好用的訂飲料工具。

• 上班族最愛的飲料表單：假設今天是要請客，要請同事大家都填自己想要的飲料，再跟飲料店叫外送：線上飲料表單製作 (Image+Query)，裡面也有範例檔案可以直接使用喔！
• 共編時的權限控管：除此之外，Google Sheet 的共同編輯功能，或是想要管控對方只能查看工作表或是留言的權限
• 鎖定特定重要欄位，只允許某些人編輯 (gif)

5. 引用別張 Google Sheet 的資料

在日常工作應該常常會碰到，我這邊的資料 Google Sheet A 想要跟別的地方的資料 Google Sheet B 作比對，最簡單的方式當然是把 B 的資料複製、貼上到 A，但最棒的做法是：

• 用 IMPORTRANGE() 把 B 的工作表自動連動到 A 裡面，這樣即使是 B 移動了，A 也會跟著連動
• 如果想要在自己的試算表裡面，在不同分頁裡面互相複製也可以用 ARRAYFORMULA() 做到連動

6. 連網公式超好用 (股市/網上圖片/api)

• GOOGLEFINANCE()：即時股市價格資料
• IMAGE()：網上圖片抓取，Google Sheet 呈現
• IMPORTHTML()、IMPORTDATA()、IMPORTXML()：將網頁上的資料載到 Google Sheet 內，雖然還是有一些限制，不過可以試試看你想要的網站資料能不能夠用 Google Sheet 公式自動匯入

7. Google Sheet 的探索功能：自動做圖

當你把資料都整理好之後，想要做圖，可是不知道要用圓餅圖/直方圖/等等呈現時，可以點選右下角的探索功能，Google Sheet 會建議你他覺得最適合你資料型態的圖！

8. 個人理財好方便

目前沒有找到比較好用的外掛，因此我自己做了一個，可以分析我的投資標的 vs 被動式投資 ETF 的差距：免費獲得財務模型，我個人平時懶人記帳會使用麻布記帳，因為麻布記帳可以幫我把所有銀行帳戶/信用卡/台灣證券帳戶都串接在一起，因此我覺得很方便了，但在投資這一塊，我稍微有再多要求自己要每年追蹤自己跟被動投資的成效比較，因此我覺得 Google Sheet 為我提供很多方便！

9. 區塊鏈工作表：版本回復/編輯紀錄查看

在共同編輯的環境下，我們最怕的就是有人不小心刪掉重要的資訊，跟找出誰是兇手 (抓戰犯！)，好讓我們可以善意的提醒他，Google Sheet 能夠做到這兩點：

• 版本回復：如果有人不小心把重要的資訊刪除，可以找到他刪除前的版本，然後回復
• 編輯紀錄查看：每一格的編輯紀錄都可以查看，因此是一個很安全的線上共編軟體，至少所有的更改都有被記錄 (就像區塊鏈一樣，每一個行為都不可逆/不可更改)

10. 免費的 Google Drive/Google Cloud

最後回到性價比，Google Sheet 基本上就是完勝 Excel 或是其他視覺化的軟體，因為 Google Sheet 的免費功能不只可以儲存超多資料 (單個 Google Sheet 50 萬格)，整個 Google Drive 也有超大容量 15G，而除了 Google Sheet 以外，Google Cloud 還提供免費的 Data Studio (像 PowerBI、Tableau 的相似 BI 工具)，還有 BigQuery (Online DataBase for SQL) 可以使用。

Google 官方開的 Google Sheet 免費課程

現在 Google 官方有在出免費的課程 (和 Coursera 合作)，我個人覺得很適合幾種讀者：

• 完全 Google Sheet 初學者
• 會一點 Excel (樞紐、vlookup、sumif)，但沒有太常使用 Google Sheet
• 工作需要，非常需要快速速成學會
• 需要利用 Google Sheet 做一些基本報表和基本數據整理和分析

不適合

• 已經接觸 Google Sheet 半年以上
• 或是對於我寫的一些進階公式：Query、ArrayFormula 有了解

這堂課你會學到哪些 Google Sheet 技巧？

1. Google Sheet 介面和基本功能 (建立副本、捷徑、新增欄位)
2. 學習如何利用 Google Sheet 做基本的資料分析，這堂課會帶到基本公式 (算數統計函數、篩選)
3. 資料處理，以便觀察出 insight (資料格式設定、標出趨勢、條件格式設定)
4. 資料視覺化 (Pivot、圖表、GoogleFinance)
5. 如何和團隊協作 Google Sheet (權限控管、Error 訊息排除、Google Form+Google Sheet)

我個人覺得 Google 官方課程言簡意賅，這些學完之後，就可以立刻開始發掘一些進階數據分析公式、外掛！我會把我的進階教學放在文章最下方，如果有興趣歡迎一起成為 Google Sheet Master～

點我加入 Google Sheet 免費課程 (Google 官方)

總結

Google Sheet 對於整理資料、自動化工作流程真的很有幫助，我之前在 honestbee 的時候，有用過 Google sheet 自動排班 (填寫 Google form 之後，可以利用 Google sheet 做排班)、也有用過 Google sheet 結算 1000 多個司機的薪水！

所以誰最適合學習 Google sheet？

• 所有上班族 (真的含淚必學，很多節省時間的運作模式建起來之後，可以讓你早點下班！)
• 公司沒有系統，要做很多人工的營運處理
• 你們是很早期的新創團隊、或是自己經營自媒體的厲害創業家們

以上這三種讀者們！真心推薦你們變成 Google Sheet 試算表的信徒～一起來幫助我們節省時間、早點下班吧！

學習資源推薦

1. Google 數據分析證照班：Google Data Analytics Professional Certificate 點我查看誰適合參加？
2. <延伸閱讀：所有與資料分析相關的教學捷徑>
   • 學好數據分析的基礎 9 堂課，但在這之前，先來看看數據分析師的一天吧！
3. <延伸閱讀：Google Sheet 進階公式教學>
   • Google Sheet QUERY 用法
   • Google Sheet FILTER 用法
   • Google Sheet ARRAYFORMULA 用法
   • Google Sheet FLATTEN 教學
4. Google Sheet 範本 (全都免費下載)
   • 線上飲料訂單製作
   • CRM 三大模型 (RFM、NES、Cohort)
   • 股票投報率分析