엑셀 분단위 데이터를 시간단위로 평균내기

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제

 

 

01. 문제

Q. 안녕하세요. 엑셀 초보자입니다. 데이터 분석 중인떼, 1번 사진은 yyyy-mm-dd h-mm 순으로 분단위로 되어있고 2번 사진은 yyyy-mm-dd h 순으로 시간단위로 되어있습니다. 1번 사진에 해당되는 분단위의 데이터가 있는데,  그 데이터를 2번 처럼 시간단위 데이터로 평균을 내고 싶습니다. 예를들어 2번 사진의 2023-01-01 0:00은 1번 사진의 2023-01-01 0:00 ~ 0:59의 평균값으로 나타내는 겁니다. 데이터가 너무 많아서 한번에 처리할 수 있는 방법을 알고 싶습니다.

1번 사진

 

2번 사진

 

문제를 정리하면 다음과 같다.

1. 관측되는 시점의 시간과 함께 기록되는 관측데이터가 있다. 

2. 관측되는 시점의 시간은 분단위까지 기록된다.

3. 별도의 영역에 시간별 관측 데이터의 평균을 내야한다.

 

02. 문제 해결 방법

(1) 같은 구간에 있는 데이터를 식별할 수 있는 값 생성

 

같은 구간에 속한 데이터인지 아닌지를 식별할 수 있는 방법이 필요하다. 

문제에서는 관측되는 시점의 시간 중에서 년, 월, 일, 시간이 일치하는 경우 같은 구간으로 인식한다.

 

즉 주어진 시간에서 분단위 데이터를 날려버리는 작업이 필요하다.

 

필요한 함수는 year, month, day, date, hour, time이다. 

 

year, month, day는 날짜에서 각각의 구성요소를 추출하는 함수다. 자세한 설명은 (4) 관련 포스트를 참고하기 바란다.

date 함수는 year, month, day를 이용하여 날짜 값을 생성해주는 함수다.

hour는 시간에서 시에 해당하는 정보만 추출하는 함수이다.

time 함수는 시, 분, 초를 이용하여 시간을 만드는 함수이다.

 

마지막으로 날짜와 시간을 덧셈연산 하면, 질문자가 사용하는 날짜 시간이 결합된 형태의 데이터를 구할 수 있다.

 

우리가 필요한 것은 날짜 + 시간까지만 채워져있고, 분/초는 0으로 되어있는 데이터이다.

위의 방법으로 구할 수 있다.

 

(2) 중복데이터 제거를 이용하여 구해야되는 통계값 생성

 

질문자는 데이터가 많다고 했는데, 통계를 위해서는 구간을 대표하는 값을 추출할 필요가 있다. 구간을 대표하는 값은 위의 방법으로 구할 수 있으며, 이중에서 중복데이터를 제거하여 구할 수 있다.

 

엑셀의 데이터 메뉴의 중복항목 제거를 이용하여 해당 작업을 할 수 있다.

 

 

(3) COUNTIF 와 SUMIF (사용하는 엑셀이 AVERAGEIF를 지원하면 그것을 사용해도 된다.) 

원하는 구간별로 데이터의 평균을 구하기 위해서는 AVERAGEIF 함수를 사용하면되는데, 구버전 엑셀을 사용하는 사람들은 COUNTIF와 SUMIF를 활용하여 평균을 구할 수 있다.

 

이 함수에 대한 설명은 (4) 관련 포스트를 참고하기 바란다.

 

 

 

(4) 관련 포스트

https://eggdrop.tistory.com/42

SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

https://blog.naver.com/excali/222944383397

엑셀 시간 데이터에서 시,분,초 추출하기

https://blog.naver.com/excali/222942162485

엑셀 시간 관련 함수 (TIME, TIMEVALUE)

https://blog.naver.com/excali/222939492391

엑셀 날짜 관련 함수 (DateValue 함수, Year 함수, Month 함수, Day 함수)

https://eggdrop.tistory.com/30

Excel 매크로 (VBA) - 016. 중복 데이터 제거하기

 

03. 예제

(1) 시트 구성

시트의 구성은 다음과 같다.

 

A열과 B열에는 관측 데이터의 관측 시간, B열에는 데이터가 들어가 있다.

 

C열에는 통계의 기준이 되는 구간 값이 들어갈 예정이다.

 

N열에는 통계를 위한 구간값이 들어갈 예정이다.

 

O열에는 구간별 평균값이 들어갈 예정이다.

 

 

(2) 구간 값 구하기

관측시간에서 분과 초를 제거한 시간값을 구해서 C에 채워보자

 

(2-1) 년, 월, 일, 시간 추출하기

년 추출

 

월 추출

 

일 추출

시간 추출

추출결과

(2-2) 날짜 만들기

위에서 추출한 정보를 이용하여 날짜를 만들어보자.

=DATE(YEAR(A2),MONTH(A2),DAY(A2))+TIME(HOUR(A2),0,0)

 

분, 초가 들어갈 곳에 0을 각각 입력하면 분/초를 제거한 날짜+시간을 만들 수 있다.

셀 A의 형태로 표기하기 위해서는 A열의 셀 서식을 복사하여 C열에 붙여넣던지, C열에서 다음과 같은 셀서식/표기형식을 지정한다.

 

엑셀에서 날짜는 정수로 시간은 소수로 저장된다는 것을 기억하자.

 

 

위의 (2)까지 진행한 값을 채우기 핸들을 이용하여 C열에 모두 복사하자. 다음과 같은 결과를 얻을 수 있을 것이다.

분단위 데이터가 날아가 있는 것을 확인할 수 있다.

 

이제 C열을 모두 복사한 후, 선택하여 붙여넣기 (값+서식)으로 통계값이 있는 N열에 복사하자

 

 

위에 데이터 중에서 중복데이터를 제거해보자.

 

 

선택 영역 확장하지 않고 그대로 중복 제거하면 다음과 같은 데이터만 남게된다.

(이 작업은 수작업으로 해도되지만 질문자가 데이터가 많다고해서 팁을 주는 것이다.)

 

 

 

 

(3) 구간별 평균값 구하기

이제 O열에 구간별 평균을 구해주면 된다.

이 포스트에서는 SUMIF / COUNTIF로 구하려고 한다.

 

=SUMIF($C$2:$C$56,N2,$B$2:$B$56)/COUNTIF($C$2:$C$56,N2)

 

아래 처럼 N열에 해당하는 통계기준에 대한 평균이 구해진다.