「複製貼上」能把多張Excel工作表,合在一起計算?超簡單的兩個做法


在用Excel時,該怎麼把多張工作表裡的資料,整合在一起分析和計算?我今天想分享兩個不需要撰寫程式、善用你已經懂得功能就能辦到的兩個操作方式:
1. 透過傳統的複製,再以選擇性貼上的操作,進行運算貼上
2. 藉由連結公式的建立,製作跨工作表的儲存格連結

這兩種解法沒有孰好孰壞的差別,而是根據資料來源的架構、不同的需求、使用時機和情境,再來決定該使用哪一種方式,解決多方資料彙整的需求。

1. 選擇性貼上:直接透過複製貼上,加總數值

適用前提:多張工作表的架構相同,首列和最左側的內容一致

以下用「分區預算表」做例子。通常總部會設計制式的預算表(固定資料範圍裡的「頂端列」和「最左側的標題」內容),交給各區域的負責人填寫各自的預算與實際支出等數據,回傳給總部,由專人彙整處理。

王仲麒

而專人該怎麼整理比較快呢?答案可能讓你意外: 複製貼上就可以了!

四個地區分公司的預算表每個月都會彙報給總公司,而總公司便可以將這四份預算表進行彙整的工作,當然,四個地區分公司的報表規格都一致,裡面的數據則各有差異。

這裡的複製貼上,不是一般的Ctrl+C、Ctrl+V,而是善用「選擇性貼上」的功能,點選[運算]裡[加]選項,將原先複製好的數據,直接在總表裡指定的儲存格進行累加運算。

王仲麒
王仲麒

假設總共有北、中、南、東四個區域的工作表,就進行四次的[選擇性貼上][運算][加]的操作,就可以輕輕鬆鬆彙總成功了!

優點: 操作方便、直覺、簡單。
缺點: 來源資料有所異動時,彙總的結果不會自動更新;彙總運算的方式僅有加、減、乘、除,並沒有提供更多元、更彈性的計算模式;工作表太多的話,要複製多次很麻煩。

2. 等於:藉由連結公式的建立,製作跨工作表的儲存格連結

雖然上述介紹的第一種方式簡單、直覺,但如果分區工作表裡的數據有更動,無法即時更新到總表,必須再來一輪各來源資料的連串複製與選擇性貼上,相對麻煩。

這時候,改由「簡單的公式」來連結跨活頁簿與工作表的資料,就可以在資料異動時即時更新、彙整運算結果了!也就是說,如果希望工作表中的某些資料是來自其它工作表,甚至其他活頁簿,則只要標示清楚該參考資料的儲存格位址,進行資料連結公式的建立,如此,不同來源位置的資料便可以彙算在一起,當來源儲存格資料有所變更時,參照到此儲存格的任何資料也將自動更新。

這個簡單的公式,就是「=」!只要輸入等於符號,加上資料來源的位址,就可以進行資料串聯和運算囉,基本上有兩種輸入規則:

A.若要參照到同一活頁簿裡的某一張工作表的某一儲存格,則連結公式的語法為:

=工作表名稱!儲存格位址

B.若要參照到某一活頁簿檔案裡,某一張工作表的某一儲存格,則連結公式的語法為:

='路徑[活頁簿檔檔名]工作表名稱'!實際位址

如果不想要自己輸入位址,就直接輸入等於符號後,用滑鼠找到想要連結的資料來源,直接點選該儲存格就可以了。

在此範例中,完成一個儲存格的連結公式,並修改適當的絕對或相對位址的參照後,只要將滑鼠指標停在填滿控點上便可以完成相鄰儲存格裡所有連結公式的建立。

王仲麒

完成冗長但有連結資料來源效果的跨活頁簿、跨工作表參照的連結公式,再透過公式填滿操作以完成彙算各個來源資料的目的。

優點: 當連結的來源資料內容有所異動時,彙整的運算結果會自動更新。
缺點: 跨活頁簿、跨工作表的連結公式通常頗為冗長,而參照位置也必須一致(也就是制式規格的資料格式)才適合整體的複製連結公式,複製公式時也要注意絕對位址的調整問題。

延伸課程 / 成為Excel效率王,職場必備「樞紐分析」技能!

延伸閱讀 /

1. 放棄複製貼上吧!Excel內建一招,100份檔案都能「秒速」合併處理

2. 別再做Excel苦工!6 張圖學會「巨集」功能,自動完成重複的操作步驟


王仲麒

30年資訊教育資歷及資訊工具書籍編撰、專欄主筆。長年企業內訓課程規劃與教學,擅長透過案例和學員提問、互動,啟發更多的實戰經驗與反饋,提升員工的效率與競爭力。

延伸閱讀