2010年6月5日 星期六

Excel-利用公式來剖析資料

在Excel中要將A欄中的資料,以分隔符號「/」將資料項取出,你通常使用「資料剖析」工具,方便好用。如果您想要用公式將資料自動取出該如何執行呢?如下圖:

 

取出第一項的公式:

儲存格B2:=MID(A2,1,SEARCH("/",A2,1)-1)

利用 SEARCH 函數找到第一個 / 的位置,透過 MID 函數取出內容。

 

取出第二項以後的公式

儲存格C2:=MID($A2,FIND("*",SUBSTITUTE($A2,"/","*",B$1),1)+1,FIND("*",SUBSTITUTE($A2,"/","*",C$1),1)-FIND("*",SUBSTITUTE($A2,"/","*",B$1),1)-1)

複製儲存格C2至儲存格C2:E2。

其中,SUBSTITUTE($A2,"/","*",C$1) 公式為指定第 2 個 /,替換為 *。

然後,透過 FIND("*",SUBSTITUTE($A2,"/","*",C$1),1) 尋找*位於字串的位置。

而,SUBSTITUTE($A2,"/","*",B$1)公式為指定第 1 個 /,替換為 *。

然後,透過 FIND("*",SUBSTITUTE($A2,"/","*",B$1),1) 尋找 * 位於字串的位置。

以上兩個位置相減,即是第二項內容的字串長度。

再透過 MID 函數將第二項內容取出。餘各項內容的儲存格公式,依此類推…。

 

取出最後一項的公式:

儲存格F2:=RIGHT($A2,LEN($A2)-FIND("*",SUBSTITUTE($A2,"/","*",E$1),1))

最後一項的內容,則透過 FIND("*",SUBSTITUTE($A2,"/","*",E$1),1) 找到最後一個 / 的位置。

然後,使用 RIGHT 函數由字串右邊取出內容。

 

以上的做法要注意:所有資料內容不能含有 * 字元,所以這個替代字元要視你的資料內容而變。而如果想要剖析的分隔字元也不一定為 / 字元,你也可以將 / 字元設定於一個儲存格中,在公式中直接關聯這個儲存格,就可以方便的使用在其他要剖析的資料中。

1 則留言:

好康東東