Оформление командировки в Excel

Оформление командировки ExcelОформление командировки в Excel.

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

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

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

Порядок действий по процессу «Оформление командировки»

Я начал с изучения порядка выполнения действий по оформлению командировки. Вот что, примерно, у меня получилось:

  • Руководитель должен обозначить цель командировки, продолжительность, определить командируемого.
  • Все это перечисляется в служебной записке, которая, затем, согласовывается с директором.
  • Согласованная с директором служебная записка передается в отдел кадров.
  • Кадровик проверяет, уточняет и переписывает все данные в служебное задание (форма Т-10А).
  • Затем, те же данные заносит в командировочное удостоверение (форма Т-10).
    Актуальная поправка — с 2015 года правительство отменило командировочные удостоверения. Теперь, для отчетности, достаточно будет предъявить проездные документы. Если сотрудник использует личный автотранспорт, то для отчетности принимается служебная записка, подкрепленная документами о расходах (квитанция, или кассовый чек).
  • Далее, кадровик оформляет приказ на командировку (форма Т-9). Большинство данных в приказе снова дублируются.
  • Все документы распечатываются (если заполнялись на компьютере) и подписываются директором.
  • На основании документов и внутренних положений бухгалтер производит расчеты и выплачивает денежные средства на командировку.
  • Секретарь в командировочном удостоверении делает отметки об убытии и прибытии сотрудника, и регистрирует приказ на командировку.
  • После приезда, сотрудник отчитывается по командировке и заполняет авансовый отчет.
  • Документы согласуются с руководителем, и передаются в бухгалтерию.
  • Бухгалтер производит окончательные расчеты.

Сразу же бросается в глаза совершенно тупое переписывание кадровиком трех идентичных документов — служебного задания, командировочного удостоверения и приказа на командировку. Хорошо, что в моей фирме это делал один человек, а если это делают три разных подразделения без ведома, как это обычно бывает, друг друга?

И нужна ли служебная записка, которую руководители нередко затруднялись составлять, если можно сразу же составить служебное задание? Ведь вся информация, которая должна быть в служебном задании, начальнику должна быть уже известна (ну, если этот начальник хоть как-то соответствует своей должности).

Создание новых форм

Я предложил, чтобы руководитель сразу оформлял служебное задание в Excel. Ну, а теперь нужно сделать так, чтобы во всех других документах занесенная ранее информация отобразилась автоматически. Это сделать проще простого, создав необходимые формы (T-9, Т-10 и Т-10а) в том же файле на разных страницах, а в необходимых местах создав ссылки на ячейки с требуемой информацией.

К слову говоря, все необходимые формы документов в формате Excel есть в интернете, и их можно скачать.
Ячейка с примечанием

Ячейка с примечанием

Примечание, появляющееся при наведении курсора

Примечание, появляющееся при наведении курсора

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

Для того, чтобы сделать ссылку, необходимо внести знак «=«, а потом открыть нужную страницу, и кликнуть мышкой по той ячейке, ссылка на которую нужна. Можно, конечно же, вручную внести адрес ячейки, например «=А23».

Правда, если в ячейке, на которую ведет ссылка, отсутствуют значения, то отобразится «0». Нам это не нужно, поэтому с помощью функций «ЕСЛИ» и «ЕПУСТО» зададим условие, которое, если отсутствует запись (ячейка пустая), тоже будет оставлять ячейку пустой. Формула выглядит так: =ЕСЛИ(ЕПУСТО(А23);»»;А23)

Две кавычки в формуле как раз и обозначают отсутствие записи.

Теперь, чтобы заполняющие формы сотрудники не повредили саму форму и вносили данные только туда, куда можно, необходимо защитить листы, отменив предварительно защиту с тех ячеек, где вносить и исправлять значения можно.  Для этого ставим выделяем ячейку, которую, как мы предполагаем, можно будет изменять, нажимаем правую кнопку мыши (вызываем контекстное меню), и выбираем пункт «Формат ячеек». Выбираем закладку «Защита» и отжимаем галочку с пункта «Защищаемая ячейка«.

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

Все, форма готова. Теперь процесс будет намного проще, а именно:

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

Время на оформление документов сокращается минимум раза в два!

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

Учет рабочего времени в Excel. I

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть I.

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

При этом, сам учет рабочего времени своих сотрудников обязаны вести руководители.

И, зачастую, время тратится на совершенно бесполезное переписывание одной и той же информации.

Было бы намного проще, если бы данные о рабочем времени сотрудников заносились единожды, а вся переработка этой информации и формирование утвержденных законодательством форм учета происходили автоматически. И это вполне возможно с использованием MS Excel. В серии статей я подробно опишу порядок создания такой таблицы, а в самой последней статье выложу файл, который у нас получится. Тем, кто ищет информацию о создании графика сменности, прошу ознакомиться со статьей «График смен«, в котором выложен соответствующий готовый файл.

Подготовка структуры таблицы

Табель учета рабочего времени Т-12

Для начала, скачаем с интернета форму табеля рабочего времени в Excel. Кому-то может понравится форма табеля Т-13, но я использую форму Т-12. Можно использовать любую — жестких требований здесь нет.  Найти ее несложно, например, вот здесь. Вообще, кадровикам я рекомендую использовать справочные системы типа «Гарант» или «Консультант» в своей работе. Все, что необходимо там имеется.

В файле, который мы скачали, несколько листов. На первом указаны коды рабочего времени. Назовем этот лист «Коды«. На втором — собственно, табель. Так и назовем его — «Табель«. Третий лист посвящен оплате труда. Назовем его «Расчет» (он нам пригодится на следующих уроках). Четвертый лист назовем «Итоги«. Здесь будут подсчитываться итоговые значения по отработанному времени.

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

Создание таблицы для ввода данных

Для ввода данных об отработанном времени добавим лист, и назовем его, например, «учет». Переместим его в самое начало нашей книги (навести курсор мыши к ярлычку листа, зажать кнопку мыши и, не отпуская ее, перенести ярлычок в самое начало). Вот такой порядок у нас должен получиться (см. картинку).

