8월 31 2015
엑셀(EXCEL) – 행별 중복 유형별 해당 숫자 추출
목감기에 걸려 코가 간질간질한데 재채기가 나올려고만 하고 정작 나오지 않는 것처럼
아래 질문은 이해가 되긴 되는데 뭔가 부족하여 전체를 수작업 비슷하게 해야될 것 같아
우선 해결 논리를 세울려고 고민에 고민을 거듭하였습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3517736
(엑셀 함수 질문드립니다.(매칭관련)
질문의 내용을 보면 어떤 형식은 있는데 정규화 시킬 방법을 찾기가 너무 힘들었습니다.
댓글의 내용을 보면 어느 정도 정규화 시킬 수 있을 것 같은데 코는 간질 간질 하는데
재채기가 나올 생각을 하지 않는 것처럼 마음은 답답한데 실마리는 보이지 않았습니다.
<댓글>
달린 순서는 상관없습니다.
——
OXXX
OOXX
OXOX
OXXO
OOOX
OOXO
OXOO
OOOO
XOXX
XOOX
XOXO
XOOO
XXOX
XXOO
XXXO
(일단 복사하고 – 엑셀상에서 다시 선택해서 복사
선택하여붙여넣기-행열바꿔서 식으로 하면 됩니다.)
…
</댓글>
. 고민 중
. 고민 중
. 고민 중
!!!
위 코드를 정렬시켜 봅니다. 그리고 행렬 바꿔 복사해서 보면 이렇게 됩니다.
(한 행에 걸쳐 있는데 이 본문을 보기좋게 하기위하여 줄바꿈했습니다.)
XXXO XXOX XXOO XOXX XOXO XOOX XOOO
OXXX OXXO OXOX OXOO OOXX OOXO OOOX OOOO
뭔가 규칙 아닌 규칙이 보입니다. 잘 보세요. 서로 다른 숫자를 X라 하고 같은 숫자를 O라 할 때
이 X를 0(Zero)로 O를 1로 치환을 해 봅시다. 그러면 아래와 같은 정렬로 됩니다. 뭔가 보이나요?
0001 0010 0011 0100 0101 0110 0111
1000 1001 1010 1011 1100 1101 1110 1111
일단은 어떻게든 숫자로 처리할 수 있을 것 같습니다. 그렇습니다. 컴퓨터는 좋아하고 우리들은
싫어하는? 이진수가 보이네요. 이제 우리의 아이디어가 들어갈 순서입니다. 이진수를 십진수로
바꾸어 주는 함수입니다. (제가 만든 것은 아니고 구걸했습니다. ^^;;;)
Function BinToDec(Bin As String) As Long
Dim i As Integer
For i = 1 To Len(Bin)
BinToDec = BinToDec * 2 + CInt(Mid(Bin, i, 1))
Next
End Function
이제 이 함수의 인자로 저 규칙을 전달하면 아래와 같은 정수를 얻습니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
이제 조건식을 만들고 그 위치에 찾아진 값들을 특정 셀에 뿌려주면 됩니다. 사실 코드를 생각하는
시간이 무지 많이 들었고 그 조건식을 비교하는 루틴에서 아래 설명에 있겠지만 조건을 비교하는
순서를 잘못해서 기준값이라는 개념없이 접근해서 오류가 많이 났습니다.
Option Explicit
Sub Kind_Count()
Dim rcnt As Integer
‘ 순환, 참조할 영역
Dim Cel As Range
Dim RefCel As Range
Dim RngCel As Range
Dim RngRef As Range
‘ 찾을 값들의 최초 참조 영역
Set RngCel = Range(“A4”, Range(“A4”).End(xlDown))
‘ 참조할 값
Set RngRef = Range(“E1:S1”)
‘ 속도를 위해 업데이트 하지 않고 계산
Application.ScreenUpdating = False
‘ 전체 행의 수를 확인하여 자동화 가능
rcnt = Range(“A4”).End(xlDown)
Range(“E4:S” & rcnt).ClearContents
‘ 참조 영역 순환
For Each Cel In RngCel
‘ 참조할 값 순환
For Each RefCel In RngRef
‘ 몇 번의 시행착오를 거치면서 참조할 위치를 선정하는 것이 중요함
‘ 즉 참조할 “1” 혹은 “O”의 위치를 기준으로 같다, 다르다를 설정해야 에러가 나지 않음
‘ 이 구문에서 무지 헤메임
Select Case RefCel.Value
‘ 1과 같다는 것은 4번째 행의 숫자가 전체와 다 다르다는 것을 의미함
‘ 즉 “1”과 “O”의 위치임
Case 1
If Cel.Offset(0, 3).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 3).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 3).Value
End If
Case 2
If Cel.Offset(0, 2).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 2).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 2).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 2).Value
End If
Case 3
If Cel.Offset(0, 2).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 3).Value = Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 2).Value
End If
Case 4
If Cel.Offset(0, 1).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 1).Value <>
Cel.Offset(0, 2).Value And Cel.Offset(0, 1).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 5
If Cel.Offset(0, 1).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 3).Value <>
Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 6
If Cel.Offset(0, 1).Value = Cel.Offset(0, 2).Value And Cel.Offset(0, 1).Value <>
Cel.Offset(0, 0).Value And Cel.Offset(0, 1).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 7
If Cel.Offset(0, 1).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 1).Value =
Cel.Offset(0, 2).Value And Cel.Offset(0, 1).Value = Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 8
If Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 9
If Cel.Offset(0, 0).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 10
If Cel.Offset(0, 0).Value = Cel.Offset(0, 2).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 11
If Cel.Offset(0, 0).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value =
Cel.Offset(0, 2).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 1).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 12
If Cel.Offset(0, 0).Value = Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 2).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 13
If Cel.Offset(0, 0).Value = Cel.Offset(0, 1).Value And Cel.Offset(0, 2).Value <>
Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value = Cel.Offset(0, 0).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 14
If Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value =
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value = Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
‘ …. “15”라는 숫자는 이진수로 하면 “1111”과 같으므로 전체 숫자가 동일하다는 것을
의미함
Case 15
If Cel.Offset(0, 0).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value =
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value = Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
End Select
Next
Next
‘ 업데이트 진행
Application.ScreenUpdating = True
End Sub
‘ 십진수를 이진수로 만드는 함수
Function DecToBin(Dec As Long) As String
Do
DecToBin = CStr(Dec Mod 2) & DecToBin
Dec = Dec \ 2
Loop Until Dec = 0
End Function
‘ 이진수를 십진수로 만드는 함수
Function BinToDec(Bin As String) As Long
Dim i As Integer
For i = 1 To Len(Bin)
BinToDec = BinToDec * 2 + CInt(Mid(Bin, i, 1))
Next
End Function
그리고 마지막 행에 중복 위치별로 숫자의 카운트를 하거나 해당 숫자의 갯수를
세거나 하는 등의 함수를 추가하여 가공이 가능합니다.
첨부 화일 : 20150831-행별 중복 유형별 해당 숫자 추출
9월 2 2015
엑셀(EXCEL) – 일정 영역에서 행별 중복항목 제거 및 추출 방법
아무거나 질문 게시판에 엑셀관련 아래와 같은 무시 무시한 질문과 답변이 올라왔습니다.
해석하면 자동으로 가로로 된 영역에서 중복 값을 제거하고 자기 영역에 다시 복사하여
붙이기를 하는 매크로입니다. 머리가 아프지만 저의 삽질 본능을 깨우는 내용입니다. ㅠㅠ
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-일정 영역에서 행별 중복항목 제거 및 추출 방법
By vinipapa • 무른모 • 0 • Tags: 엑셀, 중복제거, 팁, 행별 중복항목