엑셀 피벗테이블 만들기 방법에 관해 알아 보는 글입니다. 초보자도 이 글을 통해 쉽게 엑셀 피벗 테이블를 만들 수 있습니다. 피벗테이블이란 무엇인지부터 피벗테이블을 활용 하는 방법 까지 모두 알아볼게요.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
피벗테이블이란?
피벗 테이블에서 피벗(Pivot)이란 전환, 변환이라는 의미를 가지고 있고요. 테이블은 표를 의미합니다.
아래 사진에서 왼쪽에 있는 것이 원본 데이터입니다. 그리고 오른쪽에 있는 것이 원본 데이터를 토대로 만든 피벗 테이블 입니다. 여기서 날짜별, 과일명 별 판매 금액 합계를 보기 쉽게 피벗 테이블로 바꾼 겁니다.
이처럼 피벗 테이블을 이용하면 원본 데이터에서 우리가 원하는 부분만 열 또는 행으로 위치 시켜서 데이터를 더 보기 쉽게 표현 할 수 있습니다.
피벗 테이블에서는 기본적으로 숫자 데이터를 합계 결과값으로 내 줍니다. 숫자가 아닌 데이터는 합계 대신 개수로 결과 값을 내 줍니다.
따라서 합계를 내야 하는 경우 피벗 테이블을 사용하면 따로 함수를 사용하지 않고 편하게 데이터 결과 값을 도출 할 수 있습니다.
엑셀 피벗테이블 만들기 방법 – 피벗 테이블 만들기
엑셀 피벗 테이블 만들기 방법을 알아 보겠습니다. 초보자도 따라할 수 있으니 천천히 따라와 주세요.
우선 원본 데이터가 준비 되어 있어야 합니다. 그런데 원본 데이터를 만들 때 지켜야 될 사항이 세 가지가 있습니다. 지켜야 할 3가지 사항은 아래와 같습니다.
- 데이터 첫줄 제목 입력
- 중간 집계행, 빈 열, 빈 행 ×
- 병합셀 ×
즉 데이터의 첫 줄에는 반드시 제목이 나와 있어야 합니다. 그리고 원본 데이터 중간에 집계를 하는 행 또는 빈 열, 빈 행이 있으면 안 됩니다. 또 병합 되어 있는 셀이 있으면 안 됩니다.
이점에 유의 해서 원본 데이터를 준비해주시고요. 만약 원본 데이터가 없다면 상단의 엑셀 연습 파일을 다운로드 받아 주세요.
데이터 중 아무 셀이나 클릭을 한 후 삽입 – 피벗 테이블 버튼을 클릭 합니다.
그 후 범위를 선택해야 되는데요. 범위 부분은 엑셀에서 자동으로 지정을 해 줍니다. 하지만 범위가 맞는지 한번 더 확인을 해 주세요.
그리고 피벗 테이블을 배치 할 위치를 선택해야 되는데요. 새 워크시트에 배치를 하는 것이 넓어서 보기가 편합니다. 따라서 새 워크시트를 선택한 후 확인 버튼을 눌러 주세요.
엑셀 피벗테이블 만들기 방법 – 피벗테이블 수정하기
그럼 아래 사진처럼 피벗 테이블이 만들어집니다. 근데 이렇게 봐서는 피벗 테이블인지 잘 모르겠습니다. 따라서 지금부터 우리가 피벗 테이블에서 몇 가지 수정을 해야 합니다.
피벗 테이블을 만들면 좌측에 피벗 테이블 필드가 나옵니다. 여기서 ‘필드’ 란 원본 데이터의 제목 부분을 말합니다.
즉 과일명, 판매금액, 단가, 판매개수 등등 원본데이터 맨 위에 있던 제목 부분을 피벗 테이블 필드 라고 합니다. (정확하게는 데이터가 입력되어 있는 전체 열 부분을 말합니다.)
그리고 각 제목이 하나의 필드가 됩니다. 예를 들어 ‘과일명’이 하나의 필드가 되고, ‘단가’가 하나의 필드가 되는 것입니다.
아래 사진의 우측 부분에서 각각의 필드를 드래그를 해서 열, 행, 값 칸으로 옮겨 주면 됩니다. 그럼 아래 사진의 좌측처럼 데이터가 지정한 열, 행에 맞게 나옵니다.
위 사진의 경우 ‘날짜’ 별, ‘과일명’ 별, ‘판매 금액’을 각각 행, 열, 값에 나타낸 것입니다. 그리고 판매금액의 합계도 자동으로 구해진 것을 확인할 수 있습니다.
만약 행, 열 값을 바꾸고 싶다면 드래그를 해서 이동을 시킬 수도 있습니다. 또는 필드를 없앨 수도 있습니다. 자신이 원하는 대로 바꿀 수 있습니다.
이번에는 하위 필드를 추가하는 방법을 알아 보겠습니다.
행 부분에 현재 ‘날짜’ 필드가 들어가 있는데요 ‘날짜’ 필드 아래에 ‘고객사명’ 필드를 옮겨 줍니다. 그럼 아래 사진 왼쪽처럼 날짜 아래에 ‘고객사명’이 나오는 것을 확인할 수 있습니다.
필터 부분에 필드를 추가 하면 아래 사진의 왼쪽처럼 우리가 지정한 필드에 필터를 적용 할 수 있습니다. ‘고객사 명’ 필드를 필터 칸에 옮겼더니 아래 사진처럼 ‘고객사명’을 선택 할 수 있는 필터가 나왔습니다.
여기서 필터 아래 화살표를 눌러서 자신이 원하는 ‘고객사명’ 데이터만 보이게 할 수 있습니다.
피벗 테이블에서는 기본적으로 합계를 자동으로 나타내 준다고 했었는데요. 합계가 아닌 평균, 최소값, 최대값을 나타내는 방법도 있습니다.
아래 사진처럼 ‘값’ 칸의 ‘합계 : 판매금액’ 옆 아래 화살표를 클릭해 줍니다. 그 후 ‘값 필드 설정’ 을 클릭 합니다.
그럼 아래 사진과 같은 창이 하나 뜨는데요. 여기서 평균을 선택한 후 확인 버튼을 눌러 주면 됩니다. 그럼 합계가 아닌 평균 값이 나옵니다.
숫자에서 천 단위로 콤마가 나오게 하면 조금 더 보기가 편합니다.
피벗 테이블에서 아무 셀이나 클릭을 합니다. 그 후 오른쪽 마우스 버튼을 클릭하고 ‘필드 표시 형식’을 클릭 합니다.
그럼 아래 사진처럼 ‘표시 형식’ 창이 나옵니다. 여기서 ‘숫자’ 를 클릭하고 ‘1000 단위 구분 기호(,) 사용’ 에 체크 표시를 하고 원하는 음수 스타일을 선택한 후 확인 버튼을 누릅니다.
그럼 숫자의 천단위 마다 쉼표 표시가 나옵니다.
엑셀 피벗테이블 만들기 방법 – 피벗테이블 주의점
1.새로고침 하기
피벗 테이블을 만들 때 주의해야 할 점 2가지가 있습니다.
첫 번째로 새로운 데이터가 입력이 될 때마다 새로고침을 해줘야 한다는 것입니다. 새로고침을 하지 않으면 새로운 데이터가 반영이 되지 않기 때문입니다.
따라서 아래 사진처럼 피벗 테이블의 아무 셀이나 클릭을 한 후 오른쪽 마우스 버튼을 클릭 합니다. 그 후 새로고침 글자를 클릭하면 됩니다. 그럼 자동으로 피벗 테이블에도 원본데이터의 내용이 적용됩니다.
2.새 데이터 입력 시 범위 넓게 지정하기
새 데이터를 입력 해야 하는 경우라면 처음부터 범위를 더 넓게 지정해 주는 것이 좋습니다.
범위를 데이터 양만큼만 지정하면 새 데이터 입력 시 피벗테이블에 자동 반영이 안 되기 때문입니다.
그러기 위해 아래 사진처럼 피벗 테이블 범위를 지정할때 넓게 범위를 지정해 주시면 됩니다.
엑셀 피벗테이블 만들기 방법 – 레이아웃 변경하기
피벗 테이블을 만든 후 레이아웃을 변경해 주면 훨씬 더 보기가 편합니다. 디자인으로 들어가서 보고서 레이아웃을 클릭 합니다. 그 후 테이블 형식으로 표시를 클릭 합니다.
피벗 테이블을 만들면 기본적으로 ‘압축 형식으로 표시’로 설정이 되는데요. 실무에서 많이 사용하는 레이아웃 형식이 ‘테이블 형식으로 표시’입니다. 때문에 ‘테이블 형식으로 표시’를 선택 해 주세요.
이번에는 피벗 테이블 중간 중간에 나와있는 부분 합계를 없애서 조금 더 깔끔하게 보이게 하겠습니다.
이번에도 디자인 – 부분합으로 들어갑니다. 그후 ‘부분합 표시 안함’을 클릭하면 아래 사진처럼 부분합이 안 나옵니다.
피벗 테이블을 만들면 기본적으로 열과 행에 총 합계가 모두 표시 되는데요.
열 부분에만 총 합계를 표시 하기 위해서 아래 사진처럼 디자인 – 총 합계 – 행의 총 합계만 설정을 클릭 해 줍니다. 그럼 행 부분에만 총 합계가 나옵니다.
엑셀 피벗테이블 만들기 방법 – 피벗 테이블 깔끔하게 만들기
이번에는 피벗 테이블을 조금 더 깔끔하게 만드는 방법을 알아 보겠습니다.
피벗 테이블을 보시면 + – 단추가 나와 있습니다. 이 단추를 없애기 위해서 피벗 테이블 분석 메뉴의 ‘+- 단추‘ 를 클릭 합니다. 그럼 + – 단추가 없어집니다.
이번에는 필드 머리글을 없애 보겠습니다. 피벗 테이블 분석 – 필드 머리글 을 선택해 주시면 필드 머리글이 없어져서 좀 더 깔끔해 집니다.
이렇게 엑셀 피벗테이블 만들기부터 수정하고 깔끔하게 보이게 하는 방법 까지 알아 봤습니다.
이상 엑셀 피벗테이블 만들기 방법에 관한 글이었습니다.