2016年7月25日 星期一

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

參考下圖,在 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。

沒有留言:

張貼留言

好康東東