Компенсация за неиспользованный ежегодный оплачиваемый отпуск выплачивается работнику при увольнении (ст․ 127 ТК РФ) или в случаях, предусмотренных законом (например, при замене части отпуска деньгами по письменному заявлению)․ Автоматизация этого расчета в Excel позволяет избежать ошибок, ускорить работу кадрового отдела и бухгалтерии, а также обеспечить прозрачность начислений для сотрудника․

Нормативная база и общая формула

Согласно ст․ 127 ТК РФ и Постановлению Правительства РФ от № 922, компенсация рассчитывается по формуле:

Компенсация = Средний дневной заработок × Количество неиспользованных дней отпуска

Оба компонента требуют тщательного расчета, который удобно структурировать на отдельных листах книги Excel․

Структура книги Excel

Рекомендуется создать книгу со следующими листами:

• «Вводные данные» — ФИО, табельный номер, дата приема, дата увольнения, оклад/ставка, режим работы․
• «Начисления за расчетный период», реестр всех выплат за 12 месяцев до увольнения․
• «Исключаемые периоды» — больничные, отпуска, прогулы, простои не по вине работника․
• «Расчет среднего» — формула среднего дневного заработка․
• «Дни отпуска» — расчет количества неиспользованных дней․

Расчет среднего дневного заработка (Лист «Расчет среднего»)

Определение расчетного периода

Расчетный период — 12 календарных месяцев, предшествующих месяцу увольнения․ Если сотрудник работал меньше года, берется фактическое время работы․

Пример формул для определения границ периода (ячейки A1 — Дата увольнения):

• Начало периода: =ДАТА(ГОД(A1); МЕСЯЦ(A1)-12; 1)
• Конец периода: =ДАТА(ГОД(A1); МЕСЯЦ(A1); 0) (последний день предыдущего месяца)

Сумма заработка, включенного в расчет (п․ 5 Постановления № 922)

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

Исключаются: разовые премии (юбилейные, за изобретения), материальная помощь, командировочные, страховые выплаты, оплата обучения․

Реализация в Excel (СУММЕСЛИМН / SUMIFS):

Предположим, на листе «Начисления»:

• Столбец A: Дата начисления
• Столбец B: Код вида начисления (1-оклад, 2-премия кв․, 3-матпомощь․․․)
• Столбец C: Сумма

На листе «Расчет среднего» создайте справочник «Коды для расчета среднего» (например, диапазон Справочник!A:B, где А, код, Б, 1 (включать) или 0 (исключать))․

Формула суммы заработка:
=СУММЕСЛИМН(Начисления!C:C; Начисления!A:A; ">="&НачалоПериода; Начисления!A:A; "<="&КонецПериода; Начисления!B:B; "ВключитьВРасчет")

Где "ВключитьВРасчет" — это ссылка на критерий (например, 1) или использование ВПР/СЧЁТЕСЛИ для проверки кода против справочника․

Количество отработанных дней в расчетном периоде (п․ 15 Постановления № 922)

Это самая сложная часть, так как нужно вычесть дни отсутствия, не считающиеся в стаж для расчета среднего (больничные, отпуска, прогулы, простои не по вине РН)․

Алгоритм для каждого месяца расчетного периода:

1. Определить календарные дни в месяце: =ДЕНЬ(ДАТА(Год; Месяц+1; 0))
2. Найти количество исключаемых дней в этом месяце (из листа «Исключаемые периоды» с помощью СУММЕСЛИМН по пересечению диапазонов дат)․
3. Если месяц полностью отработан (исключаемых дней = 0): прибавляем календарные дни месяца․
4. Если месяц не полностью отработан: считаем по формуле:
   (Календарные дни месяца ‒ Исключаемые дни) / Календарные дни месяца × 29,3

Пример формулы для одного месяца (строка 2):

=ЕСЛИ(ИсклДни2=0; КалендДни2; (КалендДни2 ‒ ИсклДни2) / КалендДни2 * 29,3)

Формула среднего дневного заработка

Расчет количества неиспользованных дней отпуска (Лист «Дни отпуска»)

Общие правила (ст․ 121, 127 ТК РФ)

• Минимальная продолжительность, 28 календарных дней․
• За каждый полный месяц работы полагается 28 / 12 = 2,33 дня․
• Неполный месяц округляется: 15 и более дней — в целый месяц, менее 15 — не считается (Письмо Минтруда РФ от № 14-6/ООГ-2535)․
• В стаж зачета включаются: фактическая работа, отпуска, больничные, периоды вынужденного прогула (если признан незаконным), отпуск по уходу за ребенком (до 1,5 лет — в стаж зачета не включается для продления отпуска, но для компенсации при увольнении стаж считается иначе — см․ ст․ 121 ТК РФ и Постановление Пленума ВС РФ № 2)․ Важно: Для компенсации при увольнении в стаж включаются периоды работы и периоды, приравненные к работе (больничные, отпуска и т․д․), за исключением отпуска по уходу за ребенком (если только сотрудник не работал в это время неполный день/неделю)․

Реализация в Excel

Входные данные:

• B1: Дата приема на работу
• B2: Дата увольнения
• Таблица «ИсклПериодыСтаж» (Начало, Конец, Тип: «Уход за ребенком», «Прогул» и т․д․)

Шаг 1: Расчет полных месяцев и дней․

