여러 시트에 데이터가 입력된 경우(ex.지점별 매출, 월별매출 등이 다른 시트 등에 입력) 이러한 다중 범위를 하나로 통합하여 데이터를 확인할 경우가 있습니다. 이럴 경우 엑셀 기능 중 피벗테이블을 사용하여 다중 범위를 합칠 수 있습니다.
피벗테이블의 기본적인 사항을 확인하시려면 이전에 작성했던 글을 확인 해 주시기 바랍니다.
엑셀 피벗테이블 사용하여 데이터 요약하기
엑셀 기능 중 피벗테이블 (pivot table) 사용하여 데이터 요약하는 방법에 대하여 설명 드리도록 하겠습니...
blog.naver.com
아래 예제에서는 4월 매출, 5월 매출, 6월 매출이 각각 다른 시트에 입력된 것을 확인 할 수 있습니다.
입력된 데이터들을 확인해 보니 각각의 월별 현황이 입력된 시트마다 상품의 순서 등이 제각각 입니다.
4월에는 컴퓨터~공기청정기까지 7종류의 상품이 판매되었고, 5월에는 TV~모니터까지 4종류의 상품만 판매되었고, 6월에는 컴퓨터~공기청정기까지 14개의 상품이 판매되면서 각 시트별 상품의 판매구성도 다릅니다.
게다가 6월에는 컴퓨터, 에어컨 등의 상품이 중복으로 등록되어 있습니다.
이렇게 각각의 시트에 입력된 데이터들을 피벗테이블을 사용하여 합쳐보도록 하겠습니다.
엑셀 피벗테이블 여러 시트(다중 범위) 합치기
1.피벗테이블/피벗차트마법사 생성하기
(빠른실행도구모음 사용자 지정 -> 기타명령 -> 리본메뉴에 없는 명령 -> 피벗테이블/피벗차트마법사 선택)
먼저 빠른실행도구모음 사용자 지정을 클릭 후 '기타명령'을 클릭(또는 엑셀 메뉴에서 파일 - 옵션 - 빠른실행도구모음 을 클릭 하셔도 됩니다) 합니다.
excel옵션 창이 열리면 '빠른실행도구모음'을 선택합니다.
이후 '명령선택'에서는 '리본메뉴에 없는 명령'을 선택 하시면 됩니다.
'피벗 테이블/피벗 차트 마법사' 를 선택 후 '추가'를 클릭하시면 다음과 같이 빠른실행도구모음에 '피벗 테이블/피벗 차트 마법사'가 추가된 것을 확인 할 수 있습니다. 이제 확인을 누릅니다.
2.피벗테이블/피벗차트마법사 로 다중 피벗테이블 생성하기
이제 상단에 '피벗테이블 마법사' 를 클릭 합니다.
'피벗 테이블/피벗 차트 마법사' 창이 열리는데 총 3단계로 나뉘어져 있습니다.
여기서 '다중 통합 범위'를 선택하고 원하는 보고서는 '피벗 테이블'을 선택후 다음을 클릭 합니다.
이제 3단계 중 2단계 인데요.
여기에서는 하나의 페이지에 다중 데이터를 통합 할 것이기 때문에 '하나의 페이지 필드 만들기'를 선택 후 다음을 클릭 합니다.
이제 3단계 입니다.
통합할 워크시트 범위를 지정하는 것인데요.
범위는 각각의 4,5,6판매현황이 입력된 데이터 들의 전체 범위를 각각 지정해 주시면 됩니다.
먼저 4월 시트에서 범위를 다음과 같이 B3:G10까지 범위 지정(셀고정은 자동)하고 추가 버튼을 눌러 주시면 '모든 범위'창에 4월 판매현황의 범위가 추가 됩니다.
그대로 5월 시트를 클릭 후 4월과 똑같은 방법으로 범위 지정 후 추가를 클릭 합니다.
6월 시트까지 범위지정이 완료 되었다면 다음을 클릭 합니다.
마무리 단계 입니다.
피벗 테이블 보고서 작성 위치를 '새 워크시트'에 할건지 '기존 워크시트'에 할건지를 선택할 것인데, 필요에 따라 선택 하시면 되고요.
'새 워크시트'를 선택 후 마침을 클릭 합니다.
다중 범위를 통합한 피벗테이블이 완성 되어 상품별 지점별 매출의 총 합계가 피벗테이블에 표시 되었습니다.
우측 피벗테이블 필드를 잠깐 살펴보면
'행'은 상품이 입력된 데이터입니다.
'세로 막대형 (열)'은 '강동점, 강북점, 강서점, 송파점 등' 지점이 입력된 데이터 입니다.
'값'은 매출액 입니다.
3.월별 매출 구분(시트 구분)하여 피벗테이블에 적용하기
최초 다중 피벗테이블 생성시 데이터는 다음과 같이 따로 시트 구분(월별 매출 구분)없이 상품별, 매장별로 구분이 되어 있는 상태입니다. 시트구분(월별 매출 구분)은 상단에 '페이지1'에서 필터로 선택하도록 되어 있습니다.
이제 월별 매출현황이 구분될 수 있도록 피벗테이블을 변경해 보도록 하겠습니다.
'피벗 테이블 필드'에서 하단의 '필터'에 있는 '페이지1'을 아래 '행'으로 끌어 내립니다.
'페이지1'을 필터에 두지 않고 '행'레이블에 데이터를 표시하기 위해서 이동시키는 것입니다.
행레이블로 이동시 '페이지1'이 제일 상단에 위치해야 합니다. 그래야 월별매출액 기준으로 정렬이 됩니다.
만약 상품이 입력된 '행'이 '페이지1'보다 위로 올라온다면 상품명을 기준으로 정렬이 될 것입니다.
페이지1을 행레이블로 이동시켰더니 다음과 같이 항목1,2,3이 생성 되었습니다.
이 항목1,2,3이 4월 5월 6월 매출 입니다.
항목1을 4월로, 항목2를 5월로, 항목3을 6월로 해당셀에 직접 입력하여 변경 합니다.
항목1,2,3이 4월, 5월, 6월로 변경 되었습니다.
마지막으로 동영상으로도 확인 해보시기 바랍니다.