엑셀을 이용하여 문서를 작성할 때 입력되는 내용이 정해진 포맷에 맞는지 확인해야 하는 경우가 종종 발생합니다. 바로 이러한 확인 작업을 유효성 검사라고 합니다. 엑셀과 한셀 등 사용 가능한 대부분의 스프레드시트 프로그램에서 유효성 검사 기능을 제공하고 있습니다. 엑셀에서는 데이터 유효성 검사라는 이름의 셀 값을 사용자가 지정한 포맷에 맞는지 자동으로 확인해주는 기능을 통하여 유효성 검사를 실시할 수 있습니다. 이 글에서는 엑셀에서 셀 값의 유효성을 검사해야 하는 이유와 그 방법에 대하여 알아볼 것입니다.

엑셀 사용 설명서 다른 글
2017/07/29 [엑셀] 사용설명서 1. 조건부서식 by Walter Erzsamatory

데이터 유효성 검사가 필요한 이유

엑셀을 이용하여 만들 수 있는 문서의 종류는 매우 많습니다. 하지만 엑셀을 이용하여 만들어지는 문서는 하나의 목록을 구성하고 있는 경우가 대부분입니다. 또는 다음 글에서 설명했던 것과 같이 각각의 셀에 정해진 형태의 값이 입력되어야 하는 문서는 엑셀을 이용하여 만드는 것이 매우 현명한 선택입니다. 한글이나 워드와 같은 워드프로세서 프로그램을 이용하여 이와 같은 문서를 동일하게 만든다는 것은 거의 불가능에 가깝습니다. → 2015/02/19 주식 및 펀드의 투자 성과를 한 눈에 알아볼 수 있도록 관리해주는 투자수익률 관리 엑셀 파일 by Walter Erzsamatory

엑셀이나 한셀과 같은 스프레드시트 프로그램에서 데이터 유효성 검사가 필요한 가장 큰 이유는 예상할 수 있는 오류의 발생을 방지할 수 있기 때문입니다. 예를 들어, 아래와 같은 목록 형태의 문서를 만든다고 할 때 필요한 데이터 유효성 검사 종류는 다음과 같습니다. 번호 셀에 대한 정수 유효성 검사, 생년월일, 마지막 방문일 셀에 대한 날짜 유효성 검사, 소속 셀에 대한 데이터 유효성 검사입니다. 소속 셀에 대한 데이터 유효성 검사는 드롭다운 목록을 이용하면 됩니다.

만약 번호 셀에 숫자가 아닌 알파벳을 입력한다면 추후에 데이터를 분석할 때 오류가 발생하겠죠? VLOOKUP 함수를 이용하여 데이터를 검색하려고 하는데 번호 셀에 이상한 값이 있다면 분명히 오류가 발생할 것입니다. 이렇게 문서 작성자가 예상할 수 있는 오류의 발생을 방지하기 위하여 데이터 유효성 검사 기능을 이용하는 것입니다. VLOOKUP 함수 사용 방법에 대해서는 다음 글을 참고하십시오. → 2017/10/17 [엑셀] 엑셀에서 자주 사용되는 함수 정리 – VLOOKUP, HLOOKUP by Walter Erzsamatory

물론, 문서 작성자 본인이 직접 해당 문서를 수정한다면 아무런 문제가 없습니다. 왜냐하면 어떤 셀에 어떤 값이 들어가야 하는지 이미 잘 알고 있기 때문입니다. 하지만 위와 같은 목록 형태의 문서는 한 사람에 의하여 작성되는 경우는 드물고 여러 사람에 의하여 수시로 수정됩니다. 따라서 이 글에서 설명하는 데이터 유효성 검사가 필요한 것입니다. 데이터 유효성 검사 기능을 이용한다면, 여러 사람이 함께 수정하는 문서임에도 문서의 동일한 양식과 형태를 유지할 수 있게 됩니다.

데이터 유효성 검사 – 정수 입력 여부 확인하기

엑셀의 데이터 유효성 검사 기능은 사용자가 지정한 셀에 입력되는 값을 제한하기 위하여 사용됩니다. 지금부터 설명하는 정수 데이터 유효성 검사는 선택된 셀에 입력된 값이 정수인지, 정수가 맞다면 해당 값이 지정된 범위 내에 위치하고 있는지를 확인할 때 사용됩니다. 이 글에서 사용되는 양식은 위에서 보았던 그 양식과 동일합니다. 이 양식에서는 번호 셀에 정수 데이터 유효성 검사를 적용시킬 수 있을 것입니다.

