이번엔 피벗 테이블을 알아보겠습니다. 사실 피벗 테이블은 "~IF" 함수나 "~IFS" 함수에 이어서 보시는 것이 좋습니다. 맥락을 같이하기 때문이죠. 두 함수의 공통점은 만들어진 데이터 표에서 특정 조건에 맞는 통계(합계, 평균, 개수 등)를 반환하는 기능이었습니다. 이것들 말고도 "D~" 함수, "SUBTOTAL" 함수 등도 있었습니다. 모두 조건에 맞는 통계를 찾는 함수들이었죠. 위 함수들을 잘 모르신다면 이 강좌의 이해를 위해 "~IF" 함수와 "~IFS" 함수 정도는 한 번 확인하시고 돌아오시는 것이 좋겠습니다.
~IF 함수 (SUMIF, AVERAGEIF, COUNTIF) - 원래 함수의 기능에 조건 추가하기
엑셀 함수 중 뒤에 IF가 붙은 함수(SUMIF, AVERAGEIF, COUNTIF)에 대해 설명합니다. ~IF로 끝난 함수는 원래 함수의 기능에 조건 기능이 추가된 것임을 알아봅니다. 각 함수의 인수에 range와 criteria가 왜 필요한지 설명하고 실제로 3가지 함수를 작성해 봅니다. 작성된 함수와 데이터 유효성 검사로 자동화하는 방법을 설명하고 SUBTOTAL 함수와도 비교해 봅니다.
https://flylight.kr/
~IFS 함수 (SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS) - 다중 조건 기능 추가하기
엑셀 함수 중 뒤에 IFS가 붙은 함수(SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS)에 대해 설명합니다. ~IFS로 끝난 함수는 원래 함수의 기능에 다중 조건 기능이 추가된 것임을 알아봅니다. 1개의 조건이 추가될 때마다 함수 인수 2개(criteria_range와 criteria)가 추가 되는 이유를 설명합니다. ~IF 함수와 비교하여 인수들의 위치를 알아봅니다.
https://flylight.kr/
피벗 테이블 역시 기능상 위 함수들과 같은 역할을 합니다. 조건에 맞는 데이터의 통계를 반환합니다. 그런데 함수가 아니라 새로운 표로 원하는 통계를 반환합니다.
물론 위의 함수로 통계를 반환할 수 있습니다. 문제는 복잡하다는 것이죠. 함수의 기능과 그 함수의 인수도 알아야 합니다. 그리고 그걸 알아보게 하기 위해선 몇 가지 작업을 필연적으로 해줘야 합니다. 이런 문제를 피벗 테이블은 클릭 몇 번으로 모두 해결할 수 있습니다.
예제 파일은 이전에도 몇 번 사용한 "다팔아 쇼핑몰 주문 내역"입니다. 여기서 전체 금액 합을 알아내기 위해선 SUM 함수를 사용해야 합니다. 제품분류별 금액 합을 알아내기 위해선 SUMIF 함수를 사용해야 합니다. 제품분류에는 "가구, 사무용품, 전자제품"이 있으니 SUMIF 함수를 총 3번 만들어야겠습니다. 그리고 이 통계가 뭐라는 것을 알려 주기 위해서 항목(가구, 사무용품, 전자제품)도 셀을 만들어줘야겠습니다.
즉 "제품분류별 금액 합"의 통계를 반환하려면, 항목(레이블)은 "가구, 사무용품, 전자제품"으로, 데이터값(레코드)은 SUMIF 함수로, 제품분류가 가구인 것, 제품분류가 사무용품인 것, 제품분류가 전자제품인 것을 각각 만들어야 한다는 것이죠. 작업량이 많아 질 수 밖에 없습니다. 반면 피벗 테이블을 사용하면 매우 간단하게 해결할 수 있습니다.
피벗 테이블로 만들어 보겠습니다.
- 일단 표 전체를 범위 선택해야 합니다. 클릭 드래그해도 되지만 레코드가 너무 많으니, 단축키를 사용하는 것이 좋습니다. 아무 셀이나 클릭 선택하고 단축키 ctrl+a를 눌러 전체 선택합니다. ⇒ 표 전체가 회색으로 바뀌고 외곽 테두리는 초록색으로 변합니다.
- "삽입 > 피벗 테이블" 아이콘을 클릭합니다. ⇒ "표 또는 범위의 피벗 테이블" 대화상자가 나옵니다.
- 표/범위 입력란에는 이미 선택한 표의 전체 범위가 절대 참조로 작성되어 있습니다. - 기존 워크시트를 클릭합니다. ⇒ 위치 입력란이 활성화되고 커서가 깜박입니다.
- 새 워크시트를 선택하면 시트를 자동으로 하나 추가하고 작업하는 것입니다. 지금은 비교를 위해 표 옆으로 만들기 위해 "기존 워크시트"로 선택한 것입니다. - J4 셀을 클릭합니다. ⇒ J4 셀의 테두리가 초록 점선으로 바뀌고 위치 입력란에 절대 참조됩니다.
- 최종 확인 버튼을 클릭합니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
결과 그림처럼 화면 우측으로 "피벗 테이블 필드" 작업창이 나옵니다. 탭 메뉴도 숨겨져 있던 "피벗 테이블 분석" 탭 메뉴로 자동 진입됩니다. 이때 만약 작업창이 보이지 않는다면 "피벗 테이블 분석 탭 > 필드 목록"을 클릭하시면 작업창이 나옵니다.
작업창은 크게 상단 영역과 하단 영역으로 나눠지는데 상단 영역은 선택한 표의 항목이 체크박스와 함께 들어왔습니다.
하단 영역은 "필터, 열, 행, 값"으로 다시 구분되는데 이 부분이 우리가 새로 통계를 작성할 표가 됩니다. 당장은 "열"과 "값"만 확인하시면 됩니다. 하단 영역 위의 설명을 보면 "아래 영역 사이에 필드를 끌어 놓으십시오"라고 설명되어 있습니다. 그대로 진행하겠습니다.
- 지금부터가 실제 피벗 테이블의 작성입니다. 새로 만들고 싶은 표는 "제품분류별 금액 합"이었습니다. 상단 영역에서 "제품분류"를 클릭 드래그하여 하단 영역 "열"로 끌어 놓습니다. ⇒ 새로 만들도록 선택해 두었던 J4 셀에 "제품분류"에 고유 데이터값(가구, 사무용품, 전자제품)들이 열의 형태로 자동 생성됩니다.
- 이때 "제품분류"가 사용되었다는 의미로 작업창 상단 영역의 "제품분류"가 진한 검정으로 바뀌고 체크박스에도 체크 표시가 됩니다. - 항목이 만들어졌으니 항목 밑으로 금액 합을 만들어야 합니다. 상단 영역에서 "금액(원)"을 클릭 드래그하여 하단 영역 "값"으로 끌어 놓습니다.
⇒ 항목 밑으로 금액합이 자동 반환됩니다.
- 이때 작업창 하단 영역 "값"에는 끌고온 "금액(원)"이 자동 "합계:금액(원)"으로 들어옵니다. 기본적으로 원본 표에 숫자로 만들어진 값은 "합계"로 반영되는 것입니다.
- 그리고 역시 "금액(원)"이 사용되었다는 의미로 작업창 상단 영역의 "금액(원)"이 진한 검정으로 바뀌고 체크박스에도 체크 표시가 됩니다.
피벗 테이블이 완성되었습니다. 어떤가요? 설명을 다소 길었지만, 표를 작성하고 함수를 몇 번 사용해야 만들 수 있던 결과를 클릭과 클릭 드래그 몇 번으로 완성했죠. 즉 피벗 테이블은 굉장히 손쉽고 빠르게 통계를 완성할 수 있는 강력한 기능입니다.
다음, 작업창의 "열"과 "값"이 어떤 역할을 하는지는 확인했으니 "행"도 확인해 보겠습니다.
그림처럼 작업창 하단 영역 "열"에 위치해 있던 "제품분류"를 클릭 드래그하여 "행" 영역으로 끌고 옵니다.
결과 열의 형태로 만들어졌었던 피벗 테이블이 행의 형태로 변경됩니다. 간단하게 표의 형태를 뒤집은 것입니다.
이제 피벗 테이블의 의미를 설명할 수 있겠습니다. 피벗(Pivot)은 "축, 회전"을 의미합니다. 지금처럼 열의 형태든 행의 형태든 회전시킬 수 있다는 것이고요. 테이블은 "표"를 의미하니 "회전할 수 있는 표" 정도로 해석할 수 있습니다. 그런데 그냥 원본 표를 회전하는 것은 아니고 원본 표 항목들의 고유 데이터값을 기준으로 사용자가 원하는 형태로 재구성할 수 있으며, 결과로 통계(합계, 평균, 개수,... 등)를 반환한다고 정의할 수 있겠습니다.
피벗 테이블 : 통계값을 반환하기 위해 원본 고유 데이터값을 행과 열로 재구성
이번 강좌는 이렇게 피벗 테이블이 무엇인지 알아보았습니다. 앞으로 피벗 테이블의 다양한 기능을 하나씩 알아보겠습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
의견글 입니다.
()
