엑셀에서 데이터를 처리할 때 잘못된 데이터가 입력되지 않도록 하는 것이 중요합니다. 숫자필드에 문자가 입력되거나 허용되지 않은 값이 입력되면 문제가 되겠죠.
데이터유효성 검사라는 기능을 이용하면 다음과 같이 잘못된 데이터가 입력되지 않도록 할 수 있습니다.
1. 데이터 유효성 검사
1) 목록에 있는 값만 허용하기
'데이터 유효성 검사'는 잘못된 값이 입력되지 않도록 미리 제한을 설정해 놓고 제한을 벗어나면 값을 입력하지 못하도록 합니다.
예를 들어 아래와 같이 판매실적 자료에서 대리점 유형은 직영대리점, 가맹대리점만 입력되어야 하는데 누군가가 실수로 대리점이라고 입력을 했습니다. 데이터 유효성 검사 기능을 이용하여 잘못된 값이 입력되지 않도록 해 보겠습니다.
아래와 같이 실습 파일의 [판매이력-유효성검사빈행] 시트에 '데이터 유효성 검사'를 적용해 보겠습니다.
이 후 데이터 유효성 검사가 적용된 양식을 사용하면 잘못된 데이터를 입력할 일이 없겠죠.
① 먼저 '데이터 유효성 검사'를 적용할 셀 범위를 선택합니다.
② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
③ [설정] 탭의 [제한 대상]에서 '목록'을 선택하고
④ [원본]에서 '직영대리점,가맹대리점'을 입력하고
⑤ <확인> 버튼을 누릅니다.
다음과 같이 [C2]셀을 클릭해 보면 '데이터 유효성 검사'가 적용이 되어 '목록'이 표시되는 것을 확인할 수 있습니다.
'데이터 유효성 검사'를 적용한 후 대리점 유형 필드에 '직영대리점', '가맹대리점' 이외의 다른 값을 입력하면 어떻게 될까요?
다음과 같이 경고창이 나타납니다. 다른 값을 입력할 수 없습니다.
* 범위를 지정하여 데이터 유효성 목록 설정하기
위의 예에서는 데이터 유효성 목록을 직접 입력하였는데 다음과 같이 값 목록이 입력된 범위를 지정하여 설정할 수도 있습니다.
① [데이터 유효성] 대화상자에서 [원본] 오른쪽의 버튼을 누릅니다.
② 값이 입력되어 있는 시트의 셀 범위를 지정하고
③ 범위가 입력된 상자의 오른쪽 버튼을 누르면
다음과 같이 [데이터 유효성] 대화상자의 [원본]에 범위가 입력됩니다.
④ <확인> 버튼을 누르면 '데이터 유효성 검사'가 적용됩니다.
2) 숫자의 입력값 범위를 제한하기(예-음수 입력금지)
이번에는 숫자 값 입력제한을 해 보겠습니다. 예를 들어 판매수량이 음수인 경우는 없으므로 음수를 입력하지 못하도록 설정해 보겠습니다.
먼저 아래 그림과 같이 '데이터 유효성 검사'를 적용할 셀 범위 [F] 열 전체를 선택합니다(F열의 일부 범위만 적용해도 되지만 F열에 입력되는 데이터는 모두 수량만 입력되므로 F열 전체를 선택하는 것이 편리합니다).
리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
① [설정] 탭의 [제한 대상]에서 '정수'를 선택하고(소수점이 없는 숫자만 입력 가능)
② [제한 방법]은 '~보다 크거나 같은'을 뜻하는 '>=' 선택
③ [최소값]은 0을 입력하고 <확인> 버튼을 눌러서 '데이터 유효성 검사'를 적용해도 되지만
이번에는 사용자가 값을 잘못입력하면 무엇을 잘못했는지 알 수 있도록 오류메시지를 표시하는 방법까지 알아보겠습니다.
[설정] 탭의 오른쪽에 있는 [설명 메시지] 탭을 선택한 후④ [제목]에 '주의!'를 입력하고
⑤ [설명 메시지]에는 '수량은 음수를 입력할 수 없습니다.'를 입력합니다.
[설명 메시지] 탭에 입력한 내용은 다음과 같이 셀을 선택하면 노란 메모 형식으로 표시됩니다.
마지막으로 [오류 메시지] 탭을 선택한 후
⑥ [스타일]은 '중지'를 선택, ⑦ [제목]에는 '숫자 입력 오류'를 입력하고
⑧ [오류 메시지]에는 '수량은 음수를 입력할 수 없습니다.'를 입력하고
마지막으로 <확인> 버튼을 누르면 '데이터 유효성 검사'가 적용됩니다.
'데이터 유효성 검사'를 적용한 후 수량 필드에 -100을 입력하면 다음과 같이 대화상자가 나타납니다. 수량은 음수를 입력할 수 없도록 제한됩니다.
이상으로 몇가지 데이터 유효성 검사의 예를 살펴보았습니다. 다음과 같이 약간 복잡한 방식은 링크를 참고하세요.
[ 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기 ]
[ 엑셀에서 사용자 지정 수식으로 데이터 유효성 검사하기 ]
2. 데이터 유효성 검사 옵션 정리
지금까지 '데이터 유효성 검사'의 몇가지 옵션만 살펴보았는데 다음 그림에 보이는 대로 나머지 옵션들도 유용한 것이 많이 있습니다.
아래와 같이 '데이터 유효성 검사 옵션'을 정리했으니 참고하시기 바랍니다.
| 옵션 | 적용 내용 |
| 모든 값 | 데이터 유효성 검사 조건이 지워짐 |
| 정수 | 정수만 입력할 수 있도록 제한 |
| 소수점 | 정수와 소수점을 포함한 숫자만 입력할 수 있도록 제한 |
| 목록 | 드롭다운 목록에 있는 항목을 선택하여 입력할 수 있도록 제한 |
| 날짜 | 날짜만 입력하도록 제한, 특정일자만 입력하거나 특정일자 이후만 입력하는 등 다양한 방법으로 제한할 수 있음 |
| 시간 | 시간만 입력하도록 제한, 특정시간만 입력하거나 특정시간 이후만 입력하는 등 다양한 방법으로 제한할 수 있음 |
| 텍스트 길이 | 텍스트의 길이를 제한, 예를 들어 사번은 5자리를 초과할 수 없도록 제한할 수 있음 |
| 사용자 지정 | 수식을 입력하여 수식을 만족하는 값만 입력하도록 제한, 예를 들어 어떤 회사의 사번은 6자리로 구성되어 있고 처음 2자리는 항상 'SC'로 시작한다면 수식을 이용하여 처음 2자리를 'SC'이외에는 입력하지 못하도록 제한할 수 있음 |
3. 잘못된 데이터 표시
위에서도 설명했지만 판매실적 자료에서 대리점 유형은 '직영대리점', '가맹대리점'만 입력되어야 하는데 누군가가 실수로 '대리점'이라고 입력을 했습니다. 잘못된 데이터를 눈으로 찾으면 시간도 오래 걸리고 누락할 수도 있겠지요. 데이터 유효성 검사 기능에 포함된 '잘못된 데이터' 표시 기능을 이용하여 잘못된 값을 한번에 표시해 보겠습니다.
이 기능은 앞에서 알아본 '데이터 유효성 검사'를 먼저 적용하고 '잘못된 데이터' 표시 기능을 사용하는 방식입니다.
① 데이터 유효성 검사를 적용할 셀 범위를 선택하고 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
② 대화상자의 [설정] 탭의 [제한 대상]에서 '목록'을 선택하고
③ [원본]에 '직영대리점,가맹대리점'을 입력하고
④ <확인> 버튼을 누르면 데이터 유효성 검사가 적용됩니다.
데이터 유효성 검사를 적용한 후 다음과 같이
① 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사] 옆의 <드롭다운> 버튼을 누르고
② 아래에 표시된 [잘못된 데이터] 메뉴를 누르면 잘못된 데이터에 빨간 동그라미가 표시됩니다.
'잘못된 데이터' 표시를 지우고 싶으면 [잘못된 데이터] 메뉴 바로 아래에 있는 [유효성 표시 지우기]를 누르면 됩니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
