[엑셀 2016] 데이터 유효성 검사의 활용 및 사용법

자신이 지정한 대상 외에는 데이터의 입력이 제한되는 데이터 유효성 검사에 대해서 알아보도록 하겠습니다.

 

메뉴의 데이터 항목에 데이터 유효성 검사가 있으며, 데이터 유효성 검사를 누르게 되면

위와 같은 팝업창이 뜨게 됩니다.

 

▣제한대상

제한 대상 설정은 정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자 지정이 있습니다. 모든 값은 제한을 걸지 않는 상태입니다.

 

▣제한 방법

제한 대상을 설정하면 위와 같이 제한 방법을 설정할 수 있다. 제한 방법은 목록과 사용자 지정을 제외하고 모두 공통된 사항이다.

 

▶해당 범위 : 최소값에서 최댓값 사이의 값만 입력할 수 있다. 해당 범위를 벗어나면 에러 메시지가 나온다.

▶제외 범위 : 최소값에서 최댓값 사이의 값을 제외하고 입력할 수 있다. 해당 범위의 값을 입력하면 에러 메시지가 나온다.

▶= : 설정한 값만 입력할 수 있다. 다른 값을 입력하면 에러메시지가 나온다.

▶<> : 설정한 값을 제외하고 입력할 수 있다. 설정한 값을 입력하면 에러 메시지가 나온다.

▶각각 나머지 부호들도 설정한 값보다 초과, 미만, 이상, 이하의 값만 입력이 가능하다.

 

▣설명 메시지

데이터 유효성을 설정한 셀을 클릭하면 설명 메시지에 작성한 내용이 나오도록 할 수 있다.

 

▣정수, 소수점

위와 같이 설정을 하면

 

1에서 10사이의 값만 입력이 되고 다른 값을 입력 시 에러 표시가 나온다.

 

▶정수값을 제한방법의 설정에 따라 데이터 입력에 제한을 둘 수 있다.

▶제한 대상을 소수점으로 하는 경우 정수로 하는 것과 사용 방법은 같고, 숫자를 소수점까지 지정할 수 있는 점만 다르다.

 

▣목록

▶C3셀의 값을 월만 입력이 가능하도록 설정을 해보았다. 원본의 범위는 A1:A12로 지정을 했다. 이 범위의 값들만 입력이 가능하다. 드롭다운 표시는 입력을 직접 하지 않고 콤보박스가 나와서 선택이 가능하도록 하는 것이다.

 

▶위와 같이 특정 목록의 값만 선택이 가능하게 된다.

▶콤보박스가 나오므로 사용하기에 좋은 점이 매우 많다.

 

▣날짜, 시간

▶제한 대상의 날짜, 시간, 텍스트 길이는 설정된 제한 방법의 값만 입력이 가능하다. 기본적인 포맷은 정수, 소수점과 같다.

 

▣사용자 지정

▶수식을 이용하여 제한 설정을 세부적으로 구현할 수 있다.

 

 

▣활용

◆중복값

 

▶수식에 COUNTIF(B:B,B1)=1 을 작성한 예이다.

 

▶3의 값이 중복되므로 입력이 되지 않는다.

▶B10셀을 선택 후 유효성 검사의 설정을 보면 위와 같은 수식으로 되어있는 것을 볼 수 있다. 자기 자신의 셀 값이 B열에서 몇 개 있는지 확인하여 1개이면 입력이 가능하고 아닌 경우 입력이 불가능하다. COUNTIF 수식은 각 셀별로 자동으로 지정이 된다.

 

◆IS함수 연계

데이터 유효성의 수식은 TRUE와 FALSE를 인식하여 TRUE의 값만 입력이 가능하게 해준다. 기본적으로 IS함수들은 조건이 맞으면 TRUE를 반환하므로 이를 응용하여 사용할 수 있다.

-ISBLANK : 빈 셀

-ISERROR : 오류값

-ISTEXT : 텍스트

-ISNUMBER : 숫자

 

▶위와 같이 수식을 작성하면 H5셀은 텍스트만 입력이 가능하다.

 

▶이밖에도 TRUE, FALSE를 반환하는 함수나 IF함수를 사용하면 다양한 형태로 조건을 작성할 수 있고, 그 외의 다른 함수도 연계가 가능하니 자신이 원하는 목적에 따라 응용이 가능하다.

 

◎데이터 유효성 검사의 경우 이미 잘못된 데이터가 있는 경우나 다른 셀의 데이터를 값만 붙여 넣기 하는 경우 적용이 되지 않는다. 유효성 검사를 작성하고 데이터를 새로 입력하지 않는 경우라면 데이터 유효성 검사의 아래에 있는 잘못된 데이터를 눌러서 꼭 확인을 해주는 게 좋다. 데이터 유효성은 입력하는 자료의 정확성이 중요한 업무에서 사용하면 매우 유용하므로 알고 있으면 좋은 것 같다.