Excel 데이터 유효성 검사: Excel에서 데이터 유효성 검사 추가, 사용, 복사 및 제거

Excel에서 데이터 유효성 검사 기능은 사용자가 셀에 입력할 수 있는 내용을 제한하는 강력한 도구입니다. 예를 들어, 텍스트 길이를 제한하거나 특정 형식으로 입력을 제한하고, 고유한 값을 강제하거나 텍스트가 특정 문자로 시작하거나 끝나도록 할 수 있습니다. 이러한 기능은 데이터 무결성을 유지하고 워크시트에서 오류를 줄이는 데 도움이 됩니다.

이 튜토리얼에서는 Excel에서 데이터 유효성 검사를 추가하고 사용하며 제거하는 방법을 탐구합니다. 기본적인 계산부터 고급 계산까지 다루며, 이 기능을 작업에 효과적으로 적용할 수 있도록 단계별로 자세한 지침을 제공합니다.

1. Excel에서 데이터 유효성 검사는 무엇인가요?

"데이터 유효성 검사" 기능은 워크시트에서 입력 내용을 제한하는 데 도움이 됩니다. 일반적으로, 선택한 셀 목록에 특정 유형의 데이터만 입력하도록 허용하거나 방지하기 위해 유효성 검사 규칙을 생성할 수 있습니다.

데이터 유효성 검사 기능의 기본 사용법:

1. "모든 값": 유효성 검사가 수행되지 않으며, 지정된 셀에 아무 내용이나 입력할 수 있습니다. 2. "정수 값": 정수만 허용됩니다. 3. "소수": 정수와 소수를 모두 입력할 수 있습니다. 4. "일차원 테이블": 미리 정의된 목록의 값만 입력하거나 선택할 수 있습니다. 값은 드롭다운 목록에 표시됩니다. 5. "날짜": 날짜만 허용됩니다. 6. "시간": 시간만 허용됩니다. 7. "텍스트 길이": 지정된 길이의 텍스트만 입력할 수 있습니다. 8. "사용자 정의": 사용자의 입력을 유효성 검사하기 위한 사용자 정의 수식 규칙을 생성합니다.

 2. Excel에서 데이터 유효성 검사를 추가하는 방법은 무엇인가요?

Excel 워크시트에서 다음 단계에 따라 데이터 유효성 검사를 추가할 수 있습니다:

1. 데이터 유효성을 설정하려는 셀 목록을 선택한 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 스크린샷을 참조하세요.

2. "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 사용자 정의 유효성 검사 규칙을 생성하세요. 기준 상자에 다음 유형 중 하나를 입력할 수 있습니다:

• "값": 기준 상자에 숫자를 직접 입력하세요;
• "셀 참조": 워크시트 또는 다른 워크시트의 셀을 참조합니다;
• "수식": 조건으로 더 복잡한 수식을 생성합니다.

예를 들어,100에서1000 사이의 정수만 입력할 수 있도록 하는 규칙을 생성하겠습니다. 아래 스크린샷에 표시된 대로 기준을 설정합니다:

3. 조건을 구성한 후, "입력 메시지" 또는 "오류 경고" 탭으로 이동하여 유효성 검사 셀에 대한 입력 메시지나 오류 경고를 설정할 수 있습니다. (경고를 설정하지 않으려면 "확인"을 클릭하여 바로 완료하십시오.)

3.1) 입력 메시지 추가(선택 사항):

데이터 유효성 검사가 포함된 셀을 선택할 때 나타나는 메시지를 생성할 수 있습니다. 이 메시지는 사용자가 셀에 입력할 수 있는 내용을 상기시키는 데 도움이 됩니다.

"입력 메시지" 탭으로 이동하여 다음을 수행하십시오:

"셀 선택 시 입력 메시지 표시" 옵션을 확인하십시오; 해당 필드에 원하는 제목과 알림 메시지를 입력하세요; 이 대화 상자를 닫으려면 "확인"을 클릭하세요.

이제 유효성 검사가 된 셀을 선택하면 다음과 같은 메시지 상자가 표시됩니다.

3.2) 의미 있는 오류 메시지 생성 (선택 사항):

입력 메시지를 생성하는 것 외에도, 데이터 유효성 검사가 설정된 셀에 잘못된 데이터가 입력되었을 때 오류 경고를 표시할 수 있습니다.

"데이터 유효성 검사" 대화 상자의 "오류 경고" 탭으로 이동하여 다음과 같이 수행하십시오:

"잘못된 데이터가 입력된 후 오류 경고 표시" 옵션을 확인하십시오; "스타일" 드롭다운 목록에서 원하는 경고 유형을 선택하세요: "중지 (기본값)": 이 경고 유형은 사용자가 잘못된 데이터를 입력하지 못하도록 합니다. "경고": 사용자가 데이터를 입력할 수는 있지만, 데이터가 잘못되었음을 경고합니다. "정보": 잘못된 데이터 입력에 대해 사용자에게 알립니다. 해당 필드에 원하는 제목과 알림 메시지를 입력하세요; 대화 상자를 닫으려면 "확인"을 클릭하세요.

잘못된 값이 입력되면, 아래 스크린샷과 같이 경고 상자가 나타납니다.

"중지" 옵션: 값을 다시 입력하려면 "다시 시도"를 클릭하거나 입력을 취소하려면 "취소"를 클릭하십시오.

"경고" 옵션: "예"를 클릭하여 잘못된 입력을 수락하거나, "아니오"를 클릭하여 수정하거나, "취소"를 클릭하여 폐기합니다.

"정보" 옵션: 잘못된 입력을 수락하려면 "확인"을 클릭하고, 이를 취소하려면 "취소"를 클릭하세요.

참고: "오류 경고" 상자에 사용자 정의 메시지를 설정하지 않으면 아래와 같이 기본 "중지" 경고 상자가 표시됩니다.

 3. 데이터 유효성 검사의 기본 예시

데이터 유효성 검사 기능을 사용할 때, 데이터 유효성 검사를 설정하기 위한8가지 기본 옵션이 제공됩니다. 예를 들어, 모든 값, 정수 및 소수, 날짜와 시간, 목록, 텍스트 길이 및 사용자 정의 수식이 있습니다. 이 섹션에서는 Excel에서 몇 가지 기본 옵션을 사용하는 방법에 대해 설명하겠습니다.

3.1 정수 및 소수에 대한 데이터 유효성 검사

