블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/12 »
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 

'셀서식 단축키'에 해당되는 글 1

  1. 2014.12.31 휴대폰 전화국번 변경 자동 찾기 엑셀 파일 만들기
 


어제 2세대 이동전화서비스(2G)용으로 부여된 식별번호가 3세대 이동전화서비스(3G)로 전환된 사실에 대해서 간략하게 설명 드렸습니다.


오늘은 사업자별 변환용(01X → 010) 국번호 부여내용으로 변환된 전화번호를 구하는 엑셀 파일을 만들어 보겠습니다.


VLOOKUP 함수와 INDIRECT 함수, 그리고 기본적인 IF 함수를 사용할 것이고,

이중 유효성 검사와 간단한 셀서식 지정법이 활용되며, 셀 수식을 변경하지 못하도록 하는 셀보호 기능을 마지막에 활용할 예정입니다.


앞서 밝힌 내용에 대해 잘 아시는 분이나, 그냥 변경된 전화번호를 찾아주는 서비스만 필요하신 분은

다음 첨부한 파일을 그냥 다운받아서 사용하시면 되겠습니다.(대신 수식을 확인하고 싶은 분을 위해 Sheet2에는 잠금이 걸지 않으니 참고하시면 되겠습니다.)


phone-ch.xlsx


아래 화면과 같은 파일을 만들어 보겠습니다.




첨부된 파일을 열어서 범위를 확대하면 아래와 같이 VLOOKUP 함수에서 참조할 영역이 포함되어 있는 것을 알 수 있습니다.




아래 보시는 바와 같이 통신사와 구분이 입력되는 B3셀과 B4셀에 이중 데이터 유효성 검사를 지정할 예정이고,

(통신사에 따라 식별자 011, 017 등이 자동으로 유효성 검사에서 적용)


국번호 변경국에 대한 데이터는 010번호통합 정책 사이트에 있는 사업자별 변환용(01X → 010) 국번호 부여내용의 표를 참조하여 작성한 것입니다.(011~019까지를 각각 이름 정의하고 INDIRECT 함수를 이용하여 동적 참조영역을 활용)

VLOOKUP 함수에서 간격으로 찾는 방법은 배웠죠. 크거나 같다로 찾을 경우 오름차순으로 배열해야 한다는 사실도 기억하고 있죠. 




이중 데이터 유효성 검사를 위해 각 통신사별 식별번호를 선택영역을 이용해 이름을 지정하도록 하겠습니다.


G2:G4, H1:H4, I2:I3의 범위를 Ctrl키를 이용하여 모두 지정하고 

[수식] - 이름관리자에 있는 [선택 영역에서 만들기]를 클릭한 뒤,

이름 만들기 창이 열리면 [첫 행]에 체크하고 [확인] 버튼을 누릅니다.




제대로 된 지 확인을 하기 위해 [수식] - [이름관리자]를 클릭하면 아래와 같이 이름관리자가 열립니다.

값을 보니 제대로 입력이 되었네요.




그럼 B3셀에 유효성 검사를 지정하겠습니다.

B3셀을 선택하고 [데이터] - [데이터 유효성 검사] 를 클릭합니다.




아래와 같이 데이터 유효성 입력창이 열리면

제한 대상에 [목록]을 그리고 원본입력창에 마우스를 클릭한 뒤 [G2:I2]까지 범위를 설정하고 [확인] 단추를 클릭합니다.




다음은 C3셀에 이중 유효성검사를 지정할 차례입니다.

B3셀을 선택하고 [데이터] - [데이터 유효성 검사] 를 클릭하고,

데이터 유효성 창이 열리면 앞에서와 같이 제한 대상에 [목록]을 선택하고,

원본 입력창에 =INDIRECT(B3) 이라고 입력한 뒤 [확인] 버튼을 클릭합니다.




지금은 참조해야 할 B3셀에 아무것도 없기 때문에 아래와 같이 에러 메시지가 뜹니다만

상관할 필요 없이 [예]를 클릭하고 빠져 나옵니다.




이번에는 전화번호를 네자리 숫자로 표기되도록 셀서식을 고쳐보겠습니다.

E3셀을 선택하고 Ctrl키를 누른채 1번키를 누르면, 아래와 같이 셀서식 창이 나옵니다.

범주 중 최하단의 [사용자 지정]을 선택하고 형식창에 000#이라고 입력한 뒤 [확인]을 클릭합니다.


전화번호처럼 숫자라도 부족한 자리수에 0을 붙여야 하는 경우에 지정하는 방법입니다.

가령 일련번호가 000000~999999까지 입력되어야 한다면 00000# 이라는 형식이 되겠죠.




본격적으로 수식을 입력할 차례입니다.

B8셀은 B3셀에 입력된 내용이 그대로 내려와야되겠지요. 그래서 =B3이라고 입력됩니다.

구분은 나중에 010으로 수정할 예정이지만 지금은 그냥 전화번호와 같이 그냥 가져오겠습니다.

B8셀에 입력된 내용을 복사(Ctrl+C)한뒤에 C8셀과 E8셀을 선택하여 붙여넣기(Ctrl+V) 하면 되겠죠.




이번에는 변경된 국번호를 VLOOKUP 함수에서 참조할 범위를 유동적으로 지정하기 위해

각 구분자에 대한 범위에 이름을 지정하겠습니다.


K3:M32까지 범위를 지정하고 [수식] - 정의된 이름탭의 [선택 영역에서 만들기]를 선택합니다.

이름만들기 창이 열리면 아래와 같이 [왼쪽 열]을 선택하고 [확인]을 클릭합니다.




이름이 제대로 지정되었는지 [이름관리자]를 클릭해보니 아래와 같이 각 자료의 맨 첫줄만 지정되었습니다.




그래서 모두 범위를 새로 조정하겠습니다.

먼저 _011 이름을 클릭하고 참조대상 입력창에 마우스를 클릭하여 입력된 곳을 전부 지우고

마우스로 L3:M10까지 직접 지정한 뒤, 참조대상 입력창 옆에 있는 체크표시 아이콘을 클릭합니다.


위와 같이 _019까지 반복해서 범위를 수정해 줍니다.




제대로 되었는지는 아래와 같이 범위를 지정한 뒤에 이름창에 제대로 된 이름이 나오는 지 확인하면 됩니다.




이젠 변경되는 국번호를 찾는 수식을 만들 것입니다.

변경전 국번호를 찾아서 변경되는 국번호를 가져오는 것은 알겠죠.

하지만 국번호가 3~4자리에서 4자리로 통일됩니다.

그리고 국번호 오른쪽 세자리 숫자는 변동이 없다는 것을 아시겠죠.


그래서 변경전 국번호에서 변경되는 국번호 첫자리를 가져오고 나머지 세자리는 원래 있던 숫자를 붙이면 되겠죠.


그래서 수식은 아래와 같이 정의가 되겠죠

 

=VLOOKUP(D3, INDIRECT(C3),2)&RIGHT(D3,3)


그런데 이렇게 하면 에러가 납니다. 왜냐하면 범위 이름앞에 언더바(_)가 붙었기 때문에 그것까지 붙여줘야 합니다. 이럴 경우 연결연산자인 and(&)를 사용하면 된다고 했죠.


그래서 수식은 다음과 같이 수정하면 되겠죠.


=VLOOKUP(D3, INDIRECT("_"&C3),2)&RIGHT(D3,3)


그런데 원래 부여되지 않은 국번호에는 변경되는 국에 "통신사 문의"라는 문자를 넣어뒀습니다.


만약 원래 부여되지 않았던 국번호에 저 공식을 대입하면 아래와 같은 문자열이 나오겠죠.


통신사 문의(이어서 원래 국번호)


화면이 이상하게 나오겠죠.


"통신사 문의"가 출력되는 경우에는 해당 셀에 내용을 비우면 깔끔하겠죠.

그래서 IF 함수를 이용하겠습니다. "통신사 문의"가 나오면 비우고, 그렇지 않으면 위의 식을 적용합니다.


=IF(VLOOKUP(D3, INDIRECT("_"&C3),2)="통신사 문의","",VLOOKUP(D3, INDIRECT("_"&C3),2)&RIGHT(D3,3))


이해가 가시나요?

그런데 아래 그림을 보면 에러가 뜨네요.(D3셀에 아무것도 없기때문에 나온 것입니다.)




D3셀에 9000을 입력하면 문제가 해결되었네요.




지금부터는 화면을 좀 더 깔끔하게 하기 위한 작업입니다.

앞서 D8셀에는 "통신사 문의"라는 문자가 아니면 국번호가 출력이 됩니다.

그래서 전화번호가 들어있는 E8셀에도 D8셀이 비어 있으면 빈칸으로 만들겠습니다.

수식은 아래와 같습니다.


=IF(D8="","",E3)


이 함수는 너무 쉬워 설명이 필요 없겠죠. ^^




이번에는 변경되는 국번호인 010으로 변경할 차례입니다.

앞서도 밝혔듯 국번호와 전화번호가 비어 있는데 010 이라는 숫자가 출력되어 있으면 어색하겠죠.

그래서 이 셀 역시 D8셀이 비어 있으면, 비우려고 합니다.


=IF(D8="","","010")




이제는 부여되지 않은 국번호에 대한 안내를 만들겠습니다.

부여되지 않은 국번호에는 해당 통신사에 문의하도록 하고, 정상적으로 변환된 자료에는 번호 변경 안내 문구가 들어가면 되겠죠.

B9:E9셀까지 셀병합을 한 뒤에 아래와 같은 수식을 입력하겠습니다.


=IF(C8="",B3&"에 문의하세요!!","위 번호로 변경되었습니다!!")


위와 같이 수식을 입력하고 해당 셀은 [홈]탭을 이용해서 글자를 굵게, 색갈은 빨간색으로 변경합니다.




이제 모두 끝났습니다만 어렵게 입력한 수식에 누가 수정해버리면 난감하겠죠.

그래서 수식이 들어가거나 참조하는 범위 등에 접근을 못하게 하겠습니다.


셀 잠금 기능을 이용하는 건데요, 사용방법은 아래와 같습니다.


먼저 셀 잠금을 적용할 셀을 Ctrl키를 사용해서 아래 여러 범위를 지정합니다.

그런 뒤에 Ctrl키를 누른 상태에서 1번을 클릭합니다.(셀서식을 부르는 단축키란 것을 아까 배웠죠.)

사용자 지정 목록이 나오면 [보호]탭을 클릭하고 [잠금]에 체크를 한 뒤 [확인] 버튼을 클릭합니다.




다음, [검토]탭에 있는 [시트 보호]를 클릭하면 아래와 같이 시트 보호 창이 열립니다.

워크시트에서 허용할 내용 중 [잠기지 않은 셀 선택]에 체크 하시고 [확인]을 클릭합니다.

암호를 입력해도 됩니다만 암호를 입력하게 되면 나중에 암호를 모르면 시트 보호를 해제할 수가 없습니다.

참고하시고 적용하시기 바랍니다.




아래 그림에서와 같이 아까 잠금을 지정한 셀에는 마우스가 갈 수 없도록 되었네요.





도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!



저작자 표시 동일 조건 변경 허락
신고