7) Далее подсчитайте количество оценок 2, 3, 4, 5 и общее количество оценок по каждой дисциплине. Для этого используйте вспомогательные столбики: F (для пятерок), G (для четверок), H (для троек), I (для двоек). Для каждого студента в ячейках этих столбиков будут находиться три нуля и одна единица (в том столбике, который соответствует фактической оценке).
С помощью Мастер функций в ячейки F6:I6 введите формулы (рис. 8).
|
Ссылка |
Формула |
Ссылка |
Формула |
F6 |
Если (D6 = 5; 1; 0) |
H6 |
Если (D6 = 3; 1; 0) |
|
G6 |
Если (D6 = 4; 1; 0) |
I6 |
Если (D6 = 2; 1; 0) |
Рис. 8. Формулы в ячейках F6:I6
Далее скопируйте (используя мышь) эти формулы во все остальные ячейки дополнительных столбиков. Определите имена блоков ячеек по каждому дополнительному столбику. Например, для столбика F это надо сделать так. Выделите все значения дополнительного столбика (F6 – последняя ссылка). Затем введите команду Вставка, Имя, Присвоить, в диалоговом окне в строке Имя наберите слово «отлично» и щелкните по кнопке Добавить.
Затем выделите столбцы F – I и сделайте их скрытыми (команда контекстного меню Формат, Скрыть).
8) Введите названия итогового количества полученных оценок («отлично», «хорошо», «удовлетворительно», «неудовлетворительно») в столбец В. Введите в ячейки столбца С формулы для подсчета суммарного количества оценок определенного вида. Например, введите (с помощью Мастер функций и выделения имени блока ячеек «отлично») формулу СУММ («отлично»). Для подсчета общего количества оценок, полученных на данном экзамене, надо установить курсор в пустой ячейке, находящейся под ячейками, где подсчитывались суммы по всем видам оценок, щелкнуть по кнопке S, выделить блок, где подсчитывались эти суммы и нажать клавишу ввода.
9) Сделайте две копии рабочего листа «Экзамен 1» с именами «Экзамен 2» и «Экзамен 3». Это можно сделать, щелкнув по ярлыку рабочего листа и используя далее контекстное меню. Заполните в ведомостях названия дисциплин (ячейка D3) на экзаменационных листах: «Информатика», «Математика», «Физика». Измените оценки в ведомостях по математике и физике произвольным образом. Рабочий лист с ведомостью по информатике будет иметь вид, который изображен на рис. 9.
 |
Рис.9. Вид экзаменационной ведомости (флажок Формулы сброшен)
 |
Рис. 10. Вид экзаменационной ведомости (флажок Формулы установлен)
10) Составьте ведомость назначения на стипендию. Пусть минимальная стипендия равна 200 р. Стипендия начисляется студентам, сдавшим все экзамены, так:
если средний балл по результатам сессии более 4,66, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл по результатам сессии более 4,3, но менее либо равен 4,66, выплачивается минимальная стипендия.
Создайте лист Стипендия, на который из столбцов А и В листа Экзамен1 скопируйте фамилии и порядковые номера студентов.
В ячейку D2 введите минимальный размер стипендии. В ячейки C4, D4 введите заголовки «Средний балл», «Стипендия». С помощью Мастера функций(и щелкая в процессе диалога по ярлычкам листов и нужным ячейкам) введите в ячейку С5 формулу (для вычисления среднего балла для первого студента по списку):
=Срзнач(Экзамен1!D6; Экзамен2!D6; Экзамен3!D6).
Скопируйте формулу по всем ячейкам столбца С.
По ранее описанной методике введите расчетные формулы в столбец D. Например, в ячейке D5 должна появиться формула:
=Если(С5>4,66;$D$2*1,5; Если(C5>4,33;$D$2;0)).
В режиме отображения значений ведомость должна иметь вид, изображенный на рис. 11.
Рис. 11. Таблица «Стипендия» в режиме отображения значений
В режиме отображения формул экзаменационная ведомость должна иметь вид, изображенный на рис. 12, 13.
Рис. 12. Таблица «Стипендия» в режиме отображения формул (столбцы А – С)
Рис. 13. Таблица «Стипендия» в режиме отображения формул (столбец D)
9.4. Подбор параметра
Существует ряд математических пакетов, с помощью которых можно решать сложные математические задачи, например, широко используемый пакет Mathcad. Некоторые задачи можно решить и в Excel. Однако следует отметить, что в отличие от большинства современных математических пакетов, позволяющих находить решение в символьном виде, т. е. в виде аналитического выражения, в Excel можно получить только численное решение.
Например, при решении задачи дифференцирования какой-либо функции в Excel можно получить числовое значение производной в некоторой точке, но не формулу.
С помощью команды Сервис, Подбор параметраможно легко решить уравнение с одним неизвестным. Для поиска решения эта команда использует метод итераций. Сначала берется начальное значение в изменяемой ячейке, содержащей параметр. Если начальное значение не дает требуемого значения в целевой ячейке, то делается шаг итерационного процесса и находится новое значение изменяемой ячейки. Если и это значение не устраивает, делается еще шаг. Этот процесс продолжается до тех пор, пока не будет найдено нужное значение (при условии, что решение задачи существует и оно может быть найдено методом итераций).
По умолчанию команда Подборпараметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Для изменения этих установок надо выполнить команду Сервис, Параметры. На вкладке Вычислениянадо изменить значения полей Предельноечислоитерацийи Относительнаяпогрешность. Если задача имеет несколько решений, то будет найдено одно из них.
При решении уравнений нужно знать начальное приближение. В Excel его легко получить, построив таблицу значений функции и определив, при каких значениях аргумента функция меняет знак.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.