[고급] 엑셀에서 대량 데이터 병합 & 통합하는 고급 기술

엑셀에서 대량 데이터 병합 & 통합하는 고급 기술

엑셀(Excel)에서 여러 개의 데이터를 병합하고 통합하는 작업은 보고서 작성, 데이터 분석, 실무 자동화 등에 필수적이다. 특히 여러 개의 시트에서 데이터를 한 곳에 모으거나, 여러 파일을 하나의 테이블로 결합하는 작업은 대량 데이터 처리 시 반드시 필요한 기능이다. 이번 글에서는 엑셀에서 대량 데이터를 병합 & 통합하는 고급 기술을 실전 예제와 함께 설명하겠다.

➔ 엑셀에서 대량 데이터 병합이 필요한 이유

◆ 대량 데이터 병합이 필요한 주요 상황

여러 개의 시트 데이터 합치기: 같은 형식의 데이터가 여러 시트에 분산되어 있음
여러 개의 파일을 하나로 통합: 월별/부서별 파일을 하나의 통합 파일로 만들기
중복 제거 후 통합: 중복된 데이터를 제거하고 고유 값만 남기기
새로운 데이터가 추가될 때 자동 병합: 매월 새로운 데이터가 추가될 때 자동 업데이트

★ Tip: 매크로(VBA), 파워쿼리(Power Query), 함수(SUMIFS, INDEX, MATCH) 등 다양한 방법으로 데이터를 병합할 수 있다.
⇒ 이제 각 방법을 실전 예제를 통해 살펴보자.

➔ 1. 여러 개의 시트 데이터를 하나로 병합하기 (Power Query 활용)

엑셀 Power Query(파워쿼리)를 사용하면 여러 개의 시트 데이터를 자동으로 하나로 합칠 수 있다.

◆ 예제 데이터 (각 시트에 동일한 형식의 데이터 존재)

[Sheet1] 데이터

[Sheet2] 데이터

◆ Power Query로 여러 시트 통합하는 방법
1. 데이터 → 데이터 가져오기 → 다른 원본에서 → 빈 쿼리 선택
2. 홈 → 고급 편집기 클릭 후 아래 코드 입력
= Excel.CurrentWorkbook()
3. 각 시트의 데이터가 목록으로 표시됨
4. 필요한 데이터 선택 후 “추가” 클릭
5. 닫기 & 로드 버튼을 눌러 데이터 병합 완료

★ Tip:
• 새로운 데이터가 추가될 경우 “새로 고침” 버튼만 누르면 자동 업데이트됨
• 동일한 구조의 데이터를 쉽게 병합할 수 있어 실무에서 매우 유용
⇒ Power Query를 활용하면 여러 개의 시트 데이터를 자동으로 병합할 수 있다.

➔ 2. 여러 개의 파일을 하나로 병합하기 (폴더 내 파일 자동 통합)

여러 개의 엑셀 파일(.xlsx, .csv 등)이 하나의 폴더에 저장되어 있을 때, 이를 자동으로 통합하는 방법을 알아보자.

◆ 예제: 각 파일에 “월별 매출 데이터” 저장됨
• “1월.xlsx”, “2월.xlsx”, “3월.xlsx” … 등의 파일을 하나로 병합하고 싶음

◆ Power Query로 폴더 내 모든 파일 자동 병합
1. 데이터 → 데이터 가져오기 → 폴더에서 선택
2. 데이터가 저장된 폴더 경로 입력 후 “확인” 클릭
3. 모든 파일 목록이 표시됨 → “파일 내용 결합” 클릭
4. 데이터 미리 보기 후 “확인” 클릭
5. 닫기 & 로드 클릭하여 엑셀 시트에 데이터 불러오기

★ Tip:
• 새로운 파일이 폴더에 추가되면, “새로 고침”만 누르면 자동으로 업데이트됨
• 여러 개의 엑셀 파일을 매번 수작업으로 합칠 필요가 없어 업무 효율성이 극대화됨
⇒ 폴더 내 파일을 자동 병합하는 Power Query 기능은 실무에서 반드시 알아야 할 필수 기능이다.

➔ 3. SUMIFS 함수로 여러 시트 데이터 합산하기

◆ 예제: 각 시트에 부서별 매출 데이터가 저장됨

[Sheet1] “영업팀” 데이터

[Sheet2] “마케팅팀” 데이터

◆ SUMIFS 함수로 데이터 합산
=SUMIFS(Sheet1!B2:B3, Sheet1!A2:A3, A2) + SUMIFS(Sheet2!B2:B3, Sheet2!A2:A3, A2)
◇ 특정 제품의 매출액을 여러 시트에서 합산하여 하나의 표로 정리
◇ 제품명이 동일하면 각 시트의 매출액을 자동으로 합산

★ Tip:
• SUMIFS 함수는 여러 개의 시트 데이터를 한 곳에서 합산할 때 매우 유용
• 시트가 많아지면 Power Query 또는 VBA를 사용하는 것이 더 효율적
⇒ SUMIFS 함수는 비교적 간단한 데이터 병합 작업에서 활용할 수 있다.

➔ 4. VBA 매크로를 활용한 대량 데이터 병합

VBA(Visual Basic for Applications)를 사용하면 반복적인 데이터 병합 작업을 자동화할 수 있다.

◆ VBA 코드: 여러 시트 데이터를 자동 병합

Sub 데이터병합()
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim pasteRow As Long

    ' 결과를 저장할 새로운 시트 생성
    Set targetSheet = ThisWorkbook.Sheets.Add
    targetSheet.Name = "통합데이터"

    pasteRow = 1

    ' 모든 시트 순회하며 데이터 병합
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "통합데이터" Then
            lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
            ws.Range("A1:B" & lastRow).Copy
            targetSheet.Cells(pasteRow, 1).PasteSpecial Paste:=xlPasteValues
            pasteRow = targetSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
        End If
    Next ws

    Application.CutCopyMode = False
    MsgBox "데이터 병합이 완료되었습니다!"
End Sub

◇ 모든 시트를 순회하며 데이터를 자동 병합하여 “통합데이터” 시트에 저장
◇ VBA를 실행하면 클릭 한 번으로 데이터가 자동으로 정리됨

★ Tip: 파일 개수가 많거나 반복적인 병합 작업이 필요할 경우 VBA 활용이 가장 효율적
⇒ VBA를 활용하면 대량 데이터 병합을 완전 자동화할 수 있다.

[ 엑셀 대량 데이터 병합 & 통합 요약 정리 ]

◇ Power Query 활용 → 여러 시트 & 파일을 자동으로 병합
◇ SUMIFS 함수 활용 → 특정 조건에 맞는 데이터 합산
◇ VBA 활용 → 클릭 한 번으로 데이터 자동 병합

⇒ 엑셀에서 대량 데이터를 효율적으로 병합하려면 Power Query와 VBA를 반드시 활용해야 한다!



#엑셀대량데이터 #엑셀파일병합 #엑셀데이터통합 #엑셀자동화 #엑셀파워쿼리 #엑셀VBA #엑셀매크로 #엑셀실무팁 #엑셀데이터분석 #엑셀파일통합