스크랩 [여시생활정보] [엑셀] 피벗 테이블 완정정복

[은하수 빛길을 너와 함께 걷는다]

RANDBETWEEN 함수 이용해 연습용 가상 데이터 생성하기

피벗 테이블을 시작하려면 우선 분석할 만한 양의 소스 데이터가 있어야 한다. 하지만 소스 데이터가 없는 경우에는 ‘RADNBETWEEN’이라는 함수를 이용해 연습용 가상 데이터를 생성할 수 있다. 엑셀 셀 내에 =RANDBETWEEN (최저수, 최대수) 함수를 입력하게 되면, 해당 셀에 최저수와 최대수 사이에 랜덤으로 숫자가 하나 입력되게 된다.
 
피벗 테이블이 가장 유용하게 사용되는 매출 데이터를 가상으로 한 번 만들어 볼 텐데, 우선 아래와 같이 칼럼을 지정하고 회계연도와 분기를 입력한다. 그 후 지역과 대리점, 제품, 수량 및 매출 칼럼의 데이터를 모두 RANDBETWEEN을 이용해 채운다. 각 칼럼의 첫번째 셀에 함수를 삽입 후에 아래쪽으로 마우스를 드래그하면 동일한 함수가 셀마다 촘촘하게 채워진다. 실제 회사에서 업무를 할 때에는 각각에 지역, 대리점, 제품 등에 고유한 이름이 들어가겠지만, 지금은 해당 데이터를 일일이 넣기 어려우니 숫자로 구분해 보기로 한다.

총 6개의 가상 지역으로 구분하기 위해, 1~6 사이의 숫자를 랜덤으로 넣어보았다. RANDBETWEEN을 사용하였기 때문에 이번에는 3이 나타나지만, 셀 클릭을 할 때마다 랜덤으로 숫자가 바뀐다.

피벗 테이블 생성하기
피벗 테이블은 소스 데이터와 같은 워크시트 내에 생성하여도 되고, 깔끔한 보고서를 원한다면 별도의 워크시트에 삽입하여도 된다. 어디에 넣을지가 정해졌다면 해당 워크시트에 셀 하나를 지정한 후, 엑셀 메뉴에서 삽입을 누른다. 삽입 메뉴에 가장 왼쪽에 있는 피벗테이블을 누르면 피벗테이블이 생성되는데, 차트 섹션에 있는 피벗 차트를 누르면 테이블과 차트를 동시에 생성 가능하니 참고!

엑셀 메뉴 > 삽입 > 피벗 테이블 순으로 삽입하면 된다.

아래와 같은 표가 나타나면, 마우스를 드래그하여 피벗에 포함될 데이터의 범위를 지정해준다. 워크시트와 기존 워크시트 중 피벗테이블이 어디에 위치할 지 이 표에서도 지정할 수 있다.

데이터 지정 후 확인을 클릭하면, 아래와 같이 새 워크시트에 피벗 테이블 레이아웃이 나타난다. 피벗을 처음 접해보는 사람의 경우 피벗 테이블의 레이아웃만 나타나고 데이터가 하나도 없어 당황할 수 있지만, 침착하자. 피벗 데이터는 지금부터 만들어 나가는 것이다.

피벗 테이블 생성 시 처음 만나게 되는 화면

피벗 테이블은 필터(Filter), 행(Row), 열(Columns) 그리고 값(Values)의 총 4개 필드로 구성을 할 수 있는데, 소스 데이터의 다양한 칼럼 (예: 회계연도, 분기 등)을 각각 드래그하여 원하는 필드에 넣으면 피벗 테이블이 완성된다. 총 4개의 필드 중 ‘값’은 피벗 테이블 완성을 위해 하나 이상 꼭 들어가야 하는 필수 필드이다. 칼럼을 어떻게 구성하느냐에 따라 같은 데이터로 만든 피벗이라도 아래와 같이 다양하게 나올 수 있다.

가장 기본적인 구성이다. 필터/열/행/값에 각각 지역/제품/대리점/매출을 넣어보았다. 이렇게 되면, 대리점별로 각 제품의 매출액이 얼마나 되는지를 확인해볼 수 있으며, 지역별로 필터링하여 각 대리점의 매출 현황을 볼 수 있다.

각 지역 내 대리점이 분기별로 판매한 수량과 매출을 요약해 보고 싶다면, 필터링 가능한 칼럼에 지역을 놓고, 행에 대리점과 분기를 차례로 넣어 각 대리점 하위에 분기별 매출 숫자를 볼 수 있도록 하면 된다.

피벗 테이블 차트 생성하기
앞서 메뉴 바에서 나온 것처럼, 피벗 테이블의 데이터도 차트로 만들어낼 수 있다. 삽입 > 차트 > 피벗차트를 눌러 손쉽게 생성할 수 있으며, 꺽은선, 파이, 막대그래프 등 일반적으로 제공되는 차트 형태는 모두 적용 가능하다. 피벗 차트가 일반 차트와 다른 점은 차트 내 데이터가 인터랙티브하게 변화한다는 것이다. 연습용으로 만든 매출데이터를 아래와 같이 막대형 피벗 차트로 만들어보았는데, 빨간 박스로 표시된 부분인 지역/매출/대리점/제품이 모두 필터로 적용된 것을 볼 수 있다. 이 필터 박스를 클릭하면 차트 내 들어가는 데이터를 마음대로 편집할 수 있다. 예를 들어, 총 10개의 제품 중 주력 제품인 제품 3과 제품 5에 대한 데이터만 차트에서 보고자 한다면, 제품 필터를 열어 제품 3과 제품 5만 선택하면 차트가 자동적으로 그에 맞게 업데이트된다.

