1、已收金额。点击F11输入公式：=IFS(SUMIFS($R$11:$R$500,$O$11:$O$500,C11)>=D11,D11,SUMIFS($R$11:$R$500,$O$11:$O$500,C11)<=D11,SUMIFS($R$11:$R$500,$O$11:$O$500,C11))
2、点击F12输入公式：=IFS(AND((SUMIFS($R$11:$R$500,$O$11:$O$500,C12)-SUMIFS(F$11:F11,C$11:C11,C12))>=0,(SUMIFS($R$11:$R$500,$O$11:$O$500,C12)-SUMIFS(F$11:F11,C$11:C11,C12))<=D12),(SUMIFS($R$11:$R$500,$O$11:$O$500,C12)-SUMIFS(F$11:F11,C$11:C11,C12)),(SUMIFS($R$11:$R$500,$O$11:$O$500,C12)-SUMIFS(F$11:F11,C$11:C11,C12))>=D12,D12)注意：单独点击F12单元格，完成公式下拉填充
3、未收金额。点击G11输入公式：=D11-F11到期日期。点击H11输入公式：=IF(B11="","-",B11+E11)
4、是否到期。点击I11输入：=IFERROR(IF(TODAY()-H11>0,"是","否"),"-")逾期0-30天。点击J11输入：=IFERROR(IF(AND(TODAY()-H11>0,TODAY()-H11<=30),G11,0),"-")
5、逾期30-60天。点击K11输入：=IFERROR(IF(AND(TODAY()-H11>30,TODAY()-H11<=60),G11,0),"-")逾期60-90天。点击L11输入：=IFERROR(IF(AND(TODAY()-H11>60,TODAY()-H11<=90),G11,0),"-")逾期90天以上。点击M11输入：=IFERROR(IF(TODAY()-H11>90,G11,0),"-")
6、回款金额合计。点击R11输入公式：=SUM(P11:Q11)
7、汇总表。C4单元格输入：=SUMIFS($J$11:$J$500,$C$11:$C$500,B4)C5单元格输入：=SUMIFS($J$11:$J$500,$C$11:$C$500,B5)C6单元格输入：=SUMIFS($J$11:$J$500,$C$11:$C$500,B6)
8、C7单元格输入：=IFERROR(SUM(C4:C6)/SUM($C$4:$F$6),"-")
