Google 試算表 13 個效率翻倍的表格整理工作技巧



最近因為企業內訓的需求,上了幾堂「 Google 試算表效率翻倍工作術」的課程,主要是帶大家練習在 Google 試算表中可以利用哪些功能,建立一套更高效率的表格整理、工作管理流程。

其實, Google 試算表大多數的關鍵功能,和 Excel 並沒有太大不同,像是樞紐分析、巨集、大多數函式,熟悉 Excel 的朋友也能在 Google 試算表中使用,只是操作方式或功能名稱稍有不同。

但是雲端協作的特性,也可以增加串聯不同資料時的方便性。

既然已經為企業課程研究了常用的 Google 試算表功能與應用,就順便把我在電腦玩物寫過的相關教學,整理成一篇簡單目錄,也分享給各位讀者。





一,避免協作出錯,善用「保護儲存格」


Google 試算表可以很方便的多人協作,一起編輯同一份報表,雖然可以設定編輯、註解等權限,然而一旦開放編輯功能,也會擔心有人在填寫表格時容易出錯。

這時候,可以利用「保護儲存格」、「保護工作表」的功能,在分享試算表時,設定好填寫位置的保護。


例如下面這樣的流程:

  • 先把整頁工作表保護起來,只開放其中可被編輯的資料填寫欄位。
    • 這樣可以讓多人編輯時,不會改到像是標題、結算公式等欄位。
  • 在可編輯儲存格中,還可以進一步設定某幾格只允許某幾位合作者編輯。
    • 這樣可以避免 A 夥伴改到 B 夥伴負責的欄位資料。





二,讓填寫格式更正確,活用「資料驗證」


無論是多人協作一份數據報表、任務進度表,或是個人反覆使用的訂購單範本,我們都希望在填寫時「有固定的格式」,這樣資料才會統一、不容易出錯。例如固定的日期格式、固定的選項、固定的數據格式等等。

這時候,可以利用「資料驗證」的功能,為特定的儲存格設定專屬填寫格式,可以是下拉選單、日期選單,或是輸入錯誤資料時的提醒。


例如解決下面這樣的需求:

  • 要填寫郵件時,檢查是否為正確郵件格式。
  • 要填寫數字時,檢查是否為正確單純的阿拉伯數字。
  • 要填寫品項時,直接拉出固定的品項選單,以免亂填。





三,雜亂數據資料,用「資料透視表」快速統計分析


有時候,試算表上收集了一堆數據項目,但次序是凌亂的,例如根據時間新增了數百筆資料,但許多同品項、同類型數據,分散在不同列,要做統計分析圖表時變得很困難。

這時候,在 Excel 中可以利用「樞紐分析」,在 Google 試算表中叫做「資料透視表」,可以幫助我們一鍵重整資料。


例如解決下面這樣的需求:

  • 有一份帳目表,裡面有數百筆帳目資料,資料欄位有關於 A、 B、 C 品項,有甲、乙、丙通路,有每一筆的銷售數量、銷售金額。
  • 但是這些資料錯雜在一起,沒有次序。
  • 利用樞紐分析(資料透視表),可以快速結算出 A 品項在甲通路、乙通路、丙通路各自的總銷售金額、B 品項在甲通路、乙通路、丙通路各自的總銷售金額、C 品項在甲通路、乙通路、丙通路各自的總銷售金額。
  • 於是要製作出有效圖表也變得更簡單。





四,重複性的整理動作,利用「巨集」自動完成


有時候一張試算表中,有好幾頁類似的工作表需要整理,每頁工作表目前的資料都還很雜亂,所以我想要做一些格式的調整,並且為每頁工作表做出樞紐分析後的統計圖。

如果每頁工作表都要手動整理一遍又製作樞紐分析統計圖,也是要花上不少時間。

這時候,就可以利用「巨集」,把第一頁工作表的操作「錄製」下來,接下來第二、第三、第 N 頁的工作表直接「播放」,就可以自動重複第一頁的各種整理步驟,快速完成其他頁面的整理。






五,匯率函式,自動轉換貨幣金額,追蹤走勢圖


如果要整理的帳目資料跟外幣有關,需要同時計算轉換成新台幣或其他外幣的金額。

這時候,可以利用 Google 試算表中的「 Google 匯率」( GOOGLEFINANCE Currency)函式,自動計算當下換算的新台幣、外幣價格。

還可以設定一個走勢圖,追蹤指定時間內的貨幣匯率趨勢。






六,翻譯函式,自動翻譯詞彙成多國語言


結合 Google 翻譯的功能, Google 試算表中還有一個翻譯公式(=googletranslate),可以自動把指定儲存格內的資料,快速翻譯成需要的語言。






七,自動彙整多份試算表的函式,讓統整報告更簡單


有時候手上有多份銷售報表檔案,或是不同專案的統計數據分散在不同的工作表、不同的範圍。

我需要為自己製作一份總表,可以追蹤所有銷售報告、所有專案數據。

