엑셀 주민번호, 전화번호 입력오류 없는 자료입력 서식 쉽게 만드는 비법

엑셀은 다양한 편리한 계산기능과 동시에 정확한 자료작성을 위한 보조기능들이 많이 있습니다. 그 중에서 오늘은 회원장부나 신청서,명단 등등의 자료를 작성할 때 입력내용을 잘못적는 일이 없도록 딱 지정한 내용만 정확히 적었을 때 입력이 되는 그런 똑똑한 엑셀 기능을 알려드리겠습니다. "데이터 유효성 서식" 이라는 것인데, 아직 사용해본 적 없는 분이라면 이번에 꼭 배워서 엑셀 자료 실수 없이 잘 작성하는 서식 만들어보시면 굉장히 뿌듯하실꺼예요. 특히 여러사람이 자료를 작성할 때 꼭 필요한 기능이니 알아두세요.

 

 

 

각 직원들에게 고객 데이터 작성을 하라고 나누어주고 그걸 합치려고 하는데, 꼭 주민번호 한자리를 빼먹는 사람들이 있죠? 사람은 실수할 수 있으니까요. 그렇지만 엑셀은 절대 실수하지 않습니다. 데이터 유효성 을 이용하면 엑셀 주민번호, 전화번호 입력에 절대 실수 없이 완벽한 데이터를 만들 수 있습니다. 

 

엑셀 주민번호 데이터 유효성 서식 넣기

 

주민등록번호는 전체가 13자리의 숫자로 구성되어 있고 생년월일 6자리 뒤에는 무조건 "-" 하이픈(대시) 기호가 들어가야 합니다. 이 규칙을 엑셀에서 주민번호가 입력되어야 하는 셀에 절대 서식으로 넣는 것을 데이터 유효성 서식 넣기라고 합니다. 간단하게 만들 수 있는 기능이니 걱정마세요. 

 

 

주민번호가 입력되어야 하는 셀을 선택한 후 데이터 탭에서 아래의 아이콘 중에서 데이터 유효성 검사라는 아이콘을 클릭해서 나오는 3개의 드롭박스 중에서 데이터 유효성 검사를 선택합니다.  

 

여기서 설정부분에 주민번호를 정확히 넣는 아래 서식을 넣어주면 됩니다. 

---

=AND(MID(E3,7,1)="-",LEN(E3)=14)

 

 

설정에서 제한대상을 사용자 지정으로 바꾸면 가장 아래서 수식이라는 부분의 활성화 됩니다. 이때 그 속에 위에서 보여드린 수식을 복사해서 붙여넣으면 간단히 끝. 

수식의 내용은 주민번호의 기본 규칙. 즉 7번째 나오는 것은 하이픈이고 전체 길이는 하이픈까지 포함해서 14개의 길이로 들어가야 한다는 내용입니다. 

 

---

 

여기까지 입력을 하고 적용하면 그 셀에 주민번호 숫자가 하나 적게 혹은 많이 들어가면 오류메세지가 뜨게 됩니다. 그런데 이 오류메세지에 주민번호 길이가 혹은 내용이 잘못되었다는 메세지가 나오게 하려면 오류메세지를 수정해주셔야 합니다. 

 

 

오류메세지 탭을 선택해주시고 스타일은 중지 만약에 경고메세지만 띄우고 싶다면 스타일을 경고로 바꾸시면 됩니다. 대신 경고메세지만 뜨게되고 입력이 가능한 상태가 되고 스타일이 중지로 만들면 정확한 형식이 아니면 입력이 되질 않습니다. 그리고 제목을 입력하고 오류 메세지에 오류에 대한 설명을 그림과 같이 적어주세요. 더 귀엽게 적어주시면 입력하는 분이 당황하시지 않으시겠죠? 

 

---

 

이렇게 입력을 마치고 이제 데이터 유효성 검사를 입력해둔 셀에 주민번호 생년월일에서 숫자하나를 작게 입력해 봅니다. 그러면 아래와 같이 팝업이 뜨면서 우리가 미리 작성해둔 오류메세지가 깔끔하게 뜨게 됩니다. 입력해야 하는 사람이 왜 안되는지를 이렇게 정확히 알려주니 당연히 절대 실수 없는 고객 데이터가 작성되겠죠? 

 

 

여기에다가 마지막으로 양념이 될 수 있는 꿀팁하나더. 눈썰미 있으신 분이라면 보셨을 껀데, 주민번호 입력하는 셀을 누르면 노란색의 포스트잇이 뜨는 것을 보실 수 있습니다. 이 셀에는 이렇게 입력해야 한다는 유의사항을 미리 볼 수 있게 하는 기능인데 이 기능도 데이터 유효성 검사에서 만드는 것입니다.

 

