엑셀 함수 기초 14 - 범위 함수 OFFSET 사용법 및 응용 정리

안녕하세요. 푸른별빛입니다.

오늘은 엑셀 함수 중 OFFSET 함수에 대해 자세히 알아보려 합니다. OFFSET 함수는 범위를 지정해주는 함수로 범위 내의 값이 바뀌면 함수값 역시 자동으로 변하게 됩니다. 다만 단순히 범위만 지정하는 용도 이상의 기능이 없어 OFFSET 함수 단독으로는 잘 쓰진 않고, 다른 함수 또는 기능과 연계하여 사용하는 것이 보통입니다. OFFSET 함수의 사용 방법 및 응용 방법을 같이 소개해 드릴게요.

 

OFFSET 함수

OFFSET 함수는 한 셀을 기준으로 조건을 부여하여 특정 범위를 지정하는 목적을 가진 함수입니다. OFFSET를 적용하는 방법은 다음의 공식대로 입력하시면 됩니다

 

=OFFSET(A,B,C,D,E)

# A : 기준이 되는 셀

# B : 기준이 되는 셀로부터 몇 칸 아래에서 시작할 지에 대한 값

# C : 기준이 되는 셀로부터 몇 칸 오른쪽에서 시작할 지에 대한 값

# D : 몇 줄을 범위로 잡을 지에 대한 값

# E : 몇 칸을 범위로 잡을 지에 대한 값

 

말로 풀어서 적어놓았지만 여전히 어렵죠? 예시를 보여드릴게요.

 

 

캡처한 엑셀 파일에는 7개의 도시와 각 도시에 해당하는 7개의 값이 적혀있죠. 저는 여기서 도시 이름을 범위로 지정해 보려 합니다. 기준은 A1셀로부터 시작하겠습니다. 이때 위에서 설명한 A~E의 값을 같이 알아볼까요?

 

기준 셀로부터 얼마나 떨어져있는지, 범위는 얼마인지가 중요!!

 

# A : 기준이 되는 셀

기준은 A1으로 하는 것으로 같이 정했으니, A1을 입력하면 됩니다.

 

# B : 기준이 되는 셀로부터 몇 칸 아래에서 시작할 지에 대한 값

지정하려는 범위의 첫 시작점이 인천이죠? 인천까지는 아래로 4칸이므로 4를 입력하면 됩니다.

 

# C : 기준이 되는 셀로부터 몇 칸 오른쪽에서 시작할 지에 대한 값

마찬가지로 인천까지는 오른쪽으로 1칸을 가야 하므로 1을 입력하면 됩니다.

 

# D : 몇 줄을 범위로 잡을 지에 대한 값

도시명 범위는 총 7줄로 되어 있습니다(인천~울산). 7을 입력하면 됩니다.

 

# E : 몇 칸을 범위로 잡을 지에 대한 값

옆으로 길게 쓰지 않고 1줄에 다 적었죠? 칸은 1칸이므로 1을 입력하면 됩니다.

 

정리하면 아래와 같은 식으로 입력하면 결과값이 나오게 되죠.

=OFFSET(A1,4,1,7,1)

 

OFFSET이 잘 적용되었죠?

OFFSET의 활용

OFFSET의 의미도 알겠고 사용법도 알겠는데, 당최 이걸 어디에 쓰느냐에 대한 궁금증이 생기실 겁니다. 제가 추천하는 방법은 크게 3가지 정도가 있습니다.

 

1. 값만 그대로 복사

주로 기존 데이터에서 일부 목록만을 발췌해서 새로운 보고서를 제작하는 등의 목적으로 사용하는 것이죠. 기존 데이터 값이 변경되더라도 OFFSET 함수로 지정되어 있기 때문에 굳이 보고서의 내용을 수정할 필요 없이 자동으로 변경된 값이 반영됩니다. 일간, 월간, 연간 단위로 반복 작업을 해야 하는 경우에는 유용하게 사용할 수 있습니다.

 

2. 다른 함수에 연계 사용

 

OFFSET은 범위를 요구하는 함수의 인수로 사용할 수 있어요

SUM 함수 뒤에 지정한 OFFSET 함수를 넣으면, OFFSET 함수에서 지정한 범위의 값을 더하는 방법으로 사용하는 것이 가능합니다. SUM 이외에도 특정 범위를 넣을 수 있는 형태의 함수는 대부분 사용이 가능합니다. 다만 실제로 이와 같이 활용을 하는 경우는 그리 많지 않아요. 굳이 OFFSET를 넣지 않고 그냥 범위를 지정해서 함수를 완성해도 동일한 값이 나오고 오히려 OFFSET을 추가하면 입력만 더 복잡해지기 때문입니다. 그래서 이 두번째 방법은 가능하다는 것 정도로만 알고 넘어가 주세요.

 

 

3. 유효성 검사, VBA에 활용

마지막 3번째 방법은, 유효성 검사 또는 VBA와 연계하는 방법입니다. VBA는 엑셀에 비주얼베이직을 연계한 프로그램 형태를 말하는데, 여기에서 설명하긴 너무 복잡해서 간단히 이 정도로만 언급하고 나중에 다른 포스팅에서 설명드릴게요. 이번 포스팅에서는 유효성 검사에 대해서만 설명드리겠습니다. 유효성 검사란 특정 셀에 지정한 값만 들어갈 수 있도록 설정하는 것으로, OFFSET을 통해 목록을 지정해 놓으면 잘못 입력하는 사태를 막을 수 있습니다. 바로 예시를 보여드릴게요

 

도시명에 포함된 7개 리스트만 입력되게 하고 싶다면?

저는 C열의 도시명 칸에는 A열에 적힌 도시명 7개만 사용할 수 있도록 설정해보겠습니다. 특정 값만 들어가게 하게 할 셀을 블록 지정 후, "데이터 유효성 검사" 버튼을 클릭해 주세요.

 

유효성 검사에는 반드시 원본에 입력되는 셀을 $을 추가해주세요!!

 

캡처에 보이는 대로 제한 대상은 "목록"으로, "드롭다운 표시"는 체크해주세요. 그리고 원본에 아까 배웠던 OFFSET을 활용하여 특정 지정 값의 목록을 입력해 주면 됩니다. 지금 원본에 입력된 내용을 해석하면 A2부터 아래로 1줄로 입력된 목록만 앞으로 이 칸에 입력할 수 있다는 뜻이 되는 거죠.

 

대신 유효성 검사에 OFFSET을 쓰는 경우, 이 2가지는 반드시 지켜주셔야 합니다. 첫번째는 셀은 무조건 $$을 추가한 절대 범위로 지정해야 한다는 것이고, 다른 하나는 4번째 범위의 줄 수를 지정할 때 COUNTA 함수로 써야 추후 목록이 추가될 때 자동으로 유효성 검사에 추가된다는 점이죠. 기억하기 어려우시면 이 함수를 통째로 복사해서 붙여 활용하셔도 됩니다.

 

=OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)

(값은 상황에 따라 바꿔서 입력하셔야 됩니다)

 

마치며

오늘은 OFFSET 함수에 대해 살펴보았는데요. OFFSET 함수는 사용법이 다소 복잡하긴 하지만, 익혀만 두면 여러모로 쓸 곳이 많습니다. 특히 지금처럼 보고서를 새로이 옮겨서 만드는 작업의 수고를 덜 수 있고, 유효성 검사나 VBA 작업에 효과적으로 활용할 수도 있죠. 다음 시간에는 OFFSET 함수만큼이나 많이 사용하는 VLOOKUP 함수에 대해 소개해 드리겠습니다.