엑셀 OFFSET 함수 사용법::어나일언덕

엑셀 글의 매번 시작은 엑셀은 편리하고 유용한 프로그램임을 강조하는 것입니다.

매번 강조하는 것도 지겨우니 오늘은 바로 본론 OFFSET 함수입니다.

OFFSET 함수 사용법

OFFSET 함수는 엑셀의 상대참조 개념을 제대로 이해하고 있다면 쉽게 익힐 수가 있습니다. 상대참조란 어떤 기준점을 잡고 그 기준점으로 부터 얼마만큼 떨어진 셀을 가리키는 것입니다.

엑셀 절대참조 상대참조 차이 절대참조 단축키 링크

엑셀의 OFFSET 관련 도움말을 보면 OFFSET 함수는 주어진 참조 영역으로부터 지정한 행과 열만큼 떨어진 위치의 참조 영역을 돌려줍니다. 라고 합니다. 참조 영역으로 부터 참조 영역을 돌려준다는데 쉽게 이해가 되지 않습니다.

OFFSET 함수는

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

과 같은 구문 형식을 가집니다. 인수를 특이하게 5개나 필요로 하지만 뒤에 두개 [height],[width] 인수는 옵션으로 없어도 오류를 발생시키지는 않으며 각 인수들은 다음과 같은 의미를 지닙니다.

reference : 기본 참조 영역

rows : 기본 참조 영역의 첫 행과 반환할 영역의 첫행 사이의 간격

cols : 기본 참조 영역의 첫 열과 반환할 영역의 첫열 사이의 간격

[height] : 반환할 영역의 행 수(높이)

[width] : 반환할 영역의 열 수(넓이)

위의 표를 예를 들어 설명해보면

reference는 참조할 영역의 첫셀을 지정해 줍니다. 쉽게 말하면 기준점을 잡아준다는 것입니다. C3셀을 지정해주면 다음에 오는 두 인수 rows와 cols의 첫행과 첫열은 C3셀이 기준이 되는 것입니다.

rows는 기본 참조 영역 첫행 3행에서 반환할 영역의 첫행이 얼마만큼 떨어져 있냐를 정해주는 것입니다. 만약 반환 영역의 첫행이 5행이라면 5-3=2로 rows 값은 2가 됩니다.

cols는 마찬가지로 기본 참조 영역의 첫열 C열에서 반환할 영역의 첫열이 얼마만큼 떨어져 있냐를 정해주는 것입니다. 만약 반환 영역의 첫열이 E열이라면 F(6)-C(3)=3으로 cols 값은 3이 됩니다.

지금까지를 정리하면 =OFFSET(C3,2,3)은 C3 셀에서 아래로 2칸, 오른쪽으로 3칸 이동 한 셀 F5의 값 33이 반환됩니다.

그렇다면 여기서 위로나 왼쪽으로는 갈수 없을까요? 물론 당연히 됩니다. 반대로 가는 것이니깐 양수의 반대 음수로 지정을 해주면 되는 것입니다.

G12셀에서 위로 2칸 왼쪽으로 3칸 떨어진 위치의 값을 반환 받고 싶다면 =OFFSET(G12,-2,-3)을 입력하면 E9의 값 27이 반환되게 됩니다.

OFFSET 함수 응용

이제 OFFSET 함수의 나머지 두인수 [height],[width]에 대해 알아보겠습니다.

위의 예시를 이용하여 계속해서 설명하면 이 두 인수 옵션은 쉽게 말해 F5셀을 기준점으로 [height],[width] 만큼의 범위의 값의 배열을 반환하게 됩니다.

[height]는 F5셀에서 행 영역의 범위를 지정 해주는 값이고 [width]는 F5셀에서 열 영역의 범위를 지정해주는 값입니다.

=OFFSET(C3,2,3,2,2)와 같이 입력 하게 되면 C3에서 아래로 2칸, 오른쪽으로 3칸 이동한 셀부터 시작해서 2행 2열의 범위의 F5:G6 값을 반환하게 됩니다. 여기서 중요한 것은 반환하는 것이 배열이므로 그냥 =OFFSET(C3,2,3,2,2)이렇게만 한다면 #VALUE! 오류가 발생하게 되므로 수식을 입력할 때 Enter 대신 Ctrl + Shift + Enter를 눌러 {=OFFSET(C3,2,3,2,2)}와 같이 배열 수식을 만들어 줘야 합니다. 이렇게 하면 셀에는 배열의 첫 값 33이 반환되어 보이게 됩니다.

그러면 머리 아프게 이 짓을 왜 할까요?

예를 들어 만약 C3셀에서 2칸아래 3칸 오른쪽 셀부터 C3셀에서 3칸아래 4칸 오른쪽 셀까지 범위 여기에서는 F5, F6, G5, G6 네 값의 합을 구하려고 한다고 가정하면