贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

新同學進入新的校園,註冊組長又面臨「分班」的問題了!如果你已經由某一個規則將學生予以排序完成,如何能以 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)

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()


留言列表 (1)

發表留言
  • 訪客
  • 老師您好, 請問如何將一個EXCEL工作表經由分割成多個WORKSHEET?
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