들어가기 전에
Excel의 데이터 유효성 검사는 셀에 입력할 수 있는 내용을 제한하는 좋은 방법입니다. 이 때 수식을 사용하여 규칙을 만들면 더욱 정확한 매개변수를 적용할 수 있습니다. 입력 오류를 방지하기 위해 사용하면 좋은 세 가지 규칙을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 3 must-know custom data validation formulas for preventing errors in Excel
- URL: https://www.howtogeek.com/microsoft-excel-custom-data-validation-formulas-preventing-errors/
1. 중복 입력 방지
데이터 유효성 검사를 사용하면 직원 ID나 송장 번호와 같이 열에 중복된 값이 들어가는 것을 방지하여 각 항목이 서로 다르게 표시되도록 할 수 있습니다.
Excel 테이블에 직원 정보를 입력할 때 Employee_ID 열의 모든 값이 고유해야 한다고 가정해 보겠습니다. 누군가 이미 존재하는 ID를 입력하려고 하면 Excel에서 해당 입력을 차단해야 합니다.
(1) 규칙을 적용할 범위를 선택합니다.
(2) [데이터 유효성 검사] 대화상자를 실행하고 설정 탭에서 수식 필드에 다음 수식을 입력합니다
=COUNTIF($A:$A, A2)=1
수식에 대한 설명은 다음과 같습니다.
- $A:$A: 이렇게 하면 수식이 적용되는 범위가 A열 전체로 지정됩니다.
- A2: Excel에서는 열의 각 셀이 개별적으로 검증되도록 이 상대 참조를 자동으로 조정합니다.
- =1: 테스트 조건입니다. 수식은 입력된 값이 범위에 정확히 한 번만 나타나는 경우에만 TRUE(유효성 검사 통과)를 반환합니다.
(3) "오류 알림" 탭으로 가서 "중지"를 선택하고 적절한 제목(예: 중복 ID가 감지됨 )과 메시지(예: 이 직원 ID는 목록에 이미 있습니다. 고유 식별자를 입력하세요)를 입력합니다.
(4) "확인"을 클릭하고 열 A의 바로 아래 셀에 중복된 ID를 입력하여 고유성 안전망이 어떻게 작동하는지 확인합니다.
데이터 유효성 검사는 셀에 규칙이 적용된 후에 데이터를 입력하거나 변경할 때만 실행됩니다. 즉, 이미 입력되어 있는 데이터에 대해서는 영향을 주지 않습니다.
2. 미래 날짜 입력 차단
Excel 스프레드시트에 과거 이벤트를 기록할 때 간단하면서도 강력한 TODAY 함수를 사용하면 미래 날짜가 입력되지 않도록 할 수 있습니다. 다음 표의 B열에서 배송 날짜를 추적하고 과거 날짜만 입력할 수 있도록 하려는 경우를 가정해 보겠습니다.
(1) 셀 서식을 날짜로 올바르게 지정해야 Excel에서 입력 내용을 인식할 수 있습니다. 이렇게 하려면 열을 선택한 후 Ctrl + 1을 눌러 셀 서식 대화 상자를 열고 [표시 형식] 탭에서 "날짜"를 선택합니다.
(2) 적절한 날짜 서식을 선택하고 "확인"을 클릭합니다.
(3) 셀 서식 대화 상자를 닫은 후 범위가 선택된 상태에서 "데이터 유효성 검사"를 열고 "사용자 지정"을 선택한 다음, 수식을 입력합니다.
=B2<=TODAY()
수식에 대한 설명은 다음과 같습니다.
- B2: 검증되는 셀에 대한 상대적 참조
- <=: 날짜를 평가할 때 작거나 같음 연산자는 해당 날짜보다 이전 또는 동일 날짜를 의미
- TODAY(): 현재 시스템 날짜 반환
TODAY 함수는 통합 문서를 열거나 다시 계산할 때마다 업데이트되지만, 날짜가 포함된 셀을 편집하지 않는 한 Excel은 유효성 검사를 다시 실행하지 않습니다. 즉, 오늘 입력된 값은 항상 유효합니다.
(4) 오류 알림 탭에서 "중지"를 선택하고 적절한 제목(예: 미래 날짜는 허용되지 않음 )과 메시지(예: 날짜는 오늘 또는 과거 날짜여야 합니다. 입력한 내용을 수정하세요) 를 추가합니다.
(5) "확인"을 클릭하고 미래의 날짜를 입력하면 잘못된 항목을 차단하는 오류 메시지가 표시됩니다.
참고로, 미래 날짜 대신 과거 날짜를 차단하려면 수식에서 연산자를 '<'에서 '>'로 수정하면 됩니다.
3. 특정 입력 형식 적용
데이터 정리 시 가장 큰 골칫거리 중 하나는 일관되지 않은 형식입니다. 예를 들어, 10자리 숫자를 입력해야 하는 열이 있는 경우, 이보다 길거나 짧거나 문자가 포함된 항목은 모두 거부해야 합니다. AND, LEN , ISNUMBER를 사용하여 길이와 데이터 형식에 대한 엄격한 규칙을 동시에 적용할 수 있습니다.
다음과 같은 테이블에서, B열에 일련번호를 입력하려고 한다고 가정해 보겠습니다. 일련번호는 10자 길이여야 하며 숫자만 포함할 수 있습니다.
(1) B열 전체(또는 적절한 범위)를 선택합니다.
(2) "데이터 유효성 검사"를 열고 "사용자 지정"을 선택한 다음, 수식을 입력합니다.
=AND(LEN(B2)=10, ISNUMBER(B2+0))
수식에 대한 설명은 다음과 같습니다.
- AND(...): 이 함수는 검증이 통과되기 위해 모든 조건이 충족되어야 함을 뜻합니다.
- LEN(B2)=10: LEN 함수는 셀 B2의 문자 수를 세는데, 이 값은 10이어야 합니다.
- ISNUMBER(B2+0): +0을 추가하는 것은 Excel의 유용한 기능입니다. B2 셀에 숫자만 포함되어 있으면, 셀 서식이 텍스트로 지정되어 있어도 Excel에서 해당 셀을 숫자로 처리합니다. 모든 문자가 숫자이면 ISNUMBER는 TRUE를 반환합니다.
(3) 오류 경고 탭에서 "중지"를 선택하고 제목(예: 잘못된 항목 )과 메시지(예: 항목은 정확히 10자리여야 하며 문자나 특수 문자를 포함할 수 없습니다. )를 입력합니다.
(4) "확인"을 클릭하고 9자리 숫자 또는 10자리 영숫자 코드를 입력하면 알림을 볼 수 있습니다.
마치며
데이터 유효성 검사를 적용하여 데이터 입력을 제한하고 오류를 방지하는 방법에 대해 소개했습니다. 이상에서 소개한 3가지 이외에도 다양하게 활용할 수 있습니다. 예를 들어, 3번째 예에서, 문자와 숫자를 특정 순서로 조합하여 입력하도록 지정할 수도 있습니다.
'Excel' 카테고리의 다른 글
| 대부분의 사용자가 존재하는지 잘 모르는 Excel 기능 7가지 (0) | 2026.03.06 |
|---|---|
| 엑셀에서 IFERROR 및 ISERROR 대신 IFNA를 사용하세요 (0) | 2026.03.05 |
| 데이터를 통찰력으로 전환하는 10가지 Excel 데이터 분석 기술 (0) | 2026.03.03 |
| 엑셀 수식 사용 방식을 완전히 다르게 만들어 주는 REDUCE 함수 (0) | 2026.02.27 |
| Excel에서 EOMONTH 함수를 사용하는 방법 (0) | 2026.02.25 |
