☰

Как выполнить `СУММ` по нескольким столбцам в Excel

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Как выполнить `СУММ` по нескольким столбцам в Excel

У меня есть файл с разделителями-запятыми, который включает два столбца: дата/время (который импортируется как mm/dd/yyyy hh:mmпользовательский формат Excel) и статус 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(...)))комбинации, чтобы это работало.

Наибольшую проблему представляет учет изменений дат... в моем примере выше, поскольку время безотказной работы, начавшееся в 14:00 1/1/2012, пересекает полночь, мне нужно знать, что 10 часов безотказной работы суммируются с 1/1/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)но пока ничего не работает.

Есть предложения? Я боролся с этим большую часть дня и мне нужен свежий взгляд.

Спасибо

решение1

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

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

  1. Данные временной метки будут иметь чередующиеся строки единиц и нулей, имитируя переключатель «вкл./выкл.», как вы описали (см. рисунок 1 ниже).
  2. Временные метки отсортированы от самых старых к самым новым.

введите описание изображения здесь

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

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

ВКЛ/ВЫКЛ колонки

Вот формула в столбце 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))

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

Объединение часов

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

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

вЧасы ВЫКЛстолбец:

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

где
stamps— именованный диапазон, ссылающийся наОтметка временидиапазон (столбецАв моем примере)
hours_on— это именованный диапазон, ссылающийся наНАдиапазон (столбецС)
hours off — это именованный диапазон, ссылающийся наВЫКЛЮЧЕННЫЙдиапазон (столбецД)

Диаграмма

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

введите описание изображения здесь

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

решение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

никаких «вспомогательных» столбцов не требуется....

решение3

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

После двух столбцов добавьте третий столбец, определенный как day([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)

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

Связанный контент