Добрый день. Нужна помощь с формулой.. Есть файл, где отмечены время прихода и ухода каждого сотрудника. И нужно посчитать сколько времени каждый из них находился на территории предприятия. Там больше трёхсот человек и я не знаю как прописать формулу для подсчета. Время с минусом справа, типа "06:26-" это вход, а время с минусом слева, типа "-16:51" это выход сотрудника. Подскажите, пожалуйста, возможно ли это посчитать формулой? Может кто-то сможет помочь, файл во вложении.
Добрый день. Нужна помощь с формулой.. Есть файл, где отмечены время прихода и ухода каждого сотрудника. И нужно посчитать сколько времени каждый из них находился на территории предприятия. Там больше трёхсот человек и я не знаю как прописать формулу для подсчета. Время с минусом справа, типа "06:26-" это вход, а время с минусом слева, типа "-16:51" это выход сотрудника. Подскажите, пожалуйста, возможно ли это посчитать формулой? Может кто-то сможет помочь, файл во вложении.krdimk
версия Excel какая? именно формулой или PQ допустим? в каком виде нужен результат? и что делать например с четвертой строкой где все начинается с времени выхода?
версия Excel какая? именно формулой или PQ допустим? в каком виде нужен результат? и что делать например с четвертой строкой где все начинается с времени выхода?прохожий2019
Сообщение отредактировал прохожий2019 - Суббота, 11.09.2021, 18:39
Результат нужен в виде количества часов, которые сотрудник провел на территории предприятия за месяц. Нужно чтобы от времени выхода, типа "-16:51" отнималось время входа, типа "06:26-" и получался результат "10:25". Проблема в том, что есть ночные смены, где человек зашел вечером и время в одной ячейке, а вышел на следующий день и время выхода в другой ячейке. Нужно как-то посчитать по условию если левый символ "-", то это время выхода и от него отнять время с правым символом "-" (время входа), но я не знаю как это сделать.
Результат нужен в виде количества часов, которые сотрудник провел на территории предприятия за месяц. Нужно чтобы от времени выхода, типа "-16:51" отнималось время входа, типа "06:26-" и получался результат "10:25". Проблема в том, что есть ночные смены, где человек зашел вечером и время в одной ячейке, а вышел на следующий день и время выхода в другой ячейке. Нужно как-то посчитать по условию если левый символ "-", то это время выхода и от него отнять время с правым символом "-" (время входа), но я не знаю как это сделать.krdimk
В четвертой строке человек работал в ночную смену, и зашел вечером предыдущего месяца. Что с ней делать точно не знаю, желательно чтобы "-07:49" прибавилось к общему времени за месяц.
В четвертой строке человек работал в ночную смену, и зашел вечером предыдущего месяца. Что с ней делать точно не знаю, желательно чтобы "-07:49" прибавилось к общему времени за месяц.krdimk
строка 14 - Федя, 15 апреля человек вышел в ночную смену... и? как поступать с такими данными? покажите в примере какие числовые значения должны быть на выходе
строка 14 - Федя, 15 апреля человек вышел в ночную смену... и? как поступать с такими данными? покажите в примере какие числовые значения должны быть на выходепрохожий2019
Ну 14-я строка это какой-то особый случай, он 15-го апреля зашел и не вышел.. Возможно у него охрана отобрала пропуск, если заходил не трезвый и его уволили.. Но если не учитывать последний вход, то на территории предприятия он провел 80 часов 31 минуту. Я посчитал вручную, сумма в красной ячейке. Единичные случаи не так важны.
Ну 14-я строка это какой-то особый случай, он 15-го апреля зашел и не вышел.. Возможно у него охрана отобрала пропуск, если заходил не трезвый и его уволили.. Но если не учитывать последний вход, то на территории предприятия он провел 80 часов 31 минуту. Я посчитал вручную, сумма в красной ячейке. Единичные случаи не так важны.krdimk
Да, спасибо вам большое. Можете подсказать, как вы это посчитали? Мне, возможно, не один месяц нужно будет пересчитать.. В вашем файле я формул не нашел...
Да, спасибо вам большое. Можете подсказать, как вы это посчитали? Мне, возможно, не один месяц нужно будет пересчитать.. В вашем файле я формул не нашел...krdimk
Сообщение отредактировал Serge_007 - Понедельник, 13.09.2021, 09:48
дык сказал же - PQ - вкладка Данные - запросы и подключения. Основной запрос[vba]
Код
let from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="TData"]}[Content]), nms = List.Buffer(Table.ColumnNames(from)), item = Text.Start(nms{3},2), lst = List.Select(nms,(x)=>Text.Start(x,2)=item), comb = Table.CombineColumns(from,lst,Text.Combine,"time")[time], trnsf = List.Transform(comb,TotalHours) in trnsf
[/vba]ну и функция [vba]
Код
TotalHours (x)=> [ textconvert = (x) => [ a = if Text.Start(x,1)="-" then "00:00-"&x else x, b = if Text.End(a,1)="-" then a & "-23:59;" else a][b], gethours = (x) => [ a = List.Transform(Text.Split(x,"--"),Time.From), b = if a{0}<=a{1} then a{1}-a{0} else #duration(1,0,0,0)+(a{1}-a{0})][b],
split = Text.Split(textconvert(x),";"), rem = List.RemoveMatchingItems(split,{null,""," "}), trnsf = List.Transform(rem,gethours), sum = List.Sum(trnsf), tohours = Duration.TotalHours(sum),
result = if x = null or x = "" or x = " " then 0 else tohours ][result]
[/vba]тут надо решить, что делать с отсутствующими данными, когда они в середине (отсюда и 8 ошибок) - пока просто игнорятся
дык сказал же - PQ - вкладка Данные - запросы и подключения. Основной запрос[vba]
Код
let from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="TData"]}[Content]), nms = List.Buffer(Table.ColumnNames(from)), item = Text.Start(nms{3},2), lst = List.Select(nms,(x)=>Text.Start(x,2)=item), comb = Table.CombineColumns(from,lst,Text.Combine,"time")[time], trnsf = List.Transform(comb,TotalHours) in trnsf
[/vba]ну и функция [vba]
Код
TotalHours (x)=> [ textconvert = (x) => [ a = if Text.Start(x,1)="-" then "00:00-"&x else x, b = if Text.End(a,1)="-" then a & "-23:59;" else a][b], gethours = (x) => [ a = List.Transform(Text.Split(x,"--"),Time.From), b = if a{0}<=a{1} then a{1}-a{0} else #duration(1,0,0,0)+(a{1}-a{0})][b],
split = Text.Split(textconvert(x),";"), rem = List.RemoveMatchingItems(split,{null,""," "}), trnsf = List.Transform(rem,gethours), sum = List.Sum(trnsf), tohours = Duration.TotalHours(sum),
result = if x = null or x = "" or x = " " then 0 else tohours ][result]
[/vba]тут надо решить, что делать с отсутствующими данными, когда они в середине (отсюда и 8 ошибок) - пока просто игнорятсяпрохожий2019