10 студентов сдают экзамены по 5 дисциплинам по каждой дисциплине можно получить оценку

Обновлено: 12.03.2023

7) Далее подсчитайте количество оценок 2, 3, 4, 5 и общее количество оценок по каждой дисциплине. Для этого используйте вспомогательные столбики: F (для пятерок), G (для четверок), H (для троек), I (для двоек). Для каждого студента в ячейках этих столбиков будут находиться три нуля и одна единица (в том столбике, который соответствует фактической оценке).

С помощью Мастер функций в ячейки F6:I6 введите формулы (рис. 8).

Формула

Ссылка

Формула

Рис. 8. Формулы в ячейках F6:I6

Далее скопируйте (используя мышь) эти формулы во все остальные ячейки дополнительных столбиков. Определите имена блоков ячеек по каждому дополнительному столбику. Например, для столбика F это надо сделать так. Выделите все значения дополнительного столбика (F6 – последняя ссылка). Затем введите команду Вставка, Имя, Присвоить, в диалоговом окне в строке Имя наберите слово «отлично» и щелкните по кнопке Добавить.

Затем выделите столбцы F – I и сделайте их скрытыми (команда контекстного меню Формат, Скрыть).

Введите названия итогового количества полученных оценок («отлично», «хорошо», «удовлетворительно», «неудовлетворительно») в столбец В. Введите в ячейки столбца С формулы для подсчета суммарного количества оценок определенного вида. Например, введите (с помощью Мастер функций и выделения имени блока ячеек «отлично») формулу СУММ («отлично»). Для подсчета общего количества оценок, полученных на данном экзамене, надо установить курсор в пустой ячейке, находящейся под ячейками, где подсчитывались суммы по всем видам оценок, щелкнуть по кнопке S, выделить блок, где подсчитывались эти суммы и нажать клавишу ввода.

9) Сделайте две копии рабочего листа «Экзамен 1» с именами «Экзамен 2» и «Экзамен 3». Это можно сделать, щелкнув по ярлыку рабочего листа и используя далее контекстное меню. Заполните в ведомостях названия дисциплин (ячейка D3) на экзаменационных листах: «Информатика», «Математика», «Физика». Измените оценки в ведомостях по математике и физике произвольным образом. Рабочий лист с ведомостью по информатике будет иметь вид, который изображен на рис. 9.

Выполните команду Сервис, Параметры, вкладка Вид, установите флажок Формулы. Результат должен иметь вид, изображенный на рис. 10. Снимите флажок Формулы.

Рис.9. Вид экзаменационной ведомости (флажок Формулы сброшен)

Рис. 10. Вид экзаменационной ведомости (флажок Формулы установлен)

10) Составьте ведомость назначения на стипендию. Пусть минимальная стипендия равна 200 р. Стипендия начисляется студентам, сдавшим все экзамены, так:

если средний балл по результатам сессии более 4,66, выплачивается 50%-ная надбавка к минимальной стипендии;

если средний балл по результатам сессии более 4,3, но менее либо равен 4,66, выплачивается минимальная стипендия.

Создайте лист Стипендия, на который из столбцов А и В листа Экзамен1 скопируйте фамилии и порядковые номера студентов.

В ячейку D2 введите минимальный размер стипендии. В ячейки C4, D4 введите заголовки «Средний балл», «Стипендия». С помощью Мастера функций(и щелкая в процессе диалога по ярлычкам листов и нужным ячейкам) введите в ячейку С5 формулу (для вычисления среднего балла для первого студента по списку):

=Срзнач(Экзамен1!D6; Экзамен2!D6; Экзамен3!D6).

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

По ранее описанной методике введите расчетные формулы в столбец D. Например, в ячейке D5 должна появиться формула:

В режиме отображения значений ведомость должна иметь вид, изображенный на рис. 11.

Рис. 11. Таблица «Стипендия» в режиме отображения значений

В режиме отображения формул экзаменационная ведомость должна иметь вид, изображенный на рис. 12, 13.

11) По трем экзаменационным ведомостям постройте столбчатую диаграмму, отражающую результаты сессии. При этом ряды выбираются по результатам трех экзаменов, а категориями являются номера студентов. По оси абсцисс укажите Ф.И.О. Улучшите внешний вид диаграммы в соответствии с возможностями программы.

Рис. 12. Таблица «Стипендия» в режиме отображения формул (столбцы А – С)

Рис. 13. Таблица «Стипендия» в режиме отображения формул (столбец D)

9.4. Подбор параметра

Существует ряд математических пакетов, с помощью которых можно решать сложные математические задачи, например, широко используемый пакет Mathcad. Некоторые задачи можно решить и в Excel. Однако следует отметить, что в отличие от большинства современных математических пакетов, позволяющих находить решение в символьном виде, т. е. в виде аналитического выражения, в Excel можно получить только численное решение.

Например, при решении задачи дифференцирования какой-либо функции в Excel можно получить числовое значение производной в некоторой точке, но не формулу.

С помощью команды Сервис, Подбор параметраможно легко решить уравнение с одним неизвестным. Для поиска решения эта команда использует метод итераций. Сначала берется начальное значение в изменяемой ячейке, содержащей параметр. Если начальное значение не дает требуемого значения в целевой ячейке, то делается шаг итерационного процесса и находится новое значение изменяемой ячейки. Если и это значение не устраивает, делается еще шаг. Этот процесс продолжается до тех пор, пока не будет найдено нужное значение (при условии, что решение задачи существует и оно может быть найдено методом итераций).

По умолчанию команда Подборпараметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Для изменения этих установок надо выполнить команду Сервис, Параметры. На вкладке Вычислениянадо изменить значения полей Предельноечислоитерацийи Относительнаяпогрешность. Если задача имеет несколько решений, то будет найдено одно из них.

При решении уравнений нужно знать начальное приближение. В Excel его легко получить, построив таблицу значений функции и определив, при каких значениях аргумента функция меняет знак.

Использовать встроенные статистических функции Excel 2003 для расчетов.

Задание 1. Рассчитать количество прожитых дней.

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

Запустить приложение Excel 2003.

В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.81). Зафиксируйте ввод данных.

Просмотреть различные форматы представления даты (Формат – Формат ячейки – Число – Числовые форматы — Дата) . Перевести дату в тип ЧЧ.ММ.ГГГГ. Пример, 14.03.2001

Рассмотрите несколько типов форматов даты в ячейке А1.

В ячейку A2 ввести сегодняшнюю дату.

В ячейке A3 вычислить количество прожитых дней по формуле =A2-A1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

Задание 2. Возраст учащихся. По заданному списку учащихся и даты их рождения. Определить, кто родился раньше (позже), определить кто самый старший (младший).

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

Получите от преподавателя файл Возраст. По локальной сети: Откройте папку Мое сетевое окружение–Соседние компьютеры–Great– Электронные_таблицы, найдите файл Возраст. Скопируйте его любым известным вам способом.

Откройте свою папку (с номером вашей группыкласса). Вставьте в нее скопированный ранее файл.

Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21);

Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21);

Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21);

Определим самого старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21).

Самостоятельная работа:

Задача. Произведите необходимые расчеты роста учеников в разных единицах измерения.

Задание 3. С использованием электронной таблицы произвести обработку данных помощью статистических функций. Даны сведения об учащихся класса, включающие средний балл за четверть, возраст (год рождения) и пол. Определить средний балл мальчиков, долю отличниц среди девочек и разницу среднего балла учащихся разного возраста.

Решение:
Заполним таблицу исходными данными и проведем необходимые расчеты. В таблицу будем заносить данные из школьного журнала.

В таблице используются дополнительные колонки, которые необходимы для ответа на вопросы, поставленные в задаче (текст в них записан синим цветом), — возраст ученика и является ли учащийся отличником и девочкой одновременно.
Для расчета возраста использована следующая формула (на примере ячейки G4):

=ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25)

Прокомментируем ее. Из сегодняшней даты вычитается дата рождения ученика. Таким образом, получаем полное число дней, прошедших с рождения ученика. Разделив это количество на 365,25 (реальное количество дней в году, 0,25 дня для обычного года компенсируется високосным годом), получаем полное количество лет ученика; наконец, выделив целую часть, — возраст ученика.
Является ли девочка отличницей, определяется формулой (на примере ячейки H4):

Приступим к основным расчетам.
Прежде всего требуется определить средний балл мальчиков. Согласно определению, необходимо разделить суммарный балл мальчиков на их количество. Для этих целей можно воспользоваться соответствующими функциями табличного процессора.

=СУММЕСЛИ(F4:F15;»м»;D4:D15)/СЧЁТЕСЛИ(F4:F15;»м»)

Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию. Таким образом и получаем требуемое.
Для подсчета доли отличниц среди всех девочек отнесем количество девочек-отличниц к общему количеству девочек (здесь и воспользуемся набором значений из одной из вспомогательных колонок):

=СУММ(H4:H15)/СЧЁТЕСЛИ(F4:F15;»ж»)

Наконец, определим отличие средних баллов разновозрастных детей (воспользуемся в расчетах вспомогательной колонкой Возраст ):

=ABS(СУММЕСЛИ(G4:G15;15;D4:D15)/СЧЁТЕСЛИ(G4:G15;15)-
СУММЕСЛИ(G4:G15;16;D4:D15)/СЧЁТЕСЛИ(G4:G15;16))

Обратите внимание на то, что формат данных в ячейках G18:G20 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.

Самостоятельная работа:

С использованием электронной таблицы произвести обработку данных помощью статистических функций.
1. Даны сведения об учащихся класса, включающие оценки в течение одного месяца. Подсчитайте количество пятерок, четверок, двоек и троек, найдите средний балл каждого ученика и средний балл всей группы. Создайте диаграмму, иллюстрирующую процентное соотношение оценок в группе.
2. Четверо друзей путешествуют на трех видах транспорта: поезде, самолете и пароходе. Николай проплыл 150 км на пароходе, проехал 140 км на поезде и пролетел 1100 км на самолете. Василий проплыл на пароходе 200 км, проехал на поезде 220 км и пролетел на самолете 1160 км. Анатолий пролетел на самолете 1200 км, проехал поездом 110 км и проплыл на пароходе 125 км. Мария проехала на поезде 130 км, пролетела на самолете 1500 км и проплыла на пароходе 160 км.
Построить на основе вышеперечисленных данных электронную таблицу.

Добавить к таблице столбец, в котором будет отображаться общее количество километров, которое проехал каждый из ребят.

Вычислить общее количество километров, которое ребята проехали на поезде, пролетели на самолете и проплыли на пароходе (на каждом виде транспорта по отдельности).

Вычислить суммарное количество километров всех друзей.

Определить максимальное и минимальное количество километров, пройденных друзьями по всем видам транспорта.

Определить среднее количество километров по всем видам транспорта.

3. Создайте таблицу “Озера Европы”, используя следующие данные по площади (кв. км) и наибольшей глубине (м): Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море 371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон 591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252; Меларен 1140 и 64. Определите самое большое и самое маленькое по площади озеро, самое глубокое и самое мелкое озеро.
4. Создайте таблицу “Реки Европы”, используя следующие данные длины (км) и площади бассейна (тыс. кв. км): Волга 3688 и 1350; Дунай 2850 и 817; Рейн 1330 и 224; Эльба 1150 и 148; Висла 1090 и 198; Луара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79; Темза 340 и 15. Определите самую длинную и самую короткую реку, подсчитайте суммарную площадь бассейнов рек, среднюю протяженность рек европейской части России.
5. В банке производится учет своевременности выплат кредитов, выданных нескольким организациям. Известна сумма кредита и сумма, уже выплаченная организацией. Для должников установлены штрафные санкции: если фирма выплатила кредит более чем на 70 процентов, то штраф составит 10 процентов от суммы задолженности, в противном случае штраф составит 15 процентов. Посчитать штраф для каждой организации, средний штраф, общее количество денег, которые банк собирается получить дополнительно. Определить средний штраф бюджетных организаций.

10 спортсменов принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает не более 100 очков. Определить среди 10 спортсменов участника с наибольшим суммарным количеством очков. Построить диаграмму, показывающую соотношение количества набранных очков, каждым спортсменом по каждому виду спорта.

10 студентов сдают экзамены по 5 дисциплинам. По каждой дисциплине можно получить оценку – 2, 3, 4, 5. Определить среди 10 студентов человека с наибольшим средним баллом. Построить диаграмму, показывающую соотношение оценок, полученных каждым студентом по каждой дисциплине.

Для 10 человек по данным о ежемесячном доходе рассчитать подоходный налог 13%, единый социальный налог 5%. Округление произвести до копеек. Посчитать сумму к выдаче в рублях и $. Построить диаграмму, показывающую соотношение сумм уплаты налога по каждому виду налога.

Билет на пригородный поезд стоит 20 руб., если расстояние до станции не более 20 км; 50 руб., если расстояние до станции больше 20 км, но меньше 75 км; 100 руб., если расстояние больше 75 км. Составить таблицу со следующими столбцами: пункт назначения, расстояние, стоимость билета, количество проданных билетов до данного пункта назначения. Установить число станций в радиусе 50 км от города. Построить диаграмму, показывающую какая станция пользуется наибольшей популярностью по отношению к остальным.

Телефонная компания взимает плату за услуги телефонной связи по тарифу: 370 мин в месяц – абонентская плата 200 руб., за каждую минуту сверх нормы – 2 руб. Составить ведомость оплаты услуг телефонной связи для 10 абонентов за 1 месяц, самостоятельно указав количество потребляемого времени каждым. Построить диаграмму, показывающую сравнительную характеристику сумм оплаты услуг телефонной компании каждым абонентом.

Компания снабжает электроэнергией клиентов по тарифу:

5 руб. за 1 кВт/ч за первые 500 кВт/ч;

10 руб. за 1 кВт/ч свыше 500 кВт/ч, но не более 1000 кВт/ч;

15 руб. за 1 кВт/ч свыше 1000 кВт/ч. Для 10 клиентов посчитать плату. Определить число клиентов, потребляющих более 1000 кВт/ч. Построить диаграмму, демонстрирующую сравнение потребляемой электроэнергии каждым клиентом.

Билет на пригородный поезд стоит 10 монет, если расстояние до станции не более 20 км; 15 монет, если расстояние до станции больше 20 км, но меньше 75 км; 25 монет, если расстояние больше 75 км. Составить таблицу со следующими столбцами: пункт назначения, расстояние, стоимость билета, количество проданных билетов до данного пункта назначения. Отсортировать таблицу по полю количество проданных билетов. Установить число станций в радиусе 60 км от города. Построить диаграмму, показывающую какая станция пользуется наименьшей популярностью по отношению к остальным.

Билет на пригородный поезд стоит 6 монет, если расстояние до станции не более 20 км; 10 монет, если расстояние до станции больше 20 км, но меньше 75 км; 15 монет, если расстояние больше 75 км. Составить таблицу со следующими столбцами: пункт назначения, расстояние, стоимость билета, количество проданных билетов до данного пункта назначения. Отсортировать таблицу по полю количество проданных билетов. Установить число станций в радиусе более 70 км от города. Построить диаграмму, показывающую, какая станция пользуется наименьшей популярностью по отношению к остальным.

10 студентов сдают экзамены по 5 дисциплинам. По каждой дисциплине студент может получить оценку – 2, 3, 4, 5. Определить средний балл учащихся. Посчитать количество 5, 4, 3 и 2. Найти студента с наибольшим средним баллом и студента с наименьшим средним баллом. Построить диаграмму, показывающую соотношение оценок, полученных каждым слушателем по каждой дисциплине.

Для отдела из 10 человек составить ведомость расчета заработной платы. Таблица содержит следующие сведения: Ф.И.О., должность, оклад, стаж работы. Для каждого человека посчитать подоходный налог 13%, надбавку 5000 руб., если стаж работы более 3 лет и сумму к выдаче. Построить диаграмму, показывающую з/плату каждого сотрудника.

Для отдела из 10 человек составить ведомость расчета заработной платы. Таблица содержит следующие сведения: Ф.И.О., должность, оклад, стаж работы. Для каждого человека посчитать подоходный налог 13%, надбавку и сумму к выдаче. Надбавка составляет 10% от оклада, если стаж работы более 5 лет. Построить диаграмму, показывающую з/плату каждого сотрудника.

Компания снабжает электроэнергией клиентов по тарифу:

15 руб. за 1 кВт/ч за первые 500 кВт/ч;

20 руб. за 1 кВт/ч свыше 500 кВт/ч.

Для 10 клиентов посчитать плату. Определить число клиентов, потребляющих не более 500 кВт/ч и найти суммарное количество потребляемой энергии. Построить диаграмму, демонстрирующую сравнение потребляемой электроэнергии каждым клиентом.

Составьте экзаменационную ведомость, в которую входят следующие данные: №, Ф. И. О. студентов, оценки за экзамены. Посчитать средний балл для каждого студента. Если сданы все экзамены и средний балл равен 5,то выплачивается 50% надбавка к минимальной стипендии, если средний балл меньше 5, но больше или равен 4, то выплачивается минимальная стипендия. Построить диаграмму, показывающую количество оценок определенного вида, полученных в данной группе.

Телефонная компания взимает плату за услуги телефонной связи по тарифу: 300 мин в месяц – абонентская плата 250 руб., за каждую минуту сверх нормы – 2 руб. Составить ведомость оплаты услуг телефонной связи для 10 абонентов за 1 месяц, самостоятельно указав количество потребляемого времени каждым. Найти людей с максимальной и минимальной оплатой услуг. Построить диаграмму, показывающую сравнительную характеристику сумм оплаты услуг телефонной компании каждым абонентом.

10 спортсменов принимают участие в некотором соревновании. Каждый спортсмен может набрать не более 30 очков. Указать номер места, которое занял спортсмен в данном соревновании. За 1 место выплачивается премия 100000 руб., за 2 место 50000 руб. и за 3 место 30000 руб. Построить диаграмму, показывающую количество набранных очков, каждым спортсменом.

Составьте ведомость контроля остаточных знаний студентов по какой-либо дисциплине. Контроль остаточных знаний проходит в форме теста, по результатам которого выставляется оценка. Если студент набрал от 95 до 100 баллов, выставляется оценка «5», от 80 до 94 – «4», от 60 до 79 – «3», менее 60 – «2». Посчитайте: количество студентов, получивших оценку «5», «4», «3», «2», средний балл в группе, максимальный и минимальный баллы. С помощью диалогового окна Условное форматирование выделите все «2» красным цветом. Постройте круговую диаграмму, показывающую процентное соотношение оценок в группе.

Индивидуальные задания

Постройте графики функций.

Задание № 7 (Excel)

Использование функций ЕСЛИ(), ИЛИ(), ВПР(), СРЗНАЧ(), СЧЕТ(), СЧЕТЗ().

Расчет стипендии с помощью приложения MS Excel.

По представленному образцу рассчитать стипендию в зависимости от результатов сессии (среднего балла).

1. Сначала необходимо повторить оформление таблицы.

§ Результаты экзаменов и зачетов (диапазон ячеек B4:F7).

§ Мин. зарплату, предварительно выбрав собственный формат числа 0,00р в текстовом окошке Тип после выполнения команды Формат ► Ячейки ► Вкл.: Число.

§ Справа данные Для расчета стипендии – Средний балл и Коэффициент.

3. В строке Ср. балл для расчета использовать функцию СРЗНАЧ() (в Категории Статистические).

4. Для подсчета студентов, сдавших экзамены, в строке Сдало использовать функцию СЧЕТ() (Категория: Статистические).

5. Для подсчета студентов, сдавших зачеты, в строке Сдало использовать функцию СЧЕТЗ() (Категория Статистические).

6. В столбце Средн. балл для студента Петрова А. расчет среднего балла подсчитывается по формуле (B4+C4+D4)/3, но только в том случае, когда сданы все экзамены и все зачеты. Для реализации этих условий в ячейке G4 можно использовать, например, такую формулу:

=ЕСЛИ(ИЛИ(B4*C4*В4=0;E4&F4<>”++”);0;(B4+C4+D4)/3)

Функции ЕСЛИ() и ИЛИ() следует выбирать из Категории Логические. Эту формулу распространить для остальных студентов.

7. Величина начисляемой стипендии (ячейка Н4) определяется с помощью функции вертикального поиска значения G4 в области J4:K8. Найденный коэффициент умножается на величину минимальной зарплаты К1. В расчетной таблице (область J4:K8) выбирается средний балл, значение которого ближе всего к значению среднего балла ячейки G4, а затем и соответствующий этому выбранному значению коэффициент. Если же средний бал в столбе G равен 0, то коэффициент сразу должен быть принят нулю. Такой алгоритм может быть реализован формулой: =ЕСЛИ(G4=0;0;ВПР(G4;J$4:K$8;2;1)*K$1). Функция ВПР() выбирается из Категории Ссылки и массивы.

8. Сумму начисленной стипендии (Н9) вычислить с помощью функции СУММ().

Пояснения к п. 7. Значения аргументов функции ВПР() означают следующее:

ВПР(<что искать>;<где искать>;<откуда взять>;<как искать>) или

ВПР(<искомое значение>;<область поиска>;<номер столбца извлечения>; <тип поиска>).

Если <тип поиска>=1, то поиск – приблизительный, если <тип поиска>=0, поиск точный.

Читайте также:

  

• Программа для проектирования трубопроводов в 3д

  

• Взвешивание выборки в excel

  

• Параметры в автокад 2014

  

• Excel есть ли значение в списке

  

• Visual studio как установить старую версию