팁

추석 연휴로 인해 며칠 지나지 않았는데도 벌써 내일 주말이 되었습니다. 별다른 일도 없고 해서
저번 아질게에 올라온 다중 영역 필터링에 대해 글을 쓰게 되었는데 아질게에 아무리 찾아보아도
관련 글이 보이지 않네요. 이제 늙으니 기억력에 문제가 생기는 …

저번에 팁란에 각 시트를 통합하고 그 통합된 시트에서 특정 데이터를 추출하는 법을 올렸습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=286941
(엑셀(EXCEL) – 시트 통합, 월간년간보고서 작성 및 특정 자료(대리점) 추출)

이제 그 반대로 두 영역의 데이터를 상호 비교하여 같은 것이 있으면 추출하는 방법을 보겠습니다.
추출할 데이터가 몇 가지 되지 않으면 그냥 필터링을 해서 추가하고 또 필터링해서 추가하고 하면
되지만 예제에는 속도를 위해 몇 가지 데이터밖에 넣지를 않았지만 비교할 데이터가 몇백개 이상
넘어가면 수작업으로는 무리가 있습니다.

이제 VBA Editor 여시고 Module하나 만드시고 아래 코드를 복사하여 붙여넣기합니다.
코드 중간중간에 주석 달아놓았으니 필요한 부분을 수정, 첨삭하시어 사용하기 바랍니다.
한 루틴은 데이터를 비교하여 한 시트에 모아 주는 루틴이고 한 루틴은 각 데이터별로 시트를
만들어 분리해 주는 루틴입니다.

Option Explicit

Sub Ext_Same_Value()

Dim cnt As Integer

Dim rngCell As Range, rngTgt As Range
Dim rngR As Range, rngRef As Range

Dim tgt As Worksheet

‘ 경고 메시지 금지 및 속도를 위해 업데이트 중지
Application.DisplayAlerts = False
Application.ScreenUpdating = False

‘ 추출해서 붙여넣기할 데이터 시트가 기존에 있으면 삭제
For Each tgt In Worksheets
If tgt.Name = “ExtData” Then
tgt.Delete

End If
Next tgt

‘ 새 시트를 워크시트 제일 마지막에 ExtData라는 이름으로 추가
Set tgt = Worksheets.Add(after:=Worksheets(Worksheets.Count))
tgt.Name = “ExtData”

‘ 추출할 데이터 영역과 추출할 참조 영역 설정
Set rngTgt = Worksheets(“Data”).Range(“B:B”).SpecialCells(xlTextValues)
Set rngRef = Worksheets(“Ref”).Range(“A:A”).SpecialCells(xlTextValues)

‘ 두 영역을 순환하면서 같은 데이터가 있으면 추출 시트에 복사
For Each rngCell In rngTgt

For Each rngR In rngRef

If rngCell = rngR Then

tgt.Range(“A1”).Offset(cnt, 0).Value = rngCell.Offset(0, -1).Value
tgt.Range(“A1”).Offset(cnt, 1).Value = rngCell.Offset(0, 0).Value
tgt.Range(“A1”).Offset(cnt, 2).Value = rngCell.Offset(0, 1).Value
tgt.Range(“A1”).Offset(cnt, 3).Value = rngCell.Offset(0, 2).Value

cnt = cnt + 1

Exit For

End If

Next rngR

Next rngCell

‘ 보기 좋게 자동 칼럼 맞춤
tgt.Columns.AutoFit

‘ 경고 메시지 및 업데이트 갱신
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Sub Ext_Same_Value_To_Sheet()

Dim cnt As Long

Dim rngCell As Range, rngTgt As Range
Dim rngR As Range, rngRef As Range

Dim tgt As Worksheet

‘ 데이터 영역과 참조 영역 선택
Set rngTgt = Worksheets(“Data”).Range(“B:B”).SpecialCells(xlTextValues)
Set rngRef = Application.InputBox(“분리할 참조 영역 선택”, Type:=8)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets(“Ref”).Activate

For Each rngR In rngRef

‘ 추출할 데이터의 시트가 있으면 삭제
For Each tgt In Worksheets
If tgt.Name = rngR Then
tgt.Delete

End If
Next tgt

‘ 추출할 데이터의 시트를 데이터 이름으로 추가
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = rngR

‘ 제목행 만듦
With Range(“A1”)
.Offset(0, 0).Value = Sheets(“Data”).Range(“A1”).Offset(0, 0).Value
.Offset(0, 1).Value = Sheets(“Data”).Range(“A1”).Offset(0, 1).Value
.Offset(0, 2).Value = Sheets(“Data”).Range(“A1”).Offset(0, 2).Value
.Offset(0, 3).Value = Sheets(“Data”).Range(“A1”).Offset(0, 3).Value

End With

‘ 순환하면서 같은 데이터가 있으면 새로 만든 시트에 추가
For Each rngCell In rngTgt

If rngR = rngCell Then

cnt = cnt + 1

With Range(“A1”)
.Offset(cnt, 0).Value = rngCell.Offset(0, -1).Value
.Offset(cnt, 1).Value = rngCell.Offset(0, 0).Value
.Offset(cnt, 2).Value = rngCell.Offset(0, 1).Value
.Offset(cnt, 3).Value = rngCell.Offset(0, 2).Value
End With

End If

Next rngCell

cnt = 0
Columns.AutoFit

Next rngR

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

분리해야할 참조 영역이 정해져 있지 않아 특정 데이터 영역에서 추출해야하는 경우에는 아래의
팁을 응용하여 분리할 참조 영역을 중복되지않게 추출하고 그 추출된 데이터를 이용하여 두 영역을
비교하여 필요한 작업을 하시면 추출 관련해서는 이 두 팁을 이용하면 못할 것이 없을 것입니다.

http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=286263
(엑셀(EXCEL) – 임의 영역 중복항목 제거 루틴을 응용한 다대다(多對多) 항목 역전개(기준항목
변경))

첨부 화일 :  20151002-두 영역 비교하여 같은 값 추출 및 시트별로 분리 

엑셀 관련 아질게에 올라오는 질문들을 보면 어찌보면 황당한 것도 많고 업무에서 실제로 데이터의
가공에 힘들고 반복적인 수작업으로 시간만 빼앗기고 업무 효율은 떨어지는 악순환에서 합리적인
해결책을 찾고자 지푸라기라도 잡는 심정으로 질문을 올리시는 분이 많으시더군요.

http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3532541
(엑셀 2007 sheet1과 sheet2 한꺼번에 양면인쇄 질문입니다.)

어떻게 보면 심플한 해결 방법이 있습니다. 수작업!!! 일단 Sheet1 전체를 출력하고 다시 출력된 종이를
맞추어 트레이에 넣고 그냥 양면인쇄 옵션없이 출력하는 방법입니다. 그런데 한두장이면 문제가 없지만
엑셀이란 프로그램 자체가 대량의 데이터를 다루다보 니 몇십장이 넘어갈 수도 있다는 것이 문제지요.

제가 양면인쇄되는 프린터가 없어서 정확하다고는 말씀드리기가 어려우나 내용으로 판단해보건데
출력시 Sheet1 전체를 출력하고 Sheet2를 출력하다 보니 양면 인쇄 옵션을 선택하게 되면 Sheet1의
1면과 2면이 양면으로 출력되고 Sheet2도 마찬가지 인 것으로 이해가 됩니다.

참 난해한 문제입니다. 머리를 이리 굴리고 저리 굴려봐도 답이 보이질 않는군요. 그러나 궁하면 통하는 법
pdf를 이용하기로 해 봅니다. 엑셀에서 출력 옵션으로 pdf로 출력하는 것이 있는데 이것을 이용하면 해결할
방법이 보입니다.

VBA Editor로 Module 추가하시고 아래 코드를 붙여넣기 합니다.

Option Explicit

Sub Prt_Dbl_Page()

‘ f: 앞면 세로, b : 뒷면 가로 접두어

Dim fp_cnt As Integer, bp_cnt As Integer
Dim i As Integer, prt_cnt As Integer
Dim f_cnt As Integer, b_cnt As Integer

Dim shtFore As Worksheet
Dim shtBack As Worksheet

f_cnt = 1
b_cnt = 1

‘ 출력되는 과정은 굳이 볼 필요가 없어 속도를 위해서 업데이트 중지
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set shtFore = Sheets(“ForePort”)
Set shtBack = Sheets(“BackLand”)

‘ 앞면을 세로로 출력 설정
shtFore.Activate
shtFore.PageSetup.Orientation = xlPortrait
fp_cnt = ExecuteExcel4Macro(“get.document(50)”)

‘ 뒷면을 가로로 출력 설정
shtBack.Activate
shtBack.PageSetup.Orientation = xlLandscape
bp_cnt = ExecuteExcel4Macro(“get.document(50)”)

If fp_cnt > bp_cnt Then
prt_cnt = fp_cnt

Else
prt_cnt = bp_cnt

End If

‘ 임시로 pdf화일 만들 폴더를 설정, 물론 먼저 만들어 두어야…
ChDir “C:\Temp\”

