2017年8月3日 星期四

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

新同學進入新的校園,註冊組長又面臨「分班」的問題了!如果你已經由某一個規則將學生予以排序完成,如何能以 S 型分班的概念來分班呢?Excel 如何幫助你?以下分別以『手動』和『自動』二種方式來說明。
參考下圖,左側是有數百名學生已排序後清單,右側是自動以 S 型來分班的結果。(本例沒有考慮男女生等因素)
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

【手動處理】
本例假設要分7班。
(1) 在分班欄位依序輸入「1,2,3,4,5,6,7,6,5,4,3,2,1」。
(2) 複製上述的儲存格範圍,往下各列貼上。
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)
(3) 進入自動篩選,篩選「1」項。
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN) Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)
(4) 複製篩選「1」的儲存格,貼至101班。
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)
(5) 重覆步驟(3)和(4),分別篩選「1,2,3,4,5,6,7」對應貼至「101,102,103,104,105,106,107」。
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

【公式自動處理】
如果你想試試以公式來自動分班,則可以輸入以下公式。
1. 產生分班欄位
儲存格C2:=IF(MOD(INT((ROW(1:1)-1)/$D$1),2),$D$1-MOD(ROW(1:1)-1,
$D$1),MOD(ROW(1:1)-1,$D$1)+1)
複製儲存格C2,往下各列貼上。
(1) MOD(INT((ROW(1:1)-1)/$D$1),2)
(2) $D$1-MOD(ROW(1:1)-1,$D$1)
(3) MOD(ROW(1:1)-1,$D$1)+1
第(1)式、第(2)式、第(3)式產生的結果如下圖:
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)
(4) IF(第(1)式,第(2),第(3))
當第(1)式傳回『1』時,相當於邏輯 TRUE,會顯示第(2)式的結果。
當第(1)式傳回『0』時,相當於邏輯 FALSE,會顯示第(3)式的結果。

2. 產生分班結果
先選取C欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分班。
儲存格F2:{=OFFSET($B$1,SMALL(IF(分班=COLUMN(A:A),ROW(分班),),
ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A)))-1,0)}
這是陣列公式,輸入完成,要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格F2,貼至儲存格F2:L41。
(1) IF(分班=COLUMN(A:A),ROW(分班),)
在陣列公式中,判斷分班的儲存格陣列中和 COLUMN(A:A) 相同者,傳回其列號。COLUMN(A:A)=1,向右複製後,得到:COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。
(2) SMALL(第(1)式,ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A))
利用 SMALL 函數,由小到大依序取出不為 0 的列號。
COUNTIF(分班,"<>"&COLUMN(A:A):計算在分班陣列中,不為「1」的個數。
(3) OFFSET($B$1,第(2)式-1,0)
最後,透過 OFFSET 函數,代入第(2)式的傳回值,得到對應的儲存格內容。
3.使用公式的好處是:不管要分幾班都可以適用。
Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

沒有留言:

張貼留言

檢視其他文章

好康東東