8291

отработанных дней.

5. Сохраните рабочую книгу и покажите результат Вашей работы и преподавателю.

Вариант 4

Средствами электронной таблицы Excel рассчитать отпускные выплаты сотрудникам. Отпускные складываются из средней зарплаты по 3 предыдущим месяцам и премии, зависящей от стажа сотрудника.

1. На Листе1 создайте таблицу Список сотрудников с полями Ф.И.О. и

Стаж. Заполните таблицу данными для 10 сотрудников (стаж от 5 до 30 лет). Переименуйте Лист1 в Сотрудники.

2. На Листе2 создайте таблицу Коэффициент начисления премий, со следующими заголовками столбцов: Стаж, Коэффициент. Коэффициент определяется в зависимости от стажа работы следующим соотношением:

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

Переименуйте Лист2 в Премиальные коэффициенты.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.9.

Заполните таблицу данными, следуя указаниям:

а) выберите 5 сотрудников из таблицы на листе Сотрудники и внесите их фамилии в поле Ф.И.О.; для заполнения первого столбца используйте автозаполнение;

б) столбцы Апрель, Май, Июнь заполните произвольными числами от

1500 до 3000 (можно использовать функцию СЛЧИС(), генерирующую псевдослучайные числа от 0 до 1);

в) вычислите значения в столбце Среднее по трем месяцам,

используя функцию СРЗНАЧ; г) значения в поле Стаж должны проставляться автоматически для

каждого работника в соответствии с данным на листе Сотрудники (для автоматизации вычислений используйте функцию ВПР);

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

Премия = (Базовая премия) * (Коэффициент).

Замечание: для определения коэффициента воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций.

е) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением:

Выдано на руки = (Среднее по трем месяцам) +(Премия).

4.Вставьте после таблицы расчет следующих величин: общего фонда заработной платы, максимальную и минимальную премии, число сотрудников не получивших премии.

5.Сохраните Вашу работу и покажите результат преподавателю.

Вариант5

Создать таблицу на 3-х листах для начисления стипендии студентам в

зависимости от среднего балла и формы обучения.

1.На Листе1 создайте таблицу Список студентов с полями Ф.И.О. и Форма обучения. Заполните таблицу данными для 10 студентов, проставляя во втором столбце буквы «Б» (бюджет) или «Д» (договор). Переименуйте Лист1 в Студенты.

2.На Листе2 создайте таблицу Коэффициенты начисления стипендии

со следующими столбцами: Средний балл, Коэффициент. Стипендиальный

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

3. На Листе3 создайте таблицу Начисление стипендии, аналогичную таблице на рис.2.10. Заполните ее данными, следуя указаниям:

а) выберите 5 студентов из таблицы на листе Студенты и внесите их фамилии в поле Ф.И.О.; для заполнения первого столбца используйте автозаполнение;

б) столбцы Математика, Физика, Информатика, заполните произвольными числами от 0 до 5 (можно использовать функцию СЛЧИС(),

генерирующую псевдослучайные числа от 0 до 1);

в) вычислите Средний балл для каждого студента, используя функцию СРЗНАЧ;

г) столбец Форма обучения должен заполняться автоматически, в соответствии с данным на листе Студенты (для автоматизации вычислений используйте функцию ВПР);

д) заполните столбец Коэффициент, который должен определяться по таблице на листе Коэффициенты для каждого студента исходя из его среднего балла (воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

е) рассчитайте для каждого студента величину стипендии, которая выдается только бюджетникам и зависит от базовой стипендии и индивидуального коэффициента:

4.Вставьте после таблицы расчет следующих величин: общего стипендиального фонда, максимальный и минимальный средний балл, число студентов не получивших стипендию.

5.Сохраните Вашу работу и покажите результат преподавателю.

Рис. 2.10.

Вариант 6

Средствами электронной таблицы Excel рассчитать выплаты работникам нескольких филиалов в зависимости от их среднегодовой прибыли.

1. На Листе1 создайте таблицу Работа фирм-филиалов и заполните ее как показано на рис.2.11. Переименуйте Лист1 в Фирмы.

Прибыль по кварталам

Рис.2.11.

2. На Листе2 создайте таблицу Начисление премий, со следующими заголовками полей: Средняя прибыль, Премия (в %). Премия определяется следующим образом:

Заполните таблицу данными таким образом, чтобы их было удобно использовать в следующей таблице при расчете выплат каждому сотруднику. Переименуйте Лист2 в Премия.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.12. Первые два столбца заполните вручную, введя названия фирм: АЛИСА, ФЛОРА и МАРС. Остальные ячейки таблицы должны заполняться автоматически, с

помощью создаваемых Вами вычисляемых выражений:

а) вставьте формулу в ячейку С5 для определения прибыли фирмы АЛИСА за 1кв. (см. рис. 2.12), используя данные листа Фирмы;

б) продлите формулу ячейки С5 по строкам, а затем по столбцам для определения прибыли всех фирм по всем кварталам (при правильном использовании смешанных и абсолютных ссылок в исходной формуле, их не надо будет изменять при продлении, однако константу, соответствующую номеру столбца в просматриваемой таблице –третий параметр функции ВПР-

придется менять вручную: для 2кв -3, для 3кв.-4, для 4кв.-5);

в) рассчитайте среднюю прибыль за год для каждого предприятия

(используйте функцию СРЗНАЧ);

г) рассчитайте величину премии для каждой фирмы, используя данные таблицы Премия (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

д) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением:

Выдано на руки = (Средний заработок работника) +(Премия).

Рис. 2.12.

4. Вставьте после таблицы расчет следующих величин: общую прибыль за год по всем предприятиям, максимальную и минимальную среднюю прибыль,

число фирм не получивших премию.

5. Сохраните Вашу работу и покажите результат преподавателю.

Вариант 7

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

1. На Листе1 создайте таблицу Табель рабочих дней (см.рис.2.13.),

заполнив ее для 10 сотрудников (рабочий день помечается символом «Р», выходной «В», больничный «Б»). Переименуйте Лист1 в Табель.

следующими заголовками полей: Оклад, Ставка налога (в %). Налоговая ставка определяется следующим образом:

2,5%, если Оклад 8000

Заполните таблицу данными таким образом, чтобы их было удобно использовать в следующей таблице при расчете выплат в пенсионный фонд. Переименуйте Лист2 в Ставки.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.14.

Заполните таблицу данными, следуя указаниям:

а) для заполнения первых двух столбцов скопировать данные из таблицы на листе Табель;

б) число отработанных дней должно вычисляться автоматически с учетом данных таблицы на листе Табель (используйте функцию СЧЕТЕСЛИ);

в) заполоните столбец Оклад, рассчитав его по формуле: Олад= (Отработано дней)*(Оплата 1-го дня);

г) рассчитайте отчисления в пенсионный фонд для каждого работника, используя данные таблицы Ставки (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

д) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением: Выдано на руки =Оклад – Отчислено в пенс.фонд.