2012年1月11日 星期三

Excel-模擬資料剖析

在網路上發現有人用公式來處理資料部析的方式不錯,很有創意,可以來介紹一下。參考下圖,在A欄中有一些字串,其中的文字以「/」做為分隔,現在希望將每段分隔的文字,分別放在右側的儲存格中,該如何處理?

假設字串中不含「*」字元。

儲存格B2:=SUBSTITUTE(MID(SUBSTITUTE($A2,"/",REPT("*",30)),30*(COLUMN(A2)-1)+1,30),"*","")

複製儲存格B2,貼至儲存格B2:D5。

公式中的「30」是假設在A欄中的字串不會超過30個字元。

REPT("*",30):產生一個30個「*」的字串。

SUBSTITUTE($A2,"/",REPT("*",30)):將「/」以前述之30個「*」字串取代。

以ABC/DEF/OP字串為例,會產生:

「ABC******************************DEF******************************OPQ」字串。

30*(COLUMN(A2)-1)+1:在B欄的結果為1,在C欄的結果為31,在D欄的結果為61,…

MID(SUBSTITUTE($A2,"/",REPT("*",30)),30*(COLUMN(A2)-1)+1,30):在上式的運算結果做為取字串的起點,每次取30個字。

如果在B欄,則結果為「ABC******************************」。(由第1個字取30個字)

如果在C欄,則結果為「***DEF***************************」。(由第31個字取30個字)

如果在D欄,則結果為「******DEF」。(由第61個字取30個字,字元數不足30字)

最後利用 SUBSTITUTE 函數,將上述字串中的「*」全部去除,即可得想要的字串。

沒有留言:

張貼留言

檢視其他文章

好康東東