1. 정수 또는 소수만 허용할 셀 목록을 선택한 다음, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다.

2. "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 상자에서 해당 항목 "정수" 또는 "소수"를 선택하십시오.
• 그런 다음 "데이터" 상자에서 기준 중 하나를 선택합니다 (이 예에서는 "사이" 옵션을 선택합니다).
• 팁: 기준에는 다음이 포함됩니다: 사이, 사이가 아님, 같음, 일치하지 않음, 보다 큼, 보다 작음, 보다 크거나 같음, 보다 작거나 같음.
• 다음으로, 필요한 "최소" 및 "최대" 값을 입력하세요 (이 경우,0과100 사이의 숫자).
• 마지막으로 "확인" 버튼을 클릭하세요.

3. 이제 선택한 셀에는0에서100 사이의 정수만 입력할 수 있습니다.

3.2 날짜 및 시간에 대한 데이터 유효성 검사

특정 날짜나 시간을 입력할 수 있도록 유효성을 검사하려면, "데이터 유효성 검사"를 사용하여 쉽게 할 수 있습니다. 다음과 같이 진행하세요:

1. 특정 날짜 또는 시간만 허용하고자 하는 셀 목록을 선택한 후 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다.

2. "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 상자에서 해당 항목 "날짜" 또는 "시간"을 선택합니다.
• 그런 다음 "데이터" 상자에서 기준 중 하나를 선택합니다 (여기서는 "보다 큼" 옵션을 선택합니다).
• 팁: 기준에는 다음이 포함됩니다: 사이, 사이가 아님, 같음, 같지 않음, 보다 큼, 보다 작음, 보다 크거나 같음, 보다 작거나 같음.
• 다음으로, 필요한 "시작 날짜"를 입력하세요 (8/20/2021보다 큰 날짜를 원합니다).
• 마지막으로, "확인" 버튼을 클릭하세요.

3. 이제 선택한 셀에는8/20/2021보다 큰 날짜만 입력할 수 있습니다.

3.3 텍스트 길이에 대한 데이터 유효성 검사

셀에 입력할 수 있는 문자 수를 제한해야 하는 경우가 있습니다. 예를 들어, 특정 범위의 텍스트를10자 이하로 제한하려면, 이 "데이터 유효성 검사" 기능이 도움이 될 수 있습니다.

1. 텍스트 길이를 제한하려는 셀 목록을 선택한 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다.

2. "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 상자에서 "텍스트 길이"를 선택하세요.
• 그런 다음, "데이터" 상자에서 기준 중 하나를 선택합니다 (이 예에서는 "보다 작음" 옵션을 선택합니다).
• 팁: 기준에는 다음이 포함됩니다: 사이, 사이가 아님, 같음, 같지 않음, 보다 큼, 보다 작음, 보다 크거나 같음, 보다 작거나 같음.
• 다음으로 제한할 "최대" 숫자를 입력하세요 (텍스트 길이를10 문자 이하로 설정하고 싶습니다).
• 마지막으로, "확인" 버튼을 클릭하세요.

3. 이제 선택한 셀에는10자 미만의 문자열만 입력할 수 있습니다

3.4 데이터 유효성 검사 목록 (드롭다운 목록)

이 강력한 "데이터 유효성 검사" 기능을 사용하면 셀에 드롭다운 목록을 빠르고 쉽게 생성할 수 있습니다. 다음과 같이 하세요:

1. 드롭다운 목록을 삽입할 셀을 선택한 후, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다.

2. "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "일차원 테이블"을 선택합니다.
• "링크 소스" 텍스트 상자에 쉼표로 구분된 목록 항목을 직접 입력하세요. 예를 들어, 사용자 입력을 세 가지 선택으로 제한하려면 Not Started, In Progress, Completed를 입력하거나, 드롭다운 목록을 삽입할 값이 포함된 셀 목록을 선택할 수 있습니다.
• 마지막으로, "확인" 버튼을 클릭하세요.

3. 이제 드롭다운 목록이 아래 스크린샷에 표시된 것처럼 셀에 생성되었습니다.

드롭다운 목록에 대한 자세한 정보를 보려면 클릭하세요…

4. 데이터 유효성 검사를 위한 고급 사용자 정의 규칙

이 섹션에서는 고급 사용자 정의 데이터 유효성 검사 규칙을 만들어 다양한 문제를 해결하는 방법을 소개하겠습니다. 예를 들어, 숫자나 문자열만 허용하는 유효성 검사 수식 생성, 고유한 값만 허용, 특정 전화번호나 이메일 주소만 허용하는 방법 등을 다룹니다.

4.1 데이터 유효성 검사 숫자 또는 텍스트만 허용

데이터 유효성 검사 기능을 사용하여 숫자만 입력할 수 있도록 허용

셀 범위에 숫자만 입력할 수 있도록 하려면 다음과 같이 하세요:

1. 숫자만 입력되도록 하고 싶은 셀 범위를 선택합니다.

2. "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타나는 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행하십시오:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 다음 수식을 입력합니다. ("A2"는 제한하려는 선택한 범위의 첫 번째 셀입니다)

  =ISNUMBER(A2)

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

3. 이제부터 선택한 셀에는 숫자만 입력할 수 있습니다.

참고: 이 "ISNUMBER" 함수는 정수, 소수, 분수, 날짜 및 시간을 포함하여 유효성 검사된 셀에 모든 숫자 값을 허용합니다.

데이터 유효성 검사 기능을 사용하여 문자열만 입력할 수 있습니다.

셀 입력을 텍스트로만 제한하려면, "ISTEXT" 함수를 기반으로 한 사용자 정의 수식을 사용하여 "데이터 유효성 검사" 기능을 사용할 수 있습니다. 다음과 같이 하십시오:

1. 문자열만 입력할 수 있도록 하려는 셀 범위를 선택합니다.

2. "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타나는 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에서 다음 계산을 수행하십시오:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 다음 수식을 입력합니다. ("A2"는 제한하려는 선택한 범위의 첫 번째 셀입니다)

  =ISTEXT(A2)

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

3. 이제 특정 셀에 데이터를 입력할 때, 텍스트 형식의 데이터만 허용됩니다.

4.2 데이터 유효성 검사 알파벳 문자와 숫자 문자만 허용