아래와 같이 데이터 유효성 검사를 실시할 셀을 선택한 후 엑셀 리본 메뉴 중 [데이터] – [데이터 유효성 검사]를 눌러 데이터 유효성 검사 설정 창을 띄웁니다. 제한 된 영역의 셀을 선택할 수도 있고, 아래의 스크린샷과 같이 번호에 해당되는 모든 열을 선택할 수도 있습니다. 지정된 위치에서부터 가장 하단까지의 셀을 선택하려면 [Ctrl] + [Shift] + ↓ 키를 동시에 누릅니다. 아래의 경우에는 B5 셀에서 세 개의 키를 동시에 눌렀습니다.

번호 셀에는 1 이상의 모든 정수가 입력될 수 있어야 합니다. 따라서 제한 대상은 정수로 하고 제한 방법은 어느 수준의 이상을 뜻하는 >= 등호를 선택합니다.

마지막으로 최소값에는 1을 입력하여 1 이상의 모든 정수가 입력될 수 있도록 하면 됩니다. 위와 같이 설정함으로써 번호 셀에는 1 이상의 정수만 입력할 수 있게 됩니다. 소수가 입력되거나 알파벳, 한글이 입력되는 경우에는 오류 창이 나타나며 값 수정이 거부됩니다. 잘못된 값을 입력했을 때 나타나는 오류 메시지는 사용자가 직접 수정할 수 있습니다. 메시지 내용의 수정 방법은 뒤에서 자세하게 설명하도록 하겠습니다.

데이터 유효성 검사 설정 창을 보면 공백 무시 옵션이 있는 것을 확인할 수 있습니다. 이 옵션을 사용하면 데이터 유효성 검사를 실시하는 것으로 지정된 셀에 아무런 데이터가 없는 경우 데이터 유효성 검사를 실시하지 않게 할 수 있습니다. 위의 경우에는 B8 셀에서부터는 데이터 유효성 검사가 실시되지 않습니다. 왜냐하면 B7 셀까지만 데이터가 있기 때문이죠.

데이터 유효성 검사 – 날짜 입력 여부 확인하기

엑셀에서 문서를 작성할 때 날짜는 매우 중요합니다. 왜냐하면 대부분의 문서에서 날짜는 데이터 분석의 기초 자료로 사용되기 때문입니다. 엑셀에서 표현할 수 있는 날짜의 형태는 매우 많지만, 엑셀 내부적으로는 단 두 가지의 방법으로 날짜를 표현합니다. 엑셀에서는 다음과 같은 형태의 값을 날짜를 인식할 수 있으며, 이렇게 인식된 날짜는 다른 날짜 포맷으로 변환할 수도 있습니다.

• 2015-04-04 → 2015년 4월 4일 토요일
• 2015/04/04 → 2015년 4월 4일 토요일

만약 사용자가 2015.04.04 의 형태로 날짜를 표현하려고 한다면 엑셀에서는 이 형태를 날짜로 인식할 수 없습니다. 날짜를 입력하고자 한다면, 반드시 위와 같은 형태의 값을 이용해야 합니다. 엑셀에서 날짜를 입력하는 방법에 대하여 알아봤으니, 이제는 본론으로 돌아가서 날짜 유효성 검사를 하는 방법에 대하여 알아보도록 하겠습니다.

가장 먼저, 날짜 유효성 검사를 적용할 셀을 선택한 후 리본 메뉴의 [데이터] – [데이터 유효성 검사] 메뉴를 눌러 데이터 유효성 검사 설정 창을 띄웁니다. 아래와 같이 제한 대상을 날짜로 지정함으로써 선택된 셀에 날짜 형태의 값만 입력되도록 강제할 수 있습니다. 이렇게 날짜 유효성 검사를 적용시킨 셀에는 2015.04.04 와 같이 엑셀에서는 문자열로 인식해버리는 형태의 날짜는 입력할 수 없습니다.

날짜 유효성 검사는 생일의 유효성을 검사할 때 많이 사용될 것입니다. 위의 예제에서도 생년월일 값을 검증할 때 이 기능을 이용합니다. 일단 제한 대상은 날짜를 선택하고, 제한 방법은 어느 수준의 이하를 나타내는 <=를, 끝 날짜에는 오늘의 날짜를 의미하는 수식인 =TODAY()를 입력합니다. TODAY() 함수는 엑셀에서 오늘의 날짜를 반환하는 함수로 사용됩니다. 사용자가 일일이 오늘의 날짜를 엑셀에 입력할 필요 없이 엑셀에서 자동으로 오늘의 날짜를 지정해줍니다. 매우 편리한 함수입니다.

