Ученики на Учителя.com


Конспект урока "Прикладное программирование в EXCEL" 10 класс

ПРИКЛАДНОЕ ПРОГРАММИРОВАНИЕ В EXCEL
Автор: учитель информатики
МБОУ Тёпловская СОШ
Карачевского района Брянской области
Федоров Александр Николаевич
На уроках информатики огромные возможности MS Excel по созданию полезных в жизни приложений обычно
остаются за скобками. Учебного времени хватает лишь на простое моделирование физических задач да
табличные вычисления. Но если ученик всерьез интересуется информатикой, если у него есть склонности к
программированию, то можно придумать куда более сложные, но и куда более интересные задачи для него.
Причем нам не придется даже прибегать к средствам VBA – достаточно будет «чистого», скажем так
функционала Excel.
Как раз такую задачку мы и будем решать в рамках данной статьи. Мы создадим полноценный инструмент,
который смогут использовать в реальной жизни школы администрация, заместители директора. Мы создадим
программу по проверке школьного расписания.
Небольшое лирическое отступление. Что заставило меня в свое время сделать подобную вещь? Дело в том, что
моя мама – завуч нашей небольшой сельской школы. И в число ее обязанностей входит составление расписания
занятий. И всякий раз она просила меня помочь ей найти недостающие часы или совпадения. Ну а как вы знаете,
лень – двигатель прогресса. В итоге я сделал динамическую таблицу, способную решать целый ряд задач по
проверке расписания. Позже я написал приложение в Visual Basic.Net, которое позволяло в полуавтоматическом
режиме составлять расписание, а не просто проверять его. Но об этом, возможно, речь пойдет в другой раз.
Итак, что будет уметь наша программа?
1. Находить совпадения в расписании (когда у одного и того же учителя оказываются два класса в один
урок);
2. Находить лишние или недостающие часы
3. Определять баллы ежедневной нагрузки (согласно санитарных норм)
4. Строить графики недельной нагрузки по классам
Для работы лучше использовать Excel 2007 или более новый, однако все функции, которые мы будем
использовать, не являются специфичными для данных версий, так что, думаю, в более старых версиях тоже все
будет работать.
Данную статью я построю по принципу «смотри, как я делаю». Лично по моим ощущениям именно такое
изложение будет наиболее эффективным в данном случае, ведь целевая аудитория данной статьи – люди,
владеющие Excel на более чем минимальном уровне.
Итак, приступим.
НАЧАЛО
Какие листы нам потребуются. Конечно же, лист с расписанием. У нас – сельская школа, так что нет параллельных
классов. Собственно, это не имеет большого значения, и более крупное расписание можно создать так же, как и
наше. Итак, первый лист я переименую в «РАСПИСАНИЕ». И на него как раз в будущем будет вбито расписание.
Сами предметы можно не заполнять пока, а вот сетку лучше распланировать заранее, чтобы знать диапазон
ячеек.
Синие, желтые и зеленые ячейки на скриншоте – это мои уроки. Я сделал это для себя, пока не нужно обращать
на это внимания. Обычное условное форматирование ячеек, и об этом мы поговорим позже.
Теперь нам потребуется лист, где будет проведено соответствие между учителями и предметами, которые они
преподают. Назовем этот лист «УЧИТЕЛЯ». Собственно, проще всего скопировать лист «Расписание», и слегка его
подправить. Но это уже – дело вкуса. В итоге должна получиться таблица, где в строку будут перечислены
предметы, а в столбцы – классы. Обратите внимание, что уже на данном этапе нужно начинать быть крайне
внимательным, ведь различие всего в один символ превратит одну строку в совершенно другую с точки зрения
Excel. И еще: учтите, что так, как вы назовете предметы здесь, точно так же, буква в букву, вам придется называть
их в самом расписании. В итоге у нас получится нечто вроде этого:
ОСУЩЕСТВЛЯЕМ ПРОВЕРКУ СОВПАДЕНИЙ
Собственно говоря, у нас уже есть все необходимое для того, чтобы проверить, нет ли совпадений у учителей.
Скопируем еще раз лист «Расписание» и назовем эту копию «СОВПАДЕНИЯ».
Какова наша дальнейшая логика? Все очень просто – на этом листе нам предстоит преобразовать расписание
предметов в расписание учителей. Т.е. для каждой ячейки мы проверяем, какой предмет соответствует ей в
листе «Расписание», и затем находим фамилию учителя, ведущего данный предмет в данном классе, по листу
«Учителя».
Для выполнения этой задачи воспользуемся функцией ИНДЕКС. Не думаю, что в рамках данной статьи стоит
подробно расписывать каждую функцию, тем более, что их описание можно найти в справке Excel. Аргументами
данной функции являются: массив искомых значений, а также номер строки и столбца, в пересечении которых и
будет найдено то самое значение.
Массивом значений будет таблица фамилий учителей. Т.е. в моем конкретном случае (представленном на
скриншотах) это будет диапазон Учителя!$B$2:$L$30 (если на представленных выше рисунках плохо видно
что-либо из-за плохого качества, можно посмотреть в конце документа – там будут приложения с теми же
скриншотами, но большего размера).
Номер строки предстоит получить, выискав название предмета (которое мы получаем из листа «Расписание» по
координатам, совпадающим с координатой нашей ячейки) в первом столбце листа «Учителя», т.е. перечне
предметов. Поможет нам в этом функция ПОИСКПОЗ, аргументами которой являются искомое значение и
просматриваемый массив. Тип сопоставления, естественно, выбираем равным 0. Искомое значение -
Расписание!B2 (для ячейки В2 на листе «Совпадения»). Не буду устраивать ликбез по поводу абсолютных и
относительных ссылок, думаю, все и так это знают, и понимают, почему в одних случаях я ставлю знак $, а в
других – нет. В данном случае, как вы понимаете, мне нужна как раз относительная ссылка, которая будет
меняться в зависимости от координат редактируемой ячейки. Просматриваемый массив –
Учителя!$A$2:$A$30, т.е. первый столбец.
Номер столбца в нашем случае, конечно же, будет равен 1 (для 1 класса; 2 – для второго и т.п., это, думаю,
понятно). Что же мы получаем, подставив все эти аргументы в функцию ИНДЕКС? А получаем мы вот такую
строчку:
= ИНДЕКС(Учителя!$B$2:$L$30;ПОИСКПОЗ(Расписание!B2;Учителя!$A$2:$A$30;0);1)
Однако остался еще один момент: если соответствующая ячейка в расписании будет пустой, то результатом
данной функции станет #Н/Д! Оно нам надо? Оно нам не надо. Поэтому введем дополнительную проверку –
есть ли вообще в данной ячейке какой-либо предмет. Если нет – просто выведем пустую строку. Очевидно, для
этого нам потребуется функция ЕСЛИ. Уверен, что тут вообще ничего объяснять не нужно, поэтому просто
приведу конечный вариант формулы.
=ЕСЛИ(Расписание!B2<>"";ИНДЕКС(Учителя!$B$2:$L$30;ПОИСКПОЗ(Расписание!B2;Учителя!$A$2:$A$
30;0);1);"")
Осталось размножить эту формулу. Ее можно спокойно протягивать заполнением по всему столбцу, а в
последующих столбцах придется менять значение столбца – аргумента функции ИНДЕКС (о чем мы говорили
выше), т.е. менять значение 1 на 2, 3, 4…
Итак, мы получили таблицу, которая представляет собой расписание учителей, составленное в соответствии с
расписанием уроков. Теперь наша задача – визуализировать возможные совпадения. Сделать это очень просто
при помощи условного форматирования.
Выделим строку B2:L2. Выберем пункт «Создать правило» в кнопке Условное форматирование.
Нам нужно будет выбрать вариант «Форматировать только уникальные или повторяющиеся значения», выбрать
в выпадающем списке «Повторяющиеся», а затем при помощи кнопки Формат задать вид, который будут
принимать ячейки, значения которых в пределах данной строки совпадают. Я ограничился тем, что сделал шрифт
красным. Вполне заметно. Вот и все – мы уже выполнили весьма важную задачу – теперь Excel весьма наглядно
покажет нам, где мы допустили ошибку.
Как вы видите на скриншоте, я добавил еще одно правило форматирования – окрашивание ячейки в голубой,
когда в ней находится моя фамилия. Опять же – просто для себя, чтобы видеть, какие у меня уроки.
Ну что ж, весьма важная задача была решена нами, как видите, совершенно просто и без малейшего
напряжения. Но не стоит почивать на лаврах – нам предстоит еще большая работа. Не менее серьезным бичом,
помимо совпадений, для моей мамы являлось то, что она иногда либо «теряла» какие-то уроки, либо наоборот
выдавала лишние. В общем, теперь нам предстоит проверить наше расписание на количество часов.
ПРОВЕРЯЕМ КОЛИЧЕСТВО ЧАСОВ
Для того, чтобы проверить количество часов, нам сначала потребуется узнать, сколько же их должно быть.
Поэтому создадим еще один лист «ПЛАН» (собственно говоря, просто скопируем лист «Учителя» и
переименуем). Теперь сюда мы забьем расчасовку из базисного плана. Ну а дальше все снова достаточно просто
– нам нужно посчитать количество часов по каждому предмету в каждом классе, и сравнить их с базисным
планом.
Для подсчета количества часов нам потребуется еще один вспомогательный лист, который я назвал
«КАЛЬКУЛЯЦИЯ». Мы снова копируем лист «План» и переименовываем его.
Итак, мы должны посчитать количество часов по предмету в классе. Для этого воспользуемся функцией
СУММЕСЛИ. Аргументами данной функции являются: диапазон поиска, критерий поиска и диапазон
суммирования.
Диапазоном поиска для ячейки B2 будет Расписание!B$2:B$36. По координатам столбца ссылки оставил
относительными, чтобы проще было размножать. Т.е. мы берем массив предметов 1 класса.
Критерием поиска, конечно же, будет название предмета. В нашем случае – значение ячейки $A2.
Теперь по поводу диапазона суммирования. Тут нам придется пойти на небольшую хитрость. На листе
«Расписание» в столбце М поставим единички напротив каждой строки расписания, т.е. в нашем примере это
будет диапазон $M$2:$M$36. Цвет шрифта этих единичек можно сделать белым, чтоб они не были видны.
Собственно, теперь это и будет наш диапазон суммирования - Расписание!$M$2:$M$36.
Подставим все в формулу:
=СУММЕСЛИ(Расписание!B$2:B$36;$A2;Расписание!$M$2:$M$36)
Эту формулу теперь можно размножить заполнением по всей таблице – как по вертикали, так и по горизонтали.
В итоге мы увидим таблицу значений, где каждое число будет означать количество часов по конкретному
предмету в конкретном классе.
Видите, я для красоты ввел еще условное форматирование, чтобы нолики почти исчезли из вида. Хотя это –
вспомогательная таблица, и тут красота не так важна.
Ну что ж – теперь создадим лист «КОЛИЧЕСТВО ЧАСОВ» путем копирования листа «План». Здесь мы и будем
сравнивать показатели базисного плана с полученными значениями. Причем я хочу, чтобы в случае
несовпадения пользователь бы видел – каково оно. Поэтому в данном случае будет выводиться сообщение типа
«Х<>Y», где Х – это количество часов в расписании, а Y – в базисном плане. Для наглядности еще подсветим это
сообщение красным.
Наша задача проста – сравнить соответствующие значения ячеек в листах «План» и «Калькуляция» при помощи
функции ЕСЛИ. Условие простое (опять же – для ячейки В2) - План!B2=Калькуляция!B2. Если оно выполнено, то
просто выведем значение План!B2.
Если же оно не выполнено, то выведем следующее: Калькуляция!B2&"<>"&План!B2. Кажется, тут все понятно
– значок & означает сцепку фрагментов строки.
Опять же – проведем проверку на наличие значения в листе «План» - есть ли вообще данный предмет в учебном
плане данного класса. Если нет – поставим значение 0, а для пущей красоты еще и скроем эти нули при помощи
все того же условного форматирования. В итоге получим готовую формулу:
=ЕСЛИ(План!B2<>"";ЕСЛИ(План!B2=Калькуляция!B2;План!B2;Калькуляция!B2&"<>"&План!B2);0)
Размножив эту формулу заполнением по горизонтали и по вертикали, получим готовый лист, который наглядно
продемонстрирует нам, все ли часы у нас на месте.
Правда, тут есть небольшой нюанс – если в учебном плане есть предметы, на которые выделяется не целое
число часов, тут уж придется как-то исхитряться. Например, в 8 и 9 классе по полчаса музыки и ИЗО, и мы
выходим из положения, записав в 8 класс 1 час музыки, а в 9 – 1 час ИЗО. Ну и т.д. В общем, всегда можно найти
выход из положения. В данном случае это как раз такие небольшие недостатки, которые никак не могут
испортить общую картину. А ведь мы с вами уже сделали полноценного помощника для вашего заместителя
директора по учебной работе!
ОПРЕДЕЛЕНИЕ БАЛЛОВ ТРУДНОСТИ ПРЕДМЕТОВ
Когда расписание сдается на утверждение в районный отдел образования, то в числе прочей документации
также сдаются показатели трудности (не знаю, как это правильно называется) для каждого класса. Дело в том,
что каждый предмет имеет свой балл сложности. Соответственно, сумма этих баллов за день дает представление
о дневной нагрузке. Есть нормы, регламентирующие данную нагрузку, так что с ней приходится считаться. Было
бы неплохо, чтобы наша программа также умела рассчитывать эту нагрузку, тем более, что ничего сложного в
этом нет.
Очевидно, нам придется создать еще один лист «НАГРУЗКА» (естественно, как всегда получаем его
копированием листа План, допустим). Здесь мы внесем данные по баллам на каждый предмет и каждый класс.
Ну и создадим лист «БАЛЛЫ», где и будет рассчитываться нагрузка. Это будет небольшая таблица с днями
недели в строках и классах в столбцах.
Хорошо бы еще ввести дополнительную индикацию. В этом нам снова поможет условное форматирование. Я
добавил предустановленное правило из «Набора значков» - в каждой ячейке появился индикатор по типу уровня
сигнала сотового телефона, который наглядно дает понять, как близко мы приблизились к максимуму. Ну а также
я создал еще одно пользовательское правило, которое показывает, если мы превысили предельно допустимую
нагрузку (что-то около 55 или 60 баллов). Тогда ячейка загорается красным.
По большому счету, тут уже все зависит от вашей фантазии. Однако же, нам нужно получить эти самые дневные
значения баллов. Т.е. посчитать сумму баллов на каждый день. А чтобы это сделать, нам придется создать еще
один вспомогательный лист. Я назвал его «ДНЕВНАЯ», и получил копированием… Ну вы уже сами поняли.
На этом листе мы должны преобразовать расписание предметов в расписание баллов. Операция аналогична
тому, что мы проделывали, превращая расписание предметов в расписание учителей, поэтому не вижу смысла
подробно расписывать эти действия. Так что просто приведу итоговую формулу для ячейки В2:
=ЕСЛИ(Расписание!B2<>"";ИНДЕКС(Нагрузка!$A$2:$L$30;ПОИСКПОЗ(Расписание!B2;Нагрузка!$A$2:$
A$30;0);2);"")
Очевидно, что здесь также придется менять номер столбца (2 в нашем примере) на 3, 4, 5… в зависимости от
того, как мы будем продвигаться к старшим классам.
Ну а теперь, когда мы знаем, сколько баллов получает каждый предмет для каждого класса на каждый день, мы
легко найдем их сумму на нашем листе «Баллы». Так для понедельника для 1 класса (ячейка В2) нам нужно
просто найти сумму ячеек Дневная!B2:B8. Ну и так далее. Эта работа наиболее трудоемка, т.к. тут придется
руками прописывать каждую ячейку – обычное заполнение тут не поможет. Так что вбиваем следующую
формулу с учетом корректировок:
=СУММ(Дневная!B2:B8)
Ну и последнее – построим графики недельной нагрузки – они тоже нужны для утверждения расписания в
вышестоящих инстанциях.
Как строить гистограммы, уверен, учить никого не нужно. В итоге мы получаем еще один лист «ГРАФИКИ», на
котором размещаем все диаграммы.
Все! Наша работа завершена! Наводим последний марафет и идем обрадовать вашего завуча!
ЗАКЛЮЧЕНИЕ
Думаю, данная статья позволит многим задуматься о широте возможностей применения Excel в школьной
жизни. Ведь главное предназначение компьютера – упростить нам жизнь. И Excel тому отличное подтверждение.
Допустим, я еще сделал для завуча сводные ведомости успеваемости по классам, которые необходимы ей для
анализа учебной деятельности. Эти ведомости автоматически вычисляют процент успеваемости, качества
знаний, указывают, если в классе имеются отличники или двоечники… Физруку я сделал таблицу – заявку на
соревнования, которая автоматически расставляет учащихся по возрастным группам, а так же, что самое ценное
– автоматически создает индивидуальные карточки для каждого спортсмена. Директору я сделал таблицу
расхода бензина для школьного автобуса. Ну и так, по мелочи – всего не упомнить. И все это можете сделать вы,
и даже больше. В данном случае фантазия почти безгранична, и может упереться лишь в технические аспекты, а
именно, возможность реализовать все это. Но если с этим возникают трудности – тем лучше! Значит, есть еще,
чему учиться, что осваивать. А значит – не стоять на месте.
Желаю всем успехов! С удовольствием отвечу на вопросы и комментарии. Спасибо за внимание!
ПРИЛОЖЕНИЯ. СКРИНШОТЫ
лист «Расписание»
Лист «Учителя»
Лист «Совпадения»
Лист «План»
Лист «Калькуляция»
Лист «Количество часов»
Лист «Нагрузка»
Лист «Дневная»
Лист «Баллы»
Лист «Графики»
Скачать материал

Информатика - еще материалы к урокам: