엑셀에서 여러 조건을 만족하는 사용자 지정 데이터 유효성 검사 적용하기(실전 예제 4가지)

엑셀 작업 시 하나의 셀이나 범위에 여러 조건을 동시에 적용해 데이터를 입력해야 하는 경우가 종종 있습니다. 원하는 조건에 맞지 않는 값이 입력되면 워크시트 전체의 데이터 정확성에 문제가 생길 수 있기 때문입니다. 이럴 때 데이터 유효성 검사(Data Validation)를 활용하면 잘못된 값을 미리 차단하고 오류 메시지로 사용자에게 알려줄 수 있습니다.

이 글에서는 여러 조건(Multiple Criteria)에 따라 사용자 지정 데이터 유효성 검사를 적용하는 실전 예제 4가지를 단계별로 소개합니다.

엑셀에서 여러 조건으로 사용자 지정 데이터 유효성 검사 적용하는 4가지 방법

1. 한 개의 셀에 여러 조건 적용하기

OR 함수는 인수로 지정한 조건 중 하나라도 참(TRUE)이면 참을 반환합니다. 여러 조건 중 어느 하나만 충족해도 되는 경우에 유용하게 사용됩니다. 예를 들어, 아래와 같은 샘플 데이터가 있다고 가정해 보겠습니다. 조건 1은 제품 목록이고, 조건 2는 특정 날짜 두 개(시작일과 종료일)입니다. 여기서 B5 셀에 두 조건 중 하나를 만족하는 값만 입력되도록 설정해 보겠습니다.

적용 방법:

• 먼저 상단 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 선택합니다.
• 데이터 유효성 검사 대화상자가 나타나면 [설정] 탭에서 제한 대상(Allow) 항목을 '사용자 지정(Custom)'으로 변경합니다.
• 수식(Formula) 입력란에 아래 수식을 입력합니다.

• [확인]을 눌러 설정을 완료합니다.

이 수식에서 AND 함수는 입력된 날짜가 E5(2022년 2월 1일)부터 E6(2022년 3월 1일) 사이에 있는지 확인하고, COUNTIF 함수는 B5에 입력된 텍스트가 D5:D10 범위 내 제품 목록에 포함되어 있는지 검사합니다. 마지막으로 OR 함수가 두 조건 중 하나라도 만족하는지 판별합니다.

• 설정 후에는 조건 1의 제품 이름이나 지정된 기간 사이의 날짜를 자유롭게 입력할 수 있습니다.
• 반대로 두 조건 모두에 해당하지 않는 값을 입력하면 오류 대화상자가 나타나며 입력이 차단됩니다.

2. 선택한 셀 범위 전체에 여러 조건 적용하기

앞선 예제에서는 한 개의 셀에만 유효성 검사를 적용했지만, 실무에서는 여러 셀 범위에 한 번에 적용하는 경우가 더 많습니다. 아래 데이터 세트에는 조건 1(제품 목록)과 조건 2(50보다 작은 숫자) 두 가지가 있습니다. 이번에는 B5:B10 범위 전체에 두 조건 중 하나를 만족하는 값만 입력되도록 설정해 보겠습니다.

적용 방법:

• 먼저 범위 B5:B10을 선택합니다.
• [데이터] ➤ [데이터 도구] ➤ [데이터 유효성 검사]로 이동하면 대화상자가 나타납니다.
• [설정] 탭에서 제한 대상을 '사용자 지정'으로 선택하고, 수식 입력란에 아래 수식을 입력합니다.

• [확인]을 클릭합니다.

여기서 COUNTIF 함수는 입력값이 D5:D10 범위에 존재할 때만 개수를 반환하여 제품 목록 일치 여부를 확인하고, 다음 조건은 입력값이 E5(50)보다 작은 숫자인지 검사합니다. 최종적으로 OR 함수가 B5:B10 범위의 각 입력값이 두 조건 중 하나를 충족하는지 판단합니다.

