D2:D6{=COUNT(0/(FREQUENCY(COLUMN(A:Z),IF(TEXT(DATE(YEAR(B2),MONTH(B2)+COLUMN(A:Z)-1,1),"E:M")-TEXT(DATE(YEAR(C2),MONTH(C2),1),"E:M")<=0,INT((MONTH(B2)+COLUMN(A:Z)-2)/3)+1))))-1
F2{=INDEX(A:A,SMALL(IF(D$2:D$6>=COLUMN(A:Z),ROW($2:$6),9),ROW(A1)))&""
G2=IF(F2="","",YEAR(VLOOKUP(F2,A:D,2,))+INT((MONTH(VLOOKUP(F2,A:D,2,))+COUNTIF(F$2:F2,F2)*3-4)/12)&"-Q"&MOD(ROUNDUP(MONTH(VLOOKUP(F2,A:D,2,))/3,)+COUNTIF(F$2:F2,F2)-2,4)+1)
H2{=IF(F2="","",INDEX(FREQUENCY(ROW(INDIRECT(DATE(LEFT(G2,4),RIGHT(G2)*3-2,1)&":"&DATE(LEFT(G2,4),RIGHT(G2)*3,OR(RIGHT(G2)-{1,4}=0)+30))),VLOOKUP(F2,A:C,{2,3},)-{1,0}),2))
留言
張貼留言