合併彙算多個工作表中的資料

若要從個別的工作表摘要及回報結果,可以從個別的工作表將資料合併彙算到主工作表中。這些工作表可以和主工作表位於同一個活頁簿中,也可以位於其他活頁簿中。當您合併彙算資料時,其實是將資料組合起來,以便能更輕鬆地視需要進行更新與彙總。

例如,如果工作表內容是記載各區辦公室的支出,您可能需要使用合併彙算功能,將這些數字整理到主企業支出工作表。此主工作表可能也包含銷售總額與平均值、目前的庫存量以及整個企業銷售額最高的產品。

提示: 如果您經常合併彙算資料,採用版面配置一致的工作表範本來建立工作表會很有幫助。若要深入了解範本,請參閱:建立範本。這也是設定您 Excel 表格範本的理想時機。

有兩種方法可以合併彙算資料:依類別或依位置。

依位置進行合併彙算:當來源區域中的資料以相同的順序排列,並且使用相同的標籤。使用此方法以合併彙算來自一系列工作表的資料,例如從同一個範本建立的部門預算工作表。

依類別進行合併彙算:當來源區域中的資料並未以相同的順序排列,但使用相同的標籤。使用此方法以合併彙算來自一系列工作表的資料,它們具有不同的版面配置但有相同的資料標籤。

  • 依類別合併彙算資料類似於建立樞紐分析表。不過,透過樞紐分析表,您可以輕鬆地重新組織類別。如果要更靈活地依類別進行合併彙算,請考慮改為建立樞紐分析表

附註: 本文的範例是使用 Excel 2016 建立,所以您的檢視可能會依您使用的版本而有所不同。但步驟是相同的。

合併彙算步驟

  1. 如果您還沒這麼做,則請在每一個包含要合併彙算資料的工作表中,執行下列操作來設定您的資料:

    • 確定各資料範圍均使用清單格式,這樣才能在每一欄的第一列中有一個標籤,並包含類似的資料,而且清單中沒有空白的列或欄。

    • 將每個範圍置於個別的工作表,但不要在您計劃要放置合併彙算的主工作表中輸入任何內容,Excel 會為您填入。

    • 確定每個範圍的版面配置均相同。

  2. 在主工作表中,按一下要顯示合併彙算資料的區域的左上角儲存格。

    附註: 為了避免將目的工作表中的現有資料覆寫為要合併彙算的資料,請確定在這個儲存格的右方及下方預留了足夠的儲存格,以容納合併彙算的資料。

  3. 在 [資料] 索引標籤的 [資料工具] 群組中,按一下 [合併彙算]。

    [資料] 索引標籤上的 [資料工具] 群組

  4. 在 [函數] 方塊中,按一下要讓 Excel 用來合併彙算資料的彙總函數。預設函數為 SUM

    下列範例顯示已選取三個工作表範圍。

    資料合併彙算對話方塊

  5. 選取您的資料

    • 如果包含您要合併彙算的資料之工作表位於其他活頁簿中,請先按一下 [瀏覽] 以找到該活頁簿,然後按一下 [確定] 以關閉 [瀏覽] 對話方塊。Excel 會在 [參照位址] 方塊中輸入檔案路徑,後面接著驚嘆號,而您可以繼續選取資料。

    接下來,在 [參照位址] 方塊中,按一下 [摺疊對話方塊] 按鈕以選取工作表中的資料。

    資料合併彙算摺疊對話方塊

    按一下包含要合併彙算資料的工作表,選取資料,然後按一下右側的 [展開對話方塊] 按鈕以返回 [合併彙算] 對話方塊。

  6. 按一下 [合併彙算] 對話方塊中的 [新增],然後重複執行以加入所有需要的範圍。

  7. 自動與手動更新:如果您希望 Excel 在來源資料變更時自動更新合併彙算表格,請選取 [建立來源資料的連結] 核取方塊。如果未選取此核取方塊,則可手動更新合併彙算。

    附註: 

    • 當來源和目的地區域位於同一個工作表中時,則無法建立連結。

    • 如果您在新增範圍之後需要進行變更,您可以在 [合併彙算] 對話方塊中按一下各個範圍、當它們出現在 [參照位址] 方塊中時將它們更新,然後按一下 [新增]。這會建立新範圍參照位址,所以再次進行合併彙算之前,您需要先刪除先前的合併彙算。請直接選取舊參照位址,然後按 Delete 鍵。

  8. 按 [確定],然後 Excel 會為您產生合併彙算。它會是未格式化的狀態,所以您可以自行決定格式化,但除非您重新執行合併彙算,否則您只需要做一次。

    • 各來源範圍間的任何標籤若不相符,合併彙算時會被當作個別的列或欄處理。

    • 對於任何您不要合併彙算的類別,請確定其具備唯一的標籤,而且這些標籤只出現在一個來源範圍中。

使用公式合併彙算資料

  • 如果要合併彙算的資料位於不同工作表的不同儲存格中

    輸入公式,其中必須使用指向其他工作表的儲存格參照,為每個工作表各輸入一個。例如,要合併彙算名為「銷售」(在儲存格 B4)、「人力資源」(在儲存格 F5)、「行銷」(在儲存格 B9) 等工作表中的資料,請在主工作表的儲存格 A2 上輸入下列公式:

    Excel 多個工作表公式參照

    提示: 如果您希望不用打字輸入就能在公式內輸入如銷售!B4 這類的儲存格參照,請在需要參照的位置輸入公式,按一下該工作表的索引標籤,然後再按一下該儲存格。Excel 會為您填入工作表名稱和儲存格位址。請注意,像這樣的公式容易出錯,因為很容易不小心就選到錯誤的儲存格。此外,輸入公式之後,也很難看出錯誤。

  • 如果要合併彙算的資料位於不同工作表的相同儲存格中

    輸入使用立體參照的公式,該立體參照使用一個範圍的工作表名稱當作參照。例如,若要合併彙算「銷售」到「行銷」(兩者均包含在內) 這幾個工作表儲存格 A2 中的資料,請在主工作表的儲存格 E5 輸入下列公式:

    Excel 3D 工作表參照公式

您知道嗎?

如果您沒有 Office 365 訂閱或最新版本的 Office,您可以立即試用︰

試用 Office 365 或最新版本的 Excel

對特定函數有任何問題嗎?

在 Excel 社群論壇張貼問題

協助我們改進 Excel

您是否有任何關於下一版 Excel 的改善方式的建議?如果有的話,請參閱 Excel User Voice 中的主題

請參閱

Excel 公式概觀

如何避免公式出錯

找出及修正公式中的錯誤

Excel 的鍵盤快速鍵及功能鍵

Excel 函數 (按字母排序)

Excel 函數 (依類別)

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×