엑셀: 입력값 제한-정해진 자릿수만 입력과 콤보박스(데이터 유효성 검사)

업무 중 여러 가지 데이터를

입력하다가 보면 무의식 중에

띄어쓰기를 할 때도 있고

띄어쓰기를 안 할 때도 있습니다.

당연히 띄어쓰기한 것과 안 한 것은

다른 값입니다.

 

이렇게 통일되지 않게 입력하면

나중에 데이터를 통합할 때 일일이

띄어쓰기를 통일해줘야 합니다.

 

특히 자료를 여러 사람에게 배포 후

데이터를 수합할 때 선택 목록을

만들어 주지 않으면 데이터명이

제각각이 되기 때문에

문서 배포시 콤보박스를

선택하도록 하는 것은 필수입니다.

 

그래서 데이터의 선택목록을 만들어서

선택하도록 하는 것이 필요합니다.

 

[데이터 유효성 검사]를 이용하면

콤보박스를 만들어 정해진 데이터를

선택하도록 강제하거나

자릿수 등 입력값에 조건을

걸어서 정해진 양식을

충족 못하면 입력을 못하게 하여

사전에 오류를 방지할 수 있습니다.

사업자등록번호 10자릿수 모자르게 입력할 때 오류메시가 뜹니다.

 

 콤보박스 만들기를 포함한

[데이터 유효성 검사]의기능에

대해서 설명 드리겠습니다.

 

 데이터 유효성 검사 종류

 

데이터 유효성 검사의

유효성 조건의 종류는 여러 가지가 있습니다.

유효성 조건 종류에 따른 설명과

예시를 참고하여

필요하신 조건을 쓰시면 됩니다.

조건을 걸어주실 때는 먼저 적용될 범위부터

선택을 해야 합니다.

 입력값 제한 설정(정해진 숫자 자릿수만 입력되게 하기) 

데이터 유효성 검사 종류 이외에도

사용자가 입력값에 제한을 설정 

할 수 있습니다.

[사용자지정]을 사용하면 됩니다.

 

여기서는

입력값 자릿수를 충족 시키지 못할 시

오류 문구가 뜨게하는 조건을 걸어서

입력값 제한을 설정하겠습니다.

 

사업자등록번호(10자리)의 자릿수를

만족하지 못할 시 

오류문구가 뜨는 서식을 만들면 됩니다.

그래서

문자수를 세는 함수를 이용해야 합니다.

LEN함수입니다.

LEN 함수

 

-함수설명

선택된 셀의 문자수를 셉니다

 

-함수형식

=LEN(셀주소)

 

-예시

 

 

수식만들기

 

사업자등록번호가 입력될 셀범위를

선택합니다.

 

[데이터]→[데이터 유효성 검사]→[데이터 유효성 검사] 이동합니다.

[제한대상]에서 [사용자 지정]

선택합니다.

수식을 만듭니다.

수식의 조건은

 

① 10개의 문자여야합니다.

② 숫자로만 입력을 해야합니다.

 

2개의 조건을 걸면 됩니다.

 

①부터 만듭니다.

처음에 B2부터

범위를 지정했기 때문에

B2의 문자 개수가 10개여야 합니다.

LEN함수로 입력합니다.

 

=LEN(B2)=10

 

*아래 셀들은 저절로 행이 바뀌어서

함수가 적용됩니다.

 

그런데 이렇게 만들면

숫자가 아닌

글자 10개를 입력해도 됩니다.

 

그래서

②조건도 만듭니다.

숫자여야 합니다.

여기서는 INT함수가 쓰입니다.

 

 INT함수는 정수로 만드는 함수입니다.

 

-함수형식

INT(셀주소)

 

예를들어

10.5가 쓰여져 있는 셀에

INT함수를 한다면

10으로 나타납니다.

 

즉, INT함수 안에는 숫자만 들어와야 합니다.

그래서

 

=INT(B2)

 

이렇게 서식을 쓰면

B2에는 숫자만 쓰라는

뜻이 됩니다.

 

두조건을 모두 만족 시킬때는

AND함수 입니다.

 

최종 수식은

 

=AND(INT(B2),LEN(B2)=10)

 

이것을 수식에 입력하고 

확인을 누르면 됩니다.

 

아래 처럼 기준이외의

데이터를 입력하면 

오류메시지가 뜹니다. 

아래처럼 숫자10자리만 입력하면

메시지가 뜨지 않습니다.

 

중간에 하이픈이 생성되도록

[사용자지정]서식에서 설정합니다.

 

[사용자지정]에 대한

자세한 설명은 밑에 링크를 

참고하시기 바랍니다.

 

엑셀: 셀 서식으로 주식처럼 증감(▲▼)표시를 나타내기(사용자지정 서식 설명)

 

Ctrl+1을 누릅니다.

위에 그림처럼

[사용자 지정]으로 가셔서 [형식]에

000-00-00000을

입력합니다.

그럼 저절로 하이픈이 

생성됩니다.

 

이젠 하이픈을 입력하지 말라고

설명메시지를 입력합니다.

[데이터 유효성]의 [설명메시지]탭에서

입력하면 됩니다.

 

 콤보박스

 

콤보박스 만들기

 

우선 콤보박스가

생성될 범위를 지정 합니다.

데이터 유효성 검사에서

콤보박스를 만들 겁니다.

[데이터]→[데이터 유효성 검사]→[데이터 유효성 검사]

이동합니다.

아래와 같이 화면에서

[설정]탭의 [제한대상]에서 [목록]을 선택합니다.

그후 밑에 [원본]에 목록을 입력해 줍니다.

콤마(,)로 구분하여 입력하면 됩니다.

같은 시트나 다른 시트에

목록명이 이미 만들어져 있다면

아래처럼 셀범위를 지정해 줘도 됩니다.

아래처럼 다른 시트도 가능합니다.

*다른 시트의 셀주소를 불러올때는 주소 앞에

'시트명!'

가 붙습니다.

과자명 시트의 주소를 참조하기 대문에

앞에 과자명!가 붙었습니다.

[원본]에 목록을 다 지정 후 확인을 누르면

범위에 콤보박스가 생성됩니다.

반대로 만든 콤보박스를

지우기 위해서는

지우길 원하는

범위를 선택하신 후

[데이터 유효성]의 설정 탭에서

모두 지우기를 선택하시면 됩니다.

 

콤보박스에 설명 넣기

 

콤보박스에 대한 설명을 넣을 수 있습니다.

설명이 나타나길

바라는 셀 범위를 선택합니다.

[설명 메시지] 탭으로 이동해서

메시지를 적으면 됩니다.

해당 콤보박스 셀을 선택하면

설명이 뜹니다.

 

콤보박스에 임의의 값 입력 가능하게 하기

 

콤보박스사 적용된 셀에

목록에 없는 내용을 입력하면

오류메시지가 뜹니다.

감자깡은 목록에 없는 과자입니다.

 

그러나 예상치 못한 경우를 대비하여

입력 가능하도록 해 놓을 수 있습니다.

범위를 선택 후

[데이터 유효성]의 [오류메시지] 탭에

들어가서

[유효하지 않은 데이터를 입력하면오류메시지 표시]

를 체크 해제하면

임의의 값도 입력이 가능합니다.

목록에 없는

감자깡이 입력이 가능합니다

또한 위에 [오류메시지]에 원하는 내용을

입력하여 오류메시지를 띄울 수도 있습니다.