이중 유효성 검사 목록 만들기(데이터 유효성 검사, INDIRECT 함수)

이번 강좌는 구독자 OH CH**** 님의 질문을 바탕으로 강좌를 만들어봤습니다. 
먼저 질문 내용을 보겠습니다.

안녕하세요, 한정희 강사님.
질문하기에 앞서 항상 좋은 강의를 해주셔서 정말 감사합니다.
일을 하는 데 있어 강사님의 강의가 큰 도움이 되어 일처리가 한결 더 수월해졌습니다.
정말 감사합니다.

제가 드리고 싶은 질문은, 엑셀의 유효성 검사를 통해 목록을 만드는 경우입니다.
지금 마트 물품 목록을 만들고 있는데요,

...   물품종류     세부종류    물품명   ...
---------------------------------------
...      냉장          어묵    해산어묵 500g   ...
...      냉장          김치    서울김치 1kg     ...
...      냉동          생선       아구 1kg       ...
...      냉동          만두    고향만두 1kg     ...            

이런 식으로 표를 만들고 있습니다.
물품종류는 유효성 검사로 목록을 만들어 냉장과 냉동, 하드웨어 등을 만들었는데,
문제는 세부종류 역시 목록으로 만드려고 하는데 물품종류에 따라 달라지게 만들고 싶습니다.

물품종류 : 마른제품, 냉장제품, 냉동제품, 하드웨어, 기타
- 마른제품 : 냉면, 과자, 라면, 소스 등등
- 냉장제품 : 어묵, 김치, 냉면, 음료 등등
- 냉동제품 : 생선, 어묵, 만두 등등
- 하드웨어 : 학용품, 주방용품 등등

위와 같이 물품 종류를 선택하면 그에 맞는 세부 종류의 목록만이 뜨도록 만들고 싶습니다.
즉 물품종류를 "냉장"으로 선택하면 세부종류는 "어묵", "김치", "냉면", "음료" 등의 목록이 생기고,
"냉동"으로 선택하면 "생선", "어묵", "만두" 등의 목록이 생겨서 유저가 그에 따라 쉽게 고를 수 있도록 하고자 합니다.
(모든 세부종류를 목록으로 만들자니 찾는 데 시간이 꽤 오래 걸릴 것 같아요.)

혹시 이와 관련된 강의가 있거나 블로그글이 있을런지요?
완강은 하긴 했는데 제가 실력이 부족한 탓인지 길이 잘 보이지 않습니다.
이에 대해 답변주시면 정말 감사드리겠습니다.

다시 한 번 늘 좋은 강의 올려주셔서 정말 감사드리고,
날씨가 갑자기 쌀쌀해진 것 같은데 감기 조심하시길 바랍니다.

감사합니다.

질문하는 방법을 소개하기 위해 허락을 받고 이메일 내용 전체를 인용합니다.

 

필요한 부분을 제대로 정리해서 물어보셔서 쉽게 해결방법을 생각할 수 있습니다.

물어보신 내용에 어떻게 답할까 생각하는 시간이 필요한데, 어떨 땐 뭘 물어보는지 이해하는 시간이 휠씬 많이 걸리기도 합니다.

 

이렇게 원하는 내용을 정리하는 것도 배워야 하는 내용 중 하나입니다.

 

시간과 능력이 허락하는 한 많은 분에게 도움을 드리고 싶습니다.

 

---

 

실습 파일 다운로드

이중 유효성검사_실습.xlsx

0.01MB

 

완성 파일 다운로드

이중 유효성검사_완성.xlsx

0.01MB

 

 

질문하신 내용은 여러 분들이 궁금해 하고 원하는 기능으로 공식적인 것은 아니지만 아예 이름까지 있습니다.

바로 '이중 유효성 검사 목록'입니다.

 

먼저 실습 파일을 보겠습니다.

 

거래내역 시트는 사용자가 직접 거래내역을 입력할 시트입니다.

자료 시트는 거래내역 시트를 편리하게 사용할 수 있도록 만드는데 필요한 내용을 입력해 두는 시트입니다.

 

먼저 자료 시트에서 작업을 합니다.

 

물품종류를 1행에 입력하고, 2행부터 세부종류를 적었습니다.

이름 정의를 해야 하는데, 모든 세부종류 개수가 같으면 [수식]-[정의된 이름]-[선택 영역에서 만들기] 기능을 이용해 한 번에 만들 수 있지만, 물품종류별 세부종류가 다르기 때문에 물품종류별로 각각 이름을 정의 하겠습니다.

 

