Excel 函数公式在中小学排课中的应用研究

函数公式在中小学排课中的应用研究中小教处工中常重的项任便排课现有许这样排软件但多需付，使上常不。统的工课工量，若个节出问，就牵其他节有时至前功尽。此，尝运表进行助课，实中收到了错效果下就具过作系的明，望大家所助。一、备作。软硬准。EXCEL（或以）电表，，正常启以软件电设备了解用数与式用法例如（假判）（据引用）或者、并且列找条件计（行交引用求和）、SUMIF（件和）函；学会数有性和件式的置法；解对应和对应的用等掌这些本法在课程中会挥意不的效。打子表，建工簿添加作，并次名为1信录”、设置、课程表、检”、“课”、“教师课表”。二、作的具设。当工表名以，逐一行置，于作表数会相引用所在设过中一要到准、实，据式一。（一“信息入”作。信录入作是所工表的础其数的靠、楚后面排、检、取将牵发而全，所在置前进1

全面调，以证入模统，修方，为后循环用供保证以表为（定使班教师，设了学校、上课数学期各标准时总课、师、设学科班及班任各科时。见1“土色区域入据，以区域置数据效”。“是上”列设：选格，点击据菜单，开有效性钮数据源“是否其他。置完后下复至C204.后根需依次置否上。“班任列的置选单元格，数据来源=$B$5:$B$204，即教列数据然向下制。后选设置各班主任。以下元输入式“L2”单元格输入公：=COUNTIF(C5:C204,"")此处运用条件统公，用统中含“”的个数即为上课数。“AD2单元输公式：用于计中数的，即总时。域设置条格式。选择开”菜，开“件式”钮在新规中选“只包以下容单元设格式选，设单格值于“时背景色示为黄色最后确定览，此时是数据等34的背为色。样设置于明快的握数输是否确保护锁工作。锁定活单元：选工表在单格式中择护，“锁”“隐”项上钩确定然按住“CTRL”，选择2

活动元，即格的“色区域在元格式护中消锁定和“藏前的，后确。保护作：在审”菜，开“护作表按，设密码最确定此，在作的活单格中输数据其单元处锁定态（二“任课置”作。课设工表用计教师人“标课”，对师个人的作进行划作好课况的计即就表中的计课时”为排总表供服。土黄”域为动元格可由输信，详：2“蓝色区域入式。“A5:C203”域：A5元格入公式信录入'!A5)，其单元格复粘贴。此式用于引信息录入工作表的据，可于将个元格据并为个元格“D5:D203”是否班主任区：在5单格输公式：信录入'信录入息录'信息录，公式中运的逆向查功能，在息录入工表查找主老师对的班，出现误用和“ISERROR”函数进真判断若“假显为空，则显为对的级。意式中绝应用号”可忽。最后5格复制此式即可。“E5:E203”班任折算课区域：在5单元输入公式3

=IF(OR(B5="",D5=""),"0",$E$4)此式中“指“者”的意，意义如满B5=”””中的任何个条件，显示为”，否则显为“单元格的容。以下元格复制可。“H5:H203”标准课时区域：H单元输入式=IF(B5="","",IF(C5="是",($E$204+$G$204+'1息录'息录入!$L$2-$E5-$G5,""))，公式用于算标准课，计算方为总课时+主任课兼职课，然除上课数最后去主任课时兼课时其单元复即可“IF:I203”计课时域：在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条件求和式，于计教任不同目课时总。其单格复即。“”差区：元格入式是"此公中使用的嵌套功能其意义为元格空时显为，单格示“”，显否则为空其单元复填充“K5:K203”标码区域：在5单元格输入式：=IF(J5<-1,"",IF(J5>1,"","√"))，此用同上用判断差”是否在-1和之，如是示为则显示为。这种设便于及掌教师作是否到准工量其余元复制充“”年域：单元格入=CONCATENATE('1信录入!I5)，接引用信录入工作中的年级息。“NP、T、、Z、、AD、、、AJ、AN、、AR”列时区域：在N5元格入录'!$I$4:$AC$54,MATCH($L5,'1信录入'!$I$4:$I$54,0),MATCH(M$3,'1信4

息录入'!$I$4:$AC$4,0)))，义说明，进行真假断，单元为空，显为空否根INDEX函，引用信录入表中列交叉单元的据，不年级同目的时。其单格复填。“M3:AQ3课程行输：单元输入息录入'!$E$5:$F$24,2)，此公式运用COLUMN()列号用公式，后根数引信录入表中设置课。例单所列COLUMN()值然后除，所得减5结，后根VLOOKUP信录入中号1的2列所应的程。其他元格复制公式。“AK2标码单元格入公=IF(I204=H204,"√×")，其义为，“准课数等于计课时”显示√”，否显示为“。此式用快判断程否全安，为排课提供方便使中只当识码示“”时才能进入一环节的置，记置数有性。在F2单元打开数据效性按钮数据来源B3:B203置条格。选择据B5:B203，在始菜单中开条件格按钮，选“只为包以内容单格设格”，单格值，背色显示为红”。样设置于速查教的具任安排护工表当工表景和式入完后对部单格公进锁定以防他随更改造排课混。方如：全整工作，单元格保护“定”“藏”打，确。后选活的单元，元格表中的兼工作”“教师列，取消格式护“锁”“隐”的钩确。最选审阅单点击“护作表按，输管密码（三“总课表”作。课程是个排系的核。信息入任课置入完毕后即进行课表的排首次用作量大以后会分方便只轻点标对个内进行单理即完总课。体设置见35

