엑셀 통합 기능으로 여러 시트 데이터 합치기
통합 기능으로 여러 시트나 통합 문서의 목록을 요약 목록 하나로 합치고, 레이블이나 위치를 기준으로 행을 맞추는 방법을 배워요.
- 난이도
- 중급
- Time
- 10분 분량
- 수정:
- 적용 대상:
- Microsoft 365용 Excel, Excel 2021, Excel 2019, Mac용 Excel
배울 내용
- 데이터 › 통합으로 두 개 이상의 목록을 요약 목록 하나로 합치기
- 목록의 순서가 달라도 레이블을 기준으로 행과 열 맞추기
- 합계 말고 평균이나 개수 같은 다른 요약 함수 고르기
- 통합 결과를 업데이트하고, 피벗 테이블이나 파워 쿼리가 더 나은 경우 판단하기
통합(Consolidate)은 같은 종류의 데이터가 담긴 여러 목록을 더하거나 평균을 내거나 개수를 세서 요약 목록 하나로 만들어 줘요. 매장마다, 반마다, 달마다 시트가 따로 있는데 합계를 한곳에서 봐야 할 때 가장 빠른 방법이에요. 엑셀이 레이블을 보고 행을 맞춰 주기 때문에 목록의 순서가 달라도, 들어 있는 항목이 달라도 괜찮아요.
예시로 동부와 서부 두 매장 시트를 사용해요. 시트마다 품목별 판매 수량과 매출이 있는데, 서부 시트는 품목 순서가 다르고 동부에서 팔지 않는 품목이 하나 더 있어요.
| A | B | C | |
|---|---|---|---|
| 1 | 품목 | 수량 | 매출 |
| 2 | 공책 | 120 | 360 |
| 3 | 볼펜 | 300 | 450 |
| 4 | 연필 | 250 | 125 |
| 5 | 테이프 | 80 | 160 |
| A | B | C | |
|---|---|---|---|
| 1 | 품목 | 수량 | 매출 |
| 2 | 볼펜 | 280 | 420 |
| 3 | 공책 | 90 | 270 |
| 4 | 테이프 | 60 | 120 |
| 5 | 연필 | 200 | 100 |
| 6 | 지우개 | 75 | 75 |
기능 위치
통합은 데이터 탭의 데이터 도구(Data Tools) 그룹에 있어요. 데이터 then 데이터 도구 then 통합이에요. 바로 가기 키나 마우스 오른쪽 단추 메뉴에는 없어요.
레이블을 기준으로 두 시트 합치기
목표는 요약 시트에 품목마다 한 행씩, 두 매장의 수량과 매출 합계를 만드는 거예요.
요약을 시작할 셀을 클릭하세요
요약 시트로 가서 A1을 클릭하세요. 엑셀은 이 셀부터 아래쪽과 오른쪽으로 결과를 쓰기 때문에, 그 영역이 비어 있어야 해요.
통합을 열고 함수를 고르세요
데이터 then 데이터 도구 then 통합으로 가세요. 통합 대화 상자에서 함수는 합계 그대로 두세요.
동부 범위를 추가하세요
참조 상자를 클릭하고, 동부 시트 탭을 클릭한 다음 제목과 품목 이름까지 포함해서 A1:C5를 선택하세요. 참조 상자에 시트 이름과 범위가 들어가요. 추가를 클릭하면 이 범위가 모든 참조 영역 목록으로 옮겨져요.
서부 범위를 추가하세요
서부 시트 탭을 클릭하고 A1:C6을 선택한 다음 다시 추가를 클릭하세요. 서부는 동부보다 한 행이 더 많지만 괜찮아요. 참조마다 크기가 달라도 돼요.
레이블을 쓰도록 설정하세요
사용할 레이블에서 첫 행과 왼쪽 열을 체크하세요. 그래야 엑셀이 동부의 "볼펜"과 서부의 "볼펜"을, 각 목록에서 어디에 있든 서로 맞춰요. 확인을 클릭하세요.
두 범위를 합계로 합치고, 품목 이름(왼쪽 열)으로 행을, 제목(첫 행)으로 열을 맞춰요. 결과를 확인하세요
품목마다 한 번씩만 나오고, 두 매장의 값이 더해졌어요. 공책은 동부 120개와 서부 90개를 더해 210, 매출은 360과 270을 더해 630이에요. 지우개는 서부에만 있어서 서부의 값인 75와 75가 그대로 나와요.
A B C 1 수량 매출 2 공책 210 630 3 볼펜 580 870 4 연필 450 225 5 테이프 140 280 6 지우개 75 75 통합한 뒤의 요약 시트예요. 모든 품목이 한 번씩 나오고, 지우개(초록색)는 서부에서만 왔어요. A1은 비어 있어요.
두 가지를 눈여겨보세요. 엑셀은 왼쪽 위 셀인 A1을 비워 둬요. 거기에는 품목을 직접 입력하세요. 그리고 결과는 수식이 아니라 그냥 숫자라서, 매장 시트가 바뀌어도 따라 바뀌지 않아요(아래 "통합 결과 업데이트하기"를 보세요).
위치를 기준으로 통합하기
첫 행이나 왼쪽 열을 체크하지 않으면 엑셀은 위치를 기준으로 통합해요. 모든 참조의 첫 번째 셀끼리, 두 번째 셀끼리 더하는 식이에요. 선택한 범위의 레이블은 무시되니, 숫자만 선택하고 레이블은 요약 시트에 직접 입력하세요.
이 방법은 모든 목록의 구조가 완전히 같을 때만 통해요. 품목도, 순서도, 열도 같아야 해요. 서식 파일 하나로 만든 월별 시트가 대표적인 경우예요. 순서가 한 군데라도 다르면 위치 기준 통합은 아무 경고 없이 엉뚱한 행끼리 더해요. 그래서 목록에 레이블이 있다면 레이블 기준이 더 안전해요.
대화 상자의 옵션
| 옵션 | 하는 일 |
|---|---|
| 함수 | 값을 합치는 방법이에요. 합계, 개수, 평균, 숫자 개수 말고도 최댓값, 최솟값, 곱, 표준 편차, 분산을 구하는 함수가 있어요. 개수는 COUNTA처럼 비어 있지 않은 값을 모두 세고, 숫자 개수는 COUNT처럼 숫자만 세요. |
| 참조 | 추가할 범위예요. 마우스로 선택하거나 직접 입력해요. |
| 찾아보기 | 열려 있지 않은 통합 문서에서 범위를 골라요. 보통은 다른 통합 문서를 먼저 열어 두고 거기서 범위를 선택하는 편이 쉬워요. |
| 추가 / 삭제 | 지금 참조를 모든 참조 영역에 넣거나, 목록에서 선택한 참조를 빼요. |
| 첫 행 / 왼쪽 열 | 열은 제목으로, 행은 레이블로 맞춰요. 일부 목록에만 있는 레이블도 자기 행이나 열을 따로 받아요. |
| 원본 데이터에 연결 | 숫자 대신 원본 셀에 연결되는 수식을 써요. 다음 절에서 설명해요. |
통합 결과 업데이트하기
일반적인 통합 결과는 그 순간의 스냅숏이에요. 원본 시트가 바뀌면 다시 실행하세요. 같은 왼쪽 위 셀을 선택하고 통합을 연 다음 확인을 클릭하면 돼요. 엑셀이 이 통합 문서에서 추가한 참조를 기억하고 있어서 범위를 다시 고를 필요가 없어요. 새 매장을 더하려면 그 범위를 선택하고 확인 전에 추가를 클릭하세요.
원본 데이터에 연결을 체크하면 엑셀은 숫자 대신 수식을 써요. 품목마다 원본 셀에 연결된 세부 행을 원본 수만큼 숨겨서 넣고, 그 행들을 더하는 합계 행을 만든 다음, 개요로 묶어요. 행 왼쪽의 + 단추를 누르면 각 숫자가 어디서 왔는지 볼 수 있어요. 이제 원본 값이 바뀌면 합계도 바뀌어요. 다만 두 가지 한계가 있어요. 원본이 다른 시트나 다른 통합 문서에 있을 때만 연결할 수 있고, 나중에 원본 목록에 새로 추가한 품목은 다시 통합하기 전까지 나타나지 않아요.
실제 활용 사례
세 부서의 경비 합치기
마케팅, 영업, 지원 부서가 각자 항목별 경비 시트를 관리하는데, 모든 부서에 모든 항목이 있는 건 아니에요. 합계를 고르고 레이블 상자 두 개를 모두 체크한 다음, 시트마다 항목과 금액 제목을 포함해 A1:B4를 추가하세요.
| A | B | |
|---|---|---|
| 1 | 금액 | |
| 2 | 출장비 | 3,800 |
| 3 | 소프트웨어 | 750 |
| 4 | 소모품 | 390 |
| 5 | 교육비 | 1,300 |
출장비는 3,800이에요(마케팅 1,200, 영업 2,600). 모든 부서가 쓰는 소모품은 180 + 120 + 90 = 390이에요. 항목이 몇 개의 시트에 있었든 결과에는 한 번만 나와요.
학년 전체 봉사 시간
세 반이 똑같은 구조의 시트에 봉사 시간을 기록해요. A2:A5에 9월부터 12월까지, B2:B5에 시간이 있어요. 구조가 같으니 위치를 기준으로 통합하세요. 요약 시트에 월을 입력하고 B2를 클릭한 다음, 레이블 상자는 체크하지 않고 반마다 B2:B5를 추가하면 돼요.
| A | B | |
|---|---|---|
| 1 | 월 | 시간 |
| 2 | 9월 | 30 |
| 3 | 10월 | 48 |
| 4 | 11월 | 46 |
| 5 | 12월 | 27 |
10월은 18 + 14 + 16 = 48시간이에요. 한 반이라도 월 순서를 다르게 정렬했다면 이 합계는 틀려요. 위치 기준 통합을 쓰기 전에는 시트를 꼭 확인하세요.
우리 집 평균 전기 요금
해마다 시트를 하나씩 만들어 A2:B7에 1월부터 6월까지 월별 전기 요금을 적어 두었어요. 요약 시트의 1행에 제목을 직접 입력하고 A2를 클릭하세요. 함수를 평균으로 바꾸고 왼쪽 열만 체크한 다음, 해마다 A2:B7을 추가하세요.
| A | B | |
|---|---|---|
| 1 | 월 | 평균 요금 |
| 2 | 1월 | 66 |
| 3 | 2월 | 59 |
| 4 | 3월 | 53 |
| 5 | 4월 | 47 |
| 6 | 5월 | 42 |
| 7 | 6월 | 60 |
1월 평균은 (62 + 66 + 70) ÷ 3 = 66이고, 5월은 42밖에 안 돼요. 왼쪽 열을 체크했기 때문에 월 이름으로 행을 맞춰요. 그래서 어느 해의 시트가 월 순서가 달라도 평균이 제대로 나와요.
통합, 피벗 테이블, 파워 쿼리 중 무엇을 쓸까요?
| 도구 | 이럴 때 좋아요 |
|---|---|
| 통합 | 목록 몇 개를 한 번 요약할 때, 특히 시트가 따로 있고 항목이 서로 다를 때 |
| 3차원 참조 수식 | 모든 시트의 구조가 똑같고, 합계가 저절로 업데이트되길 원할 때 |
| 피벗 테이블 | 데이터가 긴 목록 하나이거나 그렇게 만들 수 있고, 각 행이 어디서 왔는지 알려 주는 열이 있을 때 |
| 파워 쿼리 | 매주나 매달 같은 일을 반복할 때. 목록을 한 번 이어 붙여 두고 새로 고침만 하면 돼요 |
자주 하는 실수
- 목록이 다른데 레이블 상자를 체크하지 않기. 그러면 엑셀은 2행은 2행끼리 더해서 서부의 볼펜이 동부의 공책에 더해져요. 결과는 그럴듯해 보이지만 틀렸어요. 첫 행과 왼쪽 열을 체크하거나, 모든 목록의 구조를 똑같이 맞추세요.
- 거의 같은 레이블. "볼펜"과 "볼펜 "(끝에 공백)처럼 조금만 달라도 서로 다른 행이 돼요. 원본 레이블을 고치고(TRIM이 도움이 돼요) 다시 통합하세요.
- 합계 행까지 참조에 넣기. 원본 시트의 선택 범위 안에 합계 행이 있으면 그것까지 더해져서 요약이 두 번 계산돼요. 품목 행과 제목만 선택하세요.
- 요약이 저절로 바뀌길 기대하기. 원본 데이터에 연결을 체크하지 않았다면 결과는 그냥 값이에요. 원본이 바뀌면 통합을 다시 실행하세요.
- 결과가 들어갈 공간이 부족하기. 엑셀은 선택한 셀의 아래쪽과 오른쪽에 있는 셀을 덮어써요. 빈 시트나 빈 영역에서 요약을 시작하세요.
연습
연습
두 매장 시트의 요약 만들기
- 새 통합 문서에서 시트 두 개의 이름을 동부와 서부로 바꾸고, 이 페이지 맨 위의 두 목록을 입력하세요. 세 번째 시트는 요약으로 이름을 붙이세요.
- 요약 시트에서 A1을 클릭하고 데이터 then 데이터 도구 then 통합을 연 다음, 동부의 A1:C5와 서부의 A1:C6을 추가하세요.
- 첫 행과 왼쪽 열을 체크하고 확인을 클릭하세요. 볼펜의 수량이 580, 매출이 870인지 확인하세요.
- 서부 시트에서 볼펜 수량을 280에서 300으로 바꾸세요. A1에서 통합을 다시 실행하고 볼펜이 600이 되었는지 확인하세요.
- 실행 취소한 다음, 레이블 상자를 둘 다 체크하지 않고 같은 통합을 해 보세요. 3단계의 결과와 비교하고 왜 다른지 생각해 보세요.
관련 자료
도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com