☰

Excel에서 여러 열을 기준으로 'SUM'을 수행하는 방법

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Excel에서 여러 열을 기준으로 'SUM'을 수행하는 방법

두 개의 날짜/시간 열(Excel의 사용자 지정 형식으로 가져옴)과 상태 1 또는 0을 포함하는 쉼표로 구분된 파일이 있습니다. mm/dd/yyyy hh:mm상태는 켜져 있거나 꺼져 있는 장비를 나타냅니다. 매일 시간 증가와 감소를 표시하는 그래프를 생성하려고 합니다.

고려하다:

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

이는 장비가 3시간 동안 가동되었다가 11시간 동안 가동 중단된 다음 34시간 동안(역일 기준 2일 동안) 가동되었음을 알려줍니다. 그러나 나는 우리가 하루에 몇 시간 동안 상승 또는 하락했는지 보여주는 그래프를 생성하고 싶습니다.

고려하다:

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

SUM(IF(SUMIF(...)))나에게는 HOURS, STATUS, CALENDAR DAY를 합산하는 데이터 세트를 생성해야 하는 것 같습니다. 하지만 피벗 테이블이나 중첩 조합 의 특징을 찾을 수 없는 것 같습니다 .

가장 문제가 되는 것은 날짜 변경을 설명하는 것입니다. 위의 예에서 2012년 1월 1일 14:00에 시작된 가동 시간이 자정을 지났기 때문에 가동 시간 10시간이 2012년 1월 1일과 24시간으로 합산된다는 것을 알아야 합니다. 가동 시간은 2012년 1월 2일까지 합산됩니다.

날짜 합계를 계산하기 위해 달력 목록을 사용하여 작업을 수행할 수 있지만 같은 경우와 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과 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 열의 수식은 상태 = 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시간의 일부)을 나타냅니다. 예를 들어, 는 (자정을 지난 18시간은 18/24, 즉 0.75) 06/01/2012 18:00과 같습니다 .41061.75나는 이것이 당신의 마지막 문단에 대한 답이라고 믿습니다.

시간 통합

여기에 공식이 나와 있습니다.사용 시간열(아래 이미지 참조) 배열 수식이므로 Ctrl+ Shift+를 사용하여 입력 Enter한 다음 복사해야 합니다.

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

에서영업시간열:

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


stamps다음을 참조하는 명명된 범위는 어디에 있습니까?타임 스탬프범위(열ㅏ내 예에서는)는
hours_on다음을 참조하는 명명된 범위입니다.에범위(열씨)는
hours off 다음을 참조하는 명명된 범위입니다.끄다범위(열디)

차트

각 날짜에 대해 ON 및 OFF 시간의 합이 24가 되는 것을 확인하세요.

여기에 이미지 설명을 입력하세요

공식과 통합 문서를 연구하고 싶다면 다음 사본을 참조하세요.http://db.tt/KZgH7SFV

답변2

Kaze의 제안에 따라 A3:A24에 타임스탬프가 있고 B3:B24에 상태가 있다고 가정하면 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의 합계가 표시됩니다. 해당 월의 각 날짜에 대해 해당 공식을 반복합니다(내가 나는 그다지 열광하지 않습니다), 당신은 당신의 가치관을 가져야합니다.

매월 1일의 시간 합계

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

매월 2일의 시간 합계

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

매월 3일의 시간 합계

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

등등...

관련 정보