Как рассчитать для "Engaged Time" в Excel

Question

У меня есть набор данных, как это:

       |      A         |          B       | 
       -------------------------------------
    1  | Start Time     |   End  Time      |
    2  |  6:43 AM       |     7:00 AM      |
    3  |  8:01 AM       |     9:43 AM      |
    4  |  8:02 AM       |     8:20 AM      |
    5  |  8:22 AM       |     8:57 AM      |
    6  |  8:58 AM       |     9:55 AM      |
    7  |  1:20 PM       |     2:41 PM      |
    8  |  1:27 PM       |     2:11 PM      |

В каждой строке представлены случаи, когда сотрудник работает над задачей, в данном случае в период с 6:43 до 14:11. Таким образом, используя здравый смысл, вы должны вычесть 'Время окончания' до 'Время начала', чтобы получить продолжительность времени 'Задействовано'. Но это не помолвленное время. Это «Длина задачи». Время, которое он взял, чтобы закончить 1 задание.

Проблема в том, что 1 сотрудник может работать над несколькими задачами одновременно. Если вы посмотрите на строку 3 (с 8:01 до 9:43), сотруднику потребовалось более 1 часа, чтобы выполнить одну задачу. Выполняя эту конкретную задачу, он начал выполнять другие задачи в строках 4, 5 и 6.

думая о «помолвленном времени», он занимался с 8:01 до 9:55. Я не могу придумать динамическую формулу, которая будет делать это автоматически для всей таблицы. Другая проблема заключается в том, что это набор данных только для 1 сотрудника за 1 день. У меня есть 10 сотрудников, которые работают 5 дней в неделю. Они производят около 3000 строк данных за 1 месяц. Можете ли вы предложить формулу, технику сводных таблиц или код VBA для динамического сокращения чисел?

Спасибо!

Answer 1

Посмотрите, поможет ли это, создайте 2 вспомогательных столбца, первый из них - номер блока. Это показывает блок времени, в течение которого выполняется одна или несколько задач.

Формулы e3 = 1, f3 = c3, чтобы обеспечить начало начальной точки.

Остальная часть столбца e, начинающаяся с e4, использует

• =+ ЕСЛИ (B4> F3,1,0)+ Е3

где он проверяет новое время начала, чтобы увидеть, находится ли оно внутри текущего времени окончания. если есть, блок расширяется, иначе запускается новый номер блока

Столбец f, начинающийся с f4, использует

• =+ ЕСЛИ (Е4> Е3, С4, MAX (С4, F3))

Где проверяется, было ли продлено время окончания блока. Для каждого блока минимальное время в b и максимальное в c будут генерировать время включения.

Я надеюсь, что понимаю вашу проблему достаточно.