[실무 엑셀] 엑셀 데이터 유효성 검사 본래의 기능

이전 게시물에서 엑셀 데이터 유효성 검사를 활용한 콤보 박스(드롭다운 목록) 삽입을 배웠습니다. 이번 게시물에서 이 데이터 유효성 검사 도구가 어디에 필요한 도구인지 언제 사용하는 도구인지를 살펴봅니다. 데이터 유효성 검사가 과연 무엇인지를 살펴보고자 합니다.

데이터 유효성 검사, 쉽게 먼저 말씀 드립니다. 이 기능은 셀에 입력된 값이 삽입 가능한 값인지를 확인하는 도구입니다. 만일 데이터 유효성에 제한을 걸어 두게 되면 사용 가능한 값들만이 해당 셀에 삽입 될 수 있습니다. 이를 예제를 이용하여 배워보겠습니다.

1. 데이터 유효성 검사를 이용한 입력 확인

만일 영화 장르를 구분하여야 할 일이 있고 '멜로' 영화 장르가 한 번은 '로맨스', 한 번은 '멜로', 한 번은 '로맨스/멜로' 등으로 입력이 되었습니다. 동일한 장르가 이렇게 여러 가지 텍스트 값으로 표현이 된다면 나중에 이 삽입 데이터를 활용할 때 또 다른 추가 수정 작업이 불가피합니다. 해서 데이터 유효성 검사를 이용하여 삽입 가능한 멜로 장르의 이름을 붙여 주게 되면 또 다른 이름이 삽입 될 경우는 오류 발생으로 이를 알려줍니다.

1) 데이터 유효성 검사 범위를 설정합니다.
데이터 유효성 검사를 이용할 범위를 설정합니다. C3:C7 의 범위를 선택해 봅니다. 이후 데이터 탭으로 이동하여 "데이터 유효성 검사" 도구를 선택합니다.

[그림1] 엑셀 데이터 유효성 검사 - 선택

2) 데이터 유효성 검사 조건을 설정합니다.
데이터 유효성 검사 도구가 뜨면 설정탭으로 이동하여 유효성 조건을 설정합니다. 먼저 제한 대상을 설정합니다. 이는 제한할 데이터의 종류를 말합니다. 기본값은 "모든값" 입니다. 모든값으로 설정을 하는 것은 데이터 유효성 검사를 하지 않는다는 의미입니다. 정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자 지정 등으로 설정 가능합니다.

- "목록" 은 자주 사용되는 제한 대상입니다. 이는 셀에 입력될 데이터를 목록으로 나열하고 이중에서 선택할 수 있게 하는 것입니다. 이때 원본 데이터 설정은 참조를 사용할 수도 있으며 혹은 원본 영역에 데이터를 콤마(,)로 구분하여 나열하는 방법을 사용할 수도 있습니다.
- "정수" 는 정수가 입력 될 때 제한을 하겠다는 것입니다. 정수를 선택하면 정수를 제한하는 방법들을 선택할 수 있으며 그 제한 방법에 따라 하위 옵션들이 바뀌게 됩니다.
- "소수점" 역시 정수와 동일한 데이터 제한 방법이 사용됩니다.
- "날짜" 와 "시간" 모두 정수와 거의 동일한 하위 설정 방법을 사용합니다. 최소값, 최대값이 시작 날짜와 끝 날짜 등으로 표시되는 것이 다를 뿐입니다.
- "텍스트 길이" 는 문자열의 길이를 제한합니다. 글자수를 체크하는 것입니다.
- "사용자 지정" 은 수식을 입력하여 데이터 유효성을 검사합니다. 입력되는 수식은 논리값을 결과로 반환하는 수식입니다.

[그림2] 엑셀 데이터 유효성 검사 - 제한 대상

진행하고 있는 예제에서는 제한 대상으로 "목록" 을 선택하고 원본은 영화 분류가 나열된 A3:A7 범위를 참조합니다. 확인 버튼을 클릭하면 기본적인 데이터 유효성 설정은 끝납니다.

[그림3] 엑셀 데이터 유효성 검사 - 유효성 조건