[A1:D1] 셀 범위를 선택한 뒤 수식 입력줄 앞의 이름 상자에 물품종류를 입력하고 <Enter>를 누릅니다.

[A2:A5] 셀 범위를 선택한 뒤 이름 상자에 [A1] 셀 내용인 마른을 입력하고 <Enter>를 누릅니다.

[B2:B5] 셀 범위를 선택한 뒤 이름 상자에 [B1] 셀 내용인 냉장을 입력하고 <Enter>를 누릅니다.

[C2:C4] 셀 범위를 선택한 뒤 이름 상자에 [C1] 셀 내용인 냉동을 입력하고 <Enter>를 누릅니다.

[D2:D3] 셀 범위를 선택한 뒤 이름 상자에 [D1] 셀 내용인 하드웨어를 입력하고 <Enter>를 누릅니다.

 

[수식]-[정의된 이름]-[이름 관리자]를 선택해 정의된 이름을 확인합니다.

 

이제 거래내역 시트로 가서 데이터 유효성 검사를 설정하겠습니다.

 

데이터 유효성 검사를 몇 번째 행까지 설정해야 할지 정확히 알 수 없기 때문에 대략 입력 범위를 1000행까지로 정하겠습니다.

 

[B2:B1000] 셀 범위를 선택하려는데, 직접 마우스로 드래그해서 선택하기는 어려우니 이름 상자에 b2:b1000을 입력한 뒤 <Enter>를 눌러 범위를 선택합니다.

[데이터]-[데이터 도구]-[데이터 유효성 검사]를 누릅니다.

[제한 대상]은 목록, [원본]은 =물품종류를 하고 [확인]을 누릅니다.

이름 상자에 c2:c1000을 입력하고 <Enter>를 눌러 [C2:C1000] 셀 범위를 선택합니다.

[데이터 유효성 검사]를 실행해 [제한 대상]은 목록, [원본]에는 =indirect(b2)를 입력하고 [확인]을 누릅니다.

[B2] 셀에 선택된 값이 없는 경우 '원본은(는) 현재 오류 상태입니다'라는 오류 메시지가 나타나는데, [예]를 눌러 설정을 완료하면 됩니다.

INDIRECT 함수를 이용해 [B2] 셀에 입력된 내용을 정의된 이름에서 찾아 해당하는 이름 범위를 원본으로 사용합니다.

[B2] 셀에 냉장이 입력되어 있다면 냉장 이름 범위를 원본으로 하는 것이죠.

 

[데이터 유효성 검사] 기능이 설정되었습니다.

 

제대로 동작하는지 확인해 보겠습니다.

[B2] 셀에 '냉장'을 선택하고, [C2] 셀에 목록을 확인하면 냉장 물품종류에 해당하는 세부종류만 표시됩니다.

 

이렇게 이중 유효성 검사 기능을 알아보았습니다.

 

그런데 실제 운영할 때 문제가 나타날 수 있습니다.

데이터 유효성 검사 기능은 사용자가 잘못 입력하는 것을 막는 기능인데, 특정 환경에서 막지 못하는 문제가 있습니다.

 

[B2] 셀에 냉장을 선택한 뒤 [C2] 셀에서 어묵을 선택합니다.

다시 [B2] 셀을 선택해 마른을 선택합니다.

이 상태에서 입력을 마무리하면 마른 물품종류에는 없는 어묵 세부종류가 선택된 상태가 됩니다.

 

사용자가 사용방법에 맞게 물품종류, 세부종류 순서로 입력을 할 때는 문제가 없는데, 정의되지 않은 방법으로 사용하면 바르지 않는 자료가 입력될 수 있습니다.

물품종류를 다른 것으로 바꾸면 선택된 세부종류를 초기화할 수 있다면 문제를 해결할 수 있는데, 엑셀 기본 기능으로는 구현할 수 없습니다.

이런 문제가 있을 수 있다는 걸 알고, 제대로 입력되었는지 확인이 필요하다면 입력된 데이터를 검사해야 합니다.

 

 

그리고 자료 시트는 사용자가 수정하거나 볼 필요가 없는 내용을 담고 있어 임의로 변경하면 문제가 생깁니다.

이런 경우 시트를 숨기고 사용자가 수정할 수 없도록 통합문서 보호를 설정할 수 있습니다.

 

엑셀 52강] 통합 문서 보호

강좌 마치겠습니다.

수고하셨습니다.