데이터 유효성 검사 사용자 지정 수식

이 튜토리얼에서는 Excel 및 Google 스프레드시트의 데이터 유효성 검사에서 사용자 지정 수식을 만드는 방법을 보여드립니다.

데이터 유효성 검사 – 특정문자로 시작 – Excel

셀의 데이터가 특정 텍스트로 시작하도록 사용자 지정 수식을 작성할 수 있습니다.

1. 필요한 범위를 지정합니다. (예: B3:B8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.

3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=EXACT(LEFT(B3,4),”FRU-“)

이 수식은 2개의 함수 EXACT 및 LEFT를 사용하여 셀에 입력된 처음 4개의 문자가 “FRU-“와 동일한지 확인합니다.

4. 사용자에 대한 입력 메시지를 추가하려면 설명 메시지를 클릭합니다.

5. “셀을 선택하면 설명 메시지 표시” 확인란이 선택되어 있는지 확인한 다음 필요한 제목과 설명 메시지를 입력합니다.
6. 오류 메시지를 추가하려면 오류 메시지를 클릭합니다.

7. 필요한 경우 스타일을 중지에서 경고 또는 정보로 변경한 다음 제목과 오류 메시지를 입력할 수 있습니다.
8. 확인을 클릭합니다.
9. 셀을 선택하면 설명 메시지 표시를 체크한 경우, 사용자에게 규칙을 알리는 댓글이 화면에 표시됩니다.

10. 셀 B3에 “FRI-124″를 입력합니다. 중지를 선택한 경우 다음 메시지 상자가 나타납니다.

11. 경고를 선택한 경우 이 메시지 상자가 나타납니다. 데이터가 정확하다고 판단되면 계속할 수 있습니다.

12. 정보를 선택한 경우 이 메시지 상자가 표시됩니다.

13. 확인을 클릭하면 셀에 잘못된 데이터를 입력해도 계속 진행할 수 있습니다.
14. 메시지를 종료하려면 취소를 클릭하고 셀에 텍스트를 입력하려면 확인을 클릭합니다.

Excel에서 대문자만 허용하는 데이터 유효성 검사

셀에 텍스트를 입력할 때 셀의 데이터가 대문자만 허용되도록 사용자 지정 수식을 설정할 수 있습니다.

1. 필요한 범위를 선택합니다. (예: B3:B8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=EXACT(B3,UPPER(B3))

이 수식은 EXACT 및 UPPER 함수 2개를 사용하여 셀에 입력된 텍스트가 대문자인지 여부를 확인합니다. 숫자와 텍스트가 혼합된 셀은 텍스트로 간주되며 규칙에서 숫자는 무시됩니다.

4. 확인을 클릭합니다.
5. 셀 B3에 “fru-124″를 입력합니다.
6. 오류 메시지 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 표시됩니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.
8. 셀 B3에 “123456”을 입력합니다.
9. 텍스트가 아닌 숫자이므로 허용됩니다.

다음 예제에서는 셀에 대문자 텍스트만 입력될 수 있도록 설정해 보겠습니다.

Excel에서 대문자 텍스트만 허용하는 데이터 유효성 검사

데이터 유효성 검사에서 사용자 지정 수식을 작성하여 셀의 데이터에 대문자 텍스트만 허용하도록 할 수 있습니다.

참고: 셀에 텍스트로 시작하지만 숫자가 포함된 정보를 입력하면 Excel에서 해당 정보를 텍스트로 간주합니다.

1. 필요한 범위를 선택합니다. (예: B3:B8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

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

이 수식은 AND, EXACT, UPPER 및 TEXT 4개 함수를 사용하여 셀에 입력된 텍스트가 대문자인지 확인하고 입력된 정보가 실제로 숫자가 아닌 텍스트인지 확인합니다.

4. 확인을 클릭합니다.
5. 셀 B3에 “fru-124″를 입력합니다.
6. 오류 메시지 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 나타납니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.
8. 셀 B3에 “123456”을 입력합니다.
9. 다시 한 번 오류 메시지가 표시됩니다.
10. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 재 입력합니다.

Excel에서 데이터 유효성 검사로 공백 방지

셀 범위에 입력된 데이터에 공백이 입력되지 않도록 사용자 지정 수식을 작성할 수 있습니다.

1. 필요한 범위를 선택합니다. (예: B3:B8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=B3=SUBSTITUTE(B3, ” ” , “”)

이 수식은 SUBSTITUTE 함수를 사용하여 공백이 없는지 확인합니다.

4. 확인을 클릭합니다.
5. 셀 B4에 “FRU – 124″를 입력합니다.
6. 오류 메시지 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 나타납니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 데이터 유효성 검사로 중복 방지

여러 셀에 중복된 정보를 입력하지 않도록 사용자 지정 수식을 작성할 수 있습니다.

1. 필요한 범위를 선택합니다. (예: B3:B8)
2. 리본에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=COUNTIF($B$3:$B$8,B3)<2

이 수식은 COUNTIF 함수를 활용해 B3:B8 범위를 절대참조로 설정하고 COUNTIF 함수가 중복된 값이 있는지 확인할 때 이 목록을 확인하도록 합니다.

4. 확인을 클릭합니다.
5. 셀 D4에 “FRU-123″을 입력합니다.
6. 오류 메시지 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 표시됩니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 목록에 있는지 데이터 유효성 검사

특정 텍스트만 셀에 입력되도록 사용자 지정 수식을 작성할 수 있습니다.

1. 필요한 범위를 선택합니다. (예: D3:D8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=COUNTIF($F$6:$F$8,D3)>0

이 수식은 COUNTIF 함수를 사용하고 F3:F8 범위를 절대참조로 지정해 COUNTIF 함수가 올바른 텍스트가 입력되었는지 확인할 때 이 목록이 확인되도록 합니다.

4. 확인을 클릭합니다.
5. 셀 D4에 “포기”을 입력합니다.
6. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 표시됩니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 목록에 없는 값 데이터 유효성 검사

사용자 지정 수식을 사용하여 특정 텍스트가 셀에 입력되지 않도록 할 수 있습니다.

1. 필요한 범위를 선택합니다. (예: C3:C8)
2. 리본에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=COUNTIF($F$6:$F$8,C3)=0

이 수식은 COUNTIF 함수를 사용하고 F3:F8 범위를 절대참조로 사용하여 COUNTIF 함수가 올바른 텍스트가 입력되었는지 확인할 때 이 목록이 확인되도록 합니다.

4. 확인을 클릭합니다.
5. 셀 C4에 “소고기”를 입력합니다.
6. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 표시됩니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 숫자만 허용하는 데이터 유효성 검사

사용자 지정 수식을 사용하여 셀에 숫자만 입력되도록 할 수 있습니다.

1. 필요한 범위를 선택합니다. (예 : F3:F8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=ISNUMBER(F3:F8)

이 수식은 ISNUMBER 함수를 사용하여 범위의 셀에 숫자가 입력되도록 합니다.

4. 확인을 클릭합니다.
5. 셀 F4에 “nine”을 입력합니다.
6. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 나타납니다. 이 옵션을 사용하지 않은 경우 표준 경고가 나타납니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 특정 값을 초과하지 않는 데이터 유효성 검사

사용자 지정 수식을 사용하여 셀에 입력된 값이 지정된 값을 초과하지 않도록 할 수 있습니다.

1. 필요한 범위를 지정합니다. (예: E3:E8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=E3<=$G$6

이 수식에는 G6 범위를 절대참조로 입력하여 데이터가 E3에 입력될 때 G6이 규칙이 확인하는 기준값이 됩니다.

4. 확인을 클릭합니다.
5. 셀 E4에 “9”를 입력합니다.
6. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 표시됩니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 합계를 초과하지 않는 데이터 유효성 검사

사용자 지정 수식을 사용하여 셀 범위에 입력된 값이 해당 범위에 대해 지정된 총값을 초과하지 않도록 할 수 있습니다.

1. 필요한 범위를 지정합니다. (예 F3:F8)
2. 리본에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=SUM($F$3:$F$8)<=$H$6

이 수식은 SUM 함수를 사용하며 F3:F8 범위를 절대참조로 지정해 SUM함수 범위 합계가 H6에 입력한 값보다 크지 않은지 확인할 때 이 목록을 확인하도록 합니다.

4. 확인을 클릭합니다.
5. 셀 F4에 “40”을 입력합니다.
6. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 나타납니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

7. 취소를 클릭하여 메시지를 종료하거나 다시 시도를 클릭하여 셀에 올바른 텍스트를 다시 입력합니다.

Excel에서 평일 날짜만 허용하는 데이터 유효성 검사

사용자 지정 수식을 사용하여 Excel에서 날짜를 사용할 때 평일만 입력되도록 할 수 있습니다.

1. 필요한 범위를 지정합니다. (예: G3:G8)
2. 리본메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=WEEKDAY(F3,2)<6

요일 함수는 날짜에 포함된 요일이 토요일이나 일요일이 아닌지 확인합니다.

4. G5에서 날짜를 수정하여 토요일(예: 2020년 5월 9일 )로 표시합니다.
5. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 표시됩니다. 이 옵션을 사용하지 않은 경우에는 표준 경고가 표시됩니다.

7. 취소 또는 다시 시도를 클릭하여 다른 날짜를 입력합니다.

Excel에서 미래 날짜만 허용하는 데이터 유효성 검사

사용자가 미래의 날짜만 입력할 수 있도록 사용자 지정 수식을 만들 수 있습니다.

1. 필요한 범위를 지정합니다. (예: G3:G8)
2. 리본에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.
3. 제한 대상 드롭다운 박스에서 사용자 지정을 선택한 후 다음 수식을 입력합니다.

=G3>TODAY()

이 수식은 TODAY 함수를 사용하여 셀에 입력한 날짜가 오늘 날짜보다 큰지 확인합니다

4. G5의 날짜를 어제 날짜로 수정합니다.
5. 오류 경고 옵션을 사용한 경우 사용자 지정 경고 및 오류 메시지가 나타납니다. 이 옵션을 사용하지 않은 경우 표준 경고가 표시됩니다.

6. 취소 또는 다시 시도를 클릭하여 대체 날짜를 입력합니다.

데이터 유효성 검사는 Google 스프레드시트에서 시작 해야 합니다.

1. 필요한 범위를 지정합니다. (예: B3:B8)
2. 메뉴에서 데이터 > 데이터 확인을 선택합니다.

3. 셀 범위가 이미 채워져 있을 것입니다.

4. 기준 드롭다운 목록에서 맞춤수식을 선택합니다.

5. 수식을 입력합니다.

=EXACT(LEFT(B3,4),”FRU-“)

6. 데이터가 잘못된 경우 경고 표시 또는 입력 거부 중 하나를 선택합니다.

7. 필요한 경우 유효성 검사 도움말 텍스트를 입력할 수 있습니다.

8. 완료를 클릭합니다.

9. 유효성 검사 도움말 텍스트를 보려면 B3을 클릭합니다.

10. FRI-123 입력합니다.
11. 경고 표시를 선택한 경우 다음 메시지가 표시됩니다.

12. 또는 데이터가 잘못된 경우에 입력 거부를 선택한 경우 데이터를 입력할 수 없으며 화면에 다음 메시지가 나타납니다.

Google 스프레드시트의 나머지 사용자 지정 수식 예제는 정확히 같은 방식으로 작동합니다.