https://zhidao.baidu.com/question/1883243522035172108.html?qbl=relate_question_0下面這個應該可滿足你的需求儲存格合併,我只會A&"、"&B這種,不會用公式合併,抱歉了D2={INDEX(A:A,MIN(IF(COUNTIF($D$1:D1,$A$2:$A$25)=0,ROW($2:$25))))&""}E2={INDEX($B$2:$B$99,SMALL(IF($A$2:$A$99=$D2,ROW($A$1:$A$24),98),COLUMN(A2)))&""}附加壓縮檔: 201803/mobile01-fd16e62d795ecd664626fdbd814b8ac3.zip
joblyc017 wrote:公式詳解,請參考:h...(恕刪) 每次看joblyc017大大分解公式都有毛塞頓開的感覺,陣列公式真的好難學啊,joblyc017大大,可以的話,我有一文章求負值篩選的,可以麻煩一下嗎?
sam053450941 wrote:請問我的E2:E4公式該怎設才有辦法顯示像H2:H4一樣?...(恕刪) vba 的解法e2=getpart($d2) 其它往下拉'======================================'程式碼放模組裡Function getpart(n As String) As StringApplication.VolatileDim report As String, temptemp = Sheets(1).Range("a1:b6")For i = 2 To UBound(temp)If temp(i, 1) = n Then report = report & "、" & temp(i, 2)Next iIf report = "" Then report = "、無"getpart = Right(report, Len(report) - 1)End Function'======================================
joblyc017大大我弄出來的公式為這樣{=SUBSTITUTE(TRIM(IFERROR(INDEX($B:$B,SMALL(IF(D2=$A$2:$A$6,ROW($B$2:$B$6),""),COLUMN(A2))),"")&""&F2),"","、")}我有按control+shift+enter請問是哪邊錯了呢?
公式須套用至E2:G4{=SUBSTITUTE(TRIM(IFERROR(INDEX($B:$B,SMALL(IF(D2=$A$2:$A$6,ROW($B$2:$B$6),""),COLUMN(A2))),"")&""&F2),"","、")}公式修正1:判斷目標須鎖定D欄的料號公式修正2:是連結一個空格,而非空值公式修正3:是將空格取代成頓號,空值沒辦法做取代