Excel 函數(shù)公式在中小學(xué)排課中的應(yīng)用研究

函數(shù)公式在中小學(xué)排課中的應(yīng)用研究中小教處工中常重的項(xiàng)任便排課現(xiàn)有許這樣排軟件但多需付，使上常不。統(tǒng)的工課工量，若個(gè)節(jié)出問(wèn)，就牽其他節(jié)有時(shí)至前功盡。此，嘗運(yùn)表進(jìn)行助課，實(shí)中收到了錯(cuò)效果下就具過(guò)作系的明，望大家所助。一、備作。軟硬準(zhǔn)。EXCEL（或以）電表，，正常啟以軟件電設(shè)備了解用數(shù)與式用法例如（假判）（據(jù)引用）或者、并且列找條件計(jì)（行交引用求和）、SUMIF（件和）函；學(xué)會(huì)數(shù)有性和件式的置法；解對(duì)應(yīng)和對(duì)應(yīng)的用等掌這些本法在課程中會(huì)揮意不的效。打子表，建工簿添加作，并次名為1信錄”、設(shè)置、課程表、檢”、“課”、“教師課表”。二、作的具設(shè)。當(dāng)工表名以，逐一行置，于作表數(shù)會(huì)相引用所在設(shè)過(guò)中一要到準(zhǔn)、實(shí)，據(jù)式一。（一“信息入”作。信錄入作是所工表的礎(chǔ)其數(shù)的靠、楚后面排、檢、取將牽發(fā)而全，所在置前進(jìn)1

全面調(diào)，以證入模統(tǒng)，修方，為后循環(huán)用供保證以表為（定使班教師，設(shè)了學(xué)校、上課數(shù)學(xué)期各標(biāo)準(zhǔn)時(shí)總課、師、設(shè)學(xué)科班及班任各科時(shí)。見(jiàn)1“土色區(qū)域入據(jù)，以區(qū)域置數(shù)據(jù)效”。“是上”列設(shè)：選格，點(diǎn)擊據(jù)菜單，開(kāi)有效性鈕數(shù)據(jù)源“是否其他。置完后下復(fù)至C204.后根需依次置否上?！鞍嗳瘟械闹眠x單元格，數(shù)據(jù)來(lái)源=$B$5:$B$204，即教列數(shù)據(jù)然向下制。后選設(shè)置各班主任。以下元輸入式“L2”單元格輸入公：=COUNTIF(C5:C204,"")此處運(yùn)用條件統(tǒng)公，用統(tǒng)中含“”的個(gè)數(shù)即為上課數(shù)?！癆D2單元輸公式：用于計(jì)中數(shù)的，即總時(shí)。域設(shè)置條格式。選擇開(kāi)”菜，開(kāi)“件式”鈕在新規(guī)中選“只包以下容單元設(shè)格式選，設(shè)單格值于“時(shí)背景色示為黃色最后確定覽，此時(shí)是數(shù)據(jù)等34的背為色。樣設(shè)置于明快的握數(shù)輸是否確保護(hù)鎖工作。鎖定活單元：選工表在單格式中擇護(hù)，“鎖”“隱”項(xiàng)上鉤確定然按住“CTRL”，選擇2

活動(dòng)元，即格的“色區(qū)域在元格式護(hù)中消鎖定和“藏前的，后確。保護(hù)作：在審”菜，開(kāi)“護(hù)作表按，設(shè)密碼最確定此，在作的活單格中輸數(shù)據(jù)其單元處鎖定態(tài)（二“任課置”作。課設(shè)工表用計(jì)教師人“標(biāo)課”，對(duì)師個(gè)人的作進(jìn)行劃作好課況的計(jì)即就表中的計(jì)課時(shí)”為排總表供服。土黃”域?yàn)閯?dòng)元格可由輸信，詳：2“藍(lán)色區(qū)域入式?！癆5:C203”域：A5元格入公式信錄入'!A5)，其單元格復(fù)粘貼。此式用于引信息錄入工作表的據(jù)，可于將個(gè)元格據(jù)并為個(gè)元格“D5:D203”是否班主任區(qū)：在5單格輸公式：信錄入'信錄入息錄'信息錄，公式中運(yùn)的逆向查功能，在息錄入工表查找主老師對(duì)的班，出現(xiàn)誤用和“ISERROR”函數(shù)進(jìn)真判斷若“假顯為空，則顯為對(duì)的級(jí)。意式中絕應(yīng)用號(hào)”可忽。最后5格復(fù)制此式即可?！癊5:E203”班任折算課區(qū)域：在5單元輸入公式3