특정 목적을 위해, ~, %, $, 또는 공백과 같은 특수 문자를 제한하면서 알파벳 문자와 숫자 문자만 허용하고 싶을 수 있습니다. 이 섹션에서는 몇 가지 유용한 방법을 소개합니다.

데이터 유효성 검사 기능으로 알파벳 및 숫자 문자만 허용

특수 문자 입력을 방지하고 알파벳 및 숫자 문자만 허용하려면, "데이터 유효성 검사" 함수에서 사용자 정의 수식을 생성하세요. 다음 단계를 따르세요:

1. 알파벳과 숫자 문자만 입력할 수 있도록 설정할 셀 범위를 선택합니다.

2. "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행하십시오:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택하세요.
• 그런 다음, "수식" 텍스트 상자에 아래 수식을 입력합니다.

  =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 제한하려는 선택한 범위의 첫 번째 셀입니다.

3. 이제 알파벳 문자와 숫자 문자만 입력할 수 있으며, 아래 스크린샷에 표시된 것처럼 특수 문자는 입력이 제한됩니다.

 놀라운 기능을 사용하여 알파벳 및 숫자 문자만 허용

위의 수식은 이해하고 기억하기에 복잡해 보일 수 있습니다. 여기서는 이 작업을 크게 단순화하는 "Kutools for Excel"의 "입력 제한"이라는 유용한 기능을 소개합니다.

1. 알파벳과 숫자 문자만 입력할 수 있도록 하고자 하는 셀 범위를 선택합니다.

2. 그런 다음 "Kutools" > "입력 제한" > "입력 제한"을 클릭합니다. 스크린샷을 참조하세요:

3. 팝업된 "입력 제한" 대화 상자에서 "특수 문자 입력 금지" 옵션을 선택합니다. 스크린샷을 참조하세요:

4. 그런 다음, "확인" 버튼을 클릭하고, 다음 프롬프트 상자에서 "예" > "확인"을 클릭하여 계산을 완료합니다. 이제 선택한 셀에는 알파벳 문자와 숫자 문자만 허용됩니다. 스크린샷을 참조하세요:

4.3 데이터 유효성 검사: 텍스트가 특정 문자로 시작하거나 끝나도록 허용

특정 범위의 모든 값이 특정 문자나 문자열로 시작하거나 끝나야 하는 경우, EXACT, LEFT, RIGHT 또는 COUNTIF 함수에 기반한 사용자 정의 수식을 사용하여 데이터 유효성 검사를 사용할 수 있습니다.

 텍스트가 특정 문자로 시작하거나 끝나도록 단일 조건으로 허용

예를 들어, 특정 셀의 텍스트 항목이 "CN"으로 시작하거나 끝나도록 하려면 다음 단계를 따르세요:

1. 특정 문자로 시작하거나 끝나는 텍스트만 허용하는 셀 범위를 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에서 다음 계산을 수행하십시오:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 아래 수식을 "수식" 텍스트 상자에 입력합니다.
  CN:로 시작하는 텍스트만 입력할 수 있습니다.

  =EXACT(LEFT(A2,2),"CN")

  CN으로 끝나는 텍스트만 입력할 수 있습니다:

  =EXACT(RIGHT(A2,2),"CN")

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 선택한 범위의 첫 번째 셀이고, 숫자 "2"는 지정한 문자 수이며, "CN"은 시작하거나 끝내고자 하는 텍스트입니다.

3. 이제부터는 지정된 문자로 시작하거나 끝나는 문자열만 선택한 셀에 입력할 수 있습니다. 그렇지 않으면 아래 스크린샷과 같이 경고 알림이 나타납니다.

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

참고: 별표 *는 하나 이상의 문자를 일치시키는 와일드카드입니다.

 텍스트가 특정 문자로 시작하거나 끝나도록 여러 기준(또는 논리)으로 허용

예를 들어, 아래 스크린샷과 같이 텍스트 항목이 'CN' 또는 'UK'로 시작하거나 끝나기를 원한다면, 더하기(+) 기호를 사용하여 EXACT의 또 다른 인스턴스를 추가해야 합니다. 다음 단계에 따라 진행하십시오:

1. 여러 기준으로 시작하거나 끝나는 텍스트만 허용하는 셀 범위를 선택합니다.

2. 그런 다음, "데이터" > "유효성 검사" > "유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행하세요:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 아래 수식을 "수식" 텍스트 상자에 입력합니다.
  CN 또는 UK로 시작하는 텍스트만 입력 허용:

  =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")

  CN 또는 UK로 끝나는 텍스트만 입력 허용:

  =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 선택한 범위의 첫 번째 셀이고, 숫자 "2"는 지정한 문자 수이며, "CN"과 "UK"는 시작하거나 끝내고자 하는 특정 텍스트입니다.

3. 이제 선택한 셀에는 지정된 문자로 시작하거나 끝나는 문자열만 입력할 수 있습니다.

=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")

=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

참고: 별표 * 는 하나 이상의 문자를 일치시키는 와일드카드입니다.

4.4 데이터 유효성 검사 허용 항목은 특정 텍스트를 포함해야 하거나 포함하지 않아야 합니다.

이 섹션에서는 Excel에서 데이터 유효성 검사를 적용하여 값이 특정 하위 문자열을 포함해야 하거나 포함하지 않아야 하는 경우, 또는 여러 하위 문자열 중 하나를 포함해야 하는 경우에 대해 설명하겠습니다.

 허용 항목은 하나 또는 여러 특정 텍스트를 포함해야 합니다

입력 항목에는 특정 텍스트가 포함되어야 합니다

특정 문자열을 포함하는 항목을 허용하려면, 예를 들어 모든 입력 값이 아래 스크린샷에 표시된 것처럼 'KTE'라는 텍스트를 포함해야 하는 경우, FIND 및 ISNUMBER 함수를 기반으로 사용자 정의 수식을 사용하여 데이터 유효성 검사를 적용할 수 있습니다. 다음과 같이 하십시오:

1. 특정 텍스트를 포함하는 텍스트만 허용하는 셀 범위를 선택합니다.

2. 그런 다음, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하고, 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에서 다음 계산을 수행하십시오:

• 허용 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 아래 수식 중 하나를 입력합니다.
  대소문자 구분:

  =ISNUMBER(FIND("KTE",A2)) 

  대소문자 구분 안 함:

  =ISNUMBER(SEARCH("KTE",A2))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 선택한 범위의 첫 번째 셀이며, 텍스트 "KTE"는 항목이 포함해야 하는 문자열입니다.

