엑셀 데이터 유효성 검사 완벽 정리 :: 엑셀 기초 입문 10강
엑셀 데이터 유효성 검사의 모든것 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 엑셀 데이터 유효성 검사의 모든 것예제파일
강의에 사용된 기초 함수 목록
엑셀 데이터 유효성 검사란 '정해진 형식의 데이터만 입력 가능하도록 제한하는 기능'을 이야기 합니다. 즉, 잘못된 데이터가 입력되지 않도록 방지하는 기능인데요.
데이터 유효성 검사 기능은 엑셀로 많은 양의 데이터를 관리할 경우, 특히 여러개의 엑셀 파일을 직접 취합해야 할 경우라면 반드시 숙지해야 할 엑셀의 필수 기능입니다.
잘못된 형식의 데이터가 섞여 있으면 잘못된 계산 결과가 나옵니다. 여러개의 엑셀 파일을 취합해 본 경험이 있는 실무자라면, 누구나 한번쯤은 아래와 같은 문제로 고생한 적이 있으실텐데요. 위와 같이 숫자만 입력되어야 할 곳에 "2,000원" 또는 "3개" 등의 텍스트가 섞이게 되면 계산 결과로 옳바르지 않은 값이 반환됩니다.
이런때 데이터유효성검사를 활용하면 작성자가 원하는 형태의 데이터만 입력가능하도록 제한할 수 있습니다. 데이터 유효성 검사는 기본적으로 총 7개의 형식을 지원합니다.
유효성 검사 설명 예제 정수 정수만 입력 가능합니다. 1~31사이 정수 소수점 소수 또는 정수만 입력 가능합니다. 0.5보다 작은 소수 목록 지정한 범위(또는 직접 입력한 항목)의 값만 입력 가능합니다. "사과","배","포도" 날짜 특정 범위안의 날짜만 입력 가능합니다. 2020/01/01~2020/12/31 시간 시간만 입력 가능합니다. 08:00~18:00 텍스트 길이 정해진 길이의 텍스트만 입력 가능합니다. 3글자 이상의 텍스트 사용자 지정 수식을 만족하는 조건의 값만 입력 가능합니다. LEFT(셀,1)="가" --> 첫글자가 '가'로 시작하는 문자 위 7가지 항목 중 '목록' 과 '사용자 지정'의 사용법만 잘 숙지하면 실무에서 발생하는 대부분의 문제를 해결할 수 있습니다.
유효성 검사로 메모(안내메세지) 출력하기
데이터 유효성 검사를 활용하면 '메모' 기능보다 더욱 깔끔한 안내메세지를 출력할 수 있습니다.
데이터 유효성 검사를 활용하면 더욱 깔끔하게 메모를 표시할 수 있습니다. 데이터 유효성 검사를 이용한 안내 메세지는 셀 오른쪽 상단에 빨간색 표식이 뜨지 않아 더욱 깔끔하며, 마우스 커서를 셀 위에 가져가지 않아도 메모를 확인할 수 있는 장점이 있습니다.
- 예제파일의 '2' 시트로 이동합니다. B3셀(학생명)을 선택한 뒤 [데이터] - [데이터 유효성 검사]로 이동합니다.
메모를 출력할 셀을 선택한 뒤, 데이터 - 데이터 유효성 검사로 이동합니다. - '설명 메시지' 탭으로 이동한 뒤, 출력할 안내메세지의 제목과 설명 메시지를 입력합니다.
제목 : 학생명 입력
설명 메시지 : 학생명을 입력하세요.
메모로 표시할 제목과 메시지를 입력합니다. - B3셀을 선택하면 안내메세지가 출력됩니다.
셀을 선택하면 메모가 표시됩니다.
유효성 검사로 출력될 오류 메세지 문구 수정하기
데이터 유효성 검사 조건에 어긋날 경우 출력할 오류메세지의 문구도 손쉽게 수정할 수 있습니다.
- 예제파일의 C4:C14 범위를 선택합니다. [데이터] - [데이터 유효성 검사]로 이동합니다.
유효성 검사가 적용된 범위를 선택한 뒤, 데이터 유효성 검사로 이동합니다. - '오류 메시지' 탭으로 이동합니다. 오류 메시지 제목과 내용이 입력되어 있습니다.
제목 : 오류안내
오류 메시지 : 과목명을 제대로 입력했는지 다시 확인해주세요.
오류메시지 탭에서 표시할 제목과 내용을 입력합니다. - C5셀을 선택한 뒤, '미술'을 입력합니다. 이전에 설정한 오류 메시지가 출력되며 입력이 제한됩니다.
잘못된 데이터를 입력하면 오류메시지가 출력됩니다.
데이터 유효성 검사의 목록 기능을 사용하면 지정한 범위 안의 값만 입력 가능하도록 제한할 수 있습니다. 하지만 목록으로 고정된 범위를 입력하면, 범위에 새롭게 추가되는 값이 목록으로 반영되지 않는 문제가 발생하는데요.
이러한 문제를 해결하려면 '표 기능' 이나 '동적범위'를 활용할 수 있습니다. 이번 강의에서는 보다 쉽게 적용가능한 '표 기능'을 활용하는 방법을 알아보겠습니다. 동적범위에 대한 좀 더 자세한 사용법이 궁금하신 분은 아래 OFFSET 동적범위 사용법 기초 영상강의를 참고하세요.
- 예제파일의 '3' 시트로 이동합니다. G3:G8 범위를 선택한 뒤, [삽입] - [표]를 선택하거나 또는 단축키 Ctrl + T 를 눌러 표 만들기 대화상자를 실행합니다. '머릿글 포함'을 체크한 뒤, 확인을 눌러 표를 생성합니다.
범위를 표로 변환합니다. - [표 디자인] - [표 스타일]에서 표를 원하는 디자인으로 변경합니다.
표의 디자인을 변경합니다. - [표 디자인]의 왼쪽 표 이름에서 표 이름을 '과목명'으로 변경합니다.
표 이름을 '과목명'으로 변경합니다. - C4:C14 범위를 선택한 뒤, [데이터] - [데이터 유효성검사]로 이동합니다. [설정] 탭의 '원본' 값으로 아래 수식을 입력합니다.
=INDIRECT("과목명")
데이터 유효성 검사의 목록으로 INDIRECT 함수를 입력합니다. - 오른쪽 과목명 범위에 새로운 과목을 추가합니다. 예제로 '미술'을 추가해보겠습니다.
표에 새로운 과목을 추가합니다. - 이후 왼쪽 표에서 목록을 클릭하면 새로운 과목이 실시간으로 반영된 것을 확인할 수 있습니다.
유효성 검사 목록으로 새로운 값이 실시간으로 반영됩니다.
유효성 검사 기능을 이용하면 목록에 있는 값과 다른 오탈자나 잘못된 데이터를 손쉽게 확인할 수 있습니다. 단, 이 기능은 확인해야 할 값이 100개 이내일 경우 사용하면 편리하며, 확인해야 할 값이 많을 경우에는 COUNTIF 함수를 사용하면 더욱 편리합니다.
COUNTIF 함수를 사용하여 오탈자를 확인하는 방법은 영상강의 11:22 부터 설명해드렸습니다.
- 예제파일의 '4' 시트로 이동합니다. B4:B14 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다.
잘못된 데이터를 확인할 범위를 선택한 뒤, 데이터 유효성 검사로 이동합니다. - 제한대상으로 [목록]을 선택한 뒤, 원본 범위로 [G4:G20] 범위를 입력합니다.
제한대상은 목록으로, 원본 범위로 G4:G20 범위를 선택합니다. - B4:B14 범위가 선택된 상태로, [데이터] - [데이터 유효성 검사] - [잘못된 데이터]를 선택합니다.
범위가 선택된 상태에서, 데이터 유효성 검사 - 잘못된 데이터를 클릭합니다. - 목록에 있지 않은 잘못된 데이터가 빨간색 동그라미로 강조됩니다.
목록에 포함되지 않은 잘못된 데이터가 빨간색 동그라미로 표시됩니다.
유효성 검사를 이용하면 [한/영]키 입력없이 자동으로 한글과 영문 입력 설정을 변경할 수 있습니다. 이를 통해 엑셀로 한글과 영어를 번갈아가며 입력할 때 보다 편리하게 작업할 수 있습니다.
- 예제파일의 '5' 시트로 이동합니다. C4:C14 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다.
데이터 - 데이터 유효성 검사로 이동합니다. - [IME 모드] 이동합니다. 담당자명은 '한글'로 입력되므로, 입력기 모드를 [한글]로 변경합니다.
IME 모드에서 입력기 모드를 한글로 변경합니다. - 이번에는 D4:D14 범위를 선택한 뒤, 입력기 모드를 [영문]으로 변경합니다.
이메일주소 범위를 선택한 뒤, 입력기 모드를 영문으로 변경합니다. - '담당자명'과 '담당자 이메일' 을 선택하면 입력모드가 한글/영문 각각 자동으로 변경됩니다.
각 셀을 선택하면 입력모드가 한글/영어로 자동 변경됩니다.
데이터 유효성 검사 실전 예제
유효성 검사의 사용자 지정 옵션을 활용하면 보다 다양한 형태로 입력 조건을 제한할 수 있습니다. 이번 강의에서는 사용자 지정 옵션을 활용한 두가지 실전 예제를 알아보겠습니다.
- 예제파일의 '6' 시트로 이동합니다. D4:D14 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]를 선택합니다.
휴대폰 번호를 입력할 범위를 선택한 후, 데이터 유효성 검사로 이동합니다. - 엑셀 화면 좌측의 '이름상자'에 활성화 된 셀 주소를 확인합니다.
선택된 범위에서 활성화된 셀 주소를 확인합니다. - 제한대상은 [사용자 지정]을 선택합니다. 수식에는 아래 적어드린 공식을 입력합니다. 공식의 "셀"은 활성화 된 셀 주소로 변경합니다.
=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자리여야 합니다. - 휴대폰 번호를 입력합니다. 옳지 않은 휴대폰번호를 입력하면 오류메세지가 출력되면서 입력이 제한됩니다.
잘못된 휴대폰 번호를 입력하면 오류메시지가 출력됩니다.
- 예제파일의 '7' 시트로 이동합니다. B6:C16 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]를 선택합니다.
중복값 입력을 방지할 범위를 선택한 뒤, '데이터 유효성 검사'로 이동합니다. - 엑셀 화면 좌측의 '이름상자'에 활성화 된 셀 주소를 확인합니다.
선택된 범위에서 활성화된 셀 주소를 확인합니다. - 제한대상은 '사용자 지정'을 선택한 뒤, 수식으로 아래 공식을 입력합니다. '셀'은 활성화된 셀 주소로 변경하여 입력합니다.
=COUNTIF($셀:셀,셀)<2
제한 대상으로 사용자 지정을 선택한 뒤, 수식을 입력합니다. - 송장번호로 중복값을 입력하면 오류메세지가 출력되며 입력이 제한됩니다.
중복값을 입력하면 오류메시지가 출력되며 입력이 제한됩니다.
- 예제파일의 '2' 시트로 이동합니다. B3셀(학생명)을 선택한 뒤 [데이터] - [데이터 유효성 검사]로 이동합니다.
