소스) 20150410-UniqueItemSort
우리가 엑셀을 다루면서 특히 시험데이터 등에서 중복되는 데이터들이 많이 나타납니다.
반복 시험을 하는 경우나 생산 현장의 생산 기록 등 중복되는 데이터들을 다루어야 할 경우
엑셀의 기본 기능을 가지고 중복데이터를 추출하면 되지만 이것이 행으로 이루어져 있질 않고
임의의 영역에 걸쳐 데이터가 존재할 때는 추출하기가 사실 막막하기만 것이 현실입니다.
일반인이 아닌 직장인?분들이야 쉽게 데이터를 추출할 수 있으시겠지만, 물론 일반 직장인도
여러가지 방법을 통해 반복적으로 하면 데이터를 추출해서 취합하실 수 있습니다. 그렇지만
조금만 아이디어를 가지고 하면 반복적인 일들을 한 번에 추출하고 가공이 가능하지요.
우선 엑셀의 기본 기능을 가지고 데이터 중에서 중복되는 데이터를 하나의 데이터로 추출하는
방법을 여러 가지로 해 봅니다. (엑셀 2007 기준) 2010버전은 제가 사용해 보지 못한 관계로
2010에 이 기능이 있을 수도 있습니다. 그러면 전 하루를 쓸 데 없는 데 힘 쓴 경우네요. ㅠㅠ
1. 우선 데이터 > 필터 – 고급 기능을 이용하는 경우
결과 : 다른 장소에 복사
목록 범위 : 원하는 영역을 선택
복사위치 : 복사될 위치를 선택
동일한 레코드는 하나만 선택
2. 데이터 > 중복된 항목 제거 기능 선택
3. 피벗 테이블 이용 : 삽입 > 피벗테이블 선택
표 또는 범위 선택
보고서 놓을 위치 : 기존 워크시트에서 셀 선택
4. 함수를 만들어서 내장? 함수를 이용하는 경우
엑셀 시트에 삽입 > ActiveX Control : 명령 단추 하나 삽입하고 모듈에 아래 코드 삽입
Sub FindUniqueValues()
Dim soruce As Range, targetcell As Range
Set soruce = Application.InputBox(“영역을 선택하세요”, Type:=8)
Set targetcell = Application.InputBox(“셀을 선택하세요”, Type:=8)
soruce.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=targetcell, Unique:=True
End Sub
이 정도가 기본 기능만을 사용해서 오직 같은 ‘행’에서 유일한 데이터를 추출하는 방법입니다.
물론 조금씩 내장함수와 기타 기능을 응용해서 추출하실 수 있는 방법은 다양할 것으로 보입니다.
아래는 위의 결과를 원본과 함께 순서대로 오른쪽으로 결과를 나열했습니다.
(원본) (1) (2) (3) (4)
| 데이터5 |
데이터5 |
데이터5 |
행 레이블 |
|
| 65 |
65 |
65 |
10 |
65 |
| 29 |
29 |
29 |
11 |
29 |
| 10 |
10 |
10 |
12 |
10 |
| 48 |
48 |
48 |
17 |
48 |
| 28 |
28 |
28 |
18 |
28 |
| 10 |
|
|
28 |
|
|
81 |
81 |
29 |
81 |
| 81 |
18 |
18 |
34 |
18 |
| 18 |
11 |
11 |
41 |
11 |
| 11 |
80 |
80 |
48 |
80 |
|
41 |
41 |
62 |
41 |
| 80 |
34 |
34 |
65 |
34 |
| 41 |
76 |
76 |
76 |
76 |
| 34 |
12 |
12 |
80 |
12 |
| 76 |
17 |
17 |
81 |
17 |
|
62 |
62 |
(비어 있음) |
62 |
| 12 |
|
|
총합계 |
|
|
|
|
|
|
| 17 |
|
|
|
|
| 62 |
|
|
|
|
그런데 추출된 데이터를 보면 몇 가지 문제점이 있습니다. 피폿을 이용한 경우를 빼고 데이터가
빈 셀을 포함해서 정렬이 되지 않고 빈셀이 중간에 나타나 보기가 싫어집니다. 물론 데이터를
정렬해서 보며 괜찮지만 번거러운 일이 아닐 수 없습니다.
그렇지만 위의 문제점은 그냥 번거러움의 문제지 기능의 문제는 아닙니다. 엑셀의 내장 기능을
사용한 경우 “열”으로만 나열된 데이터에서 유일한 데이터를 추출해 줍니다. 일정 영역에서
데이터를 추출하려면 그 영역을 복사하거나 해서 한 열 아래로 모두 맞추고 추출해야 하지요.
데이터가 얼마되지 않으면 이런 수고스러움을 감수하면 되겠지만 몇 만개가 되면 큰 일 입니다.
그리고 그 추출한 데이터를 가공하고 싶을 때 또 번거러운 작업과 내장 함수를 이용해서 추출하고
하는 것이 번거러워서 이 팁을 공유하고자 합니다. 하고자 하는 목적이 정확히 있으면 프로그램을
완전하게 했을텐데 일반적인? 직장인이 뭘 원하지 몰라서 프로세스 별로 분리해서 작성했습니다.
아래에 원본 소스 붙입니다.
Public Sel_Rng As Range
‘ combobox, listbox의 Object에 선택한 컬럼의 유일한 데이터 추출
Sub Uniq_Item_Sort(Obj As Object, Col_no As Integer)
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
‘빈셀을 포함시키지 않음, 같은 열에서 빈 셀을 뺀 나머지 자동 선택
Set AllCells = Columns(Col_no).SpecialCells(xlTextValues)
On Error Resume Next
For Each Cell In AllCells
‘ Add method의 2번째 인자는 문자열이어야만 함
NoDupes.Add Cell.Value, CStr(Cell.Value)
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
Obj.AddItem item
Next item
Set Cell = Nothing
Set Obj = Nothing
End Sub
‘ 응용법 : combobox, listbox의 Object에 선택한 영역의 유일한 데이터 추출
Sub Rng_Uniq_Item_Sort(Obj As Object, Sel_Rng As Range)
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Set AllCells = Sel_Rng
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
Obj.AddItem item
Next item
Set Cell = Nothing
Set Obj = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Set AllCells = Sel_Rng
On Error Resume Next
For i = 0 To UserForm1.ListBox1.ListCount – 1
For Each Cell In AllCells
If Len(Cell.Value) > 0 Then ‘ 빈셀을 포함시키지 않음
If Str(UserForm1.ListBox1.List(i, 0)) = Str(Cell.Value) Then
j = j + 1
End If
End If
Next Cell
UserForm1.ListBox1.List(i, 1) = j
j = 0
Next i
For i = 0 To UserForm1.ListBox1.ListCount – 1
ListBox1.List(i, 2) = Format((Val(ListBox1.List(i, 1)) / Val(ListBox1.ListCount)) * 100, “##.0”)
Next i
Set Cell = Nothing
End Sub
Private Sub CommandButton2_Click()
Dim i As Integer, j As Integer
Dim targetcell As Range
Set targetcell = Application.InputBox(“영역을 선택하세요”, Type:=8)
Application.ScreenUpdating = False
For i = 0 To UserForm1.ListBox1.ListCount – 1
For j = 0 To UserForm1.ListBox1.ColumnCount – 1
targetcell(i + 1, j + 1) = UserForm1.ListBox1.List(i, j)
Next j
Next i
Application.ScreenUpdating = True
Unload UserForm1
‘ 메모리에서 지움
Set Sel_Rng = Nothing
End Sub
Private Sub ListBox1_Click()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Set AllCells = Sel_Rng
‘속도를 위해 화면 업데이트 취소
Application.ScreenUpdating = False
On Error Resume Next
For Each Cell In AllCells
‘ 기존 색칠된 셀을 흰색으로 우선 되돌림
Cell.Interior.Color = vbWhite
If Len(Cell.Value) > 0 Then ‘ 빈셀을 포함시키지 않음
If Str(ListBox1.List(ListBox1.ListIndex, 0)) = Str(Cell.Value) Then
Cell.Interior.Color = vbGreen
End If
End If
Next Cell
Set Cell = Nothing
‘ 업데이트 시작
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
Sheets(“Data”).Activate
On Error Resume Next
Set Sel_Rng = Application.InputBox(“영역을 선택하세요”, Type:=8)
If Sel_Rng Is Nothing Then
Exit Sub
Else
Call Rng_Uniq_Item_Sort(ListBox1, Sel_Rng)
End If
End Sub
개발도구 > Visual Basic 선택하시고 UserForm1을 삽입합니다. Listbox1하나 만드시고
Column Count 3을 선택하고 Commandbutton1, Commanbutton2 만드시고 Userform1을
더블 클릭 하시고 위 코드 붙여넣기 하시면 됩니다.
엑셀 시트에서 개발도구 삽입 > ActiveX Control에 명령단추 하나 삽입하시고 더블 클릭
Load UserForm1
UserForm1.Show
이제 디자인 모드 해제하시고 명령 단추를 클릭하면 UserForm에 유일한 데이터만 보입니다.
그리고 Commandbutton1을 누르시면 유일한 데이터의 갯수와 전체 차지하는 비율을 보여주고
(물론 빈셀은 빼고 입니다. 필요하시면 코멘트 부분 제거하시고 사용하시면 됩니다.)
그리고 ListBox1의 리스트를 클릭하시면 그 데이터 있는 셀의 위치를 녹색으로 보여줍니다.
이 부분을 다양하게 응용하시면 업무하시는데 도움이 될거라고 봅니다. Commandbutton2를
누르시면 추출된 데이터를 필요한 위치에 뿌려 줍니다. 리스트 박스에 List를 가져오는 것과
Listbox 클릭을 잘 이용하시면 설정된 영역의 데이터를 어떤 식으로든 가공이 편할 것입니다.
제가 VBA Form을 가지고 작업하는 이유는 1차 가공된 데이터 들을 Form상에서 2차 가공이
편리하기 때문에 이 방법을 자주 사용하고 있습니다. 순수하게 데이터를 추출하시려고 하면
바로 하는 것이 좋지만 대분분의 데이터는 2차 가공이 필요하기 때문이죠.
4월 13 2015
엑셀(EXCEL) – 임의 영역에서 중복된 데이터 추출 및 데이터 가공
소스) 20150410-UniqueItemSort
우리가 엑셀을 다루면서 특히 시험데이터 등에서 중복되는 데이터들이 많이 나타납니다.
반복 시험을 하는 경우나 생산 현장의 생산 기록 등 중복되는 데이터들을 다루어야 할 경우
엑셀의 기본 기능을 가지고 중복데이터를 추출하면 되지만 이것이 행으로 이루어져 있질 않고
임의의 영역에 걸쳐 데이터가 존재할 때는 추출하기가 사실 막막하기만 것이 현실입니다.
일반인이 아닌 직장인?분들이야 쉽게 데이터를 추출할 수 있으시겠지만, 물론 일반 직장인도
여러가지 방법을 통해 반복적으로 하면 데이터를 추출해서 취합하실 수 있습니다. 그렇지만
조금만 아이디어를 가지고 하면 반복적인 일들을 한 번에 추출하고 가공이 가능하지요.
우선 엑셀의 기본 기능을 가지고 데이터 중에서 중복되는 데이터를 하나의 데이터로 추출하는
방법을 여러 가지로 해 봅니다. (엑셀 2007 기준) 2010버전은 제가 사용해 보지 못한 관계로
2010에 이 기능이 있을 수도 있습니다. 그러면 전 하루를 쓸 데 없는 데 힘 쓴 경우네요. ㅠㅠ
1. 우선 데이터 > 필터 – 고급 기능을 이용하는 경우
결과 : 다른 장소에 복사
목록 범위 : 원하는 영역을 선택
복사위치 : 복사될 위치를 선택
동일한 레코드는 하나만 선택
2. 데이터 > 중복된 항목 제거 기능 선택
3. 피벗 테이블 이용 : 삽입 > 피벗테이블 선택
표 또는 범위 선택
보고서 놓을 위치 : 기존 워크시트에서 셀 선택
4. 함수를 만들어서 내장? 함수를 이용하는 경우
엑셀 시트에 삽입 > ActiveX Control : 명령 단추 하나 삽입하고 모듈에 아래 코드 삽입
Sub FindUniqueValues()
Dim soruce As Range, targetcell As Range
Set soruce = Application.InputBox(“영역을 선택하세요”, Type:=8)
Set targetcell = Application.InputBox(“셀을 선택하세요”, Type:=8)
soruce.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=targetcell, Unique:=True
End Sub
이 정도가 기본 기능만을 사용해서 오직 같은 ‘행’에서 유일한 데이터를 추출하는 방법입니다.
물론 조금씩 내장함수와 기타 기능을 응용해서 추출하실 수 있는 방법은 다양할 것으로 보입니다.
아래는 위의 결과를 원본과 함께 순서대로 오른쪽으로 결과를 나열했습니다.
(원본) (1) (2) (3) (4)
그런데 추출된 데이터를 보면 몇 가지 문제점이 있습니다. 피폿을 이용한 경우를 빼고 데이터가
빈 셀을 포함해서 정렬이 되지 않고 빈셀이 중간에 나타나 보기가 싫어집니다. 물론 데이터를
정렬해서 보며 괜찮지만 번거러운 일이 아닐 수 없습니다.
그렇지만 위의 문제점은 그냥 번거러움의 문제지 기능의 문제는 아닙니다. 엑셀의 내장 기능을
사용한 경우 “열”으로만 나열된 데이터에서 유일한 데이터를 추출해 줍니다. 일정 영역에서
데이터를 추출하려면 그 영역을 복사하거나 해서 한 열 아래로 모두 맞추고 추출해야 하지요.
데이터가 얼마되지 않으면 이런 수고스러움을 감수하면 되겠지만 몇 만개가 되면 큰 일 입니다.
그리고 그 추출한 데이터를 가공하고 싶을 때 또 번거러운 작업과 내장 함수를 이용해서 추출하고
하는 것이 번거러워서 이 팁을 공유하고자 합니다. 하고자 하는 목적이 정확히 있으면 프로그램을
완전하게 했을텐데 일반적인? 직장인이 뭘 원하지 몰라서 프로세스 별로 분리해서 작성했습니다.
아래에 원본 소스 붙입니다.
Public Sel_Rng As Range
‘ combobox, listbox의 Object에 선택한 컬럼의 유일한 데이터 추출
Sub Uniq_Item_Sort(Obj As Object, Col_no As Integer)
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
‘빈셀을 포함시키지 않음, 같은 열에서 빈 셀을 뺀 나머지 자동 선택
Set AllCells = Columns(Col_no).SpecialCells(xlTextValues)
On Error Resume Next
For Each Cell In AllCells
‘ Add method의 2번째 인자는 문자열이어야만 함
NoDupes.Add Cell.Value, CStr(Cell.Value)
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
Obj.AddItem item
Next item
Set Cell = Nothing
Set Obj = Nothing
End Sub
‘ 응용법 : combobox, listbox의 Object에 선택한 영역의 유일한 데이터 추출
Sub Rng_Uniq_Item_Sort(Obj As Object, Sel_Rng As Range)
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Set AllCells = Sel_Rng
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
Obj.AddItem item
Next item
Set Cell = Nothing
Set Obj = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Set AllCells = Sel_Rng
On Error Resume Next
For i = 0 To UserForm1.ListBox1.ListCount – 1
For Each Cell In AllCells
If Len(Cell.Value) > 0 Then ‘ 빈셀을 포함시키지 않음
If Str(UserForm1.ListBox1.List(i, 0)) = Str(Cell.Value) Then
j = j + 1
End If
End If
Next Cell
UserForm1.ListBox1.List(i, 1) = j
j = 0
Next i
For i = 0 To UserForm1.ListBox1.ListCount – 1
ListBox1.List(i, 2) = Format((Val(ListBox1.List(i, 1)) / Val(ListBox1.ListCount)) * 100, “##.0”)
Next i
Set Cell = Nothing
End Sub
Private Sub CommandButton2_Click()
Dim i As Integer, j As Integer
Dim targetcell As Range
Set targetcell = Application.InputBox(“영역을 선택하세요”, Type:=8)
Application.ScreenUpdating = False
For i = 0 To UserForm1.ListBox1.ListCount – 1
For j = 0 To UserForm1.ListBox1.ColumnCount – 1
targetcell(i + 1, j + 1) = UserForm1.ListBox1.List(i, j)
Next j
Next i
Application.ScreenUpdating = True
Unload UserForm1
‘ 메모리에서 지움
Set Sel_Rng = Nothing
End Sub
Private Sub ListBox1_Click()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, item
Set AllCells = Sel_Rng
‘속도를 위해 화면 업데이트 취소
Application.ScreenUpdating = False
On Error Resume Next
For Each Cell In AllCells
‘ 기존 색칠된 셀을 흰색으로 우선 되돌림
Cell.Interior.Color = vbWhite
If Len(Cell.Value) > 0 Then ‘ 빈셀을 포함시키지 않음
If Str(ListBox1.List(ListBox1.ListIndex, 0)) = Str(Cell.Value) Then
Cell.Interior.Color = vbGreen
End If
End If
Next Cell
Set Cell = Nothing
‘ 업데이트 시작
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
Sheets(“Data”).Activate
On Error Resume Next
Set Sel_Rng = Application.InputBox(“영역을 선택하세요”, Type:=8)
If Sel_Rng Is Nothing Then
Exit Sub
Else
Call Rng_Uniq_Item_Sort(ListBox1, Sel_Rng)
End If
End Sub
개발도구 > Visual Basic 선택하시고 UserForm1을 삽입합니다. Listbox1하나 만드시고
Column Count 3을 선택하고 Commandbutton1, Commanbutton2 만드시고 Userform1을
더블 클릭 하시고 위 코드 붙여넣기 하시면 됩니다.
엑셀 시트에서 개발도구 삽입 > ActiveX Control에 명령단추 하나 삽입하시고 더블 클릭
Load UserForm1
UserForm1.Show
이제 디자인 모드 해제하시고 명령 단추를 클릭하면 UserForm에 유일한 데이터만 보입니다.
그리고 Commandbutton1을 누르시면 유일한 데이터의 갯수와 전체 차지하는 비율을 보여주고
(물론 빈셀은 빼고 입니다. 필요하시면 코멘트 부분 제거하시고 사용하시면 됩니다.)
그리고 ListBox1의 리스트를 클릭하시면 그 데이터 있는 셀의 위치를 녹색으로 보여줍니다.
이 부분을 다양하게 응용하시면 업무하시는데 도움이 될거라고 봅니다. Commandbutton2를
누르시면 추출된 데이터를 필요한 위치에 뿌려 줍니다. 리스트 박스에 List를 가져오는 것과
Listbox 클릭을 잘 이용하시면 설정된 영역의 데이터를 어떤 식으로든 가공이 편할 것입니다.
제가 VBA Form을 가지고 작업하는 이유는 1차 가공된 데이터 들을 Form상에서 2차 가공이
편리하기 때문에 이 방법을 자주 사용하고 있습니다. 순수하게 데이터를 추출하시려고 하면
바로 하는 것이 좋지만 대분분의 데이터는 2차 가공이 필요하기 때문이죠.
By vinipapa • 무른모 • 0 • Tags: 엑셀, 유일 아이템, 중복 데이터