一條函數公式讓多份 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秒輕鬆生成圖片

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

3個 ChatGPT 輸入到輸出實驗,第二大腦筆記用 AI 生成文章報告

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

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

如何為筆記命名,建立搜尋規則?我的標題、分類、標籤命名法

Draw.io 中文線上製作流程圖首選!專業水準完全免費

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

Google 表單自動關閉回覆教學:時間人數到達就過期