一條函數公式讓多份 Google 試算表帳目自動匯整到總表教學


假設我有好幾個新舊年份、不同專案的試算表,想要把每一份試算表中的收益資料,「自動統整」到一份總表,除了不用手動剪貼,更希望可以在每一份試算表資料更新時,總表也能跟著「自動同步更新」。這樣一來,我就可以節省下好幾個步驟。

我之前介紹過一個第三方工具可以解決這樣的工作流程設計:「Sheetgo 多份記帳表「自動同步彙整」到一張 Google 試算表」。不過就在該篇文章的底下留言,有朋友提到 Excel、 Google 試算表也有內建的「函數公式」可以解決同樣問題。

所以今天就讓我們透過 Google 試算表,來介紹一下這條可以「自動彙整多份試算表,並同步更新資料」的函數公式教學。對於常常要做報告總表的朋友來說,說不定可以節省不少時間。




這條函數公式是:「=importrange(“來源試算表網址”, “指定工作表與資料範圍”)」。

操作起來並不難,可以用在當我們有好幾份試算表,某些資料希望A表修改,B表也跟著自動修改時。

我們來假設一個情況:我有開車記帳生活記帳旅行記帳等多份 Google 試算表,想要把多個表格中關鍵的記帳金額,統整到一個最終的個人記帳總表。但開車、生活、旅行記帳會持續更新,也希望最終的個人記帳總表持續更新,於是我就可以直接在總表中統整開支。

這時候,可以利用上面那條函數公式做設計。完整的教學流程如下。




我自己的做法是這樣的。

我喜歡利用 Google 表單設計不同需求的「記帳 App 」,於是最後就會獲得每一種記帳需求的 Google 試算表。

這部分的教學可參考:






不過流水帳的帳目是混亂的,所以我會利用「樞紐分析」的功能,在每一份試算表建立「各種支出類型總消費」的報告表。關於樞紐分析操作,可參考:「Google 試算表樞紐分析表範例教學,像 Excel 般資料快速重整」。

接下來,我要把這兩份帳目試算表的樞紐分析報表,自動匯整到我的個人理財總表中。




