[엑셀 스터디 #14] 데이터 유효성 검사

 

 

 

서론

데이터를 남에게 입력 받아야할 때, 데이터 유효성 검사가 쓰인다. 실무에서 여러 사람과 파일을 공유할 때, 내 파일이 한 번 돌고 돌면. 분명 보낼 때는 멀쩡한 파일이였다가 돌아올 땐 엄한 데이터들이 뒤죽박죽 섞여서 올 수도 있다. 이 때, 데이터 유효성 검사를 사용하면 사용자가 특정 셀들에 대해, 내가 설정해놓은 값들 중에서만 고를 수 있게 된다. 서론보단 본론이 낫겠다.

 

데이터 유효성 검사

1. 원하는 형태의 데이터로 제한 가능.

데이터 유효성 검사를 활용하면 작성자가 원하는 형태의 데이터만 입력 가능하도록 제한할 수 있다. 기본적으로 총 7개의 형식을 지원한다. 형식을 지정하고 조건을 걸 수 있다.

 

* 조건부서식은 조건에 부합하면 서식을 주고, 데이터유효성검사는 조건에 부합하지 않으면 작성을 불허한다.

 

예시와 같이 정수로 제한, 0~31 사이라는 조건을 걸었고 입력 데이터가 조건에 부합하지 않으면 오류 메세지를 내보내며, 값은 입력되지 않는다.

 

---

 

 

2. 목록 사용법

실무에서 가장 많이 쓰이는, 데이터 유효성 검사로 가장 많이 사용하는 목록. 목록을 추가하면 서론에서 말한 것과 같이 엄한 데이터 입력을 사전에 방지할 수 있다. 내가 원하는 데이터를 미리 목록으로 만들어 사용자에게 선택하게 하니까.

 

2가지 방법이 예시로 나오는데, 직접 입력과, 범위 선택이다. 직접 입력 시에는 ','를 기준으로 값을 입력하면 된다. 직접 입력보다 범위 선택이 더 낫다. 범위로 목록을 만들 경우, 목록의 실시간 업데이트가 가능하기 때문이다. 사전에 범위를 공백과 함께 잡아 요소를 추가할 수도 있고 요소를 수정할 수도 있으니.

 

그리고 앞서 말했듯 지정되지 않은 값이 입력되면 오류 메세지를 띄우면서 입력되지 않는다.

 

---

 

 

3. 오류 메세지 

오류 메세지를 어떻게 출력할지 내용을 수정할 수 있다.

 

 

4. 안내 문구

데이터 입력을 돕기 위해 항목 셀 선택 시 메모와 유사한 안내 문구를 추가할 수 있다.

 

---

 

 

5. 잘못된 데이터 잡기

목록 범위에 맞지 않는 잘못된 데이터가 이미 들어가있을 수 있다. 이 경우엔 잘못된 데이터를 활용해서 오타나 잘못된 데이터를 잡아낼 수 있다. 예시를 보고 넘어가겠다.

 

잘못된 데이터의 단점은 데이터의 수가 천 개씩 되는 무거운 표에서는 일일이 다 확인하기도 어렵고 수정도 어렵다. 그래서 이를 해결해줄 함수가 COUNTIF다.

 

5-1. COUNTIF를 활용한 잘못된 데이터 잡기

 

 

---

 

 

6. IME 모드

IME 모드는 셀 범위에 대해서 영문 입력 모드, 한글 입력 모드를 정할 수 있는 기능이다. 사용자가 굳이 한/영을 고려하지 않아도 알아서 써지는. 그야말로 센스 있다는 소리 들을 수 있는 실무 기능인 것 같다.

 

IME 모드가 설정된 셀 범위에서는 잠깐 영문이나 한글 모드로 바뀌고 다시 다른 셀을 선택하면 원래 있던 언어 모드로 돌아온다. 이론상 IME 모드만 잘 쓰면 한/영 쓰지 않게 해줄 수도 있다.

 

---

 

 

7. '사용자 지정'을 활용한 핸드폰 번호 예제

이번엔 사용자 지정으로 데이터 제한 유형을 선택하고 핸드폰 번호가 올바르게 입력될 수 있도록 하는 예제를 해보겠다.

 

우선 조건 3가지를 수식으로 만들어야 한다.

 

① "010"으로 시작해야 함. → LEFT(셀, 3)="010"

② 4번째, 9번째 자리는 '-'이어야 함. → AND(MID(셀, 4, 1)="-", MID(셀, 9, 1)="-")

③ 글자 수는 총 13자리 → LEN(셀)=13

 

이 3개의 수식을 AND로 묶으면 된다.

최종 수식 → =AND(LEFT(셀, 3)="010", AND(MID(셀, 4, 1)="-", MID(셀, 9, 1)="-"), LEN(셀)=13)