Страницы: 1
RSS
Подсчет общего времени с учетом пересечения часовых сессий
 
Здравствуйте. Нужна помощь в решении такой задачи.

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


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

Например: 01/06/20 Иван работал с 12 до 15 потратив 3 часа, Алексей в этот же день работал с 14 до 15, потратив 1 час. Общее затраченное ими время - 4 часа, но мне нужно получить лишь время самой рабочей сессии, начало в 12 и завершение в 15, т.е. 3 часа.

Изменено: andrey008 - 01.05.2020 20:58:48
 
PQ
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[Date],[Логин],[Логаут]],
    typ = Table.TransformColumnTypes(from,{{"Логин", type time}, {"Логаут", type time}, {"Date", type date}}),
    add = Table.AddColumn(typ, "times", each List.Times([Логин],Duration.Hours([Логаут]-[Логин]),#duration(0,1,0,0)))[[Date],[times]],
    extr = Table.TransformColumns(add, {{"Date", each Date.ToText(_,"MMMM yy"), type text}}),
    exp = Table.ExpandListColumn(extr, "times"),
    dist = Table.Distinct(exp),
    to = Table.Group(dist, {"Date"}, {{"Часы", each Table.RowCount(_), type number}})
in
    to
Изменено: buchlotnik - 01.05.2020 12:28:10
Соблюдение правил форума не освобождает от модераторского произвола
 
andrey008, не уверен что понял почему по месяцам нужно
то делать -массиивная
Код
=(МАКС(ЕСЛИ(($A$2:$A$5>=$H7)*($A$2:$A$5<=КОНМЕСЯЦА(H7;0));$E$2:$E$5))-МИН(ЕСЛИ(($A$2:$A$5>=$H7)*($A$2:$A$5<=КОНМЕСЯЦА(H7;0));$D$2:$D$5)))/0,0416666666666667
Изменено: Mershik - 01.05.2020 14:18:15
Не бойтесь совершенства. Вам его не достичь.
 
Уточните название темы. Подсчет времени с условием - таких задач множество.
Заменят название модераторы
 
Mershik, да, я не совсем верно описал задачу, прошу прощения. Нужно чтобы по вашему методу считались часы за каждый день заданного месяца (т.е. с условием, что если часы в этот день пересекаются - то считается разница между максимальным временем логаута и минимальным временем логина), и затем уже считается их сумма за весь месяц.
 
vikttur, нигде не вижу возможности изменить название темы
 
andrey008,
Цитата
andrey008 написал:
Заменят название модераторы
Цитата
andrey008 написал:
Уточните название темы
Не бойтесь совершенства. Вам его не достичь.
 
buchlotnik, из-за неправильной постановки задачи, код выдает такой же результат, как и формула Mershik, т.е. не учитываются часы за каждый отдельный день заданного месяца
 
Mershik,
Цитата
Mershik написал:
Заменят название модераторы
Дошло :D

"Подсчет общего времени с учетом пересечения часовых сессий"
 
andrey008, чуть отредактировал вашу исходную таблицу (заменил данные в таблице в последнем столбце)
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
andrey008 написал:
не учитываются часы за каждый отдельный день
удалите лишний шаг и все:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[Date],[Логин],[Логаут]],
    typ = Table.TransformColumnTypes(from,{{"Логин", type time}, {"Логаут", type time}, {"Date", type date}}),
    add = Table.AddColumn(typ, "times", each List.Times([Логин],Duration.Hours([Логаут]-[Логин]),#duration(0,1,0,0)))[[Date],[times]],
    exp = Table.ExpandListColumn(add, "times"),
    dist = Table.Distinct(exp),
    to = Table.Group(dist, {"Date"}, {{"Часы", each Table.RowCount(_), type number}})
in
    to
Соблюдение правил форума не освобождает от модераторского произвола
 
Mershik, спасибо! Снова нюанс, который я не учел. Если рабочих сессий за один день несколько, скажем утренняя и вечерняя, а между ними перерыв. В таком случае нужно чтобы считалась разница максимального и минимального значения времени не за весь день сразу, а сперва за первую сессию, потом за вторую, и потом уже их сумма за день, и, наконец, сумма за месяц. Для этого нужно ввести еще одно дополнительное условие в формулу?
 
andrey008, наверное нужно еще  - но я не хочу делать по 100 раз, так как Вам "в западло" нормальный пример сделать...
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, спасибо и на этом. Не получается нормально задачу описать, чтоб все сразу учесть
 
Цитата
andrey008 написал:
Если рабочих сессий за один день несколько, скажем утренняя и вечерняя, а между ними перерыв.
так ведь мой вариант это учитывает
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
так ведь мой вариант это учитывает
У меня почему-то не работает ваш вариант, т.е. совсем не реагирует на изменение вводных данных, постоянно "3" выдает. Макросы включены
 
Цитата
andrey008 написал:
Макросы включены
какие макросы? это PQ: Данные - Обновить все
Изменено: buchlotnik - 01.05.2020 17:57:55
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
какие макросы? это PQ
Я вот это имею ввиду, с терминологией не очень)
Изменено: andrey008 - 01.05.2020 17:59:55
 
Цитата
buchlotnik написал:
Данные - Обновить все
или Alt+Ctrl+F5
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, можно как-то изменить код, чтобы правильно отображалось время десятичным числом? Например, когда надо вывести результат 7,5 - фактически выводится 7
 
Цитата
andrey008 написал:
можно как-то изменить код
можно, и наверняка кто-то поможет, но не я
Цитата
Mershik написал:
так как Вам "в западло" нормальный пример сделать...
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
так как Вам "в западло" нормальный пример сделать...
Мне не в западло, опыта недостаточно, поэтому и обратился сюда
 
Цитата
andrey008 написал:
Мне не в западло
ну и где?
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
ну и где?

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

Например: 1/06 было три сессии:

первая 12:00 - 15:00

вторая 14:00 - 15:45

третья 19:00 - 22:30.

Певая и вторая сессия пересекаются, поэтому их общий результат должен быть посчитан как разница 15:45 минус 12:00, т.е. 3 часа и 45 минут. Также в этот день была третья сессия с 19:00 до 22:30 - длительность которой 3 часа и 30 минут.

Т.е. общее "полезное" время за этот день составляет 7 часов и 15 минут.Таким образом нужно посчитать "полезное время" за каждый месяц.

 
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[Date],[Логин],[Логаут]],
    typ = Table.TransformColumnTypes(from,{{"Логин", type time}, {"Логаут", type time}, {"Date", type date}}),
    add = Table.AddColumn(typ, "times", each List.Times([Логин],Duration.TotalMinutes([Логаут]-[Логин]),#duration(0,0,1,0)))[[Date],[times]],
    exp = Table.ExpandListColumn(add, "times"),
    dist = Table.Distinct(exp),
    group = Table.Group(dist, "Date", {"Часы", each Table.RowCount(_)/60}),
    month = Table.TransformColumns(group, {{"Date", each Date.ToText(_,"MMMM yy"), type text}}),
    group2 = Table.Group(month, "Date", {"Часы", each List.Sum([Часы]), type number})
in
    group2
Изменено: buchlotnik - 01.05.2020 20:57:14
Соблюдение правил форума не освобождает от модераторского произвола
Страницы: 1
Наверх