이 튜토리얼에서는 데이터 유효성 검사의 목적부터 Excel 워크시트에 적용하는 방법까지 모든 것을 배우게 됩니다.
위 이미지에서는 "재고 수량"이라는 열에 데이터 유효성 검사를 적용하여 셀에 입력으로 숫자만 저장합니다. 텍스트 값을 입력하면 "이 값은 이 셀에 정의된 데이터 유효성 검사 제한 사항과 일치하지 않습니다."라는 메시지 상자가 표시됩니다. 이것이 데이터 검증이 작동하는 방식입니다.
⏷Excel의 데이터 유효성 검사
⏷데이터 검증 사용 목적
⏷데이터 검증 입력 유형
⏷1. Excel에서 데이터 유효성 검사를 적용하는 방법은 무엇입니까?
⏵1.1. Excel 셀에 데이터 유효성 검사 적용
⏵1.2. 하나의 셀에 여러 데이터 유효성 검사 규칙 적용
⏵1.3. 다른 셀을 기반으로 데이터 유효성 검사 적용
⏵1.4. 체크박스를 사용하여 데이터 유효성 검사 적용
⏷2. 데이터 유효성 검사를 통해 드롭다운 목록 만들기
⏷3. 색상으로 Excel에서 데이터 유효성 검사 사용
⏷4. Excel에서 사용자 정의 데이터 유효성 검사 적용
⏵4.1. 데이터 검증 시 영숫자 문자만 입력 허용
⏵4.2 사용자 정의 데이터 유효성 검사에서만 숫자 허용
⏵사용자 정의 Excel 데이터 유효성 검사 규칙이 작동하지 않음
⏷5. 데이터 검증 편집
⏷6. 데이터 유효성 검사를 다른 Excel 셀에 복사합니다.
⏷데이터 유효성 검사 규칙이 포함된 셀 찾기
⏷Excel에서 데이터 유효성 검사를 제거하시겠습니까?
⏷데이터 검증 적용에 유용한 팁
⏷데이터 검증의 한계
⏷데이터 검증 문제 및 해결 방법
이 Excel 블로그 게시물을 통해 다음 방법을 배우게 됩니다.
- 데이터 유효성 검사 적용(셀 내, 여러 규칙, 다른 셀 기반 규칙, 확인란 사용)
- 데이터 유효성 검사를 통해 드롭다운 목록 만들기
- 데이터 유효성 검사 사용
- 맞춤 데이터 유효성 검사 적용(영숫자만, 숫자만)
- 데이터 확인 편집
- 데이터 유효성 검사 찾기
- 데이터 유효성 검사 제거
참고: 우리는 Microsoft 365를 사용했습니다. 이 기사의 데이터 세트를 준비합니다. 언급된 방법은 Excel 2007 이후 버전에서 적용할 수 있습니다.
Excel의 데이터 유효성 검사란 무엇인가요?
Excel의 데이터 유효성 검사는 셀에 입력된 데이터 유형을 제어할 수 있는 기능입니다. 입력할 수 있는 항목에 대한 규칙을 설정하여 정확성과 일관성을 유지하는 데 도움이 됩니다. 예를 들어 정수, 소수, 날짜 또는 사전 정의된 목록으로 항목을 제한할 수 있습니다.
Excel에서 데이터 유효성 검사를 사용하는 목적은 무엇인가요?
Excel 워크시트에서 데이터 유효성 검사를 사용할 수 있는 이유는 다음과 같습니다.
- 셀에 잘못된 데이터를 입력하는 것을 방지합니다.
- 데이터의 형식을 균일하게 유지합니다.
- 데이터 입력을 단순화하고 속도를 높입니다.
- 전반적인 데이터 신뢰성을 향상시킵니다.
- 긍정적인 사용자 경험을 창출합니다.
- 고유한 요구사항에 맞게 수식을 사용하여 맞춤 규칙을 허용합니다.
Excel의 모든 유형의 데이터 유효성 검사 입력은 무엇입니까?
1 – 설정 탭
두 개의 입력 상자가 있습니다:허용 및 데이터 . 그들은 데이터 검증 규칙을 설정했습니다.
a) 상자 허용:
데이터 유효성 검사 유형을 선택할 수 있습니다:
- 정수: 정수만 허용됩니다.
- 십진수: 설정된 정밀도로 십진수를 허용합니다.
- 목록: 목록에서 선택할 수 있는 드롭다운 목록을 만듭니다.
- 날짜: 항목이 지정된 날짜 범위 내에 있는지 확인합니다.
- 시간: 시간만 허용됩니다.
- 텍스트 길이: 허용되는 문자 수를 제어합니다.
- 맞춤: 이를 통해 수식을 사용하여 고유한 규칙을 설정할 수 있습니다.
b) 데이터 상자:
데이터 탭에서는 허용 탭에서 선택한 확인을 기반으로 기준을 정의해야 합니다. 탭.
- 정수: 최소값과 최대값을 지정하세요.
- 십진수: 허용되는 소수 자릿수를 설정합니다.
- 목록: 허용되는 항목 목록을 작성하십시오.
- 날짜/시간: 최소 및 최대 날짜 또는 시간 값을 설정합니다.
- 텍스트 길이: 최소 및 최대 문자 수를 결정합니다.
- 맞춤: 확인을 위해 맞춤 수식을 입력하세요.
2 – 입력 메시지 탭:
이 탭을 사용하여 메시지를 맞춤화하여 지침을 제공할 수 있습니다.
'셀을 선택하면 입력 메시지 표시' 상자: 사용자가 검증된 셀을 선택하면 메시지를 표시합니다.
'제목 및 입력 메시지' 상자: 알림 제목과 메시지를 맞춤설정하세요.
3 – 오류 경고 탭:
a) '잘못된 데이터를 입력한 후 오류 경고 표시': 사용자가 잘못된 정보를 입력한 경우 알림을 받도록 합니다.
b) 스타일:
이 상자에서 오류 알림 유형을 선택하세요:
- 정지(입장 금지)
- 경고(차단하지 않고 알림)
- 정보(차단하지 않고 정보를 제공합니다.)
c) 제목 및 오류 메시지: 오류 알림의 제목과 메시지를 사용자 정의하세요.
가이드 1 – Excel에서 데이터 유효성 검사를 적용하는 방법
- 확인을 원하는 셀이나 범위를 선택하세요.
- '데이터'로 이동 ” 탭.
- '데이터 유효성 검사를 클릭하세요. ”를 선택하고 필요한 확인 유형을 선택하세요.
- 요구사항에 따라 모든 탭에서 기준을 구성하고 '확인을 클릭하세요. ” 유효성 검사를 적용합니다.
더 구체적인 예를 보려면 계속 읽어보세요.
사례 1.1 – Excel 셀에 데이터 유효성 검사를 적용하는 방법
아래와 같은 데이터세트가 있는데, 여기서 '생년월일이라는 열에 데이터 유효성 검사를 적용하겠습니다. ” 날짜만 저장합니다.
- 범위 선택 F6:F19 .
- 데이터 를 클릭하세요. 탭을 클릭하고 데이터 유효성 검사를 선택하세요.
- 날짜 선택 허용 상자.
- '보다 큼을 선택합니다. ”를 데이터 상자에 입력하세요.
- 1900년 1월 1일(mm/dd/yyyy)을 시작 날짜로 설정 .
- 확인을 클릭하세요. .
- 열의 셀에는 mm/dd/yyyy 형식의 날짜 입력이 허용됩니다. 그렇지 않으면 Excel에서 "이 값은 이 셀에 정의된 데이터 유효성 검사 제한 사항과 일치하지 않습니다."라는 오류 메시지를 표시합니다.
- 올바른 데이터를 입력하면 허용됩니다.
사례 1.2 – 하나의 셀에 여러 데이터 유효성 검사 규칙을 적용하는 방법
아래에는 E6 셀에서 입력을 받아 유효성을 검사할 데이터세트가 있습니다. 기준 1(가격이 50보다 큼) 및 기준 2(목록에 있는 제품만)를 기준으로 합니다.
- E6 셀 선택 .
- 데이터 를 클릭하세요. 탭을 클릭하고 데이터 유효성 검사로 이동합니다.
- 사용자 정의 선택 허용합니다.
- 수식에 다음 수식을 사용하세요. 상자:
=OR(E6>$B$6, COUNTIF($B$9:$B$12,E6)=1)
- 확인을 클릭하세요.
- 이렇게 하면 E6 셀의 데이터 유효성 검사가 활성화됩니다. . 40을 입력하면 기준을 전혀 만족하지 않기 때문에 즉시 오류 메시지가 표시됩니다.
- 50보다 큰 70을 입력하면 기준 1을 충족하므로 이 값을 유효한 데이터로 허용합니다.
- E6 셀에 Coconut을 입력하는 경우 , 기준 2를 충족하므로 수락됩니다.
사례 1.3 – Excel의 다른 셀을 기반으로 데이터 유효성 검사를 적용하는 방법
F6:F11 셀에 데이터가 있다고 가정합니다. . 이를 사용하여 "지정"이라는 열의 값을 확인합니다.
- 범위 선택 F5:F11 .
- Ctrl + T를 누르세요.
- 내 테이블에 헤더가 있습니다를 확인하세요. 옵션입니다.
- 확인을 클릭하세요. .
- 생성된 테이블을 선택하세요.
- 테이블 디자인을 클릭합니다.
- 테이블 이름 설정 '정보'로.
- D6:D16 범위를 선택하세요. .
- 데이터 유효성 검사 삽입 .
- 목록 선택 허용합니다.
- 소스 상자에 아래 나열된 수식을 사용하세요.
=INDIRECT(“Info [Designation]”)
- 확인을 클릭하세요. .
- 이렇게 하면 표를 기반으로 드롭다운 목록이 생성됩니다. 이제 '지정' 열에서 목록을 사용하여 데이터를 입력하세요.
- 원하는 대로 값을 선택할 수 있습니다.
사례 1.4 – Excel에서 체크박스를 사용하여 데이터 유효성 검사를 적용하는 방법
확인란을 사용하는 경우 해당 항목에 대한 데이터 확인을 활성화하려면 확인란을 클릭해야 합니다.
확인란을 사용하여 "이름" 및 "떠나기(취함)"라는 두 열에 데이터 유효성 검사를 적용할 아래 데이터세트를 고려해 보세요.
- 개발자로 이동 삽입 및 체크박스.
- 체크박스를 선택하세요.
- 마우스 오른쪽 버튼을 클릭하고 텍스트 수정을 선택합니다.
- 이름을 "Validate Name"으로 바꿉니다.
- 반복하여 다른 확인란을 추가하고 이름을 '휴가 확인(취득)'으로 바꿉니다.
- 첫 번째 확인란을 선택하세요.
- 마우스 오른쪽 버튼을 클릭하고 형식 제어를 선택합니다. .
- 컨트롤 을 클릭하세요. 객체 서식의 탭 대화 상자.
- $G$6 셀 선택 셀 링크 .
- 확인을 클릭하세요. .
- 체크박스에 따라 TRUE를 반환합니다. 또는 거짓 인접한 셀 G6.
- 링크 G7 "휴가 확인(취득)"이라는 다른 확인란을 선택하세요.
- C6:C16 범위를 선택하세요. .
- 데이터 유효성 검사 삽입 .
- 사용자 정의 선택 허용 상자에.
- 이 수식을 수식 상자:에 복사하세요.
=IF($G$6, ISTEXT(C5:C16),FALSE)
- 확인을 클릭하세요. .
- 데이터 유효성 검사가 활성화되었습니다. "이름" 열에 "Marylin Pittaman"을 입력하려고 하면 "이름 확인" 확인란을 선택하지 않았기 때문에 오류 메시지 상자가 반환됩니다.
- '이름 확인' 확인란을 선택하면 'Marylin Pittaman'이 입력으로 사용됩니다. 이것이 바로 확인란이 '이름' 열의 셀에 입력된 데이터의 유효성을 검사하는 방법입니다.
- D6:D16 범위를 선택하세요. .
- 새 데이터 유효성 검사 삽입 .
- 허용에서 맞춤을 선택하세요. 상자.
- 수식에 다음 수식을 적용하세요. 상자.
=IF($G$7, ISNUMBER(D6:D16),FALSE)
- 확인을 클릭하세요. .
- 두 번째 확인란을 선택 취소하면 '탈퇴(취소)' 열에 숫자를 입력할 수 없습니다.
- '휴가 확인(취득)' 확인란을 선택하면 셀에 원하는 숫자를 입력할 수 있습니다.
- 두 확인란을 모두 선택하면 항목에 데이터를 입력할 수 있습니다. 결과는 아래 이미지와 같습니다.
가이드 2 – Excel에서 데이터 유효성 검사를 통해 드롭다운 목록을 만드는 방법
시트에 있는 기존 데이터에서 아래 그림과 같은 드롭다운 목록을 만들고 싶다고 가정해 보겠습니다.
- D6:D16 범위를 선택하세요. .
- 데이터 유효성 검사 삽입 .
- 목록 선택 허용 상자.
- 소스를 클릭하세요. 아이콘.
- 범위 선택 F6:F11 . 이러한 항목은 드롭다운 목록에 추가됩니다.
- 확인을 클릭하세요. .
- 아무 셀이나 클릭하세요.
- 오른쪽에 있는 드롭다운 아이콘을 선택하세요.
- 드롭다운 목록이 나타나며 원하는 항목을 선택할 수 있습니다.
- 아래 이미지에 표시된 것처럼 드롭다운 목록을 사용하여 열을 채웠습니다.
가이드 3 – 색상을 사용하여 Excel에서 데이터 유효성 검사를 사용하는 방법
아래에 데이터 세트가 있다고 가정해 보겠습니다. '획득된 숫자' 열에서는 40보다 큰 정수만 입력으로 허용하고 조건부 서식을 사용하여 조건에 따라 해당 값을 강조표시합니다.
- D6:D16 범위를 선택하세요. .
- 데이터 유효성 검사를 삽입하세요.
- 정수 선택 허용 상자.
- 다음 중을 선택하세요. 데이터 상자에 있습니다.
- 세트 40 최소 및 100 최대로.
- 확인을 클릭하세요. .
- 이렇게 하면 이 범위에 대한 데이터 유효성 검사가 활성화됩니다. 40에서 100 사이의 숫자를 입력할 수 있습니다. 40보다 작은 숫자(예:34)를 입력하면 오류 메시지 상자가 나타납니다.
- D6:D16 범위를 선택합니다.
- 조건부 서식 을 클릭하세요. 새 규칙을 선택합니다.
- '다음을 포함하는 셀에만 서식 지정을 클릭합니다. ".
- 다음 중 을 선택하세요.
- '다음 셀에만 서식 지정 아래 상자에 숫자 40과 59를 입력합니다. ” 옵션을 선택하세요.
- 형식을 클릭하세요. 색상을 추가합니다.
- 채우기를 클릭하세요. 셀 서식에서 대화 상자.
- 원하는 색상을 선택하세요.
- 확인을 클릭하세요. .
- 확인을 클릭하세요. .
- 열에서 60 미만의 숫자는 선택한 색상으로 강조표시됩니다.
- 새 조건부 서식 규칙을 사용하여 60에서 100 사이의 범위에 대해 반복합니다.
- 다른 채우기 색상을 선택하세요.
- 아래 이미지와 같이 60보다 큰 숫자가 선택한 색상으로 강조표시됩니다.
가이드 4 – Excel에서 사용자 정의 데이터 유효성 검사 적용
이미지에 표시된 대로 데이터 유효성 검사 대화 상자의 사용자 정의 규칙에서 데이터에 대한 규칙/공식을 설정했습니다.
사례 4.1 – 데이터 검증 시 영숫자 문자만 입력하도록 허용하는 방법
영숫자는 알파벳(문자)과 숫자(숫자) 문자의 조합입니다. 아래 데이터세트가 있으며 '모델' 열에 영숫자 문자를 추가하겠습니다.
- D6:D16 범위를 선택합니다.
- 데이터 확인으로 이동 .
- 사용자 정의 선택 허용 상자.
- 다음 공식을 사용하세요:
=ISNUMBER(SUMPRODUCT(SEARCH(MID(B5, ROW(INDIRECT("1:"&LEN(D6))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
- 확인을 클릭하세요. .
<강한> 
- 이렇게 하면 범위에 영숫자 문자만 허용됩니다.
- 셀에는 영숫자 문자만 저장할 수 있습니다.
사례 4.2 – Excel에서 사용자 정의 데이터 유효성 검사를 통해서만 숫자 허용
아래 이미지와 같은 데이터 세트가 있다고 가정합니다. "재고 수량" 열에는 숫자만 허용하려고 합니다.
- D6:D14 범위를 선택합니다.
- 데이터 유효성 검사 삽입 .
- 사용자 정의 선택 허용 상자.
- 수식에 다음 수식을 사용하세요. 상자:
- 확인을 클릭하세요. .
- 이렇게 하면 열의 셀에 숫자만 허용됩니다. 그렇지 않으면 오류 메시지가 표시됩니다.
- '재고 수량' 열에 숫자만 포함된 데이터 세트가 완성되었습니다.
사용자 정의 Excel 데이터 확인 규칙이 작동하지 않음:이유 및 해결 방법
- 입력한 맞춤 수식이 올바른지 확인하세요. 수식에 구문 오류나 오타가 있는지 다시 확인하세요.
- 맞춤 수식이 다른 셀을 참조하는 경우 셀 참조가 정확하고 참조된 셀에 예상 값이 포함되어 있는지 확인하세요.
- 데이터 확인 규칙을 적용하기 위해 올바른 셀 범위를 선택했는지 확인하세요.
- 확인 규칙을 위반한 경우 오류 알림을 올바르게 설정했는지 확인하세요. 때로는 오류 경고가 표시되지 않거나 선택한 경고 스타일이 상황과 일치하지 않을 수 있습니다.
- 셀 형식이 유효성 검사 규칙에 적합한지 확인하세요. 예를 들어 규칙이 날짜를 확인하는 경우 셀 형식은 날짜로 지정되어야 합니다.
- 이전 Excel 버전으로 작업하는 경우 호환성 모드 문제에 유의하세요. 특정 기능은 호환 모드에서 예상대로 작동하지 않을 수 있습니다.
- 워크시트가 보호되어 있는 경우 데이터 유효성 검사 변경이 허용되는지 확인하세요. 때로는 보호 설정으로 인해 새로운 규칙의 적용이 제한될 수도 있습니다.
- 조건부 서식 규칙과 충돌하는지 확인하세요. 충돌하는 규칙은 서로를 무시할 수 있습니다.
가이드 5 – Excel에서 데이터 유효성 검사를 편집하는 방법
데이터 유효성 검사를 사용하는 기존 드롭다운 목록이 있는 아래와 같은 데이터세트가 있습니다. 이를 편집하고 드롭다운 목록에 새 항목 "대표"를 추가하겠습니다.
- '지정' 열에서 아무 셀이나 선택하세요.
- 출처 상자에 ', 대표'를 추가하세요(쉼표 포함).
- '이 변경사항을 동일한 설정을 가진 다른 셀에 적용을 선택합니다. ”를 선택하여 다른 셀에 적용합니다.
- 확인을 클릭하세요. .
- 이렇게 하면 새 항목 "대표"가 목록에 추가됩니다.
- 이 목록을 사용하여 셀에 데이터 입력을 완료할 수 있습니다.
가이드 6 – 데이터 유효성 검사를 다른 Excel 셀에 복사하는 방법
"재고 수량" 열에 정수 규칙만 적용한 데이터 유효성 검사가 포함된 데이터세트가 있습니다. 이 데이터 유효성 검사를 "판매 수량" 열에 복사하겠습니다.
- '재고 수량'에서 아무 셀이나 선택하세요.
- Ctrl + C 누르기 .
- '판매 수량' 열의 모든 셀을 선택합니다.
- 마우스 오른쪽 버튼을 클릭한 다음 선택하여 붙여넣기를 클릭하세요. 메뉴에서.
- 확인 을 클릭하세요. 붙여넣기 아래 확인을 선택하세요. .
- '판매수량' 셀에 숫자를 입력하면 허용됩니다. 하지만 텍스트를 입력하면 오류 메시지 상자가 나타납니다.
- 열의 셀에 숫자를 입력할 수 있습니다.
데이터 유효성 검사 규칙이 포함된 셀을 찾는 방법
아래 이미지에는 데이터 유효성 검사가 포함된 데이터 세트가 있지만 어떤 열에 있는지는 알 수 없습니다.
- 홈 을 클릭하세요. 찾기 및 선택으로 이동하세요. .
- 데이터 확인을 선택하세요. 목록에서.
- 이렇게 하면 데이터 유효성 검사가 포함된 열이나 셀이 선택됩니다. (예:D6:D16 ).
Excel에서 데이터 유효성 검사를 제거하는 방법
아래에는 "지정" 열에 데이터 유효성 검사가 포함된 데이터세트가 있습니다. 삭제해 보겠습니다.
- D6:D16 범위를 선택하세요. 열에 있습니다.
- 데이터 를 클릭하세요. 데이터 유효성 검사로 이동하세요. .
- '모두 지우기를 클릭하세요. ”를 데이터 검증 대화 상자에 표시합니다.
- 선택 확인 .
- 이렇게 하면 선택한 셀의 기존 데이터 유효성 검사가 제거됩니다.
Excel에서 데이터 유효성 검사를 적용하는 데 유용한 팁
- 일관되고 오류 없는 항목을 위해 드롭다운을 사용하세요.
- 사용자 안내를 위한 정보 메시지를 제공합니다.
- 잘못된 항목에 대한 알림을 구성합니다.
- 유연한 드롭다운을 위해 이름이 지정된 범위를 사용하세요.
- 특정 검증을 위한 공식을 사용하세요.
- 규칙을 시행하기 위해 시트 보호를 고려하세요.
- 규칙을 확정하기 전에 철저하게 테스트하세요.
Excel의 데이터 유효성 검사에는 어떤 제한이 있나요?
- 오류는 다음 셀로 이동한 후에만 감지됩니다.
- 규칙은 시트마다 개별적으로 설정해야 합니다.
- 규칙은 여러 셀에 적용되는 것이 아니라 셀 수준에서 적용됩니다.
- 소스 목록이 변경되어도 드롭다운은 자동으로 업데이트되지 않습니다.
- 내장된 기간 옵션은 다소 제한되어 있습니다.
- 오류 알림에 대한 맞춤설정 옵션이 제한되어 있습니다.
- 특정 특수 문자를 제한하거나 허용하는 기본 제공 옵션이 없습니다.
데이터 검증 문제 및 해결 방법
복사 및 붙여넣기에 대해 데이터 유효성 검사가 제대로 작동하지 않거나 Excel에서 데이터 유효성 검사가 회색으로 표시되는 것과 같은 몇 가지 다른 문제가 있습니다. 기사를 통해 이러한 일반적인 문제에 대해 알아보고 해결 방법을 알아볼 수 있습니다.
연습 학습서 다운로드
Excel의 데이터 유효성 검사:지식 허브
- Excel 셀에서 제한을 설정하는 방법
- 특정 값을 초과하지 않는 Excel 수식
- Excel 테이블의 데이터 유효성 검사 목록
- 다른 시트의 데이터 유효성 검사 목록
- 날짜 형식에 대한 Excel 데이터 유효성 검사
- 색상으로 Excel에서 데이터 유효성 검사를 사용하는 방법
- Excel에서 잘못된 데이터에 동그라미
- Excel의 체크박스 컨트롤을 사용한 데이터 유효성 검사
- [수정됨] Excel에서 복사 붙여넣기에 대한 데이터 유효성 검사가 작동하지 않음
- Excel 데이터 유효성 검사가 회색으로 표시됨
- 하나의 셀에서 여러 데이터 유효성 검사
- Excel에서 데이터 유효성 검사를 복사하는 방법
- Excel의 데이터 유효성 검사 목록에서 공백 제거
- Excel에서 데이터 유효성 검사 제한 제거
- Excel 맞춤 데이터 확인
<