=IF(OR(B5="",D5=""),"0",$E$4)此式中“指“者”的意，意義如滿B5=”””中的任何個(gè)條件，顯示為”，否則顯為“單元格的容。以下元格復(fù)制可?！癏5:H203”標(biāo)準(zhǔn)課時(shí)區(qū)域：H單元輸入式=IF(B5="","",IF(C5="是",($E$204+$G$204+'1息錄'息錄入!$L$2-$E5-$G5,""))，公式用于算標(biāo)準(zhǔn)課，計(jì)算方為總課時(shí)+主任課兼職課，然除上課數(shù)最后去主任課時(shí)兼課時(shí)其單元復(fù)即可“IF:I203”計(jì)課時(shí)域：在5元格入公式：=IF($B5="","",SUMIF($M$5:$N$54,$B5,$N$5:$N$54)+SUMIF($O$5:$P$54,$B5,$P$5:$P$54)+SUMIF($Q$5:$R$54,$B5,$R$5:$R$54)+SUMIF($S$5:$T$54,$B5,$T$5:$T$54)+SUMIF($U$5:$V$54,$B5,$V$5:$V$54)+SUMIF($W$5:$X$54,$B5,$X$5:$X$54)+SUMIF($Y$5:$Z$54,$B5,$Z$5:$Z$54)+SU$AH$54,$B5,$AH$5:$AH$54)+SUMIF($AI$5:$AJ$54,$B5,$AJ$5:$AJ$54)+$5:$AR$54,$B5,$AR$5:$AR$54))，此公式使用“SUMIF條件求和式，于計(jì)教任不同目課時(shí)總。其單格復(fù)即?！啊辈顓^(qū)：元格入式是"此公中使用的嵌套功能其意義為元格空時(shí)顯為，單格示“”，顯否則為空其單元復(fù)填充“K5:K203”標(biāo)碼區(qū)域：在5單元格輸入式：=IF(J5<-1,"",IF(J5>1,"","√"))，此用同上用判斷差”是否在-1和之，如是示為則顯示為。這種設(shè)便于及掌教師作是否到準(zhǔn)工量其余元復(fù)制充“”年域：?jiǎn)卧袢?CONCATENATE('1信錄入!I5)，接引用信錄入工作中的年級(jí)息?！癗P、T、、Z、、AD、、、AJ、AN、、AR”列時(shí)區(qū)域：在N5元格入錄'!$I$4:$AC$54,MATCH($L5,'1信錄入'!$I$4:$I$54,0),MATCH(M$3,'1信4

息錄入'!$I$4:$AC$4,0)))，義說(shuō)明，進(jìn)行真假斷，單元為空，顯為空否根INDEX函，引用信錄入表中列交叉單元的據(jù)，不年級(jí)同目的時(shí)。其單格復(fù)填。“M3:AQ3課程行輸：?jiǎn)卧斎胂浫?!$E$5:$F$24,2)，此公式運(yùn)用COLUMN()列號(hào)用公式，后根數(shù)引信錄入表中設(shè)置課。例單所列COLUMN()值然后除，所得減5結(jié)，后根VLOOKUP信錄入中號(hào)1的2列所應(yīng)的程。其他元格復(fù)制公式?！癆K2標(biāo)碼單元格入公=IF(I204=H204,"√×")，其義為，“準(zhǔn)課數(shù)等于計(jì)課時(shí)”顯示√”，否顯示為“。此式用快判斷程否全安，為排課提供方便使中只當(dāng)識(shí)碼示“”時(shí)才能進(jìn)入一環(huán)節(jié)的置，記置數(shù)有性。在F2單元打開(kāi)數(shù)據(jù)效性按鈕數(shù)據(jù)來(lái)源B3:B203置條格。選擇據(jù)B5:B203，在始菜單中開(kāi)條件格按鈕，選“只為包以內(nèi)容單格設(shè)格”，單格值，背色顯示為紅”。樣設(shè)置于速查教的具任安排護(hù)工表當(dāng)工表景和式入完后對(duì)部單格公進(jìn)鎖定以防他隨更改造排課混。方如：全整工作，單元格保護(hù)“定”“藏”打，確。后選活的單元，元格表中的兼工作”“教師列，取消格式護(hù)“鎖”“隱”的鉤確。最選審閱單點(diǎn)擊“護(hù)作表按，輸管密碼（三“總課表”作。課程是個(gè)排系的核。信息入任課置入完畢后即進(jìn)行課表的排首次用作量大以后會(huì)分方便只輕點(diǎn)標(biāo)對(duì)個(gè)內(nèi)進(jìn)行單理即完總課。體設(shè)置見(jiàn)35