Теперь подумаем, как руководителю, который будет вести ежедневный учет рабочего времени своих сотрудников, было бы удобнее вносить данные по своим работникам. Необходимо предусмотреть:

  • время начала работы;
  • время окончания работы;
  • продолжительность обеденного перерыва.

При желании, можно предусмотреть различные варианты ввода данных. Например, руководитель может выбирать ФИО из выпадающего списка, и заносить время начала и окончания работы этого сотрудника. А при выборе другого сотрудника, будут отображаться только поля для ввода данных о времени только этого сотрудника. Но такую таблицу будет сложнее создать, а задача этой статьи прописать алгоритм, который будет понятен большинству.

Набросок таблицы ввода данных об отработанном времени

Поэтому ограничимся относительно простым вариантом ввода данных в таблицу. Предусмотрим поле для ввода наименования подразделения. Пусть, в нашем случае, это будет «Магазин №1«.  Ниже предусмотрим место для названия месяца. Далее — начинаем создавать таблицу. В ней будут следующие поля:

  • Порядковый номер;
  • ФИО сотрудника;
  • Наименование должности сотрудника;
  • Табельный номер сотрудника;
  • Далее будут располагаться данные о времени. Предусмотрим время начала рабочего дня, время окончания, продолжительность обеденного перерыва и колонку для внесения кода рабочего времени.

Таблица ввода данных об отработанном времени

Последние четыре колонки относятся к первому числу месяца. Нам необходимо будет повторить их 31 раз (по максимальному количеству дней в месяце). Однако, вначале, выше наименования колонок нашей таблицы поставим цифру 1. Теперь выделим четыре колонки, которые необходимо продублировать 31 раз, подведем курсор мыши к черному квадратику справа вверху и протянем диапазон до того момента, пока блоков из четырех колонок не будет 31. Должно получится как на картинке внизу.

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

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

Учет рабочего времени в Excel. II

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть II.

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

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

Иначе постоянно придется отвечать на вопросы и принимать нарекания о том, что таблица не работает (хотя не работает исключительно из-за неправильного ее заполнения).

О возможностях Excel в этом вопросе я писал в соответствующей статье.

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

Вот что нам необходимо обеспечить:

  • в поле «Месяц» должен допускаться только ввод одного из 12 наименований месяца;
  • во всех колонках «Начало» и «Окончание» должен до пускаться только ввод в формате времени от 0 часов до 24 часов;
  • Время окончания не должно быть раньше времени начала;
  • во всех колонках «Обед» должен допускаться только ввод в формате времени от 0 часов до 1 часа;
  • во всех колонках «Код» допускается ввод только одного из кодов рабочего времени.

Защита от ошибочного введения месяца

Для безошибочного ввода названия месяца необходимо создать список. Нам придется создавать еще списки, поэтому предлагаю для них создать отдельный лист, который мы так и назовем — «Списки«. В дальнейшем мы его попросту скроем.

На этом листе перечислим все месяцы. Выделяем список и задаем ему имя — «месяц«. О том, как присваивать имя диапазону и работать со списками, я писал здесь.

Возвращаемся на лист «Учет«, и подводим курсор мыши к ячейке, в которой хотим использовать список. Через меню «Проверка данных» задаем тип данных «Список» и источник данных — диапазон с именем «Месяц«.

Ограничение на ввод данных о времени

Выделим ячейки колонок «Начало» и «Окончание«, относящихся к 1-му числу месяца. Также, через «Проверку данных» задаем тип данных «Время«, и выбираем значения между 0:00 и 23:59. В закладке «Сообщение об ошибке» оставляем вид «Останов«, а в заголовке пишем «Неверный формат данных«. В тексте сообщения пишем примерно следующее — «Введите правильное время от 0:00 до 23:59«.

Похожим образом защитим ячейки в колонке «Обед«, только диапазон значений выбираем между 0:00 и 1:00. В тексте сообщения об ошибке я написал — «Введите правильную продолжительность от 0:00 до 1:00«.

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

Для этого выделяем ячейку в колонке «Начало«, и в меню «Условное форматирование» выбираем пункт «Создать правило«. Затем выбираем пункт «Использовать формулу для определения форматированных ячеек«. Устанавливаем курсор в строке формулы и вписываем условие =E6>F6.

Обратите внимание — если вы не вписываете адреса ячеек с клавиатуры, а указывает на эти ячейки мышкой, то Excel автоматически сделает эти адреса абсолютными (со знаками «$»). Необходимо сделать эти адреса относительными (удалить все знаки «$»). В противном случае при копировании этого условия на другие ячейки, ссылки не будут перемещаться.

Затем устанавливаем курсор в ячейку «Окончание«, и создаем второе правило с аналогичной формулой. Выделим обе ячейки, и протянем условие на всю колонку. Проверяем — работает ли условие.

Защита от ошибочного ввода кода

Для защиты полей в колонке «Код» нужно создать список этих кодов. Хоть он уже имеется на странице «Коды«, но располагается в двух колонках и не в каждой строчке. Поэтому копируем обе эти колонки на страницу список в одну колонку и в виде значений. Но буквы будут расположены не в каждой строчке. Для удаления пустых ячеек выделяем всю колонку, и в меню «Найти и выделить» выбираем пункт «Выделение группы ячеек«.

В появившемся окошке ставим галочку в пункте «Пустые ячейки«, и нажимаем «ОК«. Теперь подводим курсор мыши к любой выделенной ячейке, и нажимаем правую кнопку мыши. В открывшемся контекстном меню выбираем пункт «Удалить«, а затем ставим галочку на пункте «Ячейки со сдвигом вверх«. После того, как мы нажмем «ОК«, список приобретет требуемый вид. Выделяем его, и задаем имя «Код«.

