엑셀 유동 범위 - offset 함수 데이터유효성 목록 범위 지정때 유용

엑셀을 사용하다보면 버튼을 사용하면 사용하기 편리할 때가 많습니다.

그래서 데이터유용성으로 버튼을 자주 만들곤 하는데 보통 목록을 잘 안보이는 곳에다 만들죠.

나중에 목록 업데이트를 하면 다시 찾아서 수동으로 해야하니 가끔 불편할때가 있습니다.

그래서 자동으로 목록을 업데이트해주는 함수인 offset 함수에 대해 알아보도록 하겠습니다.
분명 고급함수이긴하지만 한번 알게 되면 사용법이 절대 어렵지 않으니 한번 익혀보세요.

보통 이렇게 4개의 목록을 지정해서 사용하다가 한가지 더 추가하기 위해 범위를 지정한 곳으로 찾아가게 되죠.

 

그렇게 한 후 방문이라고 목록에 입력한 후에 다시 데이터유효성으로 가서 새로 입력한 범위까지 포함해야 버튼을 눌렀을때 새로 입력한 값이 뜹니다.

 

하지만 offset함수를 이용하면 아래와 같이 목록에 입력만 하면 바로 버튼에서 리스트가 보이게 됩니다.

물론 처음 데이터 범위를 지정할때 좀 여유롭게 지정해서 몇 개를 더 입력해서 사용하는 방법도 있습니다.

하지만 그러면 처음 목록뜰때 빈칸의 형태로 쭈욱 나와서 보기도 안좋고 불편합니다.

이럴때 해결하는 것이 앞에서도 언급했던 엑셀유동범위를 offset함수로 지정하는 것인데 아래처럼 딱 한줄만 외우면 됩니다.

범위넣을때 지정된 범위 대신에 =offset(시트명!목록첫시작셀,,,counta(시트명!범위좀넓게))

이렇게 써주시면 됩니다.

설명을 하겠습니다.

시트명은 같은 시트에 있다면 생략해도 되고 중요한게 목록입력된 첫 시작셀을 입력해야 합니다.

여기서부터 범위를 지정한다는 의미입니다.

 

그리고 중요한게 ,,, 이렇게 쉼표 3번을 입력해야합니다.

사실 두 개의 변수를 더 입력해야하는데 여기서는 생략해도 되기때문에 쉼표가 3번 들어가는 것입니다.

다음이 counta함수.

counta함수는 값이 있는 셀의 개수를 구해주는 함수인데 이렇게 한 후에 목록이 들어있는 열을 지정해주면 됩니다.

저처럼 그냥 열 전체를 지정해도 됩니다.

만약 목록위에 목록을 통합하는 이름을 지정했을 경우는 -1을 해줘야하는데 복잡할 수 있으므로 목록이름을 따로 지정하지 않을 경우를 가정하겠습니다.

실제로도 목록범위 지정할때 이름까지 지정안하는 경우도 많으니까요.

(물론 확실히 관리하려면 이름까지 지정해주는게 좋긴합니다.)

 

이렇게 엑셀 offset함수로 유동범위를 지정하면 나중에 목록에 추가되어도 버튼범위를 따로 지정안해도 자동으로 목록에 추가가 되니 자주 업데이트하는 엑셀파일이 있다면 한번 사용해보세요.

몇번만 연습해보면 정말 쉽게 할 수 있고 이 함수를 잘 이해한다면 다른 곳에 응용해서 다른 목적으로도 사용가능합니다.

vlookup범위 등에서도 응용할 수 있겠네요.

vlookup에 응용하려면 좌우로도 범위를 지정해줘야하니 다른 문서도 찾아보면서 offset함수 공부를 해야할 것 같긴하네요.