張錢霞用excel制作“應(yīng)收賬賒賬齡分析表,，下面以應(yīng)收賒賬明表為例，制作可按日期匯總的賬齡分析表,。  材料/工具Excel 方法
選取區(qū)域，插入 - 數(shù)據(jù)透視表 具體如下： 1.對應(yīng)收賬款做賬齡歸類,。賬齡分析根據(jù)實(shí)際需要,，可以對賬齡時(shí)間段進(jìn)行分檔設(shè)置，一般分為：0-30,，31-90,，91-180，181-365,，366-720,，721以上六檔，企業(yè)可以根據(jù)自己實(shí)際需要進(jìn)行間隔設(shè)置。企業(yè)實(shí)際操作中一般以發(fā)貨日期或發(fā)票開具日為賬齡開始日,，本表中以公式：=IF(D2<=30,"0-30",IF(AND(D2<=90,D2>30),"31-90",IF(AND(D2<=180,D2>90),"91-180",IF(AND(D2<=365,D2>180),"181-365",IF(AND(D2<=720,D2>365),"366-720",IF(D2>720,"721及以上","請檢查"))))))對每筆應(yīng)收款進(jìn)行賬齡分類,。對于已經(jīng)分類好的賬款用數(shù)據(jù)透視表按日期，客戶進(jìn)行歸集,。 2.對收款總額按客戶名稱進(jìn)行歸集,，使用數(shù)據(jù)透視表進(jìn)行歸集。 3.將第一步中數(shù)據(jù)透視表做出的賬齡分段數(shù)據(jù)過渡到賬齡分銷表中,，使用公式如下： =IF(ISNA(OFFSET(應(yīng)收明細(xì)!$G$1,MATCH(賬齡分析!$A4,應(yīng)收明細(xì)!$G$3:$G$100,0)+1,MATCH(賬齡分析!B$2,應(yīng)收明細(xì)!$G$2:$M$2,0)-1)),0,OFFSET(應(yīng)收明細(xì)!$G$1,MATCH(賬齡分析!$A4,應(yīng)收明細(xì)!$G$3:$G$100,0)+1,MATCH(賬齡分析!B$2,應(yīng)收明細(xì)!$G$2:$M$2,0)-1)),，本步驟主要使用OFFSET函數(shù)+match函數(shù)對分段數(shù)據(jù)進(jìn)行過渡。 應(yīng)收總金額則以簡單的sumif函數(shù)進(jìn)行匯總：=SUMIF(收款明細(xì)!B:C,賬齡分析!A4,收款明細(xì)!C:C),。 4.對賬齡進(jìn)行分析,。 本步驟原本是賬齡分析中最核心的步驟，但是因?yàn)榍懊嫒齻€(gè)步驟已經(jīng)對賬齡,，應(yīng)收分步驟進(jìn)行了歸類匯總,，在該步驟只需用IF函數(shù)即可完成。 賬齡核銷的一個(gè)最基本原則就是先核銷最早之前的賬款,，早先的賬款沒有核銷完之前不核銷賬齡較短的賬款,，這是一個(gè)需要遵循的原則。 本表中對6個(gè)時(shí)間段的賬齡分析設(shè)置基本公式如下： 721及以上=IF((H4-SUM(B4:G4))>0,0,IF((G4-H4)>=0,(G4-H4),0)) 366-720：=IF((H4-SUM(B4:G4))>0,0,IF(N4>0,F4,IF(AND((SUM(F4:G4)-H4)>0,F4>0),(SUM(F4:G4)-H4),0))) 181-365：=IF((H4-SUM(B4:G4))>0,0,IF(M4>0,E4,IF(AND((SUM(E4:G4)-H4)>0,E4>0),(SUM(E4:G4)-H4),0))) 91-180：=IF((H4-SUM(B4:G4))>0,0,IF(L4>0,D4,IF(AND((SUM(D4:G4)-H4)>0,D4>0),(SUM(D4:G4)-H4),0))) 31-90：=IF((H4-SUM(B4:G4))>0,0,IF(K4>0,C4,IF(AND((SUM(C4:G4)-H4)>0,C4),(SUM(C4:G4)-H4),0))) 0-30：=IF((H4-SUM(B4:G4))>0,0,IF(J4>0,B4,IF(AND((SUM(B4:G4)-H4)>0,B4>0),(SUM(B4:G4)-H4),0))) 對于收款中客戶預(yù)付的款項(xiàng)不在應(yīng)收賬款賬齡中分析,，因?yàn)閷?shí)質(zhì)上這款項(xiàng)屬于預(yù)收性質(zhì),，在為到期賬齡中匯總歸集。 END 客戶借款明細(xì)表 添加一列公式,，計(jì)算借款天數(shù) =Today()-b2 注：Today函數(shù)可以返回當(dāng)天日期,，兩個(gè)日期相減可以得到間隔天數(shù)。另外還需要把D列設(shè)置成常規(guī)格式,，否則會(huì)顯示成日期,。 插入數(shù)據(jù)透視表并根據(jù)天數(shù)進(jìn)行組合 最終結(jié)果： 注：分段的天數(shù)是固定的，如果自定義,，需要在原表中再加一個(gè)列判斷公式 =LOOKUP(D2,{0,'0~30天';31,'31~90天';91,'91~120天';121,'120天以上'})
|
|
|