위와 같이 지정하면, 생년월일 셀에는 오늘의 날짜를 넘어서는 날짜 값을 입력할 수 없게 됩니다. 즉, 오늘의 날짜가 2015년 4월 4일이라고 가정한다면, 날짜 셀에 2015-05-05 를 입력하는 것은 금지됩니다. 이렇게 날짜 유효성 검사를 설정함으로써 날짜에 대한 정확한 검증이 가능해집니다.

데이터 유효성 검사 – 목록을 이용한 검증

엑셀 문서를 작성할 때 사용자가 지정한 값 중에서 하나를 선택하여 입력해야 하는 경우가 종종 발생합니다. 이 때에도 역시 엑셀의 데이터 유효성 검사 기능을 이용하면 됩니다. 데이터 유효성 검사 중에서 목록 유효성 검사 기능을 이용하면 됩니다.

위 데이터에서는 소속 셀에 목록 유효성 검사 기능을 적용할 수 있습니다. 왜냐하면 회원의 소속은 일정하게 정해져 있기 때문입니다. 예를 들면, 초급반, 중급반, 고급반, 응용반 등과 같이 말이죠. 이 셀에 초급반, 중급반 등과 같이 이미 정해진 값만 입력할 수 있게 하려면 목록 유효성 검사를 이용해야 합니다.

목록 유효성 검사를 적용할 셀을 모두 선택한 후, 리본 메뉴에서 [데이터] – [데이터 유효성 검사] 메뉴를 눌러 데이터 유효성 검사 설정 창을 띄웁니다. 제한 대상에서 목록을 선택하면 목록 유효성 검사가 적용됩니다. 이제 사용자가 만들어 놓은 원본 목록을 시트에서 지정하면 해당 목록에 있는 값만 지정된 셀에 입력할 수 있게 됩니다. 원본 목록이란 아래의 스크린샷에서 볼 수 있는 빨간 테두리 안에 있는 목록입니다. 이 목록 표는 데이터 유효성 검사 설정을 할 때에만 사용되어야 하고, 그 이후에는 열 숨김 처리를 해야 깔끔한 문서 작성이 가능해집니다.

원본 목록 셀을 지정할 때에는 위의 스크린샷에서 볼 수 있는 것과 같이 하단의 모든 셀까지 지정하는 것이 좋습니다. 이렇게 지정하는 경우에는 새로운 소속이 추가되었을 때 별도의 설정 없이 자동으로 유효성 검사 목록에 신규 소속이 추가되도록 할 수 있습니다. 이 때에는 반드시 공백 무시 옵션에 체크를 하여 컴퓨터 자원 낭비를 막아야 합니다. 드롭다운 표시 옵션의 체크를 해제하면 지정된 셀에 커서를 두었을 때 드롭다운 목록이 표시되지 않습니다. 사용자가 직접 지정된 값을 입력해야 하죠.

위와 같이 목록 유효성 검사를 지정하면 아래와 같이 해당 셀에 커서를 두었을 때 드롭다운 메뉴가 나타나게 됩니다. 드롭다운 메뉴에 나타나는 값만 입력할 수 있는 것입니다. 드롭다운 메뉴가 나타나게끔 한다면 사용자가 값을 입력하는 것이 한결 수월해질 것입니다.

데이터 유효성 검사 – 설명 메시지 띄우기

강제적으로 값을 검사하고 제한하는 것도 중요하지만, 사용자가 올바른 값을 입력할 수 있도록 유도하는 것도 중요합니다. 엑셀 데이터 유효성 검사의 설명 메시지 띄우기 기능은 사용자가 셀을 선택하였을 때, 해당 셀에 어떠한 값을 입력해야 하는지 설명하는 메시지를 표시하는 기능입니다. 메시지의 내용은 사용자가 자유롭게 꾸밀 수 있습니다.

이 기능은 아래와 같이 마지막 등록일 셀을 선택할 때마다 어떠한 값을 입력해야 하는지 사용자가 쉽게 이해할 수 있도록 설명 메시지를 띄우는 기능입니다. 이렇게 메시지를 띄움으로써 완성된 엑셀 양식을 처음 사용하는 사람도 해당 양식을 쉽게 이용할 수 있게 됩니다. 예를 들면, 매우 복잡하게 구성되어 있는 엑셀로 만들어진 가계부에 이 기능을 이용한다면 초보자도 가계부를 쉽게 이용할 수 있을 것입니다.

