Расчет стипендии с помощью приложения MS Excel с использованием функций
Задание № 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, поиск точный.
Практическая работ а «Стипендиальная ведомость»
Стипендиальная ведомость факультета представляет собой ЭТ 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 руб.
Для назначения стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – средний балл <3,5;
Стипендия с надбавкой хорошистам и отличникам
Студентам, имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка в размере 10% от стипендии.
Для расчета стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:
Условие1 – категория «отличник»;
Условие2 – категория «хорошист»;
Истина – стипендия с надбавкой 10%;
Стипендия с доп.надбавкой
Всему факультету дополнительно выделили 50% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:
1. Вставить дополнительный лист в рабочую книгу, назвать его «общий фонд».
2. Рассчитать величину стипендиального фонда каждой группы. Для этого внизу каждой таблицы, в поле Стипендия с надбавкой хор и отл, вставить функцию СУММ.
3. Рассчитать первоначальный стипендиальный фонд. Для этого используется Консолидация данных, расположенная на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма, далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые значения фондов по каждой группе.
После нажатия ОК в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.
4. Рассчитать дополнительный фонд, умножив общий фонд на 50%
5. Рассчитать количество отличников на факультете. Для этого необходимо воспользоваться функцией СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций указать диапазон ячеек первой таблицы, содержащей информацию о категории успеваемости. Критерий для отбора указать «отличник».
Т.к. у нас на рабочем листе две таблицы, для расчета общего количества отличников на курсе необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить вычисления по каждому курсу отдельно.
Таблица в режиме отображения формул выглядит следующим образом
6. 
Рассчитать общее количество отличников. Для этого вставить функцию СУММ внизу таблицы.
7. Рассчитать величину надбавки каждому отличнику. Для этого необходимо дополнительный фонд разделить на количество отличников.
8. Рассчитать Стипендию с доп.надбавкой.
Для этого используется условная функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – категория успеваемости студента — «отличник»;
Истина – стипендия с дополнительной надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное значение – клавишей F 4);
Ложь – стипендия без изменений.
Для построения диаграммы, отображающей стипендиальные фонды групп факультета, необходимо воспользоваться данными на листе «общий фонд».
Как в excel назначить стипендию 200 руб студентам со средним баллом больше 4 остальным 100 руб?
Как в excel назначить стипендию 200 руб студентам со средним баллом больше 4 остальным 100 руб.
Функция Если = ЕСЛИ(A2 = 4 ; 100 ; 200) = Если(лог.
Выражение ; если истина то ; если ложно то).
В компьютерном салоне имеется следующий набор устройств : • процессор Socket — 1155 Intel Celeron, 2, 5 ГГц — 1980 руб?
В компьютерном салоне имеется следующий набор устройств : • процессор Socket — 1155 Intel Celeron, 2, 5 ГГц — 1980 руб.
; • процессор Socket — 1155 Intel Core i3 — 2100, 3, 1 ГГц — 4390 руб.
; • процессор Socket — 1155 Intel Core i5 — 3450, 3, 1 ГГц — 6740 руб.
; • процессор Socket — АМЗ AMD ATHLON II ХЗ, 3, 1 ГГц — 2510 руб.
; • процессор Socket — АМЗ AMD Phenom II Х4, 3, 5 ГГц — 5510 руб.
; • материнская плата Socket — 1155 ASRock DDR3 mATX AC’97 + LAN + VGA — 1850 руб.
; • материнская плата Socket — 1155 ASUSTeK 2xPCI — E + GbLAN SATA 2DDR — III — 2760 руб.
; • материнская плата Socket — 1155 GigaByte 2xPCI — E + GbLAN SATA — 4180 руб.
; • материнская плата Socket — 775 ASUSTeK PCI — E + SVGA + GbLAN SATA 4DDR — III — 1770 руб.
; • материнская плата Socket — АМЗ ASUSTeK PCI — E + GbLAN SATA 4DDR — III — 3300 руб.
; • корпус компьютера с блоком питания мощностью 350 В — 1310 руб.
; • корпус компьютера с блоком питания мощностью 400 В — 2480 руб.
; • модули оперативной памяти объемом 1 Гбайт — 640 руб.
; • модули оперативной памяти объемом 2 Гбайта — 850 руб.
; • модули оперативной памяти объемом 4 Гбайта — 1330 руб.
; • модули оперативной памяти объемом 8 Гбайт — 2010 руб.
; • жесткий диск объемом 500 Гбайт — 3100 руб.
; • жесткий диск объемом 1 Тбайт — 3570 руб.
; • жесткий диск объемом 2 Тбайта — 4800 руб.
; • видеокарта с объемом видеопамяти 512 Мбайт — 1270 руб.
; • видеокарта с объемом видеопамяти 1 Гбайт — 2700 руб.
; • видеокарта с объемом видеопамяти 2 Гбайта — 5880 руб.
; • звуковая карта — 960 руб.
; • звуковая карта (профессиональная) — 5770 руб.
; • звуковые колонки 5 В — 730 руб.
; • звуковые колонки 5.
1 (5 колонок + сабвуфер) — 2920 руб.
; • сетевая карта 10 / 100 / 1000 Мбит / с — 570 руб.
; • привод CD — RW — 830 руб.
; • привод CD — RW / DYD — RW — 970 руб.
; • принтер струйный (цветной) — 2150 руб.
; • фотопринтер струйный — 2500 руб.
; • принтер лазерный — 4630 руб.
; • сканер — 2510 руб.
; • монитор LCD, диагональ 19 дюймов — 3600 руб.
; • монитор LCD, диагональ 20 дюймов — 5870 руб.
; • монитор LCD, диагональ 20 дюймов — 8540 руб.
; • мышь оптическая — 200 руб.
; • клавиатура — 180 руб.
; • клавиатура мультимедийная — 1250 руб.
Домашний компьютер, предназначенный в основном для компьютерных игр, просмотра видеофильмов и выхода в Интернет через телефонную линию связи.
Сумма — 30 000 руб.
Найти решение системы уравнений Оформите таблицу и график в соответствии с общепринятыми правилами?
Найти решение системы уравнений Оформите таблицу и график в соответствии с общепринятыми правилами.
В группе обучается 20 студентов.
Стипендию могут получить студенты занимающиеся без «3».
Если студент имеет 75 % и более «5», то его стипендия повышается на 25 %.
Определить, кто из студентов будет получать стипендию обычную, повышенную, сколько студентов будет получать стипендию.
Определить средний балл по каждому предмету и в целом по группе.
Определить лучшего студента.
Автомат по продаже билетов принимает купюры достоинством 10 руб?
Автомат по продаже билетов принимает купюры достоинством 10 руб.
Но требует оплаты без сдачи .
Билет стоит 130 руб.
Какое минимальное количество билетов может купить может купить пассажир потратив 10 банкнот?
Автомат по продаже билетов принимает купюры достоинством 10 руб, 50 руб, 100 руб, но требует оплаты без сдачи?
Автомат по продаже билетов принимает купюры достоинством 10 руб, 50 руб, 100 руб, но требует оплаты без сдачи.
Билет стоит 130 руб.
Какое минимальное количество билетов может купить пассажир, потратив 10 банкнот?
Помогите, пожалуйстапроцессор Socket — 1155 Intel Celeron, 2, 5 ГГц — 1980 руб?
процессор Socket — 1155 Intel Celeron, 2, 5 ГГц — 1980 руб.
• процессор Socket — 1155 Intel Core i3 — 2100, 3, 1 ГГц — 4390 руб.
• процессор Socket — 1155 Intel Core i5 — 3450, 3, 1 ГГц — 6740 руб.
• процессор Socket — АМЗ AMD ATHLON II ХЗ, 3, 1 ГГц — 2510 руб.
• процессор Socket — АМЗ AMD Phenom II Х4, 3, 5 ГГц — 5510 руб.
• материнская плата Socket — 1155 ASRock DDR3 mATX AC’97 + LAN + VGA — 1850 руб.
• материнская плата Socket — 1155 ASUSTeK 2xPCI — E + GbLAN SATA 2DDR — III — 2760 руб.
• материнская плата Socket — 1155 GigaByte 2xPCI — E + GbLAN SATA — 4180 руб.
• материнская плата Socket — 775 ASUSTeK PCI — E + SVGA + GbLAN SATA 4DDR — III — 1770 руб.
• материнская плата Socket — АМЗ ASUSTeK PCI — E + GbLAN SATA 4DDR — III — 3300 руб.
• корпус компьютера с блоком питания мощностью 350 В — 1310 руб.
• корпус компьютера с блоком питания мощностью 400 В — 2480 руб.
• модули оперативной памяти объемом 1 Гбайт — 640 руб.
• модули оперативной памяти объемом 2 Гбайта — 850 руб.
• модули оперативной памяти объемом 4 Гбайта — 1330 руб.
• модули оперативной памяти объемом 8 Гбайт — 2010 руб.
• жесткий диск объемом 500 Гбайт — 3100 руб.
• жесткий диск объемом 1 Тбайт — 3570 руб.
• жесткий диск объемом 2 Тбайта — 4800 руб.
• видеокарта с объемом видеопамяти 512 Мбайт — 1270 руб.
• видеокарта с объемом видеопамяти 1 Гбайт — 2700 руб.
• видеокарта с объемом видеопамяти 2 Гбайта — 5880 руб.
• звуковая карта — 960 руб.
• звуковая карта (профессиональная) — 5770 руб.
• звуковые колонки 5 В — 730 руб.
• звуковые колонки 5.
1 (5 колонок + сабвуфер) — 2920 руб.
• сетевая карта 10 / 100 / 1000 Мбит / с — 570 руб.
• привод CD — RW — 830 руб.
• привод CD — RW / DYD — RW — 970 руб.
• принтер струйный (цветной) — 2150 руб.
• фотопринтер струйный — 2500 руб.
• принтер лазерный — 4630 руб.
• сканер — 2510 руб.
• монитор LCD, диагональ 19 дюймов — 3600 руб.
• монитор LCD, диагональ 20 дюймов — 5870 руб.
• монитор LCD, диагональ 20 дюймов — 8540 руб.
• мышь оптическая — 200 руб.
• клавиатура — 180 руб.
• клавиатура мультимедийная — 1250 руб.
Подобрать комплектующие для компьютера, предназначенного для решения определенного круга задач (см.
При выборе компонент компьютера необходимо уложиться в заданную сумму.
Для подбора различных вариантов решения указанной задачи использовать табличный процессор (электронные таблицы).
Мультимедийный компьютер, предназначенный для выполнения видеомонтажа и создания рекламных видеороликов.
Сумма — 35 000 руб.
(обязательно два жестких диска).
Дан список студентов с указанием фамилий и результатов сдачи трех экзаменов(оценки — 5, 4, 3 или 2)?
Дан список студентов с указанием фамилий и результатов сдачи трех экзаменов
(оценки — 5, 4, 3 или 2).
Известны : размер базовой стипендии — 1 000 руб.
при сдаче экзаменов только на «отлично» — 50% (к базовой стипендии).
для каждого студента размер его стипендии, которая не назначается при хотя бы
одной удовлетворительной или неудовлетворительной оценке.
Мальчик, продающий на улице газеты, зарабатывает а руб?
Мальчик, продающий на улице газеты, зарабатывает а руб.
На продаже первых 75 газет.
На каждой из остальных проданных газет он зарабатывает по х руб.
Вычислить заработок мальчика, если он продаст 133 газеты.
Решить в Паскале.
Решить задачу в excelАлина отправилась в магазин за покупками?
Решить задачу в excel
Алина отправилась в магазин за покупками.
Девушка купила пачку молока за 32 руб.
, две упаковки замороженных овощей, заплатив за каждую 121, 2 руб.
Три пачки 2 % творога, заплатив за каждую пачку 54, 77 руб.
Пять килограммов мяса.
Один килограмм мяса стоил в магазине 191, 85 руб.
Сколько денег Алина потратила в магазине?
Как вычислить средний балл учащихся которые сдавали экзамен 15 июля и как определить средний балл учащихся которые сдавали экзамен по истории в любой день в excel помогите формула какая?
Как вычислить средний балл учащихся которые сдавали экзамен 15 июля и как определить средний балл учащихся которые сдавали экзамен по истории в любой день в excel помогите формула какая.
Помогите решить задачу пожалуйста ?
Помогите решить задачу пожалуйста !
Составить блок — схему и программу.
В магазине имеется товар трёх сортов.
Цена первого сорта — А руб.
Цена 2 сорта — В руб.
, цена 3 сорта — С руб.
Определите среднюю стоимость товара
Надо сделать в программе Pascal.
Вы зашли на страницу вопроса Как в excel назначить стипендию 200 руб студентам со средним баллом больше 4 остальным 100 руб?, который относится к категории Информатика. По уровню сложности вопрос соответствует учебной программе для учащихся 5 — 9 классов. В этой же категории вы найдете ответ и на другие, похожие вопросы по теме, найти который можно с помощью автоматической системы «умный поиск». Интересную информацию можно найти в комментариях-ответах пользователей, с которыми есть обратная связь для обсуждения темы. Если предложенные варианты ответов не удовлетворяют, создайте свой вариант запроса в верхней строке.
Как высчитать стипендию в 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 установим курсор мыши в окне ввода формул после И перед открывающей скобкой — в результате будет открыто окно ввода аргументов для функции И():
Введем аргументы — в качестве первого — С12>=4, в качестве второго С12 установив курсор мыши в строке формул после слова ЕСЛИ перед открывающей скобкой :
В окно Значение_если_истина введем соответствующее выполнению условия значение Коэффициента, равное 1,2, а в Значение_если_ложь необходимо ввести вложенную функцию ЕСЛИ(), с целью дальнейшей проверки условий (условия 8 установить указатель мыши в строке формул, после второго ЕСЛИ перед открывающей скобкой . Будет вновь предложено окно для ввода аргументов функции ЕСЛИ(), на сей раз — первой вложенной. Как и в предыдущем случае, введем в окно Логического_значения функцию И(), перейдем в окно ввода аргументов уже для нее и в качестве аргументов введем — в качестве первого — С12>=8, в качестве второго С12 =4;C12 =8;C12 Автор admin Опубликовано 7 декабря 2011 10 августа 2014 Рубрики Excel, Новое, Советы
Практическая работа по Excel «Стипендиальная ведомость»
Стипендиальная ведомость факультета представляет собой ЭТ 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);
Ложь – стипендия без изменений.
Для построения диаграммы, отображающей стипендиальные фонды групп факультета, необходимо воспользоваться данными на листе «общий фонд».
Практическая работ а «Стипендиальная ведомость»
Стипендиальная
ведомость факультета представляет собой ЭТ 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 руб.
Для назначения
стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя
одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – средний балл <3,5;
Стипендия с надбавкой хорошистам и отличникам
Студентам,
имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка
в размере 10% от стипендии.
Для расчета
стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция
включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:
Условие1 – категория «отличник»;
Условие2 – категория «хорошист»;
Истина – стипендия с надбавкой 10%;
Стипендия с доп.надбавкой
Всему факультету дополнительно
выделили 50% стипендиального фонда. Необходимо распределить его между
отличниками. Для выполнения данных расчетов необходимо:
1. Вставить дополнительный лист
в рабочую книгу, назвать его «общий фонд».
2. Рассчитать величину
стипендиального фонда каждой группы. Для этого внизу каждой таблицы, в поле
Стипендия с надбавкой хор и отл, вставить функцию СУММ.
3. Рассчитать первоначальный
стипендиальный фонд. Для этого используется Консолидация данных, расположенная
на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма,
далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые
значения фондов по каждой группе.
После нажатия ОК
в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего
поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.
4. Рассчитать дополнительный
фонд, умножив общий фонд на 50%
5. Рассчитать количество
отличников на факультете. Для этого необходимо воспользоваться функцией
СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций
указать диапазон ячеек первой таблицы, содержащей информацию о категории
успеваемости. Критерий для отбора указать «отличник».
Т.к. у нас на
рабочем листе две таблицы, для расчета общего количества отличников на курсе
необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить
вычисления по каждому курсу отдельно.
Таблица в режиме
отображения формул выглядит следующим образом
6. Рассчитать общее количество
отличников. Для этого вставить функцию СУММ внизу таблицы.
7. Рассчитать величину надбавки
каждому отличнику. Для этого необходимо дополнительный фонд разделить на
количество отличников.
8. Рассчитать Стипендию с
доп.надбавкой.
Для этого
используется условная функция ЕСЛИ. Данная функция включает в себя одно
Условие, Истину и Ложь. Выглядит следующим образом:
Условие – категория успеваемости
студента — «отличник»;
Истина – стипендия с дополнительной
надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное
значение – клавишей F 4);
Ложь – стипендия без изменений.
Для построения
диаграммы, отображающей стипендиальные фонды групп факультета, необходимо
воспользоваться данными на листе «общий фонд».
Задание
№ 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: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) , но подскажите как написать все вместе сразу, чтоб работало?
Очень надеюсь на Вашу помощь
Формулировка задания 2 лабораторной работы 3-3:
Дана таблица с итогами экзаменационной сессии. Составить электронную таблицу, определяющую стипендию согласно правилу.
Полная формулировка задания такова:
- если 4 <= s < 8, то k=1.5,
- если 8 <= s < 10, то k=1.8,
- если s= 10, то k=2.0
Выполнение:
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 <= s < 8) — в случае выполнения коэффициент равен1.5,
- — затем — второе условие (8 <= s < 10) — в случае выполнения коэффициент равен1.8,
- — затем третье условие (s = 10) — в случае выполнения коэффициент равен 2,
- — а в случае невыполнения ни одного из условий необходимо выдать сообщение «неправильные данные».
Проверка будет осуществляться с использованием нескольких вложенных функций ЕСЛИ(1Логическое_ выражение; 2Значение_если_истина;3Значение_если_ложь). Исходными данными этой функции является Логическое выражение (на первом месте) и два значения — а результатом ее выполнения — одно из заданных на втором либо на третьем месте значений, в зависомости от того, равно ли ИСТИНЕ логическое выражение.
Для ввода функции надо сделать активной ячейку D12 (Коэффициент, используемый для расчета стипендии студента Макарова С.П.), и вызвав окно мастера функций (нажав на кнопку fx), выбрать функцию ЕСЛИ():
Вначале введем условия (Логическое выражение) для первого варианта коэффициента (если 4 <= s <8, то k=1.2). — Как сказано выше, в том случае, если средний балл студента больше либо равен 4 но меньше 8, то его коэффициент равен 1,2. Учитывая, что условия фактически 2 (первое — s больше либо равно 4, второе — s меньше 8 ), нам понадобится еще одна логическая функция — И(), в результате выполнения которой выдается ИСТИНА в том случе, если все ее аргументы (логические выражения т.е. условия) также равны ИСТИНЕ. В данном случае у этой функции будет два аргумента (условия) — выполнение первого условия (4 <= s) и выполнение второго условия (s <8). В случае выполнения обоих коэффициент будет равен 1,2.
Для их записи установим курсор мыши в окно ввода логического выражения функции ЕСЛИ() и запишем И()
после чего установим курсор мыши в окне ввода формул после И перед открывающей скобкой — в результате будет открыто окно ввода аргументов для функции И():
Введем аргументы — в качестве первого — С12>=4, в качестве второго С12<8:
и вернемся ко вводу аргументов функции ЕСЛИ(), установив курсор мыши в строке формул после слова ЕСЛИ перед открывающей скобкой :
В окно Значение_если_истина введем соответствующее выполнению условия значение Коэффициента, равное 1,2, а в Значение_если_ложь необходимо ввести вложенную функцию ЕСЛИ(), с целью дальнейшей проверки условий (условия 8 <= s < 10, при выполнении которого коэффициент k=1.8 и условия s= 10 при выполнении которого k=2.0)
После ввода значения ЕСЛИ(), необходимо вновь установить указатель мыши в строке формул, после второго ЕСЛИ перед открывающей скобкой . Будет вновь предложено окно для ввода аргументов функции ЕСЛИ(), на сей раз — первой вложенной. Как и в предыдущем случае, введем в окно Логического_значения функцию И(), перейдем в окно ввода аргументов уже для нее и в качестве аргументов введем — в качестве первого — С12>=8, в качестве второго С12<10:
и после возврата ко вводу аргументов второй функции ЕСЛИ() необходимо ввести значение коэффициента 1,8 в окно Значение_если_истина и еще одну функцию ЕСЛИ() в окно Значение_если_ложь
При вводе аргументов в третью вложенную функцию ЕСЛИ() условие формулируется проще C12=10, соответственно, нет необходимости в задействовании функции И(). В том случае, если данное условие выполняется, коэффициент равен 2, в том случе, если нет, то (с учетом того, что задействованы все возможные варианты значения среднего балла от 4 до 10 включительно) необходимо вывести текстовое значение «неправильные данные«.
В итоге будет введена функция
=ЕСЛИ(И(C12>=4;C12<8);1,2;ЕСЛИ(И(C12>=8;C12<10);1,6;ЕСЛИ(C12=10;2;»неправильные данные»)))
в результате выполнения которой в ячейке D12 (коэффициент студента Макарова) будет выведено 1,2.
5.2 А после копирования значения функции в другие ячейки диапазона D12:D16, которые соответствуют значениям коэффициентов других студентов будет получена следующая таблица:
то есть у всех студентов значение коэффициента равно 1,2. Однако, например, если изменить оценку студента Макарова С.П. по предмету Информатика на 10, его средний балл превысит 8 и, соотвественно, коэфициент повысится до 1,6:
6. Остается лишь по известному коэффициенту найти размер стипендии студента Макарова С.П. (просто умножив коэффициент на размер минимальной стипендии — пусть он будет равен 500), а затем, скопировав функцию в остальные ячейки — стипендий остальных студентов.
Дла этого установим указатель мыши в ячейке E12 (Стипендия студента Макарова) и, перейдя в строку ввода в строке ввода формул введем =500*D12, то есть переножим величину минимальной стипендии m (равной 500) на значение коэффициента студента. В результате получим, что стипендия студента Макарова равна 800 (тыс. руб.)
Скопируем данную формулу в диапазон E12:E16 и получим значения стипендий всех студентов: