Заполняем данные на листе «Общ» данные берутся произвольные

Дипломные работы на заказ

Заполняем данные на листе «Общ», данные берутся произвольные.

Чтобы рассчитать размер вычета, используется формула

В формуле для текущей строки проверяется, если нет ни одного ребенка (E2=0), то вычет равен нулю, если детей 1 или 2 ( ИЛИ(E=1;E2=2) ), то вычет равен количеству детей умноженному на 1400. Иначе, то есть, если детей больше двух, вычет равен 2800 (за первых двух детей) плюс 3000 умножить на количество детей – 2 (за третьего и последующих детей).
Переходим на лист «Табель»
В соответствии с заданием, данные для поля ФИО и должность берем из таблицы «Общ», для этого в ячейку B11 вводим формулу как показано на рисунке.

Эта формула означает, что ФИО берется с листа «Общ» из ячейки, смещенной относительно ячейки A2 на значение номера по порядку минус 1. Формулу растягиваем до конца таблицы.

В ячейку С11 вводим формулу как показано на рисунке.

С помощью функции ВПР можно выполнить поиск в первом столбце диапазона ячеек и получить значение из любой ячейки в той же строке диапазона. В данном случае мы выполняем на листе «Общ» поиск должности по ФИО сотрудника.
Растягиваем формулу до конца таблицы.
Теперь заполняем данные по явкам-неявкам в табеле.

При заполнении используются условные обозначения: Я – явка, Б – больничный, К – командировка, От – отпуск, П – проглул (неявка). Данные берутся произвольные.
Теперь рассчитаем результат в группе ячеек «Отработано за». В ячейку T11 вводим формулу, как показано на рисунке

Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию, в данном случае мы считаем количество отработанных дней — явок, то есть сколько ячеек в данной строке содержит значение «Я».
В ячейке T12 с помощью функции СУММ находим сумму диапазона D12:S12 – сумма отработанных часов за половину месяца.

Выделяем ячейки T11 и T12, растягиваем формулы до конца таблицы – получаем сумму отработанных дней и часов для каждого сотрудника за первую и вторую половину месяца.

Чтобы рассчитать отработанные дни и часы за месяц, в ячейки U11 и U13 вводим формулы:

То есть, суммируем отработанные часы и дни за первую и вторую половину месяца.
Выделяем ячейки U11 и U13, растягиваем формулы до конца таблицы – получаем сумму отработанных дней и часов для каждого сотрудника за месяц.
Рассчитаем результат в группе ячеек «Неявки по причинам». В ячейку V11 вводим формулу, как показано на рисунке

В данном случае мы считаем количество дней на больничном, оно определяется как сумма ячеек в 11-й строке, которые содержат значение «Б» и ячеек в 13-й строке, которые содержат значение «Б». Аналогичным образом находим количество дней командировок, отпусков, прогулов, только ищем значения «К», «От», «П» соответственно.
Выделяем ячейки V11, W11, X11, Y11 растягиваем формулы до конца таблицы – получаем количество дней больничных, командировок, отпусков и прогулов для каждого сотрудника за месяц.

Лист «Табель» заполнили. Переходим к листу «Расчетная».
ФИО берем с листа «Общ» — вводим в ячейку B10 формулу

и растягиваем до конца таблицы.
Должность, как и на листе «Табель» определяем с помощью функции ВПР.
Также с помощью функции ВПР заполняем колонку «Оклад».

Чтобы заполнить столбцы «отработано дней» и «отработано часов» в ячейки E10 и G10 вводим формулы как показано на рисунках.

Эта формулы означают, что отработанные дни и часы будут браться с листа «Табель» из ячейки, смещенной относительно ячейки U11 (для отработанных часов U13) на значение номера по порядку минус 1, умноженного на 4. Формулу растягиваем до конца таблицы. Получаем заполненные данные.

Рассчитаем оплату по окладу, она равна окладу, умноженному на отношение отработанных часов за месяц к норме часов.

Районный коэффициент везде поставим 1.
Чтобы найти сумму командировочных, с помощью функции СМЕЩ находим количество дней в командировке на листе «Табель», это количество умножаем на средний дневной заработок – оклад, деленный на 29,4.

Аналогично находим отпускные

Расчет больничных выполняется похоже, но добавляется умножение на коэффициент 0,6. При расчете больничных за счет работодателя берутся первые 3 дня – используется функция МИН, берется минимум из трех и количества дней больничного.

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

Другие доходы в виде различных социальных и материальных благ – ставим везде 0.
Всего начислено вычисляем как сумму начислений по всем видам расчетов.

Налог на доходы рассчитываем как оклад минус вычет, эту разность умножить на 0,13. Вычет выводим из таблицы «Общ».

Алименты – везде ставим 0.
Всего удержания – сумма налога на доходы и алиментов.

Задолженности за организацией и работником заполняем произвольно.

Сумму к выплате рассчитываем как Всего начислено – Всего удержано + Задолженность за организацией – Задолженность за работником

Получаем заполненную таблицу «Расчетная»

Переходим на лист «Налоги».
Список сотрудников берем с листа «Общ», начисленную сумму и вычеты ищем с помощью функции ВПР на листах «Табель» и «Общ» соответственно.
НДФЛ рассчитываем как разность начисленной суммы и вычета, умноженная на 0,13.

Остаток к выплате рассчитывается как начисленная сумма минус НДФЛ, аванс и алименты.

Рассчитываем взносы в ПФР (страховую часть) по формуле.

Смысл формулы следующий: с помощью функции ВПР на листе «Общ» ищем дату рождения сотрудника, с помощью функции ГОД получаем из даты год рождения, если год меньше 1967, то страховая часть составляет 22% от начисленной суммы, иначе составляет 16%.
Рассчитываем взносы в ПФР (накопительную часть) по формуле.

Смысл формулы следующий: с помощью функции ВПР на листе «Общ» ищем дату рождения сотрудника, с помощью функции ГОД получаем из даты год рождения, если год меньше 1967, то страховая часть составляет 0, иначе составляет 6% от начисленной суммы.
Для расчета значений в колонках «ОМС», «ФСС», «ФСС от НС» начисленную сумму умножаем соответственно на 0,059 , 0,029 , 0,002.
С помощью функции СУММ находим итоги по всем числовым колонкам таблицы, лист «Налоги» рассчитан.


+7 (812) 389-23-13

Работаем: Пн-Пт, с 10 до 17

+7 (499) 649-65-17

Работаем: Пн-Пт, с 10 до 17