같은 내용을 묶어서 합치기(VBA)

안녕하세요.

 

실습 파일 다운로드

같은 내용을 묶어서 합치기(VBA)_실습.xlsm

0.01MB

완성 파일 다운로드

같은 내용을 묶어서 합치기(VBA)_완성.xlsm

0.02MB

출판사별 분야 목록을 나타내려 합니다.

원하는 처리 결과 모습입니다.

 

엑셀 기본 기능인 피벗 테이블, MATCH 함수, TEXTJOIN 함수를 이용해 나타내 보았습니다.

같은 내용을 묶어서 합치기(피벗 테이블, MATCH, TEXTJOIN)

이번에는 VBA로 만들어 보겠습니다.

 

단축키 <Alt + F11>키를 눌러 VBE를 실행합니다.

[삽입] - [모듈]을 선택합니다.

[삽입] - [프로시저]를 선택합니다.

[프로시저 추가] 대화상자 [이름]에 '분야요약만들기'를 입력하고 [확인]을 누릅니다.

[E1], [F1] 셀에 각각 '출판사', '분야' 필드명을 나타냅니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
End Sub

A열 자료처리 위치를 정하는 변수 i에 첫 데이터 위치인 2행을 입력합니다.

그리고 2행부터 입력된 데이터 마지막 행까지 반복해 확인하도록 Do~Loop문을 작성합니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    
    Do
    
        i = i + 1
    Loop While Range("A" & i).Value <> ""
End Sub

출력행 위치를 나타낼 변수 출력행에 첫 출력 위치인 2를 입력합니다.

동일한 출판사명이 출력되었는지 확인하는 변수 확인에 True를 입력합니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    출력행 = 2
    확인 = True
    
    Do
    
        i = i + 1
    Loop While Range("A" & i).Value <> ""
End Sub

Do~Loop문 안에 출력한 출판사와 같은 출판사인지 확인하고, 같은 출판사라면 분야를 추가해 넣도록 작성합니다.

다음 번에 추가될 내용과 구분하기 위해 끝에 ', '(쉼표와 빈칸)을 넣습니다.

같은 출판사를 찾아서 분야를 추가했다면 변수 확인에 False를 넣어 표시합니다.

어떤 동작이 실행되었는지를 다음 단계에서 확인해야할 때 많이 사용되는 방법입니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    출력행 = 2
    확인 = True
    
    Do
        For j = 2 To 출력행
            If Range("A" & i).Value = Range("E" & j).Value Then
                Range("F" & j).Value = Range("F" & j).Value & Range("C" & i).Value & ", "
                
                확인 = False
                Exit For
            End If
        Next j
        
        i = i + 1
    Loop While Range("A" & i).Value <> ""
End Sub

안쪽 For~Next문이 끝났을 경우에도 변수 확인이 True이면 아직 추가된 적이 없는 출판사라는 뜻입니다.

 

그래서 출력하고, 변수 출력행을 1 증가시킵니다.

다음번 동작을 위해 변수 확인은 True로 초기화 합니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    출력행 = 2
    확인 = True
    
    Do
        For j = 2 To 출력행
            If Range("A" & i).Value = Range("E" & j).Value Then
                Range("F" & j).Value = Range("F" & j).Value & Range("C" & i).Value & ", "
                
                확인 = False
                Exit For
            End If
        Next j
        
        If 확인 Then
            Range("E" & 출력행).Value = Range("A" & i).Value
            Range("F" & 출력행).Value = Range("C" & i).Value & ", "
            출력행 = 출력행 + 1
        End If
        
        확인 = True
        
        i = i + 1
    Loop While Range("A" & i).Value <> ""
End Sub

동작할 부분을 다 작성했으니 제대로 동작하는지 확인을 해 보겠습니다.

단축키 <F5>키를 누르면 실행됩니다.

엑셀 창을 확인해 보면 실행된 결과가 보입니다.

실행된 결과를 보니 두 가지 문제가 보입니다.

첫째는 분야가 중복되어 나타난 것입니다.

두번째는 분야 셀 내용 끝에 ', '(쉼표와 빈칸)이 추가되어 있습니다.

첫번째 문제는 미처 예상하지 못한 문제인데, 알고리즘을 다시 짜야 해결될 부분입니다.

두번째 문제는 처음부터 예상했던 문제로 For~Next문을 하나 더 작성해 해결하겠습니다.

이런 식의 코딩도 자주 사용되는 방법입니다.

변수 출력행은 다음 출력될 위치를 기억하고 있기 때문에 현재 출력행 변수에 입력되어 있는 값보다 1 작은 값까지 처리를 하면 실제 출력된 값들에 대해 처리가 됩니다.

Len 함수와 Left 함수는 엑셀의 LEN 함수와 LEFT 함수의 사용법이 똑같습니다.

Len 함수로 전체 길이를 파악한 다음, Left 함수로 마지막 2글자를 제외한 나머지만 글자만 나타냅니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    출력행 = 2
    확인 = True
    
    Do
        For j = 2 To 출력행
            If Range("A" & i).Value = Range("E" & j).Value Then
                Range("F" & j).Value = Range("F" & j).Value & Range("C" & i).Value & ", "
                
                확인 = False
                Exit For
            End If
        Next j
        
        If 확인 Then
            Range("E" & 출력행).Value = Range("A" & i).Value
            Range("F" & 출력행).Value = Range("C" & i).Value & ", "
            출력행 = 출력행 + 1
        End If
        
        확인 = True
        
        i = i + 1
    Loop While Range("A" & i).Value <> ""
    
    For j = 2 To 출력행 - 1
        Range("F" & j).Value = Left(Range("F" & j).Value, Len(Range("F" & j).Value) - 2)
    Next j
End Sub

다시 한번 단축키 <F5>키를 눌러 실행하고, 엑셀 창으로 돌아가면 줄 끝부분에 있던 필요 없는 글자가 사라진 것을 확인할 수 있습니다.

아직 문제가 다 해결된 것은 아니지만 이번 강좌에서는 여기까지만 해결하고, 다른 강좌에서 분야가 중복되지 않도록 처리해 보겠습니다.

 

수고하셨습니다.

 

2020.06.25 - 같은 내용을 묶어서 합치기2(배열)

같은 내용을 묶어서 합치기2(배열)

 

2020.06.26 - 같은 내용을 묶어서 합치기3(중복된 항목 제거, 정렬, VBA)

같은 내용을 묶어서 합치기3(중복된 항목 제거, 정렬, VBA)