‘ pdf로 만들어지 각 페이지의 병합을 편하게 하기위해
‘ 문서를 이름순으로 정리하여 병합할 수 있도록 함
For i = 1 To prt_cnt

shtFore.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=i & “1” & “.pdf”, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, from:=f_cnt, to:=f_cnt, _
OpenAfterPublish:=False

shtBack.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=i & “2” & “.pdf”, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, from:=b_cnt, to:=b_cnt, _
OpenAfterPublish:=False

‘ 한장 한장 출력하도록 함
f_cnt = f_cnt + 1
b_cnt = b_cnt + 1

Next

Application.ScreenUpdating = True

End Sub

코드는 딱히 어려운 코드가 아닙니다. 세로 출력할 시트와 가로 출력할 시트를 설정하고 그 자료의 페이지
수를 알아내고 제일 큰 페이지 수 만큼 프린터를 하라는 것입니다. 그리고 그 프린터를 할 때에 종이로
직접하지 말고 pdf화일로 만들라는 것인데 세로출력과 가로출력을 한장씩 번갈아 출력해서 각각으로
pdf로 만들어 특정 폴더에 저장하라는 프로시져입니다.

내용 중에 페이지 병합을 위하여 1페이지에 양면으로 들어갈 것을 예상해서 접미사 1,2를 붙여 저장해서
특정 폴더를 이름순으로 정렬해서 Merge(병합)시킬 때 편하게 화일을 선택할 수 있도록 해 두었습니다.

…

여기서 자동으로 pdf로 병합하는 루틴을 구걸해서 찾았는데 이것이 구버전 API라서 사용하기가 번거럽고
해서 무료로 제공되는 pdf Tool을 이용해 병합하기로 해 봅니다. 여기서는 pdftk를 사용하기로 합니다.
다른 Tool들도 인터페이스만 다르지 결과는 똑같으므로 아무거나 사용하셔도 됩니다.

https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/
http://angusj.com/pdftkb/
http://www.vb-helper.com/howto_pdf_merger.html

이제 인쇄 명령을 누르면 자동으로 Sheet1 한페이지, Sheet2 한페이지가 지속적으로 지정된 특정 폴더에
pdf로 저장되고 이 화일들을 pdf Tool로 묶어서 양면인쇄 옵션을 켜시고 출력하시면 됩니다. 힘들군요!

제가 양면인쇄되는 프린터가 없고 경험도 없어서 이 내용이 이론적으로만 만들어진 것이기에 잘 안되면
프린터 없는 제 잘못입니다? … 여하튼 pdf로 각각 만들어 두었으니 프린터 가지신 분께서 이리 저리 병합
해 보시면서 잘 될 수 있도록 하는 것은 사용자 몫으로 돌립니다.

첨부 화일 : 20150918-서로 다른 방향으로 세팅된 2시트의 교차 양면 출력

아무거나 질문 게시판에 엑셀관련 아래와 같은 무시 무시한 질문과 답변이 올라왔습니다.
해석하면 자동으로 가로로 된 영역에서 중복 값을 제거하고 자기 영역에 다시 복사하여
붙이기를 하는 매크로입니다. 머리가 아프지만 저의 삽질 본능을 깨우는 내용입니다. ㅠㅠ

http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3517395
([excel] 해당 코드에서 selection에서 자꾸 에러가 납니다. 원인분석을 부탁드립니다.)

엑셀은 사실 숫자를 다루기도 하지만 데이터를 더 많이 다루는 프로그램입니다. 자료를 작성
할 때는 데이터베이스처럼 필드 개념의 열과 데이터 개념의 행으로 보통 무의식적으로 만듭니다.
그래서 엑셀 내장 함수에도 아래로 입력된 데이터에 대해서는 중복 항목 제거하라는 기능을
고급필터에서 사용가능하도록 되어 있는데 행으로 된 데이터에 대해서 중복항목을 제거하려면
위의 내용처럼 정말 수작업이 많이 필요합니다.

특히나 중복 항목이라는 것은 데이터의 위치가 필요 없고 그 데이터만 필요하고 내용 중 빈셀도
의미 없는 데이터이기 때문에 처리를 해서 없애 버리고 자료를 구하는 것이 일반적인 상식이라고
생각합니다. 그래서 제가 예전에 찾아 쓰던 모듈을 응용하여 함수를 만듭니다. 빈 셀도 자료라고
처리해야 할 필요가 있을 때는 if 문을 제거하시고 사용하시면 됩니다.
모듈을 하나 만드시고 아래 식을 붙여 넣습니다.

