Технология работы

1. Загрузите Excel и введите в указанные ниже ячейки текст заголовка и шапку таблицы:

2. Отформатируйте название и шапку таблицы по своему усмотрению.

3. Заполните ячейки столбцов B, C и D данными о студентах учебной группы (см. Рис 3.11).

4. Для подсчета количества разных оценок в группе необходимо использовать дополнительно для каждого вида оценки столбцы: F (для пятерок), G (для четверок), H (для троек), I (для двоек), J (для неявок). В эти столбцы введите вспомогательные формулы. Логика работы формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего дополнительного столбца как 1. По остальным ячейкам данной строки в дополнительных столбцах устанавливается 0.

5. С помощью Мастера функций введите следующие формулы в указанные ниже ячейки:

6. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов.

7. Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрим это на примере дополнительного столбца F:

· выделите все значения дополнительного столбца (от F6 до последней заполненной строки);

· введите команду Вставка, Имя, Присвоить;

· в диалоговом окне в строке «Присвоение имени» ввести слово ОТЛИЧНО;

· щелкнуть по кнопке «Добавить»;

· аналогично создайте еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.

8. Выделите столбцы F – J целиком и сделайте их скрытыми:

· выделите столбцы F – J целиком;

· введите команду ФОРМАТ, Столбец, Скрыть.

9. Введите названия итогового количества полученных оценок в группе в столбец B начиная со строки 15 : Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, Итого (см. рис. 3.11).

10. Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Покажем это на примере подсчета количества отличных оценок

· установить курсор в ячейку подсчета количества отличных оценок;

· вызвать Мастер функций и выбрать имя функции – СУММ;

· в диалоговом окне в строке ЧИСЛО 1 установить курсор и ввести команду ВСТАВКА, Имя, Вставить;

· в диалоговом окне выделить имя блока ячеек «Отлично», ОК;

· повторить аналогичные действия для подсчета количества других оценок;

· подсчитайте общее количество (ИТОГО) всех полученных оценок.

11. Переименуйте текущий лист в Экзамен 1.

12. Скопируйте текущий лист Экзамен 1 и переименуйте в Экзамен 2. Измените название дисциплины и проведите коррекцию оценок по этому предмету:

· вызовите контекстное меню имени текущего листа;

· выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию.

13. Переименуйте Лист2 в лист Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов. В ячейку B2 введите «Минимальный размер стипендии», а в ячейку D2 введите 150 (это минимальный размер стипендии). Оформите ведомость назначения на стипендию в соответствии с рисунком, приведенным ниже.

14. Введите формулу в ячейку С5 на листе Стипендия для вычисления среднего балла студента: =СРЗНАЧ('Экзамен 1'!D6;'Экзамен 1(2)'!D6), используя Мастер функций (восклиц. знак означает признак листа). Скопируйте эту формулу в другие ячейки столбца. Если у вас более двух экзаменационных ведомостей, то отредактируйте вышеприведенную формулу.

15. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок с помощью формулы: =СЧЁТ('Экзамен 1'!D6;'Экзамен 1(2)'!D6), используя Мастер функций. Если у вас более двух экзаменационных ведомостей, то отредактируйте вышеприведенную формулу.

16. Введите формулу для вычисления размера стипендии студента в ячейку Е5 на листе Стипендия(размер стипендии зависитот средней оценки и от числа сданных экзаменов, поэтому используется логическая функция И: =ЕСЛИ(И(C5>=4,5;D5=2);$D$2*1,5;ЕСЛИ(И(C5>=3;D5=2);$D$2;0), используя Мастер функций. Если у вас более двух экзаменационных ведомостей, то отредактируйте вышеприведенную формулу. Скопируйте эту формулу в другие ячейки столбца Е.

17. Проверьте работоспособность таблицы, вводя другие оценки в ведомости. Измените минимальный размер стипендии.

18. Используя Условное форматирование (команда ФОРМАТ), оформите столбец Стипендия следующим образом:

· повышенная стипендия должна быть оформлена зеленым цветом, полужирным курсивом;

· минимальная стипендия – синим цветом, курсивом;

· стипендия, равная 0, – красным цветом.

Выполненную работу сохраните в своей папке.

Дополнительное задание:

Для оплаты за обучение используется рассчитанный средний балл по результатам сдачи экзаменов по каждому студенту. При этом, учитывая, что сданы все экзамены, соблюдаются условия:

• если средний балл не менее 4.5 и сданы все экзамены, то оплата за обучение в следующем семестре уменьшается на 10% от базовой оплаты на текущий год;
• если средний балл от 3 до 4.5 (включительно) и сданы все экзамены, то оплата за обучение равна базовой;
• если средний балл меньше 3, а также имеются задолженности и неявки, то оплата за обучение увеличивается на 10% от базовой.

Примечание. Базовую оплату взять свою.

Требуется подготовить для группы ведомость оплаты за обучение в следующем семестре студентами по результатам экзаменационной сессии.