Задание 2. 1. Создать таблицу - экзаменационную ведомость.

Лабораторная работа №3

Задание 1

1. Создать таблицу - экзаменационную ведомость.

2. Заполните ячейки столбцов В и С данными о студентах учебных групп приблизительно 10 строк. Если Вы набрали фамилии не в алфавитном порядке, то отсортируем их в алфавитном порядке для этого:

- выделяете ячейки В6: С(номер последней заполненной строки);

- на панели инструментов выбираете кнопку Сортировка по возрастанию.

6. Присвойте студенту порядковый номер:

- введите в ячейку А6 число 1;

- установите курсор в нижний правый угол ячейки A6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер, выберите пункт Прогрессия:

расположение -по столбцам;

тип -арифметическая;

шаг -1;

- нажмите кнопку OK.

7. Выделите ячейки вашей таблицы, выберите Формат, Ячейки..., Граница

- выберите линию для обрамления и укажите обрамление слева, справа, снизу, сверху.

- нажмите кнопку OK.

8. Просмотрите результаты.

Задание 2

Рассчитать:

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

- скопируйте несколько раз этот шаблон на другие листы и проведите коррекцию оценок по каждому предмету;

- на новом листе создайте ведомость стипендии, куда скопируйте список группы из экзаменационной ведомости;

- введите формулу начисления стипендии по условию, где используется ее базовое значение (минимальный размер стипендии).

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

1. Для подсчета количества разных оценок в группе необходимо использовать дополнительно для каждого вида столбцы: F (для пятерок), G (для четверок), H (для троек), I (для двоек), J (для неявок).

2. Воспользуйтесь мастером функций для задания исходных формул. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:

- установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций;

- в 1-м диалоговом окне выберите вид функции:

категория -логические;

имя функции -если.

- щелкните по OK.

- во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите:

логическое выражение: D6=5;

значение, если истина - 1.

значение, если ложно - 0.

- щелкните по кнопке OK.

3. С помощью мастера функций введите формулы аналогичным в остальные ячейки данной строки.

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

- выделите блок ячеек F6: J6;

- установите курсор в правый нижний угол выделенного блока и, нажав правую кнопку мыши, протащите ее до конца вашей таблицы.

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

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

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

- в диалоговом окне в строке имя ввести слово «отлично»;

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

- проводя аналогичные действия с остальными столбцами, вы создадите еще несколько имен блоков ячеек: хорошо, удовлетворительно, неудовлетворительно, неявка.

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

- установите курсор на названии столбцов и выделите столбцы F-J.

- выберите Формат, Столбец, Скрыть.

7.Введите названия итогового количества полученных оценок в группе в столбец A: отлично, хорошо, удовлетворительно, неудовлетворительно, неявка, итого.

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

- установите указатель мыши в клетку подсчета количества отличных оценок;

- щелкните на кнопку Мастер функций;

- в диалоговом окне 1 выбрать:

категория - математические;

функция - СУММ;

щелкнуть по кнопке OK.

- в диалоговом окне 2 в строке Число1 установить курсор и выбрать Вставка, Имя, Вставить;

- в диалоговом окне выделить имя блока ячеек Отлично, щелкнуть на кнопке OK;

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

9. Сохраните таблицу.

10. Переименуйте текущий лист:

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

-выберите параметр Переименовать и введите новое имя, например Экзамен 1.

11. Скопируйте несколько раз текущий лист:

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

- выберите параметр Скопировать, поставьте флажок Создать копию и выберите лист перед которым Вы хотите поместить копию. Нажмите клавишу OK.

Обратите внимание на автоматическое наименование ярлыков новых листов.

12. Создайте новый лист Стипендия, на который из столбцов A и B листа Экзамен 1 скопируйте фамилии и порядковые номера студентов. Оформите ведомость назначения на стипендию:

13. Введите формулу в ячейку C5 для вычисления среднего балла студента, щелкните на кнопке Мастер функций и выберите в диалоговом окне параметры:

категория -статистическая;

имя -СРЗНАЧ;

щелкните по кнопке OK.

- установите курсор в 1-й строке, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой первого студента по первому экзамену;

- установите курсор во 2-й строке, щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой первого студента по второму экзамену;

- установите курсор в 3-й строке, щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой первого студента по третьему экзамену.

- щелкните по кнопке OК.

- в ячейке C5 появится значение рассчитанное по формуле:

= СРЗНАЧ (‘Экзамен’! D6; ‘Экзамен (2)’! D6; ‘Экзамен (3)’! D6;)

- скопируйте формулу по всем ячейкам столбца С.

14. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов:

=СЧЕТ (‘Экзамен’! D6; ‘Экзамен (2)’! D6; ‘Экзамен (3)’! D6;);

15. Введите формулу для вычисления размера стипендии студента, например в ячейку E5, используя минимальное значение стипендии и учитывая, что сданы все экзамены по условию: если средний балл не менее 4, 5, выплачивается 50% надбавка к минимальной стипендии; если средний балл от 4 до 4, 5 включительно выплачивается минимальная стипендия; если средний балл меньше 3, стипендия не выплачивается.

= Если (и (С5> =4, 5; D5=3); $D$2*1, 5; Если (и (С5> =4; D5=3); $D$2; 0)).

16. Скопируйте эту формулу в другие ячейки столбца Е.

17.Подсчитайте сумму стипендиального фонда для всей группы, например в ячейке Е15.

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

19. Сохраните книгу.