엑셀 유효성 검사에 대해 포스팅하고자 한다.

"유효성 검사"라는 용어가 어렵게 느껴지지만, "입력값 제한하기"라고 표현하면 무슨 말인지 조금 친숙하게 다가온다.

 

이 기능은 셀에 입력하는 데이터 형식 또는 값을 제한 할 수 있는 기능이다. 예를 들어, "00월 실적보고서"에서 "00"란에 숫자 1부터 12까지만 입력되도록 제한하는 것이다. 이렇게 함으로써 "13월 실적보고서 "라고 적는 실수를 미연에 방지할 수 있다. 또한 파일을 여러 부서에 배포하여 작성할 때, 입력부서마다 개성 있게 입력된 값으로 파일이 취합되면, 정리하는데 많은 시간이 걸린다. <그림1>에서처럼 파일을 작성하는 사람에 따라 부서명을 빨간 상자처럼 입력하면, 파일관리자는 파란 상자처럼 다시 정리해야 하는 번거로움이 생기는 것이다. 이런 불필요한 업무를 줄여 퇴근시간을 당겨주는 것이 유효성 검사 기능이다.

 

※ 본 포스팅에서 사용된 이름, 부서, 직급 등은 랜덤(임의방식)으로 작성된 것으로써 동일한 조건을 가진 사람이 있다면 100% 우연에 의한 것임을 밝혀둔다.

 

 

<그림1. 팀별 2017년 실적을 부서별로 작성 후 수합한 파일>

- 부서명은 "영업1팀, 영업2팀, 영업3팀, 영업4팀, 신규사업팀, 해외영업팀"으로 구분되어 있는데, 각 팀별 자료입력자가 자기부서 이름을 "1팀, 영업팀, 영2, 신사팀, 해영" 등으로 입력하면 실적 정리가 안 된다. 그러면 파일관리자는 파란 상자처럼 부서명 작업을 다시 해야 하는 번거로움이 생긴다.

 

 

<그림2. 유효성 검사 적용 방법1>

- 유효성 검사를 적용할 셀을 선택(빨간상자)한 후 "데이터 - 데이터 유효성 검사 - 데이터 유효성 검사"를 차례로 찾아 들어간다.

- 엑셀은 데이터 유효성 검사를 "잘못된 데이터를 셀에 입력하는 것을 방지합니다."라고 정의한다.

 

 

<그림3. 유효성 검사 적용 방법2>

- "데이터 유효성"이라는 대화창의 제한 대상 기본 설정 값은 "모든 값" 이므로, 입력 제한이 없는 상태이다.

- 제한 대상을 설정함으로써 잘못된 데이터 입력을 막을 수 있다.

 

 

<그림4. 유효성 검사 적용 방법3>

- 제한 대상은 정수, 소수점, 목록, 날짜, 시간, 텍스트길이, 사용자지정 등으로 나눠져 있으며, 사용 목적에 맞는 것을 선택하면 된다.

- 여기서는 특정부서명만 입력하도록 제한할 것이므로 "목록"을 선택한다.

 

 

<그림5. 유효성 검사 적용 방법4>

- 제한 대상이 목록으로 입력되면, 목록을 만들어줘야 한다. 아래 원본 란에 목록을 콤마(,)로 구분하여 입력한다.

- 목록을 드롭다운으로 보여줄 것이기 때문에 "드롭다운 표시" 기능은 자동 선택되어 진다.

 

 

<그림6. 유효성 검사 적용 방법5>

- 설정을 완료하면 녹색 상자처럼 드롭다운 버튼이 생기고, 그걸 누르면 위에서 입력한 목록이 생성된다. 데이터 입력자들은 부서명을 입력하지 않고 목록에서 선택하면 되는 것이다.

 

 

<그림7. 유효성 검사 적용 방법6>

- 목록에서 선택하지 않고 직접 입력 할 수도 있는데, 목록에 없는 값을 입력하게 되면 위와 같은 안내 메시지를 만날 수 있으며 입력되지 않는다.

- 브라보!

 

 

<그림8. 유효성 검사 적용 방법7>

- <그림5>에서 목록을 콤마(,)를 이용해 입력하는 방법 이외에도 sheet 어딘가에 또는 다른 sheet에 목록을 입력하고, 그 영역을 선택하는 방법도 있다.

 

 

 

설명된 바와 같이 생각보다 간단하다. 하지만 이 간단한 작업을 통해 자료입력자들은 예상되는 실수를 미연에 방지할 수 있고, 파일관리자는 불필요한 정리 작업을 하지 않아도 되기에 퇴근시간을 당길 수 있다. 특히 하나의 파일에 많은 사람들이 작업을 하고 취합하여 정리하는 경우, 이 작업은 필수적이다. 입력되지 말아야 하는 데이터를 입력 단계에서 제한함으로써 불필요한 문의(전화)를 하지 않게 되어 서로의 업무효율을 높일 수 있다. 그리고 데이터베이스 관리 차원에서도 꼭 필요한 작업이다. <그림1>의 빨간 상자와 같이 부서명이 중구난방으로 입력되면, 부서명으로 통계(sumif, countif, vlookup, 피벗테이블 등) 작성이 불가능해진다. 일관성 있는 데이터 관리를 위해 꼭 필요한 작업이니 기억하고 활용하도록 하자.

 

 

2018.01.13. 코리.

 

 

 

관련된 다른 글 보기

[한글] 엑셀에서 복사한 한글 표 배경색 바꾸기

[한글] 문서 비교하기

[엑셀] 불필요 공백, 점, 글자 지우기 (ctrl + h)

[업무tips] 퍼센트(%)와 퍼센트포인트(%p)

[업무tips] 글자 수 세기

[윈도우] 윈도우10에서 예전 그림판 사용하기

+ Recent posts