엑셀 데이터 유효성 검사의 모든것

엑셀 데이터 유효성 검사란 '정해진 형식의 데이터만 입력 가능하도록 제한하는 기능'을 의미합니다. 즉, 잘못된 데이터가 입력되지 않도록 사전에 방지하는 기능입니다.

데이터 유효성 검사는 엑셀로 많은 양의 데이터를 다루는 환경, 특히 여러 개의 엑셀 파일을 직접 취합해야 하는 작업이라면 반드시 숙지해야 할 필수 기능입니다.

여러 엑셀 파일을 취합해 본 경험이 있는 실무자라면 누구나 한 번쯤 비슷한 문제로 어려움을 겪었을 텐데요. 위 예시처럼 숫자만 입력되어야 할 곳에 "2,000원" 또는 "3개" 같은 텍스트가 섞이면 계산 결과로 잘못된 값이 반환됩니다.

이런 상황에서 데이터 유효성 검사를 활용하면 작성자가 원하는 형태의 데이터만 입력 가능하도록 제한할 수 있습니다. 데이터 유효성 검사는 기본적으로 7가지 형식을 지원합니다.

위 7가지 항목 중 '목록'과 '사용자 지정'의 사용법만 잘 익히면 실무에서 발생하는 대부분의 문제를 해결할 수 있습니다.

유효성 검사로 메모(안내메세지) 출력하기

데이터 유효성 검사를 활용하면 '메모' 기능보다 더 깔끔한 안내 메시지를 출력할 수 있습니다.

데이터 유효성 검사로 출력하는 안내 메시지는 셀 오른쪽 상단에 빨간색 표식이 뜨지 않아 더욱 깔끔하며, 마우스 커서를 셀 위에 올리지 않아도 메모를 확인할 수 있다는 장점이 있습니다.

1. 예제파일의 '2' 시트로 이동합니다. B3셀(학생명)을 선택한 뒤 [데이터] – [데이터 유효성 검사]로 이동합니다.
2. '설명 메시지' 탭으로 이동한 뒤, 출력할 안내 메시지의 제목과 내용을 입력합니다.
   제목 : 학생명 입력
   설명 메시지 : 학생명을 입력하세요.

   

3. B3셀을 선택하면 안내 메시지가 출력됩니다.

유효성 검사로 출력될 오류 메세지 문구 수정하기

데이터 유효성 검사 조건에 어긋날 때 출력할 오류 메시지의 문구도 손쉽게 수정할 수 있습니다.

1. 예제파일의 C4:C14 범위를 선택한 뒤, [데이터] – [데이터 유효성 검사]로 이동합니다.
2. '오류 메시지' 탭으로 이동합니다. 오류 메시지의 제목과 내용을 입력합니다.
   제목 : 오류안내
   오류 메시지 : 과목명을 제대로 입력했는지 다시 확인해주세요.

   

3. C5셀을 선택한 뒤 '미술'을 입력합니다. 설정한 오류 메시지가 출력되며 입력이 제한됩니다.

데이터 유효성 검사의 목록 기능을 사용하면 지정한 범위 안의 값만 입력 가능하도록 제한할 수 있습니다. 다만 목록으로 고정된 범위를 입력하면 새로 추가되는 값이 목록으로 반영되지 않는 문제가 발생합니다.

이 문제를 해결하려면 '표 기능'이나 '동적범위'를 활용할 수 있습니다. 이번 강의에서는 보다 쉽게 적용할 수 있는 '표 기능'을 활용하는 방법을 다룹니다. 동적범위에 대한 자세한 사용법은 아래 OFFSET 동적범위 사용법 기초 강의를 참고하시기 바랍니다.

1. 예제파일의 '3' 시트로 이동합니다. G3:G8 범위를 선택한 뒤, [삽입] – [표]를 선택하거나 단축키 Ctrl + T를 눌러 표 만들기 대화상자를 실행합니다. '머리글 포함'을 체크한 뒤 확인을 눌러 표를 생성합니다.
2. [표 디자인] – [표 스타일]에서 표를 원하는 디자인으로 변경합니다.
3. [표 디자인]의 왼쪽 표 이름에서 표 이름을 '과목명'으로 변경합니다.
4. C4:C14 범위를 선택한 뒤, [데이터] – [데이터 유효성 검사]로 이동합니다. [설정] 탭의 '원본' 값으로 아래 수식을 입력합니다.
   =INDIRECT("과목명")

   

