Как мне `SUM` по нескольким столбцам в Excel

Question

У меня есть файл с разделителями-запятыми, который включает в себя два столбца даты / времени (который импортирует в формате Excel в формате mm/dd/yyyy hh:mm ) и статус 1 или 0. Статус представляет собой часть оборудования, включенную или выключенную. Я пытаюсь сгенерировать график, который покажет, часы вверх против вниз днем.

РАССМАТРИВАТЬ:

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

Это говорит мне о том, что оборудование работало в течение трех часов, выключалось в течение одиннадцати часов, а затем работало в течение тридцати четырех часов (в течение двух календарных дней). Тем не менее, я хотел бы создать график, который показывает, сколько часов в день мы были вверх или вниз.

РАССМАТРИВАТЬ:

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

Мне кажется, мне нужно сгенерировать набор данных, суммирующий ЧАСЫ по СТАТУСУ К ДНЮ КАЛЕНДАРЯ ... но я не могу найти какой-либо вариант сводной таблицы или вложенной комбинации SUM(IF(SUMIF(...))) чтобы это работало.

Больше всего беспокоит учет изменений даты ... в моем примере выше, так как мое время безотказной работы, начинающееся с 01:00 по 01.01.2012, пересекает полночь, мне нужно знать, что 10 часов безотказной работы суммируются с 01.01.2012 и 24 Время работы в часах составило 1/2/2012.

Возможно, я смогу что-то сделать со списком календарей для суммирования дат, но тогда мне понадобится способ сравнить 01/01/2012 с 01/01/2012 03:00 как равный. Должен быть способ, if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0) но пока ничего не работает.

Какие-либо предложения? Я боролся с этим большую часть дня, и мне нужна свежая перспектива.

Спасибо

Answer 1

OMGBBQ это сложно! Я хотел бы объяснить это лучше, но, пожалуйста, потерпите меня.

Мое решение требует некоторой дополнительной недвижимости - 3 столбца, если быть точным - и основано на нескольких предположениях:

1. Данные меток времени будут иметь чередующиеся строки из 1 и 0, имитирующие включение / выключение, как вы описали (см. Изображение 1 ниже).
2. Отметки времени расположены от самых старых до самых новых.

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

Столбец E отслеживает количество часов, оставшихся на день в каждой отметке времени. Я использовал это, чтобы "РАЗДЕЛИТЬ" прошедшие времена, которые пересекают полночь.

Столбцы ВКЛ / ВЫКЛ

Вот формула в столбце ON:

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

Эта формула добавляет два значения:

1. Количество часов, в течение которых переключатель включен, если запись не является последней отметкой времени дня; в противном случае 0.

2. Количество часов, оставшихся до полуночи, если это последняя отметка времени дня ИЛИ количество часов, прошедших с полуночи, если это первая отметка времени дня.

Формула в столбце OFF аналогична, за исключением того, что проверяется, если Status = 0.

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

Чтобы извлечь DATE из столбца метки времени, я использовал функцию INT() . Excel использует целые числа для представления даты, а десятичные дроби для представления времени (часть дня или 24 часа). Например, 06/01/2012 18:00 равно 41061.75 (18 часов после полуночи 18/24, что составляет 0,75). Я считаю, что это отвечает на ваш последний абзац.

Консолидация часов

Вот формула в столбце Hours ON (см. Изображение ниже). Это формула массива, поэтому вам нужно ввести ее, используя Ctrl+Shift+Enter, а затем скопировать ее:

=SUM((INT(stamps)=$G3)*hours_on)

В столбце Часы выключены :

=SUM((INT(stamps)=$G3)*hours_off)

где
stamps - это именованный диапазон, относящийся к диапазону Time Stamp (столбец A в моем примере)
hours_on - это именованный диапазон, относящийся к диапазону ON (столбец C)
hours off - это именованный диапазон, относящийся к диапазону выключения (столбец D)

Диаграмма

Обратите внимание, что для каждой даты количество часов ВКЛ и ВЫКЛ составляет до 24.

Если вы хотите изучить формулы и рабочую книгу, вот копия: http://db.tt/KZgH7SFV

Answer 2

Предполагая, что у вас есть метки времени в A3:A24 и статус в B3:B24 согласно предложению Kaze, тогда вы можете получить [десятичное] время ON для даты в D3 с этой формулой

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

столбцы "помощник" не требуется ....

Answer 3

Это не красиво, но вот решение:

После ваших двух столбцов добавьте третий столбец, определенный как день ([datecolumn])

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

Затем вы можете сделать столбцы суммы, определенные как

sumif(D1:D99, 1, C1:C99)

Который в приведенном выше случае покажет вам сумму единиц только в том случае, если столбец D (ранее определенный как часть "день" даты-времени равен 1). Повторите эту формулу для каждого дня месяца (часть, о которой я не так без ума), и вы должны иметь свои ценности.

Сумма часов на 1-е число месяца

sumif(D1:D99, 1, C1:C99)

Сумма часов на 2-е число месяца

sumif(D1:D99, 2, C1:C99)

Сумма часов 3-го числа месяца

sumif(D1:D99, 3, C1:C99)

и так далее...