3寫表頭輸公式（1）“課節(jié)”行中數(shù)字說(shuō)明：例如11，指星一第一節(jié)，25指星二五節(jié)以類推（2）“”元格：=CONCATENATE('1信錄入'（3）“L3”元格：=CONCATENATE('1息錄入'!O2)（4）“AG1”格，此處據(jù)來(lái)源為課表檢驗(yàn)工作表，式同。（5）“區(qū)域：?jiǎn)胃駥懝剑?IF(MOD(ROW(),2)=1,VLOOKUP((ROW()+1)/2-3,'1信息錄入'!$H$5:$I$54,2),"教")，公式ROW()用于引于前單元格在的行號(hào)，用于算前單格在行除后余數(shù)整個(gè)式，如余，則引用“信息錄”表中設(shè)的年級(jí)，否則示“教”其余元復(fù)制公進(jìn)行充（6）“教師”所在行區(qū)：在“單元格輸公式任課置!$L$3:$AR$54,MATCH($A7,'2課設(shè)置任置'!$L$3:$AR$3,0)))。此公中運(yùn)用公，用提取“任設(shè)置”工表中“七”交叉元的數(shù)，七年班星期一第節(jié)課的教。其余元復(fù)制公填充（7課時(shí)”列域：?jiǎn)卧褫?IF(OR($B7="",$A7="教師"信息錄入!$I$4:$AC$54,MATCH($A7,'1息錄信息入'!$I$4:$AC$4,0)))，此式中先6

進(jìn)行假斷，白A7=教師，則顯為空。否從信息錄入工表提取七和任意程交叉元的數(shù)，為計(jì)劃時(shí)。其單格復(fù)此式予填。另，表中B1為動(dòng)單元格可以點(diǎn)開(kāi)拉菜單隨選擇不同程。為活動(dòng)單格用于擇意教，文會(huì)到（8）“實(shí)際課時(shí)”列區(qū)：例如在元格入公式=COUNTIF(B7:AJ7,$B$1)用于計(jì)行課中課個(gè)。其B7:AJ7為相對(duì)用，即為動(dòng)區(qū)域絕對(duì)用請(qǐng)注意。他單格制填。B1、單元各課程元設(shè)置拉單樣。（1）在總課程表右側(cè)空處添加“程”列和教師”列利用函數(shù)用信息入工作中所有課”和“教”（B1元格設(shè)置據(jù)有效性數(shù)據(jù)來(lái)源剛才設(shè)置“課程列，景為色元設(shè)數(shù)據(jù)效，數(shù)來(lái)為“師列”背為藍(lán)。置完預(yù)效果（3）在各班課程行設(shè)置據(jù)有效性例單元，據(jù)來(lái)為“課列，背色“土色，其班和區(qū)復(fù)此單格置“注行信。在寫入：=IF(COUNTIF(B7:B106,$T$1)>=2,"重,COUNTIF(B7:B106,$T$1))意義說(shuō)明：判所在中任意教”數(shù)否于等2，如果有則顯示“”否則示計(jì)的數(shù)B6:AJ6”區(qū)域其他元格復(fù)制公式充“備”域?yàn)閹煼翊嬷噩F(xiàn)象行示。置條格。（1）選擇“B6:AJ6”，點(diǎn)條格式鈕只為含下內(nèi)容的單元設(shè)格式當(dāng)元格為重”，景設(shè)為紅色。（2選擇“B7:AJ106”點(diǎn)擊條件式按鈕，為包含單元格容設(shè)置景“綠”為包單格內(nèi)的置背景為藍(lán)”。（3）選擇“AL7:AM7”，設(shè)條件格式值為“唯”時(shí)，背色為粉色”以區(qū)域擇式刷應(yīng)此設(shè)?！翱偝獭毕驴仗幮赂揭?jiàn)4。表數(shù)為一工作“表檢”“是重”列供務(wù)。7

（1）“區(qū)域姓名列）在111輸入式信息入!B5)，下復(fù)制此式填充。（2）“B111:B310”區(qū)寫入公式在輸入=IF($A111="","",COUNTIF(B$7:B$106,$A111))，公式用C函數(shù)計(jì)師在節(jié)中的時(shí)。其區(qū)復(fù)制公填充（3）“AK111:AK310”區(qū)：在AK111”中寫入公式=IF(COUNTIF(B111:AJ111,">=2")>=1,"重,"")，明：本行數(shù)據(jù)存在大于2時(shí)顯示“重”，則顯示為，用于判教師是否在重課現(xiàn)。余復(fù)此式填?！翱偝獭庇铱仗幮赂揭?jiàn)5。表數(shù)為一工作“表檢”的“際時(shí)”提服務(wù)8

（1）“”域?qū)懭牍豪鏡7單元，=IF(MOD(ROW(),2)=1,VLOOKUP((ROW()+1)/2-3,'1信息錄入'!$H$5:$I$54,2),"教")，公式“總課程”第列。（2）“AS5:BM5”域?qū)懝豪鼳單格，信息錄'!$E$5:$F$24,2)，此公式用來(lái)引“信錄”工表的“程。（3）“AS7:BM106”區(qū)寫入式例如S7格，=COUNTIF($B7:$AJ7,AS$5)，公式來(lái)統(tǒng)計(jì)“課表”中一年級(jí)課程的數(shù)其他制充。護(hù)工表（1）全選工作表，設(shè)置元格格式在保護(hù)菜的“隱藏和“鎖定前打，后確。（2）選擇活動(dòng)單元格和域。例、級(jí)的課程行，設(shè)置元格式取“隱”“鎖”的鉤可（打開(kāi)審”菜下“保工表”鈕設(shè)置理碼。（四“課表驗(yàn)”作。“課表檢”為總課表的編排供服務(wù)。此工作表可以直觀的看個(gè)教是存在課象、天課時(shí)多、總時(shí)是否達(dá)計(jì)課時(shí)、校是存空課象最后動(dòng)斷排是成功。板6入公。（1）“A4:B203”區(qū)域：A4單格輸：9

