회사에서 엑셀로 작업하다 보면 늘 마주치는 고민이 있습니다. 바로 여러 개의 파일과 시트로 흩어진 데이터를 하나로 합쳐야 할 때인데요!😱 데이터 취합을 일일이 복사-붙여넣기로 하다 보면, 시간도 많이 걸리고 실수도 종종 발생하곤 합니다.
엑셀 모든 버전에서 사용 가능한 파일/시트 합치기 방법을 정리했습니다!✨
버튼 클릭 한 번으로 여러 개의 엑셀 파일과 시트를 한 번에 취합하는 방법을 소개합니다. 엑셀 파워쿼리(2016 이후)와 VBA(모든 버전) 2가지 방법으로 정리했으니, 상황에 맞게 잘 활용해보세요!😉
파워쿼리 활용 (엑셀 2016 이후)
엑셀 2016 이후 버전을 사용하는 분들은 파워쿼리를 활용하면 여러 파일과 시트를 간편하게 취합할 수 있습니다. [데이터] 탭으로 이동한 후, [데이터 가져오기] 메뉴에서 '엑셀, CSV, SQL, 웹 주소' 등 다양한 형식의 데이터를 불러올 수 있습니다.
[데이터] - [데이터 가져오기] 메뉴에서 다양한 형식의 데이터를 엑셀로 불러올 수 있습니다.
오빠두Tip : 파워쿼리 입문자를 위한 원데이 무료 챌린지를 준비했습니다. 챌린지 참여 방법은 아래 링크를 확인하세요!👇
VBA 활용 (엑셀 모든 버전)
엑셀 2013 이전 버전 사용자나 더욱 다양한 자동화 작업이 필요할 경우, VBA를 활용할 수 있습니다. 아래 단계를 따라 파일 합치기 작업을 손쉽게 자동화해보세요!
오빠두Tip : 실무에서 꼭 필요한 VBA 의 핵심 이론과 활용 예제는 아래 원데이 무료 챌린지에서 알기 쉽게 정리했습니다. 엑셀 자동화가 필요하다면, VBA 원데이 챌린지를 통해 엑셀 업무 생산성을 크게 향상시켜 보세요!😊
- 비어있는 시트에 취합할 데이터의 머리글을 입력합니다.
취합할 데이터의 머리글을 작성합니다. 머리글의 순서는 반드시 동일해야 합니다.
오빠두Tip : 파워쿼리는 머리글 순서에 상관없이 동일한 머리글을 자동으로 취합하지만, VBA를 사용할 경우에는 취합할 파일의 머리글이 반드시 동일한 순서로 배치되어야 하는 점을 주의하세요!
-
매크로 편집기 - 삽입 - 모듈로 새 모듈을 추가합니다.
[개발도구] 탭 - [Visual Basic] 버튼을 클릭하거나 Alt + F11을 눌러 매크로 편집기를 실행합니다. [삽입] 탭 - [모듈]을 선택하여 새로운 모듈을 추가합니다. 만약 개발도구가 보이지 않을 경우, 리본 메뉴를 우클릭 → 리본메뉴 사용자 지정에서 개발도구 탭을 활성화합니다.
- 아래 코드를 복사한 후, 모듈에 붙여넣기 합니다.
'---- 특정 단어를 포함하는 시트만 취합하려면 입력하세요 -----
Const 포함시트명 As String = ""
'----------------------------------------------
Sub 파일및시트합치기()
Dim strFilePath As String: strFilePath = Multiple_FileDialog
run_Merge strFilePath
End Sub
'-----------------------------------------------
Sub run_Merge(paths)
Dim WB As Workbook
Dim WS As Worksheet: Dim toWS As Worksheet
Dim rng As Range
Dim i As Long: i = 0: Dim j As Long
Dim endCol As Long: Dim endRow As Long
Dim strWS As String
Dim varFilePaths As Variant: Dim varFilePath As Variant
'// 스크린업데이트 중단 (빠른 동작을 하려면 작은따옴표를 ScreenUpdating에 작은 따옴표를 제거하세요!)
'Application.ScreenUpdating = False
Application.DisplayAlerts = False
'// 오류방지
If Len(paths) = 0 Then
MsgBox "병합할 파일을 선택하세요."
Exit Sub
End If
'// 파일병합
Set toWS = ActiveSheet
j = toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Row + 1
varFilePaths = Split(paths, ", ")
For Each varFilePath In varFilePaths
Set WB = Application.Workbooks.Open(varFilePath)
For Each WS In WB.Worksheets
If WS.Name Like 포함시트명 & "*" Then
With WS
endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
rng.Copy toWS.Cells(j, 1)
j = j + rng.Rows.Count
End With
End If
Next
WB.Close
Next
'// 안내메세지
MsgBox "파일 병합이 완료 되었습니다."
'//스크린 업데이트 활성화 (빠른 동작을 하려면 작은따옴표를 ScreenUpdating에 작은 따옴표를 제거하세요!)
'Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Public Function Multiple_FileDialog(Optional Title As String = "파일을 선택하세요", Optional FilterName As String = "엑셀파일", _
Optional FilterExt As String = "*.xls; *.xlsx; *.xlsm", Optional InitialFolder As String = "", _
Optional InitialView As MsoFileDialogView = msoFileDialogViewList, Optional MultiSelection As Boolean = True) As String
Dim FDG As FileDialog
Dim Selected As Integer: Dim i As Integer
Dim ReturnStr As String
Set FDG = Application.FileDialog(msoFileDialogFilePicker)
With FDG
.Title = Title
.Filters.Add FilterName, FilterExt
.InitialView = InitialView
.InitialFileName = InitialFolder
.AllowMultiSelect = MultiSelection
Selected = .Show
If Selected = -1 Then
For i = 1 To FDG.SelectedItems.Count - 1
ReturnStr = ReturnStr & FDG.SelectedItems(i) & ", "
Next i
ReturnStr = ReturnStr & FDG.SelectedItems(.SelectedItems.Count)
Multiple_FileDialog = ReturnStr
ElseIf Selected = 0 Then
MsgBox "선택된 파일이 없으므로 프로그램을 종료합니다."
End
End If
End With
End Function
- 코드에서 '파일및시트합치기' 함수를 선택한 후 [실행] 버튼을 클릭하거나 F5 키를 눌러 매크로를 실행합니다.
파일및시트합치기 코드를 실행합니다.
- 파일 선택창에서 취합할 파일을 선택한 후 [확인] 버튼을 클릭합니다.
파일 선택창에서 취합할 파일을 선택한 후, [확인] 버튼을 클릭합니다.
- "파일 병합이 완료되었습니다."라는 메시지가 출력되며, 선택한 엑셀 파일의 모든 시트 데이터가 한 번에 취합됩니다.
파일 및 시트 합치기가 완료됩니다.
오빠두Tip : 코드 상단에서 "포함시트명"을 지정하면 특정 단어(예: "서울")가 포함된 시트만 선택하여 취합할 수 있습니다.
