데이터 유효성 검사(Data Validation)는 엑셀의 핵심 기능 중 하나입니다. 이 글에서는 다른 셀의 값을 기준으로 데이터 유효성 검사를 설정하는 방법을 알아보겠습니다. 데이터 유효성 검사를 활용하면 목록이 훨씬 더 직관적이고 사용자 친화적으로 변합니다. 여러 셀에 흩어진 데이터를 일일이 입력하는 대신, 드롭다운 목록에서 원하는 값을 선택하기만 하면 되기 때문입니다. 이 글에서는 데이터 유효성 검사로 종속 드롭다운 목록을 만드는 과정과, 특정 범위의 셀에 대한 데이터 입력 제한 방법까지 함께 살펴보겠습니다.
엑셀 데이터 유효성 검사란?
데이터 유효성 검사는 셀에 어떤 종류의 데이터를 입력할 수 있는지 규칙을 만들 수 있는 엑셀 기능입니다. 즉, 데이터 입력 시 특정 규칙을 적용할 수 있도록 도와줍니다. 유효성 검사 규칙은 매우 다양합니다. 예를 들어, 숫자나 텍스트만 입력을 허용하거나, 특정 범위 내의 숫자 값만 받아들이도록 설정할 수 있습니다. 또한 지정된 범위를 벗어나는 날짜와 시간의 입력도 차단할 수 있습니다. 데이터 유효성 검사는 데이터를 사용하기 전에 정확성과 품질을 점검하고, 입력되거나 저장되는 데이터의 일관성을 유지하도록 여러 가지 확인 절차를 제공합니다.
엑셀에서 데이터 유효성 검사 설정하는 기본 방법
엑셀에서 데이터 유효성 검사를 사용하려면 먼저 유효성 검사 규칙을 정의해야 합니다. 그 후 데이터를 입력하면 유효성 검사가 자동으로 작동하여, 입력값이 규칙에 부합하면 셀에 데이터가 저장되고, 그렇지 않으면 오류 메시지가 표시됩니다.
먼저 학생 ID, 이름, 나이로 구성된 데이터 세트를 준비하고, '나이는 18 미만이어야 한다'는 조건의 유효성 검사를 만들어 보겠습니다.
먼저 D11 셀을 선택한 뒤, 리본 메뉴에서 [데이터] 탭으로 이동합니다. 그리고 [데이터 도구] 그룹에서 [데이터 유효성 검사] 옵션을 선택합니다.
[데이터 유효성 검사] 대화 상자가 나타나면 [설정] 탭을 클릭합니다. [제한 대상] 항목에서 [정수]를 선택하고, [빈 셀 무시] 옵션에 체크합니다. 이어서 [데이터] 조건에서 [보다 작음]을 선택하고, [최대값]을 18로 설정한 후 [확인]을 누릅니다.
이제 나이에 20을 입력하면 최대 허용치인 18을 초과했기 때문에 오류가 표시됩니다. 이것이 바로 데이터 유효성 검사의 기본 동작입니다.
다른 셀을 기준으로 하는 데이터 유효성 검사 예제 4가지
엑셀에서 다른 셀을 기준으로 데이터 유효성 검사를 활용하는 대표적인 방법 4가지를 소개합니다. 이 글에서는 INDIRECT 함수와 이름 정의된 범위(명명 범위)를 활용하는 방법과 함께, 셀 참조를 이용한 방법, 값 입력 제한 방법까지 다룹니다. 모든 방법은 비교적 간단하니, 아래 설명을 차근차근 따라 해 보시기 바랍니다.
1. INDIRECT 함수 활용하기
첫 번째 방법은 INDIRECT 함수를 활용하는 것입니다. 데이터 유효성 검사 대화 상자에 INDIRECT 함수를 입력하면, 특정 셀의 선택 값에 따라 드롭다운 목록이 자동으로 변경됩니다. 여기서는 두 개의 품목과 각 품목별 종류로 구성된 데이터 세트를 사용하겠습니다.
단계별 진행 방법
- 먼저 세 개의 열을 각각 별도의 테이블로 변환합니다.
- B5부터 B6까지의 셀 범위를 선택하면 [테이블 디자인] 탭이 나타납니다.
- 리본 메뉴에서 [테이블 디자인] 탭으로 이동한 뒤, [속성] 그룹에서 [테이블 이름]을 변경합니다.
- 같은 방식으로 D5부터 D9까지의 셀 범위를 선택하고 테이블 이름을 변경합니다.
- 마찬가지로 F5부터 F9까지의 셀 범위도 동일한 절차로 이름을 변경합니다.
- 리본 메뉴에서 [수식] 탭으로 이동한 뒤, [정의된 이름] 그룹에서 [이름 정의]를 선택합니다.
- [새 이름] 대화 상자가 나타나면 이름을 설정하고, [참조 대상]에 아래 수식을 입력합니다.
=Items[Item]
- [확인]을 클릭한 뒤, 데이터 유효성 검사를 적용할 두 개의 새 열을 만듭니다.
- 그리고 H5 셀을 선택합니다.
- 리본 메뉴의 [데이터] 탭에서 [데이터 도구] 그룹의 [데이터 유효성 검사]를 선택합니다.
- [데이터 유효성 검사] 대화 상자에서 상단의 [설정] 탭을 클릭합니다.
- [제한 대상]에서 [목록]을 선택합니다.
- [빈 셀 무시]와 [셀 내 드롭다운 표시] 옵션에 체크합니다.
- [원본]에 아래 내용을 입력합니다.
=Item
- [확인]을 클릭합니다.
- 그러면 아이스크림 또는 주스를 선택할 수 있는 드롭다운 목록이 생성됩니다.
- 이번에는 I5 셀을 선택하고, 같은 방법으로 데이터 유효성 검사 대화 상자를 엽니다.
- [설정] 탭에서 [제한 대상]을 [목록]으로 지정하고, [빈 셀 무시], [셀 내 드롭다운 표시]에 체크합니다.
- [원본]에 아래 수식을 입력합니다.
=INDIRECT(H5)
- [확인]을 클릭합니다.
- 그러면 해당 품목에 맞는 종류를 선택할 수 있는 드롭다운 목록이 완성됩니다. 아래는 아이스크림을 선택했을 때 표시되는 맛 목록입니다.
- 이제 품목 목록에서 주스를 선택하면, 종류 목록도 자동으로 변경되는 것을 확인할 수 있습니다.
2. 명명된 범위(Named Range) 활용하기
두 번째 방법은 명명된 범위를 활용하는 것입니다. 테이블의 특정 범위에 이름을 지정한 뒤, 데이터 유효성 검사 대화 상자에서 그 이름을 사용하는 방식입니다. 여기서는 의상, 색상, 사이즈 정보로 구성된 데이터 세트를 사용하겠습니다.
단계별 진행 방법
- 먼저 데이터 세트로 테이블을 만듭니다. B4부터 D9까지의 셀 범위를 선택합니다.
- 리본 메뉴에서 [삽입] 탭으로 이동한 뒤, [테이블] 그룹에서 [테이블]을 선택합니다.
- 그러면 아래 스크린샷처럼 테이블이 생성됩니다.
- [수식] 탭에서 [정의된 이름] 그룹의 [이름 정의]를 선택합니다.
- [새 이름] 대화 상자에서 이름을 설정하고, [참조 대상]에 아래 수식을 입력한 뒤 [확인]을 클릭합니다.
=Table1[Dress]
- 다시 [이름 정의]를 열어, 이번에는 색상 열에 대해 아래 수식으로 이름을 지정합니다.
=Table1[Color]
- 사이즈 열에도 동일한 절차로 이름을 정의합니다.
- 이제 새로운 열 세 개를 만듭니다.
- F5 셀을 선택한 뒤, [데이터] 탭의 [데이터 유효성 검사]를 실행합니다.
- [설정] 탭에서 [제한 대상]을 [목록]으로 지정하고, [빈 셀 무시], [셀 내 드롭다운 표시]에 체크한 뒤, [원본]에 아래 내용을 입력하고 [확인]을 클릭합니다.
=Dress
- 그러면 의상 종류를 고를 수 있는 드롭다운 목록이 생성됩니다.
- 같은 방법으로 G5 셀에 색상 목록을 적용합니다. 원본에는 아래 내용을 입력합니다.
=Color
- 색상 선택용 드롭다운 목록이 완성됩니다.
- 마지막으로 H5 셀에 사이즈 목록을 적용합니다. 원본에는 아래 내용을 입력합니다.
=Size
- 사이즈 선택용 드롭다운 목록까지 완성되었습니다.
3. 셀 참조를 데이터 유효성 검사에 적용하기
세 번째 방법은 데이터 유효성 검사에 직접 셀 참조를 사용하는 것입니다. 유효성 검사 대화 상자에 셀 범위를 지정하면 해당 범위의 값들이 드롭다운 목록으로 표시됩니다. 여기서는 주(州)와 판매액 정보로 구성된 데이터 세트를 사용하겠습니다.
단계별 진행 방법
- 먼저 주와 판매액을 담을 새로운 셀 두 개를 만듭니다. 그리고 F4 셀을 선택합니다.
- [데이터] 탭에서 [데이터 도구] 그룹의 [데이터 유효성 검사]를 선택합니다.
- [설정] 탭에서 [제한 대상]을 [목록]으로 지정하고, [빈 셀 무시], [셀 내 드롭다운 표시]에 체크합니다.
- 원본에는 B5부터 B12까지의 셀 범위를 지정한 뒤 [확인]을 클릭합니다.
- 그러면 원하는 주를 선택할 수 있는 드롭다운 목록이 생성됩니다.
- 이제 선택한 주에 해당하는 판매액을 가져오겠습니다. F5 셀을 선택하고 VLOOKUP 함수를 이용해 아래 수식을 입력합니다.
=VLOOKUP(F4,$B$5:$C$12,2,0)
- Enter 키를 눌러 수식을 적용합니다.
- 이후 드롭다운 목록에서 주를 변경하면 판매액이 자동으로 갱신됩니다. 아래 스크린샷을 확인해 보세요.
4. 데이터 유효성 검사로 값 입력 제한하기
마지막 네 번째 방법은 데이터 유효성 검사를 통해 값 입력 자체를 제한하는 것입니다. 특정 규칙을 적용하면 지정된 범위 안의 데이터만 입력이 허용되고, 범위를 벗어난 값은 오류 메시지와 함께 거부됩니다. 여기서는 주문 ID, 품목, 주문일, 수량으로 구성된 데이터 세트를 사용하겠습니다.
단계별 진행 방법
- 이 예제에서는 주문일을 2021년 1월 1일부터 2022년 5월 5일 사이로 제한하겠습니다. 이 범위를 벗어나는 날짜를 입력하면 오류가 발생합니다.
- D10 셀을 선택한 뒤, [데이터] 탭의 [데이터 유효성 검사]를 실행합니다.
- [설정] 탭에서 [제한 대상]을 [날짜]로 지정하고, [빈 셀 무시] 옵션에 체크합니다.
- 날짜 조건에서 [사이]를 선택하고 시작일과 종료일을 설정한 뒤 [확인]을 클릭합니다.
- 이제 D10 셀에 지정된 범위를 벗어나는 날짜를 입력하면 아래처럼 오류가 표시됩니다.
엑셀에서 인접 셀을 기준으로 데이터 유효성 검사하기
데이터 유효성 검사는 인접한 셀의 값을 기준으로도 설정할 수 있습니다. 예를 들어, 옆 셀의 값이 특정 조건을 충족할 때만 다음 열에 입력할 수 있도록 제한하는 식입니다. 여기서는 시험, 의견, 사유로 구성된 데이터 세트를 사용하고, 시험 의견이 '어려움(Hard)'일 경우에만 사유 열에 내용을 입력할 수 있도록 설정해 보겠습니다.
단계별 진행 방법
- 먼저 D5부터 D9까지의 셀 범위를 선택합니다.
- [데이터] 탭의 [데이터 도구] 그룹에서 [데이터 유효성 검사]를 선택합니다.
- [설정] 탭에서 [제한 대상]을 [사용자 지정]으로 변경합니다.
- [수식] 입력란에 아래 수식을 입력합니다.
=$C5="Hard"
- [확인]을 클릭합니다.
- 이제 인접 셀의 값이 Hard일 때만 사유 열에 설명을 입력할 수 있습니다.
- 반대로 인접 셀 값이 다르면 입력 시도 시 아래와 같은 오류가 표시됩니다.
마무리
이 글에서는 엑셀 데이터 유효성 검사를 활용해 목록을 만드는 다양한 방법을 살펴보았습니다. INDIRECT 함수를 이용해 다른 셀의 값에 따라 자동으로 변하는 종속 목록을 만드는 방법과, 다른 셀을 기반으로 데이터 입력을 제한하는 방법까지 확인했습니다. 이러한 기능은 통계 작업이나 데이터 관리에 매우 유용하게 활용될 수 있습니다. 글을 따라 하다 어려움이 있다면 댓글로 남겨 주세요.
함께 읽으면 좋은 글
- 엑셀 데이터 유효성 검사로 영숫자만 입력 허용하기(사용자 지정 수식 활용)