MOS Excel Expert 완전 대비 - 데이터 유효성 검사

1. 데이터 유효성 검사란?

엑셀에서 잘못된 값의 입력을 방지하고, 정확한 데이터를 입력하도록 유도하는 기능입니다.
 

 

2. 어떤 상황에서 사용하나요?

상황	예시
숫자 범위를 제한하고 싶을 때	나이는 0~120 사이 정수만
선택 목록에서만 입력받고 싶을 때	부서는 ‘영업’, ‘기획’, ‘개발’ 중 선택
날짜 제한	입사일은 오늘 이전 날짜만
텍스트 형식 확인	이메일에 @ 포함 여부 확인
텍스트 길이 제한	비밀번호는 8자 이상

➡ 즉, 입력 실수를 방지하거나 특정 기준을 강제하고 싶을 때 사용합니다.
 

3. 시험에서는 어떻게 출제되나요?

난이도	출제 유형	예시
기초	정수 범위 설정	0~100 사이만 입력 가능
중급	목록 지정	드롭다운 메뉴로 과일 선택
기출	사용자 지정 수식	평균 이상, 공란 금지 등
고급	이메일 형식 검사, 날짜 비교	@ 포함 여부, 오늘보다 이전만 허용

출제 포인트:

• 조건을 정확하게 해석할 수 있는지
• 수식이 필요한 문제에서 논리를 적용할 수 있는지
• 메뉴 경로와 옵션 사용법을 알고 있는지

 

 

4. 주요 기능별 정리 + 언제 사용하는지

제한 대상	사용 예시	조건 설정	사용 상황
정수	1~100 정수만	제한값: 사이, 최소~최대	점수, 나이 등
소수	0.0~1.0 허용	제한값: 사이	비율 입력
목록	드롭다운: 사과, 배	원본에 직접 항목 입력	항목 선택 강제
날짜/시간	오늘 이전 날짜만 입력	수식 사용 or 날짜 조건	입사일 제한
텍스트 길이	10자 이내	길이 조건 지정	비밀번호 등
사용자 지정 수식	평균 이상만, 공란 금지 등	논리식 입력	고급 조건 적용

 

반응형

5. 설정 방법

[데이터] 탭 → [데이터 유효성 검사] → 제한 대상 / 수식 / 오류 메시지 설정

입력 메시지 탭

• 셀 클릭 시 힌트 제공
• 예: “이 셀에는 1~100 사이 정수만 입력하세요.”

오류 메시지 탭

• 잘못된 입력 시 경고 표시
• 예: “유효하지 않은 나이입니다.”

 

6. 시험에서 문제를 풀기 위한 논리적 사고 흐름

문제를 받았을 때 이렇게 생각하세요!

생각 단계	질문	예시
1단계	어떤 데이터를 제한하려는가?	숫자? 날짜? 텍스트?
2단계	제한 조건은 무엇인가?	특정 범위? 포함 여부?
3단계	수식이 필요한가?	단순 설정 vs 사용자 지정 수식
4단계	적용 범위는 어디까지인가?	열 전체? 특정 셀?
5단계	오류 메시지를 줄까?	입력 오류 힌트를 주는지 확인

 

7. 자주 출제되는 예시와 논리 적용

🔹 문제 V-1 (기초)

문제: "나이" 열에 0부터 120까지의 정수만 입력할 수 있도록 설정하세요.

✔ 해설:

1. "나이" 데이터가 있는 셀 범위 선택 (예: B2:B100)
2. 메뉴에서 [데이터] → [데이터 유효성 검사]
3. 제한 대상을 정수로 선택
4. 데이터 → 사이
5. 최소값: 0, 최대값: 120

 

🔹 문제 V-2 (기초)

문제: "과일" 열에서 ‘사과’, ‘배’, ‘바나나’ 중에서만 선택할 수 있도록 목록을 설정하세요.

✔ 해설:

1. "과일" 열 범위 선택 (예: C2:C100)
2. [데이터] → [데이터 유효성 검사]
3. 제한 대상: 목록
4. 원본: 사과,배,바나나 입력
5. 확인 클릭
6. 각 셀 클릭 시 드롭다운 목록이 생성됨

SMALL

🔹 문제 V-3 (중급)

문제: "입사일" 열에서 오늘 이후의 날짜는 입력하지 못하도록 설정하세요.

✔ 해설:

1. "입사일" 열 선택 (예: D2:D100)
2. [데이터 유효성 검사]
3. 제한 대상: 날짜
4. 데이터: 보다 작거나 같다
5. 값: =TODAY()

 

