엑셀 데이터 유효성 검사, 말은 대단히 거창해 보인다.
쉽게 풀어 설명하면 셀에 내가 정한 값만 넣도록 제한하는 기능이다.
특정 셀에 데이터 유효성 검사를 적용하면 유효한 값 외에 값을 입력하지 못하도록 제한할 수 있다. 유효한 값 외의 값을 입력하면 경고 메세지가 뜨는도록 해서 사용자의 재입력을 유도한다.
하나씩 단계별로 설명하도록 하겠다.
엑셀 데이터 유효성 검사
B2 셀에 1부터 10까지의 정수만 입력 가능하도록 만들어 보자. 우선 데이터 유효성 검사를 적용할 셀 B2를 마우스로 선택한 후에 데이터->데이터 유효성검사로 들어가보자.
제한대상(A): 드롭다운목록을 열어보면 정수부터 사용자 지정까지 다양한 데이터 형식을 선택할 수 있다. 각 형식마다 추가 선택 옵션이 조금씩 달라지고 응용(활용) 방법도 달라진다.
유효성 검사의 응용에 대해서는 나중에 생각하고 우선 제한 대상으로 정수를 선택해보자.
추가적인 설정 옵션도 그림과 같이 설정한 후에 확인을 누른다.
그리고 해당 셀(B2)에서 11을 입력해보자.
입력 가능한 범위를 1부터 10까지의 정수로 제한했기 때문에 유효한 숫자가 아니라고 경고 창이 뜨는 것이다. 다시 시도를 눌러 유효한 값을 입력하면 경고창은 뜨지 않는다.
엑셀에서 데이터 유효성 검사를 활용하는 방법은 다양하다. 그 중 드롭다운 목록을 만드는 것은 데이터 유효성 검사를 활용한 대표적인 예다. 드롭다운 목록에 대해서는 다음 링크에 아주 자세히 설명되어 있다.
- 활용 예 - 지출비용 상한선 정하기
아주 간단한 예지만 짚고 넘어갈 문제가 있다.
지출 합계의 상한선을 990,000으로 정했다고 했을 때 데이터 유효성 검사를 해야할 셀의 범위로 ①과 ② 중에 어떤 범위를 선택해야 할까? 합계를 제한하는 것이니 ②번이라고 생각할 수도 있겠으나 ②번 셀에는 우리가 값을 직접 입력하는 것이 아니므로 ①번 셀 범위에 유효성 검사를 적용하는 것이 맞다. 데이터 유효성 검사는 값을 입력한 셀에 대해서만 수행되기 때문이다.(정확히 말하면 값을 입력하고 엔터를 칠 때 해당 셀에 설정된 데이터 유효성 검사가 수행된다.)
위 표에서 신발, 식비, 등등과 같은 지출 내역을 ①번 범위에 추가적으로 입력할 것이므로 ①에 유효성 검사를 적용해야할 것이다. 먼저 ①번 범위를 선택한 후에 데이터 유효성 검사 창을 띄운다.
제한 대상(A): 를 사용자 지정으로 해 놓으면 수식을 입력하는 칸이 나오는데 다음 수식을 입력하도록 하자.
=$C$13<$E$3
$C$13 는 합계에 해당하는 셀이다. 반드시 절대 주소로 입력해야 한다.
$E$3 는 상한선(990,000)에 해당하는 값이 입력되어 있는 셀의 주소다. 역시 절대 주소록 입력해야 한다.
이 두 셀의 값을 비교해서 TRUE를 반환하면 데이터 유효성 검사를 통과하는 것이고 FALSE를 반환하면 유효성 검사를 통과하지 못하고 경고 메세지가 뜬다.
추가로 침대를 구입해서 지출 비용으로 700000을 넣으려 하자 유효성 검사 제한에 걸려서 이대로는 입력을 못한다. 그런데 이미 침대를 구매했으므로 어떻게든 입력을 해야할 것이다. 이 경우 오류 메세지의 수위를 낮출 수 있다.
- 오류 메세지 설정방법
데이터 유효성 검사 설정 창을 띄운 후에 오류메세지 탭에서 스타일을 경고로 바꿔보자.
이제 침대 구입비용을 입력하면 노란색 경고 창이 뜨는데 예(Y)를 클릭하면 그대로 입력이 가능하다.