錦子老師

2592	EXCEL分組排入各組

李美熹：「錦子老師，我帶一個班共有52位學員分成7組，多餘學員排至最後一組，抽籤分出小隊長及小隊長的朋友排入該隊，左方為拆解說明(A~D欄)，再來是實際編排方式(F~H欄)，右方希望能產出結果，依學員號順序將未排入的學員陸續分組(J~K欄)，最後如結果(N~P欄)。」

錦子老師：「這三個問題都可以使用RANDARRAY函數解決。」

1、實際編排方式

點取F2儲存格輸入公式：=A2

再將公式複製到F3:F53儲存格，填入每個學員編號。

點取G2儲存格輸入公式：=B2

再將公式複製到F3:F53儲存格，填入每個學員姓名。

點取H2儲存格輸入公式：

=IF(C2<>"",C2,IF(D2="","",D2))

再將公式複製到H3:H53儲存格，填入己編排的學員分組編號。

2、依學號順序未編排學員編排

首先在R欄輸入各個分組編號。

點取S2儲存格輸入公式：

=COUNTIF($H$2:$H$53,R2)

再將公式複製到S3:S53儲存格，統計每個分組編號的個數。

點取J2儲存格輸入公式：=A2

再將公式複製到F3:F53儲存格，填入每個學員編號。

點取K2儲存格輸入公式：=B2

再將公式複製到F3:F53儲存格，填入每個學員姓名。

點取H2儲存格輸入公式：

=IF(H2<>"",H2,SUMPRODUCT(SMALL(($S$2:$S$53=0)*($R$2:$R$53),SUMPRODUCT((H$2:H2="")*1)+COUNT($H$2:$H$53))))

 

再將公式複製到H3:H53儲存格，填入己編排的學員分組編號。

3、依分組編號排列

點取P2儲存格輸入公式：=R2

再將公式複製到P3:P53儲存格，填入每個學員編號。

點取O2儲存格輸入公式：

=VLOOKUP(R2,IF({1,0},$L$2:$L$53,$K$2:$K$53),2,0)

再將公式複製到O3:O53儲存格，填入每個學員姓名。

點取N2儲存格輸入公式：

=VLOOKUP(R2,IF({1,0},$L$2:$L$53,$J$2:$J$53),2,0)

再將公式複製到N3:N53儲存格，填入己編排的學員分組編號。

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

部落格相關範例

2020.04.30	982資料欄位大分解-IFERROR、IF、FIND、MID函數
2020.04.29	981資料欄位大合併-IF函數
2020.04.28	988MSGBOX函數
2020.04.27	990傳回有關STUDENT’S Y式檢定之機率值-TTEST函數
2020.04.26	989廠商型號資料交叉統計
2020.04.25	987Excel正負值不同色彩顯示
2020.04.24	2591傳回隨機數字陣列-RANDARRAY
2020.04.24	985EXCEL輸入分號問題
2020.04.23	2590Excel中的#SPILL!(#溢位!)錯誤
2020.04.23	986數值重排
2020.04.22	2589Excel如何將只出現一次的資料擷取出來-UNIQUE函數
2020.04.22	984最大/最小值統計-SUMPRODUCT、COUNTIF函數
2020.04.21	2589Excel如何將重複資料剔除-UNIQUE函數
2020.04.21	《姊姊》
2020.04.21	982公式參照運算-OFFSET函數
2020.04.20	2588Excel用函數雙條件篩選資料
2020.04.20	980皮耳森績差相關係數-PEARSON函數
2020.04.19	2587Excel用函數篩選資料
2020.04.19	979資料剖析問題-IF、FIND函數組合應用
2020.04.18	2583在標題列產生1到12月動態清單及5列12欄變數-SEQUENCE