H3:I23{=IFERROR(OFFSET(A$2,SMALL(IF(YEARFRAC(TEXT(RIGHT($C$3:$C$5,8),"00!/00!/00"),TEXT(LEFT($C$3:$C$5,8),"00!/00!/00"))*12/(FIND(LEFT($E$3:$E$5),"季半 年")*3)>=COLUMN($A:$Z),$A$3:$A$5),ROW(A1)),),"")
J3:J23=EDATE(TEXT(LEFT(VLOOKUP(H3,A$3:F$5,3,),8),"00!-00!-00"),FIND(LEFT(VLOOKUP(H3,A$3:F$5,5,)),"季半 年")*3*(COUNTIF(H$3:H3,H3)-1+(RIGHT(VLOOKUP(H3,A$3:F$5,4,),3)="尾月付"))-(RIGHT(VLOOKUP(H3,A$3:F$5,4,),3)="尾月付"))
K3:K23=EDATE(TEXT(LEFT(VLOOKUP(H3,A$3:F$5,3,),8),"00!-00!-00"),FIND(LEFT(VLOOKUP(H3,A$3:F$5,5,)),"季半 年")*3*(COUNTIF(H$3:H3,H3)-1))
L3:L23=EDATE(K3,FIND(LEFT(VLOOKUP(H3,A$3:F$5,5,)),"季半 年")*3)-1
M3:M23=VLOOKUP(H3,A$3:F$5,6,)/LOOKUP(1,0/(I3=B$3:B$5),YEARFRAC(TEXT(RIGHT($C$3:$C$5,8),"00!/00!/00"),TEXT(LEFT($C$3:$C$5,8),"00!/00!/00"))*12/FIND(LEFT(VLOOKUP(H3,A$3:F$5,5,)),"季半 年")/3)
留言
張貼留言