# excel IF CONCATENATE公式修正

http://www.funp.net/12421

=IF(AND(B2=0,B3=0),"",IF(AND(B2>0,B3=0),A2&B2,IF(AND(B2>0,B3>0),CONCATENATE(A2&B2,CHAR(10),A3&B3),IF(AND(B2>0,B3>0,B4>0),CONCATENATE(A2&B2,CHAR(10),A3&B3,CHAR(10),A4&B4),IF(AND(B2>0,B3>0,B4>0,B5>0),CONCATENATE(A2&B2,CHAR(10),A3&B3,CHAR(10),A4&B4,CHAR(10),A5&B5),IF(AND(B2>0,B3>0,B4>0,B5>0,B6>0),CONCATENATE(A2&B2,CHAR(10),A3&B3,CHAR(10),A4&B4,CHAR(10),A5&B5,CHAR(10),A6&B6),""))))))

1.B2可能為0或>0

2.B3可能為0或>0

3.B4:B7如B2或B3為0則一定=0，但如B2或B3>0則B4:B7則可能=0或>0

D大~

### 2 個解答

• Daniel
Lv 7
6 年前
最佳解答

A11:

=IF(OR(B2>0,B3>0),SUBSTITUTE(TRIM(IF(B2>0,A2&B2,)&IF(B3>0," "&A3&B3,)&IF(B4>0," "&A4&B4,)&IF(B5>0," "&A5&B5,)

2015-04-04 12:41:22 補充：

接上...

&IF(B6>0," "&A6&B6,)&IF(B7>0," "&A7&B7,))," ",CHAR(10)),"")

試試看!

2015-04-07 12:16:10 補充：

如A2~A7不會有空白，則A11可寫為:

=IF(OR(B2>0,B3>0),SUBSTITUTE(TRIM(IF(B2>0,A2&B2,)&IF(B3>0," "&A3&B3,)&IF(B4>0," "&A4&B4,)&IF(B5>0," "&A5&B5,)&IF(B6>0," "&A6&B6,)&IF(B7>0," "&A7&B7,))," ",CHAR(10)),"")

如B2~B7只可能等於或大於0的純數值，不會小於0也不會有文字，則可簡為:

=IF(B2+B3,SUBSTITUTE(TRIM(IF(B2,A2&B2,)&IF(B3," "&A3&B3,)&IF(B4," "&A4&B4,)&IF(B5," "&A5&B5,)&IF(B6," "&A6&B6,)&IF(B7," "&A7&B7,))," ",CHAR(10)),"")

• 6 年前

ＡＢ欄只有６列資料？

2015-04-04 15:45:21 補充：

後面數值依B2.B3而生，所以 =IF(OR(B2>0,B3>0), 應可省略，

另Ｂ欄為純數值，可沿Ｄ大慣用的 IF(B2,A2&B2,) 即可～～

敝解與Ｄ大幾乎相同，僅如上補充！！！