贊贊小屋

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數

Excel篩選排序是報表整理分析的常用指令,本文以會計實務的成本異常狀況表為例,介紹篩選排序應用,進而分享MID、SEARCH、IFERROR函數公式靈活組合的實務案例。

上一節文章是關於成本結算異常訊息的Excel處理,當時範例簡單,只要一次資料剖析、或者套用一次函數,任務即達成。不過,範例總是簡單,真實世界往往複雜,龐大的資料裡必須有些相同的特性,才得以歸納統計、才能一個招式用到底。當系統跑出來的報表或是別人給的資料,不是那麼完美,沒有明顯統一的規則可循的時候,的確是有點麻煩,因為Excel函數功力要能正常發揮,前提條件是規則,假使原始資料真的是不規則堆積起來,唯一能做的,就是逐步在其中找到最大公約數的規則,也許不存在統一適用的規則,只能雜七雜八、東一塊西一塊的,這其實勉強可行,找到一塊是一塊,畢竟只要能夠省時省力,就值得一試。這一節便以實務上可能會遇到的案例作分享:

一、成本異常狀況表

到手的資料相當棘手,初步看起來混亂,想分門別類先行整理,但似乎資料剖析或FIND函數都沒辦法一次解決。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第1張

二、排序與篩選

依照經驗法則,拿到這種資料第一直覺反映:「排序」。管它有多亂,先排序看看!上方功能區「資料」頁籤,「排序與篩選」群組中的「排序」指令。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第2張

三、排序標題

按下「排序」指令,跳出來的視窗很容易理解,以「異常狀況」這一欄為基準,依照字母順序重新排列,注意到右上角有個「我的資料有標題」,勾選狀態和目前的報表型態是一致的。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第3張

四、無標題排序

第二步驟選取範圍的第一行是標題,應該像第三步驟勾選「我的資料有標題」。如果選取範圍的資料本身沒有標題列,例如我們只是選取報表中間一段的資料,這時候就要把「我的資料有標題」的勾勾點掉,排序方式變成Excel欄位,注意到「排序對象」的值預設為儲存格內容,其實也有其它選項,隨著Excel不斷改版,這方面功能稍有增加,通常用到最多的仍然是預設的「值」,但不排除其它選項有用到的情況,例如儲存格色彩實務上也會用到,多知道一點,多多益善。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第4張

五、筆劃順序排序

果然排序之後,資料開始有點規則出來,上面開頭都是英文字母,剛好是存貨料號,統一為七碼,這個就很容易剖析了,下面是文字開頭,顯然按照筆劃多寡順序排列,剛好也就是成本結算的錯誤類型歸類了。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第5張

六、基本篩選

有些時候我們沒有要剖析分割,只是想在一堆混雜資料之中,找出具有特定內容的資料,這時候「篩選」是個蠻適合的指令,同樣是在上方功能區「資料」頁籤的「排序與篩選」群組。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第6張

七、進階篩選

同樣隨著Excel持續改版,「篩選」這個基本指示越來越強大,除了傳統的「文字篩選」,還可以「依色彩篩選」,在這裡我們想要找出某種特定內容的錯誤訊息,所以選擇「文字篩選」中的「包含」。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第7張

八、雙重篩選

於「異常狀況」第一項「包含」中,欄位輸入「工單已入庫」,這裡最多能設置兩項多重條件,之間關係可以是「且」、「或」,Excel還很貼心說明了「?」(單一萬用字元)和「*」(連續萬用字元)。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第8張

九、篩選成功

不錯吧,輕輕鬆鬆,Excel管家幫我拎出想要的東西了。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第9張

十、MID函數

像成本異常檢核表這類案例,是會計結帳每個月例行性工作,資料多且雜,不過基本上大同小異,就是那幾種,如果能找到大致上的規則,也是很方便以函數處理的,例如設計公式:「=MID(B2,8,2)」,一次解決!

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第10張

十一、SEARCH函數

工具不嫌多,再多介紹一個:「=SEARCH(“工單已入庫”,A2)」。「SEARCH」這個函數在支援中心的說明:「在某個文字字串內找到另一個文字字串,並傳回該文字字串在第一個文字字串中的起始位置。」光讀文字不容易理解,實際範例一做就懂。這個步驟裡,計算結果為「8」的,表示「工單已入庫」出現在「A2」儲存格內容的第8個位置,其他儲存格公式找不到所要搜尋的文字串時,會顯示「#VALUE!」。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第11張

十二、IFERROR函數

像這種情況,還可以搭配「IFERROR」函數:「=IFERROR(SEARCH(“工單已入庫”,A2),SEARCH(“結案工單依轉出”,A2))」,表示如果第一個「工單已入庫」找不到,找第二個「結案工單依轉出」,如此便是兩層的IF邏輯結構,照樣造句還可以再繼續內嵌,三層、四層,端視實務上遇到的情況。

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第12張

篩選指令與SEARCH函數比較

如同本節第八步驟清楚所示,篩選指令有其先天限制,至多兩項條件,實務狀況需要三個以上的話,必須設計函數公式了,例如本節第十二步驟所示,SEARCH函數IFERROR函數可以多重套嵌,Excel支援文件稱其為「巢狀函數」,表示函數公式中的某一參數是由另一個相同或不同的函數公式計算而來,Excel最多允許以巢狀方式內嵌 64 個不同的函數。實務上不太可能用到64層巢狀函數,大約到第四五層,公式看起來就會相當複雜難以理解了,不過,有這個可能性總比沒有好,至少不會受限於像是篩選指令最多兩項的設計。

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

博客來網路書店網址:

Excel成本會計案例:排序篩選、MID、SEARCH、IFERROR函數 成本會計 第13張