포스트 목차
본 포스트에서는 SUMIF 함수, ISOWEEKNUM 함수를 이용하여 주간 평균을 구하는 내용에 대해 다룬다.
01. 질문
| 엑셀 주간 평균 구하는 방법 품목 별/날짜 별로 데이터가 구성되어 있습니다. 품목 별로 주간 평균 구하는 법을 알려주세요. |
주간 평균을 구해야 하는 데이터는 다음과 같다.
문제를 정리하면, 다음과 같다.
- 날짜별로 품목에 대한 수치데이터가 존재한다.
- 주단위로 수치 데이터에 대한 평균값을 구해야 한다.
- 품목별로 주단위 평균이 필요하다.
02. 문제 해결 방법
(1) 특정 조건에 해당하는 데이터만 합하기
여기서는 특정 조건에 맞는 데이터만 합하거나, 평균을 구할 필요가 있다. 주 단위 산술 평균이므로 합을 한 후에 7로 나누면 산술평균을 구할 수 있으므로 본 포스트에서는 데이터의 합을 구하는 함수를 소개하려고 한다.
SUMIF 함수를 사용하면 특정 조건에 해당하는 데이터들의 합을 구할 수 있다.
SUMIF 함수의 인수는 다음과 같다.
- Range: 특정 조건들이 펼쳐져 있는 영역이다.
- Criteria: 특정 조건의 부합 여부를 확인하기 위한 값이다.
- Sum_range: Range 중에서 Criteria를 만족하는 영역 중 실제로 합을 할 수치 데이터가 있는 영역을 의미한다.
간단한 예제로 살펴보자.
위의 그림에서 참외의 과실 수만 구해서 D2셀에 채워보자.
다음과 같은 수식으로 이 문제를 해결하는 것이 가능하다.
=SUMIF(A2:A8,D1,B2:B8)
SUMIF 함수의 첫 번째 인자는 과일의 종류가 나열되어 있는 A열을 입력한다.
우리는 이중에서 참외에만 관심이 있기 때문에 참외라는 값이 있는 D1셀을 두 번째 인수로 입력한다.
마지막으로 합 연산을 수행해야 하는 데이터는 B열에 있기 때문에 B열을 3번째 인수로 입력한다.
참외를 실제로 합친 값이 53이 정상적으로 출력되는 것을 확인할 수 있다.
(2) 날짜에서 주차를 가져오기
다시 데이터로 돌아가서 A열을 살펴보면 날짜에 대한 정보를 확인할 수 있다. 우리가 지금 원하는 것을 주간 평균이기 때문에 날짜에서 “같은 한 주”로 취급될 수 있는 데이터를 추출해야 한다.
여러가지 방법을 사용할 수 있겠지만 본 포스트에서는 WEEKNUM 함수와 ISOWEEKNUM 함수를 소개하려고 한다.
두 함수는 주의 시작을 어떤 요일로 할 것인지 에 대한 차이 외에는 거의 비슷하다고 보면 된다.
다음 작업을 수행하면 주차 정보를 추출하는 것이 가능하다.
- A열 데이터가 날짜 인지 확인하기
- 날짜에서 함수를 사용하여 주차 정보를 추출하기
데이터가 엑셀에서 날짜로 취급 되는 지를 확인하자. 날짜가 아니라 텍스트라면 날짜로 변경 해야 할 필요가 있다.
WEEKNUM 함수 와 ISOWEEKNUM 함수
두 함수 모두 인수로 날짜를 입력 받는다.
해당 날짜가 해당 년도에서 몇 번째 주에 해당하는 지를 반환한다.
위의 예제를 살펴보면 WEEKNUM 함수는 일요일을 주의 시작으로 간주하고 ISOWEEKNUM함수는 월요일을 주의 시작으로 간주한다는 것을 알 수 있다.
03. 예제
(1) 시트 구성
시트 구성은 다음과 같다.
집계할 대상이 되는 데이터는 A~ F열에 위치한다.
계산을 쉽게 하기 위해 ISOWEEKNUM 을 이용한 주의 번호가 M열에 나열된다.
주별 평균을 계산한 데이터는 O열과 T열 사이에 위치한다.
(2) 주간평균 구하기
이 예제에서는 ISOWEEKNUM을 이용하여 주간 평균을 구하려고 한다.
= ISOWEEKNUM(A2)를 M2셀에 입력하여, 주의 번호를 구한다.
데이터는 18주 부터 22주까지 있기 때문에, O열에 18~22까지의 값을 채운다.
포도부터 다음과 같은 수식을 이용하여 주간 평균을 계산해보자.
=SUMIF($M$2:$M$32,$O2,B$2:B$32)/7
SUMIF 함수의 첫 번째 인수는 $M$2:$M$32를 입력 했는데, 우리는 18주차에 해당하는 데이터만 합쳐야 하므로 주차정보(주의 번호)가 저장되어 있는 M열을 입력해야 한다. 또한 우리는 P2셀에 입력된 수식을 열방향과 행방향으로 모두 복사할 예정이기 때문에 열과 행에 대한 절대주소를 사용해야 한다.
SUMIF 함수의 두 번째 인수는 $O2를 입력 했는데 , 주수 정보가 O열에 있기 때문이고, 해당 수식을 참외, 수박 방향으로 복사하더라도 O열이 선택되어 야 하기 때문에 열에 대해서 만 절대주소를 사용한다. 행은 19~22까지 변경돼야 하기 때문에 상대주소를 사용한다.
SUMIF 함수의 세 번째 인수는 B$2:B$32를 사용했는데, 해당 수식을 참외, 수박 방향으로 복사하면 이 부분의 주소도 같이 움직여야 하므로 열에 대해서는 상대주소를 사용한다. 행 19~22로 이동하더라도 B2:B32가 선택돼야 하기 때문에 행에 대해서는 절대주소를 사용한다.
포도의 18주차 평균이 계산되었다. 위의 수식을 P2:T6까지 복사해주자.
위와 같은 결과를 얻을 수 있다.
본 포스트는 SUMIF 함수, ISOWEEKNUM 함수를 이용하여 주간 평균을 구하는 내용에 대해 다뤘다.