但是,不要用手動複製貼上的方式,因為那樣總表的資料沒辦法跟著其他試算表同步更新。

這時候,就可以利用「匯入其他試算表資料」的函式(「=importrange」),把其他檔案的資料,自動匯入到總表,並且當其他檔案資料更新時,總表也可以自動更新。






八,快速將文字分欄、轉換欄列


如果說剪貼一份資料到表格中,結果一個儲存格中有太多筆資料,想要快速分成多欄。

或是一開始的欄列設定想要轉換,把欄換成列。

這時候, Google 試算表也內建了直接分欄,或是欄列轉換的功能。






九,利用 Google 表單,快速、精準收集帳目報表


若是需要收集大量的資料,最後整理成一份統計報表,那麼除了直接在 Google 試算表上填寫,不如先設計一份「 Google 表單」。

透過製作一份可以快速輸入與選擇的線上問卷,但做為回報資料的用途,這樣一來也能精準地收集需要的資料報表。



我之前曾經利用這樣的方式,來收集家庭收支表,或是多人旅行時的記帳功能,非常好用。






十,安裝合併列印郵件外掛,寄送客製化郵件


有些關鍵的功能,在 Google 試算表中缺乏,可以透過安裝外掛的方式實現,例如在 Excel 中常用的合併列印郵件功能。

當擁有一份客戶名單的 Google 試算表,安裝「 Yet Another mail merge 」這個外掛程式,就可以直接透過 Gmail ,寄送包含客戶資料的客製化郵件。





十一,修改網址,分享試算表時可以直接複製或下載


當我們自己慣用 Google 試算表時,除了直接分享網址給合作者之外,如果對方想要快速複製試算表,或是把 Google 試算表下載成 Excel 檔案,有時候對方並不知道如何操作。

這時候,只要在試算表網址後面,修改一下後贅,就可以讓對方點開網址直接複製副本,或是點開網址直接下載 Excel 檔案。





十二,把試算表內容張貼到網頁中,並可即時更新


我自己在製作軟體工具比較表時(例如:Webex, Teams, Meet, Jitsi 8款視訊會議軟體需求測試比較表格),用 Google 試算表製作,完成後可以獲得嵌入到網頁中的程式碼,這樣就能分享到網站。

而且在 Google 試算表更新時,網頁中的表格資料也會同步更新。

  • 圖文教學:超簡單網頁表格製作!免語法用 Google 試算表教學






十三,Google 試算表的範本


如果想要製作複雜的統計報表,或是像利用試算表製作甘特圖,這時候或許從範本開始入手,然後進行修改,也是一個快速的好方法。

現在許多提供 Excel 範本的網站,也會提供 Google 試算表的範本可以套用。





結論:


以上就是我自己會用到的幾種 Google 試算表整理功能、工作技巧,整理給大家參考。

我的電子郵件是 esorhjy@gmail.com ,如果你有任何關於相關工具、時間管理、提升工作效率的問題,歡迎寫信跟我討論。

(歡迎社群分享。但全文轉載請來信詢問,禁止修改上述內文,禁止商業使用,並且必須註明來自電腦玩物原創作者 esor huang 異塵行者,及附上原文連結:Google 試算表 13 個效率翻倍的表格整理工作技巧

留言

  1. 非常感謝~ 又學到了一些東西

    回覆刪除
  2. 你好 請問google sheet 有沒有類似excel多重彙總資料範圍的功能呢?我把檔案轉存後樞紐就不能使用了

    回覆刪除
  3. 你好,想請教你googlesheet 裡面有沒有類似excel多重彙總資料範圍的功能呢,我把檔案轉存後googlesheet的樞紐就不能使用了,當初有用多重彙總資料範圍,合併多個資料表(都在同一個檔案內)

    回覆刪除
  4. 請問有訂購名單+計算金額一起的範本嗎

    回覆刪除

張貼留言

為了避免垃圾廣告留言過多,開始測試「留言管理」機制,讓我可以更容易回應讀者留言,並更簡單過濾掉廣告,但只要不是廣告留言都會通過審核。

這個網誌中的熱門文章

Google 文件推出「分頁」,高效整理專案文件、論文研究資料庫

全家沖印、 7-11 列印照片誰好用?立可得與 ibon教學+比較表

實測用《 ChatGPT x Excel 自動化工作聖經》技巧,設計自動規劃電動車里程電量的 Google 試算表

Google NotebookLM 免費中文 AI 筆記實例教學,老師、學生、創作者利器

Google Maps 我的地圖完全教學!規劃自助旅行攻略

Slack 完整教學與上手心得:找到正確的團隊溝通之道

為什麼大家愛用 Trello ?最佳整理教學讓事情井然有序

畫張圖說得更清楚! Napkin 用 AI 幫你的簡報文章手繪視覺思考圖

ChatGPT Canvas 全新 AI 寫作、程式碼編輯工具,不再只能對話!

Google NotebookLM 影音升級:用 AI 學習英文 YouTube 影片、 整理大量錄音檔最佳工具