Лабораторная работа №1. Технология работы с формулами на примере подсчета количества разных оценок в группе в экзаменационной ведомости.

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

ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
Группа__________ Дисциплина_________________

No п/п Фамилия, имя, отчество No зачетной книжки Оценка Подпись экзаменатора
    
    
    
    
 «отлично» ____________________________________
 «хорошо» _____________________________________
 «удовлетворительно»____________________________
 «неудовлетворительно»__________________________
 «неявки»_______________________________________
 ИТОГО ________________________________________

Рис.1. Форма экзаменационной ведомости


В созданной рабочей книге с экзаменационной ведомостью рассчитайте:
 количество оценок (отлично, хорошо, удовлетворительно, неудовлетворительно, неявок), полученных в данной группе;
 общее количество полученных оценок.
Технология выполнения.
1. Введите в cоответствующие ячейки тексты заголовка и шапки таблицы в соответствии с рисунком.
2. Отформатируйте ячейки А1:Е1 и A3:E3:
 выделите блок ячеек, нажмите правую кнопку мыши для вызова контекстного меню;
 введите команду контекстного меню Формат ячеек;
 на вкладке выравнивание выберите опции: По горизонтали: по центру выделения. По вертикали: по верхнему краю
 нажав кнопку <Размер>, выберите размер шрифта, например 14 пт;
 при необходимости выделите текст жирным шрифтом, нажав на панели инструментов кнопку <Ж>.
3. Cформируйте шапку таблицы, задав параметры выравнивания вводимого текста:
 выделите блок ячеек А5:J5, где располагается шапка таблицы;
 вызовите контекстное меню и выберите команду ;
 на вкладке Выравнивание задайте параметры: По горизонтали: по значению. По вертикали: по верхнему краю. Переносить по словам: поставить флажок. Ориентация: горизонтальный текст (по умолчанию).
- нажмите кнопку <OK>.
4. Установите ширину столбцов таблицы в соответствии с рисунком. Для этого:
 подведите указатель мыши к правой черте клетки с именем столбца, например В, так, чтобы указатель изменил свое изображение;
 нажмите левую кнопку мыши и, удерживая ее, протащите мышь так, чтобы добиться нужной ширины столбца или строки.
 аналогичные действия проделайте с остальными столбцами.
5. Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10 — 15 строк (фамилии и оценки). Отформатируйте данные.
6. Присвойте каждому студенту порядковый номер:
 введите в ячейку А6 число 1;
 установите курсор в нижний правый угол ячейки А6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить.
7. После списка студентов в нижней части таблицы согласно рисунку введите в ячейки столбца А текст итоговых строк: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО.
8. Объедините две соседние ячейки для более удобного представления текста итоговых строк. Технологию объединения покажем на примере объединения двух ячеек столбцов А и В, в которых будет расположена надпись Отлично:
 выделите две ячейки;
 вызовите контекстное меню и выберите команду Формат ячеек;
 на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку <ОК>;
 аналогичные действия проделайте с остальными ячейками, где хранятся названия итоговых ячеек.
9. Сохраните рабочую книгу, для которой файл будет иметь тип xls:
 выполните команду Файл, Сохранить как;
 в диалоговом окне установите следующие параметры: Папка: имя вашего каталога. Имя файла: Session1. Тип файла: Книга Microsoft Excel.
