(부제 : OFFSET 함수 사용 및 활용법, vlookup/index/match 함수와 차이점)
최근 엑셀로 인한 자동화작업이 많아지면서 참조 함수를 많이 사용을 하게 되었습니다.
참조 함수로는
가장 기본적인 것이 #vlookup 함수가 있고
그 다음에는 #index, #match 함수가 있습니다.
이러한 참조 함수에서 상위의 버전이 #offset 함수입니다.
그러면 offset함수에 대해서 기본 사용법에 대해 알아보고
이를 어떻게 활용을 하는지, 그리고 언제 사용하는지,
다른 참조 함수와는 차이점은 무엇인지 알아보도록 하겠습니다.
(vlookup, index, match 함수)
#offset함수사용법
offset 함수는 특정 셀을 선택하거나 범위를 지정할 때 사용하는 함수입니다.
이 중에서도 특히, 범위를 지정할 때 많이 사용합니다.
함수 구조는
OFFSET(reference, rows, cols, [height], [width])
으로 이루어져 있습니다.
좀 더 간단하게 한글로 표현을 하자면
OFFSET(기준, 이동행, 이동열, 범위행, 범위열)
입니다.
위의 그림을 보면
A5 셀의 “일자”를 가지고 오려면
=OFFSET(A5,0,0,1,1)
이라고 함수를 넣으면 됩니다.
① A5 셀 기준으로 (시작으로)
② 행(아래) 이동이 없으며
③ 열(오른쪽) 이동이 없고
④ 행(아래)로 범위는 1개 (자기자신)
⑤ 열(오른쪽)으로 범위도 1개 (자기자신)
이기 때문에 함수를 위와 같이 넣으면 됩니다.
서울의 20년 1월 24일자 확진자수를 가지고 오려면
=OFFSET(A5,5,2,1,1)
이라고 함수를 넣으면 됩니다.
① A5 셀 기준으로 (시작으로)
② 행(아래) 이동이 5번이고
③ 열(오른쪽) 이동이 2번이고
④ 행(아래)로 범위는 1개 (자기자신)
⑤ 열(오른쪽)으로 범위도 1개 (자기자신)
이기 때문에 함수를 위와 같이 넣으면 됩니다.
숫자가 늘어가는 것에 대해 명확하게 이해가 필요합니다.
※ 이동에서 마이너스(-)인 경우에는 행(위), 열(왼쪽)으로 이동합니다.
범위를 가져오는 것을 연습하면
1) 빨간색 테두리 데이터 가져오기</h4
나머지는 다 동일하고 범위에서만
행(아래)로만 8개를 가져오기 때문에
=OFFSET(A5,11,2,8,1)
로 표현을 하면 됩니다.
2) 파란색 테두리 데이터 가져오기</h4
범위가
행(아래)로는 2개
열(오른쪽)으로는 5개를 가져오기 때문에
=OFFSET(A5,16,6,2,5)
로 표현을 하면 됩니다.
#offset함수활용법
OFFSET 함수의 특징은 내가 원하는(조건) 자료를 찾는데(참조하는데)
있어서 특정값 뿐만 아니라 특정범위까지 찾는게 가능하다는 점입니다.
특정값을 찾는 방법은 vlookup 함수나 index 및 match 함수로도 가능하지만
범위를 찾는 것은 offset으로 좀 더 수월하게 가능합니다.
※ vlookup 함수, index 및 match 함수 설명은 아래 컨텐츠를 참고 부탁드립니다.
바로가기 – 클릭 : 엑셀 VLOOKUP 함수 설명 및 사용팁 (INDEX 및 MATCH 함수로 대체 방법)
예제를 통해서 알아보겠습니다.
위의 그림과 같이
W723의 노란색 음영의 기준일을 넣으면 자동으로 아래의 서울과 경기의 데이터가 바뀔 수 있도록 자동화를 하고 싶다면 어떻게 진행을 하면 될까요?
조건은 기준일 기준 이전 일주일간의 평균값입니다.
서울 옆의 W724에는 아래와 같이 함수가 들어가면 됩니다.
=AVERAGE(OFFSET($A$5,MATCH($W$723,$A$5:$A$1235,0)-1,MATCH($V724,$A$5:$T$5,0)-1,-7,1))
상당히 복잡해보이지만 차근차근 제외하면 명확하게 보일 겁니다.
① 평균값을 구하는 것이니깐 가장 바깥에는 AVERAGE 함수가 들어가 있습니다.
그러면 7일간의 범위를 나타낸 함수는 AVERAGE 함수 안쪽의 아래의 함수입니다.
OFFSET($A$5,MATCH($W$723,$A$5:$A$1235,0)-1,MATCH($V724,$A$5:$T$5,0)-1,-7,1)
② OFFSET 함수의 구조는 OFFSET(기준, 이동행, 이동열, 범위행, 범위열) 이니깐 하나씩 살펴보면
* 기준 : $A$5
→ 시작점은 A5 입니다. 함수가 복사가 되어도 해당 시작점은 변경이 없으므로 $ 표시를 합니다.
* 이동행 : MATCH($W$723,$A$5:$A$1235,0)-1
→ 넣는 기준일($W$723)이 $A$5:$A$1235의 범위에서 몇번째인지를 알아야하므로
→ MATCH 함수를 사용합니다.
→ 이동행에서 자기자신은 “0”값이니깐
→ MATCH 함수에서 나온 값에서 “-1″을 해줍니다.
* 이동열 : MATCH($V724,$A$5:$T$5,0)-1
→ 지역($V724)이 $A$5:$T$5의 범위에서 몇번째인지를 알아야하므로
→ MATCH 함수를 사용합니다.
→ 이동열에서도 자기자신은 “0”값이니깐
→ MATCH 함수에서 나온 값에서 “-1″을 해줍니다.
* 범위행,: -7
→ 이전 7일이니깐 “마이너스(-)를 붙여서 “-7” 값을 넣어줍니다.
* 범위열 : 1
→ 열의 변화는 없으므로 “1” 값을 넣어줍니다.
위와 같이 OFFSET 함수와 다른 함수를 활용해서
특정 조건에 따른 값(범위)을 추출하는데 수월하게 함수를 만들 수 있습니다.
해당 함수를 명확하게 인식하고, 다양하게 활용할 수 있는데 도움이 되시길 바랍니다.