엑셀(Excel) Offset 함수 사용법과 응용방법

Excel 함수 중 잘은 안 쓰이지만 그래도 가끔씩 쓸 대가 있는 함수인 Offset 함수에 대해 포스팅해 보도록 하겠습니다.

 

 

  Offset 함수는 기본적으로 Indirect 함수와 비슷한 용도로 많이 쓰이는데 보고용 자료를 만들거나 누구에게 보기 편하게 자료를 보여줄 때 많이 쓰이는 함수입니다.

 

엑셀 예시

 

  위의 예시를 보면 데이터가 D열부터 있는데 실제로 18년 1월부터 22년 12월까지 하면 옆으로 셀이 엄청나게 길어질 수 밖에 없습니다. 이때 A, B열에 Offset 함수를 이용해서 붉은 음영 부분의 숫자만 치면 바로 해당월의 값을 가져올 수 있게 하는 방법입니다. 이러면 화면을 복잡하게 움직이지 않고도 바로 값을 확일 할 수 있기 때문입니다.

 

 바로 수식부로 넘어가면 아래처럼 정의 할 수 있습니다.

 

=offset(기준셀 선택,아래로 몇칸내려갈지, 우측으로 몇칸갈지)

 

사실 수식을 보더라도 아직 이해가 안되실 텐데, 예시로 설명해보도록 하겠습니다. 

 

엑셀 예시

 

내가 만약 12월의 값을 가져오고 싶다고 한다면, 수식은 아래와 같습니다.

 

=OFFSET(C2,0,A1-1)

 

  수식부의 C2는 기준점을 정하는 것으로 18년 1월 값인 10을 지정하고, 다음은 몇칸을 내려가겠느냐는 지정인데 0칸을 움직이기 때문에 행을 내리지 않고 지금 행에서 움직이라는 것이며, 3번째 몇칸을 우측으로 가겠냐는 것은 A1셀을 지정했기 때문에 12칸을 가겠다는 의미입니다. 다만 12칸을 우측으로 가면 12월을 넘어 13월의 수치를 가리크기 때문에 11칸을 움직이라고 하면 12월의 값을 불러옵니다.

 

 

  근데 사실 이런 함수만 보면 뭐하러 이 함수를 쓰느냐 생각하실 분들이 많으실 텐데, 이 함수의 진가는 SUM함수와 연계했을때 강력한 기능을 보여줍니다. 

 

엑셀 예시

 

  다시 위의 예시를 확장하면 만약 내가 1월부터 12월까지 전체의 합을 보고 싶다라고 한다면 1월부터 12월까지 Sum을 하면 되겠지만, 만약 2~4월이나 3~7월 등 분기단위 숫자도 아닌 값들의 합을 바로바로 보고싶다라고 한다면 이 함수를 사용해서 A1,B1의 범위만 바꿔주면 바로바로 합계가 계산되서 표현됩니다.

 

  위의 예시에서는 A1,B1을 조건으로 하는 함수를 만들면 다음과 같습니다.

 

=SUM(OFFSET(D2,0,A1-1):OFFSET(D2,0,B1-1))

 

 Sum함수를 만들고 그 안에 Offset 함수로 시작지점과 끝지점을 A1,B1셀에 연동시키면 어떠한 월간 합계라도 쉽게 볼 수가 있습니다.

 

 이정도만 하실 수 있으면 Offset 함수는 다 이해했다고 보셔도 무방합니다. 그 뒤에부터는 응용과정인데, 18년 뿐만이 아니라 뒤로 19년 20년 등 계속해서 월별로 자료를 붙여 나가고, 년도을 드랍다운 목록에서 선택하게 하여 셀 참조값을 indirect 함수와 Vlookup 함수를 이용하면  A1,B1셀 두개로 년도 상관없이 데이터를 보여줄 수 있습니다.