Возвращаемся к листу «Учет«. Выделяем колонку «Код» и через меню «Проверка данных» задаем тип данных «Список» и источник данных — диапазон с именем «Код«. Галочку с пункта «Список допустимых значений» лучше всего отжать. Кнопка раскрытия списка только будет мешать, а значения со слишком большого списка лучше вносить вручную, а не выбирать. В сообщении об ошибке я написал «Вы ввели неверный код. Ознакомьтесь со списком и их значениями на страницы «Коды».

Завершение защиты листа

Теперь все ячейки, относящиеся к первому числу месяца, защищены. Для защиты остальных ячеек воспользуемся протягиванием блока. Выделяем этот блок (все ячейки, относящиеся к первому числу), подводим курсор мыли к маленькому черному квадратику в правом нижнем углу выделенного диапазона, и протягиваем до конца.

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

  • Поле с наименованием месяца;
  • Колонка «ФИО сотрудника»;
  • Колонка «Должность»;
  • Колонка «Табельный номер»;
  • Все колонки с данными о времени.

Подведя курсор мыши к любой из выделенных ячеек, нажимаем правую кнопку и выбираем в контекстном меню пункт «Формат ячеек«. Заходим на вкладку «Защита» и отжимаем галочку в пункте «Защищаемая ячейка«.

Теперь, на вкладке меню «Рецензирование» лист можно защитить. Подробнее об этом в статье «Защита от ошибочного ввода данных«. Пароль на снятие защите в своем файле я не задаю, но при использовании таблицы другими сотрудниками настоятельно рекомендую это сделать.

Для удобства, предлагаю закрепить колонку с данными о сотрудниках и строки с наименованием колонок. Для этого устанавливаем табличный курсор на ячейку с адресом E6, и открываем закладку меню «Вид«. Находим пункт «Закрепить области» и нажимаем на него-же в выпавшем списке. Теперь, при прокрутке таблицы вниз или вправо, наименования колонок и данные о сотрудниках не будут перемещаться.

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

Учет рабочего времени в Excel. III

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть III.

В прошлых материалах (часть I и часть II) мы с вами скачали официальную форму табеля учета рабочего времени, и создали таблицу для ввода данных об отработанном времени сотрудников.

Теперь необходимо сделать так, чтобы все эти данные преобразовывались в необходимые нам значения в табеле учета рабочего времени.

Автозаполнение данных о сотрудниках

Для начала, обеспечим дублирование данных по сотрудникам. Зачем нам вводить по нескольку раз имена и должности. Попросту создадим прямые ссылки на те ячейки, где данные уже внесены. Для этого устанавливаем табличный курсор на листе «Табель» в ячейку, которая должна ссылаться на имя сотрудника в листе «Учет«, пишем в ней знак «=«, открываем лист, на который ссылаемся и щелкаем мышкой там, где находятся данные.

Однако, обратим внимание, нам необходимо указать не только имя сотрудника, но и его должность. В этом случае можно просто объединит данные с двух ячеек через оператор «&«. После того, как мы указали ссылку на данные с ФИО, щелкаем мышкой в строке формулы, чтобы здесь появился мигающий курсор, и вносим необходимые операторы. Затем ссылаемся на вторую ячейку. В конечном итоге должна получиться вот такая формула: =Учет!B6&» «&Учет!C6

Как видите, мы не просто объединили данные, а еще добавили кавычки с запятой и пробелом между ними. Если бы мы этого не сделали, то данные бы объединились без пробела и запятой.

К сожалению. нам не удастся заполнить остальные ячейки табеля с помощью автопротягивания. Поскольку в исходной таблице данные внесены в каждой строке, а в табеле через строку (ячейки двух строк объединены), то Excel формирует новые ссылки через строку. Поэтому придется каждую формулу вносить заново.

Должно получиться следующее (см. скриншот)

Расчет продолжительности работ и автозаполнение кодов

Теперь необходимо вычислить ежедневную продолжительность работы. Формула здесь проста — из времени окончания вычитаем время начала и из полученной разницы вычитаем продолжительность обеденного перерыва. Однако такая формула не сработает при наличии ночных смен. В этом случае необходимо задавать условия, но здесь мы этого делать не будем, чтобы не усложнять.

Внесем формулу в ячейку, соответствующую времени, отработанного Ивановым первого числа месяца: =Учет!F6-Учет!E6-Учет!G6

Теперь мы можем протянуть эту формулу до ячейки, соответствующей пятнадцатому числу. Все ссылки переместятся правильно, поскольку количество колонок, объединяющих одну ячейку в табеле совпадает с количеством колонок, соответствующих одному дню на листе «Учет«. Приятное совпадение.

Аналогичным образом вносим формулу в ячейку, соответствующую шестнадцатому числу (=Учет!BN6-Учет!BM6-Учет!BO6), и протягиваем ее до 31-го числа. Во всех этих ячейках необходимо установить формат времени с указанием часов и минут.

Теперь, если мы начнем проверять расчеты, то обнаружим неприятный момент — значения не вмещаются в границы ячейки, поэтому отображаются одни решетки. Выйти из положения можно несколькими путями:

  • уменьшить размер шрифта до 5 пунктов;
  • увеличив ширину ячеек;
  • задать формат с указанием только часов (при выборе формата ячейки, в поле «Тип» вносим одну букву «ч» см. картинку).

Я решил уменьшить размер шрифта.

Заполняем все ячейки с указанием отработанных часов табеля.

С указанием кода рабочего времени проблем быть не должно — просто ссылаемся на нужную ячейку. Однако, нам не нужны нули в случаях, когда ячейка с кодам на листе «Учет» пуста. Поэтому закладываем следующее условие: =ЕСЛИ(ЕПУСТО(Учет!H6);»»;Учет!H6).

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

Учет рабочего времени в Excel. IV

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть IV.

В прошлой статье мы настроили автозаполнение данных о сотрудниках, и данных об отработанном времени в табеле.

Сейчас нам необходимо внести формулы, подсчитывающие итоговые значения за месяц.

Все формулы, которые мы будем использовать, рассматривались в статье «Суммирование по условию«.

Подсчет отработанных дней и часов

В полях «Итого отработано…» просто заносим формулу суммирования значений отработанного времени. Обратите внимание, что формат времени не всегда считает исходя из общего количества отработанных часов. Часто, по умолчанию, устанавливается формат, исходящий из 24 часов. Для исправления ошибки выделите все ячейки с суммой часов, и замените формат на 37:30:55. Он считает не теряя часы за пределами 24.

А вот для подсчета количества отработанных дней нужно будет использовать функцию подсчета количества ячеек по условию (подробнее о ней здесь). Пишем: =СЧЕТЕСЛИ(AG9:CN9;»>0″)+СЧЕТЕСЛИ(CV9:FG9;»>0″). Как вы понимаете, поскольку диапазоны у нас разорваны, то мы складываем два диапазона по отдельности.

Копируем формулу во все остальные ячейки. При копировании ссылки будут смещаться правильно. Если отобразится временной формат, то, попросту, замените его на числовой (с помощью контекстного меню «Формат ячеек«), без указания десятичных дробей.

Подсчет отработанных часов за месяц обеспечиваем простейшей формулой складывания промежуточных формул, которые мы писали ранее в полях «Итого отработано…«.

Подсчет прочих данных об отработанном времени

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

По этой же причине я игнорирую пункт подсчета количества ночных часов. Раз уж я изначально предложил не рассматривать подобные случаи, то и сейчас начинать это делать бессмысленно. Тем не менее, при возникновении интереса, можно прописать алгоритмы учета работы и в ночное время.

С подсчетом количества часов, отработанных в праздничные дни, все — просто. Используем функцию «СУММЕСЛИ«: =СУММЕСЛИ(AG10:CN10;»=РВ»;AG9:CN9)+СУММЕСЛИ(CV10:FG10;»=РВ»;CV9:FG9)

Смотрим — что получилось. Укажем в один из дней код «РВ«. Получилась десятичная дробь. Проблема в том, что так настроился формат ячеек. Просто выделяем все ячейки, формат которых должен быть временной, и устанавливаем, собственно, тот, что указывает часы и минуты. Копируем формулу на остальные ячейки.

Количество неявок предлагаю указывать в днях, поскольку логика этого табеля предполагает именно такой подход. Неявка на работу отмечается кодами «ПР» и «НН«. Используем функцию «СЧЕТЕСЛИ«.  Поскольку у нас два условия, то мы их складываем. А, поскольку, два диапазона, до складываем еще два условия и по второму диапазону. Таким образом получается формула: =СЧЁТЕСЛИ(AG10:CN10;»=НН»)+СЧЁТЕСЛИ(AG10:CN10;»=ПР»)+СЧЁТЕСЛИ(CV10:FG10;»=НН»)+СЧЁТЕСЛИ(CV10:FG10;»=ПР»)

Копируем эту формулу на другие ячейки.

В колонке «Из них по причинам…» предусмотрено по две ячейки. Заносим в каждую из них коды «ПР» и «НН«, соответственно. А в следующих ячейках, также, с помощью функции «СЧЕТЕСЛИ» подсчитываем количество ячеек, отмеченных этими кодами. Копируем формулы на все прочие ячейки.

Аналогично заносим формулу подсчета количества ячеек с кодами «В» в колонке «Количество выходных и праздничных дней»: =СЧЁТЕСЛИ(AG10:CN10;»=В»)+СЧЁТЕСЛИ(CV10:FG10;»=В»). Должно получиться как на картинке.

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

Заполнение формы Т-12 в Excel. I часть

Заполнение формы Т-12 ExcelЗаполнение формы Т-12 в Excel. Часть I.

В серии статей «Учет рабочего времени в Excel» мы настраивали автозаполнение табеля учета рабочего времени в установленной отчетной форме Т-12.

Правда 3-я и 4-я страницы этой формы остались без внимания. А ведь это страницы расчета с персоналом по оплате труда и статистических итогов по отработанному времени сотрудников. Когда таблица создавалась, мы дали этим страницам имена «Расчеты» и «Итоги«.

В этой серии статей мы продолжим настройку формы таким образом, чтобы ее заполнение занимало минимум рабочего времени а, в идеале, полностью заполнялось автоматически. К слову, напомню, что раздел «Расчет с персоналом по оплате труда» можно не вести, если в организации временной учет и расчеты с персоналом по оплате труда ведутся отдельно.

Внесение первичных данных

Начнем с самого простого — сделаем ссылки на ячейки, которые уже должны быть заполнены на предыдущих страницах. Это колонка «Табельный номер» и графа 22 — «Часы (дни)«. Табельный номер мы заносили на странице «Учет«. Ставим знак «=» и кликаем мышкой на соответствующей ячейке. И так по всему списку.

Расчет отработанных в течение месяца часов осуществляется на странице «Табель«. Это ячейки в колонке «Всего«, являющейся подразделом колонки «Итого отработано за месяц«. Также, как и табельный номер, вносим ссылки на эту колонку.

Далее, в графе 19, пишем установленный для каждого сотрудника оклад (о более сложных схемах оплаты труда мы будем говорить в другой статье). Графа 20 для бухгалтеров. В нее заносится счет БУ, на который относятся расходы по оплате труда.

Собственно, расчеты, начинаются с графы 21. Это окладная сумма, которая рассчитывается пропорционально отработанному времени (как правило, в часах). И вот здесь начинается очень интересная тема. Для ежемесячного учета мы отталкиваемся от нормативов рабочего времени, установленных трудовым законодательством и указанных в ежегодно утверждаемом правительством производственном календаре.

Поскольку нормативы устанавливаются единожды в год, и не корректируется впоследствии, мы можем установить их там же, где составляли список месяцев, один раз, и не возвращаться к нему в течение всего года. В дальнейшем, при выборе того или иного месяца, таблица будет сама определять — сколько рабочих часов установлено в этом месяце.

Подготовка списка для месячного норматива рабочего времени

