피봇

엑셀에서 데이터와 문서라는 개념을 정립하고 시작하지 않으면 초기의 작은 데이터들은 문제가
없지만 데이터의 양이 많아지면 관리의 문제가 생깁니다. 저의 데이터와 문서라는 개인적인 구분은
열의 개념을 필드로 보고 행을 데이터로 정의합니다. 아래 질문에 첨부된 이미지 처럼 된 부분을 저는
문서라고 정의하고 일을 하고 있습니다. 사람이 보기 편한 데이터의 집합이지요.

http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3528009
(중복 되는 것을 확인하는 방법이 있을까요?)

이 팁의 첨부된 그림처림 왼쪽 부분은 문서, 오른쪽 부분을 데이터라 정의하고 시작해 봅시다.

위의 질문의 그림처럼 문서로 보고 데이터를 분리하고 해석하려면 상당히 많은 고민을 하고 한계가
있는 것도 사실입니다. 생각을 바꾸어서 이 문서를 데이터로 만들어 봅시다. 필드로 교수, 시간, 요일
내용으로 4개의 필드를 만들고 데이터를 입력해 봅니다.

일단 왼쪽과 같이 중복된 항목은 입력하지 않고 A교수의 요일별 시간별 강의대상을 입력해 봅시다.
그런데 엑셀보고 일을 시키려면 사람이 보기 좋은 것 보다는 컴퓨터가 보기 좋고? 처리하기 쉽도록
데이터형식으로 만들어 주어야 합니다. 몇 교수님의 내용이면 그냥 채우기하면 좋겠지만 사람이
많이지면 채우는 것도 힘들어집니다. 이제 게을러져야 합니다.^^;;; 자동채우기 루틴입니다.

내용을 보시면 아시겠지만 채워야할 영역과 그 영역의 끝을 판단할 컬럼을 선택해서 채우는 것입니다.

Option Explicit

Sub Fill_Data()

Dim RngCel As Range
Dim OffsetCol As Integer

Set RngCel = Application.InputBox(“시작셀 선택”, , Type:=8)

OffsetCol = InputBox(“비교 칼럼 입력”)

Do
If IsEmpty(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
ActiveCell.Offset(1, 0).Activate

Else
ActiveCell.Offset(1, 0).Activate

End If

Loop Until IsEmpty(ActiveCell.Offset(0, OffsetCol).Value)

End Sub

반대로 데이터들을 보기좋게 문서화 하기 위한 루틴입니다. 아래로 중복되는 행을 지워서 보기좋게
문서화하는 과정입니다. 이것보다 더 사람이 보기 좋은 것은 질문란의 이미지와 더 비슷하겠지요.

Sub UnFill_Data()

Dim TempStr As String

Dim RngAll As Range
Dim RngCell As Range

Set RngAll = Application.InputBox(“영역 선택”, , Type:=8)

TempStr = “초기값”

For Each RngCell In RngAll
If TempStr = RngCell.Value Then
If Len(RngCell.Offset(1, 0).Value) > 0 Then
RngCell.Value = “”

End If

ElseIf TempStr <> RngCell.Value Then
TempStr = RngCell.Value

End If

Next

Set RngAll = Nothing
Set RngCell = Nothing

End Sub

이제 위 문제를 다른 시각으로 해석해 봅니다. 댓글에 좋은 내용이 담기고 상당한 논리적 접근이 필요한데
4가지 정도는 2^n 16개로 쉽지만 몇 개가 더 늘어나면 논리도 논리이고 힘들어 지는 것이 사실입니다.
그래서 엑셀의 기능의 종결자라고 하는 Pivot을 이용해 봅니다. 논리는 합계가 1보다 크면 중복이라는 것을
잘 활용해 무엇이 중복이 되는지를 파악해야 합니다.

Sub CreatePivotTable()

Dim pvtPCache As PivotCache
Dim pvtPTable As PivotTable
Dim pvtFld As PivotField
Dim shtSheet As Worksheet
Dim rngStart As Range

Set rngStart = Sheets(“Data”).[A2]

‘ 기존 중첩체크용 시트 삭제
For Each shtSheet In ThisWorkbook.Sheets
If shtSheet.Name = “PivotSheet” Then

Application.DisplayAlerts = False

shtSheet.Delete

Application.DisplayAlerts = True

End If

Next shtSheet

‘ 새로운 시트 작성
Worksheets.Add.Name = “PivotSheet”

Set pvtPCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=rngStart.CurrentRegion.Address)

Set pvtPTable = pvtPCache.CreatePivotTable _
(TableDestination:=Sheets(“PivotSheet”).[A1], _
TableName:=”중첩체크”)

‘ 피봇 구성
With pvtPTable
.PivotFields(“내용”).Orientation = xlRowField
.PivotFields(“내용”).Position = 1
.PivotFields(“요일”).Orientation = xlColumnField
.PivotFields(“요일”).Position = 1
.PivotFields(“시간”).Orientation = xlColumnField
.PivotFields(“시간”).Position = 2

.AddDataField .PivotFields(“교수”), “개수:교수”, xlCount

‘ 전체요약 숨기기
.RowGrand = False
.ColumnGrand = False

End With

‘ 소계 부분 숨기기
With pvtPTable
For Each pvtFld In .PivotFields
pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False

Next pvtFld
End With

End Sub

해석하면 내용(반), 요일, 시간이 중복되는 교수님의 합을 구하는 것이 핵심입니다. 이렇게 되면 교수님이
몇 분으로 늘어나더라고 피벗 돌려서 중복되는 부분을 찾아 수정해 주면 됩니다. 교수님이 6분만 되어도
64가지 조건이 생기니 이것을 엑셀로 조건을 분리하는 것이 여간 힘든 일이 아닌 것을 알 수 있습니다.
피봇테이블에서 1보다 큰 숫자를 더블 클릭하면 중복된 교수님의 중복된 내용을 확인할 수 있습니다.

주저리 주저리 많이 써 놓았는데 문제를 바라보는 시각을 조금만 달리하면 데이터를 잘 활용할 수 있지만
문서로 보기 좋게 정리해 놓으면 보기는 좋은데 나중에는 가공하기가 힘들다는 것입니다. 그래서 서두에
써 놓았지만 데이터를 정리하는 시트는 시트대로 정리 후 이 데이터를 기반으로 가공하는 것이 훨씬 더
능률적이다는 것입니다.

첨부 화일 : 20150915-수업시간표 작성(중복금지체크)