[엑셀 2016] OFFSET 함수의 활용 및 사용법

 

 

지정된 위치의 범위 또는 셀에 대한 참조를 반환하는 offset 함수에 대해서 알아보겠습니다.

 

◆ 구조

기본적인 구조는 OFFSET(reference, rows, cols, [height], [width]) 으로 이루어져 있습니다.

 

▶Reference(시작 점) : 필수 요소로 기준 점을 지정한다. 셀 또는 범위를 사용한다.

▶rows(행 이동) : 필수 요소로 시작 점에서 아래 또는 위 방향으로 이동하는 행의 수를 나타낸다.

▶cols(열 이동) : 필수 요소로 시작 점에서 왼쪽 또는 오른쪽 방향으로 이동하는 열의 수를 나타낸다.

▶height(행 범위) : 선택 요소로 행과 열로 이동하고 나서 아래 또는 위 방향의 행 범위를 지정한다.

▶width(열 범위) : 선택 요소로 행과 열로 이동하고 나서 왼쪽 또는 오른쪽 방향의 열 범위를 지정한다.

  - height 또는 width를 생략하면 높이나 너비가 reference와 같아 진다.

 

글만 읽어보면 이해하기 어려울 수도 있으나 아래의 사용법을 보면 간단한 원리라는 것을 알 수 있다.

 

◆ 사용

◎서울의 사과 수와 충청도의 바나나 수를 offset을 사용하여 알아보겠습니다.

▶서울의 사과 수 OFFSET(B1,1,1,1,1) : B1에서 시작하여 아래로 한칸 오른쪽으로 한칸 이동하고, 범위는 행으로 한칸 열로 한칸 이라는 의미다.  

  - 시작 점에서 사과는 아래로 한칸 옆에 있으며, 서울은 오른쪽으로 한칸 옆에 있다.

▶충청도의 바나나 수 OFFSET(B1,4,3,1,1) : B1에서 시작하여 아래로 네칸 오른쪽으로 세칸 이동하고, 범위는 행으로 한칸 열로 한칸 이라는 의미다.  

  - 바나나는 시작점에서 아래로 4칸, 충청도는 오른쪽으로 3칸 옆에 있다.

 

▶결과 : 서울의 사과 수 1개가 나오고, 충청도의 바나나 수 3개가 나온다. 참고로 OFFSET(B1,1,1,1,1)과 OFFSET(B1,1,1)는 같은 값이 나온다. 지정하는 시작점의 범위가 행열로 각각 한칸짜리 셀이기 때문이다. 마찬가지로 OFFSET(B1,4,3,1,1)과 OFFSET(B1,4,3)도 같은 값이 나온다.

 

◎이번에는 범위를 지정하여 경기도의 총 과일수와 딸기의 총 수량을 알아보겠습니다.

초록 네모박스와 빨간 네모박스의 합을 각각 구하면 되는 문제입니다. 기본적인 SUM함수를 이용하여 알아보겠습니다.

 

▶경기도의 총 과일수 : 초록색 네모의 범위를 지정하기 위해 OFFSET(B1,1,2,4,1) 수식을 사용했다. B1에서 아래로 한칸 오른쪽으로 두칸 이동하면 D2셀이 되고 D2셀을 기점으로 범위 지정을 아래로 4칸 오른쪽으로 1칸을 범위로 지정했다. 이렇게 하면 초록색 네모칸을 범위로 지정하게 된다. 이후 sum 함수를 이용하여 더해주기만 하면 된다.

 

▶딸기의 총 수량 : 빨간색 네모의 범위를 지정하기 위해 OFFSET(B1,3,1,1,4) 수식을 사용했다. B1에서 아래로 3칸 오른쪽으로 한칸 이동하면 C4셀이 되고 C4셀을 기점으로 범위 지정을 아래로 1칸 오른쪽으로 4칸을 범위로 지정했다. 이렇게 하면  빨간색 네모칸을 범위로 지정하게 된다. 이후 sum 함수를 이용하여 더해주기만 하면 된다.

 

▶결과 : 각각 지정된 범위의 합산 결과가 나오게 된다.

 

 

 

◆ 응용

◎match() 함수와 연계 - 1

▶VLOOKUP(), HLOOKUP()은 행이나 열에서 한가지 조건을 가지고 원하는 값을 찾아내기 때문에 2가지 조건으로는 값을 찾기 어렵다. 하지만 match()와 offset() 함수를 연계하면 쉽게 해결할 수 있다.  

▶ match() 함수는 지정하는 값과 같은 값이 지정된 범위에서 몇 번째 있는지 알려준다.

 

▶과일명과 지역명을 작성하면 교차하는 지점의 값이 나오도록 하는 수식이다.  

- MATCH(I2,B2:B5,0) : 배라는 값이 B2:B5 범위에서 몇 번째 있는지 알려준다. 결과는 2가 된다.  

- MATCH(I3,C1:F1,0) : 전라도라는 값이 C1:F1 범위에서 몇 번째 있는지 알려준다. 결과는 4가 된다.  

- 결국 OFFSET(B1,2,4,1,1) 라는 수식이 완성된다.

 

▶결과 : 배와 전라도의 교차지점인 7이라는 값이 나온다.

 

◎match() 함수와 연계 - 2

▶업무를 하다보면 기존 양식에서 좌측이나 위에도 내용을 추가하는 경우가 있는데 이렇게 연계하게 되면 찾으려는 값을 기준으로 왼쪽 및 위의 값도 찾을 수 있다.

 

▶OFFSET(C1,MATCH(J2,C2:C5,0),-2,1,1)는 시작점에서 match()함수를 이용하여 아래로 3칸, 좌측으로 2칸[-2] 이동하라는 의미다.  - 를 사용하면 좌측으로 이동한다.

▶OFFSET(C1,MATCH(J2,C2:C5,0),-1,1,1)는 시작점에서 match()함수를 이용하여 아래로 3칸, 좌측으로 1칸 이동하라는 의미다.

 

▶결과 : 딸기의 가격과 코드가 나온다.

 

◎간접참조로 활용

▶위에서 만들어 놓은 수식에서 추가로 과일의 리스트를 만들고 각각의 과일에 대한 가격과 코드가 자료와 맞는지 확인을 한다고 생각해보자. 검수를 했다는 것에 대한 확인을 위해 리스트의 과일을 잘라내기 후 과일항목에 붙여넣기 하면 어떻게 될까?

보다시피 에러가 나오게 된다.

 

수식을 아래와 같이 바뀌보자

▶가격 : OFFSET(C1,MATCH(OFFSET(I2,0,1,1,1),C2:C5,0),-2,1,1)

▶코드 : OFFSET(C1,MATCH(OFFSET(I2,0,1,1,1),C2:C5,0),-1,1,1)

수식을 바꾸고 동일하게 진행하면

문제없이 잘 된다.

 

▶가격 수식 변경 전 :OFFSET(C1,MATCH(j2,C2:C5,0),-2,1,1)

▶가격 수식 변경 후 :OFFSET(C1,MATCH(OFFSET(I2,0,1,1,1),C2:C5,0),-2,1,1)

 - match() 함수가 직접 j2를 참조하는 것을 offset()을 이용해서 간접참조를 한 경우다.

 - 코드에 대한 수식도 동일하게 바뀌주면 된다.

 

▶이밖에도 offset() 함수는 범위를 이용하는 다양한 함수에서 응용하여 사용할 수 있고, 유사하게 매크로에서도 사용이 가능하기 때문에 꼭 개념을 이해하고 활용하도록 해보는 게 좋다.