Excel單欄資料切割成多欄
|
3001 |
Excel單欄資料切割成多欄 |
習近平：「錦子老師您好，小弟我目前遇到一個問題，想將一整欄(B欄)約1萬多筆資料，依條件(A欄的值=on)分至D欄、E欄.....等，預計完成結果如下：
錦子老師：「其實這比較適合用VBA來處理，但若不會VBA的話，用函數公式的作法如下：」
步驟1：點取C2儲存格輸入「1」。
步驟2：點取C3儲存格輸入公式：
=IF(A3<>A2,C2+1,C2)
再將公式複製到C4:C61儲存格，這是要安排其在那一欄用(目前以60筆記錄作說明)。
步驟3：在D1:H1儲存格輸入「1、3、5、7、9」，可依視需求擴大範圍。
步驟4：點取D2儲存格輸入公式：
=IFERROR(INDEX($B$1:$B$61,SUMPRODUCT(LARGE(($C$2:$C$61=D$1)*ROW($B$2:$B$61),COUNTIF($C$2:$C$61,D$1)+2-ROW()))),"")
再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入，再將公式複製到D2:H20儲存格(這將來要視筆數擴大範圍，但若範圍太大會造成記憶體不足，運算要花很長時間，要自行測試可承受範圍。
【公式說明】
=IFERROR(INDEX($B$1:$B$61,SUMPRODUCT(LARGE(($C$2:$C$61=D$1)*ROW($B$2:$B$61),COUNTIF($C$2:$C$61,D$1)+2-ROW()))),"")
公式1：COUNTIF($C$2:$C$61,D$1)
統計C2:C61儲存格中與D1儲存格內容相同的儲存格數量。
公式2：LARGE(($C$2:$C$61=D$1)*ROW($B$2:$B$61),公式1+2-ROW())
傳回C2:C61儲存格中與D1儲存格內容相同的列號第N(公式1傳回的結果加2減目前列號)大值，這是為了傳回由列號小排到列號大的內容。
公式3：SUMPRODUCT(公式2)
傳回公式2的合計。
公式4：INDEX($B$1:$B$61,公式3)
傳回B1:B61儲存格範圍第N(公式3計算結果)列儲存格內容。
公式5：IFERROR(公式4,"")
如果公式4傳回錯誤值的話，不填入資料，否則填入公式4結果。
以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~
更多相關影片教學：請點我
更多相關文章：請點我