3. 이제 입력된 값에 필수 텍스트가 포함되지 않으면 경고 메시지 상자가 나타납니다.

허용 항목은 여러 특정 텍스트 중 하나를 포함해야 합니다

위의 수식은 하나의 문자열에만 작동합니다. 여러 문자열 중 하나가 셀에 허용되도록 하려면, 아래 스크린샷에 표시된 것처럼 SUMPRODUCT, FIND 및 ISNUMBER 함수를 함께 사용하여 수식을 생성해야 합니다.

1. 여러 항목 중 하나를 포함하는 텍스트만 허용하는 셀 범위를 선택합니다.

2. 그런 다음, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행하십시오:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 필요한 수식을 "수식" 텍스트 상자에 입력하세요.
  대소문자 구분:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0

  대소문자 구분 없음:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0

• 그런 다음, 대화 상자를 닫으려면 "확인"을 클릭하세요.

참고: 위 수식에서 "A2"는 선택한 범위의 첫 번째 셀이고, "C2:C4"는 입력이 포함될 수 있는 값 목록입니다.

3. 이제 특정 목록에 있는 값 중 하나를 포함하는 항목만 입력할 수 있습니다.

허용 항목에는 하나 또는 여러 특정 텍스트가 포함되지 않아야 합니다

허용 항목은 특정 텍스트를 포함하지 않아야 합니다

입력값이 특정 텍스트를 포함하지 않도록 유효성을 검사하려면, 예를 들어 셀에 'KTE'라는 텍스트를 포함하지 않는 값을 허용하려면 ISERROR와 FIND 함수를 사용하여 데이터 유효성 검사 규칙을 생성할 수 있습니다. 다음과 같이 진행하세요:

1. 특정 텍스트를 포함하지 않는 텍스트만 허용하는 셀 범위를 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행하십시오.

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 아래 수식 중 하나를 입력하세요.
  대소문자 구분:

  =ISERROR(FIND("KTE",A2))

  대소문자 구분 안 함:

  =ISERROR(SEARCH("KTE",A2))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 선택한 범위의 첫 번째 셀이며, 텍스트 "KTE"는 항목에 포함되지 않아야 하는 문자열입니다.

3. 이제 특정 텍스트가 포함된 항목은 입력이 제한됩니다.

허용 항목에는 특정 텍스트 중 하나가 포함되어서는 안 됩니다

아래 스크린샷과 같이 목록에 있는 여러 문자열 중 하나라도 입력되지 않도록 하려면 다음 단계를 따르세요:

1. 텍스트 입력을 제한하고자 하는 셀 범위를 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행하십시오:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 아래 수식을 "수식" 텍스트 상자에 입력합니다.
  대소문자 구분:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0

  대소문자 구분 없음:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0

• 그런 다음, 대화 상자를 닫으려면 "확인"을 클릭하세요.

참고: 위 수식에서 "A2"는 선택한 범위의 첫 번째 셀이고, "C2:C4"는 입력값이 포함될 경우 방지하고자 하는 값 목록입니다.

3. 이제 특정 텍스트가 포함된 항목은 입력이 제한됩니다.

4.5 데이터 유효성 검사에서 고유한 값만 허용

셀 범위에 중복 데이터가 입력되지 않도록 하려면, 이 섹션에서는 Excel에서 이 작업을 해결하기 위한 몇 가지 빠른 방법을 소개합니다.

데이터 유효성 검사 기능을 사용하여 고유한 값만 허용

일반적으로, COUNTIF 함수에 기반한 사용자 정의 수식을 사용하는 데이터 유효성 검사 기능이 도움이 될 수 있습니다. 다음 단계를 따라 진행하세요:

1. 고유한 값만 입력하려는 셀 또는 열을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 아래 수식을 입력하세요.

  =COUNTIF($A$2:$A$9,A2)=1

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2:A9"는 고유한 값만 허용하려는 셀의 범위이며, "A2"는 선택한 범위의 첫 번째 셀입니다.

3. 이제 고유한 값만 입력할 수 있으며, 중복 데이터가 입력되면 아래 스크린샷과 같이 경고 메시지가 나타납니다.

 VBA 코드를 사용하여 고유한 값만 허용

다음 VBA 코드는 중복 값이 입력되는 것을 방지하는 데 도움이 될 수 있습니다. 다음과 같이 하십시오:

1. 고유한 값만 허용하려는 시트 탭을 마우스 오른쪽 버튼으로 클릭하고, 컨텍스트 메뉴에서 "코드 보기"를 선택합니다. 팝업되는 "Microsoft Visual Basic for Applications" 창에서 빈 모듈에 다음 코드를 복사하여 붙여넣으세요:

VBA 코드: 셀 범위에서 고유한 값만 허용하기:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

참고: 위 코드에서 "A1:A100" 및 "A:A"는 중복을 방지하려는 열의 셀입니다. 필요에 따라 변경하십시오.

2. 그런 다음 이 코드를 저장하고 닫습니다. 이제 셀 A1:A100에 중복 값을 입력하면 아래 스크린샷과 같이 경고 메시지 상자가 나타납니다.

편리한 기능을 사용하여 고유한 값만 허용

"Kutools for Excel"의 "중복 입력 방지" 기능을 사용하면 몇 번의 클릭만으로 셀 범위에서 중복을 방지하는 데이터 유효성 검사를 빠르게 설정할 수 있습니다.

1. 중복 값을 방지하고 고유한 데이터만 허용하려는 셀의 범위를 선택합니다.

2. 그런 다음 "Kutools" > "입력 제한" > "중복 입력 방지"를 클릭합니다. 스크린샷을 참조하세요:

3. 이 기능을 적용하면 데이터 유효성 검사가 제거된다는 경고 메시지가 나타납니다. "예"를 클릭하고, 다음에 나타나는 프롬프트 상자에서 "확인"을 클릭합니다. 아래 스크린샷을 참조하세요:

4. 이제 지정한 셀에 중복 데이터를 입력하면, 중복 데이터가 유효하지 않음을 알리는 메시지 상자가 표시됩니다. 스크린샷을 참조하세요:

4.6 데이터 유효성 검사 대문자 / 소문자 / 첫 글자 대문자만 허용

