엑셀에서 모든 시트를 어떻게 합치나요?

엑셀에서 여러 시트의 데이터를 하나의 시트로 합치는 방법은 다양하며, 데이터의 형태와 목표에 따라 최적의 방법이 달라집니다. 단순히 모든 시트의 내용을 한 시트에 나열하는 것부터, 특정 조건에 맞는 데이터만 추출하여 합치는 것까지, 다양한 상황에 맞는 방법을 알아보겠습니다.

1. 데이터 탭의 ‘통합’ 기능 활용 (동일한 형태의 데이터)

데이터 탭의 ‘통합’ 기능은 각 시트의 데이터 구조가 동일하고, 같은 위치에 같은 종류의 데이터가 있는 경우 유용합니다. 예를 들어, 각 시트에 지역별 매출 데이터가 같은 형태로 정리되어 있을 때, 전체 매출 합계를 구하고자 한다면 이 기능이 적합합니다.

• 통합할 시트들의 첫 번째 셀 위치와 데이터 범위가 동일해야 합니다.
• ‘데이터’ 탭의 ‘데이터 도구’ 그룹에서 ‘통합’을 클릭합니다.
• ‘함수’ 상자에서 원하는 함수(합계, 평균, 개수 등)를 선택합니다.
• ‘참조’ 상자에 통합할 시트 범위를 입력합니다. 시트 이름을 직접 입력하거나, ‘축소’ 버튼을 눌러 마우스로 시트 범위를 선택할 수 있습니다. 여러 시트를 선택할 때는 Shift 키 또는 Ctrl 키를 활용합니다.
• ‘맨 위 행’과 ‘왼쪽 열’ 체크박스를 선택하여 레이블을 포함할지 여부를 결정합니다.
• ‘새로운 워크시트에 만들기’ 또는 ‘결과를 현재 워크시트에 표시’를 선택하여 통합 결과를 표시할 위치를 지정합니다.

2. Power Query (Get & Transform) 활용 (복잡한 데이터 통합)

Power Query는 엑셀 2016 이상 버전에서 제공되는 강력한 데이터 가져오기 및 변환 도구입니다. 다양한 소스의 데이터를 가져와서 정리하고 변환한 후, 엑셀 시트로 불러올 수 있습니다. 각 시트의 데이터 구조가 다르거나, 특정 조건에 맞는 데이터만 추출하여 합치고 싶을 때 유용합니다.

• ‘데이터’ 탭의 ‘데이터 가져오기 및 변환’ 그룹에서 ‘새 쿼리’를 클릭하고 ‘파일에서’ -> ‘Excel 통합 문서에서’를 선택합니다.
• 통합할 엑셀 파일을 선택합니다.
• ‘탐색기’ 창에서 통합할 시트들을 선택하고 ‘변환 데이터’를 클릭합니다.
• Power Query 편집기에서 ‘추가’ 탭의 ‘테이블 추가’를 사용하여 여러 시트의 데이터를 하나의 테이블로 합칠 수 있습니다.
• 필요에 따라 데이터 필터링, 열 추가/삭제, 데이터 형식 변환 등의 작업을 수행합니다.
• ‘홈’ 탭의 ‘닫기 및 로드’를 클릭하여 변환된 데이터를 엑셀 시트로 불러옵니다.

3. VBA (매크로) 활용 (반복 작업 자동화)

시트가 매우 많거나, 복잡한 조건에 따라 데이터를 통합해야 하는 경우 VBA 매크로를 사용하여 작업을 자동화할 수 있습니다. VBA는 프로그래밍 지식이 필요하지만, 한 번 매크로를 작성해 놓으면 반복적인 작업을 효율적으로 처리할 수 있습니다.

각 시트의 데이터 구조와 통합 조건에 따라 VBA 코드를 작성해야 하므로, 구체적인 예시는 생략합니다. 하지만 인터넷 검색을 통해 다양한 VBA 코드 예시를 찾아 참고할 수 있습니다.

4. 3D 수식 활용 (특정 셀 범위 합계)

각 시트에 같은 위치의 셀 범위에 대한 합계, 평균 등을 구하고자 할 때 3D 수식을 사용할 수 있습니다. 예를 들어 ‘Sheet1:Sheet3’!A1:B2 와 같이 입력하면 해당 시트들의 A1:B2 셀 범위에 대한 계산을 수행합니다. 이 방법은 빠르고 간편하지만, 모든 시트의 데이터를 하나의 시트에 나열하는 것은 아니라는 점에 유의해야 합니다.

위의 방법들을 상황에 맞게 활용하면 엑셀에서 여러 시트의 데이터를 효율적으로 통합하고 관리할 수 있습니다. 각 방법의 장단점을 이해하고, 데이터의 특징과 목표에 따라 최적의 방법을 선택하는 것이 중요합니다.

#시트합치기 #엑셀팁 #엑셀합치기