Лабораторная работа №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. Сохраните рабочую книгу с экзаменационными ведомостями (она понадобится для следующего задания).