Excel & IT Info

아이엑셀러 닷컴, 엑셀러TV

Excel

엑셀에서 여러 시트의 데이터를 한 시트로 합치는 4가지 방법

권현욱(엑셀러) 2023. 12. 9. 12:00
반응형

들어가기 전에

Excel은 데이터를 효율적으로 구성하고 분석할 수 있는 강력한 도구입니다. 그러나 여러 시트에 분산된 데이터를 하나의 시트로 수집하고 통합하는 것은 상당히 번거로울 수 있습니다. 여러 시트의 데이터를 하나의 시트로 복사하는 다양한 방법을 소개합니다. 초보에서 숙련자까지 자신의 상황에 맞는 방법을 골라서 사용하세요.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴


방법 1: 가장 직설적(?)인 방법

Excel에서 여러 시트의 데이터를 한 시트로 복사하는 가장 간단한 방법은 복사 및 붙여넣기를 사용하는 것입니다. 이 방법을 사용하면 각 개별 시트에서 원하는 데이터를 수동으로 선택하고 복사하여 대상 시트에 붙여 넣을 수 있습니다.

 

(1) Excel에서 원본 시트(데이터를 복사하려는 시트)와 대상 시트(데이터를 붙여넣을 시트)를 모두 엽니다. 둘 다 화면에 표시되는지 확인하세요.

 

(2) 원본 시트로 이동하여 복사하려는 데이터가 포함된 셀 범위를 선택합니다. 원하는 셀 범위 위로 마우스를 클릭하고 드래그하면 됩니다.

 

(3) 셀이 선택되면 선택한 범위를 우클릭하고 [복사]를 선택하거나 Ctrl + C를 누릅니다.

 

(4) 대상 시트로 가서 복사한 데이터를 붙여넣을 셀을 선택합니다. 선택한 셀을 우클릭하고 [붙여넣기]를 선택하거나 Ctrl + V를 누릅니다.

 

(5) 각 원본 시트에 대해 이 과정을 반복하고 원하는 데이터를 선택 및 복사한 후 대상 시트에 붙여넣습니다.

 

이 방법을 사용할 때 서식이나 수식에 문제가 발생할 수 있다는 점에 유의하세요. 원본 시트에 대상 시트와 다른 형식이나 수식이 있는 경우 보다 안정적인 결과를 위해 통합 기능이나 VBA 매크로와 같은 다른 방법을 사용하는 것이 좋습니다.

 

방법 2: 데이터 통합 기능 사용

Excel에서 여러 시트의 데이터를 하나의 시트로 복사할 때 사용할 수 있는 또 다른 효과적인 방법은 [통합] 기능입니다. 이 기능을 사용하면 여러 시트의 데이터를 단일 시트로 결합하여 데이터를 더 쉽게 분석하고 관리할 수 있습니다.

 

(1) 통합된 데이터를 복사할 대상 시트를 선택합니다.

 

(2) [데이터] 탭 - [데이터 도구] 그룹 - [통합]을 클릭합니다.

 

(3) [통합] 대화 상자에서 데이터를 통합하는 데 사용할 함수를 선택합니다. 합계 , 평균 , 개수 등 11가지 중에서 하나를 선택할 수 있습니다.

 

(4) [추가] 버튼을 클릭하여 통합하려는 셀 범위를 추가합니다.

 

(5) 복사하려는 데이터가 포함된 각 시트의 범위를 선택하고 [추가] 버튼을 클릭하여 통합에 포함시킵니다.

 

(6) 데이터에 통합 시트에 포함하려는 머리글이나 행/열 레이블이 있는 경우 위쪽 행 및 왼쪽 열 옵션을 선택합니다.

 

(7) 통합 시트의 셀 참조를 조정하는 방법을 결정하려면 적절한 참조 옵션을 선택합니다. [확인] 버튼을 클릭하면 데이터가 통합됩니다.

 

데이터 [통합] 기능은 여러 시트에 유사한 데이터 구성이 있고 정보에 대한 중앙 집중식 보기를 생성하려는 경우에 특히 유용합니다. 이 방법을 사용하면 데이터를 자동으로 복사하고 정렬하여 시간과 노력을 절약할 수 있습니다.

방법 3: 파워 쿼리 사용하기

여러 시트의 데이터를 Excel의 한 시트로 복사하는 효율적이고 자동화된 방법을 찾고 있다면 파워 쿼리가 적합한 도구입니다. 파워 쿼리는 통합 문서 내의 여러 시트를 포함하여 다양한 원본의 데이터를 쉽게 가져오고, 변환하고, 결합할 수 있도록 하는 Excel에서 사용할 수 있는 추가 기능입니다.

 

다음 영상을 보시면 내용을 보다 명확하게 이해할 수 있습니다.

 

(영상: 엑셀러TV)

 

(1) 통합할 시트가 있는 파일을 닫습니다.

 

