이번 강좌는 함수의 사용법은 아닙니다만 함수와 같이 사용하면 효과적인 기능을 설명하려 합니다. 이전 강좌에서 알아본 VLOOKUP과도 종종 같이 사용하는 기능이니 VLOOKUP 함수에 이어서 알아보는 것이 좋을 것 같습니다.
함수의 가장 큰 장점은 데이터가 바뀌어도 함수를 통한 결과가 즉시 반영된다는 자동화였습니다. 그럼, 데이터를 손쉽게 바꿀 수 있다면 그 장점이 더 극대화되겠죠. '데이터 유효성 검사'는 바로 그런 작업을 가능하게 해 줍니다.
우리가 어떤 웹사이트에 회원 가입할 때 아이디와 패스워드를 입력하면 "8자 이상 영문 대소문자와 특수문자를 사용해 주세요."라는 메시지를 볼 수 있습니다. 전화번호에 한글을 입력하면 "숫자만 사용해 주세요"라는 메시지도 볼 수 있죠. 이런 것들이 바로 "데이터 유효성 검사"입니다. 입력란에 사용할 수 있는 문자를 제한해 둔 것이죠. 숫자만 사용하게 한다든가, 8자 이상 사용하게 한다든가 하는 것들입니다. 그렇지 않은 문자를 사용하면 오류를 발생하여 아예 입력할 수 없게 제한한 것입니다.
엑셀의 각 셀도 마찬가지로 입력할 수 있는 내용을 제한할 수 있습니다.
엑셀을 시작하고 새 통합 문서를 열어 주세요.
- 입력값을 제한하고자 하는 셀(B2)을 클릭 선택합니다.
- 데이터 > 데이터 유효성 검사를 클릭합니다. => 데이터 유효성 대화상자가 나옵니다.
(제한 대상은 기본 모든 값입니다. 모든 값을 입력할 수 있게 제한하겠다는 뜻이니 제한하지 않는다는 얘기입니다.) - 제한 대상을 클릭하여 모든 값에서 정수로 바꿉니다. => 대화상자 아랫부분에서 정수를 어떻게 제한할 것인지 설정할 수 있게 바뀝니다.
(정수만 입력할 수 있게 제한 한 것입니다. 이제 문자는 입력할 수 없게 된 것입니다.) - 최소값을 10, 최대값을 20으로 입력합니다. (정수 중 10부터 20까지만 사용할 수 있게 제한한 것입니다.)
- 최종, 확인 버튼을 클릭합니다.
사용법은 간단합니다. 제한할 셀을 선택하고 사용할 수 있는 사항으로 설정하면 끝입니다. 이제 B2셀은 정수 10~20까지만 입력할 수 있습니다. B2셀에 문자나 10~20이 아닌 다른 숫자를 입력해 보세요.
유효성 검사 제한에 부합하지 않는다는 오류 메시지가 나오고 입력할 수 없게 됩니다.
정수로 제한 했지만, 소수점, 날짜, 시간 등으로도 제한할 수 있습니다. 대부분은 한번 해보시면 금방 하실 수 있습니다.
사실 현실에서 이렇게 셀을 제한하는 경우는 많지 않습니다. 그렇지만 제한 대상의 목록은 의외로 많이 사용합니다. 왜냐하면 입력 대상을 선택 박스로 처리할 수 있기 때문입니다. 예제 파일을 열고 사용해 보겠습니다.
- 입력값을 제한하고자 하는 셀(H5)을 클릭 선택합니다.
- 데이터 > 데이터 유효성 검사를 클릭합니다. => 데이터 유효성 대화상자가 나옵니다.
- 제한 대상을 목록으로 변경합니다. => 대화상자 아랫부분에서 어떤 목록으로 제한할 것인지 설정할 수 있게 바뀝니다.
- 원본 입력란을 클릭합니다.
- H5셀에는 원본의 모델명만 입력할 수 있어야 합니다. 원본의 모델명 항목의 모든 데이터를 범위로 선택하여 참조합니다. => 선택 범위가 절대 참조됩니다.
- 최종 확인 버튼을 클릭합니다.
결과, 선택했던 H5셀 우측 옆에
버튼이 생깁니다.
이렇게 제한 대상을 목록으로 하고 목록화 대상을 범위로 참조하면 선택했던 H5셀에는 참조한 범위의 값만 입력할 수 있게 됩니다.
더 좋은 것을 입력할 수 있는 것들이 명확하게 제한되었기 때문에 굳이 입력하지 않더라도 클릭하여 선택할 수 있게 된다는 것입니다.
그림처럼 모델명을 바꾸고 싶다면 입력할 필요 없이 클릭하여 선택할 수 있게 된 것입니다.
이제 제품명과 보유 수량을 VLOOKUP 함수를 사용해 찾아주면 됩니다. 이건 이미 해본 사항이니 건너뛰겠습니다. 직접 해 보세요.
잘 안된다면 아래 강좌를 참고하고 만들어 주세요.
모델명을 클릭 선택하여 변경하면 자동으로 제품명과 보유 수량이 변경되는 것을 확인할 수 있습니다.
추가로 말씀드리면 제품명과 보유 수량을 VLOOKUP으로 만들 때, 지금은 자동 채우기를 할 것이 아니므로 table_array도 굳이 절대 참조할 필요가 없습니다.
의견글 입니다.
()
