<p>用一个函数公式下拉完成,用回复可见帖答题</p><p>具体要求见附件</p><p><br/></p><p>我的公式:</p><p>=ROUND(IF(C2="",LOOKUP(99,$C$1:C2)-((ROW()-MATCH(99,$C$1:C2))*((LOOKUP(99,$C$1:C2)-INDEX($C2:$C$1203,MATCH(1,N($C2:$C$1203<>""),)))/(MATCH(1,N($C2:$C$1203<>""),)-1+ROW()-MATCH(99,$C$1:C2)))),C2),1)</p><p>2楼公式:(没有保留1位小数)</p><p>=IF(B2="",TREND(IF({1;0},LOOKUP(1,0/(B$2:B2<>""),B$2:B2),OFFSET(B1,MATCH(1,--(B2:B$1204<>""),),)),IF({1;0},LOOKUP(1,0/(B$2:B2<>""),A$2:A2),OFFSET(A1,MATCH(1,--(B2:B$1204<>""),),)),A2),B2)</p><p>3楼公式:(没有保留1位小数)</p><p>=IF(B2="",ROUND((INDEX(B:B,MIN(IF(($B$2:$B$1204<>"")*(ROW($2:$1204)>ROW()),ROW($2:$1204),4^8)))-INDEX(B:B,MAX(IF(B$2:B2<>"",ROW($2:2)))))/(MIN(IF(($B$2:$B$1204<>"")*(ROW($2:$1204)>ROW()),ROW($2:$1204),4^8))-MAX(IF(B$2:B2<>"",ROW($2:2))))*(ROW()-MAX(IF(B$2:B2<>"",ROW($2:2))))+INDEX(B:B,MAX(IF(B$2:B2<>"",ROW($2:2)))),2),B2)</p><p>10楼公式:(没有保留1位小数)</p><p>=TREND(N(OFFSET($B$1,SMALL(IF(N(INDIRECT("B"&ROW($2:$1204))),ROW($2:$1204),65536),COUNT(B$2:B2)+{0,1})-1,)),SMALL(IF(N(INDIRECT("B"&ROW($2:$1204))),ROW($2:$1204),65536),COUNT(B$2:B2)+{0,1}),ROW())</p>
[此贴子已经被作者于2009-12-25 15:57:09编辑过] |