Задача 6.2. Расшифровка кода группы и номера зачетной книжки студента

Предметная область: извлечение и обработка данных, содержащихся в номере группы и в номере зачетки студента. Элементы предметной области: фамилии, имена и отчества студентов факультета математики и информатики (15 человек, зачисленные в разные годы и на разные специальности), номера зачеток и номера групп.

Отношения:

• код группы представляет собой комбинацию из 6 символов вида:

Б	Б	–	Ц	Ц	Ц
1	2	3	4	5	6

где Б – буква, Ц – цифра. Комбинация букв – это код факультета; цифра в четвертой позиции – это код специальности на факультете; цифра в пятой позиции – последняя цифра года набора студентов в данную группу: цифра в шестой позиции – номер группы в пределах данной специальности;

• результаты кодирования специальностей на факультете математики и информатики (МТ) представлены на рис. 39;

• номер зачетки – пятизначное число, предпоследняя цифра которого означает форму оплаты за обучение: четная – бюджет, нечетная – контракт.

Требования:

• разработать и реализовать табличную модель обработки данных, содержащихся в номере группы, и номере зачетки студента;

• входные данные оформить в соответствии с рис. 39 (ввестичерез пробел фамилию, имя, отчество каждого студента, начальные буквы – прописные (верхний регистр), остальные – строчные (нижний регистр));

• результаты обработки оформить на двух листах: первый лист должен содержать исходные данные, промежуточные преобразования и результаты обработки; второй – только значения согласно рис. 38, полученные в результате преобразования формул первого рабочего листа в значения;

Фамилия, инициалы	Пол	Специальность	Год поступления	Форма оплаты
КЛЮС П.С.	М	080201 – Информатика	2002	Бюджет

Рис. 38. Вид результирующей таблицы для задачи «Расшифровка кода группы и номера зачетной книжки студента»

• присвоить диапазону с данными о специальностях имя ИНФО;

• при вводе номера группы и зачетки разрешить ввод данных заданной длины;

• разбить текст, состоящий из фамилии, имени и отчества, на три столбца, с помощью средства Мастер текстов;

• для автоматического прописывания названия и шифра специальности по ее номеру использовать функцию ВПР,

• выполнить числовое и стилевое форматирование данных.

Методические указания к решению

1. На рабочем листе Исходный набрать данные согласно рис. 38.

	A	B	C
1	Обработка номера группы и номера зачетки
2	Код	Специальность	Шифр
3	1	Информатика	080201
4	2	Прикладная математика	080202
5	3	Социальная информатика	080203
6	4	КС и сети	091505
7	ФИО	Группа	Зачетка
8	Рач Елена Ивановна	МТ-131	32412

Рис. 39. Фрагмент макета таблицы для задачи «Расшифровка кода группы и номера зачетной книжки студента»

2. Разрешить ввод номера группы, равный 6 символам, и ввод номера зачетки в виде пятизначного числа.

• Инструкция по разрешению ввода текста определенной длины

1. выделить диапазон, который требуется проверять при вводе;

2. исполнить команду Данные  Проверка. В открывшемся диалоговом окне Проверка вводимых значений активизировать вкладку Параметры и выбрать значение Длина текста из раскрывающегося списка Тип данных;

3. в списке Значение выбрать отношение, а в поле Данные ввести требуемое ограничение;

4. ввести тексты сообщений об ошибке; щелкнуть ОК.

3. Разбить текст в столбце Фамилия, имя, отчество на три столбца.

• Инструкция по разделению текста на столбцы

1. выделить диапазон ячеек, содержащих текстовые значения. Выделенная область может содержать любое число строк, но только один столбец. Необходимо, чтобы справа от выбранного столбца имелись один или несколько пустых столбцов или данные, которые будут заменены;

2. в меню Данные выбрать команду Текст по столбцам;

3. следовать инструкциям Мастера текстов для указания способа разбиения текста на столбцы.

4. Сцепить фамилию, записанную прописными буквами, и инициалы в один текстовый элемент. Приведем два варианта записи соответствующей формулы:

=ПРОПИСН (A8) & “ “ & ЛЕВСИМВ (B8;1) & “.” & ЛЕВСИМВ (C8;1) & “.”

Второй вариант формулы представлен с помощью средства Мастер функций (рис. 40)

Рис. 40. Образец ввода формулы с функцией СЦЕПИТЬ

5. Для определения пола, специальности, года поступления и формы оплаты извлечь соответствующие символы из отчества, кода группы и номера зачетки, используя функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР. Например, формула для возвращения четвертого символа номера группы:

=ЗНАЧЕН⁵ (ПСТР (D8;4;1))

6. Используя символы, полученные с помощью функций ЛЕВСИМВ, ПРАВСИМВ, ПСТР, записать формулы для определения пола, года поступления и формы оплаты обучения.

7. Записать формулы для автоматического прописывания названия и шифра специальности по ее номеру с использованием функции ВПР.

8. Прописать шифр и название специальности в одной ячейке, соединив их с помощью функции СЦЕПИТЬ.

9. Выполнить числовое и стилевое форматирование данных.

10. Создать копию листа Исходный и переименовать его в Выходной.

11. Преобразовать в значения все формулы листа Выходной, удалить лишние диапазоны ячеек в соответствии с рис. 37.

• Инструкция по преобразованию формул в значения

1. выделить данные всего листа и скопировать их в буфер;

2. исполнить команды Правка  Специальная вставка.

3. в диалоговом окне Специальная вставка в группе Вставить установить переключатель значения и нажать ОК.