贊贊小屋

Excel成本計算異常整理:資料剖析、排序、FIND函數

Excel報表內容如果都擠到同一個欄位,除了專門的資料剖析工具,MID、FIND、LEFT等文字函數組合也能完成分開的任務。本文以成本計算異常表為例介紹並比較。

中大型的ERP系統,導出來報表大多可以選擇Data only,每個欄位每列資料排列整齊,沒有合併、沒有空格、沒有跨欄,如此很便於Excel彙總。不過難免有些情況,系統還是會丟給你不怎麼順手的東西,這時候只得自己花點巧思加工處理。以下便以會計每個月成本結算出現的錯誤提示為例,分享將資料剖析、排序等Excel技巧應用資料整理:

一、成本異常勾稽表

首先,成本結算完,系統跑出來的勾稽異常報表不是很漂亮,料號和異常情況併在一起,和通常我們習慣的、依照性質分開欄位不太一樣。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第1張

二、資料工具

仔細一看,料號和異常情況中間隔著空格,想分割的話,Excel剛好有個合適工具,位於上方功能區「資料」頁籤,「資料工具」群組中的正宗「資料剖析」:「將單一文字欄分割成多個欄。」

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第2張

三、資料剖析精靈

資料剖析這個指令,作者於先前的《會計人的Excel VBA小教室》中,第四章第三節《資料剖析應用》已經有介紹過,當時仍然是著重於以函數方式也可以達到和資料剖析相同效果。不過學到的是LEFT、LEN、RIGHT的經典用法。這一節仍然會是資料剖析和函數並用,除了溫習資料剖析此核心指令於不同實務案例的應用之外,重點在於因應不規則文字會使用到的FIND函數,如圖為資料剖析三步驟,如同Excel較為進階指令,會有較多步驟、較多功能選項,然而在操作過程中都是相當直覺。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第3張

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第4張

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第5張

四、報表剖析

剖析之後的結果,漂亮!本來應當如此。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第6張

五、報表排序

Excel資料分開弄好了之後,接下來很容易進行分析處理。首先重新設置好欄位:「料號」、「異常狀況」,接著選取資料範圍進行:「排序」。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第7張

六、從A到Z排序

以「異常狀況」這一欄的值作為基準,從A到Z排序,以中文字而言,類似於依照筆劃排序。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第8張

七、排序後報表

排序之後的報表,清楚一目瞭解,便於管理上依照各種異常狀況,分別追查原因。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第9張

八、MID與FIND函數

除了指令「資料剖析」之外,可以如此設計函數公式:「=MID(A2,FIND(” “,A2)+1,100)」,先找出「A2」儲存格裡空格位置,從這個位置後面一個字元開始,抓取100個字元長度的文字串,因為100是個大數,通常儲存格文字不會超過100個字元這麼長,實際結果便是取空格以後的文字串,也就是系統所要提示的異常狀況。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第10張

九、LEFT與FIND函數

清楚了「FIND」用法,如法泡製便可以抓出料號:「=LEFT(A2,FIND(” “,A2))」,先找出空格所在位置,取空格以前的文字串,也就是包含在合併訊息裡的系統料號。

Excel成本計算異常整理:資料剖析、排序、FIND函數 成本會計 第11張

Excel兩把武器:資料剖析與FIND函數

讀者也許會有疑問,既然有功能完整的「資料剖析」指令,何須再迂迴設計FIND函數公式呢?首先,功夫招式是嫌少不嫌多,多學一招是一招,再者,指令是框架設置好的,函數相對較為靈活自由,例如遇到其它應用或ERP程式跑出來的成本異常訊息,竟然中間沒有空格,或者竟然料號在異常狀況後面,諸如此類的,這時候光會一招「資料剖析」,應該會備感受限,如果好好寫幾個函數,公式稍微複雜了點,但至少事情可能好辦了許多。特別是像成本結算這種工作,每個月都要進行一次,剛開始辛苦點設好公式,下次只要複製貼上就好了,以一句成語來形容,就是一勞永逸!

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010817518