=ДАТАРАЗН(ДатаПриема; ДатаУвольнения+1; "m") // Полных месяцев
=ДАТАРАЗН(ДатаПриема; ДатаУвольнения+1; "md") // Дней в неполном месяце

Примечание: Функция ДАТАРАЗН (DATEDIF) скрытая, но рабочая․ Можно использовать комбинацию ГОД/МЕСЯЦ/ДЕНЬ․

Шаг 2: Корректировка на периоды, не входящие в стаж (отпуск по уходу за ребенком > 1․5 лет и пр․)․

Для каждого исключаемого периода вычитаем количество полных месяцев и дней из стажа․ Удобно сделать вспомогательную таблицу, где для каждого исключаемого периода считаем его длительность в месяцах/днях и вычитаем из общего стажа․

Шаг 3: Округление неполного месяца․

=ЕСЛИ(ОстатокДней >= 15; 1; 0) // Добавляем 1 месяц, если дней >= 15

= (ПолныхМесяцев + ОкругленныйМесяц) * 28 / 12

Шаг 5: Вычитание уже предоставленных дней․

На листе «ИспользованныйОтпуск» суммируем дни всех использованных отпусков за текущий рабочий год (год, предшествующий увольнению)․

Страховые взносы (для бухгалтерии)

На сумму компенсации начисляются страховые взносы (30% в ПФР/ФСС/ФОМС в совокупности) за счет работодателя․ В зарплатную ведомость сотрудника они не входят, но нужны для проводок (Дт 20/26/44/91 ‒ Кт 69)․

Пример оформления «Справки-расчета» для сотрудника

Рекомендуется на отдельном листе («Печать») сформировать отчет для подписи работника, используя функцию ТЕКСТ для форматирования дат и сумм прописью (через надстройку или VBA-функцию СуммаПрописью)․

ООО «Ромашка»
СПРАВКА-РАСЧЕТ
о компенсации за неиспользованный ежегодный оплачиваемый отпуск

Работник: Иванов Иван Иванович (Таб․ № 123)
Дата приема: 
Дата увольнения: 
Расчетный период: – 

Заработок за расчетный период: 1 200 000,00 руб․
Отработанные дни за расчетный период: 351,60 дн․
Средний дневной заработок: 3 412,97 руб․ (1 200 000 / 351,60)
Стаж зачета за текущий рабочий год: 6 мес․ 15 дн․ -> 7 мес․
Дней отпуска за текущий год: 16,33 дн․ (7 * 28 / 12)
Использовано дней отпуска ранее: 14 дн․
Неиспользованных дней: 2,33 дн․
Сумма компенсации: 7 952,22 руб․ (3 412,97 * 2,33)
Удержан НДФЛ (13%): 1 033,79 руб․
К выдаче на руки: 6 918,43 руб․

Главный бухгалтер: __________ /Петров П․П․/
Работник ознакомлен: __________ /Иванов И․И․/ «___» ________ 202_ г․

Типичные ошибки и советы по проверке (Чек-лист)

• Неверный расчетный период: Проверьте, что берется 12 месяцев, предшествующих месяцу увольнения, а не 12 месяцев от даты увольнения․
• Пропуск премии: Квартальные/годовые премии включаются пропорционально отработанному времени в периоде, за который начислялись (п․ 16 Постановления № 922)․ В Excel это требует отдельного расчета: СуммаПремии / КалендарныеДниПериодаПремии * ОтработанныеДниВПериодеПремии․
• Округление среднего: Средний дневной заработок не округляется до копеек промежуточно․ Округляется только итоговая сумма компенсации․
• Исключение дней из стажа: Дни отпуска по уходу за ребенком (после 1,5 лет) исключаются из стажа для расчета компенсации․ Дни больничных — включаются․
• Региональные коэффициенты: Северные надбавки (РК) включаются в заработок для расчета среднего, но НДФЛ удерживается с полной суммы (включая РК)․
• Защита формул: Защитите листы с формулами паролем, оставив открытыми только ячейки ввода данных․

Автоматизация через Power Query (для продвинутых)

Если данные выгружаются из 1С или другой СУБД (CSV, SQL), вместо ручного заполнения листов «Начисления» и «Исключаемые периоды» используйте Power Query (Данные → Получить данные):

1. Подключитесь к источнику․
2. Отфильтруйте нужные виды начислений/отсутствий․
3. Сгруппируйте по сотрудникам и месяцам․
4. Загрузите в модель данных Excel (Power Pivot) или на лист для финальных формул․

Это исключит ошибки ручного ввода и позволит пересчитывать компенсации для сотен сотрудников за секунды․

Грамотно настроенный Excel-файл для расчета компенсации за неиспользованный отпуск — это не просто калькулятор, а инструмент контроля соблюдения трудового законодательства․ Ключевые моменты: правильное определение заработка по Постановлению № 922, точное учетом исключаемых периодов при расчете среднего и стажа, а также корректное округление только на финальном этапе․ Регулярно сверяйте формулы с актуальными разъяснениями Роструда и Минтруда․

Данная статья носит информационный характер․ Для расчета конкретных сумм обратитесь к бухгалтерии вашей организации или к профессиональному консультанту по трудовому праву․ Нормативная база: ТК РФ, Постановление Правительства РФ № 922, Постановление Пленума ВС РФ № 2․