Как посчитать стипендию в excel по среднему баллу
7) Далее подсчитайте количество оценок 2, 3, 4, 5 и общее количество оценок по каждой дисциплине. Для этого используйте вспомогательные столбики: F (для пятерок), G (для четверок), H (для троек), I (для двоек). Для каждого студента в ячейках этих столбиков будут находиться три нуля и одна единица (в том столбике, который соответствует фактической оценке).
С помощью Мастер функций в ячейки F6:I6 введите формулы (рис. 8).
Формула
Ссылка
Формула
Рис. 8. Формулы в ячейках F6:I6
Далее скопируйте (используя мышь) эти формулы во все остальные ячейки дополнительных столбиков. Определите имена блоков ячеек по каждому дополнительному столбику. Например, для столбика F это надо сделать так. Выделите все значения дополнительного столбика (F6 – последняя ссылка). Затем введите команду Вставка, Имя, Присвоить, в диалоговом окне в строке Имя наберите слово «отлично» и щелкните по кнопке Добавить.
Затем выделите столбцы F – I и сделайте их скрытыми (команда контекстного меню Формат, Скрыть).
8) Введите названия итогового количества полученных оценок («отлично», «хорошо», «удовлетворительно», «неудовлетворительно») в столбец В. Введите в ячейки столбца С формулы для подсчета суммарного количества оценок определенного вида. Например, введите (с помощью Мастер функций и выделения имени блока ячеек «отлично») формулу СУММ («отлично»). Для подсчета общего количества оценок, полученных на данном экзамене, надо установить курсор в пустой ячейке, находящейся под ячейками, где подсчитывались суммы по всем видам оценок, щелкнуть по кнопке 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 его легко получить, построив таблицу значений функции и определив, при каких значениях аргумента функция меняет знак.
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
Стипендиальная ведомость факультета представляет собой ЭТ Excel , содержащую 5 рабочих листов. Соответственно Лист 1 – курс 1, Лист 2 – курс 2 и т. д.
На каждом рабочем листе составлены списки двух групп по 25 человек. Каждая таблица содержит следующие поля: №п\п, ФИО, оценки по пяти предметам, средний балл, успеваемость, стипендия, стипендия с надбавкой хорошистам и отличникам, стипендия с надбавкой из дополнительного фонда.
Поля: №п\п, ФИО, оценки по пяти предметам заполняются, остальные поля расчетные. (пример на рисунке)
Успеваемость
Успеваемость студентов определяется по следующей схеме: если средний балл 4,75 и выше, присваивается категория «отличник», если в промежутке от 3,75 до 4,75 – «хорошист», в промежутке от 2,5 до 3,75 – «троечник», если средний балл меньше 2,5 – «неуспевающий».
Для расчета успеваемости используется логическая функция ЕСЛИ. Данная функция включает в себя три Условия, три Истины и Ложь. Выглядит следующим образом:
Условие1 – средний балл >=4,75; ему соответствует Истина1 «отличник»;
Условие2 – средний балл >=3,75; ему соответствует Истина2 «хорошист»;
Условие3 – средний балл >=2,5; ему соответствует Истина3 «троечник»;
Ложью является значение «неуспевающий».
Стипендия
В условии задачи заявлено, что стипендия студентам, чей балл меньше 3,5 не начисляется. Стипендия остальным студентам составляет 460 руб.
Для назначения стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – средний балл
Стипендия с надбавкой хорошистам и отличникам
Студентам, имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка в размере 10% от стипендии.
Для расчета стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:
Условие1 – категория «отличник»;
Условие2 – категория «хорошист»;
Истина – стипендия с надбавкой 10%;
Стипендия с доп.надбавкой
Всему факультету дополнительно выделили 50% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:
1. Вставить дополнительный лист в рабочую книгу, назвать его «общий фонд».
2. Рассчитать величину стипендиального фонда каждой группы. Для этого внизу каждой таблицы, в поле Стипендия с надбавкой хор и отл, вставить функцию СУММ.
3. Рассчитать первоначальный стипендиальный фонд. Для этого используется Консолидация данных, расположенная на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма, далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые значения фондов по каждой группе.
После нажатия ОК в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.
4. Рассчитать дополнительный фонд, умножив общий фонд на 50%
5. Рассчитать количество отличников на факультете. Для этого необходимо воспользоваться функцией СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций указать диапазон ячеек первой таблицы, содержащей информацию о категории успеваемости. Критерий для отбора указать «отличник».
Т.к. у нас на рабочем листе две таблицы, для расчета общего количества отличников на курсе необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить вычисления по каждому курсу отдельно.
Таблица в режиме отображения формул выглядит следующим образом
6. Рассчитать общее количество отличников. Для этого вставить функцию СУММ внизу таблицы.
7. Рассчитать величину надбавки каждому отличнику. Для этого необходимо дополнительный фонд разделить на количество отличников.
8. Рассчитать Стипендию с доп.надбавкой.
Для этого используется условная функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – категория успеваемости студента - «отличник»;
Истина – стипендия с дополнительной надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное значение – клавишей F 4);
Ложь – стипендия без изменений.
Для построения диаграммы, отображающей стипендиальные фонды групп факультета, необходимо воспользоваться данными на листе «общий фонд».
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
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» красным цветом. Постройте круговую диаграмму, показывающую процентное соотношение оценок в группе.
Индивидуальные задания
Постройте графики функций.
y = x 5 +x 2 –10 , [-10;10] ,
y = |tg(x)| × x, [-1;1],
y = cos(x+x 5 )–2, [-2;2] ,
y = |x 3 +x –10|, [-2;2] ,
y = e x -3, [-1;1] ,
y = e x ·|x|, [-1;1] ,
y = cos(x 3 )– 5 , [-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, поиск точный.
На форуме есть похожая тема, но конкретно выполненного задания не нашел, а завтра нужно срочно сдать. Половину сделал, нужно только рассчитать стипендию. Вот задание: Пусть "обычная" стипендия равна минимальной зарплате. Полагаем также, что отличники получают стипендию на 40% выше номинальной, а студенты, не имеющие троек, - на 10%. Кроме того, студенты с детьми получают по одной минимальной зарплате на каждого ребенка.
Расчет стипендии в таблице
Ребят помогите рассчитать стипендию,последовательность значений не знаю,допускаю ошибки в.
Расчет стипендии в Excel
Помогите решить! Извините если не правильно оформил тему. Расчет стипендии. Создать таблицу.
Расчет стипендии в зависимости от оценок
Здравствуйте. Помогите, пожалуйста, гуманитарию решить задачку. Смысл в чем: посчитать размер.
Реализовать автоматический расчет стипендии (Сумма на руки) по следующим условиям
Ребят как можно с помощью кнопок реализовать В поле Минимальная стипендия необходимо.
Петров химию не сдал, а стипендию будет получать?
Студенты с детьми получают пособие на детей в любом случае?
Извиняюсь за не полные данные, вот полное задание :
Вычислить размер стипендии в зависимости от сред него балла, полученного в сессию, и наличия детей. Средний балл счи тается равным нулю, если у студента есть задолженности - двойка по одному предмету или "незачет" по одному зачету. Зачет обозначается буквой "з" в колонке зачетов, незачет - буквой "н". Отсутствие на конец сессии экзаменационных оценок и отметок о зачетах/незачетах хотя бы по одному предмету означает, что сессия данного студента продлена. Этот студент получает только доплаты на детей, а в столбце "Продлено" ставится отметка "+". Считаем, что всем студентам, не имеющим задолженностей, назначается стипендия. Пусть "обычная" стипендия равна минимальной зарплате. Полагаем также, что отличники получают стипендию на 40% выше номинальной, а студенты, не имеющие троек, - на 10%. Кроме того, студенты с детьми получают по одной минимальной зарплате на каждого ребенка
Читайте также: