Board logo

標題: [發問] EXCEL函數下拉有的要自動跳號有的要固定 [打印本頁]
作者: qq78787    時間: 2013-8-29 20:09     標題: EXCEL函數下拉有的要自動跳號有的要固定

請問 我有個函數 =IF(B3='1'!$C$2,SUM('1'!$C$8:$C$50)-SUM('1'!$V$8:$V$50),0) 要往下拉
可以自動產生      =IF(B4='2'!$C$2,SUM('2'!$C$8:$C$50)-SUM('2'!$V$8:$V$50),0)
                            =IF(B5='3'!$C$2,SUM('3'!$C$8:$C$50)-SUM('3'!$V$8:$V$50),0)
藍色就是下拉自己產生的
我想要紅色部分也跟著變

感謝大家了
作者: jakcy1234    時間: 2013-8-29 22:22

用replace將  '1'!   變做  DD1,   再將最後的,0 取消 , 然後拉下

=IF(B4=DD1,$C$2,SUM(DD1,$C$8C$50)-SUM(DD1,$V$8V$50))
=IF(B5=DD2,$C$2,SUM(DD2,$C$8C$50)-SUM(DD2,$V$8V$50))
=IF(B6=DD3,$C$2,SUM(DD3,$C$8C$50)-SUM(DD3,$V$8V$50))
=IF(B7=DD4,$C$2,SUM(DD4,$C$8C$50)-SUM(DD4,$V$8V$50))
=IF(B8=DD5,$C$2,SUM(DD5,$C$8C$50)-SUM(DD5,$V$8V$50))
=IF(B9=DD6,$C$2,SUM(DD6,$C$8:$C$50)-SUM(DD6,$V$8:$V$50))
=IF(B10=DD7,$C$2,SUM(DD7,$C$8:$C$50)-SUM(DD7,$V$8:$V$50))
=IF(B11=DD8,$C$2,SUM(DD8,$C$8:$C$50)-SUM(DD8,$V$8:$V$50))
=IF(B12=DD9,$C$2,SUM(DD9,$C$8:$C$50)-SUM(DD9,$V$8:$V$50))
=IF(B13=DD10,$C$2,SUM(DD10,$C$8:$C$50)-SUM(DD10,$V$8:$V$50))
=IF(B14=DD11,$C$2,SUM(DD11,$C$8:$C$50)-SUM(DD11,$V$8:$V$50))

再用replace將  =IF    變做  '=IF
再用replace將  =DD   變做  ='
再用replace將  (DD   變做  ('
再用replace將  ,$    變做  '!
再用replace將  '=IF    變做  =IF

=IF(B4='1'!C$2,SUM('1'!C$8:$C$50)-SUM('1'!V$8:$V$50))
=IF(B5='2'!C$2,SUM('2'!C$8:$C$50)-SUM('2'!V$8:$V$50))
=IF(B6='3'!C$2,SUM('3'!C$8:$C$50)-SUM('3'!V$8:$V$50))
=IF(B7='4'!C$2,SUM('4'!C$8:$C$50)-SUM('4'!V$8:$V$50))
=IF(B8='5'!C$2,SUM('5'!C$8:$C$50)-SUM('5'!V$8:$V$50))
=IF(B9='6'!C$2,SUM('6'!C$8:$C$50)-SUM('6'!V$8:$V$50))
=IF(B10='7'!C$2,SUM('7'!C$8:$C$50)-SUM('7'!V$8:$V$50))
=IF(B11='8'!C$2,SUM('8'!C$8:$C$50)-SUM('8'!V$8:$V$50))
=IF(B12='9'!C$2,SUM('9'!C$8:$C$50)-SUM('9'!V$8:$V$50))
=IF(B13='10'!C$2,SUM('10'!C$8:$C$50)-SUM('10'!V$8:$V$50))
=IF(B14='11'!C$2,SUM('11'!C$8:$C$50)-SUM('11'!V$8:$V$50))
作者: ANGELA    時間: 2013-8-29 22:35

=IF(B3=INDIRECT(ROW(A2)&"!$C$2"),SUM(INDIRECT(ROW(A2)&"!$C$8:$C$50"))-SUM(INDIRECT(ROW(A2)&"!$V$8:$V$50"),0))
作者: qq78787    時間: 2013-8-30 09:09

回復 2# jakcy1234

謝謝你 向下拉可以變數了

但是我用不出來
再用replace將  =IF    變做  '=IF
再用replace將  =DD   變做  ='
再用replace將  (DD   變做  ('
再用replace將  ,$    變做  '!
再用replace將  '=IF    變做  =IF

可以教一下嗎! 謝謝



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)