Создать в excel таблицу ведомость на стипендию

Обновлено: 07.07.2024

Задание № 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. Построить диаграмму другого типа и разместить ее на отдельном листе.

Стипендиальная ведомость факультета представляет собой ЭТ 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% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание №1 Создание и заполнение таблицы Excel данными и формулами.

1. Создать в среде EXCEL электронную таблицу Экзаменационная Ведомость и заполнить ее данными. Отформатировать согласно образцу.

ввести дополнительное количество столбцов, по одному на каждый вид оценки
(5 столбцов);
в первые ячейки столбцов ввести вспомогательные формулы с помощью Мастера функций;

скопировать эти формулы во все остальные ячейки дополнительных столбцов;
под таблицей (в выделенные ячейки) ввести формулы подсчета суммарного количества полученных оценок определенного вида с помощью функции СУММ;
подсчитать общее количество (ИТОГО) всех полученных оценок другим способом – Автосумма;
сделать дополнительные столбцы скрытыми;
провести коррекцию оценок и оценить полученные результаты;
переименовать текущий лист на Экзамен1, сохранить рабочую книгу под именем Задание №1.

4. На новом листе Экзамен3 создать третий вариант Экзаменационной ведомости. Провести коррекцию оценок. Сохранить документ.

1. На новом листе Стипендия создать Ведомость назначения на стипендию и скопировать на нее список группы из Экзаменационной ведомости.

2. В отдельной ячейке задать размер минимальной стипендии – 1100 руб.

3. Вычислить средний балл на основе трех Экзаменационных ведомостей по каждому студенту:

например: =СРЗНАЧ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)

4. Подсчитать количество сданных каждым студентом экзаменов с учетом неявок:

например: =СЧЕТ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)

если средний балл равен 5 – выплачивается 50% надбавка к минимальной стипендии;
если средний балл от 4 до 5 – выплачивается минимальная стипендия;
если средний балл меньше 4 – стипендия не выплачивается.

6. Подсчитать сумму стипендиального фонда для всей группы.

7. Применить условное форматирование Ведомости (заливка ячеек и начертание шрифта)

для трех столбцов: средний балл, количество сданных экзаменов, стипендия.

8. Скрыть формулы, установить защиту на неизменяемые ячейки (снять защиту с ячеек с фамилиями студентов и минимальным размером стипендии), лист, книгу.

9. Сохранить и защитить документ (установить пароль на открытие документа).

10. На новом листе рассчитать начисление стипендии по следующему условию:

Задание № 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, поиск точный.

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