10. Введите названия (5,4,3,2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов.
11. В эти столбцы F – J введите вспомогательные формулы. Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1. Например, студент Снегирев получил оценку 5, тогда в ячейке F6 должна стоять 1, а в остальных вспомогательных столбцах G – J в данной строке – 0.
Для ввода исходных формул воспользуйтесь Мастером функций. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:
 установите курсор в ячейку F6 и выберите мышью на панели управления инструментов кнопку Мастера функций;
 в 1-м диалоговом окне выберите вид функции:
Категория – логические. Имя функции – ЕСЛИ.
 щелкните по кнопке <OK>;
 во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции:
Логическое выражение – D6=5. Значение, если истина, – 1.
Значение, если ложно, – 0.
 щелкните по кнопке <OK>
Примечание. Для ввода адреса ячейки в строку наберите его сами или щелкните в ячейке D6 правой кнопкой мыши.
12. С помощью Мастера функции введите формулы аналогичным способом в остальные ячейки данной строки.
13. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов
 выделите блок ячеей F6 : J6;
 установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протащите ее до конца таблицы Экзаменационная ведомость;
 выберите в контекстном меню команду Заполнить значения
14. Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрим это на примере дополнительного столбца F:
 выделите все значения дополнительного столбца, например F6: <адрес ячейки в столбце, в которой находится последнее значение>;
 введите команду Вставка, Имя, Присвоить;
 в диалоговом окне в строке Имя введите слово ОТЛИЧНО:
 щелкните по кнопке <Добавить>;
 проведите аналогичные действия с остальными столбцами и задайте имена блоков ячеек: ХОРОШО, УДОВЛЕТВОР, НЕУД, НЕЯВКА.
15. Выделите столбцы F - J целиком и сделайте их скрытыми:
 установите курсор на названии столбцов и выделите столбцы F –J;
 введите команду Формат, Столбец, Скрыть.
16. Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Покажем это на примере подсчета количества отличных оценок:
 установите указатель мыши в ячейку С13 подсчета количества отличных оценок;
 щелкните по кнопке Мастер функций;
 в диалоговом окне Мастер функций выберите: Категория — Математические, функция — СУММ; щелкните по кнопке <ОК>;
 в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка, Имя, Вставить;
 в появившемся диалоговом окне выделите имя блока ячеек Отлично, щелкните по кнопке <ОК>;
 повторите аналогичные действия для подсчета количества других оценок в ячейкахС14-С17.
17. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:
 установите курсор в пустой ячейке С18 (рядом с ИТОГО). Эта ячейка должна обязательно находиться под ячейками, где подсчитывались суммы по всем видам оценок;
 щелкните по кнопке <> ;
 выделите блок ячеек, где подсчитывались суммы по всем
видам оценок, и нажмите клавишу <Enter>.
18. Переименуйте текущий лист:
 установите курсор на имени текущего листа, и вызовите контекстное меню;
 выберите параметр Переименовать и введите новое имя, например Экзамен 1.
19. Скопируйте несколько раз текущий лист Экзамен 1:
 установите курсор на имени текущего листа и вызовите контекстное меню;
 выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец, нажмите <ОК>.
20. Сохраните рабочую книгу с экзаменационными ведомостями (она понадобится для следующего задания).

Этап 2.
Подготовьте для группы ведомость (рис.2) назначения студентов на стипендию по результатам экзаменационной сессии.

ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа No_______
Минимальный размер стипендии _____________

No п/п Фамилия, имя, отчество Стипендия
  
  
  

  Итого стипендиальный фонд по группе – -----------------------__________________

Рис. 2. Форма стипендиальной ведомости

Последовательность действий:
1. Загрузите экзаменационную ведомость с оценками по трем предметам (информатика, мат. анализ, культурология ) из Этапа 1.
2. На новом листе создайте ведомость стипендии (см. рис. 2) и скопируйте в нее список группы с порядковыми номерами и номерами зачетных книжек из экзаменационной ведомости предыдущего задания.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
4. Используя минимальное значение стипендии, и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:
 если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
 если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
 если средний балл меньше 3, стипендия не выплачивается.
Обратите внимание, что наличие неявки или двойки за экзамен говорит о том, что экзамен не сдан, и, следовательно, стипендия не начисляется.
5. Подсчитайте сумму стипендиального фонда для всей группы.
Технология выполнения
1. Загрузите рабочую книгу с экзаменационными ведомостями.
2. Создайте в этой книге новый лист — Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.
3. Оформите название и шапку ведомости назначения на стипендию согласно рис.2.(задайте шрифт и тип выделения — полужирный).
4.  Укажите размер минимальной стипендии в ячейке D3;
5.  Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов.
6.  Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента, например Снегирева. Для этого:
 установите курсор в ячейке С6;
 щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:
 Категория: Статистические; Имя: СРЗНАЧ.
 щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;
 установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
 аналогичным образом в качестве параметров Число2 и Число 3 выберите ячейку D6 с оценкой того же студента по второму экзамену и ячейку D6 с оценкой по третьему экзамену;
 щелкните по кнопке <ОК>;
 в ячейке С6 появится значение, рассчитанное по формуле 
СРЗНАЧ('Экзамен 1`!D6;'Экзамен 1 (2)'D6;'Экзамен 1 (3)'D6).
7. Скопируйте формулу по всем ячейкам столбца С.
8.  Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок и двоек, при этом технология ввода будет аналогична описанной в п.6, отличие заключается в использовании функции СЧЁТЕСЛИ. Окончательная формула в ячейке имеет вид:
=СЧЁТЕСЛИ('Экзамен 1'!D6;">2")+СЧЁТЕСЛИ('Экзамен 1 (2)'!D6;">2")+СЧЁТЕСЛИ('Экзамен 1 (3)'!D6;">2").
9. Скопируйте формулу по всем ячейкам столбца D.
10. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула может иметь следующий вид: =ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)) Технология ввода формулы аналогична описанной в п.6. Для ввода вложенных функций можно пользоваться кнопкой вызова функций.
11. Скопируйте эту формулу в другие ячейки столбца Е так же, как вы делали в п. 7 и 9.
12. Проверьте работоспособность таблицы:
 вводите другие оценки в экзаменационные ведомости;
 измените минимальный размер стипендии.
13. Подсчитайте сумму стипендиального фонда для всей группы.

АКТУАЛЬНА НА 21-22 год.
лабораторная работа 1 **.**.2021 Зачет