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

Например, столбец A имеет время начала, а столбец B - время окончания.

Столбцы C & D имеют интервалы в 30 минут с 07:00 до 23:00.

В столбце E мне нужна формула, которая будет рассчитывать время, затраченное на задачу, с 09:07-10:56, но для заполнения соответствующего 30-минутного интервала, т. Е. 09:00-09:30 будет составлять 23 минуты, 09:30- 10:00 будет 30 минут и т.д.

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

|источник

1 ответ1

1

Я действительно хотел бы прикрепить файлы
Если у кого-то есть решение для этого, дайте мне знать
У меня есть решение для вас. Сначала немного настроек:

Вы можете настроить ту же самую вещь с динамическими именованными диапазонами и использовать их в своих формулах, но мне нравятся таблицы, поэтому я использовал их.
Я создал таблицу в столбцах A:B с именем tblTimeLog
Имеет два поля: Старт, Стоп
Здесь вы вводите все время запуска и остановки по мере необходимости

Я создал вторую таблицу в столбцах D:G с именем tblTimeSummary
Имеет 4 поля: Интервальный взгляд, Интервал Стоп, Интервал, Общее время
Интервал Start & Stop - это удобство
Вы можете сделать все с одним текстовым полем, показывающим промежуток времени, но формулы будут намного проще, если вы разделите его
Эти два будут содержать диапазоны времени, такие как 07:00 и 07:30, 07:30 и 08:00 и т.д.
Я набрал их вручную и заполнил
Интервал просто объединяет два ради презентации, поэтому вы получаете такие вещи, как «07:00 - 07:30», «07:30 - 08:00» и т.д.
Я использовал здесь формулу: =TEXT([@[Interval Start]],"HH:mm") & " - " & TEXT([@[Interval Stop]],"HH:mm")
Общее время - это место, где будет рассчитываться объем работы, выполненной за этот интервал.

Вот большая формула для общего времени:

=SUMPRODUCT((tblTimeLog[Start]<=[@[Interval Stop]])*(tblTimeLog[Stop]>[@[Interval Start]])*(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])-IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])))*24*60

Давайте разберемся с этим
SUMPRODUCT создаст массивы, умножит каждую строку вместе, а затем суммирует эти строки
Важно знать, что это будет трактовать TRUE как 1 и FALSE как 0

Первый массив (tblTimeLog[Start]<=[@[Interval Stop]])
Это находит все записи журнала, которые начались до конца этого интервала

Второй массив - противоположная идея (tblTimeLog[Stop]>[@[Interval Start]])
Он находит все записи журнала, которые закончились после начала этого интервала
Вместе они находят все записи в журнале, которые имеют некоторое время в течение интервала

(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])
Этот массив выбирает либо конец интервала, либо конец действия, в зависимости от того, что было первым

IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])
Этот массив выбирает либо начало интервала, либо начало действия, в зависимости от того, что наступило последним

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

*24*60 этот бит в конце просто превращает значение времени в днях в значение в минутах

Подведем итоги с помощью некоторого псевдокода:
=Sum((If the log entry is in this interval)*(How much time was spent in this interval))
Первый массив будет 1 или 0, а второй будет некоторое значение времени
Умножение их вместе дает массив значений 0 и времени
Суммирование, которое дает вам общее зарегистрированное время в этом интервале

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .