Please Enable JavaScript!
Gon[ Enable JavaScript ]

엑셀 Excel 3단계 의존관계 대분류, 중분류, 소분류 구현하기

엑셀(Excel)/Excel
반응형

엑셀에서는 사용자의 데이터 선택 범위를 지정하고 실수를 줄이기 위한 용도로 선택상자를 주로 사용합니다. 데이터 유효성 검사로 구현할 수 있습니다. 이보다 좀 더 고도화한 것이 단계별 데이터 의존 관계입니다. 이름관리자, 데이터 유효성 검사 기능을 이용해서 3단계 의존 관계를 만들어 보겠습니다. 그러니까 이전 값을 선택했을 때 자동으로 관련 값이 나타나도록 대분류, 중분류, 소분류 선택상자 기능을 구현한다는 의미입니다.

 

* 실전 연습을 위한 작업파일이 필요하다면 아래 파일을 다운받으세요.

엑셀(Excel) 의존관계 대분류, 중분류, 소분류 만드는 방법.xlsx
0.01MB

 

3단계 의존관계를 구현할 데이터는 다음과 같습니다. 앞에서 선택한 값에 해당하는 타이틀의 데이터를 다음 선택상자에 자동으로 추가하는 구조입니다.


 

 

 

품종 이름 관리자로 지정하기

 

첫 번째 단계로 이름 관리자를 지정해야 합니다. 수식 탭 > 정의된 이름 그룹 > 이름 관리자 리본 메뉴를 클릭합니다.

 

다음은 이름 관리자 창에서 상단에 새로 만들기 버튼을 클릭합니다. 곡물 정보를 하나씩 이름 관리자에 추가하기 위한 작업입니다.

 

새 이름 창에서 이름을 입력하고 참조 대상 란에 사용할 데이터 영역을 지정합니다. 이름은 곡물 제목과 동일하게 합니다.

 

처음 추가한 방법대로 모든 곡물에 대한 이름을 정의해서 추가합니다. 만약 이름 정의할 항목들이 많다면 바로 아래에 소개하는 방법을 사용해 보세요. 클릭 몇 번으로 한 번에 모두 추가할 수 있습니다.

 

아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다.
엑셀 Excel PERCENTILE 와 QUARTILE 이용 백분위수와 사분위수 구하기
엑셀 Excel RAND, RANDBETWEEN 함수로 랜덤 숫자추출하는 방법
엑셀 Excel 원본파일과 다른 별도의 문서로 작업하는 방법
엑셀 Excel 데이터 유효성 검사로 의존 관계 대분류, 소분류 리스트 만들기
셀 Excel INDIRECT 함수의 사용법



 

 

한 번에 여러 항목을 이름 관리자로 지정하기

 

데이터 영역 전체를 한 번에 이름 정의하는 방법을 소개합니다. 데이터가 있는 영역에서 제목을 포함해서 선택하고 수식 탭 > 정의된 이름 그룹 > 선택 영역에서 만들기 리본 메뉴를 클릭합니다.

 

다음은 이름 정의에서 사용할 각 이름 지정을 위해 옵션에서 첫 행을 체크합니다. 첫 행을 체크하면 제목에 해당하는 값을 이름으로 지정합니다.

 

이름 관리자를 띄워서 제대로 이름 정의가 되었는지 확인해 보시기 바랍니다.


 

 

 

데이터 유효성 검사로 첫 번째 선택상자 만들기

 

이제 의존관계가 있는 3개의 선택상자를 만들 수 있는 조건이 갖춰졌습니다. 선택상자를 추가할 셀을 선택하고 데이터 탭 > 데이터 유효성 검사 리본 메뉴를 클릭합니다.

 

데이터 유효성 창 > 설정 탭으로 이동해서 유효성 조건을 설정해야 합니다. 제한 대상 값을 목록으로 변경합니다.

 

원본 에는 이전에 만든 이름 을 입력합니다. 첫 번째 선택상자에 들어갈 식용농작물“=” 와 함께 추가합니다.

 

결과는 다음과 같습니다. 첫 번째 선택상자에는 지정한 이름에 해당하는 값이 들어와 있습니다.


 

 

 

두 번째 선택상자 만들기

 

다음으로 첫 번째 선택상자의 값에 따라 달라질 두 번째 선택상자를 만들겠습니다. 데이터 유효성 설정 창을 띄운 후 제한 대상을 목록으로 하고 원본에 =INDIRECT(B10) 을 입력합니다. INDIRECT 함수는 인수값으로 입력한 텍스트의 참조 위치 값을 반환합니다. 그러니까 첫 번째 선택상자의 값에 해당하는 이름영역에 값을 배열로 반환하는 것입니다.

 

결과는 다음과 같습니다. 첫 번째 선택이 과일이므로 과일이라고 정의한 이름 값을 가져와서 두 번째 목록에 추가합니다. 첫 번째 선택을 변경하면 두 번째도 그에 맞는 이름 값을 불러와서 채웁니다.


 

 

 

세 번째 선택상자 만들기

 

세 번째도 두 번째와 동일하게 INDIRECT 함수로 원본 항목을 채웁니다.

 

두 번째에 선택한 값에 해당하는 이름영역의 값을 세 번째 선택상자에 채웠습니다.

 

아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다.
엑셀 Excel PERCENTILE 와 QUARTILE 이용 백분위수와 사분위수 구하기
엑셀 Excel RAND, RANDBETWEEN 함수로 랜덤 숫자추출하는 방법
엑셀 Excel 원본파일과 다른 별도의 문서로 작업하는 방법
엑셀 Excel 데이터 유효성 검사로 의존 관계 대분류, 소분류 리스트 만들기
셀 Excel INDIRECT 함수의 사용법
반응형
Posted by 녹두장군

댓글을 달아 주세요

  1. sarah 2015.05.21 02:23  댓글주소  수정/삭제  댓글쓰기

    저 위에 유효성 만들어주는 데이터를 다른 시트에 놓고도 유효성 검사를 할 수 있는 방법이 있을까요?
    예를 들어 음식, 피자 기타등등의 데이터는 시트1 엑셀에 있고
    제가 유효성을 클릭해서 넣는 셀은 시트2에 있게 하는 식으로요!

  2. 익명 2015.10.06 11:32  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  3. 익명 2015.11.26 15:24  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  4. 김소울 2017.07.21 18:41  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 감사합니다!

    혹시 구글 스프레드시트로 의존관계 대/중/소분류 설정하는 방법이 있나요?
    현재는 엑셀에서만 가능한걸까요?

  5. 아리아리 2018.02.21 18:52  댓글주소  수정/삭제  댓글쓰기

    안녕하세요,

    고급정보는 감사히 잘 보고 있습니다 :)

    대중소 분류시에, 중분류의 값에 띄어쓰기가 있다면 소분류에서는 인지를 못 하는것 인가요?

    예를 들어
    중분류의 값을 '벼 밀-1' 혹은 '벼-88' 으로 하고자하고
    소분류를 선택했을때 중분류와 같은 값, '벼 밀-1' 혹은 '벼-88' 로 나오게 하고자 합니다..

    미리 감사드리며...

  6. 배만자라 2019.12.27 10:23  댓글주소  수정/삭제  댓글쓰기

    귀한 노하우 공유해 주셔서 감사합니다. 잘 배우고 갑니다. 꾸벅~~