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

 

엑셀 강좌이니만큼 엑셀의 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

댓글을 달아 주세요

  1. april

    처음에 연습삼아 주소목록을 적게 지정했는데....수정이 안되네요~
    주소목록 다시 지정할려면 어떻게 해야 하나요?
    엑셀 초보라 간신히 수식만 대입해서 하구 있네요~

    2013.11.13 17:48 [ ADDR : EDIT/ DEL : REPLY ]
    • 연습하실거라면 먼저 첨부한 예제 파일로 연습하신 후에
      어느정도 습득이 되시고 나서 응용하시는게 나을거 같네요
      주소목록이 작으면 더 입력하시면 되지만..일단 예제파일로 연습해보세요^^

      2013.11.13 18:46 신고 [ ADDR : EDIT/ DEL ]
  2. 해피수정

    님~~

    33번부터는 출력이 안돼요~~

    어떻게 해야 되죠?>

    2013.12.16 09:39 [ ADDR : EDIT/ DEL : REPLY ]
    • 고객번호에 33을 입력하면 33~48번까지 안나온다는 말씀이신거죠?
      그럼 제가 올린 완성 파일을 다운받으셔서 뭐가 틀렸는지 비교해보세요. 대신 제가 올린 파일은 32번까지만 데이터가 있으니까 위의 데이터를 복사한 후에 번호를 수정해서 연습하세요.

      2013.12.16 15:25 신고 [ ADDR : EDIT/ DEL ]
  3. 라벨 제작을 더 쉽게 할 수 있는 방법이 있습니다.

    주소 라벨 홈페이지(http://www.jusolabel.com )를 이용하는 것인데요
    주소 데이터만 입력하면 라벨지에 곧바로 출력해 사용할 수 있는 이미지가 만들집니다.
    우편번호는 자동으로 입력되고, 생성된 이미지를 곧바로 라벨지에 출력해 우편봉투에 붙이기만 하면 됩니다.

    참고하세요 :)

    2014.03.19 21:54 [ ADDR : EDIT/ DEL : REPLY ]
    • 아~그렇군요. 좋은 정보 감사드려요^^
      근데 이 글은 vlookup 함수를 이해하기 쉬우시라고 활용 예제용으로 만든거라서 사실 라벨 제작과는 크게 상관이 없을거 같네요~

      2014.03.19 21:56 신고 [ ADDR : EDIT/ DEL ]
  4. moonsroom

    제가 만든 파일은 33번부터 나오는데 인쇄가 16칸이 잘 안맞아서
    님걸 다운받으니 딱맞게 인쇄되더라구요~
    그런데 33번부터 #n/v로 뜹니다.
    저는 명단이 총 533명이라... 왜 그런걸까요?

    2014.03.20 14:39 [ ADDR : EDIT/ DEL : REPLY ]
    • 제 파일은 데이터가 딱 32개만 있습니다.
      첫번째 시트 데이터를 님 데이터로 바꾸시면 될거 같네요~

      2014.03.20 15:49 신고 [ ADDR : EDIT/ DEL ]
    • 지나가는사람

      고객목록시트에서 이름상자가 32번까지 지정되어있습니다.

      엑셀 2007 버전 기준으로 수식->이름관리자에서

      기존 '주소목록'을 삭제 하시고 다시 고객목록시트에서

      1번부터 끝사람 연락처까지 범위를 다 잡으신 상태로

      이름상자에 주소목록이라고 입력하시면 됩니다.

      2014.04.18 09:45 [ ADDR : EDIT/ DEL ]
  5. 깍쟁이

    1000명정도 되는데요 더 할 수 는 없나요?

    2014.06.12 16:54 [ ADDR : EDIT/ DEL : REPLY ]
    • 당연히 할 수 있죠.
      고객목록표 전부(1000명)를 '주소목록'으로 이름 지정하시고 나머지는 위와 똑같이 작업하시면 됩니다.

      2014.06.12 19:21 신고 [ ADDR : EDIT/ DEL ]
  6. 깍쟁이

    죄송하지만 잘 안되네요.
    잘 이해가 안가네요
    제발 부탁인데
    완성된 견본을 주시면 안되실까요?

    2014.06.19 09:55 [ ADDR : EDIT/ DEL : REPLY ]
    • 1000명으로 된 파일을 달라는 말씀이신가요?
      완성 파일을 다운받으신 후에 첫번째 고객목록 시트에 1000명의 데이터를 복사해넣으시고, 데이터 전체를 범위잡아서 이름을 '주소목록'으로 지정하시면 되는데..이름 지정 방법은 글에 있구요.
      나머지는 바꾸실 필요가 없습니다.

      2014.06.19 09:59 신고 [ ADDR : EDIT/ DEL ]
  7. 깍쟁이

    말씀대로 해 보았는데요....주소라벨에서 고객번호 33이상... 45나 50나 80 등등 을 치면 주소가 정상적인 주소가 뜨질 않고
    에러상태로 떠요
    수식이 잘못된것처럼요. 그래서 답답할 노릇이죠
    사람 한명 구한단 샘치구 1000명으로 완성된 파일 좀 다운 받게 해 주시면 안될까요?

    2014.06.24 12:38 [ ADDR : EDIT/ DEL : REPLY ]
    • [수식]-[이름 관리자]에서 '주소목록' 이름을 삭제안하셔서 그런 듯;
      이미 쓴 글을 함부로 수정할 수는 없고, 이메일 주소를 비밀 댓글로 남겨주시면 1000명 대충 만들어서 보내드릴게요.

      2014.06.24 13:20 신고 [ ADDR : EDIT/ DEL ]
  8. 비밀댓글입니다

    2014.07.01 13:02 [ ADDR : EDIT/ DEL : REPLY ]
  9. SADF SA

    파일은 잘만들었는데 주소라벨시트출력하면 고객번호때문에 두장이 출력되는데...
    고객번호 없앨수있는 방법은 없나요 ..?
    수식-이름관리자에서 삭제하면 수식오류뜨더라구요...
    수식자체에서 고객번호 지워도 오류뜨고..
    고객번호에 입력하면 그번호 부터 자동으로 옮겨지는 기능은 없어도되고
    그냥 고객목록에 입력하면 주소라벨에 자동으로 뜰수있게만
    하는방법좀알려주세요 ㅠㅠㅜ

    2014.07.07 15:17 [ ADDR : EDIT/ DEL : REPLY ]
    • 1. 고객번호 입력하는 부분은 지우시고..
      2. 식을 '고객번호' 대신 '1'을 대체해서
      =VLOOKUP(1+INT(ROW(A1)/3),주소목록,6,0) -> 왼쪽
      =VLOOKUP(1+INT(ROW(A1)/3)+8,주소목록,6,0) -> 오른쪽
      3. 주소라벨의 표를 필요한 만큼 아래로 복사하셔서 여러 페이지를 미리 만드시고 인쇄하시면 될 거 같네요.

      그리고 수식-이름관리자에서 '주소목록'을 지우시고, 주소목록만큼 새로 범위 잡으로셔서 '주소목록'으로 새롭게 이름 주시면 됩니다.
      지금 주소목록 늘려서 다시 해보니까 잘 되네요.

      2014.07.08 10:01 신고 [ ADDR : EDIT/ DEL ]
  10. 깍쟁이

    너무너무 고맙습니다. 고맙게 잘 사용하겠습니다.
    복 받으실 거여요~~^^

    2014.07.08 14:12 [ ADDR : EDIT/ DEL : REPLY ]
  11. 보련윤성맘

    좋은정보 감사합니다 ^^

    2014.09.05 13:33 [ ADDR : EDIT/ DEL : REPLY ]
  12. 하느님감사

    하느님 감사합니다. 이런 귀인을 여기서 만나 뵐 수 있게 해주시다니! 제가 엑셀 초짜지만 넣어주신 자료랑 제가 원하는 것과 합쳐서 조금 응용했더니 잘 되네요!! 정말 필요했던 업무였는데 덕분에 해결 했습니다!!

    2014.10.13 16:08 [ ADDR : EDIT/ DEL : REPLY ]
    • 솔직히 주소라벨로 진짜로 사용하시라고 만든 강좌라기보다는 vlookup 함수를 공부하시라고 만든 강좌였는데 생각보다 실무에 사용하시는 분들이 많아 놀랐네요^^;
      업무에 도움이 되서 어찌됐든 다행~!!
      즐거운 하루되세요~

      2014.10.14 10:47 신고 [ ADDR : EDIT/ DEL ]
  13. zini

    아놔 뭐 이렇게 친절한 블로그가 있지 ㅋㅋ 두여성분 짱!!

    2014.11.07 17:57 [ ADDR : EDIT/ DEL : REPLY ]
  14. 처음부터 끝까지

    2015.01.06 11:56 [ ADDR : EDIT/ DEL : REPLY ]
    • 1번부터 1000번까지
      첫번째 줄에는 1~500을 두번째 줄에는 501~1000까지의 내용을 담고싶은데..
      지금 여기 형식대로 하면 첫번째줄에는 1~1000까지 두번째 줄에는 9~1000까지의 내용이 담기네요 이건 어떻게 해야하나요?
      끝의 한도를 정해서 나눠서 하고싶습니다.

      2015.01.06 11:58 [ ADDR : EDIT/ DEL ]
    • VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,6,0)
      식에서 8대신에 499를 넣으시면 됩니다.
      8+1이라서 9번 고객부터 표시되는거라
      8 대신 499를 입력하시면 500번 고객부터 표시됩니다.

      2015.01.07 13:56 신고 [ ADDR : EDIT/ DEL ]
  15. 감사합니다~~ 33번이상을 하는데 수식-이름관리자에서 범위를 다시 지정하니 고객번호를 33번부터 해도 나오네요 감사합니다~~~*^^*

    2015.08.28 17:21 [ ADDR : EDIT/ DEL : REPLY ]