엑셀에서 셀에 잘못된 데이터가 입력되지 않도록 하고자 할 때 데이터유효성 검사라는 기능을 이용하여 입력값을 체크합니다. 보통은 입력 가능한 목록을 만들어 놓고 체크하거나 숫자 또는 날짜 값만 입력받는 방식으로 체크를 합니다. 이런 방법은 입력값 체크 유형이 단순한 경우에는 유용하긴 하지만 입력값 체크 유형이 복잡할 경우에는 뭔가 다른 방법이 필요합니다.
데이터 유효성 검사에 대한 전반적인 내용은 다음 링크를 참고하세요.
[ 엑셀에서 허용된 데이터만 입력하기 ]
1. 정해진 규칙에 맞는 값만 입력받기
예를 들어 다음과 같이 어떤 회사의 사번은 6자리로 구성되어 있고 처음 2자리는 항상 'SC'로 시작한다면 데이터 유효성 검사의 사용자 지정 수식을 이용하여 처음 2자리를 'SC'이외에는 입력하지 못하도록 할 수 있습니다.
- SC1205
- SC1206
- SC1207
먼저 실습파일을 다운로드해서 [근무이력-유효성검사] 시트를 선택합니다.
① 데이터 유효성 검사를 적용할 셀 범위를 선택(A열 전체 선택)하고
② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
③ [설정] 탭의 [제한 대상]에서 '사용자 지정'을 선택하고
④ [수식]에 =LEFT(A1,2)="SC"를 입력하고
* 입력된 수식 =LEFT(A1,2)="SC"는 LEFT함수로 첫 2자리를 잘라내어 'SC'와 같은지 비교하여 같으면 TRUE를 반환하고, 다르면 FALSE를 반환합니다. 최종적으로 데이터 유효성 검사에서는 수식의 결과값이 FALSE일때 경고창을 표시하여 입력을 제한합니다.
다음으로 값을 잘 못 입력했을 때 [대화상자]에 표시할 메시지를 [오류 메시지] 탭을 선택하여 입력합니다.
⑤ [스타일]은 '중지'를 선택, ⑥ [제목]에는 '경고'를 입력하고
⑦ [오류 메시지]에는 '사번의 앞 2자리는 "SC"만 입력하세요.'를 입력하고
마지막으로 <확인> 버튼을 누르면 '데이터 유효성 검사'가 적용됩니다.
'데이터 유효성 검사'를 적용한 후 사번 필드에 'SC'로 시작하는 값이 아닌 'TC1207'을 입력하면 다음과 같이 [경고] 대화상자가 나타납니다. 처음 2자리가 'SC'로 시작하지 않으면 입력할 수 없도록 제한합니다.
앞에서는 사번의 처음 2자리가 'SC'인지만 체크를 했는데 다음 수식을 이용하면 동시에 사번의 길이가 6자리인지도 체크하는 것도 가능합니다.
=AND(LEFT(A1,2)="SC", LEN(A1)=6)
* 위 수식에서 LEFT(A1,2)="SC"는 처음 2자리가 'SC'가 맞는지 체크하고 LEN(A1)=6는 입력된 값의 길이가 6이 맞는지 체크합니다. 동시에 이 2가지 조건을 만족하면 AND함수는 최종적으로 TRUE를 반환합니다.
* 위에서 나온 함수는 다음 링크를 참고하세요.
2. 중복 값은 입력 금지
엑셀을 이용하여 데이터를 다룰 때 시트에 중복된 값이 입력되지 않도록 해야 할 때가 있습니다. 예를 들어 사원정보에서 사번, 상품정보에서 상품코드와 같은 값들은 중복으로 들어가면 안되므로 다음과 같이 데이터 유효성 검사로 중복을 체크하여 입력하지 못하도록 할 수 있습니다.
먼저 실습파일의 [상품정보-유효성검사] 시트를 선택합니다.
① 데이터 유효성 검사를 적용할 셀 범위를 선택(상품코드 필드 A열 전체를 선택)하고
② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
③ [제한 대상]에서 '사용자 지정'을 선택
④ [수식]에 =(COUNTIF(A:A,A1)=1)를 입력하고
* 입력된 수식 =(COUNTIF(A:A,A1)=1)의 COUNTIF함수는 A열의 어떤 행에 입력하는 값이 A열 전체에서 이미 입력되어 있는지 체크해서 없으면 1, 있으면 2(이미 입력된 것 1개 + 현재 입력하는 1개 = 2개가 되므로 중복이 됨)를 반환하고 반환된 값이 1과 같으면 TRUE를 반환(즉, 중복이 없음)하고 1이 아니면(1보다 커서 2가 되므로 중복) FALSE를 반환합니다.
최종적으로 데이터 유효성 검사에서는 수식의 결과값이 FALSE일때 경고창을 표시하여 입력을 제한합니다.
다음으로 [오류 메시지] 탭을 선택한 후
⑤ [스타일]은 '중지'를 선택, ⑥ [제목]에는 '경고'를 입력하고
⑦ [오류 메시지]에는 '동일한 상품코드가 이미 존재합니다. 다른 상품코드를 입력하세요.'를 입력하고
마지막으로 <확인> 버튼을 누르면 '데이터 유효성 검사'가 적용됩니다.
'데이터 유효성 검사'를 적용한 후 상품코드 필드에 이미 존재하는 'NP0027'을 입력하면 다음과 같이 [경고] 대화상자가 나타납니다. 이미 존재하는 상품코드는 입력할 수 없도록 제한합니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
