2012年8月8日 星期三

Google 文件試算表跨檔案取資料

這次總算靜下心來找到想找的解答了。

由於使用 Google 文件的試算表記帳,
所以遇到了一個關於跨檔案取資料的問題。

除了每月記帳的試算表外,
還有新增年份資產負債表供查核。

月記帳中常用到跨資料表(sheet)取值的功能(因為有做比例分配的關係),
這很容易就能完成;
而做年份資產負債表的時候則需用到多個試算表文件才能達成,
本以為這種跨檔案取值可能無法使用(因為先查過 Excel 的 VBA 方式),
沒想到其實也很簡單,
只要先記得被取值的試算表網址上之代號,
再套用上一個公式即可辦到。


示範
從式算表 A 的工作表 1 取出某一格的資料至式算表 B 的工作表 1,
只要在式算表 B 工作表 1 的某一格上輸入下列示範公式:
=ImportRange("AAAAAAAAAAAA","工作表1!A1")
主要使用 ImportRange() 函數進行導入。
重點在紅色的字體,
這裡要填入式算表 A 的名稱,
至於是什麼名稱就要看一下網址。

例如我的某一個式算表網址:
https://docs.google.com/spreadsheets/d/1Y1nxtPwTYh0UX6Qu9KM4sjjurqQpf0O70-78p037jrk/edit?usp=sharing

這列紅色的英數混合字串就是代表式算表 A 的名稱,
後面加填上該式算表的哪一個工作表第幾個表格
表格選擇也能使用範圍(A1:G5)性的方式選定!