• 예를 들어 B5 셀에 '오븐(oven)', B6 셀에 '15'를 입력하면 조건을 만족하므로 정상적으로 입력됩니다.
• 하지만 '59'처럼 50보다 큰 숫자를 입력하면 어떤 조건도 만족하지 않아 오류 메시지가 표시됩니다.

3. 중복 입력 방지 및 문자 수 제한하기

사용자 지정 데이터 유효성 검사를 활용하면 중복 값 입력을 막고, 동시에 입력 가능한 자릿수까지 제한할 수 있습니다. 예를 들어 아래 데이터 세트에는 회사의 ID, 영업 사원, 제품 정보가 담겨 있습니다. 여기서 ID 열은 3자리 숫자만 입력 가능하고, 중복 값은 허용되지 않도록 설정해 보겠습니다.

적용 방법:

• 범위 B5:B10을 선택합니다.
• [데이터] ➤ [데이터 도구] ➤ [데이터 유효성 검사]로 이동합니다.
• [설정] 탭에서 제한 대상을 '사용자 지정'으로 선택하고, 수식 입력란에 아래 수식을 입력합니다.

• [확인]을 누릅니다.

이 수식의 구성 요소를 살펴보면, ISNUMBER 함수는 숫자만 입력받도록 제한하고, LEN 함수는 입력값이 정확히 3자리인지 확인합니다. COUNTIF 함수는 같은 값이 두 번 이상 입력되지 않도록 중복을 방지하며, 마지막으로 AND 함수가 모든 조건을 동시에 만족하는지 최종 검사합니다. 세 조건을 모두 충족하는 값만 허용됩니다.

• 모든 조건을 충족하는 유효한 데이터는 정상적으로 입력됩니다.
• 조건에 어긋나는 값(중복 값, 3자리가 아닌 숫자 등)을 입력하면 오류 대화상자가 나타납니다.

4. 특정 기간 사이의 날짜만 입력 허용하기

마지막 예제에서는 시작일과 종료일 사이의 날짜만 입력되도록 제한하는 방법을 알아보겠습니다. 아래 데이터 세트에는 D12 셀에 시작 날짜가, D13 셀에 종료 날짜가 입력되어 있으며, 각 영업 사원의 발송일(Dispatch Date)을 이 기간 안에서만 입력해야 합니다.

적용 방법:

• 먼저 범위 D5:D10을 선택합니다.
• [데이터] ➤ [데이터 도구] ➤ [데이터 유효성 검사]를 실행합니다.
• 데이터 유효성 검사 대화상자에서 [설정] 탭의 제한 대상을 '사용자 지정'으로 선택하고, 수식 입력란에 아래 수식을 입력합니다.

• [확인]을 클릭합니다.

여기서 AND 함수는 입력된 날짜가 D12와 D13 셀에 지정된 기간 사이에 속하는지 검사합니다.

• 따라서 기준에 맞는 날짜는 문제없이 입력되지만, 조건에 맞지 않는 날짜를 입력하는 즉시 오류 메시지가 표시됩니다.
• 흥미로운 점은, 지정된 기간 안에 속하더라도 2022년 2월 29일을 입력하면 오류가 발생한다는 것입니다. 2022년은 윤년이 아니므로 2월 29일이라는 날짜 자체가 존재하지 않기 때문입니다. 엑셀은 날짜의 유효성까지 자동으로 검증해 줍니다.

마무리

지금까지 엑셀에서 여러 조건을 결합한 사용자 지정 데이터 유효성 검사를 적용하는 4가지 방법을 살펴보았습니다. OR, AND, COUNTIF, ISNUMBER, LEN 등의 함수를 조합하면 제품 목록 매칭, 날짜 범위 제한, 중복 방지, 자릿수 제한 등 다양한 규칙을 자유롭게 구현할 수 있습니다. 위 방법들을 실무에 활용해 보고, 더 좋은 방법이 있다면 댓글로 공유해 주세요. 궁금한 점이나 제안 사항도 언제든 환영합니다.