信息錄入!A5)此公用于引用信息錄入”中的號(hào)其他制公式充引用師（2）“”區(qū)：先C4單元格輸：任設(shè)置'!$B$5:$I$203,8,0))，意義為先進(jìn)行斷格空時(shí)示白，則據(jù)列找，找“課置”區(qū)域8列對(duì)的據(jù)，計(jì)課時(shí)。以下元復(fù)制充（3）“D4:H203”區(qū)域“D4”：=IF(B4="","",COUNTIF('3總課表!$B$4:$H$106,$B4))意義統(tǒng)“總程”中域B4應(yīng)教師的數(shù)?！肮秸n程表!$I$4:$O$106,$B4))“F4”公式課程表!$P$4:$V$106,$B4))“G4”式程表“H4=IF(B4="","",COUNTIF('3總課程'!$AD$4:$AJ$106,$B4))以上式義相，余各復(fù)此公填。（）”區(qū)：”，列之。（5）“J4:J203”域，元格入：課程表'!$A$110:$AK$309,37,0)，意義根據(jù)列查公，找“總程”下“表”A110:AK306”區(qū)域第7其單元格制公式充此時(shí)以藏“課表”側(cè)附表（6√誤其義為先進(jìn)判斷，為空顯示為空且J空時(shí)，顯為“顯為“誤。此式運(yùn)用IF嵌套能，于判斷每教師排課否正確。下單元格制此公式填。（7）“M4:M203”區(qū)域：M4輸入VLOOKUP((ROW()-1)/3,'1信息入!$H$5:$I$54,2)，其意義為先引所單元的號(hào)，后根據(jù)查公式查信息入中對(duì)的級(jí)。下制填。（8）“O4:AH6”：“教”，“”元格入任課設(shè)置任設(shè)置'!$L$3:$L$54,0),MATCH(O$3,'2任課置“計(jì)課”行“O5”單格輸入息錄10

