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

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

若要從個別的工作表摘要及報告結果,您可以將每個工作表的資料合併至主工作表。 工作表可以與主工作表在同一個活頁簿中,或是在其他活頁簿中。 合併資料時,您可以彙集資料,以便在必要時更容易更新及匯總。

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

提示: 如果您經常合併資料,可能會協助您從使用一致版面配置的工作表範本建立新的工作表。 若要深入了解範本,請參閱:建立範本。 這也是設定您 Excel 表格範本的理想時機。

有兩種方法可以合併資料(依位置或類別)。

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

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

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

附註: 本文中的範例是使用 Excel 2016 建立的。 雖然您的視圖可能會因您使用的是其他版本的 Excel 而有所不同,但上述步驟是一樣的。

請按照下列步驟,將多個工作表合併至主工作表中:

  1. 如果您尚未安裝,請執行下列動作,在每個構成的工作表中設定資料:

    • 確定每個資料範圍都是清單格式。 每個資料行在第一列中必須有一個標籤(標題),而且包含類似的資料。 清單中的任何位置都不能有空白的列或欄。

    • 將每個範圍放在個別的工作表上,但不要在您打算合併資料的主工作表中輸入任何內容。 Excel 會為您執行這項作業。

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

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

    附註: 若要避免覆寫主工作表中的現有資料,請確定您在此儲存格右側及下方留出足夠的儲存格,以用於合併的資料。

  3. 按一下[資料>合併] (在 [資料工具] 群組中)。

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

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

    以下是已選取三個工作表範圍的範例:

    資料合併彙算對話方塊

  5. 選取您的資料。

    接著,在 [參照] 方塊中,按一下 [折迭] 按鈕縮小面板,然後選取工作表中的資料。

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

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

    如果包含您要合併的資料的工作表位於其他活頁簿中,請按一下[流覽]以找出該活頁簿。 尋找並按一下[確定]後,Excel 會在 [參照] 方塊中輸入檔案路徑,並在該路徑上附加驚嘆號。 接著,您可以繼續選取 [其他資料]。

    以下是已選取三個工作表範圍的範例:

    資料合併彙算對話方塊

  6. 在 [整合] 快顯視窗中,按一下 [新增]。 重複此步驟,以新增您合併的所有範圍。

  7. 自動與手動更新: 如果您希望 Excel 在來源資料變更時自動更新合併資料表,只要核取 [建立來源資料的連結] 核取方塊即可。 如果此方塊保持未選取狀態,您可以手動更新合併。

    附註: 

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

    • 如果您需要變更範圍的範圍(或取代範圍),請在 [合併] popup 中按一下該範圍,然後使用上述步驟進行更新。 這會建立新範圍參照位址,所以再次進行合併彙算之前,您需要先刪除先前的合併彙算。 只要選擇舊參照,然後按 Delete 鍵。

  8. 按一下[確定],Excel 就會為您產生合併。 或者,您也可以套用格式設定。 除非您重新執行合併,否則只需要格式化一次。

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

    • 確保您不想要合併的任何類別都有唯一的標籤,且只出現在一個來源範圍中。

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

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

Excel 多個工作表公式參照
 

提示: 若要輸入儲存格參照,例如 Sales!B4-在沒有輸入的公式中,將公式輸入到您需要參照的點,然後按一下工作表索引標籤,然後按一下儲存格。 Excel 會為您完成工作表名稱和儲存格位址。 注意:這些案例中的公式可能容易出錯,因為很容易不小心選取了錯誤的儲存格。 在輸入複雜公式之後,可能難以找出錯誤。

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

輸入使用立體參照的公式,該立體參照使用一個範圍的工作表名稱當作參照。 例如,若要將儲存格 A2 中的資料合併為 [銷售] 與 [行銷],請在主工作表的儲存格 E5 中輸入下列內容:

Excel 3D 工作表參照公式

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

請參閱

Excel 公式概觀

如何避免公式出錯

找出及修正公式中的錯誤

Excel 的鍵盤快速鍵及功能鍵

Excel 函數 (按字母排序)

Excel 函數 (依類別)

使用 Microsoft 365 保持領先

需要更多協助?

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×