엑셀의 유효성 검사 기능을 이용한 드롭다운 목록은 정해진 목록을 미리 만들고 이 목록을 가지고 필요한 셀을 채우는 기능입니다.
이 기능을 사용하여 얻는 장점은 유효성 검사기능이라는 이름에서 알 수 있듯이 미리 작성된 목록을 사용하므로 잘못 입력하는 일이 적어지고, 반복적인 입력을 간단히 클릭으로 해결할 수 있습니다. 왜 이 기능이 유효성 검사기능을 이용하는지 이해하셨으리라 생각됩니다.
간단한 드롭다운 목록
위에서 드롭다운 목록은 입력의 범위를 제한하여 유효한 입력을 유도하는 방법이라고 설명하였습니다. 목록을 작성하는 방법을 예로 들어 보겠습니다.
위와 같이 나라들의 목록을 나열하여 하나의 목록을 만들었습니다. 나라의 입력을 자주 한다면 위와 같이 목록을 을 작성한 후 이를 이용한 드롭목록을 만들 수 있습니다.
한번, C열에 만들어보죠.
드롭다운 목록 만드는 방법
드롭다운 목록을 사용하려는 셀을 택한 뒤
데이터 => 데이터 도구=>데이터 유효성 검사=>데이터 유효성 검사
제한 대상(A): 에서 목록 선택
원본(S)에서 이용할 목록의 범위를 마우스로 지정(한국 ~ 일본) => 확인
기본적으로 원본(S)에 입력되는 셀 범위는 절대값참조로 되어 있습니다.(이유는, 아래와 같이 채우기를 할 경우 목록 값을 상대참조하면 안되기 때문이죠.)
원하는 만큼 드래그 드롭으로 채우기
셀 범위 대신 이름정의 사용하기
2015/07/25 - [엑셀] - 엑셀 이름정의 가지고 놀아 보자
위에서는 단순하게 셀 범위를 사용하였습니다. 여기서 셀 범위를 이름정의로 만들어 놓으면 더 가독성 있게 되고, 다른 기능들을 유용하게 사용할 수 있습니다. 우선 이름 정의를 이용해 보겠습니다.
이름정의 설정
데이터 유효성 검사에서 원본(S)에 범위지정대신 이름정의를 사용하였습니다.(=나라)
보통 같은 Sheet에 사용하면 지저분해서 다른 시트에(보통Sheet2)에 목록을 적어놓고 이름정의를 만든 후 이름정의로 사용하는 게 좋습니다.(여기서는 설명을 위해 같은 셀에 만들어 놓음)
대분류, 소분류 만들기
드롭목록을 사용하면 꼭 대분류, 소분류에 대한 드롭다운 목록을 사용할 줄 알아야 합니다. 다음은 대분류, 소분류에 대한 예입니다.
C1열의 한국은 첫 번째로 만든 드롭 다운 목록이고 한국으로 채워 놓았습니다. D1은 일종의 C1에 종속적인 드롭다운 목록이라고 이해하면 되는데, C1이 대분류라면 D1은 소분류가 됩니다. 물론 소 분류 이후에 더 많은 분류를 할 수도 있다.
먼저 대분류의 목록 하나하나에 해당하는 소분류 목록을 적어주는게 우선적으로 해야 할 일입니다.
그리고 이름정의를 각각 자신의 부모에 해당하는 이름으로 이름정의를 해줍니다.(중요) (붉은 색이 각 목록의 이름정의입니다.)
여기서 엑셀의 표로 돌아가서 보면, C1이 대분류에 속하고, D1이 소 분류에 속합니다. 원하는 시나리오대로라면 C1에서 대분류를 한국으로 택하였고 D1의 소분류는 한국에 대한 도시의 목록이어야 할 것입니다. 대분류의 선택에 따라 종속적으로 D1의 목록이 변해야 하므로 D1의 드롭다운 목록은 C1, 바로 왼쪽에 있는 셀을 참조해야 한다는 것을 알 수 있습니다. (참고 : 상대참조가 되어야한다.)
소분류에서 대분류를 참조하는 방법
잘못 된 방법
모든 조건은 다 갖추어 져 있습니다. 데이터 유효성 검사에서 원본(S)에 셀 범위나 이름정의를 넣어야 하는데, C열의 텍스트 값을 참조해야 합니다. 그런데 위 그림처럼 단순히 셀을 택하면 '한국'이라는 하나의 목록으로 인식하게 됩니다.
이런 식이죠.
INDIRECT 함수
2017/03/22 - [엑셀] - 엑셀 INDIRECT 함수 정확히 이해하기
이 함수는 텍스트 값을 이름정의로 인식해주는 함수입니다. 위의 경우로 말하면 '한국'을 텍스트가 아닌 이름정의로 인식해 주는 것이죠. 그리고 앞에서 한국에 대한 이름정의를 다음과 같이 해 놓았습니다.
제대로된 사용
INDIRECT(이름정의로 바꿀 텍스트)
주의사항 : 왼쪽은 절대 참조, 오른쪽은 상대 참조입니다. 채우기를 해야 할 경우는 당연히 오른쪽같이 상대 참조 C1으로 사용해야 합니다.
제대로 적용된 모습입니다.
정리하면, 대분류의 경우 절대참조로 또는 이름정의를 이용했지만, 소분류의 경우는 상대참조로 INDIRECT함수를 이용하여 텍스트 값을 이름정의로 바꾸었습니다. 소분류의 소분류 또한 INDIRECT함수를 사용하여 똑 같은 방식으로 하면 됩니다.