지난 시간에 이어서 또 다른 '검색'기능을 가진 엑셀함수에 관해서 알아보겠습니다. "offset"이라는 함수인데요..
이 함수는 어떤 특정 셀을 기준으로 행,열로 검색해서 그 위치의 결과값을 나타내주는 함수입니다.
쉽게 말해서 내가 어떤 곳에 서있는데..그 곳에서 아래쪽으로 몇 걸음 옮기고 옆으로 몇 걸음 옮겼을때 그 위치를 알려주는 검색기능이죠.
엑셀 offset 함수의 구조를 보면 offset(기준값,이동할 행수, 이동할 열수,높이,너비)로 구성되어 있어요.
offset 함수를 알기 쉽게 설명하기 위해서 엑셀 프로그램을 실행한 다음 위와 같은 '영어점수'표를 임의로 만들어 보았습니다^^
만약에 이 표에서 3학년 4반에 있는 5번의 영어점수를 알고자 한다면 어떻게 해야할까요? 물론 해당 값이 그냥 눈에 보여서 누구나 다 알겠지만..
여기서는 offset 함수를 이용해서 결과값을 나타내는 방법에 관한 포스팅이기때문에 offset 함수가 어떻게 사용되는지를 직접 입력해보면서 실제로 알아두는 것이 좋습니다.
빈셀에 =offset를 입력해보면 이 함수가 어떤 함수인지에 관해서 간략한 설명이 메시지로 나타나네요.
'주어진 참조영역으로 부터 지정한 행과 열만큼 떨어진 위치의 참조 영역을 돌려줍니다'라고 나오는데..말이 조금 어렵죠? ㅎㅎ 위에서도 이야기 한 것처럼 이해하시면 쉬워요..
특정 위치를 기준으로 정해진 행,열수 만큼 이동되었을때 그 위치의 결과값을 보여주는 함수가 offset 함수라는 말이죠. 이렇게 머리속에 넣어두시면 나중에도 적용하기가 수월해집니다.
그리고 offset 다음에 괄호 열기를 입력해보면 offset 함수의 구조까지 영어로 표시가 되는데 좌측부터.. 참조(기준위치값),행,열,높이,너비에요.
그런데 여기서 높이,너비는 필요가 없습니다. 결과값이 나타나는 셀의 높이,너비를 지정해주는 수치거든요~ 물론 개별적으로 꼭 필요하다면 수치를 지정해주면 되겠죠^^
offset 함수는 특정위치(행,열) 검색용 엑셀함수
영어점수 표에서 3학년 4반 5번의 영어접수를 구하는 offset 함수는 =offset(G6,4,5)입니다.
여기서 가장 중요한 건 기준위치값이에요~ 예를 들기 위해서 '구분'이라는 글씨가 있는 G6셀 기준 위치값으로 지정했는데..이 부분은 본인이 원하는 부분을 적용하기 나름입니다.
일단 이렇게 기준위치값이 지정되면 그 위치를 기준으로 행수,열수 만큼 이동을 시켜야겠죠. G6셀에서 행수 4만큼 내려가고 열수 5만큼 오른쪽으로 이동하니 결과값이 100으로 나타납니다.
그러고 보면..여기서 특별한 점을 발견할 수 있는데요..기준위치에서 행을 무조건 아래로 이동하고..열은 무조건 오른쪽으로 이동한다는 사실!
그럼 이번에는 예전에 포스팅했던 vlookup함수와 offset 함수를 결합해서 응용하는 방법에 대해서 알아보겠습니다.
사실 엑셀에 있는 함수들은 홀로 사용되는 것보다는 다른 함수와 연계되서 사용될때 보다 효과적으로 사용된다고 하네요~
영어점수표 바로 아래에 3학년 6반까지의 순번이 있는 표를 하나 더 만들어 보았습니다.
그런 다음에 3학년 6반 4번의 영어점수를 vlookup함수와 offset 함수를 결합시키면 위 캡쳐화면처럼 되는데요..그냥 봐도 복잡해 보이죠?^^
offset 함수의 구조에서 행에 해당하는 부분만 vlookup("3학년 6반",G15:H20,2,0)으로 지정해주었는데..이건 열에 지정해도 상관없습니다.
아무튼..vlookup 함수가 사용된 결과값은 '6'이 되겠죠.. 바로 이 결과값이 offset 함수에서 행이동수로 사용되게 됩니다.
그래서 최종 결과값은 G6셀(구분)을 기준으로 6칸 내려가서 오른쪽으로 4칸 옮겨간 95가 되겠네요.
일전에 vlookup 함수를 설명할때와 똑같은 방법인데..저렇게 하면 함수구조가가 복잡해지기 때문에 엑셀에 있는 빈칸을 이용해서 링크로 연결하는 것도 좋은 방법입니다.
임의의 빈칸에 vlookup 함수만 사용해주면 위와 같습니다.
결과값이 6이 나오게 되면 이 셀을 offset 함수의 내부로 링크시켜주면 되겠죠.. offset(기준값,링크값,열수) 이렇게요^^ 다른 함수와의 연결 응용은 이런 방식으로 하면 되구요..
가장 중요한건 엑셀 offset 함수가 어떻게 사용되느냐 입니다.. 초보자라도 이 함수의 기능과 구조만 확실히 이해하면 나중에 충분히 써먹을 수 있으리라 생각되네요. 엑셀 vlookup 함수 조금 쉽게 이해해보기