엑셀에서는 사용자의 데이터 선택 범위를 지정하고 실수를 줄이기 위한 용도로 선택상자를 주로 사용합니다. 데이터 유효성 검사로 구현할 수 있습니다. 이보다 좀 더 고도화한 것이 단계별 데이터 의존 관계입니다. 이름관리자, 데이터 유효성 검사 기능을 이용해서 3단계 의존 관계를 만들어 보겠습니다. 그러니까 이전 값을 선택했을 때 자동으로 관련 값이 나타나도록 대분류, 중분류, 소분류 선택상자 기능을 구현한다는 의미입니다.
* 실전 연습을 위한 작업파일이 필요하다면 아래 파일을 다운받으세요.
▼ 3단계 의존관계를 구현할 데이터는 다음과 같습니다. 앞에서 선택한 값에 해당하는 타이틀의 데이터를 다음 선택상자에 자동으로 추가하는 구조입니다.
| ◎ 품종 이름 관리자로 지정하기 |
▼ 첫 번째 단계로 이름 관리자를 지정해야 합니다. 수식 탭 > 정의된 이름 그룹 > 이름 관리자 리본 메뉴를 클릭합니다.
▼ 다음은 이름 관리자 창에서 상단에 새로 만들기 버튼을 클릭합니다. 곡물 정보를 하나씩 이름 관리자에 추가하기 위한 작업입니다.
▼ 새 이름 창에서 이름을 입력하고 참조 대상 란에 사용할 데이터 영역을 지정합니다. 이름은 곡물 제목과 동일하게 합니다.
▼ 처음 추가한 방법대로 모든 곡물에 대한 이름을 정의해서 추가합니다. 만약 이름 정의할 항목들이 많다면 바로 아래에 소개하는 방법을 사용해 보세요. 클릭 몇 번으로 한 번에 모두 추가할 수 있습니다.
| ◎ 한 번에 여러 항목을 이름 관리자로 지정하기 |
▼ 데이터 영역 전체를 한 번에 이름 정의하는 방법을 소개합니다. 데이터가 있는 영역에서 제목을 포함해서 선택하고 수식 탭 > 정의된 이름 그룹 > 선택 영역에서 만들기 리본 메뉴를 클릭합니다.
▼ 다음은 이름 정의에서 사용할 각 이름 지정을 위해 옵션에서 첫 행을 체크합니다. 첫 행을 체크하면 제목에 해당하는 값을 이름으로 지정합니다.
▼ 이름 관리자를 띄워서 제대로 이름 정의가 되었는지 확인해 보시기 바랍니다.
| ◎ 데이터 유효성 검사로 첫 번째 선택상자 만들기 |
▼ 이제 의존관계가 있는 3개의 선택상자를 만들 수 있는 조건이 갖춰졌습니다. 선택상자를 추가할 셀을 선택하고 데이터 탭 > 데이터 유효성 검사 리본 메뉴를 클릭합니다.
▼ 데이터 유효성 창 > 설정 탭으로 이동해서 유효성 조건을 설정해야 합니다. 제한 대상 값을 목록으로 변경합니다.
▼ “원본” 에는 이전에 만든 “이름” 을 입력합니다. 첫 번째 선택상자에 들어갈 “식용농작물” 을 “=” 와 함께 추가합니다.
▼ 결과는 다음과 같습니다. 첫 번째 선택상자에는 지정한 “이름” 에 해당하는 값이 들어와 있습니다.
| ◎ 두 번째 선택상자 만들기 |
▼ 다음으로 첫 번째 선택상자의 값에 따라 달라질 두 번째 선택상자를 만들겠습니다. 데이터 유효성 설정 창을 띄운 후 제한 대상을 목록으로 하고 원본에 =INDIRECT(B10) 을 입력합니다. INDIRECT 함수는 인수값으로 입력한 텍스트의 참조 위치 값을 반환합니다. 그러니까 첫 번째 선택상자의 값에 해당하는 “이름” 영역에 값을 배열로 반환하는 것입니다.
▼ 결과는 다음과 같습니다. 첫 번째 선택이 “과일” 이므로 “과일” 이라고 정의한 이름 값을 가져와서 두 번째 목록에 추가합니다. 첫 번째 선택을 변경하면 두 번째도 그에 맞는 이름 값을 불러와서 채웁니다.
| ◎ 세 번째 선택상자 만들기 |
▼ 세 번째도 두 번째와 동일하게 INDIRECT 함수로 원본 항목을 채웁니다.
▼ 두 번째에 선택한 값에 해당하는 “이름” 영역의 값을 세 번째 선택상자에 채웠습니다.
댓글을 달아 주세요
sarah 2015.05.21 02:23 댓글주소 수정/삭제 댓글쓰기
저 위에 유효성 만들어주는 데이터를 다른 시트에 놓고도 유효성 검사를 할 수 있는 방법이 있을까요?
예를 들어 음식, 피자 기타등등의 데이터는 시트1 엑셀에 있고
제가 유효성을 클릭해서 넣는 셀은 시트2에 있게 하는 식으로요!
익명 2015.10.06 11:32 댓글주소 수정/삭제 댓글쓰기
비밀댓글입니다
어떤 내용인지 메일로 보내주세요
gonhaha@naver.com 입니다.
익명 2015.11.26 15:24 댓글주소 수정/삭제 댓글쓰기
비밀댓글입니다
네 언젠가는 제가 따라할 일이 있것죠 ㅋㅋ
김소울 2017.07.21 18:41 댓글주소 수정/삭제 댓글쓰기
좋은 자료 감사합니다!
혹시 구글 스프레드시트로 의존관계 대/중/소분류 설정하는 방법이 있나요?
현재는 엑셀에서만 가능한걸까요?
구글 스프레드시트는 데이터 유효성 검사에 목록 만드는 기능만 있고 의존성 관계를 설정할 수 있는 옵션은 없습니다.
아리아리 2018.02.21 18:52 댓글주소 수정/삭제 댓글쓰기
안녕하세요,
고급정보는 감사히 잘 보고 있습니다 :)
대중소 분류시에, 중분류의 값에 띄어쓰기가 있다면 소분류에서는 인지를 못 하는것 인가요?
예를 들어
중분류의 값을 '벼 밀-1' 혹은 '벼-88' 으로 하고자하고
소분류를 선택했을때 중분류와 같은 값, '벼 밀-1' 혹은 '벼-88' 로 나오게 하고자 합니다..
미리 감사드리며...
배만자라 2019.12.27 10:23 댓글주소 수정/삭제 댓글쓰기
귀한 노하우 공유해 주셔서 감사합니다. 잘 배우고 갑니다. 꾸벅~~