데이터 유효성 검사 기능은 사용자가 셀 범위 내에서 대문자, 소문자 또는 첫 글자 대문자 입력을 강제할 수 있도록 도와주는 강력한 도구입니다. 다음 단계를 따라 진행하세요:

1. 대문자, 소문자 또는 첫 글자 대문자 텍스트만 입력하고자 하는 셀 범위를 선택합니다.

2. 그런 다음, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하고, 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택하십시오.
• 그런 다음, 필요한 아래 수식 중 하나를 "수식" 텍스트 상자에 입력하세요.
  대문자 텍스트만 허용:

  =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))

  소문자 텍스트만 허용

  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))

  첫 글자 대문자 텍스트만 허용

  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀입니다.

3. 이제 생성한 규칙에 맞는 항목만 허용됩니다.

4.7 데이터 유효성 검사: 다른 목록에 존재하는 값 / 존재하지 않는 값 허용

다른 목록에 있는지 여부에 따라 값을 허용하거나 방지하는 것은 많은 사용자에게 어려운 작업이 될 수 있습니다. 사실, COUNTIF 함수에 기반한 간단한 수식을 사용하여 데이터 유효성 검사 기능으로 이를 처리할 수 있습니다.

예를 들어, 아래 스크린샷과 같이 범위 C2:C4의 값만 셀 범위에 입력되도록 하고 싶다면, 이 작업을 해결하려면 다음과 같이 하세요:

1. 데이터 유효성을 적용할 셀 범위를 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타나는 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에서 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 필요한 아래 수식 중 하나를 "수식" 텍스트 상자에 입력합니다.
  다른 열에 존재하는 값만 허용

  =COUNTIF($C$2:$C$4,A2)>0

  다른 열에 존재하는 값 방지

  =COUNTIF($C$2:$C$4,A2)=0

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀이고, "C2:C4"는 입력이 해당 값 중 하나일 경우 허용하거나 방지하려는 값 목록입니다.

3. 이제 생성한 규칙에 맞는 항목만 입력할 수 있으며, 다른 항목은 입력이 제한됩니다.

4.8 데이터 유효성 검사 전화번호만 입력할 수 있습니다.

회사 직원의 정보를 입력할 때, 전화번호를 입력해야 하는 열이 있습니다. 전화번호를 빠르고 정확하게 입력하기 위해 이 경우 전화번호에 대한 데이터 유효성 검사를 설정할 수 있습니다. 예를 들어, 워크시트에 (123)456-7890 형식의 전화번호만 입력할 수 있도록 설정하고 싶다면, 이 섹션에서는 이 작업을 해결하기 위한 두 가지 빠른 방법을 소개합니다.

 데이터 유효성 검사 기능으로 전화번호 형식만 입력할 수 있습니다

특정 전화번호 형식만 입력할 수 있도록 하려면 다음 단계를 따르세요:

1. 특정 전화번호 형식을 입력할 셀 목록을 선택한 다음 마우스 오른쪽 버튼을 클릭하고, 컨텍스트 메뉴에서 "셀 형식 설정"을 선택합니다. 스크린샷을 참조하세요:

2. "셀 형식 설정" 대화 상자에서 "숫자" 탭 아래, 왼쪽의 "범주" 목록 상자에서 "사용자 정의"를 선택한 후, 필요한 전화번호 형식을 유형 텍스트 상자에 입력합니다. 예를 들어, "(###) ###-####" 형식을 사용할 것입니다. 스크린샷을 참조하세요:

3. 그런 다음 "확인"을 클릭하여 대화 상자를 닫습니다.

4. 셀을 서식 설정한 후, 다시 선택하고 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하여 "데이터 유효성 검사" 대화 상자를 엽니다. 팝업 대화 상자에서 "옵션" 탭 아래에서 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 수식 텍스트 상자에 다음 수식을 입력합니다.

  =AND(ISNUMBER(A2),LEN(A2)=10)

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 전화번호를 유효성 검사하려는 열의 첫 번째 셀입니다.

5. 이제10자리 숫자를 입력하면, 필요한 특정 전화번호 형식으로 자동 변환됩니다. 스크린샷을 참조하세요:

참고: 입력한 숫자가10자리가 아니면 경고 메시지 상자가 나타나 알림을 제공합니다. 스크린샷을 참조하세요:

 유용한 기능을 사용하여 전화번호 형식만 강제 적용

"Kutools for Excel"의 "전화번호만 입력할 수 있습니다" 기능은 몇 번의 클릭만으로 전화번호 형식만 입력하도록 강제할 수 있습니다.

1. 특정 전화번호만 허용하는 셀 목록을 선택한 후, "Kutools" > "입력 제한" > "전화번호만 입력할 수 있습니다"를 클릭합니다. 스크린샷을 참조하세요:

2. "전화번호" 대화 상자에서 필요한 특정 전화번호 형식을 선택하거나 "추가" 버튼을 클릭하여 사용자 정의 형식을 생성할 수 있습니다. 스크린샷을 참조하세요:

3. 전화번호 형식을 선택하거나 설정한 후 "확인"을 클릭하세요. 이제 특정 형식의 전화번호만 입력할 수 있으며, 그렇지 않으면 경고 메시지가 나타납니다. 스크린샷을 참조하세요:

4.9 데이터 유효성 검사 이메일 주소만 입력할 수 있습니다.

워크시트의 열에 여러 이메일 주소를 입력해야 한다고 가정해 봅시다. 잘못된 이메일 주소 형식의 입력을 방지하기 위해, 일반적으로 이메일 주소 형식만 허용하는 데이터 유효성 검사 규칙을 설정할 수 있습니다.

데이터 유효성 검사 기능을 사용하여 이메일 주소 형식만 허용

사용자 정의 수식을 사용한 데이터 유효성 검사 기능을 통해 잘못된 이메일 주소 입력을 방지하는 규칙을 빠르게 생성할 수 있습니다. 다음 단계를 따르세요:

1. 이메일 주소만 입력할 수 있도록 하려는 셀을 선택한 다음, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다.

2. 팝업된 "데이터 유효성 검사" 대화 상자에서, "옵션" 탭 아래에서 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 다음 수식을 수식 텍스트 상자에 입력하세요:

  =ISNUMBER(MATCH("*@*.?*",A2,0))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀입니다.

3. 이제 입력한 텍스트가 이메일 주소 형식과 일치하지 않으면 경고 메시지 상자가 나타나 알림을 제공합니다. 스크린샷을 참조하세요:

 편리한 기능을 사용하여 이메일 주소 형식만 강제 적용

"Kutools for Excel"은 놀라운 기능인 "이메일 주소만 입력할 수 있습니다."를 지원합니다. 이 유틸리티를 사용하면 단 한 번의 클릭으로 잘못된 이메일 주소를 방지할 수 있습니다.

1. 이메일 주소만 입력할 수 있도록 허용할 셀을 선택한 다음 "Kutools" > "입력 제한" > "이메일 주소만 입력할 수 있습니다"를 클릭합니다. 스크린샷을 참조하세요:

2. 그런 다음 이메일 주소 형식만 입력할 수 있으며, 그렇지 않으면 경고 메시지 상자가 나타나서 알림을 제공합니다. 스크린샷을 참조하세요:

4.10 데이터 유효성 검사 IP 주소만 입력할 수 있습니다.

이 섹션에서는 셀 범위에서 IP 주소만 입력할 수 있도록 데이터 유효성 검사를 설정하는 몇 가지 빠른 요령을 소개하겠습니다.

데이터 유효성 검사 기능으로 IP 주소 형식만 허용

특정 셀 범위에 IP 주소만 입력할 수 있도록 하려면 다음 단계를 따르세요:

1. IP 주소만 입력할 수 있도록 설정할 셀을 선택한 후, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다.

2. 팝업된 "데이터 유효성 검사" 대화 상자에서, "옵션" 탭 아래에서 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 아래 수식을 "수식" 텍스트 상자에 입력합니다.

  =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀입니다.

3. 이제 셀에 잘못된 IP 주소가 입력되면 아래 스크린샷과 같이 경고 메시지 상자가 나타납니다.

 VBA 코드로 IP 주소 형식만 강제 적용

여기에서 다음 VBA 코드는 IP 주소만 입력할 수 있도록 하고 다른 입력을 제한하는 데 도움이 될 수 있습니다. 다음과 같이 수행하세요:

1. 시트 탭을 오른쪽 클릭하고 컨텍스트 메뉴에서 "코드 보기"를 클릭합니다. 열리는 "Microsoft Visual Basic for Applications" 창에서 아래 VBA 코드를 복사하여 붙여넣습니다.

VBA 코드: 셀이 IP 주소만 입력할 수 있도록 유효성 검사

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

참고: 위 코드에서 "A2:A10"은 IP 주소만 허용하려는 셀 범위입니다.

2. 그런 다음 이 코드를 저장하고 닫습니다. 이제 지정된 셀에는 유효한 IP 주소만 입력할 수 있습니다.

간편한 기능을 사용하여 IP 주소 형식만 강제 적용

워크북에 "Kutools for Excel"이 설치되어 있다면, "IP 주소만 입력할 수 있습니다." 기능을 사용하여 이 작업을 해결할 수 있습니다.

1. IP 주소만 입력할 수 있도록 허용할 셀을 선택한 다음, "Kutools" > "입력 제한" > "IP 주소만 입력할 수 있습니다."를 클릭합니다. 스크린샷을 참조하세요:

2. 이 기능을 적용한 후에는 이제 IP 주소만 입력할 수 있으며, 그렇지 않으면 경고 메시지 상자가 나타나서 알림을 제공합니다. 스크린샷을 참조하세요:

4.11 데이터 유효성 검사로 총값을 초과하는 값을 제한

월별 지출 보고서가 있고, 예산 총액이 $18000이라고 가정해 보겠습니다. 아래 스크린샷에 표시된 것처럼 지출 목록의 총 금액이 이 사전 설정된 총액을 초과하지 않도록 해야 합니다. 이 경우, 값의 합계가 사전 설정된 총액을 초과하지 않도록 하기 위해 SUM 함수를 사용하여 데이터 유효성 검사 규칙을 생성할 수 있습니다.

1. 값을 제한하려는 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하고, 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 아래 수식을 입력합니다.

  =SUM($B$2:$B$7)<=18000

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "B2:B7"은 입력을 제한하려는 셀의 범위입니다.

3. 이제 B2:B7 범위에 값을 입력할 때, 값의 총합이 $18,000 미만이면 유효성 검사가 통과됩니다. 어떤 값이 총합을 $18,000 이상으로 만들면 경고 메시지 상자가 나타나 알림을 제공합니다.

4.12 데이터 유효성 검사로 다른 셀을 기준으로 셀 입력 제한

다른 셀의 값을 기준으로 셀 목록에 데이터 입력을 제한하고 싶을 때, 데이터 유효성 검사 기능이 이 작업을 해결하는 데 도움이 될 수 있습니다. 예를 들어, 셀 C1이 텍스트 'Yes'인 경우, 범위 A2:A9는 모든 입력을 허용합니다. 그러나 셀 C1에 다른 텍스트가 포함되어 있으면, 범위 A2:A9의 입력이 제한됩니다. 아래 스크린샷에 표시된 대로입니다.

이 문제를 해결하려면 다음과 같이 하십시오:

1. 값을 제한하고자 하는 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 아래 수식을 입력하세요.

  =$C$1="Yes"

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "C1"은 사용하고자 하는 특정 텍스트가 포함된 셀이며, "Yes"는 셀을 제한하고자 하는 기준 텍스트입니다. 필요에 따라 변경하세요.

3. 이제 셀 C1에 'Yes'라는 텍스트가 있으면 범위 A2:A9에 어떤 값이든 입력할 수 있습니다. 셀 C1에 다른 텍스트가 있으면 값을 입력할 수 없습니다. 아래 데모를 참조하세요:

4.13 데이터 유효성 검사에서는 평일 또는 주말만 입력할 수 있습니다.

월요일부터 금요일까지의 평일 또는 토요일과 일요일의 주말만 셀 목록에 입력하려면 "데이터 유효성 검사"를 사용할 수 있습니다. 다음 단계를 따라 진행하세요:

1. 평일 또는 평일이 입력될 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하고, 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래의 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 필요에 따라 "수식" 텍스트 상자에 아래 수식 중 하나를 입력합니다.
  평일만 허용

  =WEEKDAY(A2,2)<6

  주말만 허용

  =WEEKDAY(A2,2)>5

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀입니다.

3. 이제 선택한 범위에 따라 지정된 셀에 평일 또는 주말 날짜만 입력할 수 있습니다.

