Excel費用差異分析表:篩選及條件式格式編製報告

Excel編製分析報告時可運用許多不同指令函數,不同工具各有特色。本文以會計實務中的費用差異分析表為範例,介紹如何應用自訂篩選和條件化格式設定。

會計師事務所查帳,針對金額大於重要性的兩期費用會科,詢問客戶差異原因進行分析,算是查帳基本功。在業界編製財務報表,針對兩期差異較大的會科項目,核對分析後作為公司內部管理依據,也是財會部門基本功。有時候如果科目太多,一行一行找出差異大的科目,眼睛容易花掉,耗費時間也就算了,還有可能出錯,這一節以範例分享如何讓Excel一絲不苟嚴謹地代勞:

一、費用差異分析表

簡化的兩期費用差異分析表,針對差異大於30萬者,分析其差異原因。

Excel費用差異分析表:篩選及條件式格式編製報告 41

二、資料排序與篩選

先選取整個報表範圍,這裡因為資料格式非常整齊,所以也可以選取報表第一列或者整個第一列。然後上方功能區「常用」頁籤,於「編輯」指令集中將「排序與篩選」下拉,選擇「篩選」。

Excel費用差異分析表:篩選及條件式格式編製報告 43

三、數字自訂篩選

滑鼠游標停在「數字篩選」,右邊選單可以看到有很多快捷選項,在此點選「自訂篩選」,表示於較完整的對話方塊中設定條件。

Excel費用差異分析表:篩選及條件式格式編製報告 45

四、差異篩選條件

「自訂自動篩選」視窗中,設定「大於或等於」「30000」「或」「小於或等於」「-300000」,非常直覺化的操作。

Excel費用差異分析表:篩選及條件式格式編製報告 47

五、差異分析說明

成功篩選出來了差異大於30萬的會科,包括大於等於30萬和小於等於負30萬,查帳員或會計人員深入追踪瞭解後,填入差異說明,如此即為一份簡單而完整的費用差異說明報表。

Excel費用差異分析表:篩選及條件式格式編製報告 49

六、設定格式化條件

前述篩選有個遺憾,只會顯示差異大、需要分析的會科,其餘會科被隱藏了。很多時候,老闆或主管喜歡報告中顯示完整的資料,再把差異大有說明的部份標示出來,達到較為全面性的掌握。

對此,於上方功能區「常用」頁籤,可以「樣式」指令群組中的「設定格式化條件」,選單下拉後點選「新增規則」。

Excel費用差異分析表:篩選及條件式格式編製報告 51

七、編輯格式化規則

在「新增格式化規則」視窗中,這裡用到規則類型為「只格式化包含下列的儲存格」,於下面的「編輯規則說明」中,設定「儲存格值」「大於或等於」「300000」,接著點選「格式」。

Excel費用差異分析表:篩選及條件式格式編製報告 53

八、設定儲存格格式

出現相當熟悉的「設定儲存格格式」視窗,於「填滿」頁籤中將背影色彩設定為黃色,最後「確定」。

Excel費用差異分析表:篩選及條件式格式編製報告 55

九、差異分析報告

先回到「新增格式化規則」,再按「確定」回到工作表,結果合乎預期,差異大於30萬的儲存格,業已填滿黃色背景。

Excel費用差異分析表:篩選及條件式格式編製報告 57

十、完備差異分析表

依樣畫葫蘆,再設定一項小於負30萬的規則,如此即達成正負差異大於30萬的會科,Excel皆標示黃色了。

Excel費用差異分析表:篩選及條件式格式編製報告 59

自訂篩選與格式化條件

這節範例主要分享以格式化條件替代執行篩選功能,用意是可以在完整報表中呈現異常追踪項目,其實在第五個步驟篩選好了之後,將篩選出來的項目背景填滿顏色,再取消篩選,同樣可以達到相同效果,其操作相對簡單,這裡不特別以圖文說明。另外,格式化條件可說是Excel重點發展的複合指令,隨Office幾次改版增添了許多實用的功能,日後規劃將會以適當範例,完整介紹格式化條件的進階應用。