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逾期應收帳款管控,條件式格式設定自動標示 3

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

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

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

四、設計ROUNDDOWN公式

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

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

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

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

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

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

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

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

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

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

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

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

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

每天學習,每天充電:Excel指令文章合集

Excel逾期應收帳款管控,條件式格式設定自動標示 15
加入Line社群,口袋裡的Excel小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.