엑셀 split - egsel split

반응형

  업무를 하다보면 엑셀에 여러 정보가 한 셀에 묶여 있어 정보별로 구분을 해야하는 경우가 있다. 예를 들면 위와 같이 설명란에 나이, 국적, 연락처가 한번에 섞여있어 구분이 필요한 경우를 들 수 있다. 위 이미지는 가상의 예시로 만들어본거고 실제 회사에서 업무를 할 때 묶여있는 정보를 유의미하게 분류하는 작업이 필요하다. (데이터 전처리)

  가장 쉽게 하는 방법은 파이썬을 활용하는 것이다. 파이썬을 활용하여 코드 몇줄 작성하면 위와 같은 케이스는 쉽게 해결이 가능하다. 그래도 이 포스팅은 VBA 공부를 위한 포스팅이므로 엑셀 VBA 입장에서 내용을 정리해본다.

엑셀 VBA도 파이썬과 마찬가지로 다양한 내장함수들을 제공한다. 그 중 자주 쓰이는 2가지 함수가 Split 함수와 UBound 함수이다.

1. Split 함수

  Split 함수는 특정 문자열을 지정한 구분자를 통해 배열로 분리하는 VBA 메서드이다. 파이썬에도 같은 이름의 함수가 있는데 차이는 파이썬은 리스트로 분리한다는 점. 사용법은 아래와 같다.

  보통 문자열을 구분하는 경우는 위 엑셀 데이터처럼 특정 문자(콤마, 공백) 등 을 기준으로 구분하므로 첫번째, 두번째 요소인 "문자열", "구분자"를 잘 활용하면 된다. 결과는 배열로 반환한다. 아래 예시를 보자.

<예시 코드>

Sub 문자열구분()

    Dim str As String
    Dim var As Variant

    str = "연습,test,그냥"

    var = Split(str, ",")


End Sub

<결과>

"연습,test,그냥"이라는 문자열 데이터를 ","기준으로 분리하니 3개 요소를 가진 배열이 생성되었다.

2. Ubound 함수

  Ubound 함수는 배열의 크기를 반환해주는 함수이다. 보통 여러개의 배열을 루프를(for문 등) 통해 동작시켜야할 경우 사용한다. 배열의 크기가 각가 동일하지 않을 경우 Ubound 함수를 사용하여 for문의 최대값을 정해주면 된다.

(3번 코드에서 설명)

  위 1번 예시코드에서 size라는 integer 변수를 선언하고 size = UBound(var)를 넣어보면 배열의 크기인 2를 반환하는 것을 확인할 수 있다.

<예시 코드>

str = "연습,test,그냥"
var = Split(str, ",")
size = UBound(var)

<결과>

3. Split, Ubound 코드 예시

  앞서 설명한 예시대로 위 엑셀 양식에서 이름 별로 Age, Nationality, H.P를 분리하여 구분하는 코드를 작성해보았다. 구분자는 ","이다. 위 시트명은 "DB"이고 새로운 구분 데이터를 출력할 곳은 "분류" Sheet이다.

<코드>

Sub sub_split_ubound()

  Dim strData As String 'data를 담을 문자열 변수
  Dim varData As Variant 'Split함수로 데이터를 담을 복합형 변수
  Dim name As String '이름을 저장할 변수
  Dim LastRow As Integer ' sht1에서 마지막 행값 반환
  Dim LastRow2 As Integer 'sht2에서 마지막 행값 반환
  
  Dim a As Integer 'DB 시트의 데이터 행값을 저장할 변수
  Dim i As Integer '분류 시트에 데이터를 출력하기 위한 행값 변수
  
  Dim sht1 As Worksheet
  Dim sht2 As Worksheet
  
  
  Set sht1 = Sheets("DB")
  Set sht2 = Sheets("분류")

  LastRow = sht1.Cells(Rows.Count, 1).End(xlUp).Row 'DB 시트의 마지막 행값 반환
            
  For a = 2 To LastRow
      strData = Sheet1.Cells(a, 2).Value '설명란의 문자열 읽어오기
      varData = Split(strData, ",") ' ,기준으로 문자열 분리(배열 반환)
     
      name = Sheet1.Cells(a, 1).Value '분리된 문자열에 해당되는 이름 저장
    
      LastRow2 = sht2.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
      For i = 0 To UBound(varData)
         sht2.Cells(LastRow2, 1).Value = name
         sht2.Cells(LastRow2, 2).Value = varData(i)
         LastRow2 = LastRow2 + 1
      Next i
      

  Next a
  
  sht2.Range("a1").Value = "이름"
  sht2.Range("b1").Value = "정보"
  
  

End Sub

<결과>

  1,2에서 설명한 코드를 그대로 활용한 내용이라 어렵지 않다. 추가로 설명할 부분은 중간에 Ubound 이다. 위 예시코드는 기준으로 분리하였을 때 배열의 크기가 같지만 만약 Raw data가 수가 많고 분리시 각각 크기가 다르다면 유용한 함수가 될 것이다.

참고링크 : Split, Ubound - https://stat-and-news-by-daragon9.tistory.com/237

[엑셀VBA] Split, 문자열 나누기

안녕하십니까 춤추는초코칩입니다. 업무 중에 필요한 엑셀VBA를 기록하고 있습니다. 오늘은 "텍스트 나누기"기능입니다. 엑셀 VBA의 Split 함수는 문자열을 특정 구분자로 나누고 배열로 반환합니