iT邦幫忙

1

Excel函數問題!!!

excel excel vba vba excel 2013
小哈 2018-04-27 16:43:15 ‧ 11015 瀏覽

  • 分享至 

  • xImage

Dear IT前輩們~
以下方圖片問題!!!要用函數處理...
想把合併字串(B2欄位),只有【】拆開,並把字串塞到C D E欄位去...
可以麻煩前輩們指點一下技巧嗎/images/emoticon/emoticon16.gif
而合併字串(B2欄位),最前面未來可能還會有【xxx】值,所以只取最後三個塞過去CDE欄位就OK了...
則C2,D2,E2該怎麼表示函數呢
https://ithelp.ithome.com.tw/upload/images/20180427/20089833EyQtlQt7Bf.jpg

㊣浩瀚星空㊣ iT邦大神 1 級 ‧ 2018-04-27 17:09:15 檢舉
如果是由左至右的。那我倒還可以幫你。
你是你有一項需求。我實在想不到有什麼方法可以辦到。
就是你要取字串最後的3個。
除了寫成巨集，我已經想不到招了。

給你相應對的函數好了。LEFT、MID、RIGHT、SEARCH 及 LEN
一般是搭配這幾個處理的。去查看看吧。
不過這幾個只有辦法幫你處理前三個。
froce iT邦大師 1 級 ‧ 2018-04-27 19:35:31 檢舉
乖乖寫VBA或是丟給其他語言去處理吧。
就算寫出來也會複雜到你不想用。

python的話大概一行就寫得出來。
"【xxx】【xxx】【xxx】".replace("【", "").split("】")[-4:-1]
小哈 iT邦新手 4 級 ‧ 2018-04-27 22:24:09 檢舉
對啊，我也是有點困擾ˊˋ
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

4 個回答

1
paicheng0111
iT邦大師 5 級 ‧ 2018-04-27 22:40:44
最佳解答

撰寫陣列公式

在C2儲存格

  1. 輸入:
    =LEFT(RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="【",ROW(INDIRECT("1:"&LEN($B2))),""),3)),SEARCH("】",RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="【",ROW(INDIRECT("1:"&LEN($B2))),""),3)))-1)

  2. 按Ctrl + Shift + Enter

在D2儲存格

  1. 輸入:
    =LEFT(RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="【",ROW(INDIRECT("1:"&LEN($B2))),""),2)),SEARCH("】",RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="【",ROW(INDIRECT("1:"&LEN($B2))),""),2)))-1)

  2. 按Ctrl + Shift + Enter

在E2儲存格

  1. 輸入:
    =LEFT(RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="【",ROW(INDIRECT("1:"&LEN($B2))),""),1)),SEARCH("】",RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="【",ROW(INDIRECT("1:"&LEN($B2))),""),1)))-1)

  2. 按Ctrl + Shift + Enter

選取C2:E2，用填滿控點往下拉即可

結果如下圖

結果

看更多先前的回應...收起先前的回應...
froce iT邦大師 1 級 ‧ 2018-04-27 22:46:43 檢舉

真的寫出來了，不過看完我還是覺得我乖乖寫python去處理吧。哈

paicheng0111 iT邦大師 5 級 ‧ 2018-04-27 22:53:52 檢舉

是寫出來了，不知道合不合用。

基本上就是利用excel函數進行陣列運算。
有點類似Matlab或是python的pandas。

我在這篇的回應中有稍微解釋陣列公式的運算邏輯。

froce iT邦大師 1 級 ‧ 2018-04-27 23:13:52 檢舉

陣列公式感覺很像sumproduct？

paicheng0111 iT邦大師 5 級 ‧ 2018-04-27 23:16:01 檢舉

froce，就是這感覺。

小哈 iT邦新手 4 級 ‧ 2018-04-30 09:58:39 檢舉

感謝pcw大,只是我套用程式碼後,呈現#NUM
是Code哪個地方要調整呢?麻煩前輩指點小弟...
https://ithelp.ithome.com.tw/upload/images/20180430/20089833UVCDfnn3Ya.jpg

小哈 iT邦新手 4 級 ‧ 2018-04-30 11:01:50 檢舉

前輩,很像只有E攔OK,只是E攔還是只抓第一組字串
https://ithelp.ithome.com.tw/upload/images/20180430/20089833e1IstOye3i.jpg

