3908Excel資料如何分為上下列-二位數以上 @ 錦子老師

3908

Excel資料如何分為上下列-二位數以上

李田：錦子老師好，如圖，想請問Excel裡面有沒有功能不用寫程式可以做到把格子裡面的資料分列?

每個禮拜會有上萬筆資料......救救小弟。

如果B欄有包含10以上的數字，有什麼方式可以不要取到","號嗎? 謝謝~

錦子老師：田弟，我的作法如下：

框選B1:B4儲存格，按CTRL+C鍵複製。

點取C1儲存格，按CTRL+V鍵貼上，這是為了保留原始資料。

框選B1:B4儲存格，點取「資料 > 資料剖析」圖示。

在【資料剖析精靈-步驟3之1】對話方塊，點取「分隔符號」選項鈕。

點取「下一步」鈕。

在【資料剖析精靈-步驟3之2】對話方塊，點取「逗點」核取方塊。

點取「完成」鈕，結果如下圖。

點取I1儲存格輸入公式：「=A1」。

點取I2儲存格輸入公式：

「=IFERROR(IF(COUNTIF($I$1:I1,I1)<COUNT(OFFSET($A$1,MATCH(I1,$A$1:$A$4,0)-1,2,1,5)),I1,INDEX($A$1:$A$4,MATCH(I1,$A$1:$A$4,0)+1)),"")」。

再將公式複製到I3:I16儲存格。

MATCH(I1,$A$1:$A$4,0) 用MATCH函數傳回I欄儲存格內容在日A欄第幾列。

OFFSET($A$1,MATCH(I1,$A$1:$A$4,0)-1,2,1,5) 用OFFSET函數從日A1儲存格移動MATCH函數傳回列值減1列，向右移動2欄，抓取1列5欄的儲存格資料。

COUNT(OFFSET($A$1,MATCH(I1,$A$1:$A$4,0)-1,2,1,5)) 用COUNT統計OFFSET框選的範圍中非空白儲存格數量。

COUNTIF($I$1:I1,I1) 用COUNTIF函數統計目前列I欄儲存格內容在I1到目前列I欄儲存格範圍中的筆數。

用IF函數判斷如果COUNTIF函數傳回的值小於COUNT函數傳回的值，則填入目前列I欄儲存格內容，否則用MATCH函數傳回上一儲存格在A欄位置(MATCH(I1,$A$1:$A$4, 0))，再用INDEX函數抓取其下一儲存格內容(INDEX($A$1:$A$4,MATCH(I1,$A$1:$A$4, 0)+1)))。