Рассчитать стипендию в excel
Задание № 3. Подготовьте для каждой группы ведомость (рис. 3.4) назначения студентов на стипендию по результатам экзаменационной сессии. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании 2, формулам.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №________
Рис. 3.4. Форма стипендиальной ведомости
Ознакомьтесь с алгоритмом действий по технологии выполнения данного задания:
1. Загрузите экзаменационную ведомость.
2. На новом листе создайте ведомость стипендии (см. рис.3.4) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис.3.9.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается.
5. Подсчитайте сумму стипендиального фонда для всей группы.
6. Сверьте полученные общий вид таблицы, результаты и структуры формул с тем, что отображено на рис.3.5 и 3.6.
Рис. 3.5. Электронная таблица Ведомость назначения на стипендию в режиме отображения значений
Методика выполнения работы
выполните команду Файл, Открыть;
в диалоговом окне установите следующие параметры:
3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 5.
Для этого введите название таблицы - ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕН-
ДИЮ Группа № и названия столбцов - № п/п; Фамилия, имя, отчество; Стипендия,
задайте шрифт и тип выделения - полужирный.
4. Укажите размер минимальной стипендии в ячейке D3;
5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.5. Скорректируйте расхождение.
введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
Категория: Статистические
Имя: СРЗНАЧ
щелкните по кнопке , появится панель ввода аргументов функции СРЗНАЧ;
установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
щелкните по кнопке ;
в ячейке С6 появится значение, рассчитанное по формуле:
установите курсор в ячейке С6;
наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;
нажмите левую кнопку мыши и протащите ее до конца этого столбца;
просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.
установите курсор в ячейке D6;
введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
щелкните по кнопке , появится панель ввода аргументов функции СЧЕТ;
установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
установите курсор во 2-й строке (имя Значение 2), щелкните на названии листаЭкзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экза-
мену;
установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
щелкните по кнопке ;
в ячейке D6 появится значение, рассчитанное по формуле
9. Скопируйте формулу по всем ячейкам столбца D аналогично п.7.
10. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид: =4,5;D6=3); =3;D6=3);$D$3;0)'>$D$3*1,5 ;ЕСЛИ(И(C6>=3;_D_6=3);$D$3;0))__Внимание!'>=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(C6>=3; D6=3);$D$3;0))
Внимание!
1. В структуре формулы имеются вложенные функции И(. ), ЕСЛИ(. ). Для ввода этих функций надо воспользоваться
кнопкой вызова функции, находящейся в строке
ввода под панелями.
2. При наборе формулы автоматически расставляются круглые скобки и разделительный символ - точка с запятой.
3. В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.
4. В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.
5. Если после ввода формулы появится синтаксическая ошибка,
то следует проверить количество скобок, наличие разделите-
ля (точки с запятой), заменить в числе точку на запятую или
наоборот.
установите курсор в ячейке Е6;
введите команду Формулы, Вставить функцию и выберите в диалоговом окне параметры:
щелкните по кнопке , появится панель ввода аргументов функции ЕСЛИ;
курсор будет находиться в 1-й строке (имя — Логическое выражение) панели ввода аргументов функции;
нажмите кнопку вызова функции в строке ввода, выберите категорию Другие функции и функцию И, нажмите кнопку ;
появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое значение1;
щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5
установите курсор на второй строке Логическое значение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере - это число 3) D6=3
щелкните по кнопке . В результате в строке ввода должно появиться выражение =ЕСЛИ(И(С6>=4,5;D6=3)
щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ;
установите курсор во 2-й строке (Значение_ если_ истина), щелкните в ячейке D3 и нажмите клавишу . Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение $D$3*1,5
установите курсор в 3-й строке ( Значение_ если_ ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(С6>=3;D6=3);$D$3;0)
после окончания формирования формулы нажмите кнопку .
вводите другие оценки в экзаменационные ведомости;
измените минимальный размер стипендии.
8. Закройте рабочую книгу командой Файл, Закрыть.
Работа 3. Построение, редактирование и форматирование диаграмм
Задание № 1. Для таблицы на рис.3.7 постройте два вида диаграмм - внедренную на лист с исходными данными и на отдельном листе.
Для этого вам необходимо выполнить следующие действия:
создать рабочую книгу;
сохранить рабочую книгу;
переименовать Лист1 на Успеваемость.
Создать таблицу в соответствии с рис.3.7 и вычислить средний балл по факультету по каждому предмету.
Рис. 3.7. Таблица успеваемости к заданию 1
3. Построить внедренную диаграмму, оформив согласно рис.3.8.
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:K8) выбирается средний балл, значение которого ближе всего к значению среднего балла ячейки G4, а затем и соответствующий этому выбранному значению коэффициент. Если же средний бал в столбе G равен 0, то коэффициент сразу должен быть принят нулю. Такой алгоритм может быть реализован формулой: =ЕСЛИ(G4=0;0;ВПР(G4;J$4:K$8;2;1)*K$1). Функция ВПР() выбирается из Категории Ссылки и массивы.
8. Сумму начисленной стипендии (Н9) вычислить с помощью функции СУММ().
Пояснения к п. 7. Значения аргументов функции ВПР() означают следующее:
ВПР(<что искать>;<где искать>;<откуда взять>;<как искать>) или
ВПР(<искомое значение>;<область поиска>;<номер столбца извлечения>; <тип поиска>).
Если <тип поиска>=1, то поиск – приблизительный, если <тип поиска>=0, поиск точный.
2. Составить электронную таблицу для выплаты стипендий.
· Построить таблицу по образцу
· Графу Ф.И.О. скопировать с исходной таблицы.
· Графы средний балл и стипендия рассчитать по соответствующим формулам с использованием логических функций ЕСЛИ, И, ИЛИ, НЕ.
Выполнение:
1. В начале необходимо внести на лист Excel таблицу согласно образцу, заполнив необходимое количество записей – студентов и их оценок по трем предметам:
2. Необходимо также заполнить ячейки С8, D8 и E8, в которых с использованием функции СРЗНАЧ() необходимо рассчитать средний балл по каждому из трех предметов.
Оптимально использовать кнопку панели инструментов Автосумма:
Сначала необходимо сделать активной ячейку 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.2,
- затем – второе условие (8 <= s < 10) – в случае выполнения коэффициент равен - 1.8,
- затем третье условие (s = 10) – в случае выполнения коэффициент равен - 2,
Проверка будет осуществляться с использованием нескольких вложенных функций ЕСЛИ(Логическое_ выражение; Значение_если_истина;Значение_если_ложь). Исходными данными этой функции является Логическое выражение (на первом месте) и два значения – а результатом ее выполнения – одно из заданных на втором либо на третьем месте значений, в зависимости от того, равно ли ИСТИНЕ логическое выражение.
Для ввода функции надо сделать активной ячейку 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,8;ЕСЛИ(C12=10;2;"неправильные данные")))
в результате выполнения которой в ячейке D12 (коэффициент студента Макарова) будет выведено 1,2.
5.2 А после копирования значения функции в другие ячейки диапазона D12:D16, которые соответствуют значениям коэффициентов других студентов будет получена следующая таблица:
то есть у всех студентов значение коэффициента равно 1,2. Однако, например, если изменить оценку студента Макарова С.П. по предмету Информатика на 10, его средний балл превысит 8 и, соответственно, коэффициент повысится до 1,8:
6. Остается лишь по известному коэффициенту найти размер стипендии студента Макарова С.П. (просто умножив коэффициент на размер минимальной стипендии – пусть он будет равен 500), а затем, скопировав функцию в остальные ячейки – стипендий остальных студентов.
Для этого установим указатель мыши в ячейке E12 (Стипендия студента Макарова) и, перейдя в строку ввода в строке ввода формул введем =500*D12, то есть перемножим величину минимальной стипендии m (равной 500) на значение коэффициента студента. В результате получим, что стипендия студента Макарова равна 900 (тыс. руб.)
Скопируем данную формулу в диапазон E12:E16 и получим значения стипендий всех студентов.
Размер стипендии, выплачиваемой студентам, зависит от их успеваемости. Размер стипендии определяется вузом, но не может быть меньше размера базовой стипендии, устанавливаемого правительственным постановлением. Стипендия может не выплачиваться студентам, получившим на последней экзаменационной сессии одну или более удовлетворительные оценки. Для того, чтобы вузу не выйти за пределы финансируемого за счет государственного бюджета стипендиального фонда вуза, полезно прикинуть, какие необходимы финансовые средства для выплаты стипендии студентам при установленных вузом размерах стипендии. Эту задачу Вы решите в процессе выполнения приведенного ниже задания. При этом исходите из нижеприведенных условий.
ü Вузом установлены, например, следующие размеры стипендии студентов в зависимости от их успеваемости на последней экзаменационной сессии:
· стипендия студента-отличника составляет 150% базовой стипендии;
· стипендия студента, имеющего хорошие и отличные или только хорошие оценки, составляет 125% базовой стипендии;
· стипендия студента, имеющего одну или более удовлетворительных оценок, составляет 100% базовой стипендии, но назначается лишь при условии достаточности стипендиального фонда.
Студентам, получившим и пересдавшим неудовлетворительную оценку, стипендия не назначается.
Выполнение задания
Запустите Excel и щелкните на кнопке Сохранить.
С помощь кнопки Создать папку в появившемся окне Сохранение документа создайте на диске d свою рабочую папку и сохраните в ней файл Книга1 под именем Стипендия.xls.
Создайте электронную таблицу, соответствующую рис. 1. В таблице применяйте шрифт Times New Roman размером 12.
В ячейки G4:G9 введите формулы для вычисления числа студентов факультета, успешно сдавших экзаменационную сессию.
В ячейках B10:G10 запишите формулы для вычисления итогов экзаменационной сессии вуза (суммы по колонкам). Результат должен соответствовать рис. 2.
 |
