Заполнение формы Т-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, и курсор встанет на новую ошибочную ячейку.

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

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *