엑셀에서 열 값 기준으로 시트를 여러 개로 분할하는 5가지 방법

열 값을 기준으로 엑셀 시트를 여러 시트로 분할하는 가장 쉬운 방법을 찾고 계신다면, 이 글이 큰 도움이 될 것입니다.

대량의 데이터를 특정 열을 기준으로 나누어 여러 시트에서 각각 작업해야 하는 경우가 종종 있습니다. 이 작업을 효율적으로 수행하는 다양한 방법을 지금부터 자세히 살펴보겠습니다.

워크북 다운로드

열 값 기준으로 엑셀 시트를 여러 시트로 분할하는 5가지 방법

이 글에서는 대학 학생들의 성적 결과가 담긴 아래 데이터 표를 사용합니다. 학생 이름(Student Name) 열을 기준으로 세 명의 학생에 해당하는 세 개의 시트로 분할해 보겠습니다.

여기서는 Microsoft Excel 365 버전을 사용하지만, 사용자에게 편리한 다른 버전을 사용해도 무방합니다.

방법 1: FILTER 함수를 활용한 시트 분할

학생 이름 열을 기준으로 데이터 시트를 여러 시트로 나누고 싶다면 FILTER 함수를 사용할 수 있습니다. 여기서는 Daniel Defoe, Henry Jackson, Donald Paul 세 학생의 데이터를 각각 담은 세 개의 시트로 분할해 보겠습니다.

1단계:
➤세 학생의 이름으로 된 시트 세 개를 생성합니다.
➤Daniel Defoe 학생의 시트에서 B3 셀과 같은 임의의 셀을 선택합니다.

➤아래 수식을 입력합니다.

Filter!B5:D16은 Filter라는 이름의 기본 시트에서 머리글 행을 제외한 데이터 범위이며, Filter!B5:B16은 기본 시트의 학생 이름 범위입니다. 이 값이 "Daniel Defoe"와 일치하는지 조건으로 설정한 것입니다.

➤ENTER 키를 누릅니다.
이제 해당 학생의 시트에서 Daniel Defoe의 데이터를 확인할 수 있습니다.

➤마지막으로 각 열 위에 머리글 이름을 입력하고 데이터 표에 테두리 서식을 적용하면 완성됩니다.

2단계:
나머지 두 시트(Henry Jackson, Donald Paul)에도 동일한 방법을 적용하면 각 시트에 아래와 같은 두 개의 표가 만들어집니다.

함께 보면 좋은 글: Excel VBA: 행 기준으로 시트를 여러 시트로 분할하기

방법 2: 피벗 테이블을 활용한 시트 분할

피벗 테이블(Pivot Table)을 사용하면 학생 이름 열을 기준으로 한 시트를 세 학생별 시트로 손쉽게 분할할 수 있습니다.

1단계:
➤삽입 탭 >> 피벗 테이블 옵션으로 이동합니다.

그러면 테이블 또는 범위에서 피벗 테이블 대화 상자가 나타납니다.
➤테이블/범위를 선택합니다.
➤새 워크시트를 클릭합니다(피벗 테이블은 새 시트에 배치하는 것이 좋습니다).
➤확인을 누릅니다.

이후 PivotTable1과 피벗 테이블 필드 두 부분으로 구성된 새 시트가 열립니다.

➤학생 이름(시트를 분할할 기준이 되는 열)을 필터 영역으로 끌어다 놓고, 과목과 등급은 행 영역으로 이동합니다.

➤디자인 탭 >> 레이아웃 그룹 >> 보고서 레이아웃 드롭다운 >> 개요 형태로 표시 옵션을 선택합니다.

➤이어서 디자인 탭 >> 레이아웃 그룹 >> 총합계 드롭다운 >> 행 및 열에 대해 해제 옵션을 선택합니다.

➤그다음 피벗 테이블 분석 탭 >> 피벗 테이블 그룹 >> 옵션 드롭다운 >> 보고서 필터 페이지 표시 옵션으로 이동합니다.

보고서 필터 페이지 표시 마법사가 나타납니다.
➤필터 영역에 있던 학생 이름 열을 선택합니다.
➤확인을 누릅니다.

결과:
완료하면 Daniel Defoe, Henry Jackson, Donald Paul 세 학생별로 각각의 시트가 생성됩니다.

함께 보면 좋은 글: 행 기준으로 엑셀 시트를 여러 시트로 분할하기

방법 3: 테이블 옵션 활용

학생 이름 열을 기준으로 기본 시트를 여러 시트로 나누려면 테이블(Table) 옵션을 활용할 수 있습니다.

1단계:
➤세 학생의 이름으로 된 시트 세 개를 생성합니다.
➤기본 시트의 데이터 표를 복사하여 세 개의 시트에 각각 붙여넣습니다.

2단계:
➤삽입 탭 >> 테이블 옵션으로 이동합니다.

테이블 만들기 대화 상자가 나타납니다.
➤테이블로 만들 데이터 범위를 선택합니다.
➤테이블에 머리글 포함을 체크합니다.
➤확인을 누릅니다.

그러면 아래와 같은 테이블이 생성됩니다.

➤테이블 디자인 탭 >> 도구 그룹 >> 슬라이서 삽입 옵션으로 이동합니다.

슬라이서 삽입 대화 상자가 나타납니다.
➤학생 이름 열(분할 기준이 되는 열)을 선택합니다.
➤확인을 누릅니다.

그러면 세 개의 옵션(세 학생 이름)이 있는 학생 이름 슬라이서 상자가 나타납니다.

