|
制作完成的課程表如圖： 知識(shí)點(diǎn)：利用Excel數(shù)據(jù)關(guān)聯(lián)和規(guī)則公式完成指定數(shù)據(jù)排序 應(yīng)用環(huán)境：學(xué)校課程表編排、員工任務(wù)、值班安排等 每個(gè)學(xué)期，學(xué)校教務(wù)處教師最頭疼的一件工作就是編制課程表。一般學(xué)校的課程表至少包括給領(lǐng)導(dǎo)的全?？傉n程表、學(xué)生的班級(jí)課程表和教師用的課程表三種。三種課程表數(shù)據(jù)密切相關(guān)，修改任何一張課程表都將影響到另兩張表格，因此編輯時(shí)很難兼顧。為此，作者利用Excel 2007輕松解決了這個(gè)問題，制作過程如下。 1.創(chuàng)建工作表 打開Excel 2007，創(chuàng)建七個(gè)工作表，分別重命名為：教師與班級(jí)、教師安排、總課程表、教師課程總表、班級(jí)課程總表、教師課程表打印、班級(jí)課程表打?。ㄈ鐖D） “教師與班級(jí)”工作表是學(xué)校的教師名單和班級(jí)名單（如圖） “教師安排”工作表是各班級(jí)的任課教師（如圖） 2總課程表編制 切換到“總課程表”工作表設(shè)計(jì)總課程表格，表中包括全部班級(jí)的課程安排和每節(jié)課的教師。總課程表外觀（如圖） 選中B4，單擊“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)有效性”圖標(biāo)，在“數(shù)據(jù)有效性”窗口的允許下拉列表中選擇“序列”，輸入來源為“=教師安排!$A$3:$A$17”(不含引號(hào))，確定完成設(shè)置。在B5輸入公式=IFERROR(VLOOKUP(B4，教師安排!$A:$S，ROW(B4)/2，F(xiàn)ALSE)，“”)，并設(shè)置填充色為淺藍(lán)作為與學(xué)科行的區(qū)分。然后選中B4:B5進(jìn)行復(fù)制，再選中B4:BD39區(qū)域進(jìn)行粘貼即可。 現(xiàn)在選中B4單擊下拉按鈕選擇學(xué)科，下面的B5單元格就會(huì)自動(dòng)顯示上課的教師名，其他單元格也是一樣。如此一來安排課程就簡(jiǎn)單多了吧，只要用鼠標(biāo)單擊選擇即可。 3.總課程表限制提醒 編制總課程表時(shí)總有各種附加條件限制，比如：一個(gè)教師不能同時(shí)上兩班的同一節(jié)課，操場(chǎng)太小全校只能有兩班同時(shí)上體育課，電腦室只有1間不能有兩班同時(shí)上電腦課等等。要在排課中兼顧這些要求顯然不容易。對(duì)此可設(shè)置條件格式，讓它在違反限制時(shí)自動(dòng)變色提示，事情就簡(jiǎn)單多了。 選中B4:BD39，在“開始”選項(xiàng)卡中單擊“條件格式”選擇“新建規(guī)則”，在“新建規(guī)則”窗口中選擇規(guī)格類型為“使用公式確定要設(shè)置格式的單元格”，并輸入公式=AND(COUNTIF(B:B，B4)>1，MOD(ROW()，2)=1)(圖3)。再單擊“格式”按鈕，在彈出窗口中設(shè)置字體顏色為紅色。確定后，當(dāng)同一節(jié)課中有兩班出現(xiàn)同一老師同時(shí)，兩班中這位老師的名字都會(huì)變成紅色，你可以及時(shí)決定看要更換哪班的課程。 同樣再選中B4:BD39設(shè)置“條件格式”，但輸入的公式改成=AND(COUNTIF(B:B，B4)>2，C1=“體育”)，文字顏色改成綠色。即可在同時(shí)上體育課超過2班時(shí)變成綠色。電腦課的設(shè)置公式則是=AND(COUNTIF(B:B，B4)>1，C1=“電腦”)顏色改成藍(lán)色。如果你還有課時(shí)等其他限制要求只要像這樣繼續(xù)疊加設(shè)置條件格式即可。 注：必須從B4拖動(dòng)到BD39選中B4:AJBD39，或先選中B4再按住Shift鍵單擊BD39進(jìn)行選中才行。若你從BD39拖動(dòng)到B4選中，雖然選中區(qū)域相同但條件格式的公式就不同了，得把公式中的B全部改成BD，B4改成BD39。 4.分離教師、班級(jí)課程 再來就是要從總課程表中分離出班級(jí)、教師的課程了。切換到“班級(jí)課程總表”（如圖）建立好表格結(jié)構(gòu)。 在C2輸入公式=OFFSET(總課程表!B$2,ROW()*2-2,) 選中C2復(fù)制再選中C2:BE19進(jìn)行粘貼，即可看到各班的課程總表。 切換到“教師課程總表”工作表中同樣設(shè)計(jì)好表格的行列標(biāo)題（如圖） 在A2單元格輸入公式：=教師課程表打印!K2 在B2單元格輸入公式：=VLOOKUP(教師課程表打印!$K$2,教師與班級(jí)!$A$1:$B$100,2) 在C2輸入公式：=IFERROR(INDEX(總課程表!$A:$A,MATCH($B$2,總課程表!B:B,0)-1),'') 選中C2復(fù)制再選中C2:BE2進(jìn)行粘貼，即可自動(dòng)顯示教師“課程表打??！K2”單元格老師的周一到周五的課程了。 5.制作教師課程表和班級(jí)課程表 首先建立課程表結(jié)構(gòu)（如圖），教師課程表同班級(jí)課程表的結(jié)構(gòu)是一樣的。 切換到教師課程表打印工作表，在C3單元格輸入公式：=VLOOKUP(教師課程表打印!$K$2,教師課程總表!$A$1:$BE$2,3)，公式的使用方法在前兩篇文章中已經(jīng)作過介紹，不在贅述。 請(qǐng)參閱： 小升初畢業(yè)生登記表，全自動(dòng)填充，包括照片 在需要排出課程的單元格輸入公式：=VLOOKUP(教師課程表打印!$K$2,教師課程總表!$A$1:$BE$2,n)，只需要給n賦值就可以了！ 類似的切換到班級(jí)課程表打印工作表，在C3單元格輸入公式：=VLOOKUP(班級(jí)課程表打印!$K$2,班級(jí)課程總表!$A$2:$BE$19,n)，必須根據(jù)需要給n賦值，確定該單元格的課程。其他有課程的單元格公式類似。 打印教師課程表和班級(jí)課程表前先要設(shè)置打印區(qū)域，在這兩個(gè)工作表中選擇A1：I17區(qū)域，在頁面布局→打印區(qū)域中設(shè)置打印區(qū)域。A1：I17以外的區(qū)域在打印時(shí)不打印。 到此基本算完成制作過程，如果要打印教師課程表，就切換到教師課程表打印工作表，在K2單元格輸入該教師在教師與班級(jí)工作表中的序號(hào)就可以打印出該教師的課程表了。班級(jí)課程表的打印方法類似。 6.批量打印教師課程表 輸入一個(gè)序號(hào)打印出一個(gè)教師的課程表，有多少名教師需要操作多少次，麻煩。為解決這個(gè)問題，請(qǐng)?jiān)贓XCEL開發(fā)工具中插入命令按鍵，并把下列代碼粘貼到命令按鈕的單擊事件中： Private Sub CommandButton1_Click() For i = Range('k6') To Range('k7') Range('k2') = i ActiveSheet.PrintOut Next End Sub 保存關(guān)閉，返回到EXCEL界面。現(xiàn)在就可以批量打印了。 批量打印時(shí)，先輸入開始序號(hào)，再輸入結(jié)束序號(hào)，然后點(diǎn)擊批量打印按鈕，就按照你設(shè)置的序號(hào)開始打印。 最后，由于本人水平有限，不妥或需要改進(jìn)之處，敬請(qǐng)同仁批評(píng)指正，如需已經(jīng)制作完成的工作表模板，請(qǐng)?jiān)谙旅媪粞浴?/span> 人生的路，總有幾道溝坎；生活的味，總有幾分苦澀。有些事，無能為力，就順其自然；有些人，不能強(qiáng)求，就一笑了之；有些路，躲避不開，就義無反顧。沒有陽光，學(xué)會(huì)享受風(fēng)雨的清涼；沒有鮮花，學(xué)會(huì)感受泥土的芬芳。想要的多了，是負(fù)累；奢望少了，會(huì)滿意。微笑的眼睛，才能看見美麗的風(fēng)景；簡(jiǎn)單心境，才能擁有快樂心情。 |
|
|