paicheng0111 iT邦大師 5 級 ‧ 2018-04-30 11:04:26 檢舉

因為你沒有按ctrl + shift + enter，而是僅按enter。
如果你按ctrl + shift + enter的話，你的公式會自動被{}包起來被當作陣列來處理。

https://i.imgur.com/GcaZlba.png

小哈 iT邦新手 4 級 ‧ 2018-05-04 11:02:33 檢舉

感謝pcw前輩的幫忙,暫時沒問題,只是還有些問題還沒解,先試著解看看:)
不行再反應上來~~~

登入發表回應
2
蟹老闆
iT邦大師 1 級 ‧ 2018-04-27 21:15:35

請參考

C欄
=IF(ISNUMBER(FIND(C1,(MID($B$2,LEN($B$2)-(LEN($C$1)+LEN($D$1)+LEN($E$1)+5),(LEN($C$1)+LEN($D$1)+LEN($E$1)+6)))))=TRUE,C1,"")
D欄
=IF(ISNUMBER(FIND(D1,(MID($B$2,LEN($B$2)-(LEN($C$1)+LEN($D$1)+LEN($E$1)+5),(LEN($C$1)+LEN($D$1)+LEN($E$1)+6)))))=TRUE,D1,"")
E欄
=IF(ISNUMBER(FIND(E1,(MID($B$2,LEN($B$2)-(LEN($C$1)+LEN($D$1)+LEN($E$1)+5),(LEN($C$1)+LEN($D$1)+LEN($E$1)+6)))))=TRUE,E1,"")

這個是運用在什麼地方?看起來似乎是老師出的考題.
補充:
第一列需如圖
https://ithelp.ithome.com.tw/upload/images/20180427/20006149bdGdkRM6fj.jpg
若不想看起來複雜可以直接將需要的字串字數放入公式裡.
若未來會產生的字串過少會產生問題請注意

看更多先前的回應...收起先前的回應...
paicheng0111 iT邦大師 5 級 ‧ 2018-04-27 22:10:10 檢舉

為何要參照C1、D1 與 E1呢？

小哈 iT邦新手 4 級 ‧ 2018-04-27 22:29:09 檢舉

感謝蟹老闆提供資訊…
CDE欄位可以參照B欄位去處理嗎？
因為B3以下的儲存格也會有更多的【xxx】資料，所以B欄位的都要取後面三筆，不管多少筆都取最後三筆~
想破頭……

蟹老闆 iT邦大師 1 級 ‧ 2018-04-27 22:59:56 檢舉

我的思路是這樣的
將要取出的三個字串統計數量後-去B欄的整個字串,然後取右邊剩餘字串再去搜有無CDE中的字串,有則顯示相關字串.
但這會有個問題,假設要取出的字串長度是20,而最後三個假設是[A][B][C]這樣的話就會有問題,若可將字串長度統一則可安心使用

pcw用做統計字串長度及搜尋條件
harry789111是參照B欄沒錯

小哈 iT邦新手 4 級 ‧ 2018-05-04 11:01:44 檢舉

感謝前輩們的幫忙,暫時沒問題,只是還有些問題還沒解,先試著解看看:)
不行再回應上來~~~

登入發表回應
0
richardsuma
iT邦大師 1 級 ‧ 2018-04-30 08:58:53

https://ithelp.ithome.com.tw/upload/images/20180430/20001981BysHq3GQ5u.png

  1. 可以透過 =REPT("[",5-(LEN(B2)-LEN(SUBSTITUTE(B2,"[","")))) &SUBSTITUTE(B2,"]","") 先整理資料。
  2. 再透過資料剖析，將資料分割到每個欄位，再取最後三個欄位即可。
小哈 iT邦新手 4 級 ‧ 2018-04-30 10:34:44 檢舉

感謝richardsuma大提供,我會多嘗試看看!!!有問題再回報上來

登入發表回應
0
iamaraymond
iT邦新手 5 級 ‧ 2018-05-06 13:49:40

VBA感覺這樣吧?

Sub test()
myStr = Split([B2], " ")
For i = 1 To 3
    Cells(2, 6 - i) = Replace(Replace(myStr(3 - i), "【", ""), "】", "")
Next
End Sub
登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22200 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