Для начала дополним список с месяцами. Откроем страницу «Список«, и добавим колонку справа от наименований месяцев. Назовем эту колонку «Норматив«.

Один важный момент. Поскольку мы будем использовать данные из табеля, внесенные в формате времени, то и здесь норматив часов по месяцам я рекомендую вносить в формате времени. Иначе впоследствии придется очень повозиться, чтобы расчеты были адекватными.

Поэтому выделяем колонку, в которую будем заносить данные, и через правую кнопку мыши вызываем диалоговое окно «Формат ячеек«. Чтобы лишние поля, типа «Минуты» и «Секунды» нам не мешались, я выбираю пункт «Все форматы» и ставлю в поле «Тип» букву «ч» в квадратных скобках (вот так — [ч]), обозначающей отображение только часов.

Найдем в интернете производственный календарь (я использую эту  ссылку) и перепишем с него нормативы по каждому месяцу для 40-часовой рабочей недели. Только записывать нужно будет с минутами через двоеточие, например — 120:00. Иначе программа будет число воспринимать неадекватно.

Во второй части статьи «Учет рабочего времени в Excel» мы давали имя списку месяцев. Сейчас нам необходимо создать еще один поименованный диапазон. Пусть он будет называться «НормативМесяц» (без пробела). Для этого выделяем диапазон с ячейки A2 до ячейки B13 (в него должен попасть список месяцев и нормативы часов в эти месяцы). В адресную ячейку вносим имя «НормативМесяц» (см. картинку выше).

Настройка автоматического расчета окладной части заработной платы

Теперь мы можем рассчитать окладную сумму, рассчитанную пропорционально отработанному времени сотрудника. Формула арифметически проста — отработанное время делим на нормативное время и умножаем на оклад. Но как это реализовать в Excel?

Нормативное время мы можем взять на основании выбранного на странице «Учет» месяца. Для этого используем функцию ВПР, которая будет выводить то количество часов, которое соответствует выбранному месяцу (для этой функции мы и создавали диапазон «НормативМесяц«). Функцию можно сразу ввести в строку формул: ВПР(Учет!C3;НормативМесяц;2;ЛОЖЬ).

Устанавливаем курсор вначале формулы (после знака равно) и вводим частное — адрес ячейки с суммой отработанного времени. Для первой записи это будет ячейка BI11 (я, обычно, не заношу адрес ячейки вручную, а просто кликаю по ней мышкой).

Затем устанавливаем курсор в конце формулы и умножаем все на ячейку с суммой оклада (М11). Конечная формула для первой строки будет выглядеть так: =BI11/ВПР(Учет!$C$3;НормативМесяц;2;ЛОЖЬ)*M11

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

После того, как вы нажмете Enter, в ячейке может отобразиться не число, а время. Все потому, что в формуле ссылки идут на ячейки со временем. Для корректного отображения давайте заменим формат времени на денежный формат. Выделяем всю колонку, и, как обычно, через контекстное меню производим необходимые настройки (см. картинку выше).

Копируем формулу на все прочие ячейки

Графы с 23 по 32 оставляем пустыми. для нашей формы оплаты труда они не используются. С граф 33 и 34 делаем простые ссылки на графы 21 и 22.

Страница «Расчет» заполнена. Страницу итогов начнем настраивать в следующей статье.

Заполнение формы Т-12 в Excel. II часть

Заполнение формы Т-12 ExcelЗаполнение формы Т-12 в Excel. Часть II.

В прошлой статье мы настроили страницу «Расчеты» для расчета окладной части заработной платы.

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

Особенности 4-й страницы формы Т-12

Четвертая страница формы Т-12 имеет несколько иную структуру, нежели предыдущие. Если на прежних страницах данные вводились по каждому сотруднику, то здесь рассчитываются сводные данные по всем сотрудникам на каждый день.  На основании данных этой страницы, впоследствии, можно настроить ежемесячную статистическую отчетность по персоналу организации.

Автозаполнение даты

Хотелось бы, чтобы не приходилось вносить вручную. Чтобы дата формировалась автоматически. Ведь месяц мы уже вносили на странице «Учет«, число там уже стоит а год… ну, давайте внесем год на странице «Списки«, поскольку  итак ежегодно корректируем там ежемесячные нормативы по часам.

Заходим на страницу, и чуть ниже наименований месяцев пишем слово «Год«. Правее от него вносим 2015. Однако, что мы видим — отображается странное числе 48360. Все потому, что на эту ячейку распространился формат времени в часах, который мы настраивали ранее. Выделяем ячейку, и выбираем формат числовой без десятичных знаков.

Теперь нам предстоит непростая задача. Нужно сделать так, чтобы число месяца, наименование месяца и год были объединены в одну ячейку и преобразованы в формат даты. Я для этого использовал функцию ДАТАЗНАЧ, которая преобразует текст в виде 1-янв-2015 в формат даты. Но для этого необходимо сформировать этот текст.

Число внесем в формулу простой цифрой. С помощью оператора «&», который объединяет записи различных ячеек в одной, соединим это число с наименованием месяца через дефис. Чтобы дефис воспринимался не как оператор, а как символ, заключим его в кавычки. Далее, снова через дефис, соединяем с информацией из ячейки с текущим годом.

Формула приобретает следующий вид: =ДАТАЗНАЧ(1&»-«&Учет!C3&»-«&Списки!B15). Не забудем по всей колонке через формат ячеек настроить краткий формат даты.

Внесение дат следующих чисел в таблице упростим простым прибавлением к вышестоящей ячейке единицы. Таким образом каждая следующая ячейка будет отображать следующее число установленного месяца и года. Например в ячейке А7 будет формула =А6+1.

Далее, протянем формулу до нижней ячейки таблицы. Обратите внимание, что в месяцы с количеством дней менее 31, в нижних ячейках будут отображаться числа следующего месяца. Можно, конечно, оставить как есть, но я решил исправить несоответствие формулой в ячейках, соответствующих 29-31 числам: =ЕСЛИ(A33=»»;»»;ЕСЛИ(МЕСЯЦ(A33+1)=МЕСЯЦ(A33);A33+1;»»))

Суть этой логической формулы в двойной проверке.

  • Во-первых, она проверяет, не смениться ли месяц при прибавлении единицы вышестоящей даты. Если да, то отображается пустая ячейка, если нет, то отображается дата.
  • Вторая проверка проверяет, не является ли вышестоящая ячейка пустой, если да, то и эта ячейка остается пустой.

Если вторую проверку не сделать, то в следующей, после пустой, ячейке будет отображаться ошибочное значение (#ЗНАЧ).

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

Расчет человекодней и человекочасов

Подсчитать количество человекодней в тот или иной день месяца несложно, использовав функцию СЧЁТЕСЛИ, которую мы использовали ранее на странице «Табель». Единственная проблема заключается в том, что не удастся воспользоваться автозаполнением, поскольку переход чисел в табеле осуществляется по горизонтали, а на странице «Итоги» — по вертикали.

Можно использовать запись макроса, но это отдельная тема для другой статьи. О написании макросов мы обязательно поговорим позднее. А формула для подсчета человекодней, отработанных первого числа месяца будет такой: =СЧЁТЕСЛИ(Табель!AG9:AJ36;»>0″).

Для ускорения и облегчения работы, можно протянуть формулу до нижней ячейки. Единственное, что будет неправильно во всех нижестоящих формулах, это диапазон суммирования. Ставим табличный курсор в ячейку с ошибочной формулой, выделяем неверный диапазон и заново выделяем уже правильный. Нажимаем Enter, и курсор встанет на новую ошибочную ячейку.

Как вы уже, наверное, догадываетесь, человекочасы будем считать с использованием функции СУММ. Просто суммируем весь диапазон, соответствующего числа месяца. И так по всем строкам. Буквенные обозначения считаться не будут, а числовые войдут в расчет, как и положено. Не забудьте предварительно преобразовать формат ячеек во временной без указания секунд ([ч]:мм;@).

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

Заполнение формы Т-12 в Excel. III часть

Заполнение формы Т-12 ExcelЗаполнение формы Т-12 в Excel. часть III.

Продолжаем наполнение страницы «Итоги» формы Т-12.

У нас остались следующие поля:

  • Количество человеко-дней простоев. Здесь очевидно можно использовать коды, обозначающие простой по разным причинам — РП, НП, ВП, ЗБ.
  • Не отработано человеко-часов в связи с работой в режиме неполного рабочего дня. Используем код НС.
  • Дни неявок на работу:
    • ежегодный основной оплачиваемый отпуск — код ОТ;
    • ежегодный дополнительный оплачиваемый отпуск — код ОД;
    • отпуск в связи с обучением с сохранением заработной платы, повышением квалификации с отрывом от производства — коды У, ПК и ПМ;
    • отпуск по беременности и родам — код Р;
    • по болезни — код Б;
    • прочие неявки, разрешенные законодательством (выполнение государственных обязанностей и т.п.) — боюсь ошибиться, но здесь, вероятно, коды ОЖ, ДО, ОЗ, ДБ, Т, Г.
  • По причинам:
    • с разрешения работодателя — все вышеперечисленные коды;
    • прогулы — код ПР;
    • массовые неявки — забастовки в порядке, предусмотренном законом — код ЗБ;
    • количество человеко-дней выходных и праздничных — используем код В;
    • Всего — складываем пункты этого блока.
  • Всего человеко-дней явок и неявок на работу — тоже используем сложение данных в этой таблице.
  • Количество дополнительных выходных человеко-дней при пятидневной рабочей неделе — коды ОВ и НВ.
  • Списочная численность работников — складываем подсчеты по всем кодам.
  • Численность работников:
    • не учитываемых в списочном составе, но включенных в среднесписочную численность — это для ручного подсчета, поскольку данных по таким сотрудникам в табеле нет.
    • списочного состава, которые не включаются в среднесписочную численность — аналогично предыдущему пункту.

В основном будем использовать функции СЧЁТЕСЛИ и СУММЕСЛИ.  Описывать подробно каждую формулу я не буду, поскольку все подобные функции мы уже использовали не один раз. Просто приведу список этих формул, соответствующих первому числу месяца:

  • Количество человеко-дней простоев — поскольку кодов несколько, то мы складываем несколько значений. Формула в ячейке первого числа месяца будет следующей =СЧЁТЕСЛИ(Табель!AG9:AG36;»РП»)+СЧЁТЕСЛИ(Табель!AG9:AG36;»НП»)+СЧЁТЕСЛИ(Табель!AG9:AG36;»ВП»)+СЧЁТЕСЛИ(Табель!AG$9:AG36;»ЗБ»). Здесь мы снова сталкиваемся с проблемой, что в одной таблице перемещение идет по горизонтали, а в другой — по вертикали. Использовать автозаполнение не удастся.
  • Не отработано человеко-часов в связи с работой в режиме неполного рабочего дня — =СУММЕСЛИ(Табель!$AG$9:$AG$36;»НС»)
  • Дни неявок на работу:
    • ежегодный основной оплачиваемый отпуск — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОТ»);
    • ежегодный дополнительный оплачиваемый отпуск — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Од»);
    • отпуск в связи с обучением с сохранением заработной платы, повышением квалификации с отрывом от производства — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»У»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ПК»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ПМ»);
    • отпуск по беременности и родам — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Р»);
    • по болезни — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Б»);
    • прочие неявки, разрешенные законодательством (выполнение государственных обязанностей и т.п.) — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОЖ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ДО»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОЗ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ДБ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Т»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Г»).
  • По причинам:
    • с разрешения работодателя. Здесь достаточно сложить предыдущие расчеты — =СУММ(AU6:DF6);
    • прогулы — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ПР»);
    • массовые неявки — забастовки в порядке, предусмотренном законом — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ЗБ»);
    • количество человеко-дней выходных и праздничных — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»В»);
    • Всего — =СУММ(DG6:EX6).
  • Всего человеко-дней явок и неявок на работу — =СУММ(I6;EY6).
  • Количество дополнительных выходных человеко-дней при пятидневной рабочей неделе — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОВ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»НВ»).
  • Списочная численность работников — =СУММ(I6;Y6;EY6;FU6).

