錦子老師

習近平：「錦子老師您好，小弟我目前遇到一個問題，想將一整欄(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結果。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

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

更多相關文章：請點我