Класс: 10
Оборудование: Microsoft Excel, файл code.xls.
Место в учебном плане: тема “Кодирование информации”.
Практическая работа
Цели: расширить представление о стандартных функциях программы Excel; научиться применять функции ссылок и поиска значений при решении задач на обработку массива данных, а также функции проверки свойств и значений.
Задание 1. Число судьбы.
“Все вещи можно представить в виде чисел”, - считал Пифагор. Найдя числовое значение своего имени, можно определить число судьбы.
Каждой букве соответствует число:
| А, И, Й, Ы, Ь, Ъ – 1 | Д, М, Т – 4 | З, О, Ч, Ю – 7 |
| Б, Ж, К, Л, Р, Ш, Я – 2 | Е, Ё, Н, Х - 5 | П, Ф – 8 |
| Г, С, Ц – 3 | В, У, Э - 6 | Щ – 9 |
Каждое имя посредством сложения может быть сведено к одному числу, которое и считается выражением сущности человека.
Например, Иванова Анна = (1+6+1+5+7+6+1) + (1+5+5+1) = 39 > 3 + 9 = 12 > 1 + 2 = 3
Составьте таблицу для подсчета числа судьбы (рис. 1).
Комментарии
Расположение таблицы: файл code.xls, лист: 1. Код имени.
Дополнительные сведения: файл code.xls, лист: 1.Интерпретация.
Используемые стандартные функции: ГПР, ВПР, ЕПУСТО, ЕСЛИ, (НЕ), ЛЕВСИМВ, ПРАВСИМВ
Рис. 1. Пример оформления листа 1. Код имени
Таблица 1. Данные в ячейках таблицы Код имени
| Ячейка / Диапазон | Содержимое | Примечание |
| В1:AH1 | буквы русского алфавита (заглавные) | |
| В2:AH2 | число, соответствующее букве | |
| В5:AH5 | формулы, которые по значениям в соответствующих ячейках диапазона В4:AH4 выводит количество баллов за букву | формула в ячейке В5: =ЕСЛИ(НЕ(ЕПУСТО(B4)); ГПР(B4;$B$1:$AH$2;2);"") |
| В7 | формула подсчета общего количества баллов | =СУММ(B5:AH5) |
| В8 | формула нахождения суммы цифр двузначного числа из ячейки В7 | =ЛЕВСИМВ(B7;1)+ПРАВСИМВ(B7;1) |
| В9 | формула, по которой находится сумма цифр числа из ячейки В8, если оно больше 9 или дублируется число из ячейки В8 в противном случае | =ЕСЛИ(B8>9;ЛЕВСИМВ(B8;1) + ПРАВСИМВ(B8;1);B8) |
| D8 | формула, по которой выдается комментарий (данные на листе 1.Интерпретация) в зависимости от значения ячейки В9 и только в том случае, если в В9 есть значение | =ЕСЛИ(ЕПУСТО(B9);""; ВПР(B9;'1.Интерпретация'!A2:AG10; 2; ЛОЖЬ)) |
Оформление таблицы:
- А8 – содержит результат промежуточных вычислений; скрыть значение, выбрав белый цвет шрифта;
- D8 – объединение ячеек диапазона D8:AH9;
- для объединенной ячейки задать опцию Переносить по словам (на ленте Главная в разделе Выравнивание дополнительное диалоговое окно Формат: Выравнивание);
- добавить границы на все исходные и производные данные;
- залить весь лист белым цветом.
Задание 2. Дешифровка сообщения
В верхней части листа 2. Дешифровка задана кодовая таблица. Составьте таблицу дешифровки сообщения согласно заданному коду.
Рис. 2. Пример оформления листа 2. Дешифровка
Комментарии
Расположение таблицы с исходными данными: файл code.xls, лист: 2. Дешифровка.
Используемые стандартные функции: ГПР, ЕСЛИ, ЕЧИСЛО.
Таблица 2. Данные в ячейках таблицы Дешифровка
| Ячейка / Диапазон | Содержимое | Примечание |
| В1:АN2 | код шифра | |
| В4:АN7 | зашифрованное сообщение | |
| В8:АN11 | однотипные формулы, позволяющие расшифровать любое сообщение из допустимых символов | допустимые символы: заглавные буквы русского алфавита, “?” (вопросительный знак), “!” (восклицательный знак), “,” (запятая), пробел между словами, “-” (тире, дефис), “.” (точка), “”” (кавычки) |
Оформление таблицы:
- добавить границы на все исходные и производные данные;
- залить весь лист белым цветом;
- залить шифровку и дешифровку разными цветами.
Задание 3. Кодирование текста
Составьте на листе Шифровка таблицу шифровки сообщения согласно заданному коду.
Рис. 3. Пример оформления листа 3. Шифровка
Комментарии
Расположение таблицы с исходными данными: файл code.xls, лист: 3. Шифровка.
Используемые стандартные функции: ГПР, ЕСЛИ, ЕПРУСТО, (НЕ).
Таблица 2. Данные в ячейках таблицы Шифровка
| Ячейка / Диапазон | Содержимое | Примечание |
| В1:АN2 | код шифра | |
| В4:АN7 | зашифрованное сообщение | |
| В8:АN11 | однотипные формулы, позволяющие зашифровать любое сообщение из допустимых символов | допустимые символы: заглавные буквы русского алфавита, “?” (вопросительный знак), “!” (восклицательный знак), “,” (запятая), пробел между словами, “-” (тире, дефис), “.” (точка), “:” (двоеточие) |
Оформление таблицы:
- добавить границы на все исходные и производные данные;
- залить весь лист белым цветом;
- залить шифровку и дешифровку разными цветами.
Задание 4. Знаки зодиака
Используя данные листа Зодиак (рис. 4), на листе Запрос построить таблицу (рис. 5).
Рис. 4. Фрагмент таблицы на листе 4. Зодиак
Рис. 5. Пример оформления листа 4. Запрос
Комментарии
Расположение таблицы: файл code.xls, лист: 4. Запрос.
Дополнительные сведения: файл code.xls, лист: 4. Зодиак.
Используемые стандартные функции: ВПР, ЕСЛИ, ЕЧИСЛО, И, ДАТА.
Рекомендуется на листе Зодиак диапазону $A$1:$M$14 задать имя Гороскоп.
Таблица 4. Данные в ячейках таблицы Запрос
| Ячейка / Диапазон | Содержимое | Примечание |
| В2:В3 | исходные данные | номер месяца и дня рождения |
| В7:В17 | исходные данные | знак “+” |
| B4 | перевод месяца и числа даты рождения (ячейки В2:В3) в соответствующую дату 2000 года (2000 год выбран потому, что на листе Зодиак все даты относятся к этому году); значение выводится только в том случае, если в ячейках В2:В3 – числа (например, 4 месяц и 21 число) | установить для ячейки формат Дата Формула1: =ЕСЛИ(И(ЕЧИСЛО($B$2); ЕЧИСЛО($B$3)); ДАТА(2000;$B$2;$B$3); "") |
| С7 | формула, по которой в зависимости от даты, полученной в ячейке В4 ставится в соответствие элемент из третьего столбца таблицы на листе Зодиак; заполняется в том случае, если в ячейке В4 – число, а в ячейке В7 – стоит знак “+” | рекомендуется на листе Зодиак диапазону
$A$1:$M$14 дать имя Гороскоп Формула 2: =ЕСЛИ(И(ЕЧИСЛО($B$4);B7="+"); ВПР($B$4;Гороскоп;3); "") |
| С8-С17 | формулы, подобные формуле в ячейке С7 |
СПРАВКА
- Функции из раздела “Ссылки и массивы”
- ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр) - ищет значение в крайнем левом столбце указанной таблицы и возвращает значение в той же строке из указанного столбца таблицы.
- Буква “В” в имени функции ВПР означает “вертикальный”.
Примечания.
- Значения в первом столбце аргумента “таблица” могут быть текстовыми строками, числами или логическими значениями.
- Текстовые строки сравниваются без учета регистра букв.
- Интервальный_просмотр – значение логического типа, которое определяет, нужно ли, чтобы ВПР искала точное (при ЛОЖЬ) или приближенное (ИСТИНА или пропущено)соответствие.
- Если Интервальный_просмотр ИСТИНА, то значения в первом столбце таблицы должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА.
- Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.
- Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента “таблица”, то функция ВПР возвращает значение ошибки #Н/Д.
- Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д.
- ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр) - ищет значение в верхней строке таблицы или массива значений и возвращает значение в том же столбце из заданной строки таблицы.
- Буква Г в ГПР означает “горизонтальный”.
- Функция ГПР используется вместо функции ВПР, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже.
- Функции проверки свойств и значений
- ЕПУСТО(ссылка) – логическая функция, принимает значение ИСТИНА, если указанная ячейка пуста и ЛОЖЬ, в противном случае.
- ЕЧИСЛО(ссылка) – логическая функция, принимает значение ИСТИНА, если в указанной ячейке записано число и ЛОЖЬ, в противном случае.