➤해당 학생의 시트에서 Daniel Defoe를 클릭합니다.

결과:
해당 학생의 시트에서 Daniel Defoe의 데이터만 표시됩니다.

3단계:
➤나머지 두 시트에도 2단계를 반복합니다.
이렇게 하면 Henry Jackson과 Donald Paul의 시트도 아래와 같이 완성됩니다.

함께 보면 좋은 글: 엑셀 시트를 여러 파일로 분할하는 3가지 빠른 방법

관련 추천 글

• 엑셀에서 화면 분할하는 방법 (3가지)
• [해결 방법] 엑셀 '나란히 보기'가 작동하지 않을 때
• 엑셀에서 세로 정렬로 나란히 보기 활성화하는 방법

방법 4: 필터 옵션 활용

이 방법에서는 학생 이름 열을 기준으로 기본 시트를 분할하기 위해 필터(Filter) 옵션을 사용합니다.

1단계:
➤세 학생의 이름으로 된 시트 세 개를 생성합니다.
➤기본 시트의 데이터 표를 복사하여 세 개의 시트에 각각 붙여넣습니다.

2단계:
➤데이터 표를 선택합니다.
➤데이터 탭 >> 필터 옵션으로 이동합니다.

그러면 데이터 표에 필터 옵션이 활성화됩니다.
➤학생 이름 열의 드롭다운 화살표를 클릭합니다.

➤해당 시트에 맞는 이름 Daniel Defoe를 선택하고 확인을 누릅니다.

결과:
해당 학생의 시트에서 Daniel Defoe의 데이터만 확인할 수 있습니다.

3단계:
➤나머지 두 시트에도 2단계를 반복합니다.
그러면 Henry Jackson과 Donald Paul의 시트도 아래와 같이 완성됩니다.

함께 보면 좋은 글: 엑셀에서 시트를 분리하는 6가지 효과적인 방법

방법 5: VBA 코드를 활용한 시트 분할

VBA 코드를 사용하면 열 값을 기준으로 시트를 여러 시트로 자동 분할할 수 있습니다.

1단계:
➤개발 도구 탭 >> Visual Basic 옵션으로 이동합니다.

Visual Basic 편집기가 열립니다.
➤삽입 탭 >> 모듈 옵션을 선택합니다.

그러면 모듈(Module)이 생성됩니다.

2단계:

➤아래 코드를 입력합니다.

Sub Splitsheet()
Dim lr As Long
Dim sheet 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
On Error Resume Next
Set xTRg = Application.InputBox("Select the header row:", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox _
("Select the column on the basis of which split data:", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set sheet = xTRg.Worksheet
lr = sheet.Cells(sheet.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = sheet.Columns.Count
sheet.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")
sheet.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If sheet.Cells(i, vcol) <> "" And Application.WorksheetFunction. _
Match(ws.Cells(i, vcol), sheet.Columns(icol), 0) = 0 Then
sheet.Cells(sheet.Rows.Count, icol).End(xlUp).Offset(1) = sheet.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(sheet.Columns(icol). _
SpecialCells(xlCellTypeConstants))
sheet.Columns(icol).Clear
For i = 2 To UBound(myarr)
sheet.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
sheet.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr) _
.EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
sheet.AutoFilterMode = False
sheet.Activate
Application.DisplayAlerts = True
End Sub

여기서 Splitsheet()는 Sub 프로시저의 이름이며, 변수 lr, sheet, vcol, i, icol, myarr, title, titlerow, xTRg, xVRg, xWSTRg는 Dimension(Dim) 매개변수를 통해 각각 다른 데이터 형식으로 선언되었습니다.

또한 시트를 여러 시트로 분할하기 위해 여러 개의 IF문과 FOR 루프가 사용되었습니다.

➤F5 키를 누릅니다.

Select the header row:(머리글 행 선택) 대화 상자가 열립니다.
➤머리글 행의 범위를 선택하고 확인을 누릅니다.

그다음 Select the column on the basis of which split data:(분할 기준 열 선택) 마법사가 나타납니다.
➤학생 이름 열을 선택하고 확인을 누릅니다.

결과:
최종적으로 Daniel Defoe, Henry Jackson, Donald Paul 세 학생의 시트가 아래와 같이 생성됩니다.

이 코드에서는 붙여넣기 대상 위치를 A1 셀로 지정했기 때문에 분할된 데이터가 해당 셀부터 시작됩니다.

함께 보면 좋은 글: VBA 코드로 통합 문서를 개별 엑셀 파일로 분할하는 방법

연습 섹션

직접 실습해 볼 수 있도록 Practice라는 이름의 시트에 아래와 같은 연습 섹션을 준비했습니다. 직접 따라 해 보시기 바랍니다.

결론

이 글에서는 엑셀에서 열 값을 기준으로 시트를 여러 시트로 효과적으로 분할하는 가장 쉬운 방법들을 소개했습니다. 도움이 되었기를 바랍니다. 제안이나 궁금한 점이 있다면 언제든지 의견을 남겨 주세요.

추천 학습 자료

• 엑셀에서 시트를 개별 통합 문서로 분할하기 (4가지 방법)
• 엑셀 파일 두 개를 별도로 여는 방법 (5가지 쉬운 방법)
• 하나의 통합 문서에서 여러 엑셀 파일 열기 (4가지 쉬운 방법)
• 엑셀 시트를 별도 창에서 보는 방법 (4가지 방법)