Сабақ кестесін Excel-де қақтығыссыз құру және оқытушы жүктемесін есептеу

Мектепте, колледжде, оқу орталығында немесе үйірмеде кесте жиі бірнеше файлда өмір сүреді. Қиылысу дүйсенбі таңертең ғана анықталады: бір оқытушы екі топта тұр, екі топ бір кабинетке кірген. Екінші мәселе — жүктеме: кім ставкасынан артық сағат алған, кімге сабақ жетпейді, оны қолмен санау ұзақ.

Мақалада сабақ кестесін Excel-де журнал ретінде қалай құруды, қақтығыстарды формуламен қалай табуды және оқытушылардың жүктемесін ставкамен қалай салыстыруды көрсетеміз.

Кестені «тор» емес, «журнал» ретінде жүргізіңіз

Көпшілік кестені дереу апталық тор (күндер мен сағаттар) түрінде жасайды. Бірақ торда қақтығысты тексеру мен сағат санау қиын. Дұрыс тәсіл: деректерді тізім-журнал түрінде жинап, торды сол журналдан алу.

Журналда бір сабақ — бір жол:

• аптаның күні;
• слот (сабақтың реттік нөмірі: 1-сабақ, 2-сабақ);
• топ;
• пән;
• оқытушы;
• кабинет.

Слот уақытын (мысалы, 1-сабақ 8:30–9:15) бөлек анықтамалықта сақтаңыз. Сонда қоңырау кестесі өзгерсе, бір жерді ғана түзетесіз. Топтар, оқытушылар, кабинеттер мен пәндер де анықтамалықтан ашылмалы тізіммен таңдалуы керек.

Қақтығыстарды формуламен табу

Қақтығыс — бір күні бір слотта бір оқытушының, бір кабинеттің немесе бір топтың екі жолда кездесуі. Күн — A, слот — B, топ — C, оқытушы — E, кабинет — F болсын. Оқытушы бойынша тексеру:

=COUNTIFS($A:$A;A2;$B:$B;B2;$E:$E;E2)

Нәтиже 1-ден артық болса, оқытушы бос емес. Кабинет және топ үшін де осындай формула жазасыз, тек бағанды ауыстырасыз. Біріктіріп, түсінікті нәтиже шығарайық:

=IF(COUNTIFS($A:$A;A2;$B:$B;B2;$E:$E;E2)>1;"Оқытушы бос емес; ";"")&IF(COUNTIFS($A:$A;A2;$B:$B;B2;$F:$F;F2)>1;"Кабинет бос емес; ";"")&IF(COUNTIFS($A:$A;A2;$B:$B;B2;$C:$C;C2)>1;"Топ бос емес; ";"")

«Тексеру» бағанында нақты не бос емес екені жазылады, ал Conditional Formatting арқылы мәтіні бар жолды қызылға бояйсыз: =$G2<>"". Сабақты енгізген сәтте-ақ қате көрінеді, оны дүйсенбіні күтпей түзетесіз.

Апталық торды журналдан жинау

Басып шығаруға арналған тор үшін қиылысқан ұяшықтарға журналдан мәтін тартасыз. Таңдалған топ — K1 ұяшығында, күн — жол тақырыбында (A5), слот — баған тақырыбында (B4) тұрса:

=IFERROR(INDEX(пән_бағаны;MATCH(1;INDEX(($A$2:$A$800=A5)*($B$2:$B$800=B4)*($C$2:$C$800=$K$1);0);0));"")

Бұл — массивті шартпен жұмыс істейтін INDEX/MATCH тіркесімі, ол барлық Excel нұсқаларында Ctrl+Shift+Enter-сіз істейді. Топтың орнына оқытушыны таңдасаңыз, сол формула оқытушының жеке кестесін береді, тек шарттағы бағанды ауыстырасыз.

Оқытушы жүктемесін есептеу

