Информационные системы и технологии в финансах. Практическое занятие. Расчет заработной платы

Цель: научиться проводить вариантные расчеты в Еxcel, с использованием функции "ЕСЛИ" в сложных случаях с использованием вложенных формул и элементов управления листа на примере расчета заработной платы; получить представление о создании пользовательских функций с использованием втсроенного языка Visual Basic.

План:

Изучить порядок начисления зарплаты и создать на листе исходную таблицу.

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

С помощью редактора Visual Basic создать пользовательскую функцию для расчета зарплаты.

Решить обратную задачу с помощью подбора параметра.

Порядок выполнения работы.

Создайте новый документ Excel. Внесите в него заготовку таблицы, содержащую список работников и заголовки столбцов. Таблицу начните с четвертой строки.

Внесите в нее следующие данные:

Фамилия

Должность

Оклад

Иванов А. А.

Директор

1375

Петров С. В.

Гл. бухлалтер

1100

Сидоров С. М.

Менеджер

800

Терещенко А. П.

Охранник

390

Светлов И. А.

Аналитик

550

Далее заполните таблицу произвольными именами, так, чтобы общее число сотрудников было не менее 10.

Расчет заплаты будем проводить двумя способами: по старому и новому порядку. Для выбора между вариантами расчета создадим ячейку, позволяющую сделать выбор. Внесите в ячейку А2: "новая система", а в ячейку B2: "старая система". Выделите ячейку А3 и выберите пункт меню "Данные — проверка". В поле "тип данных" выберите "Список" и укажите диапазон ячеек, содержащий введенные словосочетания ("старая система" и "новая система"). Проверьте, как работает получившийся список.

Порядок начисления зарплаты

Старый вариант:

Отчисления в Пенсионный фонд – до 150 грн. – 1%, больше
150 грн. – 2%.

Отчисления в Фонд социального страхования по временной потере трудоспособности – 0,5%

Отчисления в Фонд социального страхования на случай безработицы – 0,5 %.

Подоходный налог определяется по таблице:

Зарплата

Подоходный налог

18-85

10% с превышения 17 грн.

86-170

6,80 грн +15% от превышения 85 грн

171 — 1020

19,55+20% от превышения 170

1021 — 1700

189,55+30% от превышения 1020

Более 1701

393,55+40% от превышения 1700

Сумма к выдаче определяется по формуле:

Сумма к выдаче= Оклад -(Сумма удержаний в фонда)- Подоходный налог.

Новая система.

Отчисления в Пенсионный фонд – до 150 грн. – 1%, больше
150 грн. – 2%.

Отчисления в Фонд социального страхования по временной потере трудоспособности – 0,5%.

Отчисления в Фонд социального страхования на случай безработицы – 0,5 %.

Социальная льгота (только при зарплате > 510 грн.) 30% от минимальной зарплаты (237 грн.).

Подоходный налог определяется по формуле:

Налог=13%*(Оклад – Соц. Льгота-(Сумма удержаний в фонды))

Сумма к выдаче определяется по формуле:

Сумма к выдаче= Оклад –(Сумма удержаний в фонды)- Подоходный налог.

Создание формул в ячейках таблицы (приведенные формулы носят ориентировочный характер, в каждом конкретном случае будут свои адреса ячеек!).

Отчисления в Пенсионный фонд определяются с помощью функции ЕСЛИ, которая будет иметь примерно такой вид: ЕСЛИ(C7<=150;C7*0,01;C7*0,02)

Подоходный налог определяется с помощью функции ЕСЛИ, примерный вид функции: (C7-ЕСЛИ(C7<510;237*0,3;0)-G7)*0,13.

Суммарное удержание зависит от того, какая система, старая или новая, выбрана для расчета, поэтому снова используем функцию ЕСЛИ, которая возвращает то или иное значение, в зависимости от значения ячейки А3; для учета таблицы подоходного налога используем вложенные функции ЕСЛИ. Формула будет иметь примерно такой вид:

ЕСЛИ($I$5="новая система";G7+H7;

G7+ЕСЛИ(И(C7>=18;C7<=85);0,1*(C7-17);ЕСЛИ(И(C7>=86;C7<=170);6,8+0,15*(C7-85))+ЕСЛИ(И(C7>=171;C7<=1020);19,55+0,2*(C7-170))+ЕСЛИ(И(C7>=1021;C7<=1700);189,55+0,3*(C7-1020))))

Сумма к выдаче определяется как разность оклада и суммарного удержания.

Заполните таблицу формулами, проведите расчет зарплаты и ответьте на следующие вопросы:

У кого из сотрудников прибавка к зарплате при переходе к новой системе станет наибольшей?

Какой оклад необходимо назначить Петрову, чтобы он получал на руки 600 грн? (Решение провести с помощью подбора параметра)

Какие оклады необходимо назначить сотрудникам, чтобы сумма их зарплат, выданная на руки, равнялась 15000 грн.? (Решение провести с помощью поиска решения).

Создайте пользовательскую функцию с именем "Зарплата", содержащую следующий текст:

Dim a As Double, b As Double, c As Double

If v < 150 Then a = 0.01 * v Else a = 0.02 * v

B = 0.01 * v

Select Case v

Case 0 To 17

C = 0

Case 18 To 85

C = 0.1 * (v — 17)

Case 86 To 170

C = 6.8 + 0.15 * (v — 85)

Case 171 To 1020

C = 19.55 + 0.2 * (v — 170)

Case 1021 To 1700

C = 189.55 + 0.3 * (v — 1020)

Case Is > 1701

C = 393.55 + 0.4 * (v — 1700)

End Select

Зарплата = v — a — b – c

После создания макроса вызовите окно макросов (Alt+F8), наберите имя макроса и нажмите кнопку "Свойства". В открывшемся окне введите описание функции "Расчет зарплаты" и нажмите "ОК".

Внесите эту функцию в отдельный столбец исходной таблицы с помощью мастера функций.

Контрольные вопросы:

Для чего используются функции "ЕСЛИ" и "И"?

Как вводятся сложные формулы?

Как создается макрокоманда?

Каковы особенности использования макрокоманд в мастере формул?

Метки: ,