該怎麼運用excel製作應收賬款賬齡分析表

準備一張表格（如圖）：如何運用excel製作應收賬款賬齡分析表計算未還金額：（1）單擊單元格G4（2）輸入公式：=D4-F4（3）【Enter】e79fa5e98193e78988e69d8331333365646262（4）【向下填充】如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表計算賬期：（1）單擊單元格H4（2）輸入公式：=IF($C$1-$E4<=0,"未到期",LOOKUP($C$1-$E4,$J$4:$J$7,$K$4:$K$7))（3）【Enter】（4）【向下填充】如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表插入透視圖：（1）【工具欄】（2）【數據】（3）【數據透視表和數據透視圖】（4）【下一步】（5）【選定區域】：Sheet！$B$2:$H$8（6）【下一步】（7）【現有工作表】：Sheet！$B$10（8）【完成】如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表佈局透視表：（1）將【賬期】、【客戶】、【未還金額】拖動至相應位置（2）修改【未還金額】彙總方式：【求和】如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表調整透視表：（1）隱藏空白處：單擊【客戶】下拉選單，將【空白】”√“去掉（2）調整賬期次序：單擊【未到期】，選中【順序】(移至首端）如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表如何運用Excel製作應收賬款賬齡分析表調整表格列寬：（1）選中【C、D、E、F、G、H】六列（2）鼠標定格在【C、D】間（3）向右拖動如何運用Excel製作應收賬款賬齡分析表www.51dongshi.com防採集。

如何用excel製作“應收賬賒賬齡分析表呢？下面以應收賒賬明表爲例，製作可按日期彙總的賬齡分析表。

材料/工具

Excel

方法

添加賬齡和分段列並設定公式。賬齡：D2=B2-TODAY()分段：E2=LOOKUP(D2,{0,30,60,90,180,360},{"0~1個月“，”1~2個月“，”2~3個月“，”3~6個月“，”6~12個月“，”大於一年“.。}）

應收帳款帳齡分析分級表2013年4月30日編報單位:浙江迦南科技股份有限公司序號123578

選定某區域，插入“數據透視表”，這樣就完成啦。

在上表後面加4列，分別註明：30天以內、30-60天、60-90天、90天以上。假設第一行對

示意圖如下:      在G4中輸入e799bee5baa6e59b9ee7ad9431333335343432公式:=IF(E4="","",E4*F4)  ,  公式下拉.      在I4中輸入公式:=IF(H4="","",IF(ISERROR(ADDRESS(MIN(IF(LEN(H5:H105)>0,ROW(C5:C105),""))-1,COLUMN(G4))),INT(SUM(G4:G105)),INT(SUM(G4:INDIRECT(ADDRESS(MIN(IF(LEN(H5:H105)>0,ROW(C5:C105),""))-1,COLUMN(G4)))))))   ,  同時按下ENTER鍵,SHIFT鍵,和CTRL鍵.  公式下拉.      在J4中輸入公式:=IF(LEN(I4)<1,"","欠 "&(TODAY()-A4)&" 天")  ,  公式下拉.     在K4中輸入公式:=IF(LEN(H4)=0,"",SUM(IF((C:C=C4)*(H:H<>""),1,0)))  ,   同時按下ENTER鍵,SHIFT鍵,和CTRL鍵. 公式下拉.     在L4中輸入公式:=IF(LEN(H4)=0,"",SUMIFS(I:I,C:C,C4,H:H,">0"))  ,  公式下拉.至此,大功告成,一份完美的催款報告單就新鮮出爐了，具體如下：1.對應收賬款做賬齡歸類。賬齡分析根據實際需要，可以對賬齡時間段進行分檔設定，一般分爲：0-30，31-90，91-180，181-365，366-720，721以上六檔，企業可以根據自己實際需要進行間隔設定。企業實際操作中一般以發貨日期或開具日爲賬齡開始日，本表中以公式：=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及以上","請檢查"))))))對每筆應收款進行賬齡分類。對於已經分類好的賬款用數據透視表按日期，客戶進行歸集。2.對收款總額按客戶名稱進行歸集，使用數據透視表進行歸集。3.將第一步中數據透視表做出的賬齡分段數據過渡到賬齡分銷表中，使用公式如下：=IF(ISNA(OFFSET(應收明細!$G$1,MATCH(賬齡分析!$A4,應收明細!$G$3:$G$100,0)+1,MATCH(賬齡分析!B$2,應收明細!$G$2:$M$2,0)-1)),0,OFFSET(應收明細!$G$1,MATCH(賬齡分析!$A4,應收明細!$G$3:$G$100,0)+1,MATCH(賬齡分析!B$2,應收明細!$G$2:$M$2,0)-1))，本步驟主要使用OFFSET函數+match函數對分段數據進行過渡。應收總金額則以簡單的sumif函數進行彙總：=SUMIF(收款明細!B:C,賬齡分析!A4,收款明細!C:C)。4.對賬齡進行分析。本步驟原本是賬齡分析中最核心的步驟，但是因爲前面三個步驟已經對賬齡，應收分步驟進行了歸類匯7a686964616fe59b9ee7ad9431333339666662總，在該步驟只需用IF函數即可完成。賬齡覈銷的一個最基本原則就是先覈銷最早之前的賬款，早先的賬款沒有核銷完之前不覈銷賬齡較短的賬款，這是一個需要遵循的原則。本表中對6個時間段的賬齡分析設定基本公式如下：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)))對於收款中客戶預付的款項不在應收賬款賬齡中分析，因爲實質上這款項屬於預收性質，在爲到期賬齡中彙總歸集。（還是沒改，速度還是有點慢，表中兩個按鍵如果不能用，刪掉，手工重新載入數據透視表，無礙內容來自www.51dongshi.com請勿採集。