Жүктеме — аптасына оқытушы өткізетін сағат саны. Бір сабақ бірнеше академиялық сағатқа тең болса (мысалы, екі сағаттық жұп сабақ), оны параметрде бір ұяшықта береміз. Формула:

=COUNTIF($E:$E;A2)*$K$1

Мұнда A2 — оқытушы, K1 — бір сабақтағы сағат саны. Енді ставкамен салыстырамыз:

=B2/C2

Мысал:

122% — артық жүктеме, 67% — жүктеме жеткіліксіз. Жүктеменің жоғарғы және төменгі шегін бір ұяшықта беріп, формуланың өзінде «артық» немесе «жеткіліксіз» деген белгі шығарсаңыз, кестені көзбен іздеудің қажеті жоқ.

Жиі кездесетін қателер

• Оқытушының аты-жөнін әртүрлі жазу («Иманов А.», «Иманов Асхат»): COUNTIFS оларды әртүрлі адам деп санайды.
• Слотты уақытпен емес, еркін мәтінмен жазу.
• Қос сабақты бір жолға жазып, екінші слотты бос қалдыру: онда екінші слотта қақтығыс көрінбейді.
• Кабинеттің сыйымдылығын тексермеу. Бұл қосымша тексеру, оны өзіңіз қосасыз.
• Жүктемені қолмен санап, ауыстырулардан кейін жаңартпау.

Дайын үлгі: сабақ кестесі және оқытушылар жүктемесі

Формулаларды өзіңіз құрғыңыз келмесе, дайын кестені алуға болады. Үлгі ақылы және төрт парақтан тұрады.

«Анықтамалықтар» парағында аптасына сағатпен ставкасы бар оқытушылар, топтар, кабинеттер, пәндер, сабақ уақыты (слоттар) және жүктеме параметрлері беріледі. «Сабақтар» парағы 800 сабаққа дейін сыяды: күн, слот, топ, пән, оқытушы, кабинет; уақыт, сағаттар мен қақтығыстар бөлектеумен және мәтінмен тексеріледі. «Тор» парағында таңдалған топ немесе оқытушы бойынша апталық тор журналдан формулалармен жиналады және басып шығаруға дайын. «Жүктеме» парағында оқытушылар бойынша аптасына сағат ставкамен салыстырылады, жүктеме пайызы, артық жүктеме, күндер бойынша сабақтар және екі диаграмма бар.

Слот уақыты анықтамалықта беріледі, сондықтан мектептің қоңырау кестесі де, колледждің жұп сабақтары да жарайды. Жинақта бос кесте және ойдан шығарылған деректері бар толтырылған үлгі бар. Файл қазақ тілінде, Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те макростарсыз жұмыс істейді. Бұл қарапайым формулалардағы Excel кестесі, арнайы кесте құру бағдарламасы емес. Ставкалар мен шектерді жұмыс алдында өз талаптарыңызбен салыстырыңыз.

Жиі қойылатын сұрақтар

Кестені жыл басында қалай құрған жөн?

Алдымен анықтамалықты толтырыңыз: оқытушылар, ставкалар, топтар, кабинеттер. Содан кейін ең қиын сабақтарды (бір кабинетті немесе бір оқытушыны қажет ететіндерді) бірінші енгізіңіз, ал жеңіл сабақтарды соңына қалдырыңыз.

Бір оқытушының ставкасынан артық сағат алуы дұрыс па?

Бұл ұйымыңыздың ережелеріне байланысты. Кесте артық жүктемені тек көрсетеді, ал шешімді басшылық қабылдайды.

Екі аптаға алмасатын кестені қалай жүргізуге болады?

Журналға «апта түрі» бағанын қосып, формулалар мен торға сол бағанды шарт ретінде қосыңыз. Сонда жұп және тақ апталардың кестесі араласпайды.

Кестеден бір оқытушының жеке кестесін қалай шығаруға болады?

Торда ашылмалы тізімнен оқытушыны таңдаңыз: апталық тор сол оқытушының сабақтарымен автоматты түрде толады, оны жеке басып шығаруға болады.