Страницы: 1
RSS
Сложный расчет работы часов сотрудника (учет проходов через турникет)
 
Друзья, добрый день.
Вчера друг скинул файл с просьбой посмотреть как посчитать время работы сотрудников, думал сделаю в 2 формулы, оказалось все не так просто.
Дано: Есть данные с турникетов Вход и Выход с временем по сотрудникам на каждый день.
Нужно: Рассчитать кол-во рабочих дней и часов за данный период

Что усложняет задачу:
1) Некоторые сотрудники проходят 2 турникета (первое кольцо и второе), при этом они могут иметь 2 входа (как 1 так и 2), и 1 выход через первой кольцо (т.е. на внутреннем кольце не отметился). - это я решил через поиск наименьшего и наибольшего времени в течение дня т.е. считать достаточно по первому входу и последнему выходу.
2) Есть ночные смены, где сотрудник пришел на работу вечером, а ушел утром следующего дня. Тут вся логика из пункта 1 ломается т.к. оцениваю разницу внутри дня.

Необходимый результат:
1) Таблица с ФИО, День, Кол-во отработанных часов (для ночных смен день это дата входа).
2) Совокупная таблица с количеством отработанных дней и часов суммарно.

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

Заранее спасибо.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
Вчера друг
 
"в лоб" на PQ
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([Тип] = "Проходная 1")),
    typ = Table.TransformColumnTypes(filtr,{{"Дата", type number}, {"Время", type number}}),
    add = Table.AddColumn(typ, "date", each [Дата]+[Время]),
    rem = Table.SelectColumns(add,{"ФИО сотрудника", "date", "Событие"}),
    lst = Table.ColumnNames(rem)&{"last"},
    tbl = Table.FromColumns(Table.ToColumns(rem)&{List.Skip(rem[date],1)},lst),
    filtr2 = Table.SelectRows(tbl, each ([Событие] = "Вход")),
    add2 = Table.AddColumn(filtr2, "time", each [last]-[date]),
    to = Table.SelectColumns(add2,{"ФИО сотрудника", "date", "time"})
in
    to
Соблюдение правил форума не освобождает от модераторского произвола
 
Если п данные в таком виде и работник не шастает туда сюда в течении смены бесконечно (сейчас просто для примера сделал на 10 входов и выходов что помогает пошустрее считать или не выяснять диапазон) ,  то
=IF(COUNTIFS($B$1:B2;B2;$A$1:A2;A2;$D$1:D2;"Вход")=1;IF(LOOKUP(2;1/(A2:A12=A2)/(B2:B12=B2);D2:D12)="Выход";LOOKUP(2;1/(A2:A12=A2)/(B2:B12=B2);C2:C12);INDEX(B2:B12;MATCH(1;(A2:A12=A2)*(B2:B12>B2)*(D2:D12="Выход");))-B2+INDEX(C2:C12;MATCH(1;(A2:A12=A2)*(B2:B12>B2)*(D2:D12="Выход");)))-C2;"")
остальное дело техники
Изменено: БМВ - 14.07.2020 08:37:45
По вопросам из тем форума, личку не читаю.
 
БМВ,действительно, могут входить и выходить в течение дня. Что то я не учел это в примере.
Верно понимаю что эту формулу вы зашили в вычисляемое поле сводной таблицы?
buchlotnik, моя вина, не совсем корректный пример в этом плане. названия проходных могут отличаться и они не столь очевидно пронумерованы. Есть 1 западная, южная и еще куча разных, так и внутренние имеют разнообразные названия.

Михаил Л, изучаю функцию агреграт, спасибо
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
не совсем корректный пример
а корректный пример будет?
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
phelex написал:
Верно понимаю что эту формулу вы зашили в вычисляемое поле сводной таблицы?
нет не верно. Сводная строится на основании основной таблицы и доп. столбца.
По вопросам из тем форума, личку не читаю.
 
phelex, вот ЗДЕСЬ была подобная тема, может, что подойдет
 
Цитата
buchlotnik написал:
названия проходных могут отличаться
вариант:
Скрытый текст
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik,спасибо огромное.
Честно не совсем разобрался как работает, но при проверке на большой базе считает вообще все, даже если выходили несколько раз за день считает периоды проведенные на территории.

Буду разбираться как это работает.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
считает периоды проведенные на территории
ну это обычно и есть основная проблема - "перекуры", которые то ли надо, то ли не надо учитывать  ;)
Изменено: buchlotnik - 14.07.2020 12:11:51
Соблюдение правил форума не освобождает от модераторского произвола
Страницы: 1
Наверх