Таблица настроена на максимально возможное автозаполнение. Теперь формой Т-12 можно пользоваться с минимумом рутинных трудозатрат.

Расчет заработной платы в Excel. I часть

Расчет заработной платы ExcelРасчет заработной платы в Excel. Часть I.

Схем расчета заработной платы в разных организациях разработано великое множество.

И, казалось бы, какой смысл приводить пример всего лишь одной расчетной таблицы зарплаты?

Тем не менее, я полагаю, на примере, который здесь рассматривается, достаточно понять логику, чтобы впоследствии легко настроить расчет по любому алгоритму.

Тем более, что рассматриваем мы отнюдь не простой способ расчета сдельной оплаты труда с фондом, зависящим от суммы выработки предприятия. Этот фонд часто называют «котел«.

В чем суть этого котла?

  1. Ежемесячно подсчитывается сумма, на которую было произведено (или продано, если это торговля) продукции;
  2. Эта сумма соотносится с планом, меняясь в зависимости от процента выполнения плана;
  3. По каждому рабочему (сотруднику) рассчитывается его доля, на которую он может рассчитывать в этом «котле». Эта доля зависит:
    1. От времени, которое сотрудник отработал за месяц;
    2. От его эффективности, которая может определяться по различным параметрам. Например: количество брака, которое допустил работник, количества нареканий к нему со стороны руководства или коллег (покупателей), количества произведенной (проданной) продукции и т.п.
    3. От количества работников, поскольку, чем больше людей в бригаде, тем меньшая сумма приходится на каждого.
  4. Сумма котла умножается на эту долю, определяя, таким образом, сумму, на которую может рассчитывать работник.

Настроить расчет прочих схем расчета заработной платы по показателям просто — вставляешь формулу, и готово. А в случае с котлом, помимо учета показателей, необходимо еще и определять долю каждого по нескольким параметрам.

Структура файла

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

выработка

На производстве оценка доли, которую имеет рабочий, рассчитывается на основании оценки, называемой «Коэффициент Трудового Участия» (КТУ). Ее устанавливает мастер или бригадир ежедневно, на основании того, как сработал этот день рабочий — сколько сделал брака, исполнял ли распоряжения и т.п. Обычно правила выставления КТУ строго регламентируются, чтобы свести к минимуму произвол, возможный со стороны мастеров или бригадиров. Эти правила тоже можно заложить в Excel, но мы не будем усложнять материал. Главное — понять принцип.

Итак, помимо отработанного времени, в ячейках необходимо проставлять еще и КТУ. Можно для этого создать еще одну таблицу, но мастерам это было бы неудобно. Приемлемый вариант, когда руководитель проставляет количество отработанных часов сотрудником, а рядом — его КТУ. Но для этого нужно будет добавить колонок на странице «Бригады», а этого я делать не рекомендую, так как тем самым мы нарушим структуру таблицы, на которую уже понаделали разных ссылок. Рискуем эти ссылки «сломать», что приведет к переделке всего файла.

К тому же в дальнейшем, когда мы будем использовать протягивание формул, оно не будет корректно работать, если данные в таблице будут чередоваться. Поэтому, предлагаю, добавить две страницы: одна — для заполнения мастерами, другая — для значений КТУ, скопированных из первой.

Лист «Бригады» предлагаю переименовать на «Часы«. Лист с КТУ так и назовем — «КТУ«. А лист для ввода назовем «Ввод«.

Страницы для учета часов и КТУ

Поскольку первоначально мы настраивали лист «Бригады» (теперь «Часы«) для ввода, то можно попросту скопировать его содержание на лист «Ввод» со всеми формулами. Следующий шаг — на все заполненные ячейки страницы «Ввод» настраиваем ссылки со всех ячеек страницы «Часы«. Для этого устанавливаем табличный курсор на ячейку А5 листа «Часы«, вносим знак «=«, переходим на лист «Ввод» и кликаем мышкой на аналогичной ячейке. Жмем «Enter«. Затем протягиваем ячейку А1 листа «Часы» на все ячейки в диапазоне А5:AJ28.

Аналогичным образом сделаем ссылки со всех прочих заполненных ячеек листа «Часы«.

Поскольку на странице «Часы» данные вносится не будут, нужно сделать все ячейки здесь защищаемыми. Ну, и отмените заливку, чтобы не сбивать пользователей с толку.

Страницу «КТУ» я предлагаю оформить также, как и страницу «Часы«. То есть копируем все ячейки страницы «Часы» и вставляем их в страницу «КТУ«. Ссылки на количество часов удаляем.

Теперь добавим колонки для ввода значений КТУ на странице ввод и строку для наименования колонок. После всех манипуляций с оформлением, получится вот такая таблица (см. рис.).

таблица ввода

Если вы помните, мы делали ограничение на ввод значений кроме от 1 до 12. На странице «Часы» это ограничение можно оставить, а со страницы КТУ ограничение нужно снять.

Выделите все ячейки для значений КТУ, нажмите на пункт «Проверка данных» вкладки меню «Данные» и выберите пункт «Любое значение«.ексель

Аналогичным образом выделите все ячейки для ввода КТУ на странице «Ввод» (выделять придется с помощью клавиши Ctrl) и смените ограничение.

В организации, где я работал, максимальный КТУ мог равняться двум. Поэтому давайте настроим диапазон возможных значений КТУ от 0 до 2.

Сделаем ссылки на ячейки с КТУ со страницы «КТУ«. На этот раз автозаполнением нам удастся протянуть ссылки только по вертикали. Протягивание по горизонтали не учтет то, что нам нужно пропускать ссылки через колонку. Поэтому придется настраивать ссылки вручную.

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

