2019/09/04

學校社團抽籤報名巨集EXCEL檔案思路

話說為了盡可能讓學生都能報名上想要的社團,
也為了讓訓育組可以比較輕鬆的製作繳費單跟對帳
所以弄了一個可以依據人數上限抽籤的EXCEL巨集檔案
不過這個檔案年代有點久了,巨集當初怎麼做的都有點忘了
所以還是簡單紀錄一下,以防每年報名人數不停增長得修改上限卻根本沒辦法處理


1.首先是在社團資料裡面設定好每個社團可以報名的人數上限

2.報名名冊為了讓人方便,一次可以報名5個社團,但是這樣GOOGLE表單會把這些資料分散為5個欄位,在統計上會很困難,所以到了抽籤的表單,用土法煉鋼的方式,引用這些資料,把5個欄位合併為一欄,大約每個欄位是抓1000筆左右,先前社團人數沒那麼多的時候大概是抓700筆,所以超過700筆資料就會有漏。

3.合併為一欄的同時,給每筆資料一個亂數值並產生一組隨時會變動的臨時編號

4.利用巨集將 社團 臨時編號 班級座號這三個欄的值貼到隔壁,固定住臨時編號,並利用排序的功能把空的資料排除掉

5.後面的欄位則是根據前面設定的社團報名人數上限,
N欄位判斷這筆資料是否為目標社團,是的話就引入臨時編號
O欄位則取得臨時編號的RANK(名次值)
P欄位根據RANK值跟社團的報名人數上限比大小,決定是否錄取
Q欄位則是未錄取的人,候補的順序
 

 

6.接下來的欄位則是帶出該筆資料應該繳費的金額
首先利用巨集將原來為公式的社團代碼FV跟班級座號FW貼成值到FY跟FZ,
接著按照班級座號進行排序,後面欄位GA跟GB撈出社團代碼代表的名稱及金額




7.再來的欄位是依據FY欄位資料資判斷每個學生報名了幾個社團,然後利用公式重新把每個學生所有報名的社團名稱組合在一個欄位,以便發下繳費單讓家長核對,同時並計算每個學生應該繳交的金額,最後再將這些繳費資料利用巨集將值貼在繳費資料中,就可以進行套印繳費單的作業了。