{엑셀} '데이터 유효성 검사' - 필수사용

안녕하세요? 제임스입니다. 엑셀 작업을 할 때 많은 데이터를 입력해야 할 경우가 많지요? 또 데이터는 나중에 어떤 형태로는 분석하고 관리하기 위하여 입력하게 됩니다. 그러니 분석 시에도 예외 사항 없이 분석할 수 있으면 더 좋겠죠.

오늘은 그런 면에서 입력도 쉽게 할 수 있고 나중에 예외 사항 없이 관리할 수 있는 방법을 말씀드리려고 합니다. 엑셀은 아주 많은 기능을 갖고 있어서 실제로 모든 기능을 다 활용하긴 어렵습니다. 대부분 자신이 알고 있는 것을 활용하지요.

 

이번에 말씀드리는 데이터유효성 의 목록 입력 방법은 아주 쓰임새가 높은 활용방법입니다. 그럼 시작하겠습니다.

---

인력관리

---

예를 들어 인력관리를 하는 작업을 한다고 가정하겠습니다. 성명, 전화번호, 학력, 전공, 기술 이렇게 입력 항목을 만들고 앞으로 학력, 전공, 기술로 분석을 할 것이라 생각할 경우 사용하는 방법입니다. 데이터 유효성 검사는 불량 데이터를 입력 단계에서부터 제외할 수 있다는 점에서 아주 유용합니다.

나중 작업을 편하게 하기 위하여 시트 이름은 "인력"이라 하겠습니다. 지금부터는 가급적 화면에 색깔로 표기하는 것을 안 할까 합니다. 여러분이 제가 말하는 것을 스스로 찾아서 볼 수 있게 되기를 바라는 마음에서입니다. 자 아래 화면엔 시트 이름이 인력으로 바꾸었네요. 보이시죠?

---

분석이 예상되는 항목들

---

위 입력 항목 성명, 전화번호, 학력, 전공, 기술 중 학력, 전공, 기술 항목은 반복 입력이 되고 있고 나중에 이 항목들로 분석 (분류) 하게 되겠지요. 위 샘플로 보면, 학력에 중졸, 고졸, 대졸, 대학원졸 등으로 입력이 되고 있지요. 그리고 전공도 그렇고 기술도 그렇습니다.

 

그렇다면 학력, 전공, 기술 을 다른 곳에 미리 입력해 두고, 이 인력관리 시트에서 불러다가 쓰면 좋겠죠? 입력 방법은 콤보 박스로 입력하면 더 좋을 겁니다. 입력할 때 타이핑을 하는 것이 아니라 선택을 할 수 있도록 만드는 거죠. 오늘 시트는 자료를 다운로드할 수 있도록 하겠습니다.

---

3가지 항목 별도 테이블 만들기

---

먼저 화면을 보여드렸어요. 두 번째 시트엔 이름을 "참고" 라 하였고, 이 시트에 3가지 항목을 미리 만들어 둡니다. 하나는 학력인데, 입력할 내용을 아래로 중졸, 고졸, 대졸, 대학원졸 이렇게 입력했습니다. 학력에 들어갈 내용을 모두 입력하시면 됩니다.

 

전공엔 전기, 기계, 토목, 건축, 화학 등의 항목을 입력했습니다. 기술도 관련 내용을 입력합니다.

---

테이블 이름 정의하기

---

지난번 이름 정의에 대해 설명드렸는데, 기억나시나요? 만일 못 보셨다면 아래 링크를 보세요.

 

{엑셀} 셀 이름 정의 하기 : 필독

 

이번엔 한 개의 테이블 전체 이름을 정의하면 됩니다. 지정하는 방법은 이렇습니다. 먼저 지정할 테이블을 선택합니다. 아래 화면처럼 회색으로 마킹이 되어야 합니다.

다음 수식 -> 정의된 이름 -> 이름 정의 를 클릭합니다.

그럼 아래 화면처럼 새 이름 다이얼로그 박스가 나옵니다.

[이름] 항목엔 '학력'이라고 입력하고 범위는 '통합 문서' 이건 나중에 또 설명드리겠습니다. 일단 건드리지 않습니다. 다음 참조 대상 이 부분이 중요합니다. 이 항목엔 좀 전에 마우스로 범위를 지정했던 항목이 들어가 있을 거예요. 이걸 확인해 보셔야 합니다.

 

=참고!$A$2:$A$5 이렇게 입력이 되어 있으면 됩니다. 이 뜻은 참조 시트의 A2에서 A5까지 항목이란 뜻인데, $가 붙어 있으니 변하지 않는 절대값을 말합니다.

 

그리고 [확인]을 클릭하여 지정을 마칩니다.

 

다음은 전공 테이블을 아래 화면과 같이 이름을 지정합니다.

다음은 기술 테이블을 아래 화면과 같이 이름을 지정합니다. 참조 대상이 잘 지정되었는지 확인하셔야 하세요.

자 이제 각 테이블의 이름 정의 가 되었습니다. 참고 시트 작업은 모두 완료되었습니다.

---

학력 목록으로 입력하기

---

아래 화면을 보면 학력 부분에 텍스트로 일일이 입력하였습니다. 매 셀마다 대졸, 고졸을 입력해야 하죠. 많은 작업을 할 땐 정말 귀찮은 일입니다. 그럴 경우에 사용하시면 아주 편리하죠. 이제 콤보박스로 입력할 수 있도록 만들어 보겠습니다.

먼저 학력 부분을 선택합니다. 현재 C9까지 입력했지만, 이후 계속 작업을 할 것이므로 아래 입력해야 할 곳까지 예를 들어 C14까지만 할 것 같다면 C14까지 선택합니다. 일반적으론 더 길에 아래쪽까지 하는 것이 좋겠죠? 레코드의 수가 겨우 13개 정도만 되진 않을 테니까요.

 

데이터 -> 데이터 도구 -> 데이터 유효성 검사 를 클릭합니다.

클릭하면 아래와 같은 다이얼로그 박스가 나옵니다. 타이틀이 데이타 유효성 입니다. #데이타유효성

 

위에 텝이 4개 있네요. [설명] [설명 메세지] [오류 메세지] [IME 모드] 오늘은 당장 필요한 부분만 설명드리도록 하겠습니다.

 

설명에 보면 유효성 조건이라고 되어 있네요. 그 아래 제한 대상, 제한 방법, 원본 이렇게 있습니다. 아무것도 건드리지 말고 제한 대상을 목록으로 설정합니다.

 

다음 원본 항목에 =학력 이렇게 입력합니다. 그다음 아래 [확인]을 클릭합니다. 콤보 박스로 입력할 수 있는 설정이 되었습니다.

화면을 볼까요? 인력 시트의 학력 셀의 첫번째 셀인 C3 옆에 보면 콤보박스 표시가 만들어 졌네요. 이제부터 콤보박스에서 값을 선택하여 입력할 수 있습니다.

자 이제부터는 클릭으로 학력을 입력할 수 있게 되었네요. 입력해 볼까요? 학력을 입력할 아래 빈칸 C10을 보세요, 오른쪽에 콤보박스 아이콘이 보이시죠?

이걸 클릭하면 앞에 학력으로 이름을 정의했던 것들의 목록이 보입니다. 여기서 마우스로 고졸 을 선택하면

아래와 같이 입력이 됩니다.

남은 작업을 전공, 기술도 같은 방법으로 하시면 되겠죠?

다 되었네요. 오늘 배우신 이 내용은 실제 작업에서 아주 많이 사용되는 작업입니다. 기억하셨다가 실무에서 잘 활용하시길 바랄게요.