위에서와 같은 방법으로 메시지가 나타나길 원하는 영역을 선택한 후 리본 메뉴의 [데이터] – [데이터 유효성 검사] 메뉴를 눌러 데이터 유효성 검사 설정 창을 띄웁니다. 여기에서 [설명 메시지] 탭을 누르면 설명 메시지 기능을 이용할 수 있습니다. 나타날 메시지의 제목과 내용을 입력하고 확인 버튼을 누르면 됩니다.

이렇게 설정하였을 때 나타나는 메시지의 디자인은 엑셀의 메모와 유사합니다. 하지만 메모의 기능보다는 좀 더 유용하다고 할 수 있습니다. 왜냐하면 데이터 유효성 검사의 설명 메시지 기능은 넓은 영역에 지정하는 것이 가능하기 때문입니다. 메모 기능은 각각의 셀마다 메모를 일일이 지정해야 하는 번거로움이 있습니다. 그리고 진한 노란색의 메모보다는 사용자가 읽기 쉽게 파스텔 톤의 색상을 가지고 있는 메시지 창이 나타납니다.

데이터 유효성 검사 – 오류 메시지 띄우기

위에서 보았던 기능은 셀을 선택했을 때 무조건 나타나는 메시지를 설정하는 기능이었습니다. 지금부터 설명하는 기능은 데이터 유효성 검사에서 오류가 발생했을 때에만 나타나는 메시지 창을 설정하는 기능입니다. 위에서 보았듯이 데이터 유효성 검사 설정을 벗어나는 값을 입력하는 경우에는 엑셀 오류 창이 나타납니다. 이 오류 창의 내용을 사용자가 직접 지정할 수 있는 것입니다.

오류 메시지 기능을 이용할 셀을 선택한 후 리본 메뉴에서 [데이터] – [데이터 유효성 검사] 메뉴를 눌러 데이터 유효성 검사 설정 창을 띄웁니다. 여기에서 [오류 메시지] 탭을 누르면 아래와 같은 창이 나타납니다. 메시지와 함께 나타날 아이콘을 지정하고, 메시지 창의 제목과 내용을 입력하고 확인 버튼을 누르면 설정이 완료됩니다.

사용자가 지정한 데이터 유효성 검사에서 오류가 발생하는 경우에는 다음과 같이 오류 메시지가 나타납니다. 이 때 나타나는 오류 메시지는 사용자가 직접 지정한 내용으로 구성됩니다. 생년월일 셀에서 발생할 수 있는 오류는 잘못된 형태의 날짜를 입력했을 때 오늘 이후의 날짜를 입력했을 때 발생합니다. 아래와 같이 오류 메시지를 띄우는 경우에는 사용자가 생년월일 셀에 어떠한 내용을 입력해야 하는지 쉽게 알아볼 수 있을 것입니다.

설명 메시지 기능과 함께 오류 메시지 기능을 사용한다면 좀 더 완벽에 가까운 문서를 작성할 수 있을 것입니다.

이렇게 데이터 유효성 검사 설정을 적용한 후 시트 잠금이나 통합시트 잠금을 통하여 일반 사용자가 속성 변경을 못 하게 하는 경우에도 데이터 유효성 검사는 이루어집니다. 시트 잠금을 했을 때 비로소 데이터 유효성 검사의 기능이 제대로 된 효과를 발휘할 수 있는 것입니다. 사용자가 속성을 수정할 수 없게 잠긴 엑셀로 만들어진 대부분의 파일에는 데이터 유효성 검사 기능이 적용되어 있습니다. 가장 대표적인 것으로는 엑셀 가계부가 있겠죠.

마무리

지금까지 엑셀에서 데이터 유효성 검사 기능을 이용하는 방법에 대하여 알아보았습니다. 이 글에서 직접적으로 언급하지는 않았지만, 데이터 유효성 검사 기능은 개인적으로 사용되는 엑셀 파일보다는 공용적인 성격이 강한 엑셀 파일에서 주로 사용되는 기능입니다. 또는 개인적으로도 완벽한 엑셀 파일을 만들겠다는 욕구가 강한 사용자라면 데이터 유효성 검사 기능을 사용할 것입니다.

입력되는 데이터의 변조를 방지하고, 추후에 이루어지는 데이터 분석에 있어서 발생할 수 있는 오류를 사전에 차단하는 기능이 데이터 유효성 검사 기능의 가장 큰 목적입니다. 특히 데이터 분석에 VLOOKUP과 HLOOKUP 함수가 사용된다면 데이터 유효성 검사는 필수적으로 사용되어야 합니다.