Excel Macro를 이용하여 셀에서 특정 값들을 추출하는 방법

https://youtu.be/G3v1R-INnvw

어떤 경우에 취합되어 있는 내용에서 각 세부 단위별로 조사된 내용을 분리해야 하는 경우가 있습니다. 취합도 문제지만, 이런 경우는 많은 수동 작업을 기계적으로 해야 할 소지가 많습니다. 그래서 간단한 예제를 만들었습니다.

먼저 취합된 데이터는 아래와 같이 부서별로 총수량과 상세 내역이 취합수량열에 정리되어 있습니다. 

실제로 조사된 내역대로 파트별로 수량을 배포하고 확인하기 위한 용도로 다시 세로로 쪼개고 싶은 경우입니다.

즉 아래가 입력이고

 

아래쪽이 출력입니다.

물론 실제 엑셀에는 원래 취합수량열도 남아 있습니다.

이렇게 변환하려면 많은 수동 작업을 해야 합니다.

오토핫키와 매크로를 공부하는 우리가 그렇게 할 수는 없죠. 

고민을 해야죠. 어떻게 할 건인지?

우선 원본시트는 그냥 두고, 별도의 시트가 필요하겠고,

다음으로 part, count열을 추가하고 각각의 내용을 파트수만큼 복사해서 집어 넣고

각 파트별로 수량을 분리해서 집어 넣어 주면 논리적으로는 완성이 됩니다.

별도의 시트는 어떻게 추가할까요? 어렵지 않습니다.

    Sheets.Add After:=ActiveSheet

이 한줄로 추가가 됩니다. 그리고 나서 해당 시트의 이름을 우리가 원하는 것으로 바꿉니다.

저는 Result로 바꾸겠습니다.

    ActiveSheet.Name = "Result"

Result가 맘에 들지 않는 분들은 맘에 드는 이름으로 바꾸시면 됩니다.

원래 시트이름은 Origin으로 했습니다. 

다음은 part와 count열을 추가해야 하는데, 그 전에 원본시트의 최종행과, 열을 찾아 놓아야 합니다.

여러 방법이 있지만, 아래처럼 진행하겠습니다.

    With ThisWorkbook.Worksheets("Origin")
        endRow = Cells(Rows.Count, 1).End(xlUp).Row
        endCol = .UsedRange.Columns.Count + .UsedRange.Column - 1
    End With

다음으로 part와 count열을 추가할려면 원본에 해야 할까요? 복사본에 해야 할까요?

모두 가능하지만 저는 원본에 추가하고 그 내용을 복사본에 복사하기로 했습니다.

그러면 최초 한번만 추가하고 마지막에 지워주면 되거든요.

    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

다음으로 타이틀에 해당하는 첫행을 먼저 복사해 줍니다.

    Rows("1:1").EntireRow.Select
    ActiveCell.Activate
    Selection.Copy
    Sheets("Result").Select
    ActiveCell.Select
    ActiveSheet.Paste

복사본에서 Part와 Count을 타이틀로 집어 넣습니다.

    Cells(1, 4).Value = "Part"
    Cells(1, 5).Value = "Count"

원본에서도 행별로 읽어서 복사본에 넣어야 하는데 원본에서도 각행을 처리할 변수가 필요하고,

복사본에서도 각 행을 처리할 변수가 필요하므로 원본에서는 rw로 사용하고 복사본에서는 drow로 사용하겠습니다.

시작은 2행부터이므로 모두 초기값은 2로 줍니다.

원본에서는 rw값이 for loop로 돌아가므로 별도 초기화 과정없이 진행할 수 있지만, 복사본에서는 drow값을 2로 설정해 줘야 합니다. 

    drow = 2
    For rw = 2 To endRow

이제 원본 시트에서 첫번째 행의 6열의 셀값을 읽습니다. 거기에 추출할 데이터 원본이 있습니다. 

이 데이터 안에서 추출할 갯수는 파트갯수인데, 다음과 같이 파트 갯수를 파악합니다.

전체 스트링 길이로부터  현재 스트링에서 ,를 없애고 난 스트링의 길이를 빼주면 파트의 갯수보다 1개 작은 숫자가 추출됩니다. 정확히 맞추고 싶으면 1을 더해 주면 됩니다.

다음으로 원본 데이터를 복사하기 위해 행의 범위를 잡습니다. rw행만 복사하면 되므로 아래처럼 범위를 잡고 해당 열 전체를 선택합니다.

        src = Trim(Str(rw)) + ":" + Trim(Str(rw))
        Rows(src).EntireRow.Select

이제 그 부분을 복사합니다.

        Selection.Copy

다시 복사본으로 위치를 바꾸고 현재 행부터 파트갯수만큼 더한 영역을 복사할 영역으로 잡고 집어 넣습니다.

        Sheets("Result").Select
        dest = Trim(Str(drow)) + ":" + Trim(Str(drow + no))
        Rows(dest).EntireRow.Select
        ActiveSheet.Paste

이제 각 파트와 수량을 추출해서 넣을 차례입니다.

우선 원본 데이터에서 불필요한 내용을 없애줍니다.

먼저 수량뒤에 있는 "EA)"를 없애줍니다.

Replace(org, "EA)", "") ==> 이 부분을 ㅁ로 생각하죠.

다음으로는 빈칸이 있으면 역시 없애줍니다.

Replace(ㅁ, " ", "") ==> 이 부분을 ★로 생각하죠

다음으로는 "Part(" 부분을 ":"으로 변경하겠습니다.

Replace(★, "Part(", ":") ==> 이 부분을 ▲로 생각하죠

다음으로는 혹시나 있을 줄바꿈 문자를 없애주겠습니다.

Replace(▲, vbLf, "") 

이 과정을 한줄로 바꾸면 다음과 같습니다.

org = Replace(Replace(Replace(Replace(org, "EA)", ""), " ", ""), "Part(", ":"), vbLf, "")

이제 각 파트와 수량만 :으로 묶인 상태에서 ,으로 구분되어 있으므로 split을 써서 배열로 바꿔줍니다.

org2 = Split(org, ",")

이제 org2에는 파트 갯수만큼의 배열이 생겼고, 각 항목에는 파트:수량으로 구성되어 있습니다.

따라서 파트숫자만큼 for loop를 돌면서 파트와 수량을 split으로 빼내면 됩니다.

        For i = 0 To no
            ':앞은 파트, :뒤는 수량으로 넣어주기
            rwVal = Split(org2(i), ":")
            Cells(drow, 4).Value = Trim(rwVal(0))
            Cells(drow, 5).Value = Int(Trim(rwVal(1)))
            '결과시트에서 변경될 행의 숫자
            drow = drow + 1
        Next

1번째 for loop를 마무리 해줍니다.

Next

이제 원본 시트에서 D열과 E열을 삭제해 줘야 합니다.

    Sheets("Origin").Select
    Columns("D:E").Select
    Selection.Delete Shift:=xlToLeft

이 과정을 통해서 원하는 데이터를 추출하는 과정을 소개했습니다.

자세한 내용은 유첨 파일을 참고 하십시요.

단축키는 Ctrl+q로 설정했으니 쉽게 사용하면 됩니다.

그리고 다시 실행하기 우해서는 복사본인 Result시트를 지워야 합니다.

그렇지 않으면 오류가 발생합니다.

CellExtract.xls

0.04MB

추가적인 내용이 필요하시면 약간 응용하면 될거 같습니다.