Описание алгоритмов
создания и оценки правильности решения кроссвордов
в среде MS Excel
для 8 – 11 классов
Автор: Петрашевич Галина Федоровна.
Для создания методического материала учителям – предметникам, а также учащимся
средних школ и гимназий с целью демонстрации алгоритмических возможностей MS
Excel, предлагается данный материал.
Темы урока:
1. Формирование
алгоритмических навыков;
2. Контроль знаний в
интеллектуально - игровой форме.
Предмет:
1. Информатика и ИКТ;
2. Предметы школьной
программы.
Класс / группа:
1.
Классы, в которых изучается тема «Алгоритмизация и программирование» по
предмету «Информатика и ИКТ»;
2.
Классы, в которых проводятся контроль знаний в игровой форме.
Использованные
источники и литература:
1.
Полное руководство MS Office 2007;
2.
Справочный материал по использованию электронной таблицы MS Excel.
Ключевые слова или
опорные понятия:
Информатика,
электронная таблица, алгоритмизация, программирование, алгоритм расчета, логические
операции, функции (формулы) MS Excel, создание и решение кроссворда, схема
кроссворда.
Алгоритмизация задачи
Создания кроссвордов по тематике учебных дисциплин
средней школы, с целью их последующего решения в 8 – 9 классах и оценки
результатов, является популярным, занимательным, игровым и эффективным методом
усвоения учебного материала, как старшеклассниками, так и их младшими
товарищами.
Предлагается два метода создания кроссвордов:
1. Метод
программирования конкретного заранее созданного кроссворда.
2. Универсальный
алгоритм создания и оценки результатов решения кроссвордов без дополнительного
программирования.
В
обеих методах используются одинаковые понятия и термины:
-
область создания кроссворда, в которой формируется кроссворд;
-
область решения кроссворда, в которой ученик в заранее «нарисованную» схему
кроссворда, отвечая на вопросы, вводит буквы слов;
-
область вопросов кроссворда;
-
схема кроссворда. Графическое представление кроссворда, как на рис. 1.
Метод программирования созданного кроссворда.
Придумайте и нарисуйте на бумаге кроссворд,
с пересекающимися в местах одинаковых букв словами.
|
Последовательность создания кроссворда в MS Excel :
|
|
1. В области создания кроссворда рис. 2
введите все слова.
|
|
2. Выделите последовательно слова кроссворда и
нарисуйте границы клеток.
|
|
3. Запишите вопросы кроссворда.
|
|
4. Пронумеруйте слова кроссворда в области
создания в соответствии с номерами вопросов.
|
|
5. Скопируйте область создания кроссворда,
включая левую верхнюю клетку, «захватив» все слова.
|
|
6. Вставьте скопированную зону в область решения
кроссворда рис. 1, начиная также с левой верхней клетки.
|
|
7. В области решения кроссворда удалите все
буквы слов клавишей «Del», оставив только номера слов.
|
|
8. Выделите в области создания кроссворда
зону, охватив все слова, и задайте цвет букв «белый» (невидимый).
|
|
9. Выделите область решения кроссворда и
снимите защиту клеток, которая установилась после копирования.
|
|
10. Защитите лист.
|
|
11. Ваш кроссворд готов для решения.
|
|
Рис.
1 Область решения кроссворда (пример).
|
Для обработки результатов решения кроссворда, в данном
случае в клетки AQ8, AM10, AM12, AT14, AJ16, AL18, AL20, AJ22, AQ24 и AK26 нужно ввести формулы с логическими
операциями дизъюнкции и конкатенации для анализа вводимых при решении
кроссворда букв.
Так, например, формула для клетки AQ8 первого вопроса
имеет вид: =ЕСЛИ(ИЛИ(C14=««;D14=««;E14=««;F14=««;G14=««;H14=««;I14=««;J14=««;K14=««;L14=««;C14=«
«;D14=« «;E14=« «;F14=« «;G14=« «;H14=« «;I14=« «;J14=« «;K14=« «;L14=« «);
BW30;
ЕСЛИ((C14&D14&E14&F14&G14&H14&I14&J14&K14&L14=
BS14&BT14&BU14&BV14&BW14&BX14&BY14&BZ14&CA14&CB14);BW31;BW32))
Дизъюнкция позволяет определить, завершен ли ввод всех
букв слова, а конкатенация дает возможность оценить соответствие введенного
слова заданному слову кроссворда.
Константы заданы в клетках BW30, BW31 и BW 32.
Ниже на рисунке показана область создания кроссворда,
в которой все слова «окрашены» в белый цвет и защищены. При совпадении слова в
обеих областях рассчитываются баллы согласно значениям клеток CF30 - CF32.
На следующем листе представлена область создания
кроссворда с буквами кроссворда белого цвета (невидимыми).
Рис.
2. Область создания кроссворда (пример).
Итоговая
оценка в баллах рассчитывается по формуле:
=ОКРУГЛВВЕРХ(CM29/CM30;0)
где
CM29 - это «Итого баллов», в данном случае 30, CM30 –
количество «Значений» 10.
Формулы
клеток СМ29 и СМ30:
=СУММ(CM8:CM27) и =СЧЁТЗ(CM8:CM27) соответственно.
Универсальный метод создания и оценки
результатов
решения кроссвордов.
Универсальный метод позволяет, используя таблицу MS
Excel с областями создания и решения кроссворда и зоной обработки, создавать и
оценивать результаты решения кроссвордов без дополнительного программирования.
Понятия и термины:
- область создания кроссворда, в которой формируется
кроссворд;
- область ввода вопросов кроссворда;
- область решения кроссворда, в которой ученик в
заранее «нарисованную» схему кроссворда, отвечая на вопросы, вводит буквы слов;
- зона обработки результатов решения кроссворда. Весь
расчет выполнен в данной зоне и выделен в отдельный лист «Обработка».
- схема кроссворда. Графическое представление
кроссворда.
Последовательность
создания кроссворда:
|
1. Снимите защиту листа «Создание».
|
|
2. В области создания кроссворда рис. 3 введите
все слова.
|
|
3. Выделите последовательно слова кроссворда и нарисуйте
границы клеток.
|
|
4. Запишите вопросы кроссворда на листе «Создание». Вопросы
отобразятся также на листе «Решение».
|
|
5. Пронумеруйте слова кроссворда в области
создания в соответствии с номерами вопросов. Используйте только цифры, не
используйте «пробел».
|
|
6. Скопируйте область создания кроссворда
листа «Создание», включая левую верхнюю клетку, «захватив» все слова.
|
|
7. Вставьте скопированную зону в область решения
кроссворда листа «Решение» рис. 4, начиная также с левой верхней клетки.
|
|
8. В области решения кроссворда аккуратно удалите
все буквы слов клавишей «Del», оставив только номера слов.
|
|
9. Выделите в области создания кроссворда
зону, охватив все слова, и задайте цвет букв «белый» (невидимый).
|
|
10. Защитите лист «Создание».
|
|
11.
Ваш кроссворд готов для решения.
|
|
Ограничения
при создании кроссворда.
|
|
Слова кроссворда не должны содержать цифры !
|
|
Номера слов не должны содержать никаких символов,
кроме цифр.
|
|
|
|
|
На
листе «Создание» в области создания кроссворда, включающей 40 строк и 30
колонок, создается кроссворд. 1200 клеток для слов.
Затем «рисуются» клетки букв, вводятся номера слов.
В
области вопросов листа «Создание», которая содержит 15 зон, записываются
вопросы. Вопросы отобразятся также на листе «Решение».
Рис. 3. Область
создания кроссворда и зоны ввода вопросов листа «Создание».
На
листе «Решение» в области решения кроссворда рис. 4 создайте схему кроссворда.
Для этого скопируйте на листе «Создание» область создания кроссворда, включая
левую верхнюю клетку, «захватив» все слова. Вставьте скопированную зону
кроссворда в область решения листа «Решение», начиная также с левой верхней
клетки. Аккуратно удалите все буквы кроссворда в области решения, оставив
только номера слов.
Для
получения оценки в ответ на вопрос «Вы закончили решения кроссворда?» нужно
ввести слово «Да» или «да».
Рис. 4. Область решения
кроссворда листа «Решение».
Для
удаления ошибочно введенной буквы из области решения кроссворда можно использовать
клавишу «Del» или один «пробел».
Расчетные
формулы находятся на листе «Обработка» и защищены.
Во всех 1200 клетках зоны обработки решения кроссворда
рис. 5, введены формулы, отличающиеся только адресами обрабатываемых в формулах
клеток.
Так,
например, в клетке ВS4 находится формула:
=ЕСЛИ(И(AI4>0;AI4<100);»«;ЕСЛИ(ИЛИ(AI4=««;AI4=« «); ЕСЛИ(C4<>AI4;ЕСЛИ(ИЛИ(C4=««;C4=«
«);»«;$BV$49);»«); ЕСЛИ(AI4=C4;$BV$46;ЕСЛИ(ИЛИ(C4=««;C4=« «);$BV$48;$BV$47))))
В
клетке CV4 находится формула:
=ЕСЛИ(И(BL4>0;BL4<100);»«;ЕСЛИ(ИЛИ(BL4=««;BL4=« «);ЕСЛИ(AF4<>BL4;ЕСЛИ(ИЛИ(AF4=««;AF4=«
«);»«;$BV$49);»«);ЕСЛИ(BL4=AF4;$BV$46;ЕСЛИ(ИЛИ(AF4=««;AF4=« «);$BV$48;$BV$47))))
Рис.
5. Зона обработки решения кроссворда.
В клетках BV46 – BV49 находятся признаки видов
событий обработки результатов решения кроссворда.
Адрес клетки
|
Признак
|
|
Виды
событий результата обработки
|
BV46
|
1
|
Правильно указанная буква кроссворда.
|
BV47
|
1000
|
Неправильно указанная буква.
|
BV48
|
1000000
|
Отсутствие буквы.
|
BV49
|
1000000000
|
Буква вне зоны слова.
|
Такие
значения признаков, при условии общего количества каждого вида события не более
999, позволяют суммировать виды событий построчно с последующим расчетом
количества каждого вида события методом деления на значение признака и
вычитания количества, ранее рассчитанных видов событий, начиная с большего по
значению признака.
Таким
образом, при методе построчного суммирования видов событий и указанных выше
значениях признаков, размер кроссворда составит до 999 столбцов и
неограниченное количество строк, что, по сути, является избыточным.
Практически
достаточным будет метод суммирования видов событий всей зоны обработки решения
кроссворда BS4:CV43, при котором ограничением будет общее количество
букв кроссворда не более 999. При средней длине слова 20 букв, максимальным
количеством слов кроссворда будет 50, что вполне достаточно.
Краткое описание алгоритма решения задачи.
Если клетка области создания ПУСТАЯ, тогда:
|
если в соответствующей клетке области решения ПУСТО, тогда в
соответствующей клетке зоны обработки тоже запишем ПУСТО (««).
|
если в соответствующей клетке области решения БУКВА, тогда эта буква
ВНЕ зоны, и в соответствующей клетке зоны обработки запишем признак «1000000000»
(вне зоны).
|
Если в клетке области создания БУКВА, тогда:
|
если в соответствующей клетке области решения такая же БУКВА, тогда в
соответствующей клетке зоны обработки запишем признак «1» (правильно).
|
если в соответствующей клетке области решения ДРУГАЯ БУКВА, тогда в
соответствующей клетке зоны обработки запишем признак «1000» (неправильная
буква).
|
если в соответствующей клетке области решения ПУСТО (нет буквы),
тогда в соответствующей клетке зоны обработки запишем признак «1000000»
(отсутствие буквы).
|
В расчетной части зоны обработки решения кроссворда,
признаки суммируются и дифференцируются по видам событий.
В прямоугольной зоне клеток с адресами с CX4 по
DA43 находятся четыре вида формул.
Так в клетке CX4 введена формула:
=ЦЕЛОЕ(СУММ(BS4:CV4)/$BV$49)
В клетке CY4 введена формула:
=ЦЕЛОЕ((СУММ(BS4:CV4)-CX4*$BV$49)/$BV$48)
В клетке CZ4 введена формула:
=ЦЕЛОЕ((СУММ(BS4:CV4)-CX4*$BV$49-CY4*$BV$48)/$BV$47)
В клетке DA4 введена формула:
=ЦЕЛОЕ((СУММ(BS4:CV4)-CX4*$BV$49-CY4*$BV$48-CZ4*$BV$47))
Формулы расчета видов событий по всей зоне обработки
решения кроссворда BS4:CV43
CX47 =ЦЕЛОЕ(СУММ(BS4:CV43)/$BV$49)
CY47 =ЦЕЛОЕ((СУММ(BS4:CV43)-CX47*$BV$49)/$BV$48)
CZ47 =ЦЕЛОЕ((СУММ(BS4:CV43)-CX47*$BV$49-CY47*$BV$48)/$BV$47)
DA47 =ЦЕЛОЕ((СУММ(BS4:CV43)-CX47*$BV$49-CY47*$BV$48-
CZ47*$BV$47))
При
данном методе расчета количество букв кроссворда не должно превышать 999 !
Все формулы
таблицы расчета видов событий зоны обработки решения кроссворда приведены на
рис. 6.
В
зависимости от количества правильно указанных букв кроссворда рассчитывается
итоговая оценка и выводится комментарий к ней.
Варианты
оценок решения кроссворда:
|
Кроссворд решен полностью
Отлично.
|
Кроссворд решен более чем на 75 %
Хорошо.
|
Кроссворд решен более чем на 50 %
Удовлетворительно.
|
Кроссворд решен менее чем наполовину
Пока плохо.
|
Комментарии оценки :
«Молодец».
«Осталось
немного и кроссворд будет решен».
«Больше
половины кроссворда уже решено».
«Постарайся
ответить на вопросы кроссворда».
Рекомендации по отладке сложных формул.
Для пошагового вычисления формул нужно выделить часть формулы в
строке формул и нажать F9.
Вместо
выделенной части формулы будет показано вычисленное значение.
Для переключения
таблицы между значениями формул и формулами нужно:
- на вкладке
Формулы,
- в группе
Зависимости формул,
- кликнуть
команду-переключатель «Показать формулы»
Для
показа и пошагового вычисления формул можно использовать команду:
Вычислить
формулу
Заключение.
Предложенное решение задачи в среде MS Excel призвано продемонстрировать широкие
возможности редактора «Электронная таблица» по алгоритмизации и
программированию.
Решение задач в среде MS Excel формирует навыки логического мышления,
математического подхода и искусства программирования.
MS Excel является мощным инструментом для
развития математических и логических способностей учащихся школ и гимназий.
Имена
файлов-таблиц MS Excel для работы с кроссвордами:
Кроссворд универсальный –
ПГФ.xls
Кроссворд по информатике –
ПГФ.xls
Кроссворд универсальный -
пример – ПГФ.xls
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.