Excel 팁: 열 값에 따라 데이터를 여러 워크시트 / 워크북으로 분할하기

Excel에서 대규모 데이터 세트를 관리할 때 특정 열 값에 따라 데이터를 여러 워크시트로 분할하는 것이 매우 유용할 수 있습니다. 이 방법은 데이터의 조직을 개선할 뿐만 아니라 가독성을 향상시키고 더 쉬운 데이터 분석을 가능하게 합니다.

예를 들어, 제품 이름과 1분기 판매량 등 여러 항목을 포함한 대규모 판매 기록이 있다고 가정해 보겠습니다. 목표는 이 데이터를 각 제품 이름별로 별도의 워크시트로 분할하여 개별 판매 성과를 별도로 분석할 수 있도록 하는 것입니다.

열 값에 따라 데이터를 여러 워크시트로 분할하기

• VBA 코드 사용하기
• 강력한 기능인 Kutools for Excel 사용하기

VBA 코드를 사용하여 열 값에 따라 데이터를 여러 워크북으로 분할하기

열 값에 따라 데이터를 여러 워크시트로 분할하기

일반적으로 데이터 목록을 먼저 정렬한 다음 하나씩 새 워크시트에 복사하여 붙여넣을 수 있습니다. 하지만 이 과정은 반복적인 작업을 필요로 하며 인내심을 요구합니다. 이 섹션에서는 두 가지 간단한 방법을 소개하여 시간을 절약하고 오류 가능성을 줄이는 동시에 효율적으로 이 작업을 처리하는 방법을 안내합니다.

VBA 코드를 사용하여 열 값에 따라 데이터를 여러 워크시트로 분할하기

1. ALT + F11 키를 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. 삽입 > 모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣습니다.

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3. 그런 다음, F5 키를 눌러 코드를 실행하면 머리글 행을 선택하라는 메시지 상자가 나타납니다. 그런 다음, 확인을 클릭하세요. 스크린샷 보기:

4. 두 번째 메시지 상자에서 분할 기준으로 사용할 열 데이터를 선택한 후, 확인을 클릭하세요. 스크린샷 보기:

5. 활성 워크시트의 모든 데이터가 열 값에 따라 여러 워크시트로 분할됩니다. 결과 워크시트는 분할된 셀의 값에 따라 이름이 지정되며 워크북의 끝에 배치됩니다. 스크린샷 보기:

Kutools for Excel을 사용하여 열 값에 따라 데이터를 여러 워크시트로 분할하기

Kutools for Excel은 스마트한 기능인 Split Data를 Excel 환경에 바로 제공합니다. 데이터를 여러 워크시트로 분할하는 것은 더 이상 어려운 일이 아닙니다. 직관적인 도구는 선택한 열 값 또는 행 수에 따라 데이터 세트를 자동으로 나누어 각 정보가 필요한 위치에 정확히 배치되도록 합니다. 수작업으로 스프레드시트를 정리하는 번거로움을 잊고 더 빠르고 오류 없는 데이터 관리 방식을 경험하세요.

Kutools for Excel 설치 후 데이터 범위를 선택하고 Kutools Plus > Split Data를 클릭하여 여러 워크시트로 데이터 분할 대화 상자를 엽니다.

1. Split based on 섹션에서 특정 열 옵션을 선택하고 드롭다운 목록에서 데이터를 분할할 열 값을 선택합니다.
2. 데이터에 머리글이 포함되어 있고 새로 분할된 워크시트에 이를 삽입하려면 My data has headers 옵션을 체크하세요. (데이터에 따라 머리글 행 수를 지정할 수 있습니다. 예를 들어 데이터에 두 개의 머리글이 포함된 경우 2를 입력하세요.)
3. 그런 다음 New worksheets name 섹션에서 워크시트 이름 규칙을 Rules 드롭다운 목록에서 지정할 수 있습니다. 접두사나 접미사를 시트 이름에 추가할 수도 있습니다.
4. 클릭 확인 버튼. 스크린샷 보기:
   

이제 워크시트의 데이터가 새 워크북의 여러 워크시트로 분할되었습니다.

VBA 코드를 사용하여 열 값에 따라 데이터를 여러 워크북으로 분할하기

때때로 데이터를 여러 워크시트로 분할하는 대신 주요 열을 기준으로 데이터를 별도의 워크북으로 분할하는 것이 더 유리할 수 있습니다. 여기에서는 VBA 코드를 사용하여 특정 열 값에 따라 데이터를 여러 워크북으로 분할하는 과정을 자동화하는 단계별 가이드를 제공합니다.

1. ALT + F11 키를 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. 삽입 > 모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣습니다.

Sub SplitDataByColToWorkbooks()
    ' Updateby Extendoffice
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    Dim xTRg As Range
    Dim xVRg As Range
    Dim xWS As Workbook
    Dim savePath As String
    ' Set the directory to save new workbooks
    savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
    Application.DisplayAlerts = False
    Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", Type:=8)
    If TypeName(xTRg) = "Nothing" Then Exit Sub
    Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", Type:=8)
    If TypeName(xVRg) = "Nothing" Then Exit Sub
    vcol = xVRg.Column
    Set ws = xTRg.Worksheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = xTRg.Address(False, False)
    titlerow = xTRg.Row
    ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
    myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
    ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
    For i = 2 To UBound(myarr)
        Set xWS = Workbooks.Add
        ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
        ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
        xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
        xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"

        xWS.Close SaveChanges:=False
    Next i
    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    ws.Activate
End Sub

3. 그런 다음, F5 키를 눌러 코드를 실행하면 머리글 행을 선택하라는 메시지 상자가 나타납니다. 그런 다음, 확인을 클릭하세요. 스크린샷 보기:

4. 두 번째 메시지 상자에서 분할 기준으로 사용할 열 데이터를 선택한 후, 확인을 클릭하세요. 스크린샷 보기:

5. 분할 후 활성 워크시트의 모든 데이터가 열 값에 따라 여러 워크북으로 분할됩니다. 분할된 모든 워크북은 지정한 폴더에 저장됩니다. 스크린샷 보기:

관련 기사: