Как высчитать стипендию в excel

Расчет стипендии с помощью приложения MS Excel с использованием функций

Страницы работы

Содержание работы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Пояснения к лабораторной работе №3_3 Задание 2

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

Полная формулировка задания такова:

Дана таблица с итогами экзаменационной сессии:

Составить электронную таблицу, определяющую стипендию по следующему правилу:

По рассчитанному среднему баллу за экзаменационную сессию (s) вычисляется повышающий коэффициент (k), на который затем умножается минимальная стипендия (m).

Повышающий коэффициент s вычисляется по правилу:

если 4 10, то стипендия не назначается и поэтому нужно в этом случае коэффициент k вычислять специальным образом, например, присвоить k текст «Неправильные данные»

Выполнение:

1. В начале необходимо внести на лист Excel таблицу согласно образцу, заполнив необходимое количество записей — студентов и их оценок по трем предметам:

2. Необходимо также заполнить ячейки С8, D8 и E8, в которых с использованием функции СРЗНАЧ() необходимо расчитать средний балл по каждому из трех предметов.

Оптимально использовать кнопку панели инструментов Автосумма:

Cначала необходимо сделать активной ячейку C8, затем на панели инструментов открыть меню кнопки Автосумма и выбрать функцию Среднее:

и затем, убедившись, что предложена правильная функция СРЗНАЧ() и правильный диапазон ячеек C3:C7 (в ячейках этого диапазона приведены оценки по предмету Математика всех пяти студентов), нажать клавишу Ввод.

Останется лишь, вновь сделав активной ячейку C8, переместить курсор мыши к ее правому нижнему углу (так, чтобы он выглядел как черный крестик), нажать на левую кнопку мыши и, не отпуская ее, скопировать содежимое в ячейки D8 и E8. В результате получим три средних значения оценок по трем предметам:

3. Далее необходимо подготовить таблицу для расчета стипендий студентов, продублировав их порядковые номера и фамилии (Ф.И.О.), а в качестве трех других заголовков столбцов записать Средний балл, Коэфициент и Стипендия (то есть переменные s, k и m):

4. Затем необходимозаполнить ячейки со средними баллами студентов (столбец Средний балл).

4.1 Вначале необходимо вычислить средний балл по трем предметам первого студента.

Для этого надо сделать активной ячейку С12 (средний балл студента Макарова С.П.) и вызвав окно мастера функций (нажав на кнопку fx), выбрать функцию СРЗНАЧ(), и в качестве исходных данных выбрать диапазон C3:F3 (содержащий оценки студента Макарова С.П. по всем трем предметам). В результате в строке формул появится запись СРЗНАЧ(С3:F3):

После нажатия ОК будет подсчитан средний балл студента Макарова С.П. за сессию и я ячейке C12 появится значение 7:

4.2 Остается скопировать введенную формулу в ячейки, соответствующие всем остальным студентам.

Для этого необходимо сделать активной ячейку C12 и подведя к ее правому нижнему углу указатель мыши, нажать левую клавишу мыши и скопировать значения в ячейки С13, С14, С15 и С16. Таким образом, будут подсчитаны средние баллы всех студентов:

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

5.1 Вначале вычисляется коэффициент первого студента.

Коэффициент зависит от среднего балла (s) согласно правилу в условиях (см. выше), таким образом, надо последовательно проверить,