3写表头输公式（1）“课节”行中数字说明：例如11，指星一第一节，25指星二五节以类推（2）“”元格：=CONCATENATE('1信录入'（3）“L3”元格：=CONCATENATE('1息录入'!O2)（4）“AG1”格，此处据来源为课表检验工作表，式同。（5）“区域：单格写公式：=IF(MOD(ROW(),2)=1,VLOOKUP((ROW()+1)/2-3,'1信息录入'!$H$5:$I$54,2),"教")，公式ROW()用于引于前单元格在的行号，用于算前单格在行除后余数整个式，如余，则引用“信息录”表中设的年级，否则示“教”其余元复制公进行充（6）“教师”所在行区：在“单元格输公式任课置!$L$3:$AR$54,MATCH($A7,'2课设置任置'!$L$3:$AR$3,0)))。此公中运用公，用提取“任设置”工表中“七”交叉元的数，七年班星期一第节课的教。其余元复制公填充（7课时”列域：单元格输=IF(OR($B7="",$A7="教师"信息录入!$I$4:$AC$54,MATCH($A7,'1息录信息入'!$I$4:$AC$4,0)))，此式中先6

进行假断，白A7=教师，则显为空。否从信息录入工表提取七和任意程交叉元的数，为计划时。其单格复此式予填。另，表中B1为动单元格可以点开拉菜单随选择不同程。为活动单格用于择意教，文会到（8）“实际课时”列区：例如在元格入公式=COUNTIF(B7:AJ7,$B$1)用于计行课中课个。其B7:AJ7为相对用，即为动区域绝对用请注意。他单格制填。B1、单元各课程元设置拉单样。（1）在总课程表右侧空处添加“程”列和教师”列利用函数用信息入工作中所有课”和“教”（B1元格设置据有效性数据来源刚才设置“课程列，景为色元设数据效，数来为“师列”背为蓝。置完预效果（3）在各班课程行设置据有效性例单元，据来为“课列，背色“土色，其班和区复此单格置“注行信。在写入：=IF(COUNTIF(B7:B106,$T$1)>=2,"重,COUNTIF(B7:B106,$T$1))意义说明：判所在中任意教”数否于等2，如果有则显示“”否则示计的数B6:AJ6”区域其他元格复制公式充“备”域为师否存重现象行示。置条格。（1）选择“B6:AJ6”，点条格式钮只为含下内容的单元设格式当元格为重”，景设为红色。（2选择“B7:AJ106”点击条件式按钮，为包含单元格容设置景“绿”为包单格内的置背景为蓝”。（3）选择“AL7:AM7”，设条件格式值为“唯”时，背色为粉色”以区域择式刷应此设。“总程”下空处新附见4。表数为一工作“表检”“是重”列供务。7

（1）“区域姓名列）在111输入式信息入!B5)，下复制此式填充。（2）“B111:B310”区写入公式在输入=IF($A111="","",COUNTIF(B$7:B$106,$A111))，公式用C函数计师在节中的时。其区复制公填充（3）“AK111:AK310”区：在AK111”中写入公式=IF(COUNTIF(B111:AJ111,">=2")>=1,"重,"")，明：本行数据存在大于2时显示“重”，则显示为，用于判教师是否在重课现。余复此式填。“总程”右空处新附见5。表数为一工作“表检”的“际时”提服务8

（1）“”域写入公：例如R7单元，=IF(MOD(ROW(),2)=1,VLOOKUP((ROW()+1)/2-3,'1信息录入'!$H$5:$I$54,2),"教")，公式“总课程”第列。（2）“AS5:BM5”域写公：例A单格，信息录'!$E$5:$F$24,2)，此公式用来引“信录”工表的“程。（3）“AS7:BM106”区写入式例如S7格，=COUNTIF($B7:$AJ7,AS$5)，公式来统计“课表”中一年级课程的数其他制充。护工表（1）全选工作表，设置元格格式在保护菜的“隐藏和“锁定前打，后确。（2）选择活动单元格和域。例、级的课程行，设置元格式取“隐”“锁”的钩可（打开审”菜下“保工表”钮设置理码。（四“课表验”作。“课表检”为总课表的编排供服务。此工作表可以直观的看个教是存在课象、天课时多、总时是否达计课时、校是存空课象最后动断排是成功。板6入公。（1）“A4:B203”区域：A4单格输：9

信息录入!A5)此公用于引用信息录入”中的号其他制公式充引用师（2）“”区：先C4单元格输：任设置'!$B$5:$I$203,8,0))，意义为先进行断格空时示白，则据列找，找“课置”区域8列对的据，计课时。以下元复制充（3）“D4:H203”区域“D4”：=IF(B4="","",COUNTIF('3总课表!$B$4:$H$106,$B4))意义统“总程”中域B4应教师的数。“公式课程表!$I$4:$O$106,$B4))“F4”公式课程表!$P$4:$V$106,$B4))“G4”式程表“H4=IF(B4="","",COUNTIF('3总课程'!$AD$4:$AJ$106,$B4))以上式义相，余各复此公填。（）”区：”，列之。（5）“J4:J203”域，元格入：课程表'!$A$110:$AK$309,37,0)，意义根据列查公，找“总程”下“表”A110:AK306”区域第7其单元格制公式充此时以藏“课表”侧附表（6√误其义为先进判断，为空显示为空且J空时，显为“显为“误。此式运用IF嵌套能，于判断每教师排课否正确。下单元格制此公式填。（7）“M4:M203”区域：M4输入VLOOKUP((ROW()-1)/3,'1信息入!$H$5:$I$54,2)，其意义为先引所单元的号，后根据查公式查信息入中对的级。下制填。（8）“O4:AH6”：“教”，“”元格入任课设置任设置'!$L$3:$L$54,0),MATCH(O$3,'2任课置“计课”行“O5”单格输入息录10

入'信息入'!$I$4:$I$54,0),MATCH(O$3,'1信息入“实课”行”元格总程表'课程表'!$AR$5:$AR$106,0),MATCH(O$3,'3总课表'!$AR$5:$BM$5,0)))。以上公式运了多式套方，进行断假，格为时显示，则根INDEX+MATCH公式找“课程表”侧附表中列交单格的。复制公填充一单元，后同复三行单格式，充以下元。此可隐藏总程表的统表（9）“C204:K204区域C204:I204入SUM求和式。J204入：=COUNTIF(J4:J203,"重)，于中“重”个数K204输：=COUNTIF(K4:K203,"错误，用统中“误”的个。7（10）单格，排标识写公式排成功,"失败)，公式表示并”之，理解，同时足204=I204,J204=0,K204=0三个件，显为排课功，否显为“败。置条格。（1）选择4:J203，开开始菜中的“条格式”，置当单元格容含“”，背颜为“色。（）K4:K204,打开始菜单中“条件格”，设置单元格内包“错”背景色“红”11

（选择5:O6，设置当元格内容“唯一”时背景为玫红色，他“划时”“际课”元格用式刷用一设置（选D4:H4，设条件格式当单元格大于等时背景色为淡色”其的是了时提，证每老每天工量基持，避课集中某。护工表“课检”工表手动入信息全后，开元格式设置在护中“藏”“定”面钩。后择“阅菜单，保工作中置管密。（五“年级表”作。本作表来“总程”中动取“级表”其板见。812

课程模右侧白新建年列”“节表见图。9I5格，输信录入'!I5)，从信录工作中取“级。以单格复填。（2）在“”区，输课数字例“11”指期一第1节，21”指星期第，期第，以此类。如图。B3单格设置下菜单。打开数有效”钮，效条件“列”元格景“黄”此时单元格下菜单，可意选择“年”年级程写入式（1）元格入公式CONCATENATE('1信息录'!O2)，动引信录入中学期（2）“课程”单元格。如星期一课的课程写入：课程表!$A$5:$AJ$106,MATCH($B$3,'3总程表'!$A$5:$A$106,0),MATCH(I6,'3总程表'!$A$5:$AJ$5,0))，义从总课程中用“和“所在列叉单格“课”指年，I6指课（，此公式使套功，能很快取不同节课程其复制公填充设完毕藏M:N。（3）“教师”单元格。如，星期第节的教，在7中13

写入：任设置!$L$3:$AR$54,MATCH($B$3,'2课设置任置'!$L$3:$AR$3,0))，其义同上。其余制公式充列以上式完成以，及时隐列，防他人修改，致据错。护工表（1）全选工作表，设置元格格式护，在“藏”和“定”前面钩（2）此工作表中“”为活单元，取消“藏”和“定”前的。（在“阅菜单，开“护作表按设置理码。（六“教师表”作。此作表于总课表提取师人课表见1014

在板右空处新一“教姓”，如单格输入：=CONCATENATE('1信息引用信息录入表中的教姓名见。11“”元格设下拉单选B3单元格，开数据有性按钮，件为“序”，数据源E，也就刚才设置教师姓名，最后确B3单背景设“色”此点单元下拉菜单任意选择师。工作中入公。（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中在的列”交