(2) 새로운 통합 문서(또는 시트를 통합할 문서)를 열고, [데이터] 탭 - [데이터 가져오기 및 변환] 그룹 - [데이터 가져오기] - [파일에서] - [폴더에서]를 선택합니다.

 

(3) 통합할 시트가 있는 파일이 있는 곳을 지정합니다.

 

(4) 파워 쿼리 대화상자에서 [데이터 변환] 버튼을 클릭합니다.

 

(5) [파워 쿼리 편집기] 대화상자가 나타납니다. [Content] 열을 선택하고 [홈] 탭 - [열 관리] - [열 제거] - [다른 열 제거]를 선택합니다. 그러면 [Content] 이외의 다른 열은 모두 제거됩니다.

 

(6) 이 상태에서 [열 추가] 탭 - [사용자 지정 열]을 선택합니다.

 

(7) [사용자 지정 열] 대화상자에서 [사용자 지정 열]란에 '= Excel.Workbook([Content])'을 입력하고 [확인] 버튼을 누릅니다.

 

(8) [사용자 지정] 열 오른쪽의 확장 아이콘을 클릭한 다음 [확인] 버튼을 클릭합니다.

 

(9) [사용자 지정.Data]열을 선택하고, [홈] 탭 - [열 제거] - [다른 열 제거]를 선택합니다.

 

(10) [사용자 지정.Data]열의 [확장] 아이콘을 클릭하고 [확인]을 누릅니다.

 

(11) [홈] 탭 - [변환] 그룹 - [첫 행을 머리글로 사용]을 클릭합니다.

 

(12) [상품] 열의 드롭다운 버튼을 클릭하고 불필요한 정보를 필터링하여 제거합니다.

 

(13) [홈] 탭 - [닫기 및 로드]를 클릭하면 대상 파일 내의 모든 시트가 하나의 시트에 통합됩니다.

 

파워 쿼리는 데이터 변환, 필터링, 병합 기능과 같은 다양한 고급 기능을 제공하므로 통합된 데이터를 신속하게 정리하고 구체화할 수 있습니다. 원본 시트가 변경되거나 새 데이터가 추가되면 간단한 클릭만으로 데이터를 새로 고칠 수도 있습니다. 또한 파워 쿼리의 기능을 활용하면 정기적으로 여러 시트의 데이터를 통합하고 업데이트하는 자동화된 워크플로를 만들어 시간과 노력을 절약할 수 있습니다.

 

파워 쿼리는 데이터 통합 ​​프로세스를 간소화하여 놀라울 정도로 사용자 친화적이고 효율적으로 만드는 강력한 도구입니다. 따라서 여러 시트의 데이터를 Excel의 한 시트로 복사하려는 경우 파워 쿼리를 사용해 보고 자동화된 데이터 통합의 편리함을 경험해 보세요.

 

방법 4: VBA 활용

VBA를 이용하여 여러 시트의 데이터를 Excel의 한 시트로 복사하는 프로세스를 자동화할 수 있습니다. VBA를 사용하면 단 몇 줄의 코드만으로 복잡한 작업을 수행할 수 있는 사용자 지정 매크로를 작성할 수 있습니다.

 

(1) 워크시트에서 Alt + F11을 눌러 VBA 편집기를 엽니다.

 

(2) VBA 편집기에서 [삽입] - [모듈] 메뉴를 선택하여 새 모듈을 삽입합니다.

 

(3) 새 모듈에서 다음 VBA 코드를 작성합니다.

Sub copyDataFromMultipleSheets()
    Dim ws As Worksheet
    Dim shtTarget As Worksheet
    Dim lRow As Long
    
    Set shtTarget = Workheets("Target")
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> shtTarget.Name Then
            lRow = shtTarget.Cells(shtTarget.Rows.Count, 1).End(xlUp).Row
            ws.UsedRange.Copy shtTarget.Cells(lRow + 1, 1)
        End If
    Next
End Sub

 

위 코드에서 "Target"은 데이터를 복사하려는 시트 이름으로 수정하세요. 데이터를 복사하기 전에 특정 범위를 복사하거나 필터를 적용하도록 코드를 수정할 수도 있습니다.

 

매크로를 실행하려면 워크시트 상태에서 Alt + F8을 눌러 매크로 대화 상자를 열고, 실행할 매크로를 선택한 다음 [실행]을 클릭합니다. 코드가 실행되어 통합 문서의 모든 시트(대상 시트 제외)에서 데이터를 복사하여 지정된 시트에 붙여넣습니다. 여러 시트의 최신 데이터로 통합 시트를 업데이트해야 할 때마다 이 프로세스를 반복할 수 있습니다.

 

VBA 매크로를 사용하면 여러 시트의 데이터를 Excel의 한 시트로 복사하는 작업이 크게 단순화될 수 있습니다. 시간과 노력을 절약할 수 있는 간소화되고 자동화된 솔루션을 제공합니다.