贊贊小屋

Excel逾期應收帳款管控,條件式格式設定自動標示

Excel很多資料檢查工具可以應用在企業的經營管理。本文以應收帳款管控為例,介紹運用MAX、DATE、ROUNDDOWN等函數公式,搭配條件式格式設定,自動標示逾期帳款。

針對逾期應收帳款,要達到異常管控效果,會希望將異常項目特別標示出來,例如把逾期兩個月以上的帳款標黃色,這個可以一筆一筆手動標示,也可以借助Excel自動化標示。在《會計人的Excel小教室》:5.5 「逾期應收帳款」分享過以函數設定,於此借用當時範例,進一步將函數公式整合到格式化條件裡,以下具體介紹:

一、必須認識的Excel日期序列值

首先是插播,先整理Excel中關於日期的概念。如圖所示,Excel是微軟體系一份子,所以概念相通,同樣以1900.01.01作為基準點,因此1900.01.10的序列值是10(十天後),1900.02.01序列值是32(一個月後),1901.01.01是367(一年後),而範例裡的期末日:2016.04.30,序列值是42490。想知道某日期的值為多少,將儲存格格式從「日期」變更為「通用格式」即可,有時候在某些場合,特別是輸入函數公式,日期序列值相當實用。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第1張

二、巧妙利用MAX及DATE函數

沿用先前文章範例,函數公式:「MAX((DATE(2016,4,30)-D2),0)」,如此設定是易於公式閱讀。其實依照上一步驟的說明,直接輸入:「MAX((42490-D2),0)」,效果相同,有興趣讀者可以自行實驗。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第2張

三、新增格式化條件的規則

想將函數公式整合到格式化條件,先選取「E2」到「E9」的範圍,在上方功能區依序點選:「常用」、「樣式」、「設定格式化的條件」、「新增規則」。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第3張

四、設計ROUNDDOWN公式

在「新增格式化規則」視窗中,選擇「使用公式來決定要格式化哪些儲存格」,在「編輯規則說明」中,輸入公式:「 =ROUNDDOWN(MAX((DATE(2016,4,30)-D2)/30,0),0)>=2」。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第4張

五、符合條件的儲存格格式

在上個步驟的視窗中,點選「格式」,在跳出來的「儲存格格式」視窗中,移到「填滿」頁籤,選擇黃色正方形,可以看到下方範例變成一條鮮黃色,表示設定為填滿黃色。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第5張

六、逾期應收帳款自動標示

結果如圖所示,符合期待,逾期60天以上的應收帳款,都已經自動標示黃色。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第6張

七、豐富選擇的格式化條件

眼尖的讀者,應該都有看到格式化條件都很多選項,前面介紹是將既有的函數公式帶進條件中,但其實也可以很直覺地用法,例如類型選取「只格式化包含下列的儲存格」,「儲存格值」設定為「大於或等」,然後在框框中輸入「60」,再將格式設定為填滿黃色,得到結果會和前面一樣。

Excel逾期應收帳款管控,條件式格式設定自動標示 基本操作 第7張

資料篩選與格式化條件的比較

如果熟悉Excel篩選指令,兩相比較,會發現篩選和格式化條件,都是把符合某個規則的儲存格標示出來,篩選是將不合規則的隱藏、只呈現想要的,格式化條件沒有作任何隱藏、不過會將想要的特別註記出來。讀者可依照實務個案上的需要,選擇比較合適方式操作。