33 則留言:

  1. 你的說明對我幫助很大,謝謝。

    回覆刪除
  2. 哈囉!謝謝您的分享!
    許多表格的數值無法同步更新真的讓人很頭痛
    不過這邊有兩個問題想問一下
    這邊是抓取其他工作表的數值
    不過如果當工作表新增一欄或一列的時候是不是數值就會跑掉呢?
    還有想詢問INDIRECT抓取數值的公式 使用方式是不是和這個一樣呢?謝謝

    回覆刪除
    回覆
    1. 您好,
      問題一:因為是指定某工作表的指定欄位,
      所以當來源工作表欄位有所異動則數值應該也會不正確

      問題二:我還不曾用過「INDIRECT」公式,
      但根據說明來看是寫著「傳回以字串指定的儲存格參照。」:
      https://support.google.com/docs/answer/3093377?hl=zh-Hant
      因為我原始目的是記帳及年度帳目計算,
      所以到目前還沒試過說用 INDIRECT 傳回來的「字串」是否可用來計算…

      刪除
  3. 很感謝分享,有些問題想請教,
    上述的方式是取得A檔案試算表1的A1儲存格
    有沒有辦法篩選說像這樣的範例"A檔案試算表1裡面B欄值為X的C欄儲存格"呢?

    回覆刪除
    回覆
    1. 抱歉,
      關於「B欄值為X的C欄儲存格」這一段看不是很懂的說……

      刪除
    2. 不好意思我講得怪怪的
      我有自己記帳的紀錄,假設試算表1的B欄是紀錄類別(飲食交通等等)C欄則是細項(早餐午餐等等),如果我要在試算表2抓出只要B欄是飲食的資料這樣。不曉得會不會太麻煩

      刪除
    3. 不好意思,
      因為工作環境不能使用電腦所以比較晚回應…

      初步了解只能在同一個試算表中使用「篩選器」來建立條件篩選,
      但還沒試過跨試算表要如何使用………

      刪除
    4. 沒關係,因為是目前使用上會遇到的問題到處問人XDD

      刪除
    5. 參考自 http://shanhua0131.blogspot.tw/2014/01/google-spreadsheet_19.html:
      =QUERY('工作表2'!B:C,"select * where B like '%飲食%'")

      請您試試看吧!

      刪除
    6. 您好, 感謝您的分享,受用無窮~
      想再請教,我依照您的公式,已成功取到資料,
      但若改成A1:A9,卻無法加總,不曉得跨檔案能否加總、或做其他計算呢? 謝謝

      刪除
    7. Elmy 您好:
      本人不是很了解上述 A1:A9 無法加總的意思…

      取得後的數據是可以進行加總運算,
      所以不曉得你是卡在何處?

      刪除
    8. 不好意思,沒寫清楚,感謝您的回覆~

      我希望B檔案的A1欄位是A檔案A1~A9的加總,所以填入:
      =ImportRange("AAAAAAAAAAAA","工作表1!A1:A9")
      但他卻是把A檔案A1~A9 依序填入B檔案A1~A9的各欄位,而非加總...
      所以想請教,是否可以直接讓「B檔案的A1 = A檔案A1~A9的加總」,再次感謝。

      刪除
    9. 那試試下列這行呢?
      =SUM(ImportRange("AAAAAAAAAAAA","工作表1!A1:A9"))

      刪除
    10. 成功了耶,原來如此~ 太感謝了!

      刪除
    11. 你好,這樣的做法只適用於雲端裡的資料嗎?

      刪除
    12. 這裡所指「雲端裡的資料」是什麼意思?

      一般來說,
      Import 指令應該要看試算表(Excel or Calc)是否有支援,
      如果有的話基本上都能使用才對!

      刪除
    13. 嗯.那使用ImportRange這個函數連動過去的表格,例如A表ImportRange到B表後,B表想新增插入"行"或"列"可否??用何種方式呢?謝謝哦

      刪除
    14. 假設有一個資料表從 A1:J9,
      如果你直接匯入 A1:J9 的資料,
      這可就沒辦法囉…

      因為它是佔用這個 A1:J9 的範圍,
      所以不能在此範圍中插入任何「行」或「列」!

      不過………
      如果你只有插入少數行或列的話,
      或許能用些變通方法,
      像是 ImportRange 範圍改成小範圍,
      由 A1:J9 改成 A1:F9(假設上半部)及 G1:J9(假設下半部),
      在上半部及下半部之中為你想插入的「列」。

      或許是個笨方法,
      多少參考看看吧!

      刪除
    15. 所以在importrange(雲端)下的試算表,功能並不完全跟microsoft相同??
      而且發現運用在importrange(雲端)下拉的資料並不會做累加,只是當作文字在復制?但是在microsoft下的試算表下拉是會自動累進的??謝謝您回答,對我很有幫助

      刪除
    16. 因為目前只有使用 Google Drive 的試算表,
      所以不太了解是不是與 Excel 有所差異………

      至於「ImportRange(雲端)下拉的資料並不會做累加,只是當作文字在復制?」的問題,
      老實說不是很了解這句話的意思;
      「Excel 的會自動累進」也不太了解………

      你是不是想問說 ImportRange 所匯入的「數值」,
      到底能不能進行運算呢?

      刪除
  4. 我也跟樓上的大大有一樣的問題。
    EXCEL可以下拉後複製儲存格公式,如A1.A2.A3.A4
    GOOGLE會變成A1.A1.A1.A1
    想問有什麼解決的好方法嗎

    回覆刪除
    回覆
    1. 1.是不是有什麼公式可供測試?
      2.不同的軟體自然有不同的使用方法!

      Excel 讓許多人感到好用,
      但畢竟它是付費程式,
      然而 Google 試算表不收取任何費用,
      在功能上自然會比較受限。

      刪除
  5. 如樓上大大相同問題
    版大的教學由表格A拉取數據至表格B
    =ImportRange("AAAAAAAAAAAA","工作表1!A1")
    下一格要拉A2的數據,直接往下拖曳會都顯示A1.A1.A1
    =ImportRange("AAAAAAAAAAAA","工作表1!A2"),目前僅能手動更改A1→A2
    小弟需關聯較多數據是否有其他方式可以解決呢?

    回覆刪除
    回覆
    1. 目前還未試過這種問題,
      再說匯入也有數量限制的說!

      刪除
    2. =ImportRange("AAAAAAAAAAAA","工作表1!A1:A10")
      他會自己抓資料從A1抓到A10,
      =ImportRange("AAAAAAAAAAAA","工作表1!A:C")
      他會自己抓資料從A到C。

      刪除
  6. 非常感謝您清楚的說明和分享!
    我用這個方法來將總試算表中不同區塊的資訊及時更新給群組中的不同成員,對我很有幫助!感恩~

    回覆刪除
  7. 你好,非常感謝你的分享,想要請問
    "同一個試算表"裡"不同工作表"的"相同欄位"是否可以相加,我嘗試了一下是可以的,但是他的計算式會變得很複雜且表單右下角下拉欄位的值不會自動改變。

    會這樣詢問的原因是因為我目前想把google表單變成店鋪的庫存表,"不同工作表"代表不同的分店,"相同欄位"則是相同的商品,這樣可以一目了然各店的庫存狀況,除此之外我還想做一個總庫存表所以產生了現在的問題,真的很想解決,不知道老師可否幫忙

    回覆刪除
    回覆
    1. 不好意思,
      針對「表單右下角下拉欄位的值不會自動改變」這句我不是懂完整意義;
      至於計算式會變得複雜就要看試算表的設計有沒有問題!

      我做這個當初是為了計算0050各檔的價位,
      每一檔均以不同的工作表做處理,
      再將各個工作表的結果統整到第一個工作表上顯示結果。

      刪除
  8. 你好,我想請問如果我是工作表一想顯示出工作表二至工作表五同欄位的加總該用怎麼樣的公式呢?

    回覆刪除
    回覆
    1. 您好,近期比較沒看訊息所以晚回…

      假設表1的A1想顯示表2~表5的B1加總結果,
      那表1的A1式子能這樣寫:
      =sum('工作表2'!B1+'工作表3'!B1+'工作表4'!B1+'工作表5'!B1)

      刪除
  9. 您好,我的問題有點複雜難解釋,我想請教假設表1的一個欄位中有1~100的編號,我想把每個不同的編號中的每一列帶入另一個sheet,我的程式應該怎麼寫呢?謝謝您

    回覆刪除