데이터 유효성 검사(1) - 모든 값, 정수, 소수점, 목록

1. 정의

 

데이터가 정확하게 입력될 수 있도록 데이터의 입력 조건을 제한하는 것입니다.

 

프로그램을 통해서 입력된 데이터라도 여러가지 형태이거나 잘못된 데이터인 경우가 많습니다. 예를 들어 서울특별시를 입력하는 경우 서울, 서울시, 서울특별시 등 여러가지 형태가 가능하고, 사울 또는 서을로 잘못 입력할 수도 있습니다. 이런 경우에 오로지 서울특별시만 입력할 수 있도록 하는 것이 데이터 유효성 검사 기능입니다.

 

데이터 유효성 검사는 데이터 탭의 데이터 도구 그룹에 있습니다.

 

2. 메뉴 구성

 

가. 데이터 유효성 검사 - 제한 대상

데이터 유효성 검사 오른쪽의 꺽기(콤보 상자 버튼)가 아니라 데이터 유효성 검사 글자 부분을 클릭하면

설정, 설명 메시지, 오류 메시지, IME 모드 탭으로 되어 있고,

 

설정 탭에서 제한 대상 아래 모든 값을 클릭하면 모든 값 아래 정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자 지정이 있습니다.

 

나. 데이터 유효성 검사 콤보 상자

데이터 유효성 검사 오른쪽 콤보 상자 버튼을 누르면 데이터 유효성 검사와 잘못된 데이터, 유효성 표시 지우기가 있습니다.

 

다. 제한 방법

제한 방법에는 해당 범위, 제외 범위, =(같다), <>(다르다), >(보다 크다), <(보다 작다), >=(보다 크거나 같다),<=(보다 작거나 같다)가 있습니다.

3. 제한 대상

가. 모든 값

아무런 제한을 두지 않는 것입니다. 다시 말해, 텍스트(문자), 숫자, 날짜, 논리값 등 어떤 것도 입력가능하고, 텍스트의 형태도 제한하지 않습니다.

 

A1셀을 선택하고 데이터 탭, 데이터 도구 그룹의 데이터 유효성 검사를 누른 다음 제한 대상으로 모든 값이 선택된 상태에서 확인 버튼을 누르면

A1에 어떤 값을 넣더라도 에러 메시지가 표시되지 않습니다.

 

나. 정수

정수만 입력가능하고, 문자, 소숫점 이하 입력이 불가능합니다.

 

(1) 최소, 최대값을 숫자로 입력

A1셀을 선택한 상태에서 제한 대상을 모든 값에서 정수로 변경하고 확인 버튼을 누르면

최대 길이(값)와 최소 길이(값)을 입력해야 한다고 합니다.

 

 

확인 버튼을 누르고, 최소값으로 -5000, 최대값으로 1,000,000을 입력한 다음 확인버튼을 누릅니다.

 

그리고, -5001을 입력하면 "유효성 검사 제한에 부합하지 않는다"는 에러 메시지를 보여줍니다.

 

다시 시도 버튼을 누른 후 1000001을 입력하고 엔터 키를 입력하면

또 다시 위 에러 메시지 창이 보입니다. 따라서, 정확한 값을 입력해야만 에러 메시지 창이 표시되지 않습니다.

 

5000을 입력하면 이제 에러 메시지 창 없이 값이 입력됩니다.

 

소수를 포함해서 3.14라고 입력해도 정수가 아니기 때문에 에러 메시지가 표시됩니다. 취소 버튼을 누르면 

 

3.14가 입력되지 않고, 기존 값 그대로 남아 있습니다.

 

그러나, 날짜도 숫자이기 때문에 표시형식을 간단한 날짜로 변경하면

 

(2) 최소, 최대값을 셀 주소로 지정

B1셀에 최소값, C1셀에 최대값이라고 입력하고,

B2셀에 0, C2셀에 5000이라고 입력합니다.

 

그리고, B3셀을 선택한 다음 데이터 유효성 검사 명령을 누른 후 최소값 입력란을 마우스로 클릭한 다음 B2셀을 클릭하고, 최대값 입력 란을 클릭하고 C2셀을 클릭해도 됩니다.

 

그리고, B3셀에 -10이라고 입력하면 에러 메시지가 표시되고, 3000을 입력하면 유효성 조건에 맞으므로 입력됩니다.

 

다. 소숫점