Расчет заработной платы в Excel. II часть

Расчет заработной платы ExcelРасчет заработной платы в Excel. Часть II.

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

В прошлой статье мы подготовили страницы учета рабочего времени, КТУ и выработки.

Теперь, на основании данных на этих страницах, нужно рассчитать необходимую сумму.

Страница расчета ЗП

После того, как все страницы настроены, остается добавить лист для расчета заработной платы. Так его и назовем — «РасчетЗП» Первые 5 колонок будут ссылками на страницу «Ввод» (№; ФИО; Профессия; Разряд; Бригада). Поэтому просто скопируем их с листа «Часы» или с листа «КТУ«.

Расчет оклада

Теперь нам необходимо рассчитать окладную часть заработной платы. Но, прежде чем рассчитать оклад, который привязан к отработанному времени, необходимо знать его размер в нормальных условиях. Для этого будет предназначена следующая колонка, которую мы назовем «Оклад«.

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

Теперь в колонке «Оклад» в ячейку F5 занесем формулу: =ВПР(D5;Разряд;2;ЛОЖЬ)

Функция ВПР возвращает значение, соответствующее выбранному из другого диапазона. То есть мы ссылаемся на значение в ячейке D5 (Разряд 1), сравниваем его с первой колонкой таблицы «Разряд» (если вы помните, то списку с разрядами мы дали, в свое время, наименование «Разряд«), указываем, что необходимо отобразить значение из второй колонки этой таблицы, которое соответствует нашей записи, и указываем на то, что совпадение должно быть точным.

Протягиваем формулу до нижней строки таблицы.

Добавив колонку в таблицу «Разряды«, мы немного нарушили условие на выбор из списка разрядов на странице «Ввод«. Выделяем все ячейки с разрядами, открываем пункт «Проверка данных» вкладки меню «Данные«. Теперь нам просто необходимо уточнить ссылку — ссылаемся не на всю таблицу, под названием «Разряд«, а еще и на ее колонку, тоже под названием «Разряд«. Наименования колонок в таблице, на которую ссылаемся, пишем в квадратных скобках (см. рис.)

Помимо количества отработанных за месяц часов, необходимо знать норматив — количество рабочих часов, установленных трудовым законодательством и указанных в производственном календаре. Чтобы пользователям не приходилось ежемесячно обращаться к производственному календарю, можно заносить количество нормативных рабочих часов на весь год. Сделаем это на листе «Списки» добавив колонку справа от названий месяцев.

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

Нормативное количество рабочих часов в текущем месяце предлагаю указать с помощью функции ВПР в ячейке справа от названия месяца (F1). Формула в ней будет такой: =ВПР(E1;Списки!A2:B13;2;ЛОЖЬ).

Количество отработанных часов, это сумма строк на странице «Часы«.

Назовем колонку «Оклад начисл.» Вот формула в ячейке G5:

=F5/$F$1*СУММ(Часы!F5:AJ5).

Протянем ее до нижней строки таблицы.

Расчет сделки

Приступаем к разработке формулы расчета сдельной части оплаты труда. Логика расчета такова:

  1. В зависимости от выполнения плана производства, определяется сумма общего котла.
  2. Доля каждого рабочего в этом котле определяется по сумме всех КТУ сотрудника за месяц.
    1. Сумма КТУ всех работников, это 100%.
    2. Доля КТУ работника, это сумма его КТУ за месяц, деленное на общую сумму всех КТУ и умноженное на 100%.
  3. Подобным образом определяем долю отработанного работником времени.
  4. Умножаем долю работника в котле на его же долю в общем количестве отработанных рабочими часов.
  5. Умножаем сумму котла на долю работника, определив, тем самым, сумму его сделки.

Для определения суммы котла руководство определило такое условие:

  • При выполнении плана менее чем на 50% сумма не выделяется;
  • При выполнении плана в диапазоне от 50% до 70% выделяется 1% от месячной суммы выработки;
  • При выполнении плана в диапазоне от 70% до 90% выделяется 2% от месячной суммы выработки;
  • При выполнении плана в диапазоне от 90% до 110% выделяется 3% от месячной суммы выработки;
  • При перевыполнении плана более чем на 110% выделяется 3,5% от месячной суммы выработки.

Заведем следующий список на странице «Списки» (см. рис.)

Назовем этот список «ДоляКотла«. Для определения процента, составляющего долю котла будем использовать функцию ВПР с указанием ИСТИНА (при указании аргумента ИСТИНА будет возвращаться значение, соответствующее ближайшему меньшему значению сравниваемого числа).

Формула расчета будет такой:

ВПР(СУММ(Планирование!$B$9:$AF$9)/Планирование!$B$1;ДоляКотла;2;ИСТИНА).

  • СУММ(Планирование!$B$9:$AF$9) — это сумма выработки за весь месяц;
  • Планирование!$B$1 — это план по выработке;
  • ДоляКотла — это таблица, на которую ссылается функция ВПР.

Теперь приступим к определению доли каждого в этой сумме. Как мы помним, расчет производится дважды — доля от общей суммы КТУ и доля от общего количества отработанного всеми времени. Предлагаю добавить отдельную колонку, которую мы назовем «Персональная доля«. Заносим формулу:

=СУММ(Часы!F5:AJ5)/СУММ(Часы!$F$5:$AJ$32)*СУММ(КТУ!F5:AJ5)/СУММ(КТУ!$F$5:$AJ$32)

Следующий шаг — расчет сделки. Перемножаем сумму выработки, долю котла и персональную долю сотрудника. Формула для сотрудника в верхней строке будет такой:

=Планирование!$B$3*ВПР(СУММ(Планирование!$B$9:$AF$9)/Планирование!$B$1;ДоляКотла;2;ИСТИНА)*H5

Теперь осталось просуммировать все составляющие в колонке «Итого ЗП«, оформить таблицу и все готово.