Option Explicit

Function UniqItemRng(SelRng As Range) As String

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Dim UniqStr As String

Set AllCells = SelRng

On Error Resume Next

For Each Cell In AllCells

If Len(Cell.Value) > 0 Then ‘ 빈셀을 포함시키지 않음
‘ Add method의 2번째 인자는 문자열이어야만 함
NoDupes.Add Cell.Value, CStr(Cell.Value)

End If

Next Cell

On Error GoTo 0

For i = 1 To NoDupes.Count – 1

For j = i + 1 To NoDupes.Count

If NoDupes(i) > NoDupes(j) Then

Swap1 = NoDupes(i)
Swap2 = NoDupes(j)

NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1

End If

Next j

Next i

For Each item In NoDupes

UniqStr = UniqStr & “:” & item

Next item

UniqItemRng = UniqStr

Set Cell = Nothing

End Function

위 내용은 이해하실 필요는 없고 해석을 하자면 가로나 세로 영역이 선택되면 그 영역의 내용을
중복 항목을 제거하고 자료를 정렬해서 UniqStr이라는 변수에 차곡차곡 붙여 넣는 것입니다.
중간에 구분자(“:”)가 중요한 역할을 합니다. 제가 올린 팁에서 보시면 왜 넣었는지 이해하실
것입니다. “:”는 임의로 넣은 문자이니 엑셀 자료에서 잘 사용하지 않는 문자열로 대치하십시오.

이렇게 하면 상기의 스샷과 같이 특정 셀에서 =UniqItemRng라고 하시면 일반 엑셀 내장함수와
같이 영역을 선택하라고 하면 영역을 선택해주면 해당 셀에 중복자료를 표시해 줍니다. 다른
엑셀 시트에서 사용하시려면 .xla로 추가해서 사용하세요. 추가하는 방법은 구걸하세요 ^^;;;

그 다음 버튼 하나 만드시고 아래 서브 루틴을 버튼에 연결하시면 =UniqItemRng 함수가 사용된
셀의 오른쪽에 중복 항목들이 주르륵 나타납니다. 위의 매크로처럼 왼쪽 전체를 지우고 처리할 수도
있겠지만 그냥 예시로 나타내었으니 알아서 수정해서 사용하세요.

Private Sub CommandButton1_Click()

Dim TempStr As String
Dim RngStr As String

Dim intNum As Integer
Dim NumCnt As Integer

Dim RngCel As Range
Dim RngRef As Range

NumCnt = 0
TempStr = “”

‘ 검색할 셀의 조건을 셀을 클릭해서 선택
Set RngRef = Application.InputBox(“셀 선택”, , Type:=8)

‘ 속도를 위하여 스크린 업데이트 하지 않음
Application.ScreenUpdating = False

‘ 검색할 영역에서 선택된 셀과 같은 조건이 있으면 카운트
For Each RngCel In RngRef

RngStr = RngCel.Text

For intNum = 1 To Len(RngStr)
If Mid(RngStr, intNum, 1) <> “:” Then
TempStr = TempStr + Mid(RngStr, intNum, 1)

RngCel.Offset(0, NumCnt).Value = TempStr

Else
‘ 열의 위치 수정
NumCnt = NumCnt + 1
‘ 임시 문자열 초기화
TempStr = “”

End If

Next intNum

‘ 열의 위치 리셋
NumCnt = 0

Next

‘ 스크린 업데이트 진행
Application.ScreenUpdating = True

End Sub

위 내용을 보시면 간단하지만 이 영역을 자동화하려는 시도를 많이 해봤는데 더 복잡할 것 같고
엑셀의 내장함수의 고급 필터와 같은 방법으로 영역 선택해서 같은 행의 위치에 중복 데이터를
출력하도록 만들었습니다. 물론 코드 하나 더 넣어서 출력 위치도 선택할 수 있지만 크게 의미가
있을 것 같지 않아서 기능을 넣지 않았습니다.

하나의 문제를 보고 해결하는데 많은 고민을 하고 시행착오도 많이 겪습니다. 이런 아이디어가
괜찮을까? 저런 아이디어가 괜찮을까? 이 팁을 읽고 실무에서 잘 사용하도록 코드를 간결하게
만들어야지하면서 업무 중간 중간에 생각날 때마다 코드 작성해서 실행하고 무지 막지한 에러!
가끔씩 무한루프에 빠져 저장안한 코드 다 날아가고 … 여튼 재미있는 작업들입니다.

첨부 화일 : 20150902-일정 영역에서 행별 중복항목 제거 및 추출 방법