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

엑셀(Excel)/Excel

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

 

환경: Microsoft Excel 2013

 

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

 

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

 

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

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

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

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

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

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

 

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

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

 

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

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

 

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

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

 

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

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

 

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

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


저작자 표시 비영리 변경 금지
신고
Posted by 녹두장군