Лабораторная работа №1. Технология работы с формулами на примере подсчета количества разных оценок в группе в экзаменационной ведомости.
Задание.
Сформируйте структуру таблицы (рис. 1) и заполните ее постоянными значениями (подпись экзаменатора ставить не надо).
ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
Группа__________ Дисциплина_________________
№ п/п
|
Фамилия, имя, отчество
|
№ зачетной книжки
|
Оценка
|
Подпись экзаменатора
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
«отлично» ____________________________________
«хорошо» _____________________________________
«удовлетворительно»____________________________
«неудовлетворительно»__________________________
«неявки»_______________________________________
ИТОГО ________________________________________
Рис.1. Форма экзаменационной ведомости
В созданной рабочей книге с экзаменационной ведомостью рассчитайте:
количество оценок (отлично, хорошо, удовлетворительно, неудовлетворительно, неявок), полученных в данной группе;
общее количество полученных оценок.
Технология выполнения.
Введите в cоответствующие ячейки тексты заголовка и шапки таблицы в соответствии с рисунком.
Отформатируйте ячейки А1:Е1 и A3:E3:
выделите блок ячеек, нажмите правую кнопку мыши для вызова контекстного меню;
введите команду контекстного меню Формат ячеек;
на вкладке выравнивание выберите опции: По горизонтали: по центру выделения. По вертикали: по верхнему краю
нажав кнопку <Размер>, выберите размер шрифта, например 14 пт;
при необходимости выделите текст жирным шрифтом, нажав на панели инструментов кнопку <Ж>.
Cформируйте шапку таблицы, задав параметры выравнивания вводимого текста:
выделите блок ячеек А5:J5, где располагается шапка таблицы;
вызовите контекстное меню и выберите команду ;
на вкладке Выравнивание задайте параметры: По горизонтали: по значению. По вертикали: по верхнему краю. Переносить по словам: поставить флажок. Ориентация: горизонтальный текст (по умолчанию).
Установите ширину столбцов таблицы в соответствии с рисунком. Для этого:
подведите указатель мыши к правой черте клетки с именем столбца, например В, так, чтобы указатель изменил свое изображение;
нажмите левую кнопку мыши и, удерживая ее, протащите мышь так, чтобы добиться нужной ширины столбца или строки.
аналогичные действия проделайте с остальными столбцами.
Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10 — 15 строк (фамилии и оценки). Отформатируйте данные.
Присвойте каждому студенту порядковый номер:
введите в ячейку А6 число 1;
установите курсор в нижний правый угол ячейки А6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить.
После списка студентов в нижней части таблицы согласно рисунку введите в ячейки столбца А текст итоговых строк: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО.
Объедините две соседние ячейки для более удобного представления текста итоговых строк. Технологию объединения покажем на примере объединения двух ячеек столбцов А и В, в которых будет расположена надпись Отлично:
выделите две ячейки;
вызовите контекстное меню и выберите команду Формат ячеек;
на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку <ОК>;
аналогичные действия проделайте с остальными ячейками, где хранятся названия итоговых ячеек.
Сохраните рабочую книгу, для которой файл будет иметь тип xls:
выполните команду Файл, Сохранить как;
в диалоговом окне установите следующие параметры: Папка: имя вашего каталога. Имя файла: Session1. Тип файла: Книга Microsoft Excel.
Введите названия (5,4,3,2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов.
В эти столбцы F – J введите вспомогательные формулы. Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1. Например, студент Снегирев получил оценку 5, тогда в ячейке F6 должна стоять 1, а в остальных вспомогательных столбцах G – J в данной строке – 0.
Для ввода исходных формул воспользуйтесь Мастером функций. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:
установите курсор в ячейку F6 и выберите мышью на панели управления инструментов кнопку Мастера функций;
в 1-м диалоговом окне выберите вид функции:
Категория – логические. Имя функции – ЕСЛИ.
щелкните по кнопке ;
во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции:
Логическое выражение – D6=5. Значение, если истина, – 1.
Значение, если ложно, – 0.
Примечание. Для ввода адреса ячейки в строку наберите его сами или щелкните в ячейке D6 правой кнопкой мыши.
С помощью Мастера функции введите формулы аналогичным способом в остальные ячейки данной строки.
Скопируйте эти формулы во все остальные ячейки дополнительных столбцов
выделите блок ячеей F6 : J6;
установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протащите ее до конца таблицы Экзаменационная ведомость;
выберите в контекстном меню команду Заполнить значения
Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрим это на примере дополнительного столбца F:
выделите все значения дополнительного столбца, например F6: <адрес ячейки в столбце, в которой находится последнее значение>;
введите команду Вставка, Имя, Присвоить;
в диалоговом окне в строке Имя введите слово ОТЛИЧНО:
щелкните по кнопке <Добавить>;
проведите аналогичные действия с остальными столбцами и задайте имена блоков ячеек: ХОРОШО, УДОВЛЕТВОР, НЕУД, НЕЯВКА.
Выделите столбцы F - J целиком и сделайте их скрытыми:
установите курсор на названии столбцов и выделите столбцы F –J;
введите команду Формат, Столбец, Скрыть.
Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Покажем это на примере подсчета количества отличных оценок:
установите указатель мыши в ячейку С13 подсчета количества отличных оценок;
щелкните по кнопке Мастер функций;
в диалоговом окне Мастер функций выберите: Категория — Математические, функция — СУММ; щелкните по кнопке <ОК>;
в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка, Имя, Вставить;
в появившемся диалоговом окне выделите имя блока ячеек Отлично, щелкните по кнопке <ОК>;
повторите аналогичные действия для подсчета количества других оценок в ячейкахС14-С17.
Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:
установите курсор в пустой ячейке С18 (рядом с ИТОГО). Эта ячейка должна обязательно находиться под ячейками, где подсчитывались суммы по всем видам оценок;
выделите блок ячеек, где подсчитывались суммы по всем
видам оценок, и нажмите клавишу .
Переименуйте текущий лист:
установите курсор на имени текущего листа, и вызовите контекстное меню;
выберите параметр Переименовать и введите новое имя, например Экзамен 1.
Скопируйте несколько раз текущий лист Экзамен 1:
установите курсор на имени текущего листа и вызовите контекстное меню;
выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец, нажмите <ОК>.
Сохраните рабочую книгу с экзаменационными ведомостями (она понадобится для следующего задания).
Лабораторная работа №2
Задание.
Подготовьте для группы ведомость (рис.2) назначения студентов на стипендию по результатам экзаменационной сессии.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа №_______
Минимальный размер стипендии _____________
-
№ п/п
|
Фамилия, имя, отчество
|
Стипендия
|
|
|
|
|
|
|
|
|
|
Итого стипендиальный фонд по группе – __________________
Рис. 2. Форма стипендиальной ведомости
Последовательность действий:
Загрузите экзаменационную ведомость с оценками по трем предметам (информатика, мат. анализ, культурология ) из предыдущего задания.
На новом листе создайте ведомость стипендии (см. рис. 2) и скопируйте в нее список группы с порядковыми номерами и номерами зачетных книжек из экзаменационной ведомости предыдущего задания.
Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
Используя минимальное значение стипендии, и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:
если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается.
Обратите внимание, что наличие неявки или двойки за экзамен говорит о том, что экзамен не сдан, и, следовательно, стипендия не начисляется.
Подсчитайте сумму стипендиального фонда для всей группы.
Технология выполнения
Загрузите рабочую книгу с экзаменационными ведомостями.
Создайте в этой книге новый лист — Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.
Оформите название и шапку ведомости назначения на стипендию согласно рис.2.(задайте шрифт и тип выделения — полужирный).
Укажите размер минимальной стипендии в ячейке D3;
Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов.
Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента, например Снегирева. Для этого:
установите курсор в ячейке С6;
щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:
Категория: Статистические; Имя: СРЗНАЧ.
щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;
установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
аналогичным образом в качестве параметров Число2 и Число 3 выберите ячейку D6 с оценкой того же студента по второму экзамену и ячейку D6 с оценкой по третьему экзамену;
щелкните по кнопке <ОК>;
в ячейке С6 появится значение, рассчитанное по формуле
СРЗНАЧ('Экзамен 1`!D6;'Экзамен 1 (2)'D6;'Экзамен 1 (3)'D6).
Скопируйте формулу по всем ячейкам столбца С.
Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок и двоек, при этом технология ввода будет аналогична описанной в п.6, отличие заключается в использовании функции СЧЁТЕСЛИ. Окончательная формула в ячейке имеет вид:
=СЧЁТЕСЛИ('Экзамен 1'!D6;">2")+СЧЁТЕСЛИ('Экзамен 1 (2)'!D6;">2")+СЧЁТЕСЛИ('Экзамен 1 (3)'!D6;">2").
Скопируйте формулу по всем ячейкам столбца D.
Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула может иметь следующий вид: =ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)) Технология ввода формулы аналогична описанной в п.6. Для ввода вложенных функций можно пользоваться кнопкой вызова функций.
Скопируйте эту формулу в другие ячейки столбца Е так же, как вы делали в п. 7 и 9.
Проверьте работоспособность таблицы:
вводите другие оценки в экзаменационные ведомости;
измените минимальный размер стипендии.
Подсчитайте сумму стипендиального фонда для всей группы.
Лабораторная работа №3. Графическое решение систем уравнений
Задание.
Решить графически систему уравнений:
 в диапазоне  с шагом 
Технология выполнения.
Системы уравнений с двумя неизвестными могут быть приближенно решены графически. Их решением являются координаты точки пересечения линий, соответствующих уравнениям систем. При этом точность решения будет определяться величиной шага дискретизации (чем шаг меньше, тем точность выше).
Рассмотрим пример графического решения системы двух уравнений.
|