4.14 데이터 유효성 검사 오늘 날짜를 기준으로 입력된 날짜 허용

때때로, 셀 목록에 오늘보다 크거나 작은 날짜만 입력할 수 있도록 해야 할 때가 있습니다. "TODAY" 함수와 함께 "데이터 유효성 검사" 기능이 도움이 될 수 있습니다. 다음과 같이 진행하세요:

1. 오늘보다 큰 날짜(미래 날짜)만 입력할 수 있도록 할 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, "수식" 텍스트 상자에 아래 수식을 입력합니다.

  =A2>Today()

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀입니다.

3. 이제 오늘 이후의 날짜만 셀에 입력할 수 있습니다. 그렇지 않으면 경고 메시지 상자가 나타나 알림을 제공합니다. 스크린샷을 참조하세요:

=A2<Today()

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 데이터 유효성 검사에서 현재 시간 기준으로 입력된 시간 허용

현재 시간에 기반하여 데이터를 유효성 검사하고 싶다면, 예를 들어 현재 시간 이전 또는 이후의 시간만 셀에 입력할 수 있도록 할 수 있습니다. 사용자 정의 데이터 유효성 검사 수식을 생성할 수 있으며, 다음과 같이 하십시오:

1. 현재 시간 이전 또는 이후의 시간만 입력할 수 있도록 하고자 하는 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 나타난 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "시간"을 선택하세요.
• 그런 다음 "보다 작음"을 선택하여 현재 시간 이전의 시간만 허용하거나, "보다 큼"을 선택하여 현재 시간 이후의 시간을 허용할 수 있습니다. "데이터" 드롭다운에서 필요에 따라 선택하십시오.
• 그런 다음, "종료 시간" 또는 "시작 시간" 상자에 아래 수식을 입력하세요:

  =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀입니다.

3. 이제 특정 셀에는 현재 시간 이전 또는 이후의 시간만 입력할 수 있습니다.

4.16 특정 연도 또는 현재 연도의 날짜 유효성 검사

특정 연도 또는 현재 연도의 날짜만 입력할 수 있도록 하려면, YEAR 함수를 기반으로 한 사용자 정의 수식을 사용하여 데이터 유효성 검사를 설정할 수 있습니다.

1. 특정 연도의 날짜만 입력하고자 하는 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "사용자 정의"를 선택합니다.
• 그런 다음, 아래 수식을 "수식" 텍스트 상자에 입력하세요.

  =YEAR(A2)=2020

• 이 대화 상자를 닫으려면 "확인" 버튼을 클릭하세요.

참고: 위 수식에서 "A2"는 사용하려는 열의 첫 번째 셀이고, "2020"은 제한하려는 연도입니다.

3. 그런 다음,2020년의 날짜만 입력할 수 있으며, 그렇지 않으면 아래 스크린샷과 같이 경고 메시지 상자가 나타납니다.

=YEAR(A2)=YEAR(TODAY())

4.17 현재 주 또는 월의 날짜에 대한 데이터 유효성 검사

사용자가 특정 셀에 현재 주 또는 월의 날짜를 입력할 수 있도록 하려면, 이 섹션에서는 Excel에서 이 작업을 수행할 수 있는 몇 가지 수식을 소개합니다.

현재 주의 날짜를 입력할 수 있도록 허용

1. 현재 주의 날짜만 입력할 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "날짜"를 선택합니다.
• 그런 다음, "데이터" 드롭다운에서 "사이"를 선택합니다.
• "시작 날짜" 텍스트 상자에 이 수식을 입력하세요:

  =TODAY()-WEEKDAY(TODAY(),3)

• "종료 날짜" 텍스트 상자에 이 수식을 입력하세요:

  =TODAY()-WEEKDAY(TODAY(),3)+6

• 마지막으로 "확인" 버튼을 클릭하세요.

3. 그런 다음, 현재 주 내의 날짜만 입력할 수 있으며, 다른 날짜는 아래 스크린샷과 같이 입력이 제한됩니다.

 현재 월의 날짜 입력 허용

현재 월의 날짜만 입력할 수 있도록 하려면 다음과 같이 하세요:

1. 현재 월의 날짜만 입력할 셀 목록을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭합니다. 팝업된 "데이터 유효성 검사" 대화 상자에서 "옵션" 탭 아래에 다음 계산을 수행합니다:

• "허용" 드롭다운 목록에서 "날짜"를 선택합니다.
• 그런 다음 "데이터" 드롭다운에서 선택하세요.
• "시작 날짜" 텍스트 상자에 이 수식을 입력하세요:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

• "종료 날짜" 텍스트 상자에 다음 수식을 입력하세요:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

• 마지막으로, "확인" 버튼을 클릭하세요.

3. 이제부터 선택한 셀에는 현재 월 내의 날짜만 입력할 수 있습니다.

5. Excel에서 데이터 유효성 검사를 편집하는 방법은?

기존 데이터 유효성 검사 규칙을 편집하거나 변경하려면 아래 단계를 따르세요:

1. 데이터 유효성 검사 규칙이 적용된 셀 중 하나를 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하여 "데이터 유효성 검사" 대화 상자로 이동합니다. 대화 상자에서 필요에 따라 규칙을 편집하거나 변경한 후 "같은 설정을 가진 다른 셀에 이 변경 사항 적용" 옵션을 선택하여 원래 유효성 검사 기준을 가진 다른 셀에 이 새로운 규칙을 적용합니다. 스크린샷을 참조하세요.

3. 변경 사항을 저장하려면 "확인"을 클릭하세요.

6. Excel에서 데이터 유효성 검사가 적용된 셀을 찾고 선택하는 방법은 무엇인가요?

워크시트에서 여러 데이터 유효성 검사 규칙을 생성한 경우, 이제 데이터 유효성 검사 규칙이 적용된 셀을 찾아 선택해야 합니다. "이동" 명령을 사용하면 모든 유형의 데이터 유효성 검사 또는 지정 유형의 데이터 유효성 검사를 선택할 수 있습니다.

1. 데이터 유효성 검사를 찾고자 하는 워크시트를 활성화하고 셀을 선택합니다.

2. 그런 다음 "홈" > "찾기 및 선택" > "선택하여 이동"을 클릭합니다. 스크린샷을 참조하세요:

3. "특수 이동" 대화 상자에서 "데이터 유효성 검사" > "모두"를 선택합니다. 스크린샷을 참조하세요:

