엑셀로 데이터 유효성을 확인하자

포스트 목차.

• 01. 데이터 유효성 검사 기능
• 02. 정수범위로 유효성 검사
• 03. 텍스트 길이를 기준으로 유효성 검사
• 04. 목록에 대한 유효성
• 05. 잘못된(유효하지 않은) 데이터 찾기
• 06. 유효성 검사 제거하기

 

 

본 포스트에서는 엑셀 시트를 사용하는 사용자의 잘못된 입력으로 인한 오류를 방지하기 위해서,  엑셀에서 제공하는 데이터 유효성 검사 도구에 대해 소개한다.

 

 

01. 데이터 유효성 검사 기능

데이터 유효성 검사 기능을 실행하기 위해서는 데이터 유효성 검사를 수행할 영역을 선택해야 한다.

 

(1) 데이터 영역 선택

 

 

(2) 데이터 유효성 검사 메뉴의 접근 방법

엑셀에서 제공하는 데이터 관리 기능중에 하나이며 다음과 같은 경로로 접근할 수 있다.

 

데이터 > 데이터 도구 > 데이터 유효성 검사

 

(3)  데이터 유효성 검사 실행

(4) 데이터 유효성 조건 설정

  - 데이터 유효성 대화상자가 나타나고, 가장 먼저 설정 탭이 노출된다. 설정 탭에서는 유효성 검사를 할 유효성 조건에 대해 지정할 수 있다. 

   - 여러형태의 유효성 조건 중 하나를 설정하는 것이 가능하며, 유효성 조건 설정방법에 대해서는 다음 절에서 자세히 다루려고 한다.

(5) 설명 메시지 설정

  설명 메시지 탭으로 이동하여 설명 메시지를 설정해보자.

- 데이터 유효성 검사가 설정되어 있는 셀을 선택했을 때 화면에 나타나는 메시지를 설정하는 것이 가능하다.

- 예를 들어 위와 같이 데이터를 입력하면, 해당 영역을 선택했을 때 아래와 같은 메시지가 노출된다.

 

(6) 오류 메시지 설정

  오류 메시지 탭으로 이동하여 오류 메시지를 설정해보자.

- 스타일은 표기된 아이콘을 나타낸다. 

- 오류 메시지가 노출된 예는 위의 그림과 같다.

 

(7) IME 모드

 IME (Input Method Editor): 언어입력기 모드를 설정할 수 있다.  예를 들어 해당 셀을 영문 모드로 설정하면 해당셀을 선택 시에 IME가 영문 모드로 변경된다. 

 

다음 절부터는 실제 예제를 활용하여 유효성 검사 방법을 익혀보자.

 

02. 정수범위로 유효성 검사

위와 같은 데이터가 있을 때, 점수 영역에 정수 범위의 유효성 검사를 추가하는 것이 가능하다. 여기서는 점수 영역에 0부터 100까지의 정수만 입력가능하게 만들어보자.

 

(1) 점수 영역 선택

(2) 유효성 검사 실행 

 - 제한 대상을 정수로 설정한다.

 

  - 제한 방법은 여러가지가 있으나, 우리는 여기서 0부터 100가지의 정수인지를 확인해야 하므로 "해당 범위"를 선택한다. 참고로 엑셀에서 제한 대상이 정수일 때 제공하는 제한 방법은 아래의 그림과 같다.

   

 - 해당 범위의 최소값에 0, 최대값에 100을 입력한다. 

(3) 오류 메시지

오류 메시지는 다음과 같이 설정한다.

이 것으로 유효성 검사 설정은 완료되었다. 

 

(4) 입력 테스트

셀 B6에 1000을 입력해보자.

입력 시도 시에 다음과 같은 오류 메시지가 노출되는 것을 확인할 수 있다.

 

03. 텍스트 길이를 기준으로 유효성 검사

위와 같은 데이터가 있을 때, A열에 10글자까지의 텍스트만 입력 가능하게 만들어보자.

 

(1) 영역 선택

 

(2) 유효성 검사 실행

 - 제한 대상을 텍스트 길이로 입력한다.

 

- 최대값만 지정하기 위해 <=를 제한방법으로 선택한다.

- 최대값이 10을 지정한다.

(3) 오류메시지 설정

 

(4) 입력테스트

위와 같이 10자가 넘는 내용을 입력 했을 때 다음과 같은 오류 메시지가 뜨는 것을 알 수 있다.

 

 

04. 목록에 대한 유효성

위와 같은 데이터가 있을 때, 플랫폼에 다음과 같은 값만 허용할 수 있게 만들어보자.

PS5, PS4, NS, PC, XBOX , 기타

 

(1) 영역 선택

(2) 유효성 검사 실행

 - 제한 대상을 목록으로 설정한다.

- 공백을 무시하고, 드롭다운 표시를 선택한다.

- 원본에 데이터 입력으로 허용할 목록을 콤마(,)로 구분하여 입력한다.

 

(3) 오류메시지 설정

(4) 입력테스트

목록에 없는 PS3를 강제로 입력해보자.

또한 위에서 설정 시에 드롭다운 표시를 설정했기 때문에, 입력할 때 다음과 같은 드롭다운이 발생한다.

 

 

05. 잘못된(유효하지 않은) 데이터 찾기

유효성 검사를 실행하기 전에 미리 입력되어 있는 잘못된 데이터를 찾아보자.

위의 그림처럼 B5:B7에 잘못된 데이터 PS2가 들어가 있다.

 

(1) 영역 선택

(2) 목록에 대한 유효성 검사를 적용한 후에,  잘못된 데이터를 찾아보자.

데이터 유효성 검사 밑에 있는 잘못된 데이터를 선택한다.

(3) 잘못된 데이터 확인

위의 그림처럼 빨간 동그라미로 체크된다.

 

(4) 유효성 표시 지우기

위에 빨간 동그라미를 지워보자.

잘못된 데이터 밑에 있는 유효성 표시 지우기를 선택한다.

동그라미가 없어진 것을 확인할 수 있다.

 

 

06. 유효성 검사 제거하기

이번에는 적용되어 있는 유효성 검사를 제거해보자.

(1) 제거할 영역 선택

(2) 유효성 검사 실행 후 모두 지우기 선택

 

모두 지우기를 선택하면, 유효성 검사가 제거된다.