[엑셀] 엑셀의 네비게이션 OFFSET 함수

엑셀 시트는 행과 열로 무수히 많은 셀이 존재합니다. 2016버전의 경우 16384 열 × 1048576 행의 셀이 존재하며 아주 많은 데이터를 담을 수 있습니다.

이렇게 많은 데이터를 활용하는 경우 필요한 함수가 바로 찾기/참조 영역 함수 입니다.

그중에서 OFFSET 함수는 제목대로 자동차 네비게이션과 같은 역할을 합니다.

내가 서 있는 곳에서 도착지에 데려다 주는 기능을 하니깐요!

여기서 도착지는 지점(1개의 셀)이 될수 있고 영역(범위)가 될 수 있습니다.

OFFSET 함수 설명

범주 : 찾기/참조 영역 함수

주어진 참조 영역으로 부터 지정한 행과 열만큼 떨어진 위치의 참조 영역으 돌려 줍니다.

☞ 수식

OFFSET(reference, rows, cols, [height], [width])

OFFSET(참조영역, 행수, 열수, [행 높이], [행 너비])

☞ OFFSET의 인수

reference(참조영역)  :  필수 요소. 오프셀 할 기준 위치라고 보시면 됩니다. 셀 1개 또는 영역을 지정 할 수 있습니다. 경우에 따라 오류값이 반환되며 아래에서 확인 바랍니다.

rows(행수) :  필수 요소.  기준위치 즉 reference에서 행(위, 아래)으로 건너 뛸 수 입니다.

cols(열수) :  필수 요소.  기준위치 즉 reference에서 열(좌, 우)로 건너 뛸 수 입니다.

[height](행 높이) :  선택 요소. 반환되는 참조의 높이(단위: 행 수)입니다. 양수이며 생략시 1이 됩니다.

[width](행 너비) : 선택 요소. 반환되는 참조의 너비(단위: 열 수)입니다. 양수여며 생략시 1이 됩니다.

OFFSET(reference, rows, cols) Type

[height]와 [width]생략된 타입입니다. 그래서 height = 1,  width = 1 이 됩니다.

수식과 같이  참조영역(B3) 즉 구분에서 행(아래)으로 4칸, 열(우측)으로 3칸 떨어진 위치의 값을 반환 합니다.

OFFSET(reference, rows, cols) Type

위와 같은 타입이지만 참조영역에 변화가 있습니다. 그리고 열방향으로도 양수가 아닌 음수(-)로 이동한 타입입니다.

참조영역은 E3셀 즉 3분기가 되고 행(아래)으로 3칸, 열(좌측)으로 1칸 떨어진 위치의 값을 반환 합니다.

여기서 열방향으로 좌측으로 이동하는 것은 값이 음수(-) 이기 때문입니다.

SUM(OFFSET(reference, rows, cols, [height], [width])) Type

OFFSET(reference, rows, cols) 으로 떨어진 지점에서 다시 행높이와 열너비의 영역을 가지는 타입 입니다.

참조역역 B3셀(구분)에서 행(아래)으로 3칸, 열(우측)으로 2칸 이동한 지점에서 다시 행(아래)으로 2칸, 열(우측)으로 2칸의 영역을 반환 합니다.

다만, 아래에서도 설명하겠지만 단순히 영역을 반환되는 경우는 #VALUE!의 오류값이 반환됩니다.

그래서 SUM 함수와 같이 사용하면 OFFSET 함수에서 반환된 영역의 합을 나타냅니다.

AVERAGE(OFFSET(reference, rows, cols, [height], [width])) Type

앞서 설명한 SUM 함수와 같이 사용한것과 내용은 같습니다. SUM 함수 대신 EVERAGE 함수를 사용하였으며 이렇게 참조영역을 인수로 가지는 함수에서 사용 할 수 있습니다.

SUM(OFFSET(reference, rows, cols, [height], [width])) Type

앞에서 본 SUM 함수와 활용 방법은 같지만 참조영역이 1개의 셀이 아닌 범위로 선택된 경우 입니다.

범위의 첫지점(좌측 상단)이 기준이 됩니다.

참조영역이 범위로 지정 되었지만 첫지점은 좌측상단 C4셀이 되며 C4셀에서 행(아래)으로 3칸, 열(우측)로 1칸 이동한 지점에서 다시 행 1칸, 열2칸의 범위를 합한 결과 입니다.

SUM(OFFSET(reference, rows, cols)) Type

SUM(OFFSET(reference, rows, cols, [height], [width])) Type과 달리 참조영역의 범위가 행(아래)으로 3칸, 열(우측)로 2칸 이동 한것을 할 수 있습니다.

오류값 반환

OFFSET 함수 단독으로 사용할 경우 오류 유형입니다.

관련글 보기

VLOOKUP 함수

INDEX 함수

인사카드 만들기(카메라 기능)

첨부파일

OFFSET 함수.xlsx