若要摘要並報告不同工作表的結果,您可以將每個工作表中的數據合併匯算到主工作表。 工作表可以與主工作表在同一個活頁簿中,或在其他活頁簿中。 當您合併彙算數據時,會集結數據,以便在必要時更輕鬆地更新和匯總。
例如,如果工作表內容是記載各區辦公室的支出,您可能需要使用合併彙算功能,將這些數字整理到主企業支出工作表。 此主工作表可能也包含銷售總額與平均值、目前的庫存量以及整個企業銷售額最高的產品。
根據位置或類別,有兩種方式可以合併匯算數據。
依位置進行合併匯算:來源區域中的數據有相同的順序,並使用相同的標籤。 使用此方法以合併彙算來自一系列工作表的資料,例如從同一個範本建立的部門預算工作表。
依類別進行合併彙算:當來源區域中的資料並未以相同的順序排列,但使用相同的標籤。 使用此方法以合併彙算來自一系列工作表的資料,它們具有不同的版面配置但有相同的資料標籤。
-
依類別合併彙算資料類似於建立樞紐分析表。 不過,透過樞紐分析表,您可以輕鬆地重新組織類別。 如果您需要更有彈性的依類別進行合併匯算,請考慮 建立數據透視表 。
: 本文中的範例是使用 Excel 2016 建立。 雖然如果您使用的是其他版本的 Excel,您的檢視可能會有所不同,但步驟是相同的。
請依照下列步驟,將數個工作表合併匯算成主工作表:
-
如果您尚未設定,請執行下列動作,在每個組成工作表中設定資料:
-
確定每一個數據範圍都採用清單格式。 每一欄的第一列都必須有標籤 (標題) ,並且包含類似的數據。 清單中的任何位置不得有空白列或欄。
-
將每個範圍放在個別的工作表上,但不要在您打算合併匯算數據的主工作表中輸入任何內容。 Excel 會為您執行此動作。
-
確定每個範圍都具有相同的版面配置。
-
-
在主工作表中,按一下要顯示合併彙算資料的區域的左上角儲存格。
: 若要避免覆寫主工作表中的現有數據,請確定在此儲存格右側和下方保留足夠的儲存格,以供合併匯算數據使用。
-
按兩下 [資料工具] 群組) 中的 [資料>合併匯算 (]。
-
在 [函數] 方塊中,按一下要讓 Excel 用來合併彙算資料的彙總函數。 預設函數為 SUM。
以下是已選取三個工作表範圍的範例:
-
選取您的資料。
接下來,在 [ 參照] 方塊中,按兩下 [ 折 疊] 按鈕縮小面板,然後選取工作表中的數據。
按一下包含要合併彙算資料的工作表,選取資料,然後按一下右側的 [展開對話方塊] 按鈕以返回 [合併彙算] 對話方塊。
如果包含要合併匯算之數據的工作表位於另一個活頁簿中,請按兩下 [瀏覽 ] 找出該活頁簿。 找到並按兩下 [ 確定] 之後,Excel 會在 [ 參考 ] 方塊中輸入檔案路徑,並在該路徑加上驚歎號。 接著,您可以繼續選取其他數據。以下是已選取三個工作表範圍的範例:
-
在 [ 合併匯算 ] 彈出視窗中,按兩下 [ 新增]。 重複此步驟以新增所有合併匯算的範圍。
-
自動與手動更新: 如果您希望 Excel 在源資料變更時自動更新合併匯算表,只要核取 [ 建立源數據的連結 ] 方塊即可。 如果未選取此方塊,您可以手動更新合併匯算。
:
-
當來源和目的地區域位於同一個工作表中時,則無法建立連結。
-
如果您需要變更範圍的範圍或取代範圍,請按兩下 [合併匯算] 快顯中的範圍,並使用上述步驟進行更新。 這會建立新範圍參照位址,所以再次進行合併彙算之前,您需要先刪除先前的合併彙算。 只要選擇舊參照,然後按Delete鍵即可。
-
-
按兩下 [確定],Excel 就會為您產生合併匯算。 您也可以選擇套用格式設定。 除非您重新執行合併匯算,否則只需要設定一次格式。
-
各來源範圍間的任何標籤若不相符,合併彙算時會被當作個別的列或欄處理。
-
請確定您不想要合併匯算的任何類別,都會有唯一的標籤顯示在一個來源範圍中。
-
如果要合併匯算的數據位於不同工作表上的不同儲存格中:
輸入公式,其中必須使用指向其他工作表的儲存格參照,為每個工作表各輸入一個。 例如,要合併彙算名為「銷售」(在儲存格 B4)、「人力資源」(在儲存格 F5)、「行銷」(在儲存格 B9) 等工作表中的資料,請在主工作表的儲存格 A2 上輸入下列公式:
: 若要輸入儲存格參照,例如銷售!B4—在不輸入的公式中,將公式輸入到您需要參照的位置,然後按兩下工作表索引標籤,然後按單下單元格。 Excel 會為您完成工作表名稱和儲存格位址。 注意: 在這種情況下,公式可能會容易出錯,因為很容易不小心選取錯誤的單元格。 輸入複雜的公式之後,也很難發現錯誤。
如果要合併匯算的數據位於不同工作表上的相同儲存格中:
輸入使用立體參照的公式,該立體參照使用一個範圍的工作表名稱當作參照。 例如,若要合併匯算儲存格 A2 中的數據,從 Sales 到 Marketing inclusive,請在主工作表的儲存格 E5 中輸入下列專案:
需要更多協助嗎?
您可以隨時詢問 Excel 技術社群中的專家,或在社群中取得支援。