여러 워크시트의 데이터 통합

개별 워크시트의 결과를 요약하고 보고하려면 각 시트의 데이터를 master 워크시트로 통합할 수 있습니다. 시트는 master 워크시트와 동일한 통합 문서 또는 다른 통합 문서에 있을 수 있습니다. 데이터를 통합할 때 필요에 따라 더 쉽게 업데이트하고 집계할 수 있도록 데이터를 어셈블합니다.

예를 들어 각 지사의 경비 워크시트가 있는 경우 통합을 사용하여 이러한 수치를 마스터 회사 경비 워크시트에 통합할 수 있습니다. 이 마스터 워크시트에는 전체 엔터프라이즈의 판매액 합계 및 평균, 현재 재고 수준 및 최고 판매 제품도 포함될 수 있습니다.

팁: 데이터를 자주 통합하는 경우 일관된 레이아웃을 사용하는 워크시트 템플릿에서 새 워크시트를 만드는 데 도움이 될 수 있습니다. 템플릿에 대한 자세한 내용은 템플릿 만들기를 참조하세요. Excel 테이블을 사용하여 템플릿을 설정하는 데 이상적인 시기이기도 합니다.

위치 또는 범주별로 데이터를 통합하는 방법에는 두 가지가 있습니다.

위치별 통합: 원본 영역의 데이터는 순서가 같으며 동일한 레이블을 사용합니다. 동일한 서식 파일에서 만든 부서별 예산 워크시트 등 일련의 워크시트 데이터를 통합하려면 이 방법을 사용합니다.

범주별 통합: 원본 영역의 데이터가 동일한 순서로 정렬되어 있지는 않지만 동일한 레이블을 사용하는 경우입니다. 레이아웃은 서로 다르지만 동일한 데이터 레이블을 사용하는 일련의 워크시트 데이터를 통합하려면 이 방법을 사용합니다.

범주별 데이터 통합은 피벗 테이블을 만드는 것과 비슷합니다. 하지만 피벗 테이블을 사용하면 범주를 손쉽게 재구성할 수 있습니다. 범주별로 보다 유연한 통합이 필요한 경우 피벗 테이블을 만드는 것이 좋습니다.

참고: 이 문서의 예제는 Excel 2016 사용하여 만들어졌습니다. 다른 버전의 Excel을 사용하는 경우 보기가 다를 수 있지만 단계는 동일합니다.

다음 단계에 따라 여러 워크시트를 master 워크시트에 통합합니다.

아직 설정하지 않은 경우 다음을 수행하여 각 구성 요소 시트에 데이터를 설정합니다.
- 각 데이터 범위가 목록 형식인지 확인합니다. 각 열에는 첫 번째 행에 레이블(헤더)이 있어야 하며 유사한 데이터가 포함되어야 합니다. 목록의 아무 곳에도 빈 행이나 열이 없어야 합니다.
- 각 범위를 별도의 워크시트에 배치하지만 데이터를 통합하려는 master 워크시트에 아무 것도 입력하지 마세요. Excel에서 이 작업을 수행합니다.
- 각 범위의 레이아웃이 같은지 확인합니다.
마스터 워크시트에서 통합된 데이터를 표시할 영역의 왼쪽 위 셀을 클릭합니다.

참고: master 워크시트에서 기존 데이터를 덮어쓰지 않도록 하려면 통합 데이터에 대해 이 셀의 오른쪽과 아래에 충분한 셀을 남겨 두어야 합니다.
데이터 > 통합을 클릭합니다(데이터 도구 그룹에서).
함수 상자에서 데이터 통합에 사용할 요약 함수를 클릭합니다. 기본 함수는 SUM입니다.

다음은 세 가지 워크시트 범위가 선택된 예제입니다.
데이터를 선택합니다.

그런 다음 참조 상자에서 축소 단추를 클릭하여 패널을 축소하고 워크시트에서 데이터를 선택합니다.

통합할 데이터가 들어 있는 워크시트를 클릭하고 데이터를 선택한 다음 오른쪽에 있는 대화 상자 확대 단추를 클릭하여 통합 대화 상자로 돌아갑니다.

통합해야 하는 데이터가 포함된 워크시트가 다른 통합 문서에 있는 경우 찾아보기를 클릭하여 해당 통합 문서를 찾습니다. 확인을 찾아 클릭한 후 Excel에서 참조 상자에 파일 경로를 입력하고 해당 경로에 느낌표를 추가합니다. 그런 다음 다른 데이터를 계속 선택할 수 있습니다.

다음은 세 개의 워크시트 범위를 선택한 예제입니다.
통합 팝업 창에서 추가를 클릭합니다. 이 작업을 반복하여 통합하는 모든 범위를 추가합니다.
자동 및 수동 업데이트: 원본 데이터가 변경되면 Excel에서 통합 테이블을 자동으로 업데이트하려면 원본 데이터에 대한 링크 만들기 상자를 검사. 이 상자를 선택하지 않은 상태로 유지하면 통합을 수동으로 업데이트할 수 있습니다.
참고 사항:
- 원본 및 대상 영역이 동일한 시트에 있는 경우에는 연결을 만들 수 없습니다.
- 범위의 범위를 변경하거나 범위를 바꿔야 하는 경우 통합 팝업에서 범위를 클릭하고 위의 단계를 사용하여 업데이트합니다. 이 경우 새 범위 참조가 만들어지므로 다시 통합하기 전에 이전 참조를 삭제해야 합니다. 이전 참조를 선택하고 Delete 키를 누릅니다.
확인을 클릭하면 통합이 생성됩니다. 필요에 따라 서식을 적용할 수 있습니다. 통합을 다시 실행하지 않는 한 한 번만 서식을 지정하면 됩니다.
- 다른 원본 영역의 레이블과 일치하지 않는 레이블이 있는 경우 통합 시 별도의 행이나 열이 만들어집니다.
- 통합하지 않으려는 모든 범주에 하나의 원본 범위에만 표시되는 고유한 레이블이 있는지 확인합니다.

통합할 데이터가 다른 워크시트의 다른 셀에 있는 경우:

다른 워크시트에 대한 셀 참조가 워크시트마다 하나씩 포함된 수식을 입력합니다. 예를 들어 매출(B4 셀), HR(F5 셀) 및 마케팅(B9 셀)이라는 워크시트를 마스터 워크시트의 A2 셀에 통합하려면 다음과 같이 입력합니다.

Excel 다중 시트 수식 참조

팁: 셀 참조(예: Sales!)를 입력하려면 B4 - 입력하지 않고 수식에 참조가 필요한 지점까지 수식을 입력한 다음 워크시트 탭을 클릭한 다음 셀을 클릭합니다. Excel에서 시트 이름과 셀 주소를 완성합니다. 참고: 이러한 경우 수식은 실수로 잘못된 셀을 선택하는 것이 매우 쉽기 때문에 오류가 발생할 수 있습니다. 복잡한 수식을 입력한 후 실수를 발견하기가 어려울 수도 있습니다.

통합할 데이터가 다른 워크시트의 동일한 셀에 있는 경우:

워크시트 범위 이름에 대한 참조를 사용하는 3차원 참조가 포함된 수식을 입력합니다. 예를 들어 영업에서 마케팅을 포함한 셀 A2의 데이터를 통합하려면 master 워크시트의 셀 E5에 다음을 입력합니다.

Excel 3D 시트 참조 수식