Цель: научиться проводить вариантные расчеты в Е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), наберите имя макроса и нажмите кнопку "Свойства". В открывшемся окне введите описание функции "Расчет зарплаты" и нажмите "ОК".
Внесите эту функцию в отдельный столбец исходной таблицы с помощью мастера функций.
Контрольные вопросы:
Для чего используются функции "ЕСЛИ" и "И"?
Как вводятся сложные формулы?
Как создается макрокоманда?
Каковы особенности использования макрокоманд в мастере формул?