- 제한 대상 옆의 "공백 무시" 가 체크 된 상태에서는 데이터 유효성 검사 범위에 어떤 문자도 입력되지 않고 해당 셀을 벗어나는 것을 허용한다는 것입니다. 만일 이 체크가 해제 된 상태에서 입력을 준비하다 공백인 상태 그대로 셀을 벗어난다면 경고창이 뜨게 됩니다.
- 드롭다운 표시가 체크된 상태에서는 해당셀의 오른쪽의 버튼을 클릭하면 목록들이 드롭다운 메뉴로 표시됩니다. 이 옵션이 해제되면 목록의 나열은 없으며 입력불가능한 데이터가 입력된다면 경고창을 띄워 데이터 유효성 검사만이 이루어 집니다.

3) 우리는 이미 C3:C7 의 데이터 유효성 검사를 이용할 범위의 입력이 이루어진 상황에서 이 기능을 사용하고 있기에 그 활용도를 알지 못 할 수 있습니다. C6 셀의 "멜로/로맨스" 를 "멜로" 로 수정 입력해 봅니다. 경고창이 뜨면서 데이터가 잘못 입력되었음을 알려줍니다. 이것이 데이터 유효성 검사 도구의 원래의 기능입니다.

[그림4] 엑셀 데이터 유효성 검사 - 오류

2. 데이터 유효성 검사 삭제

데이터 유효성 검사 기능이 필요하여 사용하였지만 나중에 이 기능으로 제한된 범위 이외의 값을 입력하고 싶다면 어떻게 해야 할까요? 해당 범위를 선택하고 데이터 유효성 검사 기능을 열어 설정을 수정해 주셔도 되고 데이터 유효성 을 지워버리셔도 됩니다.

4) 데이터 유효성 검사가 설정된 C3:C7 의 범위를 선택하고 데이터 유효성 설정 창을 엽니다. 왼쪽 아래의 "모두 지우기" 버튼을 클릭하시고 확인 하시면 해당 범위의 데이터 유효성 검사 기능들은 모두 삭제됩니다.

[그림5] 엑셀 데이터 유효성 검사 - 삭제

5) C6 셀을 선택하고 "멜로/로맨스"를 "멜로"로 수정합니다. 이제 이전과 같은 경고는 볼 수 없을 것입니다. 또 셀 선택시 드롭다운 목록도 볼 수 없을 것입니다.

[그림6] 엑셀 데이터 유효성 검사 - 삭제 확인

3. 잘못된 데이터 기능 사용

이 기능은 데이터 유효성 검사가 설정된 이후에 사용가능한 기능입니다. 1) ~ 3) 을 보면서 C3:C7 의 범위를 데이터 유효성 검사 설정합니다. 데이터 유효성 검사의 조건을 기준으로 잘못된 데이터를 찾는 것입니다.

6) C3:C6 을 선택하고 데이터 탭 > 데이터 유효성 검사 > 잘못된 데이터 를 선택합니다. 아래와 같이 잘못된 데이터에 붉은색 원으로 알려줍니다. 이렇게 잘못된 데이터를 쉽게 찾아 낼 수도 있습니다. 잘못 입력된 데이터를 찾는 복잡한 작업에서는 너무도 유용한 기능이 될 수 있습니다.

[그림7] 엑셀 데이터 유효성 검사 - 잘못된 데이터

4. 데이터 유효성 검사 나머지 탭

7) 설명 메시지 탭
데이터 유효성 검사 기능이 적용되는 범위에 셀 선택 하였을 때 표시되는 설명을 설정할 수 있습니다.

[그림8] 엑셀 데이터 유효성 검사 - 설명 메시지 탭

8) 오류 메시지
오류 메시지를 표시할 것인지, 어느 형태의 오류창을 표시할 것인지, 표시되는 메시지의 내용은 어떻게 할 것인지를 설정할 수 있습니다. 제목과 오류 메시지 설정으로 개인적인 데이터 입력 오류창을 만들 수 있습니다. 오류 메시지 표시를 해제하면 잘못된 데이터가 입력되어도 오류 메시지 창이 표시되지 않습니다.

[그림9] 엑셀 데이터 유효성 검사 - 오류 메시지 탭

이것이 엑셀의 데이터 유효성 검사 도구입니다. 잘못된 데이터의 입력을 막고 잘못 입력된 데이터를 찾아내는 기능이 바로 엑셀의 "데이터 유효성 검사" 의 존재 이유입니다. 여러 실무에서 잘못된 데이터로 어려움을 격고 있다면 이 엑셀의 기능을 이용해 보시기 바랍니다.

[엑셀 실무] 엑셀 데이터유효성 검사 활용, 콤보박스 삽입 방법 :
http://secstart.tistory.com/553

마늘빵.