셀 입력방법 설명 스티커

 

이렇게 친절하게 미리 셀에 입력되어야 하는 내용과 방법을 설명해두면 자료를 입력해야 하는 사람도 그 방법을 쉽게 알 수 있고 작업이 더욱 쉽게 이뤄질 수 있습니다. 다양한 입력 셀에 이런 스티커를 만들어두면 엑셀 서식을 공유하는 곳에서 여러사람에게 직접 설명하지 않아도 엑셀 파일만 열어서 입력하려고하면 그 방법을 확인할 수 있습니다. 

 

데이터 유효성 검사로 다시 들어가서 설명메세지 탭을 열어서 아래와 같이 작성해주면 

 

 

셀을 클릭했을때 위와 같은 설명 스티커가 나오게 됩니다. 노란색 스티커는 마우스 드래그로 위치조정을 마음대로 할 수 있습니다. 가지런히 스티커 위치를 정해주면 더욱 깔끔한 엑셀 문서가 되겠죠? 

 

이런 좋은 기능 옆에 전화번호에도 만들어 두면 좋겠죠? 

 

 

엑셀 전화번호 데이터 입력 서식 넣기

 

이것도 어렵지 않습니다. 서식만 알고 있으면 그 셀을 클릭하고 그 셀에 맞는 서식을 넣어주면 됩니다. 엑셀 전화번호 입력하는 셀 서식은 아래와 같습니다. 

---

=AND(MID(D3,4,1)="-",MID(D3,9,1)="-",LEN(D3)=13)

 

 

셀을 선택해서 데이터 유효성 검사의 설정에 해당 서식을 넣고 확인을 누릅니다. 

 

 

이렇게 입력을 하고 바로 확인을 해보도록 하겠습니다. 여기 셀에 전화번호가 한자리 부족한 입력내용을 넣었을 경우에 아래와 같이 팝업이 뜨게 됩니다. 

 

 

한 번씩 어떤 분이 데이터유효성 검사 방법을 배워서 서식을 넣었던 문서를 본 적이있습니다. 그런데 경고 메세지 따로 작성하지 않으니 위와 같이 기본엑셀에 저장되어 있는 것을 그대로 나오네요. 

 

---

입력한 값이 잘못되었습니다.

이 셀에 입력할 수 있는 값은 제한되어 있습니다. 

---

 

" 뭘 잘못했다는 거야? " 이렇게 그냥 메세지가 뜨면 내가 무엇을 잘못했는지 알기가 어려워서 더욱 답답한 경우가 생기더라구요. 이렇게 간단한 전화번호라면 다시 입력하면 되지만 조금더 복잡한 내용에 엑셀 파일을 만든사람만이 아는 형식을 요구한다면 직접 물어봐야 해결해야 하는 불상사가 나올 수 있으니 스티커는 만들지 않아도 오류 메세지에는 정확한 내용을 적어주면 좋습니다. 

 

 

이렇게 오류 메세지 입력을 정확히 해두면 입력하는 사람이 실수한 내용을 쉽게 이해해서 정확한 엑셀 데이터를 입력할 수 있겠죠? 

 

마지막으로 입력된 내용이 있는 곳에 이 유효성 데이터 서식을 똑같이 적용할 때 서식복사나 복사 붙여넣기로는 작업이 되지 않습니다. 데이터 유효성 검사가 입력되어 있는 셀과 함께 입력해야 하는 셀 전체를 드래그 해준 뒤 데이터 유효성 검사아이콘을 누르면 

 

 

위와 같이 팝업이 뜨면서 제일 위 셀에 입력해 둔 유효성 검사 서식을 다른 드래그한 셀에도 전체 적용하겠느냐는 메세지 창이 뜹니다. 이걸 예 하고 누르면 전체에 데이터 유효성서식이 입력되어 정확한 전화번호를 입력해야 셀 내용이 입력되게 됩니다. 

 

엑셀 정말 똑똑하지 않나요? 엑셀 역시 알면 알수록 편리한 프로그램입니다. 

 

혹시 전화번호, 주민번호 하이픈 넣는게 귀찮으신 분들이라면 아래 서식을 활용하시면 엑셀 문서 작업 속도가 확실히 빨라질 수 있습니다. 

 

엑셀 전화번호 하이픈 자동입력 방법 2가지로 쉽게 작업하세요.

엑셀 전화번호 하이픈 자동입력 방법 2가지로 쉽게 작업하세요.

전화번호 날짜 자동입력서식으로 퀵 서류작성방법~

[엑셀] 전화번호 날짜 자동입력서식으로 퀵 서류작성방법~