匿名使用者
匿名使用者 發問時間: 電腦與網際網路軟體 · 6 年前

excel IF CONCATENATE公式修正

http://www.funp.net/12421

請教以上檔案A11公式如何修正,公式如下:

=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

公式需求:

條件1.B2:B3=0,A11=""

條件2.B2:B3其一>0,或B2:B3均>0,A11=A2&B2或A3&B3,或A2&B2換行A3&B3

條件3.同條件二之外,另加B4:B7有>0的儲存格

總之就是抓B欄>0的該列A:B,求解~~~~~~

已更新項目:

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 年前

    AB欄只有6列資料?

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

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

    另B欄為純數值,可沿D大慣用的 IF(B2,A2&B2,) 即可~~

    敝解與D大幾乎相同,僅如上補充!!!

還有問題?馬上發問,尋求解答。