한글이 아닌 엑셀로 하는 메일 머지(1)

1. 문제

고지서 서식은 한 장이고, 

 
인쇄할 내용을 아래 명단 시트에서 순서대로 찾아서 인쇄하고자 할 때 

먼저 생각나는 것이 워드나 한글의 메일 머지 기능입니다.
그러나, 여기서는 엑셀의 매크로로 구현하고자 하는 것입니다.

고지서__개별인쇄(예시).xlsx

0.02MB

 
 
인쇄할 내용을 채우는 방법이 두 가지가 있습니다.
 
이름만 입력하면 나머지 내용은 수식으로 가져오는 방법,
이름뿐만 아니라 나머지 내용까지 매크로로 처리하는 방법인데,
한 가지씩 알아보겠습니다.
 

2. 해법 1 : 이름만 입력하고 나머지는 수식으로 가져오는 방법

 

가. 연번별로 이름에 순번 부여하기

(1) 로직
 
연번을 기준으로 인쇄하는데,
연번에 동일인이 있다면 같은 고지서에 인쇄하고,
다른 사람이라면 별도의 고지서에 인쇄해야 합니다.
 
이를 위해서는 고지서 발행 기준인 순번과 동일인여부 순번 2가지가 있어야 하고,
2가지를 하이픈(-)으로 연결해서 연결키를 만들어야 합니다.

(2) 수식
(가) B열의 수식
연번이 다르면 B열의 윗셀 값+1이고,
연번이 같을 경우, 다시 말해 연번이 1일 경우는 이름이 같으면 B열의 윗셀 값이고, 다르면 B열의 윗셀 값+1이 됩니다.
 
이것을 정리하면 연번이 0보다 크거나, 이름이 다르면 B열의 윗셀 값+1이고,
나머지는, 다시 말해 연번이 0이고, 이름이 같은 경우는 B열의 윗셀 값입니다.
 
따라서, 수식은 =IF(OR(A7,E7<>E6),B6+1,B6)이 됩니다.
A7>0라고 주는 것이 이해하기 쉽지만 간단하게 적으면 A7이 됩니다.
왜냐하면 A7이 0이면 False이고, 나머지 숫자는 모두 True이기 때문입니다.

 
아래로 수식을 복사하려면 C7셀 오른쪽 아래 네모 모양의 채우기 핸들을 더블 클릭하면 됩니다.

(나) C열의 수식
 B열의 숫자가 같을 경우 1을 더해가면 됩니다.
 
따라서, 수식은
=COUNTIF($B$7:B7,B7)이라고 하면 됩니다.

 
(다) D열의 수식
이제 C열과 D열의 값을 -(하이픈)으로 연결하면 됩니다.
따라서, 수식은 =B7&"-"&C7이 됩니다.

 
 

나. 해당하는 내용 불러오는 수식 작성하기

 
(1) 순번 등 연결키 만들기
이름이 같은 경우가 있을 수 있으므로 B열의 값과 C열의 값을 기준으로
E열에서 첫 번째 자리가 같은 것을 계속 가져오면 됩니다.
 
아래와 같이 L6셀에 순번을 넘겨주면

 
L11셀부터 L6셀의 번호에 하이픈과 일련번호를 연결해서 작성되도록 합니다.
 
L11셀의 수식은 =$L$6&"-"&ROW()-10입니다.
Row는 인수로 입력된 셀의 행 번호를 반환해 주는 함수인데, 생략하면 현재 행의 번호를 반환해 줍니다.
현재 행이 11이므로 10을 빼면 1이 되므로
L6셀의 1과 하이픈 그리고 1을 연결하면 1-1이 되고,
아래는 L11셀의 채우기 핸들을 L21셀까지 끌면 수식이 복사됩니다.
 
(2) 회사명(성명) 가져오기
L6셀에 해당하는 값을 명단시트에서 찾은 다음 성명을 가져오는 것이므로
Vlookup함수를 이용합니다.
Vlookup함수의 인수는 찾을 값, 찾을 범위, 순번, 정확히 일치 여부이므로
=vlookup($l$6,명단!$b$7:$s$12,4,0)이 됩니다.

 
찾을 범위는 아래와 같이 명단 시트의 B7셀에서 S12셀까지인데, 찾으려고 하는 값이 첫 열이어야 하며, 성명은 B열부터 1, C열 2가 되므로 성명은 4가 됩니다.

 
정확이 일치하는 것을 찾는 것은 False인데, 0으로 표시해도 됩니다.
 
(3) 주소 가져오기
위와 찾을 값, 찾을 범위는 같은데, 순번만 15로 다릅니다.
=vlookup($l$6,명단!$b$7:$s$12,18,0)
 
(4) 사용내역 가져오기
사용내역은 명단 시트의 D열의 값, 고지서 시트 기준으로 하면 L열의 값을 기준으로 찾아야 합니다.
따라서, Vlookup함수의 첫 번째 인수가 L11셀부터 아래로 내려가고, 찾을 범위도 D열부터 P열까지 하면 됩니다.
 
(가) 토지 소재지 
  - 시군 : =vlookup($L11,명단!$d$7:$p$12,4,0)
오른쪽으로 복사할 때 L11에서 L이 변경되면 안 되므로 L앞에만 $표시를 합니다.
 
그런데, 채우기 핸들을 아래로 끌면 에러가 발생하므로

 
ifError함수를 이용해 에러일 때 값을 공란으로 만들어야 합니다.
 
그러면 수식은 =iferror(vlookup($L11,명단!$d$7:$p$12,4,0),"")이 됩니다.
 
이 수식을 B21셀까지 복사해서 붙여 넣는데 테두리에 영향을 주면 안 되므로 수식으로(fx) 붙여 넣어야 합니다.

 
그러면 아래와 같이 수식이 완성됩니다.

 
B12셀부터는 수식은 있지만 해당하는 데이터가 없기 때문에 공란으로 표시되는 것입니다.
 
  - 읍면, 리동, 지번 : 시군의 수식을 오른쪽으로 끈 다음 순번만 수정하면 되는데, B열과 C열이 병합되어 있으므로 먼저 B11셀만 복사해서 수식으로 D11셀에 붙여 넣은 다음 D11셀에서 i21셀까지 복사해서 수식으로 붙여 넣어야 합니다.
 
순번을 바꿀 때 D열부터 열별로 선택한 후 
찾기 및 바꾸기(Ctrl + H)를 누르고, 찾을 내용에 ,4를 입력하고, 바꿀 내용에는 열별로  ,5 ,6 ,7 ,9, ,12 ,10으로 한 열씩 바꾸는 것이 편리합니다.

 
그러면 아래와 같이 사용료에 쉼표가 안 들어가고 면적에 소수점이하 표시가 없는데,

 
사용료 셀을 선택한 다음 표시형식에서 쉼표를 누르고, 사용면적 셀을 선택한 다음 자릿수 늘림 명령을 눌러 소수점이하 첫째 자리까지 표시합니다.

 
(5) 납부금액 가져오기
사용료는 위 사용내역의 사용료 합산인 =SUM(H11:H21)로 입력하고,
납부금액 계는 아래 사용료부터 감정평가 수수료까지의 합계인 =SUM(E27:J30)로 합니다.
 

고지서__개별인쇄(수식 완성).xlsx

0.02MB