A2셀을 선택한 후 소수점을 선택하면 정수처럼 최소값과 최대값을 입력해야 합니다.

최소값으로 -50, 최대값으로 50을 입력하고 확인 버튼을 누릅니다.

 

이제 A2셀에 3.141592라고 입력하면 입력이 되는데,

 

313.141592라고 수정하면 범위를 벗어나기 때문에 아래와 같이 에러 메시지가 표시됩니다. 취소 버튼을 누릅니다.

 

라. 목록

A3셀을 선택하고, 제한 대상으로 목록을 선택합니다.

그러면 아래와 같이 원본 아래에 셀 주소를 입력할 수 있는데, 목록을 컴마를 이용해 입력할 수도 있습니다.

 

(1) 목록을 컴마로 연결

서울시, 경기도, 강원도, 충청남도, 충청북도 라고 입력하고 확인 버튼을 누르면

 

A3셀 오른쪽에 콤보 상자 버튼이 생깁니다.

 

콤보 상자 버튼을 누르거나 Alt+↓를 누면 서울시부터 충청북도까지 목록이 표시됩니다. 경기도를 클릭하거나 아래 화살표키를 이용해 이동한 경기도를 선택하면

 

경기도가 선택됩니다.

 

A3셀에 경기도가 아니라 경기라고 입력하고 엔터키를 누르면 목록에 없는 값이기 때문에 에러 메시지가 표시됩니다. 따라서, 정확한 값을 입력하도록 할 수 있는 좋은 방법입니다. 취소 버튼을 눌러 입력을 취소합니다.

 

 

(2) 셀 범위를 지정

이미 목록에 대한 데이터가 있거나, 일정 범위에 데이터를 입력한 후 그 범위를 목록으로 지정할 수 있습니다.

 

① A4셀을 선택합니다.

 

② D1셀에서 D5셀까지 서울시, 경기도, 강원도, 충청남도, 충청북도를 입력합니다.

 

③ 원본 아래 칸에 마우스로 클릭해서 커서를 넣거나 오른쪽 화살표 키를 누릅니다.

 

마우스로 끌어서 D1셀에서 D5셀까지 선택합니다.

 

커서를 넣은 상태에서 범위를 선택하면 아래와 같이 데이터 유효성 검사 창이 보입니다.

 

그러나 위 화살표 키 모양을 클릭한 후 범위를 선택하면

범위 지정 창이 작게 보이므로 엔터키를 누르면 위와 같이 커진 데이터 유효성 검사 창이 표시됩니다.

 

④ 데이터 유효성 검사 창에서 확인 버튼을 누르면 A4셀에 콤보 상자 버튼이 생깁니다.

 

⑤ 마찬가지로 Alt+↓를 누르거나, 마우스로 콤보 상자 버튼을 누르면 서울시부터 목록이 표시되므로 이 중에서 원하는 것을 선택하면 됩니다.

 

[목록을 만드는 쉬운 방법]

중복 데이터 지우기 기능을 이용하면 여러 가지 데이터 중에서 고유한 데이터만을 추출할 수 있으므로 쉽게 목록을 만들 수 있습니다.

실습자료.xlsx

0.01MB

위 파일을 열면 아래와 같이 거래처, 상차지, 하차지 등이 표시되는데,

 

C열을 복사해서 위에서 작업 중인 파일의 F열에 붙여 넣습니다.

 

데이터 탭의 데이터 도구 그룹에 있는 '중복된 항목 제거' 명령을 누릅니다.

 

'11개의 고유한 값이 유지됐다'고 하는데, 공백을 포함해야 11개가 됩니다.

 

A5셀을 선택한 상태에서 데이터 유효성 검사 명령을 누른 후 목록을 선택하고, 원본 아래 칸을 클릭한 다음 F3셀에서 F11셀까지 선택해서 입력하고, 확인 버튼을 누릅니다.

 

그러면 A5셀 오른쪽에 콤보 상자 버튼이 생기고 누르면 거래처 목록이 표시됩니다. 그런데 가나다순 정렬이 안되어 있으므로

 

F2셀을 선택한 상태에서 데이터 탭에서 정렬 및 필터 그룹의 '오름차순 정렬'을 누르면 가나다순으로 정렬되며,

 

콤보 상자의 목록도 가나다순으로 표시됩니다.

 

날짜부터는 따로 다루겠습니다.

데이터유효성검사(1).xlsx

0.01MB