■ 목차
1. 엑셀 팁/질문 : 데이터 유효성 검사의 목록 상자를 이중으로 연동해서 만들고 싶어요.
2. 데이터 유효성 검사 이중 목록 만들기 - 원리 이해하기
3. 유효성 검사 이중 목록 만들기 - 예제
** 핵심 요약
1. 엑셀 팁/질문 : 데이터 유효성 검사의 목록 상자를 이중으로 연동해서 만들고 싶어요.
- 데이터 유효성 검사에서 첫 번째 목록 선택에 따라서 달라지는 두 번째 목록을 만들고 싶습니다. 어떻게 해야 할 가요?
- 아래 예제에서 첫 번째 목록 리스트에서 "과일"을 선택하는 경우 두 번째 목록 리스트에서는 과일 항목의 사과, 포도, 배만 선택가능하도록 목록 구성되어야 합니다.
2. 데이터 유효성 검사 이중 목록 만들기 - 원리 이해하기
- 우선 "데이터 유효성 검사 - 목록"의 목적은 정확한 데이터 관리에 있습니다.
- 사전에 정해진 데이터만을 선택하고 입력하여 데이터의 정합성을 확보하고
- 사용자는 간편하게 입력 가능한 데이터 리스트를 보고 선택할 수 있는 윈윈 기능입니다.
- 그리고 이러한 데이터의 정합성의 확장 개념이 이중 목록입니다.
- 물론 데이터 유효성 검사의 이중 목록만으로 완벽하게 정확한 데이터를 관리할 수는 없습니다.
- 나름 구멍이 있긴 하지만 그래도 엑셀에서 이만한 것도 없는 듯합니다.
- 저희가 원하는 것은 첫 번째 목록은 "과일, 야채, 육류"의 리스트가 보이고 만약 "과일"을 선택한 경우 "사과, 포도, 배"의 두 번째 목록 리스트가 보이도록 하는 것입니다.
- 첫 번째 목록을 만드는 방법은 일반적인 데이터 유효성 검사의 "목록" 기능이기 때문에 크게 어려운 부분이 없습니다.
- 그리고 "과일"이 첫 번째 목록으로 선택된 경우 이와 연동하여 두 번째 목록을 보여주는 것이 이번 과정의 핵심이고
- 여기에서는 두 번째 목록을 보여 주기 위해서 엑셀의 "이름 정의"와 "Indirect 함수"를 사용하게 됩니다.
- ① "이름 정의" 기능
- "이름 정의"는 특정 셀 범위에 이름을 붙이고 셀 주소를 대신해 사용이 가능한 기능입니다.
- 그래서 셀 주소를 입력하는 대신에 "이름 정의"된 이름을 수식에 입력을 하게 되면 셀 주소 입력한 것과 동일한 기능을 하게됩니다.
- 즉 "정의된 이름(과일)" = "특정 셀 주소(B3:B5)"라고 보시면 되겠습니다.
- 그래서 우리는 두 번째 목록 리스트를 가져올 때 첫 번째 목록 리스에서 선택한 "과일, 야채, 육류"에 해당하는
- 각각의 셀 범위를 "이름 정의"로 사전에 정의해 두고 선택된 첫 번째 목록에 해당하는 셀 범위만을 가져오게 됩니다.
- ② Indirect 함수
- 셀 주소처럼 생긴 "텍스트"를 "셀 주소형식"으로 변형해 주는 함수입니다.
- 아래는 "B2"셀에 입력된 "과일"이라는 텍스트를 직접, 간접참조한 내용입니다.
- 만약 "E2"셀에 입력된 텍스트가 "과일"이 입력되어 있는 주소( "B2" )가 있는 경우
- "Indirect 간접 참조"처럼 텍스트를 셀 주소처럼 사용하게 해주는 함수라고 보시면 되겠습니다.
- (텍스트 "B2") ≠ (셀주소 B2) → (Indiret (텍스트 "B2")) = (셀주소 B2)처럼 사용할 수 있는 기능이라고 보시면 됩니다.
3. 유효성 검사 이중 목록 만들기 - 예제
- 1) 첫 번째 목록 만들기 - 데이터 유효성 검사 > 목록
- 메뉴 패스(리본 메뉴) : "데이터" 탭 > "데이터 도구" 그룹 > "데이터 유효성 검사"
- 데이터 유효성 검사의 "목록" 기능을 사용합니다.
- "드롭다운 표시"라는 옵션이 기본값으로 선택되어 있는데 혹시나 해제된 경우 선택을 해주시기 바랍니다.
- 원본 범위 ($B$2:$D$2)는 마우스로 입력란을 한 번 클릭을 하신 후에 마우스로 셀의 해당 범위를 드래그 선택을 해주면
- 엑셀이 자동으로 선택된 범위에 해당하는 셀 주소를 절댓값 형식으로 입력을 하게 되며 이렇게 입력된 셀주소를 그대로 사용하시면 됩니다.
- 이렇게 첫 번째 목록 만들기를 통해서 우리는 "과일", "야채", "육류"를 선택하는 것이 가능하게 됩니다.
- 2) "이름 정의" - "과일", "야채", "육류"
- 메뉴 패스(리본 메뉴) : "수식" 탭 > "정의된 이름" 그룹 > "이름 정의"
- 간단한 방식 : (이름 정의 하고자 하는 셀 범위 선택) > "이름 상자"
- 이번에는 두 번째 선택에서 보여지게될 범위 선택을 위해서 "이름 정의"를 하도록 하겠습니다.
- 이름정의는 첫 번째 선택에서 "과일", "야채", "육류"를 선택하게 되면 각각의 선택에 해당하는
- 두 번째 선택이 될 수 있도록 동일한 이름으로 셀 범위를 선택해 주게 됩니다.
- 즉 "과일"은 "B3:B5", "야채"는 "C3:C6", "육류"는 "D3:D7"의 범위를 선택해 주시면 됩니다.
- "이름 정의"는 리본 메뉴를 사용해도 되지만 간단하게 "이름 상자"를 많이 이용합니다.
- ①,먼저 "이름 정의"하고자 하는 범위를 먼저 마우스로 선택하고(B3:B5)
- 그 상태에서 ②"이름 상자"에 원하는 이름("과일")을 키보드로 입력하시면 됩니다.
- "야채", "육류" 모두 같은 방식으로 이름을 정의해 주시면 됩니다.
- 여기서 이름으로 입력되는 값은 첫 번째 선택되는 목록 값의 텍스트와 정확하게 일치하게 입력이 되어야 됩니다.
- 3) 두 번째 목록 만들기 - 데이터 유효성 검사 > 목록
- 메뉴 패스(리본 메뉴) : "데이터" 탭 > "데이터 도구" 그룹 > "데이터 유효성 검사"
- 두 번째 목록을 만드는 방법은 첫 번째 목록을 만드는 방법과 동일합니다.
- 다만 "데이터 유효성 검사 - 목록"에 입력되는 "원본"에 들어가는 수식이 아래와 같이 입력이 되어야 합니다.
- 입력 수식 : =INDIRECT($B$10)
- 철자 교정 등의 엑셀 지원 기능이 없기 때문에 정확하게 함수 이름을 키보드로 입력해야 합니다.
- 셀 주소는 마우스로 선택된 첫 번째 목록 값에 해당하는 "B10"셀을 마우스로 선택하시면 자동으로 입력이 되게 됩니다.
- 4) 데이터 이중목록 사용하기
- 아래와 같이 "선택1"의 첫 번째 목록을 선택하게 되면 "선택2"에서는 자동으로 해당하는 두 번째 목록이 리스트로 나타나 선택가능한 이중 목록 상자가 만들어졌습니다.
- 데이터 유효성 검사의 이중 목록에서 가장 중요한 부분은 "Indirect 함수"입니다.
- 첫 번째 선택 항목의 값으로 각각의 "이름 정의"를 만들어 줄 수 있지만
- 첫 번째 선택된 항목의 값은 "텍스트"에 해당하기 때문에 반드시 "Indirect 함수"로 이름정의(셀 주소형식)로 변경하는 작업이 필요하게 되는 것입니다.
** 핵심 요약 : 데이터 유효성 검사 목록상자, 연동해서 만들기 (이중 목록상자, 드롭다운)
1. 엑셀 팁/질문 : 데이터 유효성 검사의 목록 상자를 이중으로 연동해서 만들고 싶어요.
- 데이터 유효성 검사로 첫 번째 목록 선택에 따라서 달라지는 두 번째 목록을 만들기
2. 데이터 유효성 검사 이중 목록 만들기 - 원리 이해하기
- 첫 번째 목록 : 데이터 유효성 검사의 "목록" 기능을 사용한다.
- 두 번째 목록을 보여 주기 위해서 엑셀의 "이름 정의"와 "Indirect 함수"를 사용한다.
- ① "이름 정의" 기능
- 특정 셀 범위에 이름을 붙이고 셀 주소를 대신해 사용이 가능한 기능이다.
- 즉 "정의된 이름" = "특정 셀 주소"라고 보면 된다.
- ② Indirect 함수
- 셀 주소처럼 생긴 "텍스트"를 "셀 주소형식"으로 변형해 주는 함수이다.
- (텍스트 "B2") ≠ (셀주소 B2) → (Indiret (텍스트 "B2")) = (셀주소 B2)처럼 사용이 가능하게 된다.
3. 유효성 검사 이중 목록 만들기 - 예제
- 1) 첫 번째 목록 만들기 - 데이터 유효성 검사 > 목록
- 메뉴 패스(리본 메뉴) : "데이터" 탭 > "데이터 도구" 그룹 > "데이터 유효성 검사"
- 2) "이름 정의" - "과일", "야채", "육류"
- 간단한 방식 : (이름 정의 범위 선택) > "이름 상자"
- "과일"은 "B3:B5", "야채"는 "C3:C6", "육류"는 "D3:D7"의 범위를 선택해 "이름 상자"에 키보드로 "이름 정의"를 만들자
- 3) 두 번째 목록 만들기 - 데이터 유효성 검사 > 목록
- "데이터 유효성 검사 - 목록"의 "원본"에 입력 수식 : =INDIRECT($B$10)
- 4) 데이터 이중목록 사용하기
- "선택1"의 첫 번째 목록을 선택하게 되면 "선택2"에서는 자동으로 해당하는 두 번째 목록이 보이는 이중 목록 상자가 된다.
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글