엑셀 Excel 함수 OFFSET 을 이용해서 필요한 영역 반환하기

OFFSET 함수는 첫 번째 인수로 정한 셀을 기준으로 특정 셀의 값과 영역 전체를 반환할 수 있습니다. 셀에서 가로/세로 이동뿐만 아니라 Height 와 Width 를 사용하면 영역 값을 배열 값으로 받는 것이죠. 그래서 OFFSET 과 집계 함수인 SUM, AVERAGE 등을 함께 이용해서 영역의 값을 구할 수 있습니다.

 

 

 

▼ 먼저 옵션 함수의 각 인수에 대해 알아 보겠습니다. 수식 입력창에 OFFSET 을 넣고 함수 인수 대화상자를 띄웁니다. 아래는 인수에 대한 설명입니다.

 

l  Reference : 값을 찾기 위한 시작 위치입니다.

l  Rows : 왼쪽 위에 있는 셀이 참조할 위 또는 아래에 있는 행의 수입니다.

l  Cols : 왼쪽 위에 셀이 참조할 왼쪽 또는 오른쪽에 있는 열의 수입니다.

l  Height : 반환되는 참조의 높이 또는 행의 수 입니다.

l  Width : 반환되는 참조의 너비 또는 열의 수 입니다. 

 

▼ 그림에서 “배” 값을 찾기 위해 인수를 세팅했습니다. Reference 에 지정한 셀로부터 행(Rows) 4칸, 열(Cols) 2칸을 이동하면 찾을 수 있습니다. 그리고 Height = 1, Width = 1을 지정해서 하나의 셀만 가져옵니다.

 

▼ 다음은 영역을 반환하는 방법입니다. 집계 함수를 사용하기 위해 “매입가”에 해당하는 열 값을 가져와 보겠습니다. 매입가의 시작 위치는 Rows 와 Cols 로 찾습니다. 그리고 매입가 영역 전체를 반환하기 위해서는 Height = 12, Width = 1 이 되겠죠. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 나누기 사칙연산 기호, 함수 사용하는 방법 ▶ 엑셀 Excel 가장 기본이 되는 함수 사용법에 알아보기 ▶ 엑셀 Excel 함수 IF 와 AND, OR 를 사용해서 다양하게 표현하기 ▶ 엑셀 Excel 공백을 제거, 특정 문자 삭제 SUBSTITUE 함수 사용 ▶ 엑셀(Excel) 사용자 정의함수를 만들어 사용하기

 

 

 

 

▼ 그런데 Height 와 Width 값이 1보다 크면 여러 건이 반환되기 때문에 그림처럼 에러가 날 수 밖에 없습니다. 이런 경우는 집계 함수를 사용하고 싶을 때 주로 이용합니다. 

 

▼ 집계 함수 중에 합계인 SUM 을 사용해 보겠습니다. OFFSET 으로 얻은 결과값을 모두 합치는 것이죠. 그림처럼 =SUM(OFFSET(B2,1,3,12,1)) 을 이용해서 “매입가” 값을 모두 합쳐 보았습니다. 

 

▼ 그럼 실제로 매입가의 합계가 OFFSET 을 이용해서 영역을 가져온 후 합한 것과 같은지 알아 보겠습니다. 그림과 같이 두 값은 동일합니다. 이상으로 엑셀에서 OFFSET 함수를 사용하는 방법에 대해 알아 보았습니다. 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 나누기 사칙연산 기호, 함수 사용하는 방법 ▶ 엑셀 Excel 가장 기본이 되는 함수 사용법에 알아보기 ▶ 엑셀 Excel 함수 IF 와 AND, OR 를 사용해서 다양하게 표현하기 ▶ 엑셀 Excel 공백을 제거, 특정 문자 삭제 SUBSTITUE 함수 사용 ▶ 엑셀(Excel) 사용자 정의함수를 만들어 사용하기