Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Анализ табеля рабочего времени
 
#1
05.04.2015 11:19:05
Прошу помощи в написании макроса для анализа табеля рабочего времени Т-13.
Дано (вложение):
  1. Список сотрудников (B)
  2. Отметки об отработанных ими днях (F-V)
  3. Сумма отработанных часов (Z)
  4. Условное обозначение и количество дней неявок (AB и AD)
Требуется высести таблицу (для примера Лист2):
ФИО | Средняя длительность смены | Б | ОТ | ОД |

Средняя длительность вычисляется исходя из длительностей, которые указаны в столбцах  (F-V). Для определения 8 или 11 часов работает человек.
Б,ОТ,ОД - количество указанных неявок для каждого сотрудника.
Изменено: RIF - 05.04.2015 12:10:43
 
 
 
#2
05.04.2015 11:24:23
Вы бы сразу в примере создали нужную Вам форму таблицы Анализа (на отдельном листе) и пару строк заполнили вручную, для примера. Так легче будет макрос написать
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
05.04.2015 12:00:26
Спасибо за совет. Добавлен Лист 2 с примером.
 
 
 
#4
05.04.2015 12:03:22
Если будут ответы в параллельно созданных темах, нужно показывать ссылки на эти темы.
 
 
 
#5
05.04.2015 12:21:26
Среднее:
=ИНДЕКС(Лист1!$X$13:$X$22;ПОИСКПОЗ(A2;Лист1!$B$11:$B$19))/ВПР(A2;Лист1!$B$11:$AK$22;23;)
С неявками сложнее: нет порядка в размещении данных. Вернее, какой-то порядок есть, но для автообработки - это беспорядок :)
Если формулами, нужно изворачиваться.
 
 
 
#6
05.04.2015 12:24:30
А может для подсчета средней продолжительности смены использовать уже посчитанную сумму часов в столбце X-знаменатель? И разделить ее на количество смен (столбец X-числитель)?
Согласие есть продукт при полном непротивлении сторон
 
 
 
#7
05.04.2015 12:25:12
Так и сделано
 
 
 
#8
05.04.2015 12:31:16
Среднее это побочная задача. Основное - неявки.
 
 
 
#9
05.04.2015 12:40:28
Лучше макросом.
А ссылку на эту тему (туда, на excel-vba.ru/forum) сами, хорошо?
 
 
 
#10
05.04.2015 14:45:50
Пропуски на лист2 подтянуты формулами, а вот среднее не осилил - непонятен алгоритм расчета, единицы измерения.
Задание TheBest.xlsx (15.19 КБ)

PS Ячейка AD15 = 11, а не 14 как в файле
Изменено: TheBestOfTheBest - 05.04.2015 14:51:28 (добавлен PS)
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#11
05.04.2015 15:28:20
TheBestOfTheBest, СМЕЩ и ДВССЫЛ - летучие (пересчет при любых изменениях на листе). плюс к этому ДВССЫЛ имеет ряд недостатков. Если есть аналогичная замена, лучше обхдиться без таких функций.
Например:
=СЧЁТЕСЛИ(ИНДЕКС(Лист1!$F$11:$V$22;ПОИСКПОЗ($A2;Лист1!$B$11:$B$22;);):ИНДЕКС(Лист1!$F$11:$V$22;ПОИСКПОЗ($A2;Лист1!$B$11:$B$22;)+3;);C$1)
За счет определения двух границ диапазона формула длиннее, но не летуча.

Цитата
С неявками сложнее
Подразумевались данные в диапазоне AB:AD. А ведь можно посчитать без них, по графику. Что и делает показанная формула.
Но, кстати, беспорядок и в диапазоне графика наблюдается: например, у сотрудника1 "ОТ" записано во второй строке, для других - в первой, хотя эти строки принадлежат одному диапазону дат (до 15 числа). Бардак у табельщика! :)
 
 
 
#12
05.04.2015 16:04:03
Осмелюсь спросить, а Ваша формула не будет пересчитываться "при любых изменениях на листе" ?
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#13
05.04.2015 16:56:04
Нет, эта пересчитывается только при изменении входных данных. Почитайте, на форуме об этом много было - "летучие функции". Таких функций мало, штук 7-8 (СМЕЩ, ТДАТА, СЕГОДНЯ, ДВССЫЛ, ЯЧЕЙКА, СЛЧИС, СЛУЧМЕЖДУ...)
 
 
 
#14
06.04.2015 10:00:26
Спасибо, посчитал.

В процессе анализа табеля возник нюанс:
Сотрудник1 имеет больничный с 14 до 23. Работает в смену 2/2 (по 11 часов). Соответственно фактически он не вышел на 6 рабочих дней (15-16, 19-20, 23-24), однако в табеле больничных указано 10. Таким образом от положенных 154 часов в месяц (столбец Y) он не вышел не 11*6=66 часов.
Данный нюанс распространяется на больничные (Б) и Отпуска (ОТ).

Можно ли каким-то образом выстроить анализ на основе отработанных смен по тому количеству смен, которые попали на Отпуск и Больничный? То есть что бы система определила описанные выше 6 смен, а не 10.
 
 
 
#15
06.04.2015 10:25:03
Цитата
RIF написал: отработанных смен ... которые попали на Отпуск и Больничный
Такого не должно быть. Смена либо отработана, тогда это не больничный и не отпуск, либо нет.
Чтобы посчитать количество смен по графику, которое проведено в отпуске, потребуется для каждого сотрудника указывать график для текущего табеля (сейчас в файле этого нет). Но это задача скорее в раздел  форума "Работа".
Изменено: TheBestOfTheBest - 06.04.2015 10:27:10
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#16
06.04.2015 11:00:48
Больничные и отпуска проставляются исходя из промежутка дат больничного листа или на продолжительность всего отпуска, независимо есть там смена или нет. Изначально все на графиках и работало. Проблема в том, что графики заполяют руководители, которые постоянно ошибаются, забывают и т.д. Поэтому и пришла идея оттолкнуться от официального табеля с которым не поспоришь.
 
 
 
#17
06.04.2015 11:10:04
Табель в таком виде плохо поддается автообработке (объединения ячеек, перемешивание неоднотипных данных).
Теперь оттолкнулись от официального и... чем лучше? Нажили еще одну проблему.
 
 
 
#18
06.04.2015 11:21:06
Насколько понимаю, требуется решение для подсчета выработки сотрудников от нормы часов с учетом оф.отсутствий. Задача не решается путем одного табеля и двух-трех обозначений. Если лезть глубже для качественной отработки задачи необходимо еще учитывать ТК РФ, принципы оплаты праздничных и выходных дней, форму работы в трудовом договоре (ГРВ, СРВ, НРВ), период учета рабочего времени и еще кучу всего.
Я еще ооочень далеко не волшебник, я только учусь...
 
 
 
#19
06.04.2015 16:13:57
С помощью форму все написано. Проблема осталась только для случая, когда есть и Больничный и отпуск. В этом случае их нельзя разделить.
Если есть что-то одно, то просто недоработка часов делится на длительность смены.
 
 
 
Страницы: 1
Читают тему
Наверх