엑셀에서 대,중,소분류 목록 만들기 (이름 정의, 데이터 유효성 검사 활용)

첫 번째 리스트에서 대분류 항목을 선택하고, 선택한 대분류 기준으로 중분류 리스트가 표시되고, 중분류 기준으로 소분류 기준이 표시되도록 INDIRECT 함수와 이름 정의, 데이터 유효성 검사를 활용하면 가능합니다. 고정된 리스트가 있을 때 활용하기 좋은 방법으로, 실무에서도 꽤 많이 사용되는 방법이며, 아래 내용을 참고해서 보시면 되겠습니다.

 

INDIRECT란?

INDIRECT는 문자 형식의 셀주소 값을 읽으면, 그 값의 실제 주소 내용을 가져오는 함수인데, 예를 들면 B4라는 문자를 읽을 경우 B4 셀의 내용을 가져오는 함수입니다. 전 개별로는 잘 사용하지는 않는 함수인데, 목록을 만들 때는 자주 사용하는 함수입니다.

함수 형식 : =INDIRECT(참조할 셀주소 또는 셀주소가 들어있는 실제 셀을 참조)

예시 : =INDIRECT("B4")

결과 : 안녕하세요

"B4" 는 INDIRECT가 B4셀의 내용을 가져오도록 문자 형태로 주소값을 주었으며(큰따옴표로 묶여 있기 때문에, 문자형입니다 "B4"), 주소 값이 들어있는 셀을 직접 참조할 수도 있는데, 예를 들면 F4셀("G5"), G5셀("설날")이란 문자가 있을 경우, INDIRECT(F4)를 하면 G5셀의 "설날" 을 표시해주는 형태입니다.

 

 

 

엑셀에서 INDIRECT와 이름 정의, 데이터 유효성 검사를 이용해, 대분류, 소분류 목록을 만들어보자.

 

첫 번째 이름 정의하기 

이름을 정의한다는 것은, 사실 그룹을 지정한다는 의미와 같으며, 아래 예제를 보면, 자동차회사란 그룹명에 현대, 기아, 쉐보레란 하위 내용을 묶는 것과 같습니다. 그리고 사용할땐, 그룹 이름만 가져와도 하위 내용을 함께 사용할 수 있습니다.

 

이름 정의를 위해선 대상을 선택해야 되는데, 그룹명으로 지정할 내용을 제외하고 선택해야 됩니다. 그룹명을까지 포함해서 선택할 경우, 목록에 그룹명까지 보이게 되니 주의해주세요. 선택을 하셨다면, 수식 메뉴의 이름 정의 항목으로 이동하면, 팝업이 하나 출력됩니다.

 

이름엔 그룹명을 수기로 작성합니다.

참조 대상엔, 제가 위에서 대상을 먼저 선택하라고 말씀드렸기 때문에, 그대로 하셨다면 선택 범위가 표시됩니다. 아래 예제의 경우 B2:B4 영역이 지정되었는데요. 만약 범위를 변경하고 싶으시면, 참조 대상을 다른 범위로 지정하시면, 즉시 변경됩니다.

그리고 선택 시 대분류 선택 값 기준으로 소분류 내용이 표시되도록 만드는 것이기 때문에, 소분류 제목과 대분류 하위 내용이 일치하도록 맞춰 주셔야 됩니다. 만약 일치하지 않을 경우, 링크가 정상적으로 진행되지 않습니다.

 

 

아래 예제에선 총 4회에 걸쳐 그룹 지정을 해주었습니다. 그룹 지정된 결과물은 수식 메뉴의 이름 관리자 항목에서 확인할 수 있으며, 설명드렸던 대로 자동차회사란 대분류 하위 내용과 소분류 제목을 동일하게 맞춰줬습니다.

 

 

 

 

두 번째 데이터 유효성 검사의 목록과 INDIRECT를 활용해, 대분류, 소분류 목록 만들기 

유효성 검사는 셀에 락을 걸 수 있다고 생각하시는 게 이해하시기 편하실 것 같습니다. 유효성 검사 항목엔 제한 대상이란 항목이 있는데, 이 항목의 목록, 텍스트 길이, 사용자 지정 등이 많이 사용되며, 언어 설정 등을 락 걸 때도 사용됩니다. 그리고 이번 포스팅에선, 제한 대상의 목록을 활용해보도록 하겠습니다.

 

아래 예제 기준으로 대분류 목록이 표실될 D2 셀을 선택 후 데이터 메뉴의 데이터 유효성 검사를 선택합니다. 제한 대상을 목록으로 선택하신 후 아래 예제 기준으론 원본에 =자동차회사를 기입합니다. 이렇게 되면 자동차회사의 하위인 3개 회사 이름이 목록으로 표시됩니다.

 

 

 

소분류 목록 표시 대상인 E2 셀을 선택 후 데이터 유효성 검사를 목록으로 선택하신 후 원본에 =indirect(D2)를 입력합니다. 입력 시 D2 셀의 내용이 없을 경우 "원본은 현재 오류 상태입니다. 계속하시겠습니까?"라는 문구가 출력될 것인데, INDIRECT가 참조하는 문자 형식의 주소값이 없기 때문에 그런 것입니다. D2 내용을 기입하면 이런 오류는 발생하지 않으니, 무시하고 진행합니다.

 

 

 

그리고 INDIRECT(D2)를 소분류에 지정한 이유는, 대분류를 현대자동차로 선택했을 경우, 소분류 그룹명을 미리 정의 해둔 현대자동차란 이름으로 가져오기 위해 사용됩니다.. 간단한 예로, 현대자동차란 대분류를 선택하면, INDIRECT(D2) -> INDIRECT("현대자동차") 순으로 변환되고, 미리 지정된 이름이 있는 항목이기 때문에, 소분류인 E2 셀엔 현대자동차 이름(그룹)의 하위 내용을 목록으로 보여주는 것입니다.

 

 

위에 3가지 기능을 함께 사용하면, 엑셀을 활용하는데 상당한 도움이 됩니다. 특히 실무에서 많이 사용할 수 있으니, 꼭 익혀두시는 게 좋을 것 같습니다.

 

포스팅을 작성하면서, 가급적 쉽게 쓰려고 노력을 많이 하는 편이지만, 쉽지가 않은 것 같습니다. 아무쪼록 제 포스팅이 엑셀을 활용하는데, 조금이나마 도움이 됐으면 하는 바람입니다.