方法就是利用前面提到的函數公式`:「=importrange(“來源試算表網址”, “指定工作表與資料範圍”)」

先打開我的記帳總表,在要插入生活記帳報表的欄位,輸入上述函式:

=IMPORTRANGE("生活記帳來源試算表網址","家庭記帳分析!A3:B10")

  • =IMPORTRANGE:只要輸入函式部分開頭, Google 試算表就會提供下一步應該怎麼做的提示。
  • "生活記帳來源試算表網址":直接複製來源試算表網址列上的網址,在這邊直接貼上即可。
  • "家庭記帳分析!":這邊是指定要擷取資料的「工作表」名稱,不指定的話,就是以第一個工作表為準。
  • "A3:B10":這邊就是設定要匯入 A欄3列,到B欄10列的資料。 


如果有第二份資料表要匯入,就是在要開始匯入的欄位,再次輸入上述函數公式:

=IMPORTRANGE("旅行記帳來源試算表網址","旅行記帳分析!A3:B8")




如果同時有多份試算表要匯入,就是在每個要匯入的欄位裡,輸入上述的函數公式即可。

下圖中,我就是把兩份試算表的樞紐分析報告,自動匯入整合成同一份。這樣我就可以做出自己需要的完整統計報告了。

要注意的是,自動匯入的內容,無法在匯入的地方修改,只能從來源處修改。


一條簡單的函數公式:「=importrange(“來源試算表網址”, “指定工作表與資料範圍”)」

說不定可以節省很多我們整理報表的時間,而且之後其他試算表內容更新時,這份自動匯整的總表資料,也會即時同步更新喔!

不知道還可以用來做什麼樣的利用,或是更好的技巧呢?歡迎大家討論分享。

有興趣的朋友,還可參考:[Google 試算表活用教學:你可能還不知道的10個技巧]、[Google 試算表不是只能做表格,內建到外掛10個活用案例教學]、[免費 Excel Google 試算表範本下載百種專業工作表格]等文章。


(歡迎社群分享。但全文轉載請來信詢問,禁止修改上述內文,禁止商業使用,並且必須註明來自電腦玩物原創作者 esor huang 異塵行者,及附上原文連結:一條函數公式讓多份 Google 試算表帳目自動匯整到總表教學

留言

  1. 多份帳本會不會太複雜?
    以家慈健康紀錄為例,雖有不同類型的資料,但都是同一表單,只是會依照選擇跳到不同頁面,續填後續資料。這樣的好處是資料都在同一試算表,需要時還是可以透過篩選找出某一類別的資料。

    回覆刪除
  2. 用 IMPORTRANGE 有個好處,即使把檔案權限全部開放出來,也不必擔心原始資料被改掉。
    若配合 QUERY 使用,還能只取指定的資料欄(用 select)、只取符合條件的資料列(用 where)、排序(order by)。

    回覆刪除
    回覆
    1. Google 試算表用 Importrange 匯出匯入的小技巧
      https://darrentsnote.blogspot.com/2023/02/google-importrange.html

      刪除
  3. 請問我有辦法從最終的試算表點兩下連結到來源的試算表嗎?
    或是google試算表有像excel一樣 追蹤前導參照的功能嗎?
    謝謝

    回覆刪除
  4. 大大您好,我看了您的文章受益良多,但我有一個困擾應用,不知道能否解?

    https://ithelp.ithome.com.tw/questions/10202204

    問題在這裡~~ 整合多個工作表,串接工作,讓總表一直往下長

    回覆刪除
    回覆
    1. 把 小明、小美、小華 試算表分享出來,要幫你的人會方便一點

      刪除
    2. 我也想知道這個問題的解法

      刪除
    3. 把各個 import 進來之後,再把各個{小明的範圍;小美的範圍;小王的範圍}放在一起,再用 filter 濾掉 空白的。
      重建 小明、小美、小華 試算表 太麻煩,否則我可以做出來供大家參考

      刪除
  5. 想詢問,若要串多個試算表到同一個試算表,IMPORTRANGE 的公式要怎麼帶入,我嘗試將多個試算表寫入IMPORTRANGE公式但都無法帶出,上述版主有說「如果有第二份資料表要匯入,就是在要開始匯入的欄位,再次輸入上述函數公式」,這個部分是否可以協助說明清楚的操作方式,萬分感謝

    回覆刪除
  6. 我想詢問公式輸入後出現"您沒有該試算表的存取權限",也沒有出現允許存取的地方,請問該如何存取,謝謝

    回覆刪除
    回覆
    1. 請該試算表的擁有者開放權限給你

      刪除
  7. 想請問這個函數是不是只有電腦網頁版能使用?APP版只會出現要我使用網頁版,但平板開網頁版開不起來

    回覆刪除
    回覆
    1. 以我使用多年的經驗,沒有這個問題

      刪除
  8. 想請問,如果我的來源表單每日都會更動,可是我要的匯入資料是每日都要存起來每個當日的數值,可以做得到嗎?

    回覆刪除
    回覆
    1. 可以。
      手動:每天複製工作表,然後在新的工作表全選、複製、僅貼上值
      自動:寫GAS程式,每天固定時間做一次上述動作

      刪除
  9. 想請教使用IMPORTRANGE 函數,為什麼有時候會有部份資料一直 Loading…的狀況…

    回覆刪除
    回覆
    1. 我也有遇到同樣的問題,尤其是來源試算表的資料量大且公式多又複雜的時候就會這樣。有時候按 F5 重整會出來,但大多時候是要把出現 Loading... 這一格的公式刪掉,再 Ctrl+Z 復原回來,頗為困擾,真希望能有好方法,舊的試算表弄得太複雜,要簡化也是很不容易。

      刪除

張貼留言

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

這個網誌中的熱門文章

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

微軟 Bing AI 繪圖工具 Image Creator 免費開放,30秒輕鬆生成圖片

Arc 瀏覽器 windows 版測試心得,用四大介面改造升級專注工作流

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

個人知識管理的時間平衡心法,忙碌中我如何維持高效率輸入輸出

5 條「這時候,不要做」清單打破無意識行動模式的低效率循環

Google 表單的 10 個專業問卷調查設計秘密教學

如何用 ChatGPT 加速生成一份有圖有文的簡報,實戰案例教學

ChatGPT DALL-E 3 的7個特殊 AI 繪圖應用教學,勝過 Midjourney