Вопрос или проблема
Я искал и не смог найти решение.
Мне нужно найти сумму уникальных временных промежутков для Excel-таблицы с более чем 200 строками.
Например.
На изображении выше общее уникальное время составит 6 часов (2 часа с 12:00 до 14:00, 2 часа с 14:00 до 16:00 и 2 часа с 17:00 до 19:00).
Любая помощь будет очень appreciated! Спасибо
Не знаю, с чего начать, кроме как вручную складывать уникальные временные промежутки для каждой строки, а затем суммировать.
Предполагая, что ваш второй временной промежуток — это опечатка, и что формула для продолжительности (часы) выглядит как =(B2-A2)*24, вы можете добавить столбец, который измеряет наложение между последовательными строками, а затем просто СУММИРОВАТЬ продолжительность и наложение.
например:
D3: =MIN(0,A3-B2)*24
и заполните вниз
Уникальные часы:
=SUM(C2:D6)
Ответ или решение
Для решения задачи суммирования уникальных временных отрезков в Excel, особенно когда у вас более 200 строк данных, предлагаю следующее пошаговое руководство.
Шаг 1: Подготовка данных
Допустим, у вас есть следующая структура данных в Excel:
- Столбец A: Начало временного отрезка (например, "12:00").
- Столбец B: Конец временного отрезка (например, "14:00").
- Столбец C: Длительность (вычисляется как
(B - A) * 24, где 24 — это количество часов в дне).
Шаг 2: Вычисление продолжительности
Для вычисления продолжительности временного отрезка в часах используйте следующую формулу. Если ваши данные начинаются с первой строки:
- В ячейку C2 введите формулу:
=(B2 - A2) * 24 - Скопируйте эту формулу для всех строк в столбце C.
Шаг 3: Выявление перекрывающихся временных отрезков
Для того чтобы определить, сколько времени перекрывается между разными временными отрезками, создайте новый столбец D:
-
В ячейку D3 (вторая строка второго дня со второго ряда) введите следующую формулу, которая будет вычислять перекрытие с предыдущим временным отрезком:
=MAX(0, MIN(B3, B2) - MAX(A3, A2)) * 24- Эта формула вычисляет, сколько часов пересекается между временем начала и временем конца текущей и предыдущей строк.
-
Скопируйте эту формулу для всех строк в столбце D.
Шаг 4: Суммирование уникального времени
Теперь, когда у вас есть длительность и перекрытие, время без учета перекрытий можно суммировать:
- В ячейку E переменной результата (например, E2) введите следующую формулу для суммирования уникального времени:
=SUM(C2:C200) - SUM(D3:D200)- Сначала вы суммируете все временные отрезки, а затем вычитаете все перекрытия, которые были посчитаны.
Пример
Предположим, у вас есть такие временные отрезки:
- 12:00 – 14:00
- 14:00 – 16:00
- 17:00 – 19:00
После выполнения всех шагов, вы получите уникальную продолжительность, которая, в данном примере, составит 6 часов (2 + 2 + 2) без учета повторяющегося времени.
Заключение
Таким образом, summing unique times в Excel требует определения длительности временных отрезков, идентификации перекрытий и вычисления итоговой суммы, исключая дублирующиеся часы. Следуя этим шагам, вы сможете эффективно подсчитывать уникальное время даже в больших наборах данных без необходимости ручного суммирования.
Если у вас возникли дополнительные вопросы или трудности, пожалуйста, дайте знать!