Excel如何將部門分攤表設置為儲存格合併

有時候編制報表,喜歡把相同的分類項目合併起來,報表呈現較為簡潔美觀。然而如此一來,不方便Excel進一步處理,例如篩選、查找、樞紐分析表等等的。先前有文章分享如何取消儲存格合併,這裡則是反過來,就是基於報表美觀的需求,想要合併相同類別的儲存格,在Excel怎麼做比較有效率,以下具體介紹:

一、簡單設置的製造成本分攤表。依照部門會科的人工製造,某個既定比率分攤給各生產工序。

二、選定報表範圍,上方功能區依序選取:「資料」、「大綱」、「小計」。

三、於「小計」視窗中,選項蠻多的,但應該都不難理解,這裡重要的是「分組小計欄位」下拉選擇「部級部門」,亦即要合併儲存格的欄位,「新增小計位置」變更勾選「部級部門」,也就是報表最左邊的欄位,另外勾選「摘要置於小計資料下方」(如果預設沒有的話),設置好了按「確定」。

四、「小計」執行結果如圖所示。因為「分組小計欄位」和「摘要置於小計資料下方」在同一個位置,部門小計例如「成型部 合計」被擠到左邊自動新增的欄位,而且也是自動新增的一列,這裡的箇中奧妙,讓我們繼續往下看。

五、選取「A2」到「A9」想同項合併的範圍,快速組合鍵「Ctrl+G」,跳出「到(定位)」視窗,按「特殊」。

六、於「特殊目標」視窗選擇「空格」、再按「確定」。

七、可以看到空格部份都被選取了,接著在上方功能區的「常用」頁籤、「對齊方式」群組、選擇「跨欄置中」:「將選定儲存格中的內容合併在一個較大的新儲存格內並置中。」輔助說明看起來就是我們想要的。

八、如圖所示,成功合併之後,選擇報表中任何一個儲存格,例如「A2」,再一次操作第二步驟的「小計」,Excel會自動變成選取整個報表範圍,直接按「全部移除」。

九、執行結果,仔細看看A欄,已經是想要的儲存格合併效果了,差就差在我們希望的是B欄,於是萬事俱備,只欠東風。

十、「常用」、「剪貼簿」、「複製格式」,有來上過贊贊小屋基礎Excel課程的讀者,應該都知道接下來會發生什麼事……

十一、賓果!耍了好幾個小聰明,終於得到它了。範例雖然看來「假勞」(台語意思大約是聰明過頭),但如果實務上是一長串的報表、然後讀者已經把這裡的十一道小秘笈練上手了,成本效益之間,那是非常地清楚。

這一節介紹將報表重複項目儲存格合併,實務個案要合併的儲存格可能數量和位置都不一樣,沒有贊贊小屋小技巧的話,手工做有點哀怨。不過這裡雖然介紹了一次性整理的方法,操作上技術性較高,要好幾道步驟,難免會想說能否用VBA代碼方式,大聰明寫成一個懶人包,簡簡單單一鍵實現。但是回過頭來如前所述,實務個案差異性大,再加上運用到的Excel技巧性操作多,因此要編寫個適當的VBA代碼想必是件虐殺腦細胞的任務。像這樣的事,還是要回歸會計原則的成本效益考量,看個人在實務上用到機會多不多,多的話,也許該考驗一下自己的VBA功力了。

PS:贊贊小屋傾全力Excel VBA代表作《人人做得到的網路資料整理術》終於出版,全書範例招式如果都能學起來,VBA功力保證更上好幾層樓。這篇文章最後其實留下了一個戰帖,對於所有想挑戰的朋友,在此誠心推薦可以先讀讀我的新書!