피벗 차트의 예

제품 3과 5만 선택했을 때 맞추어 변경된 피벗 차트

외부 데이터로 피벗 테이블 생성하기
데이터가 엑셀 내에 존재하면 편하겠지만, 회사에서 실무를 하다 보면 다른 프로그램에 들어있는 데이터를 불러와서 분석해야 할 때도 있다. 피벗 테이블은 엑셀 파일 내 데이터 외에도 데이터베이스 플랫폼인 SQL이나 Access, OLAP 큐브 파일의 데이터와 연결해 만들 수 있기 때문에 더욱 강력하다는 평을 받는다. 외부 데이터와 연결할 때에는 피벗 테이블을 삽입하기 전에 ‘데이터’ 메뉴로 들어가 외부 데이터를 Excel 워크시트에 미리 연결해 놓아야 하는데, 데이터 > Access 또는 '기타 원본에서'를 클릭하여 필요로 하는 데이터를 연결할 수 있다. 연결이 완료되면 피벗 테이블을 삽입할 때 ‘표 또는 범위 선택’ 대신 ‘외부 데이터 원본 사용’을 클릭하여 연결된 데이터를 선택하면 된다.

피벗 테이블 데이터 소스 업데이트 및 변경
원본 데이터 내 데이터가 변경이 될 경우, 피벗 테이블에는 자동적으로 그러한 변경사항이 반영되지 않는다. 따라서, 최신 데이터를 반영하고자 한다면 피벗 테이블을 새로고침(Refresh)하여 수동으로 데이터 업데이트를 해줘야만 한다. 피벗 테이블을 이미 생성한 후라고 해도 소스 데이터를 변경할 수 있다. 예를 들어, 데이터 행이 더 많이 추가될 경우에는 새롭게 추가된 행을 포함하도록 데이터 범위를 더 넓힐 수 있다. 그렇지만, 데이터의 구조 등 많은 부분이 변경된 경우에는 피벗 테이블을 새롭게 만드는 것이 오히려 깔끔하고 쉽다.

피벗 테이블의 전체 데이터를 새로고침할 수도 있고, 특정 셀의 데이터만 새로고침할 수도 있다.

피벗 테이블 삭제하기
피벗 테이블이 더 이상 필요하지 않은 경우에는 피벗 테이블을 삭제할 수 있다. 이 때에는 피벗 테이블을 전체선택한 이후에 삭제(Delete) 키를 누르면 되는데, 만약 ‘전체 선택’이 되어 있지 않다면 “피벗 테이블에서 이 부분을 변경할 수 없습니다. (영문 버젼일 경우, Cannot change this part of a PivotTable report)”라는 에러 메시지를 받게 된다. 이 때에는 전체선택 단축키(Ctrl+A)를 누른 후 삭제(Delete)키를 다시 한 번 눌러주면 된다. 또는 피벗 테이블 도구 > 분석 > 선택 > 전체 피벗 테이블 선택 후, Delete키를 눌러도 동일하게 피벗 테이블을 삭제할 수 있다.


자, 이정도면 피벗 테이블에 대한 컨셉은 잡혔을 것이다. 이제 남은 것은 “피벗 테이블 좀 할 줄 아는지?”라는 질문에 자신 있게 ‘YES!’라고 답할 수 있도록 연습을 거듭해보는 것 뿐!

[오늘의 단축키] 피벗 테이블 생성하기: Alt + D + P

출처 : 마이크로소프트 블로그 

[경수랑결혼할여시]

인턴할때 피벗테이블 배워서 열심히 써먹었는데... 지금은 다까먹엇다ㅠㅠㅠㅠㅠ 다시보니까 또 어려운것ㅎㅎ...

[쟤입니다]

자격증딸때까진 할줄알앗는데 지금은 1도모르는것..... 이거보고 다시 복습해야지8ㅅ8

[수줍]

와 딱 자격증땜에 콧멍들어왔는데 봤어 운명인가봐

[징구]

애기는 넘나 어려워ㅠㅠ 모스 땄는데 여전히 엑셀은 1도 모르겠다 8ㅅ8

[Taron]

피벗테이블 안구래도 공부할라그랬는데 올려줘서 고마워 여시야!!

[마음만은 금손]

tptkddp!! gkrdnjsdksrkehehlfrjrkxdk!!

[boymeetsevil]

(엑셀) 엑셀 공부하려고 했는데 완전 고마워!

[하늘에서비가와내마음도비가와]

엑셀 피벗) 넘나 고맙슴니다 컴퓨터 공부해서 뽀사버렷

[우러우러기]

신입여시!!! 완전 필요해찌! 오늘 집 오면서 엑셀 공부 해야게따~~ 했는데!!! 고마워용!!

[내젖갈등]

와..피벗!!!유용하게 잘쓸껭 ♡

[beeeeee해피]

나 이거 처음본당 한번해봐야징 ㅎㅎㅎㅎㅎ

[프링글스어니]

고마워요 여시!!!

엑셀!! 고마워!!!

신입여시인데 고마워!!!!!

[온제쯤취업]

엑셀공부!!

[신입김여시입니다]

여시 연어하다 왓는데 꿀같은 글이야,, 너무 고맙고 감사합니다ㅠㅠㅁ
신입 여시에겐 황금같은 정보야
좋은하루 보내 여시 ❤️

[성공한다]

고마워 너무 유용하다ㅠㅠ