入'信息入'!$I$4:$I$54,0),MATCH(O$3,'1信息入“實(shí)課”行”元格總程表'課程表'!$AR$5:$AR$106,0),MATCH(O$3,'3總課表'!$AR$5:$BM$5,0)))。以上公式運(yùn)了多式套方，進(jìn)行斷假，格為時(shí)顯示，則根INDEX+MATCH公式找“課程表”側(cè)附表中列交單格的。復(fù)制公填充一單元，后同復(fù)三行單格式，充以下元。此可隱藏總程表的統(tǒng)表（9）“C204:K204區(qū)域C204:I204入SUM求和式。J204入：=COUNTIF(J4:J203,"重)，于中“重”個(gè)數(shù)K204輸：=COUNTIF(K4:K203,"錯(cuò)誤，用統(tǒng)中“誤”的個(gè)。7（10）單格，排標(biāo)識(shí)寫公式排成功,"失敗)，公式表示并”之，理解，同時(shí)足204=I204,J204=0,K204=0三個(gè)件，顯為排課功，否顯為“敗。置條格。（1）選擇4:J203，開(kāi)開(kāi)始菜中的“條格式”，置當(dāng)單元格容含“”，背顏為“色。（）K4:K204,打開(kāi)始菜單中“條件格”，設(shè)置單元格內(nèi)包“錯(cuò)”背景色“紅”11

（選擇5:O6，設(shè)置當(dāng)元格內(nèi)容“唯一”時(shí)背景為玫紅色，他“劃時(shí)”“際課”元格用式刷用一設(shè)置（選D4:H4，設(shè)條件格式當(dāng)單元格大于等時(shí)背景色為淡色”其的是了時(shí)提，證每老每天工量基持，避課集中某。護(hù)工表“課檢”工表手動(dòng)入信息全后，開(kāi)元格式設(shè)置在護(hù)中“藏”“定”面鉤。后擇“閱菜單，保工作中置管密。（五“年級(jí)表”作。本作表來(lái)“總程”中動(dòng)取“級(jí)表”其板見(jiàn)。812

課程模右側(cè)白新建年列”“節(jié)表見(jiàn)圖。9I5格，輸信錄入'!I5)，從信錄工作中取“級(jí)。以單格復(fù)填。（2）在“”區(qū)，輸課數(shù)字例“11”指期一第1節(jié)，21”指星期第，期第，以此類。如圖。B3單格設(shè)置下菜單。打開(kāi)數(shù)有效”鈕，效條件“列”元格景“黃”此時(shí)單元格下菜單，可意選擇“年”年級(jí)程寫入式（1）元格入公式CONCATENATE('1信息錄'!O2)，動(dòng)引信錄入中學(xué)期（2）“課程”單元格。如星期一課的課程寫入：課程表!$A$5:$AJ$106,MATCH($B$3,'3總程表'!$A$5:$A$106,0),MATCH(I6,'3總程表'!$A$5:$AJ$5,0))，義從總課程中用“和“所在列叉單格“課”指年，I6指課（，此公式使套功，能很快取不同節(jié)課程其復(fù)制公填充設(shè)完畢藏M:N。（3）“教師”單元格。如，星期第節(jié)的教，在7中13

寫入：任設(shè)置!$L$3:$AR$54,MATCH($B$3,'2課設(shè)置任置'!$L$3:$AR$3,0))，其義同上。其余制公式充列以上式完成以，及時(shí)隱列，防他人修改，致?lián)e(cuò)。護(hù)工表（1）全選工作表，設(shè)置元格格式護(hù)，在“藏”和“定”前面鉤（2）此工作表中“”為活單元，取消“藏”和“定”前的。（在“閱菜單，開(kāi)“護(hù)作表按設(shè)置理碼。（六“教師表”作。此作表于總課表提取師人課表見(jiàn)1014

在板右空處新一“教姓”，如單格輸入：=CONCATENATE('1信息引用信息錄入表中的教姓名見(jiàn)。11“”元格設(shè)下拉單選B3單元格，開(kāi)數(shù)據(jù)有性按鈕，件為“序”，數(shù)據(jù)源E，也就剛才設(shè)置教師姓名，最后確B3單背景設(shè)“色”此點(diǎn)單元下拉菜單任意選擇師。工作中入公。（1）“C2單元格信入（2）“課程”行輸入公。例如期第一課即6格寫入課程表!$A$5:$AJ$106,MATCH($B$3,'3總課'!BB$106,0)-1,MATCH(11,'3總課'!$5:$5,0))，意義解，在課域，找“某教在B5:B106列中在的“行與”A5:AJ5中在的列”交