使用 Excel 計算帳齡分析 Aging Analysis Report

需求說明 

常見帳齡分析的報表例如:客戶帳齡分析、產品庫存呆滯天數分析...等。 
舉例一組簡化的客戶發票資料,包括單號、日期、金額, 
欲將發票資料依天數組距加總統計,得到客戶帳齡分析, 
如下圖。 


原始資料 

交易資料中必要欄位有: 
一個日期欄位 : 用來計算與指定日期相差幾天 
一個數值欄位 : 用來各組距彙總時的數值 

 
定義天數組距 
0 ~ 30 : 表示 0 天至 30 天 
31 ~ 60 : 表示 31 天至 60 天 
以此類推  

 

計算步驟 

1. 延伸原始資料 

(1) Age / 天數 :  計算交易資料 [日期] 與指定日期相差幾天 
(2) 天數組距 :  由 [Age / 天數] 對應組距名稱 


 
公式如下

D38 = DATEVALUE($B$35)-DATEVALUE(B38)

E38 = VLOOKUP(D38,$A$27:$C$31,3,TRUE) 


2. 依照組距加總數值 

利用 Excel 函式 SUMIF 產生各組距的加總。 


公式如下

A62 = SUMIF($E$38:$E$56,A61,$C$38:$C$56) 

B62 = SUMIF($E$38:$E$56,B61,$C$38:$C$56) 


相關連結

下載範例 Excel 檔案

使用 T-SQL 計算帳齡分析 Aging Analysis Report