5. 오른쪽 과목명 범위에 새로운 과목을 추가합니다. 예제로 '미술'을 추가해보겠습니다.
6. 왼쪽 표에서 목록을 클릭하면 새로운 과목이 실시간으로 반영된 것을 확인할 수 있습니다.

유효성 검사 기능을 이용하면 목록에 있는 값과 다른 오탈자나 잘못된 데이터를 손쉽게 확인할 수 있습니다. 이 기능은 확인할 값이 100개 이내일 때 편리하며, 확인할 값이 많을 경우 COUNTIF 함수를 사용하면 더 효율적입니다.

COUNTIF 함수를 사용해 오탈자를 확인하는 방법은 영상강의 11:22 부터 설명해 두었습니다.

[related_post post_id='2631']

1. 예제파일의 '4' 시트로 이동합니다. B4:B14 범위를 선택한 뒤, [데이터] – [데이터 유효성 검사]로 이동합니다.
2. 제한대상으로 [목록]을 선택한 뒤, 원본 범위로 [G4:G20] 범위를 입력합니다.
3. B4:B14 범위가 선택된 상태에서 [데이터] – [데이터 유효성 검사] – [잘못된 데이터]를 선택합니다.
4. 목록에 포함되지 않은 잘못된 데이터가 빨간색 동그라미로 강조됩니다.

유효성 검사를 이용하면 [한/영]키 입력 없이 한글과 영문 입력 모드를 자동으로 변경할 수 있습니다. 한글과 영어를 번갈아 입력해야 하는 양식에서 작업 효율을 높여줍니다.

1. 예제파일의 '5' 시트로 이동합니다. C4:C14 범위를 선택한 뒤, [데이터] – [데이터 유효성 검사]로 이동합니다.
2. [IME 모드] 탭으로 이동합니다. 담당자명은 한글로 입력되므로 입력기 모드를 [한글]로 변경합니다.
3. 이번에는 D4:D14 범위를 선택한 뒤, 입력기 모드를 [영문]으로 변경합니다.
4. '담당자명'과 '담당자 이메일'을 선택하면 입력 모드가 한글/영문으로 자동 변경됩니다.

데이터 유효성 검사 실전 예제

유효성 검사의 사용자 지정 옵션을 활용하면 보다 다양한 형태로 입력 조건을 제한할 수 있습니다. 이번 강의에서는 사용자 지정 옵션을 활용한 두 가지 실전 예제를 알아봅니다.

1. 예제파일의 '6' 시트로 이동합니다. D4:D14 범위를 선택한 뒤, [데이터] – [데이터 유효성 검사]를 선택합니다.
2. 엑셀 화면 좌측의 '이름상자'에서 활성화된 셀 주소를 확인합니다.
3. 제한대상은 [사용자 지정]을 선택합니다. 수식에는 아래 공식을 입력하며, 공식의 "셀"은 활성화된 셀 주소로 변경합니다.
   =AND(LEFT(셀,3)="010",AND(MID(셀,4,1)="-",MID(셀,9,1)="-"),LEN(셀)=13)

   

   수식	조건 설명
   LEFT(셀,3)="010"	"010"으로 시작되어야 합니다.
   AND(MID(셀,4,1)="-",MID(셀,9,1)="-")	값의 4번째, 9번째 글자는 하이픈(-)이어야 합니다.
   LEN(셀)=13	하이픈을 포함한 총 글자 수는 13자리여야 합니다.

4. 휴대폰 번호를 입력합니다. 잘못된 휴대폰 번호를 입력하면 오류 메시지가 출력되며 입력이 제한됩니다.

1. 예제파일의 '7' 시트로 이동합니다. B6:C16 범위를 선택한 뒤, [데이터] – [데이터 유효성 검사]를 선택합니다.
2. 엑셀 화면 좌측의 '이름상자'에서 활성화된 셀 주소를 확인합니다.
3. 제한대상은 '사용자 지정'을 선택한 뒤, 수식으로 아래 공식을 입력합니다. '셀'은 활성화된 셀 주소로 변경하여 입력합니다.
   =COUNTIF($셀:셀,셀)<2

   

4. 송장번호로 중복값을 입력하면 오류 메시지가 출력되며 입력이 제한됩니다.