엑셀 데이터 유효성 검사 완벽 정리 :: 기초 입문 10강

엑셀 데이터 유효성 검사의 기초부터 실전 응용 예제까지 유효성 검사의 모든 것을 단계별로 알아봅니다.

홈페이지 » 엑셀 데이터 유효성 검사 완벽 정리 :: 기초 입문 10강

엑셀 데이터 유효성 검사 완벽 정리 :: 엑셀 기초 입문 10강

엑셀 데이터 유효성 검사의 모든것 목차 바로가기
영상 강의

예제파일 다운로드

강의에 사용된 기초 함수 목록
선택된 범위에서 하나의 조건을 만족하는 셀의 개수를 계산합니다.
텍스트 문자열의 처음부터 지정한 개수의 문자를 반환합니다.

데이터 유효성 검사란 무엇인가요?

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

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

엑셀 데이터 계산 결과 오류
잘못된 형식의 데이터가 섞여 있으면 잘못된 계산 결과가 나옵니다.

여러개의 엑셀 파일을 취합해 본 경험이 있는 실무자라면, 누구나 한번쯤은 아래와 같은 문제로 고생한 적이 있으실텐데요. 위와 같이 숫자만 입력되어야 할 곳에 "2,000원" 또는 "3개" 등의 텍스트가 섞이게 되면 계산 결과로 옳바르지 않은 값이 반환됩니다.

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

유효성 검사설명예제
정수정수만 입력 가능합니다.1~31사이 정수
소수점소수 또는 정수만 입력 가능합니다.0.5보다 작은 소수
목록지정한 범위(또는 직접 입력한 항목)의 값만 입력 가능합니다."사과","배","포도"
날짜특정 범위안의 날짜만 입력 가능합니다.2020/01/01~2020/12/31
시간시간만 입력 가능합니다.08:00~18:00
텍스트 길이정해진 길이의 텍스트만 입력 가능합니다.3글자 이상의 텍스트
사용자 지정수식을 만족하는 조건의 값만 입력 가능합니다.LEFT(셀,1)="가" --> 첫글자가 '가'로 시작하는 문자

위 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("과목명")

    데이터 유효성 목록 동적범위
    데이터 유효성 검사의 목록으로 INDIRECT 함수를 입력합니다.
  5. 오른쪽 과목명 범위에 새로운 과목을 추가합니다. 예제로 '미술'을 추가해보겠습니다.

    새로운 과목 추가
    표에 새로운 과목을 추가합니다.
  6. 이후 왼쪽 표에서 목록을 클릭하면 새로운 과목이 실시간으로 반영된 것을 확인할 수 있습니다.

    새로운 값 실시간 반영
    유효성 검사 목록으로 새로운 값이 실시간으로 반영됩니다.

유효성 검사로 오탈자/잘못된 데이터 빠르게 검색하기

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

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

  1. 예제파일의 '4' 시트로 이동합니다. B4:B14 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다.

    엑셀 데이터 유효성 검사 이동
    잘못된 데이터를 확인할 범위를 선택한 뒤, 데이터 유효성 검사로 이동합니다.
  2. 제한대상으로 [목록]을 선택한 뒤, 원본 범위로 [G4:G20] 범위를 입력합니다.

    유효성 검사 잘못된 데이터 범위
    제한대상은 목록으로, 원본 범위로 G4:G20 범위를 선택합니다.
  3. B4:B14 범위가 선택된 상태로, [데이터] - [데이터 유효성 검사] - [잘못된 데이터]를 선택합니다.

    엑셀 데이터 유효성 검사 잘못된 데이터
    범위가 선택된 상태에서, 데이터 유효성 검사 - 잘못된 데이터를 클릭합니다.
  4. 목록에 있지 않은 잘못된 데이터가 빨간색 동그라미로 강조됩니다.

    잘못된 데이터 강조
    목록에 포함되지 않은 잘못된 데이터가 빨간색 동그라미로 표시됩니다.

유효성 검사로 키보드 한/영 자동 변환하기

유효성 검사를 이용하면 [한/영]키 입력없이 자동으로 한글과 영문 입력 설정을 변경할 수 있습니다. 이를 통해 엑셀로 한글과 영어를 번갈아가며 입력할 때 보다 편리하게 작업할 수 있습니다.

  1. 예제파일의 '5' 시트로 이동합니다. C4:C14 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다.

    엑셀 데이터 유효성 검사 이동
    데이터 - 데이터 유효성 검사로 이동합니다.
  2. [IME 모드] 이동합니다. 담당자명은 '한글'로 입력되므로, 입력기 모드를 [한글]로 변경합니다.

    엑셀 데이터 유효성 검사 IME 모드 한글
    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' 시트로 이동합니다. D6:D16 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]를 선택합니다.

    엑셀 데이터 유효성 검사 이동
    중복값 입력을 방지할 범위를 선택한 뒤, '데이터 유효성 검사'로 이동합니다.
  2. 엑셀 화면 좌측의 '이름상자'에 활성화 된 셀 주소를 확인합니다.

    엑셀 활성화된 셀 주소 확인
    선택된 범위에서 활성화된 셀 주소를 확인합니다.
  3. 제한대상은 '사용자 지정'을 선택한 뒤, 수식으로 아래 공식을 입력합니다. '셀'은 활성화된 셀 주소로 변경하여 입력합니다.
    =COUNTIF($셀:셀,셀)<2

    엑셀 중복값 입력 방지 데이터 유효성 검사
    제한 대상으로 사용자 지정을 선택한 뒤, 수식을 입력합니다.
  4. 송장번호로 중복값을 입력하면 오류메세지가 출력되며 입력이 제한됩니다.

    엑셀 중복값 입력 방지 완료
    중복값을 입력하면 오류메시지가 출력되며 입력이 제한됩니다.
5 4 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
8 Comments
Inline Feedbacks
View all comments
SEANPAUL
SEANPAUL
2020년 8월 16일 7:05 오후
게시글평점 :
     

언제나 자세한 설명 및 강의 감사해요

lostark
lostark
2020년 8월 22일 4:13 오후
게시글평점 :
     

귀한 강의 감사합니다. 제가 쭉 잘 따라오다가 마지막에서 막혔습니다 ㅜㅜ 영상을 반복해서 봐도 모르겠네요.. 마지막에 countif함수를 가지고 현재 셀 기준 이전에 동일한 값이 없을 경우에만 입력 가능하다는 게 무슨 원리인지… 더보기 »

앙리
앙리
2020년 9월 15일 3:14 오후
게시글평점 :
     

입문 총 10강의 끝~
이번 강의가 가장 헷갈렸네요 ㅎㅎ
다음 기초로 GO GO~

깡상시
깡상시
2020년 9월 24일 5:06 오후
게시글평점 :
     

강의 정말 잘 듣고 있습니다 항상 좋은 강의 감사드립니다.
질문이 하나 있는데요 데이터 유효성 검사가 지정되어 있는 셀들을시각화 해서 볼 수 있는 방법이 있는지 궁금합니다.

깡상시
깡상시
2020년 9월 25일 12:20 오후

데이터유효성 검사가 적용되어 있는 셀들을 볼 수 있다거나 어떤 셀에 적용되어 있는지 알 수 있었으면 하는데 일일이 셀마다 데이터 유효성 검사 기능에 들어가서 확인해 봐야 하는 걸로만 알고 있어서요 혹시… 더보기 »

알콩달콩이
알콩달콩이
2020년 10월 23일 11:57 오후
게시글평점 :
     

멋진 강의 감사합니다.

8
0
여러분의 생각을 댓글로 남겨주세요.x