엑셀 배우기, OFFSET/ COUNTA함수를 활용한 동적 이름 정의와 드롭다운 목록 생성!

이름 정의와 드롭다운 방식으로 목록을 생성하는 방법은 지난 엑셀 강좌, 엑셀배우기, 이름정의와 INDIRECT/VLOOKUP 함수 활용, 목록과 드롭다운 표시! 에서 다룬 적이 있습니다.

복습 차원에서 다시 설명드리면, 이름 정의라는 것은 일정한 범위에 이름을 부여해서, 그 이름을 키값으로 해서 해당 범위를 참조할 수 있도록 한다는 의미입니다. 그리고 드롭다운 방식으로 목록을 생성할 때, 목록에 들어갈 원본 데이터 값을 하나하나 입력할 수도 있지만, 원본 데이터에 특정 범위를 입력하면, 해당 범위의 데이터 전체를 목록으로 표시할 수도 있답니다.

그런데, 이 특정 범위의 데이터를 편집할 필요가 있을 때는 어떻게 해야할까요? 원본 데이터를 직접 입력한다면, 매번 목록 또한 손수 수정을 해야하는데, 번거로운 작업입니다. 

원본 데이터에 특정 범위를 입력했다면, 해당 범위를 벗어난 데이터는 원래의 지정 범위만을 참조하는 목록에 자동으로 적용되지 않기 때문에, 이 역시 목록이 참조할 새로운 범위를 다시 지정해줘야할 필요가 생깁니다. 즉, 목록이 참조할 범위가 동적일 때, 이 범위를 목록이 자동으로 참조할 수 있게 만드는 방법이 오늘의 엑셀 강좌의 주제입니다.

일단 B열의 상품 목록이 있다고 합시다. 그리고 우리는 이 상품 목록을 E2셀에 드롭다운으로 표시하고 싶습니다. 드롭다운 목록을 만드는 방법은 상단 메뉴의 데이터,의 데이터 유효성 검사를 누르고, 제한 대상을 목록으로 바꾸고, 드롭다운 표시에 체크하고, 원본에 목록으로 표시될 데이터를 입력하거나, 목록으로 표시할 데이터들의 범위를 지정하면 됩니다.

그런데, B열의 상품 목록에 계속 새로운 상품이 추가된다면, 목록을 만들 때, 단순히 목록이 참조하는 범위를 B3:B7로 고정할 수가 없습니다. 이 문제를 해결하기 위해서는 OFFSET 함수와 COUNTA 함수를 통해서 동적 범위에 이름을 정의하는 방법을 사용해야 합니다.

 

간단히 말해, OFFSET 함수는 특정 셀이나 특정 범위를 참조하는 함수입니다. 특정 셀을 참조하는 OFFSET 함수의 구성은 '=OFFSET(기준 셀, 행 이동 수, 열 이동 수)'인데, 예를들어 아래에서 '=OFFSET(B2, 1, 0)'은 B2 셀을 기준으로 1행 아래, 0열 오른쪽으로 이동한 셀을 참조하고, A1 값을 반환하게 됩니다. OFFSET 함수로 특정 범위를 참조하기 위해서는 기본 함수 구성에 옵션 값인, 길이와 너비를 추가한 '=OFFSET(기준 셀, 행 이동 수, 열 이동 수, 길이, 너비)'로 해야하고, B3:B7 범위를 참조하고자 하는 OFFEST 함수의 수식은 '=OFFSET(B2, 1, 0, 5)'로 구성이 됩니다.

 그런데 앞서 계속 언급한대로, B열의 목록에는 계속 신제품이 추가될 가능성이 있기 때문에, OFFSET 함수의 길이 인수에 숫자 대신에 COUNTA함수를 삽입해 줍니다. COUNTA 함수는 범위 내에 비어있지 않은 셀의 개수를 구하는 함수입니다. 함수의 구성은 'COUNTA(범위)'로 간단합니다. 즉 OFFSET 함수 구성에, 길이 인자 5 대신에 'COUNTA($B:$B)-1'을 넣게되면, B열에서 비어있지 않은 셀의 개수 6개에서 1을 뺀, 숫자 5를 반환하고, 원래의 '=OFFSET(B2, 1, 0, 5)' 수식과 동일한 범위를 참조할 수 있습니다. 참고로 1을 빼는 이유는 B2셀의 데이터를 COUNTA 함수 계산에서 제외하기 위함합니다.

그리고 B8셀에 새롭게 A6라는 상품이 추가된다면, 'COUNTA($B:$B)-1' 수식은, 이제 숫자 6을 반환하고, '=OFFSET(B2, 1, 0, COUNTA($B:$B)-1)' 수식은 '=OFFSET(B2, 1, 0, 6)'으로, B3:B8까지의 범위를 참조하게 됩니다. 동적으로 변하는 범위를 참조하게 되는 것입니다. 그리고 이 수식을 아래의  이름 정의 창의 참조 대상에 입력해 주고, 이름을 정의해 주면 끝입니다.

OFFSET 함수의 기능만 제대로 이해하면 전혀 어렵지 않은데, 이 함수가 범위를 참조한다라는 점이 엑셀 함수 사용이 익숙하지 않은 분들에게는 사실 쉽지 않은 개념일 수도 있습니다. 반면에 OFFSET 함수까지 궁금할 정도가 되었다면 엑셀 중급자 수준은 되었다는 이야기이기도 합니다. 사실 실무에 사용하는 엑셀의 함수는 몇가지 되지 않으니 말입니다. 이 포스팅까지 14개 정도 엑셀 관련 포스팅을 했는데, 이정도만 엑셀을 다룰 수 있어도 충분히 엑셀에 자부심을 가져도 되는 수준이라 생각하셔도 좋습니다.