4. 데이터 유효성 검사가 적용된 모든 셀이 현재 워크시트에서 선택되었습니다.

팁: 특정 유형의 데이터 유효성 검사를 선택하려면 먼저 원하는 데이터 유효성 검사가 포함된 셀을 선택한 다음 "이동" 대화 상자로 이동하여 "데이터 유효성 검사" > "같음"을 선택하십시오.

7. 데이터 유효성 검사 규칙을 다른 셀에 복사하는 방법은?

예를 들어, 셀 목록에 대한 데이터 유효성 검사 규칙을 생성했으며 이제 동일한 데이터 유효성 검사 규칙을 다른 셀에 적용해야 합니다. 규칙을 다시 생성하는 대신, 기존 규칙을 다른 셀에 빠르고 쉽게 복사하여 붙여넣을 수 있습니다.

1. 사용하려는 유효성 검사 규칙이 있는 셀을 클릭하여 선택한 다음 "Ctrl + C"를 눌러 복사합니다.

2. 그런 다음, 유효성을 검사할 셀을 선택합니다. 인접하지 않은 여러 셀을 선택하려면, "Ctrl" 키를 누른 상태에서 셀을 선택하세요.

3. 그런 다음, 선택한 범위를 오른쪽 클릭하고 "특수 붙여넣기" 옵션을 선택합니다. 스크린샷을 참조하세요:

4. "특수 붙여넣기" 대화 상자에서 "유효성 검사" 옵션을 선택합니다. 스크린샷을 참조하세요:

5. "확인" 버튼을 클릭하면 이제 유효성 검사 규칙이 새 셀에 복사됩니다.

8. Excel에서 데이터 유효성 검사를 사용하여 잘못된 항목을 원으로 표시하는 방법은?

때때로 기존 데이터에 대한 데이터 유효성 검사 규칙을 생성해야 할 수 있습니다. 이 경우 셀 범위에 잘못된 데이터가 나타날 수 있습니다. 잘못된 데이터를 확인하고 수정하는 방법은 무엇일까요? Excel에서는 "잘못된 데이터 원으로 표시" 기능을 사용하여 잘못된 데이터를 빨간색 원으로 강조할 수 있습니다.

잘못된 데이터를 원으로 표시하려면, "유효성 검사" 기능을 사용하여 데이터 범위에 대한 규칙을 설정해야 합니다. 다음 단계에 따라 진행하세요:

1. 잘못된 데이터를 원으로 표시할 데이터 범위를 선택합니다.

2. 그런 다음, "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하고, "데이터 유효성 검사" 대화 상자에서 필요한 유효성 검사 규칙을 설정합니다. 예를 들어, 여기서는500보다 큰 값을 유효성 검사합니다. 스크린샷을 참조하세요:

3. 그런 다음 "확인"을 클릭하여 대화 상자를 닫습니다. 데이터 유효성 검사 규칙을 설정한 후, "데이터" > "데이터 유효성 검사" > "잘못된 데이터 원으로 표시"를 클릭하면500보다 작은 모든 잘못된 값이 빨간색 타원으로 표시됩니다. 스크린샷을 참조하세요.

참고:

• 1. 잘못된 데이터를 수정하면 빨간색 원이 자동으로 사라집니다.
• 2. 이 "잘못된 데이터 원으로 표시" 기능은 최대255개의 셀만 원으로 표시할 수 있습니다. 현재 워크북을 저장하면 모든 빨간색 원이 제거됩니다.
• 3. 이 원은 인쇄되지 않습니다.
• 4. "데이터" > "데이터 유효성 검사" > "유효성 검사 원 지우기"를 클릭하여 빨간색 원을 제거할 수도 있습니다.

9. Excel에서 데이터 유효성 검사를 제거하는 방법은?

범위, 현재 워크시트 또는 전체 워크북에서 데이터 유효성 검사 규칙을 제거하려면 다음 방법을 사용하세요.

데이터 유효성 검사 기능을 사용하여 선택한 범위에서 데이터 유효성 검사 제거

1. 제거하려는 데이터 유효성 검사가 있는 셀을 선택합니다.

2. 그런 다음 "데이터" > "데이터 유효성 검사" > "데이터 유효성 검사"를 클릭하고, 나타나는 대화 상자에서 "옵션" 탭 아래의 "모두 지우기" 버튼을 클릭하세요. 스크린샷을 참조하세요:

3. 그런 다음 "확인" 버튼을 클릭하여 이 대화 상자를 닫습니다. 선택한 범위에 적용된 데이터 유효성 검사 규칙이 즉시 제거됩니다.

팁: 현재 워크시트에서 데이터 유효성 검사를 제거하려면 먼저 전체 시트를 선택한 다음 위의 단계를 적용하십시오.

 선택한 범위에서 데이터 유효성 검사를 제거하는 편리한 기능

"Kutools for Excel"을 사용 중이라면, "데이터 유효성 제한 지우기" 기능을 통해 선택한 범위 또는 전체 워크시트에서 데이터 유효성 검사 규칙을 제거할 수 있습니다.

1. 제거하려는 데이터 유효성 검사가 포함된 셀 범위 또는 전체 워크시트를 선택합니다.

2. 그런 다음 "Kutools" > "입력 제한" > "데이터 유효성 제한 지우기"를 클릭하세요. 스크린샷을 참조하세요:

3. 나타나는 메시지 상자에서 "확인"을 클릭하면 필요한 데이터 유효성 검사 규칙이 지워집니다.

 모든 워크시트에서 데이터 유효성 검사를 VBA 코드로 제거

전체 워크북에서 데이터 유효성 검사 규칙을 제거하려면, 워크시트가 많을 경우 위의 방법은 시간이 많이 소요될 수 있습니다. 여기에서 아래 코드는 이 작업을 빠르게 처리하는 데 도움이 됩니다.

1. "ALT + F11" 키를 눌러 "Microsoft Visual Basic for Applications" 창을 엽니다.

2. 그런 다음, "삽입" > "모듈"을 클릭하고 "모듈" 창에 다음 매크로를 붙여넣습니다.

VBA 코드: 모든 워크시트에서 데이터 유효성 검사 규칙 제거:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. 그런 다음, "F5" 키를 눌러 이 코드를 실행하면, 모든 데이터 유효성 검사 규칙이 전체 워크북에서 즉시 삭제됩니다.