[Excel] Offset 함수

 

 

오늘은 offset 함수에 대해 설명드리겠습니다.

미리 말씀드릴점은 눈으로 보고 이해를 잘 했다해도 막상 본인이 직접 하려면 쉽지 않다는 점입니다. 어설픈 설명이지만, 백번 보고 이해하는 것보다는 한번 직접 따라해보는것이 그 지식을 익히는데는 더 효과적이라는 것입니다.

 

 

 

 

C2셀에서 행방향으로 3행, 열방향으로 1열 떨어진 곳의 값(365)을 구했습니다.

A사의 3분기 실적입니다.

동일한 결과값을 얻을수 있는 방법이 그 아래의 수식으로 C2셀에서 행방향으로 3행, 열방향으로 1열에서 행과 열방향으로 각각 1열  즉, D5셀 자신을 참조하는 것입니다.

뭐가 다르냐구요?

두번째 방법은 배열문입니다.

 

 

어디에 활용할 수 있을까요?

범위가 계속 업데이트되는 경우에 일일이 함수식을 수정해야 하는 경우가 있는데, 이 경우 유용합니다.

 

 

위에서 B14셀과 G14셀의 계산식은 수정을 하지 않았습니다. 하지만, 테이블의 내용이 추가되었음에도 옳은 계산을 하고 있습니다.

유의하셔야 할 점은 범위를 참조할때는 함수식을 입력한 후 그냥 Enter키가 아니라 Ctrl+Shift+Enter 키를 누르셔야 한다는 점입니다.

 

배열문에 대해 좀 더 알아보겠습니다.

B11, B12 셀은 수식이 위의 셀과 상이합니다.

여느 수식에서 그렇듯이 단일셀을 참조하는 것이 아니고, 특정 범위를 참조하고 있기 때문에 수식에 중괄호가 붙어있습니다. height & width 가 2 & 1인 범위 즉, 노란색으로 하이라이트 되어 있는 35, 46을 참조하는 것입니다.

수식에 입력되어 있는 중괄호는 쳐 넣은 것이 아니라 Ctrl + Shift + Enter 키를 입력하였기 때문에 자동으로 생성된 것입니다.

이 기능으로 뭘 할 수 있을까요.

위의 그림에서 B14 셀의 수식을 보시기 바랍니다.

특정범위의 합(35+46)을 보여주고 있습니다. 이는 Sum 함수를 이용할때 특정범위를 일일이 지정하지 않더라도 offset 함수의 row & column 그리고 height & width 값을 수식을 이용해 응용한다면 다양한 범위의 데이터를 다룰수 있는 것을 보여줍니다.

가령 height 값 대신에 counta 함수를 사용한다면 해당하는 열의 데이터의 수에 따라 배열의 범위가 바뀝니다. 이는 지속적으로 업데이트 되는 데이터의 수를 반영해주므로 에러를 방지할 수 있습니다.

엑셀은 특정 함수의 기능뿐만이 아니라, 다양한 방법으로 응용할때 그 진가가 발휘된다고 합니다. 그 다양한 방법은 개개인이 필요로 하는 해법에 따라 개개인의 응용력에 따라 달라질 수 있겠는데요.

저 역시 탁월한 전문가가 아니라 그저 오래 사용해 본 유저라서 모든 것을 전해드리지는 못합니다.ㅜㅜ

이상입니다.