엑셀에서 OFFSET 함수는 특정 셀 범위에 있는 데이터를 반환합니다. 하나의 셀을 값을 가져오기도 하지만 영역 전체를 반환할 수도 있습니다. 범위를 지정하는 방법은 첫 번째 인수로 넘긴 셀을 기준으로 행과 열의 개수만큼 이동한 뒤 지정한 높이와 너비를 측정 합니다. 장기판에 말 움직이듯이 영역을 찾는다고 생각하시면 쉽게 이해가 가실 겁니다. ▼ 먼저 OFFSET 함수를 사용해서 구간 영역을 가져와 보겠습니다. OFFSET 함수에는 총 5가지의 인수가 있습니다. 첫 번째 Reference 는 기준점이 됩니다. 기준점이 되는 셀에서 Rows 와 Cols 개수만큼 가로와 세로로 이동합니다. 그리고 Height 와 Width 영역만큼 셀을 지정하는 것이죠. Height=3 과 Width=1 은 세로 방향 3개 셀을 의미합니다. ▼ 위에서 OFFSET 함수에 세팅한 결과값은 그림과 같습니다. 3개의 셀을 반환하게 되는 것이죠. SUM 같이 인수로 영역을 받는 함수가 아니라면 에러가 나겠죠. ▼ OFFSET 함수의 사용법을 알았으니 시작과 종료 값을 이용해서 구간별 통계값을 구할 수 있겠죠. OFFSET 함수를 이용해서 시작이 6이고 종료가 10 인 영역의 합산을 구해 구간별 취업자수에 표시할 것입니다. ▼ OFFSET 함수에서 Reference 는 취업자 열의 시작이 됩니다. Rows 는 “시작” 값이 됩니다. 행의 이동은 없기 때문에 Cols 는 0 이 됩니다. Height 는 6과 10 사이의 높이 이므로 “종료” - “시작” + 1 이 됩니다. ▼ 아래 수식처럼 SUM 함수와 OFFSET 를 같이 사용하시면 간단하게 구간별 통계를 구할 수 있습니다. 시작과 종료 값은 자신이 원하는 값으로 바꿀 때 마다 해당하는 영역의 값을 가져오겠죠. =SUM(OFFSET(C3,F3-1,0,G3-F3 + 1,1)) 엑셀에서 AVERAGE 는 평균을 구하는 함수입니다. 이 외에도 AVERAGE 에 기능을 더해서 만든 함수들이 있습니다. 그것은 바로 AVERAGEIF 와 AVERAGEIFS 함수입니다. IF 와 IFS 가 붙었다는 것은 영어 단어에서 의미하듯 조건에 해당하는 값의 평균을 구할 수 있다는 뜻입니다. 다시 말해서 AVERAGEIF 는 한가지 조건에 맞는 데이터의 평균을 구하는 함수이며, AVERAGEIFS 는 여러 조건에 해당하는 값의 평균을 구하는데 사용합니다. ▼ AVERAGEIF 함수 구문과 인수로 사용되는 값들은 다음과 같습니다. AVERAGEIF(range, criteria, [average_range]) ▼ AVERAGEIF 을 사용하는데 주의할 사항은 다음과 같습니다. 주의사항 ▼ 먼저 AVERAGEIF 함수를 알아 보겠습니다. 처음에 소개했듯이 IF 가 붙은 것은 하나의 조건에 해당하는 값의 평균을 구할 수 있다는 의미입니다. 샘플에서는 “품목” 이 “포도”인 제품의 “판매이익금”을 구하는 것입니다. ▼ AVERAGEIF 의 함수 형태는 다음과 같습니다. 함수 인수는 3개 입니다. Range는 조건 영역이 들어갑니다. 그리고 Criteria 는 Range 영역에서 값을 걸러내기 위한 조건이 됩니다. 품목에서 포도만 걸러내야 하므로 Criteria 는 “포도” 가 되겠죠. 마지막으로 Average_range는 실제 합계를 구할 영역이 들어가야 합니다. “판매이익금” 영역이 되겠죠. ▼ 함수를 적용한 결과는 다음과 같습니다. “판매이익금” 은 총 166,320 으로 나왔네요. 그럼 조건이 여러 개라면 어떻게 해야 할까요? 그러니까 “품목” 뿐만 아니라 “판매량”에서 일정 개수 이상 판매된 포도의 판매이익금을 걸러 내는 것이죠. =AVERAGEIF(C3:C14,"포도",G3:G14) ◎ AVERAGEIFS 다중 조건 함수 사용하기 ▼ AVERAGEIFS 함수 구문과 인수로 사용되는 값들은 다음과 같습니다. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) ▼ 만약 평균을 구하는 조건이 하나가 아닌 두 개 이상이라면 AVERAGEIFS 함수를 사용해야 합니다. AVERAGEIF와 다른 점은 끝에 “S” 가 붙었다는 것입니다. 함수 인수의 형태는 약간 다릅니다. Average_range는 AVERAGEIF 의 Average_range 와 같이 평균 값을 구할 영역이 들어갑니다. 두 번째와 세 번째 인수인 Criteria_range1, Criteria1한 쌍입니다. Criteria_range1 은 값을 걸러 낼 조건 영역이 들어갑니다. Criteria1 은 조건 연산자가 들어가겠죠. 조건이 늘어나면 Criteria_range2, Criteria2 …. 처럼 입력 박스가 증가합니다. ▼ 다중 조건 평균 함수인 AVERAGEIFS 의 결과는 다음과 같습니다. 첫 번째 조건은 “품목” 이 포도이며, 두 번째 조건이 판매량에서 300개 이상 팔린 것만 걸러내는 것입니다. =AVERAGEIFS(G3:G14,C3:C14,"포도",F3:F14,">300") |