|
B1=MONTH(sheet2!C2)
D1=DAY(DATE(2014,B1+1,0))
以下公式下拉,其中A4、F4、G4单元格公式三键回车(公式复制后,点一下公式编辑栏的任意位置,先按住 ctrl、shift 两个键,然后敲enter键)
A4=IF(MOD((ROW()-3),$D$1+1)=0,"",INDEX(sheet2!A:A,SMALL(IF(MATCH(sheet2!$A$2:$A$97,sheet2!$A$2:$A$97,)=ROW($A$2:$A$97)-3,ROW($2:$97)),INT((ROW()-4)/($D$1+1))+1)))
B4=IF(A4="","",DATE(2014,B$1,MOD(ROW()-3,D$1+1)))
D4=IF(A4="","",VLOOKUP($A4,sheet2!$A$2:$F$97,2,0))
E4=IF(B4="","",VLOOKUP($A4,sheet2!$A$2:$F$97,4,0))
F4=IF(A4="","",TEXT(INDEX(sheet2!$C$1:$C$100,SMALL(IF(DAY(sheet2!$C$2:$C$100)=DAY($B4)*(sheet2!$A$2:$A$100=Sheet3!$A4),ROW($A$2:$A$100)),COLUMN()-5)),"h:m:s;;;"))
G4=IF(A4="","",TEXT(INDEX(sheet2!$C$1:$C$100,SMALL(IF(DAY(sheet2!$C$2:$C$100)=DAY($B4)*(sheet2!$A$2:$A$100=Sheet3!$A4),ROW($A$2:$A$100)),COLUMN()-5)),"h:m:s;;;"))
|
|