| Рис. 1. Исходные данные |
 |
| Рис. 2. Результаты расчета итогов экзаменационной сессии |
 |
| Рис. 3. Итоги экзаменационной сессии вуза |
С помощью мастера диаграмм постройте объемный вариант круговой диаграммы, отображающей итоги экзаменационной сессии вуза. Эта диаграмма должна иметь вид, показанный на рис. 3.
На втором листе рабочей книги подготовьте электронную таблицу, аналогичную изображенной на рис.4.
 |
| Рис. 4. Заготовка таблицы для расчета стипендиального фонда |
Для удобства учета возможного изменения размера базовой стипендии запишите это значение (для примера будем считать его равным 200 руб.) в свободную ячейку D7, предварительно установив в этой ячейке формат Денежный с двумя разрядами дробной части (команда Формат, Ячейки, Число). Затем присвойте этой ячейке имя Базовая (команда Вставка, Имя, Присвоить). Запишите в ячейку C7 текст Базовая стипендия = и установите в этой ячейке выравнивание по правому краю.
Установите в ячейках B4:E4 формат Денежный с двумя разрядами дробной части, а в ячейках B5:E5 установите формат Числовой с одним разрядом дробной части.
Проведите расчет необходимого стипендиального фонда. В ячейки B4:E4 запишите соответствующие формулы для четырех вариантов назначения стипендии.
10.1. Стипендия назначается только студентам, сдавшим экзаменационную сессию без троек. Для этого в ячейку B4 запишите формулу для расчета суммы стипендий всех студентов, успевающих отлично и студентов, успевающих отлично и хорошо или только хорошо. Вот эта формула, которую Вам необходимо осмыслить:
=Базовая/100*(Лист1!B10*150+(Лист1!C10+Лист1!D10)*125).
В формуле имеются ссылки на ячейки, находящиеся на другом листе. Например, при необходимости сослаться в формуле ячейки, находящейся на Листе2, на ячейку B10, находящуюся на Листе1, в формуле записано Лист1!B10. Еще одно новшество. Ссылка на ячейку D7, в которой записан размер базовой стипендии, осуществляется по ее имени Базовая. Тем самым эта ссылка является абсолютной.
10.2. Дополнительно к студентам, которым стипендия назначена в соответствии с п. 10.1, стипендия назначается также студентам, получившим на экзаменах не более одной тройки (формула ячейки C4).
10.3. Дополнительно к студентам, которым стипендия назначена в соответствии с п. 10.2, стипендия назначается также студентам, получившим не более двух троек (формула ячейки D4).
10.4. Дополнительно к студентам, которым стипендия назначена в соответствии с п. 10.3, стипендия назначается также студентам, получившим более двух троек или имевшим неудовлетворительные оценки (формула ячейки E4).
Для каждого из четырех вариантов назначения стипендии введите в ячейки B5:E5 формулы для вычисления доли необходимого стипендиального фонда вуза, приходящейся на одного обучающегося студента, выраженной в процентах базовой стипендии. В результате выполнения пунктов 10 и 11 задания, электронная таблица должна получить вид, соответствующий рис. 5.
 |
| Рис. 5. Итог расчета стипендиального фонда |
Теперь, зная размер стипендиального финансирования вуза, можно из рассмотренных четырех вариантов назначения стипендии выбрать тот, который будет обеспечен финансированием. Например, если стипендиальное финансирование вуза выполняется по принципу «базовая стипендия на каждого студента», то возможен третий вариант назначения стипендии (еще останется резерв на пособия нуждающимся студентам), а четвертый вариант не будет обеспечен финансированием.
С помощью мастера диаграмм постройте объемный вариант цилиндрической гистограммы, отображающей в процентах базовой стипендии долю необходимого стипендиального фонда, приходящегося на одного студента вуза. Эта диаграмма должна иметь вид, показанный на рис. 6.
Читайте также: