Oc-windows.ru

IT Новости из мира ПК
23 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

Табличные процессоры. Лабораторная работа 4.
Работа с формулами. Подготовка ведомости назначения студентов на стипендию по результатам экзаменационной сессии

1. Откройте файл

Загрузите экзаменационную ведомость Session

2. На новом листе создайте ведомость стипендии (см. рис. 1)

Создайте в книге новый лист – Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов

3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 1

ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №

Минимальный размер стипендии —

№ п/пФамилия, имя, отчествоСтипендия

Итого стипендиальный фонд по группе —

Рис. 1. Общий вид ведомости назначения на стипендию

Введите название таблицы – ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ; Группа №; и названия столбцов: № п/п; Фамилия, имя, отчество; Стипендия. Задайте шрифт 12; тип выделения – полужирный.

4. Укажите размер минимальной стипендии

В ячейку D3 введите размер минимальной стипендии; например 200.

5. Вставьте дополнительные столбцы

Выделите столбец Стипендия и выполните 2 раза команду Вставка-Столбец, назовите новые столбцы – Средний балл и Кол-во сданных экзаменов.

6. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту

Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента, например, Снегирева. Для этого:

  • установите курсор в ячейке С6; щелкните по кнопке на панели Стандартная и выберите в диалоговом окне параметры: Категория: Статистические, Имя: СРЗНАЧ
  • щелкните по кнопке , появится панель ввода аргументов функции СРЗНАЧ;
  • установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
  • установите курсор во 2-й строке (имя Число 2),
  • щелкните на названии листа Экзамен 2 и выберите ячейку D6 с оценкой того же студента по второму экзамену; и т.д. (по всем листам). Затем ОК

В ячейке С6 появится значение, рассчитанное по формуле:

=СРЗНАЧ(‘Экзамен1’!D6; ‘Экзамен 2’!D6; ‘Экзамен 3’!D6; ‘Экзамен 4’!D6).

7. Скопируйте формулу по всем ячейкам столбца С. Просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке

Установите указатель мыши на марке заполнения и при нажатой кнопке протащите формулу до конца вычислений столбца С.

Читать еще:  Можно ли объединить ячейки в excel

8. Посчитайте количество сданных экзаменов каждого студента

Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. При этом технология ввода будет аналогична описанной ранее:

  • установите курсор в ячейке D6;
  • щелкните по кнопке на панели Стандартная и выберите в диалоговом окне Категория: Статистические: Имя: СЧЕТ,
  • щелкните по кнопке , появится панель ввода аргументов функции СЧЕТ;
  • установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
  • установите курсор во 2-й строке (имя Значение 2), щелкните на названии листа Экзамен 2 и выберите ячейку D6 с оценкой того же студента по второму экзамену; и т.д. (по всем листам). ОК

В ячейке D6 появится значение, рассчитанное по формуле:

=СЧЕТ(‘Экзамен 1’!D6; ‘Экзамен 2’!D6; ‘Экзамен 3’!D6; ‘Экзамен 4’!D6).

Скопируйте формулу по всем ячейкам столбца D так же, как в предыдущем пункте.

9. Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:
если средний балл не менее 4,5 – выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 (включительно) до 4,5 – выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается

Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид:

=ЕСЛИ( И(С6>=4,5;D6=4); $D$3*1,5; ЕСЛИ(И(С6>=3;D6=4);$D$3;0) )

  1. В структуре формулы имеются вложенные функции И(. ), ЕСЛИ(. ). Для ввода этих функций надо воспользоваться кнопкой вызова функции, находящейся в строке ввода под панелями.
  2. При наборе формулы автоматически расставляются скобки и разделительный символ «;».

Технология ввода формулы будет аналогична описанной ранее:

  • установите курсор в ячейке Е6;
  • щелкните по кнопке на панели Стандартная и выберите в диалоговом окне параметры: Категория: Логические, Имя: ЕСЛИ;
  • щелкните по кнопке , появится панель ввода аргументов функции ЕСЛИ;
  • курсор будет находиться в 1-й строке (имя – Логическое выражение) панели ввода аргументов;
  • нажмите кнопку вызова функции в строке ввода, выберите Другие функции и функцию И, ;
  • появится второе окно ввода И, курсор автоматически будет установлен в строке Логическое1;
  • щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5;
  • установите курсор на второй строке Логическое выражение1 и аналогично сформируйте выражение, указывающее на необходимое количество сданных экзаменов (в данном примере – число 4). D6=4;
  • щелкните по кнопке . В результате в строке ввода должно появиться выражение:
    =ЕСЛИ(И(С6>=4,5;D6=4)
  • щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ;
  • установите курсор во 2-й строке (имя – Значение если истина), щелкните в ячейке D3 и нажмите клавишу . Появится символ $ перед именем столбца и номером строки Введите выражение *1,5. В результате в этой строке будет выражение $D$3*1,5;
  • установите курсор в 3-й строке (имя Значение если ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(С6>=3;D6=4);$D$3;0), Затем ОК.

Скопируйте эту формулу в другие ячейки столбца Е так же, как вы делали ранее.

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

Выделите ячейки с размером стипендии каждого студента щелкните по кнопке Автосумма (Σ) на панели инструментов или наберите формулу вручную (=СУММ(через двоеточие укажите номера ячеек)).

11. Сверьте полученные общий вид таблицы с тем, что отображено на рис. 2

Выполните команду Сервис, Параметры, вкладка Вид и установите флажок Формулы. Просмотрите внимательно формулы, затем уберите флажок.

12. Проверьте работоспособность таблицы

Введите другие оценки, измените минимальный размер стипендии, проследите, как меняются итоговые цифры в таблице.

13. Сохраните файл

Сохраните книгу под именем Money.

ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №AC118
Минимальный размер стипендии200,00р.
№ п/пФамилия Имя ОтчествоСредний баллКол-во экзаменовСтипендия
1Снегирев А.П.54300,00р.
2Орлов К.Н.3,754200,00р.
3Воробьева В.Л.34200,00р.
4Голубкина О.Л.21— р.
5Дятлов В.А.54300,00р.
6Кукушкин М.И.2,754— р.
7.
Итого стипендиальный фонд по группе1 000,00р.

Рис. 2. Электронная таблица «Ведомость назначения на стипендию» в режиме отображения значений

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

Как организовать дистанционное обучение во время карантина?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расчет стипендии в excel

Тема 5. ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL

Лабораторная работа №13. Построение связанных таблиц: задание формул со ссылками на другие рабочие листы и другие книги, сохранение и открытие связанных книг

Пример 1. Сформировать в Excel таблицу для расчета сумм стипендий студентам факультета ФЭУТ в соответствии с формой, представленной в таблице 4.1. При этом учесть следующие условия:

    Читать еще:  Вкладка главная в excel

    стипендия не начисляется студентам, обучающимся на платной основе;

    студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;

    студенты, имеющие средний балл более 4.5, получают 50% надбавки к стипендии.

    Технология создания связанных таблиц

    1. В рабочей книге ФЭУТ.xls перейти на новый лист и переименовать его на Начисления.

    2. В строку 1 ввести название таблицы Ведомость начисления стипендии студентам ФЭУТ 1-го курса.

    3. В ячейку B2 ввести текст Сумма минимальной стипендии:

    4. В ячейку D2 ввести сумму минимальной стипендии — 12000.

    5. Оформить шапку таблицы. Для этого в ячейки А3:Е3 ввести заголовки столбцов таблицы.

    6. Столбец Номер зачетки заполнить данными. Чтобы не набирать еще раз номера зачеток, в столбце Номер зачетки установить ссылки на ячейки B5:B16, находящиеся на листе Список. Для того, чтобы при изменении номеров зачеток на листе Список, на листе Начисления они изменялись автоматически, выполнить действия:

    перейти на лист Начисления, активизировать ячейку А4 и ввести знак «=» для определения формулы;

    перейти на лист Список. Ярлык листа Начисления выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде: Список!;

    выполнить щелчок левой клавишей мыши на ячейке В5 и нажать клавишу «Enter»;

    на листе Начисления в ячейке А4 будет введена формула вида: =Список!B5

    скопировать формулу из ячейки А4 в остальные ячейки столбца А5:А15.

    Замечание. Если нужная информация находится в другой книге (например в книге с именем Отчет, на листе Ведомость, в ячейке В5), то необходимо перейти в нее с помощью команды меню Окно (эта книга должна быть заранее открыта), затем перейти на нужный лист и выделить необходимую ячейку (например, В5) или диапазон ячеек. В строке формул появится ссылка вида: [Отчет.xls]Ведомость!В5

    7. Заполнить столбец Ф.И.О. данными. Для этого в ячейках В4:В15 листа Начисления установить ссылки на ячейки С5:С16 из листа Список.

    8. Ввести формулы для вычисления суммы стипендии студентам. Для этого реализовать следующую последовательность действий:

    ввести формулу для расчета стипендии первому студенту. Для этого активизировать ячейку С4 на листе Начисления;

    вызвать Мастер функций, который обеспечивает построение формулы за несколько шагов. Для этого выполнить команду Вставка -Функция;

    в окне Мастер функций — шаг 1 из 2 выбрать вид функции. Для нашего примера в списке Категория выбрать Логические, а в списке Функция — функцию Если

    щелкнуть на кнопке [ОК];

    во втором диалоговом окне задать аргументы функции. Для нашего примера, устанавливая курсор в каждой строке, ввести следующие операнды логической функции:
    Логическое_выражение И (Список!D5=1;Успеваемость!F5>=3)
    Значение_если_истина $D$2
    Значение_если_ложь 0

    Замечание. При вводе адресов ячеек в операндах можно перейти на нужный лист и щелкнуть на нужной ячейке. Ее полный адрес в поле операнда появится автоматически.

    нажать кнопку [ОК];
    В результате в ячейке С4 появится значение, рассчитанное по формуле:
    =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=3);$D$2;0)
    Для задания логического выражения используется логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
    Чтобы ссылка на ячейку D2 оставалась неизменной при копировании формулы, необходимо сделать ее абсолютной путем указания знака $ перед именем столбца и номером строки — $D$2.

    Для вычисления величины стипендии всем студентам скопировать формулу из ячейки С4 в диапазон ячеек С5:С15.

    9. Ввести формулы для вычисления величины надбавки к стипендии студентам. Для этого реализовать следующую последовательность действий:

    ввести формулу для расчета величины надбавки первому студенту в ячейку D4 на листе Начисления, загрузить Мастер функций и выбрать функцию Если;

    на втором шаге ввести следующие операнды логической функции:
    Логическое_выражение: И(Список!D5=1;Успеваемость!F5>=4,5)
    Значение_если_истина : $D$2*0,5
    Значение_если_ложь: 0

    нажать кнопку [ОК];
    В ячейке D4 появится значение, рассчитанное по формуле:
    =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=4,5);$D$2*0,5;0)

    для расчета величины надбавки всем студентам скопировать формулу из ячейки D4 в диапазон ячеек D5:D15;

    ввести формулы для расчета общей суммы начислений каждому студенту. Для этого в ячейку Е4 ввести формулу: =C4+D4 и скопировать ее в диапазон ячеек Е5:Е15. Таблица с полученными результатами приведена на рис. 4.1.

    10. Сохранить рабочую книгу.

    * Лабораторная работа заимствована из методического пособия «Лабораторный практикум по Excel» (Куратева Т.В., Ситникова М.В., Холодова Е.П., Соловьева З.М. — Мн.: БГЭУ, 2002).

    Ссылка на основную публикацию
    Adblock
    detector