☰

Excel - 30분 간격마다 연속 데이터 합계

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 30분 간격마다 연속 데이터 합계

다음 형식으로 우량계에서 수집된 데이터가 있습니다.

인덱스 데이터 타임스탬프

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T01:00:00
3. 1    2020-01-05T01:20:15
4. 0    2020-01-05T02:00:00
5. 1    2020-01-05T02:09:00
6. 1    2020-01-05T02:09:45
7. 1    2020-01-05T02:20:00
8. 1    2020-01-05T02:20:01
9. 0    2020-01-05T03:00:00

30분마다 연속된 데이터를 합산하고 싶습니다. 다음과 같은 것 :

인덱스 데이터 타임스탬프

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T00:30:00
3. 0    2020-01-05T01:00:00
4. 1    2020-01-05T01:30:00
5. 0    2020-01-05T02:00:00
6. 4    2020-01-05T02:30:00
7. 0    2020-01-05T03:00:00

나는 이미 타임스탬프에서 24시간 시간을 얻었고 다음 공식에 따라 2개의 타임스탬프를 빼는 것이 30분 미만인지 여부를 연습해 보았습니다.

=IF(((time)-(time-1))<=0.020833,"ok","not within 30min interval")

SUMIFS내가 염두에 둔 Excel 공식은 30분 간격 내에 모든 연속 값을 합하는 함수 라인을 따라 뭔가를 얻는 것이었습니다.

30분 간격으로 연속된 데이터를 확인하기 위한 일종의 장치를 마련하려고 노력했고 a 와 함수를 COUNTIF해결하려고 시도했지만 모든 것을 하나로 묶고 논리를 맞추는 데 어려움을 겪고 있습니다.SUMIFSSUMPRODUCT

감사합니다

답변1

다음 공식을 사용할 수 있습니다.

=DATEVALUE(SUBSTITUTE(C2,"T"," "))+CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")

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

다음 단계로 분류됩니다.

  1. Excel에서 텍스트를 날짜/시간으로 인식하도록 하려면 T:를 제거하세요.

    =대체(C2,"T"," ")

  2. 해당 날짜/시간에서 날짜를 얻으려면 다음을 수행하십시오.

    =날짜값(D2)

  3. 해당 날짜/시간에서 시간을 얻으려면 다음을 수행하십시오.

    =ROUND(시간값(D2),5)

(여기서 ROUND가 필요한 이유는 좀 더 아래에서 설명하겠습니다)

  1. 다음 30분 증분을 계산하려면:

    =CEILING.MATH(ROUND(TIMEVALUE(대체(C2,"T"," ")),5),"0:30")

그런 다음 날짜를 30분 단위로 연결하면 답변 상단에 수식이 표시됩니다. 그런 다음 테이블을 피벗하여 증분만큼 값을 합산할 수 있습니다.

3단계의 ROUND 호출에 대해 참고하세요.

처음 수식을 작성할 때 3단계에서 ROUND를 사용하지 않았는데, 2:00:00에 대한 증가분이 2:30:00으로 나오는 것을 발견했는데, 이는 맞지 않았습니다. 검사 결과 TIMEVALUE 함수가 올바른 정확도 수준을 반환하지 않는 것으로 나타났습니다. 소수점 이하 5자리로 반올림하여 문제가 해결되었습니다.

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

편집하다:

Excel 365가 있으므로 SEQUENCE 기능에도 액세스할 수 있다고 가정합니다.

피벗 테이블 없이 집계하려면 다음을 수행할 수 있습니다(기존 증분 목록이 없다고 가정).

  1. MIN 타임스탬프 그룹과 MAX 타임스탬프 그룹 사이의 30분 증분 수를 계산합니다.
  2. 해당만큼의 증분을 포함하는 시퀀스를 만듭니다(소스 데이터에 없는 증분도 표시되도록).
  3. SUMIF를 사용하여 원본 데이터를 다시 참조하고 TimestampGroup별로 데이터를 합산합니다.
  4. 선택적으로 집계된 테이블에 대한 GroupIndex를 생성합니다.

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

집계된 표의 수식은 다음과 같습니다.

그룹 인덱스:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1)

이는 가장 작은 TimestampGroup과 가장 큰 TimestampGroup 사이에 30분 단위의 증분 수를 알려주고 해당 증분에 대한 정수 시퀀스를 반환합니다.

타임스탬프 그룹:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1,1,MIN($D$3:$D$11),1/48)

다시, 시퀀스 함수를 사용하여 TimestampGroups 목록을 반환합니다. 그러나 기본 3번째 및 4번째 매개변수인 1에서 시퀀스를 시작하고 1씩 증가하는 대신 min TimestampGroup에서 시퀀스를 시작하고 30분씩 증가합니다. 하루의 1/48.

값:

=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)

집계된 테이블의 현재 행에서 TimestampGroup으로 표시된 행의 데이터 열을 합산합니다.

관련 정보