이 튜토리얼에서는 엑셀의 한 셀에 여러 개의 데이터 유효성 검사를 적용하는 방법을 단계별로 소개합니다. Microsoft Excel을 자주 사용하신다면 셀 하나에 데이터 유효성 검사를 설정하는 것은 익숙한 작업일 것입니다. 하지만 하나의 셀에 서로 다른 조건 두 가지 이상을 동시에 적용해야 한다면 어떻게 해야 할까요? 이번 글에서는 이러한 상황에서 활용할 수 있는 다양한 예제를 통해 해결 방법을 자세히 설명드리겠습니다.
본문에서 사용된 연습용 통합 문서는 아래에서 직접 내려받아 따라 해볼 수 있습니다.
엑셀에서 한 셀에 여러 데이터 유효성 검사를 적용하는 3가지 예제
한 셀에 적용할 수 있는 유효성 검사 조합에는 제한이 없습니다. 데이터 유효성 검사는 조건 두 개 이상을 자유롭게 결합할 수 있기 때문입니다. 이 글에서는 대표적인 활용 사례 세 가지를 예제로 들어 설정 과정을 차근차근 살펴보겠습니다.
예제 1. 특정 기준을 만족하도록 여러 데이터 유효성 검사 적용하기
첫 번째 방법은 두 가지 특정 기준을 설정하여 하나의 셀에 복합 유효성 검사를 적용하는 것입니다. 아래 데이터 집합에는 두 개의 기준이 준비되어 있으며, E8 셀에 이 두 조건을 모두 반영한 유효성 검사를 구성할 것입니다.
유효성 검사의 동작 방식은 다음과 같습니다. E8 셀에 입력되는 값은 반드시 50보다 커야 하거나, 아니면 범위(B8:B10) 안에 포함된 값이어야 합니다. 이 두 조건 중 어느 하나라도 충족하지 않는 값이 입력되면, 해당 값이 유효성 검사 규칙과 일치하지 않는다는 경고 메시지가 표시됩니다.
아래 데이터 집합의 C5 및 C9 셀에는 각각의 기준을 판별하는 수식이 들어 있습니다. 이 수식들을 유효성 검사에 그대로 활용하게 됩니다.
그럼 실제 적용 절차를 확인해 보겠습니다.
적용 단계:
- 첫째, C13 셀에 두 기준 수식을 AND 함수로 묶은 중첩 수식을 작성합니다. 수식 표시줄에서 이 중첩 수식을 복사해 둡니다(붙여넣기는 아직 하지 않습니다).
- 둘째, 유효성 검사를 적용할 E8 셀을 선택합니다.
- 셋째, 리본 메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 차례로 클릭합니다.
- 그러면 '데이터 유효성 검사' 대화 상자가 열립니다.
- 제한 대상(Allow) 드롭다운 목록에서 사용자 지정(Custom) 옵션을 선택합니다.
- 수식(Formula) 입력란에 앞서 복사한 유효성 검사 수식을 붙여넣습니다.
=AND(E8>$B$5,COUNTIF($B$8:$B$10,E8)=1)- 확인(OK) 버튼을 클릭하여 설정을 마무리합니다.
여기서 COUNTIF 함수는 입력된 값이 범위(B8:B10)의 값들과 일치하는지 확인하고, AND 함수는 두 조건을 하나의 규칙으로 묶어주는 역할을 합니다.
- 이제 E8 셀에 값 40을 입력해 보겠습니다.
- 값 40은 첫 번째 기준('기준 1')을 충족하지 못하므로, 엑셀이 경고 창을 표시합니다. E8 셀의 값은 반드시 50보다 커야 합니다.
- 마지막으로 E8 셀에 텍스트 'Apple'을 입력해 보겠습니다. 이 값은 범위(B8:B10) 안에 포함되어 두 번째 기준을 만족하므로, 엑셀이 정상적으로 입력을 받아들입니다.
함께 보면 좋은 글: 엑셀에서 여러 기준에 대한 사용자 지정 데이터 유효성 검사 적용하기 (예제 4가지)
예제 2. 특정 텍스트와 두 날짜 사이의 날짜만 허용하도록 설정하기
두 번째 예제에서는 특정 텍스트 하나와 두 날짜 사이의 날짜만 입력을 허용하는 유효성 검사를 만들어 보겠습니다. 아래 데이터 집합에는 '기준 1'로 단어 Excel이, '기준 2'로 '1-03-22'와 '31-03-22'라는 두 날짜가 준비되어 있습니다. 각 기준을 판별하는 수식도 함께 확인할 수 있으며, 이 수식들을 활용해 E8 셀에 복합 유효성 검사를 적용하겠습니다.
실제 적용 과정을 살펴보겠습니다.
적용 단계:
- 먼저, C12 셀에 두 기준 수식을 AND 함수로 묶어 중첩 수식을 만듭니다.
- 다음, 수식 표시줄에서 이 중첩 수식을 복사해 둡니다(붙여넣기는 하지 않습니다).
- 그런 다음, 데이터 유효성 검사 대화 상자를 엽니다.
- 제한 대상(Allow) 드롭다운에서 사용자 지정(Custom)을 선택하고, 수식(Formula) 입력란에 유효성 검사 수식을 입력합니다.
=OR(E8=$B$5,AND(E8>=DATE(2022,03,1),E8<=DATE(2022,03,31)))- 확인(OK)을 클릭합니다.
여기서 AND 함수는 입력된 날짜가 '1-03-22'부터 '31-03-22' 사이에 있는지 판별하고, OR 함수는 두 기준을 결합하여 어느 하나라도 참이면 입력을 허용합니다.
- 이제 E8 셀에 텍스트 Excel을 입력해 보겠습니다. 이 값은 '기준 1'과 일치하므로 경고 창이 나타나지 않습니다.
- 반대로 텍스트 Word를 입력하면 어떻게 될까요? 어떤 기준과도 일치하지 않으므로 경고 창이 표시됩니다.
- 이번에는 '1-03-22'부터 '31-03-22' 범위 안의 날짜를 입력해 보겠습니다. '기준 2'와 일치하므로 엑셀이 정상적으로 값을 받아들입니다.
- 마지막으로 유효성 검사 날짜 범위를 벗어난 날짜를 입력해 보겠습니다. 어떤 기준에도 부합하지 않기 때문에 엑셀이 경고 창을 표시합니다.
함께 보면 좋은 글: 엑셀에서 드롭다운 목록으로 데이터 유효성 검사 만들기 (방법 8가지)
관련 학습 자료:
- [해결] 엑셀에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않을 때
- 엑셀 데이터 유효성 검사에서 VLOOKUP 사용자 지정 수식 활용법
- VBA로 이름 정의 범위를 활용한 데이터 유효성 검사 목록 만들기
- 배열에서 데이터 유효성 검사 목록 생성하는 엑셀 VBA 코드
- 사용자 지정 수식으로 영문자·숫자만 입력 허용하기
예제 3. 문자열의 시작 문자와 길이를 고정하기
세 번째 예제에서는 입력되는 문자열의 시작 문자와 길이를 동시에 제한하는 유효성 검사를 만들어 보겠습니다. 데이터 집합에는 두 가지 기준이 준비되어 있으며, C9 셀에 복합 유효성 검사를 적용할 것입니다.
설정할 규칙은 다음과 같습니다. 입력 값이 'ISBN'으로 시작한다면 전체 길이는 반드시 8자여야 하고, 'CODE'로 시작한다면 반드시 10자여야 합니다. 이 조건을 벗어나는 값이 입력되면 유효성 검사 수식이 경고 창을 띄웁니다.
적용 절차를 확인해 보겠습니다.
적용 단계:
- 먼저, D5 셀에 모든 기준을 포괄하는 데이터 유효성 검사 수식을 작성합니다.
- 다음, 예제 1과 같은 방법으로 '데이터 유효성 검사' 대화 상자를 연 후, 제한 대상(Allow) 드롭다운에서 사용자 지정(Custom)을 선택합니다.
- 그런 다음, 수식(Formula) 입력란에 유효성 검사 수식을 입력합니다.
=OR(AND(LEFT(C9,4)="ISBN",LEN(C9)=8),AND(LEFT(C9,4)="CODE",LEN(C9)=10))- 확인(OK)을 클릭합니다.
수식의 동작 원리는 다음과 같습니다. LEFT 함수는 문자열에서 앞 4글자를 추출하고, LEN 함수는 문자열의 전체 길이를 계산합니다. OR 함수는 두 기준을 결합하여 어느 하나라도 참이면 입력을 허용합니다.
- 이제 'ISBN-358'이라는 값을 입력해 보겠습니다. 이 값은 'ISBN'으로 시작하며 길이가 8자이므로, 두 조건을 모두 충족해 엑셀이 정상적으로 받아들입니다.
- 마지막으로 앞서 입력한 값에 문자 두 개를 더 추가해 보겠습니다. 'ISBN'으로 시작한다는 첫 번째 기준은 만족하지만, 전체 길이가 10자가 되어 두 번째 기준과 맞지 않으므로 엑셀이 경고 창을 표시합니다.
함께 보면 좋은 글: 엑셀에서 다중 선택이 가능한 데이터 유효성 검사 드롭다운 목록 만들기
마무리
지금까지 엑셀에서 한 셀에 여러 개의 데이터 유효성 검사를 적용하는 방법을 세 가지 예제를 통해 알아보았습니다. 이 글과 함께 제공되는 샘플 워크시트를 활용해 직접 실습하면서 실력을 점검해 보시기 바랍니다. 궁금한 점이 있다면 댓글로 남겨주세요. 최대한 빠르게 답변드리겠습니다. 앞으로도 유용한 Microsoft Excel 활용 팁을 계속 소개해 드릴 예정이니 많은 관심 부탁드립니다.
관련 글
- 필터 기능이 있는 엑셀 데이터 유효성 검사 드롭다운 목록 (예제 2가지)
- 엑셀에서 표로 데이터 유효성 검사 목록 만드는 방법 (방법 3가지)
- VBA를 활용한 엑셀 데이터 유효성 검사 드롭다운 목록 (응용 7가지)
- Excel VBA로 데이터 유효성 검사 목록에 기본값 설정하기 (매크로 및 사용자 폼)
- 엑셀 데이터 유효성 검사 목록에서 빈 셀 제거하는 방법 (방법 5가지)