參考下圖,在 Excel 中,如果要將儲存格內容「877/13/3214/6481/643/4486」的文字,依其分隔符號『/』取出『877、13、3214、6481、643、4486』,該如何處理?注意其分隔符號之間的內容的文字長度並不一致。

通常,我們會使用 Excel 中的「資料部析」工具來處理,手動操作也很方便,但是如果想要以公式來處理,或像下圖中要把取出的資料放在同一欄中。(資料剖析工具只能將資料部析結果放在同一列中)

Excel-手動資料剖析(MID,ROW,COLUMN,陣列公式)

 

【公式設計與解析】

參考上圖,假設:本例的資料中有五個分隔符號『/』,將資料分成六組。

 

1. 資料剖析結果置於同一列

計算分隔符號『/』的位置,儲存格C7:

{=SMALL(IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999),COLUMN(A:A))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

複製儲存格C7,貼至儲存格C7:G7。

(1) MID($C2,ROW($1:$30),1)

假設儲存格中的文字長度不超過 30 個字,在陣列公式中 ROW(1:30) 代表{1,2,3, ... 29, ,30} 陣列。

MID($C2,ROW($1:$30),1) 可以取出儲存格C2中第 1, 2, 3, ..., 29, 30 個字。

(2) IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999)

MID($C2,ROW($1:$30),1)="/" 用以判斷第(1)式取出的每個字是否為分隔符號『/』。

如果判斷結果為真則傳回所有位置(1~30),否則就傳回『999』,該數只是一個很大的數,且比儲存格中的文字長度還要大。

(3) SMALL(IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999),COLUMN(A:A))

利用 SMALL 函數取出第 1, 2, 3, 4, 5 個分隔符號(/)的位置。其中 COLUMN(A:A)=1,當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(E:E)=5。

(4) 取出第1組文字

儲存格C11:=MID(C2,1,C7-1)

(5) 取出第2~5組文字

儲存格D11:=MID($C2,C7+1,D7-C7-1)

複製儲存格D11,貼至儲存格D11:G11。

(6) 取出第6組文字(最後一組)

儲存格H11:=MID($C2,G7+1,99)

其中參數『99』,只是一個很大的數字,只要比儲存格內文字總數大即可。

 

2. 資料剖析結果置於同一欄

計算分隔符號『/』的位置,儲存格D16:

{=SMALL(IF(MID($C$2,ROW($1:$30),1)="/",ROW($1:$30),999),ROW(1:1))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

複製儲存格D16,貼至儲存格D16:D20。

儲存格G16:=MID(C$2,1,D16-1)

儲存格G17:=MID(C$2,D16+1,D17-D16-1)

複製儲存格G17,貼至儲存格G17:G20。

儲存格G21:=MID(C$2,D20+1,99)

公式的原理和「1. 資料剖析結果置於同一列」完全相同,其中 ROW(1:1)=1,當公式向右複製時,ROW(1:1)=1→ROW(2:2)=2→ ... →COLUMN(5:5)=5。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()