'엑셀 주소라벨 만들기'에 해당되는 글 1건

  1. 2013.06.18 엑셀 주소라벨 만들기 (VLOOKUP 함수 실습) by Y 32

 

 

 

보통 수백, 수천개가 넘는 주소로 우편물을 발송할 경우 엑셀, 한글, 워드, 엑세스 등의 프로그램을 이용해서 주소라벨을 작성한 후 구입한 주소 라벨 스티커에 인쇄하고, 그 스티커를 편지 봉투에 붙여 우편을 발송한다.

 

엑셀 강좌이니만큼 엑셀의 VLOOKUP 함수를 이용하여 주소 라벨을 만들어보겠다.

VLOOKUP 함수에 대해 잘 모른다면 한번 미리 보자~!!

엑셀 함수 4 (찾기/참조 함수) by Y

 

 

예제를 위한 준비파일과 완성파일이다.

 

 주소라벨.xlsx   주소라벨완성.xlsx

 

 

 

[고객목록] 시트에는 32개의 고객 정보가 입력되어 있다.

모든 데이터가 다 사용되는건 아니고, 실제로 주소라벨에 사용되는 데이터는 이름, 우편번호, 주소만이다.

 

 

 

 

라벨을 만들 첫번째 고객의 고객번호를 F3셀에 입력하면, 자동으로 다음 16개의 고객에 대한 주소라벨이 채워지도록 만들어 아무리 많은 주소가 있더라도 쉽게 주소라벨을 만들 수 있도록 할 것이다.

주소용 라벨 스티커로는 16칸, 18칸, 21칸, 24칸이 가장 많이 사용되는데, 여기에서는 16칸 우편발송라벨 규격에 맞추어서 미리 [주소라벨] 시트에 만들어놓았다.

 

 

 

 

1. 이름 정의하기

 

함수식을 복사할 경우 인수에 사용된 범위가 항상 고정되도록 하려면??

절대참조를 사용하는 방법과 이름을 정의하는 방법의 두 가지 방법이 있다.

 

여기에서는 제목을 제외한 모든 목록범위([고객목록] 시트의 B2:H33)와 고객번호 입력란([주소라벨] 시트의 F3셀)의 이름을 정의하여 함수를 작성해보겠다.

 

범위를 선택하려면??

1. 범위의 크기가 작다면 마우스로 드래그한다.

2. 범위의 크기가 굉장히 크다면 첫번째 셀을 클릭한 후 마지막 셀을 Shift 키를 누른 상태에서 클릭한다.

 

주소 목록은 굉장히 데이터가 많은 경우가 대부분이므로 아래와 같은 방법으로 범위를 지정해보았다. 

 

 

 

 

[이름 상자]에 '주소목록'을 입력한다.

 

 

 

 

이어서 [주소라벨] 시트의 F3 셀을 선택한 후 [이름 상자]에 '고객번호'를 입력한다.

 

 

 

 

2. 함수식 입력

 

이름 정의가 모두 되었다면, [주소라벨] 시트의 첫번째 라벨 입력 부분에 각각의 함수식을 작성해보자.

 

A1 셀    =VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,6,0)
A2 셀    =VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,2,0)
A3 셀    =VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,5,0)

 

ROW 함수와 INT 함수를 모른다면 한번 미리 보자~!!

엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y

엑셀 함수 18 - INT, TRUNC, ROUND, ROUNDUP, ROUNDDOWN 함수 by Y

 

 

함수식 설명

 

 

1) INT(ROW(A1)/3) ??

 

ROW(A1) => ROW 함수는 셀의 행번호를 구하는 함수이므로 1이 구해진다

ROW(A1)/3 => 1을 3으로 나누면 0.3333....이 구해진다.

INT(ROW(A1)/3) => INT 함수는 가까운 정수를 구하는 함수이므로 0이 구해진다.

 

여기서 잠깐~!!

식을 A4 셀에 복사할 것이므로 A4셀에는 식이 'INT(ROW(A4)/3)' 식으로 변경되어 들어간다.

(왜냐? 상대참조니까~)

결국 A4셀에는 위의 식으로는 1이 구해진다.

같은 방법으로 A7, A11... 셀에는 각각 2, 3...이 구해지므로,

결국 다음 고객번호에 해당하는 고객의 주소가 입력된다.

 

2) 고객번호+INT(ROW(A1)/3) ??

 

INT(ROW(A1)/3)의 값이 0이므로 고객번호가 1이라면 1+0 값이 구해진다.

 

2) VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,6,0) ??

 

1번 고객부터 라벨을 만들기 위해 고객번호에 1을 입력한 경우 'VLOOKUP(1,주소목록,6,0)' 식으로 생각할 수 있다. 결국~~~!!

VLOOKUP(1,주소목록,6,0) => 주소목록 범위에서 고객번호가 '1'인 데이터의 6번째 열(주소)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,2,0) => 위와 같은 데이터의 2번째 열(이름)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,5,0) => 위와 같은 데이터의 5번째 열(우편번호)의 값을 구해온다.

 

 

여기서 잠깐~!!

아래와 같이 오류가 나는 이유는 고객번호를 아직 입력하지 않았으므로 'VLOOKUP(0,주소목록,6,0)' 식으로 간략화되어 고객번호가 0인 고객을 찾게 되는데, 데이터에서 고객번호가 0인 데이터가 없으므로 값을 찾을 수 없어 오류표시가 보여진다. 어짜피 고객번호를 입력하면 데이터가 제대로 표시된다.

 

 

 

 

이어서 오른쪽 상단의 라벨 부분에 아래와 같은 함수식을 각각 작성해보자.

 

C1 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,6,0)
C2 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,2,0)
C3 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,5,0)

 

 

함수식 설명

 

 

VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,6,0) ??

 

1번 고객부터 라벨을 만들기 위해 고객번호에 1을 입력한 경우 'VLOOKUP(9,주소목록,6,0)' 식으로 생각할 수 있다. 결국~~~!!

VLOOKUP(9,주소목록,6,0) => 주소목록 범위에서 고객번호가 '9'인 데이터의 6번째 열(주소)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,2,0) => 위와 같은 데이터의 2번째 열(이름)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,5,0) => 위와 같은 데이터의 5번째 열(우편번호)의 값을 구해온다.

 

 

여기서 잠깐~!!

아래와 같이 값이 표시되는 이유는 고객번호를 입력하지 않아도 'VLOOKUP(8,주소목록,6,0)'이므로 고객번호가 8인 데이터가 자동으로 입력되기 때문이다. 어짜피 고객번호를 입력하면 값은 변하게 된다.

 

 

 

 

3. 함수식 복사하기

 

주소라벨에 함수식을 모두 채우기 위해 함수식을 작성한 [A1:D3] 셀까지 범위지정한 후 하단에 각각 붙여넣는다.

 

 

 

 

4. 고객번호 입력하기

 

F3 셀에 1을 입력하면 고객번호가 1인 고객부터 16인 고객까지 주소와 이름, 우편번호가 자동으로 채워진다.

 

 

 

 

F3셀에 17을 입력하면 고객번호가 17인 고객부터 32인 고객까지 주소와 이름, 우편번호가 자동으로 채워진다.

 

 

 

여기서 팁~!!

만약 24칸 주소라벨을 만든다면 세로로 12개가 입력되므로 함수식에서 아래 부분을 수정하면 된다.

 

C1 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+12,주소목록,6,0)
C2 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+12,주소목록,2,0)
C3 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+12,주소목록,5,0)

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

 

Posted by Y&S