close

3907

Excel資料如何分為上下列

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

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

image

錦子老師田弟,我的作法如下:

點取D1儲存格輸入公式:「=A1」。

點取D2儲存格輸入公式:

=IFERROR(IF(COUNTIF(D$1:$D1,D1)<((LEN(VLOOKUP(D1,$A$1:$B$15,2,0))+1) /2),D1,INDEX($A$1:$A$15,MATCH(D1,$A$1:$A$15,0)+1)),"")」。

再將公式複製到D3:D16儲存格。

image

VLOOKUP(D1,$A$1:$B$15,2,0) VLOOKUP函數找尋D欄儲存格內容在A欄那一列並傳回同列B欄儲存格內容。

LEN(VLOOKUP(D1,$A$1:$B$15,2,0))+1 LEN函數將VLOOKUP函數傳回的值加1後除以2算出共有幾組數值。

COUNTIF(D$1:$D1,D1) COUNTIF函數算出D欄儲存格內容在A欄共用幾筆。

如果COUNTIF函數傳回的筆數小於LEN函數傳回的組數,則填入上一儲存格內容,否則用MATCH函數傳回上一儲存格在A欄位置(MATCH(D1,$A$1:$A$15,0)),再用INDEX函數抓取其下一儲存格內容(INDEX($A$1:$A$15,MATCH(D1,$A$1:$A$15,0)+1)))

點取E2儲存格輸入公式:

=IFERROR(MID(VLOOKUP(D1,$A$1:$B$15,2,0),(COUNTIF($D$1:D1,D1)-1)*2+1,1),"")」。

再將公式複製到E2:E16儲存格。

image

VLOOKUP(D1,$A$1:$B$15,2,0) VLOOKUP函數找尋D欄儲存格內容對應的A欄同列B欄資料。

COUNTIF($D$1:D1,D1) 統計目前D欄儲存格內容在D1儲存格到目前D欄儲存格中含有的筆數。

MID(VLOOKUP(D1,$A$1:$B$15,2,0),(COUNTIF($D$1:D1,D1)-1)*2+1,1) MID函數抓取女VLOOKUP函數傳回的內容中,將COUNTIF函數傳回的數減1後乘以2並加1,算出要從第幾個字元抓取1個字元。

幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()