🔹 문제 V-4 (기출)

문제: "비밀번호" 열에서 텍스트 길이가 8자 이상 입력되도록 제한하세요.

✔ 해설:

1. 열 선택 (예: E2:E100)
2. [데이터 유효성 검사]
3. 제한 대상: 텍스트 길이
4. 데이터: 크거나 같다
5. 최소값: 8

 

🔹 문제 V-5 (기출 스타일)

문제: "사원번호" 열에서 1000 이상 9999 이하의 4자리 정수만 입력 가능하게 하세요.

✔ 해설:

1. 열 선택 (예: A2:A100)
2. 데이터 유효성 검사 → 제한 대상: 정수
3. 데이터: 사이
4. 최소값: 1000 / 최대값: 9999

 

🔹 문제 V-6 (고급)

문제: "점수" 열에서 입력된 값이 평균 점수 이상만 가능하게 설정하세요. (평균은 셀 F1에 있음)

✔ 해설:

1. 점수 열 선택 (예: D2:D100)
2. 데이터 유효성 검사 → 제한 대상: 사용자 지정
3. 수식: =D2>=$F$1

 

🔹 문제 V-7 (고급)

문제: "이메일" 열에서 '@' 문자가 포함되지 않으면 입력할 수 없도록 제한하세요.

✔ 해설:

1. 열 선택 (예: E2:E100)
2. 데이터 유효성 검사 → 제한 대상: 사용자 지정
3. 수식: =ISNUMBER(SEARCH("@",E2))

728x90

시험 스타일 종합 문제

💯 실전 문제 V-E1

• "성적" 열은 0~100 사이 숫자만 입력
• "이메일" 열에는 반드시 @ 포함
• "입사일"은 오늘 이전 날짜만 입력
• "지점"은 ‘서울’, ‘부산’, ‘대전’ 중 하나만 선택

문제별 풀이 방법

1. 성적 (0~100 사이 숫자만 입력 허용)
   • [데이터] → [데이터 유효성 검사]
   • 제한 대상: 정수
   • 데이터: 사이
   • 최소: 0 / 최대: 100
2. 이메일 (@ 포함)
   • 제한 대상: 사용자 지정
   • 수식: =ISNUMBER(SEARCH("@",B2))
     → 이메일 열의 첫 셀 기준 (B2)
   • SEARCH()로 @의 위치를 찾고 ISNUMBER()로 포함 여부 판단
3. 입사일 (오늘 이전 날짜만 입력 허용)
   • 제한 대상: 날짜
   • 데이터: 작거나 같다
   • 값: =TODAY()
4. 지점 (서울, 부산, 대전 중 하나만 선택 가능)
   • 제한 대상: 목록
   • 원본: 서울,부산,대전
   • 각 셀 클릭 시 드롭다운 생성됨

 

💯 실전 문제 V-E2

• "사원번호"는 반드시 4자리 정수
• "부서"가 공란이면 입력 오류 발생
• "보너스"는 평균 이상만 입력 가능 (평균은 셀 B1)
• "비고"는 30자 이하만 입력 가능

문제별 풀이 방법

1. 사원번호 (4자리 정수만 허용: 1000~9999)
   • 제한 대상: 정수
   • 데이터: 사이
   • 최소: 1000 / 최대: 9999
2. 부서 (공란 금지)
   • 제한 대상: 사용자 지정
   • 수식: =NOT(ISBLANK(B2))
     → B2는 부서 열 첫 셀 기준
   • ISBLANK()으로 빈 셀 여부 확인 → NOT으로 반전
3. 보너스 (평균 이상만 입력 가능)
   • 기준 평균은 예: B1에 저장되어 있다고 가정
   • 제한 대상: 사용자 지정
   • 수식: =C2>=$B$1
     → C2: 보너스 첫 셀 / $B$1: 기준 셀 고정
4. 비고 (30자 이하)
   • 제한 대상: 텍스트 길이
   • 데이터: 작거나 같다
   • 최대값: 30

 

정리 요약

기능 예시	수식 유효성	예
정수 제한	1000~9999	없음 (UI 선택)
텍스트 포함 검사	이메일에 @ 포함	=ISNUMBER(SEARCH("@",A1))
평균 이상 검사	점수 ≥ 평균값	=A1>=$F$1
공란 방지	필수 입력값	=A1<>""
드롭다운 목록	지역 선택	목록: 서울, 부산 등