— выполняется ли для величины среднего балла первое условие (4 установим курсор мыши в окне ввода формул после И перед открывающей скобкой — в результате будет открыто окно ввода аргументов для функции И():

Практическая работа «Стипендиальная ведомость»

Стипендиальная
ведомость факультета представляет собой ЭТ Excel, содержащую 5 рабочих листов. Соответственно Лист 1 – курс 1, Лист 2 –
курс 2 и т. д.

На каждом рабочем
листе составлены списки двух групп по 25 человек. Каждая таблица содержит
следующие поля: №пп, ФИО, оценки по пяти предметам, средний балл, успеваемость,
стипендия, стипендия с надбавкой хорошистам и отличникам, стипендия с надбавкой
из дополнительного фонда.

Поля: №пп, ФИО, оценки
по пяти предметам заполняются, остальные поля расчетные. (пример на рисунке)

Успеваемость

Успеваемость
студентов определяется по следующей схеме: если средний балл 4,75 и выше,
присваивается категория «отличник», если в промежутке от 3,75 до 4,75 – «хорошист»,
в промежутке от 2,5 до 3,75 – «троечник», если средний балл меньше 2,5 –
«неуспевающий».

Для расчета
успеваемости используется логическая функция ЕСЛИ. Данная функция включает в
себя три Условия, три Истины и Ложь. Выглядит следующим образом:

=ЕСЛИ(Условие1;Истина1;ЕСЛИ(Условие2;Истина2;ЕСЛИ(Условие3;Истина3;Ложь))),
где:

Условие1 – средний балл >=4,75;
ему соответствует Истина1 «отличник»;

Условие2 – средний балл >=3,75;
ему соответствует Истина2 «хорошист»;

Условие3 – средний балл >=2,5; ему
соответствует Истина3 «троечник»;

Ложью является значение
«неуспевающий».

Стипендия

В условии задачи
заявлено, что стипендия студентам, чей балл меньше 3,5 не начисляется.
Стипендия остальным студентам составляет 460 руб.

Для назначения
стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя
одно Условие, Истину и Ложь. Выглядит следующим образом:

=ЕСЛИ(Условие;Истина;Ложь), где:

Условие – средний балл <3,5;

Истина – 0;

Ложь – 460.

Стипендия с надбавкой хорошистам  и отличникам

Студентам,
имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка
в размере 10% от стипендии.

Для расчета
стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция
включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:

=ЕСЛИ(или(Условие1;Условие2);Истина;Ложь),
где:

Условие1 – категория «отличник»;

Условие2 – категория «хорошист»;

Истина – стипендия с надбавкой 10%;

Ложь – стипендия.

Стипендия с доп.надбавкой

Всему факультету дополнительно
выделили 50% стипендиального фонда. Необходимо распределить его между
отличниками. Для выполнения данных расчетов необходимо:

1.     Вставить дополнительный лист
в рабочую книгу, назвать его «общий фонд».

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

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

После нажатия ОК
в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего
поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.

4.     Рассчитать дополнительный
фонд, умножив общий фонд на 50%

5.     Рассчитать количество
отличников на факультете. Для этого необходимо воспользоваться функцией
СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций
указать диапазон ячеек первой таблицы, содержащей информацию о категории
успеваемости. Критерий для отбора указать «отличник».

Т.к. у нас на
рабочем листе две таблицы, для расчета общего количества отличников на курсе
необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить
вычисления по каждому курсу отдельно.

Таблица в режиме
отображения формул выглядит следующим образом

6.      Рассчитать общее количество
отличников. Для этого вставить функцию СУММ внизу таблицы.

7.     Рассчитать величину надбавки
каждому отличнику. Для этого необходимо дополнительный фонд разделить на
количество отличников.

8.     Рассчитать Стипендию с
доп.надбавкой.

Для этого
используется условная функция ЕСЛИ. Данная функция включает в себя одно
Условие, Истину и Ложь. Выглядит следующим образом:

=ЕСЛИ(Условие;Истина;Ложь), где:

Условие – категория успеваемости
студента — «отличник»;

Истина – стипендия с дополнительной
надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное
значение – клавишей F4);

Ложь – стипендия без изменений.

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

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

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

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

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

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

2.  Ввести:

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

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

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

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

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

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

Здравствуйте. Помогите, пожалуйста, гуманитарию решить задачку.

Смысл в чем: посчитать размер стипендии исходя из условий.
 если за все экзамены получены оценки 5, то размер стипендии 2550 р.,
 если за экзамены получены оценки 4 или 5, то стипендия составляет 1700 р.,
 если среди оценок за экзамены есть хотя бы одна 3, то стипендия не начисляется.

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

Добрый вечер,
Разбирающиеся люди, помогите пожалуйста

Имеется таблица с данными об оценках, льготах , нужно заполнить столбец «Стипендия » , который зависит от остальных данных, а именно:

·    рассчитать  стипендию:

־ в размере базовой стипендии, если студент успевает по всем предметам  (не имеет двоек);

־ в размере базовой стипендии + 20%, если студент
имеет все оценки «отлично»;

־ в размере базовой стипендии + 10%, если
студент – староста и учится без двоек;

־ имеющим льготы “*”– пособие 500р.+ стипендия(учится без двоек).

Получилось только с одним условием, который «все оценки отлично »  =ЕСЛИ(I11=5;$B$22*1,2;$B$22)  , но подскажите как написать все вместе сразу, чтоб работало?

Очень надеюсь на Вашу помощь  

Картинка удалена