Excel에서 피벗 테이블을 사용하여 백분율 변경을 계산하는 방법

피벗 테이블은 엑셀의 강력한 기능 중 하나로, 데이터를 효과적으로 요약하고 분석할 수 있게 해줍니다. 특히, 단순 합계 외에도 값들 간의 변화율을 계산하는 데 매우 유용합니다. 이 기능은 사용하기 쉬우면서도 매우 강력합니다.

이 기법은 다양한 분석 작업에 활용될 수 있습니다. 예를 들어, 여러 값들을 비교하여 변화를 파악하고 싶을 때 거의 모든 상황에서 적용 가능합니다. 이 글에서는 월별 총 판매액의 변화율을 계산하고 시각화하는 간단한 예시를 통해 피벗 테이블의 활용법을 살펴보겠습니다.

아래는 예시로 사용될 데이터 시트의 모습입니다.

위 데이터는 주문 날짜, 고객 이름, 영업 담당자, 총 판매 금액 등 일반적인 판매 관련 정보를 포함하고 있습니다.

이러한 데이터를 활용하여 먼저 엑셀에서 표 형식으로 데이터를 변환한 후, 피벗 테이블을 생성하여 변화율을 계산하고 표시하는 과정을 살펴볼 것입니다.

데이터 범위를 표로 변환

데이터 범위가 아직 표 형태로 지정되어 있지 않다면, 먼저 표 형식으로 변환하는 것이 좋습니다. 표 형식으로 관리되는 데이터는 특히 피벗 테이블 작업 시 여러 장점을 제공합니다. (엑셀 표 활용의 이점 더 알아보기).

표로 변환하려면, 데이터 범위를 선택한 후 '삽입' 탭에서 '표'를 클릭합니다.

표 만들기 창에서 데이터 범위가 올바르게 선택되었는지, 그리고 머리글 행이 포함되어 있는지 확인한 후 '확인'을 클릭합니다.

이제 데이터 범위가 표 형식으로 변환되었습니다. 표 이름을 설정하면 피벗 테이블, 차트 또는 수식 작성 시 참조하기가 더욱 쉬워집니다.

표 도구의 '디자인' 탭에서 리본 메뉴의 좌측에 있는 이름 상자에 원하는 표 이름을 입력합니다. 이 예시에서는 'Sales'로 지정합니다.

필요에 따라 표 스타일을 변경할 수도 있습니다.

변화율 표시 피벗 테이블 생성

이제 피벗 테이블을 만들어 보겠습니다. '삽입' 탭에서 '피벗 테이블'을 클릭합니다.

피벗 테이블 만들기 창이 나타나면, 엑셀이 자동으로 표를 인식합니다. 이 창에서 피벗 테이블에 사용할 데이터 범위 또는 표를 선택할 수 있습니다.

날짜를 월별로 그룹화

다음으로, 날짜 필드를 피벗 테이블의 행 영역으로 드래그합니다. 이 예시에서는 'Order Date' 필드를 사용합니다.

엑셀 2016 버전부터는 날짜 값이 자동으로 연도, 분기, 월별로 그룹화됩니다.

만약 엑셀 버전에서 자동 그룹화가 되지 않거나 그룹화를 변경하고 싶다면, 날짜 셀을 우클릭하여 '그룹화' 명령을 선택합니다.

원하는 그룹 단위를 선택합니다. 이 예시에서는 '연도'와 '월'만 선택합니다.

이제 연도와 월을 분석에 사용할 수 있는 필드로 활용할 수 있습니다. 월 필드는 여전히 'Order Date'로 표시됩니다.

피벗 테이블에 값 필드 추가

연도 필드를 행 영역에서 필터 영역으로 옮깁니다. 이렇게 하면 사용자가 피벗 테이블을 특정 연도로 필터링할 수 있습니다.

변화율을 계산하고 표시할 값 필드(여기서는 '총 판매 금액')를 값 영역으로 두 번 드래그합니다.

아직은 큰 변화가 없어 보일 수 있지만, 곧 달라질 것입니다.

두 값 필드 모두 기본적으로 합계로 설정되어 있으며, 아직 서식이 지정되지 않았습니다.

첫 번째 열은 합계를 유지해야 하지만, 서식을 변경해야 합니다.

첫 번째 열의 숫자 셀을 우클릭하여 단축 메뉴에서 '숫자 서식'을 선택합니다.

셀 서식 대화 상자에서 소수점 이하 자릿수가 0인 '회계' 형식을 선택합니다.

이제 피벗 테이블은 다음과 같이 표시됩니다.

변화율 열 추가

두 번째 열의 값을 우클릭하고 '값 표시'를 가리킨 후 '% 차이' 옵션을 클릭합니다.

기준 항목으로 '(이전)'을 선택합니다. 이렇게 하면 현재 월의 값이 항상 이전 월의 값과 비교됩니다(주문 날짜 필드 기준).

이제 피벗 테이블에 실제 값과 변화율이 함께 표시됩니다.

행 레이블이 있는 셀을 클릭하고 해당 열의 머리글로 '월'을 입력합니다. 그런 다음 두 번째 값 열의 머리글 셀을 클릭하고 '변동'을 입력합니다.

변동 화살표 추가

피벗 테이블을 시각적으로 향상시키기 위해, 변화율을 더 쉽게 파악할 수 있도록 녹색 및 빨간색 화살표를 추가해 보겠습니다.

이 화살표들은 변화가 양수인지 음수인지 직관적으로 보여줄 것입니다.

두 번째 열의 값 중 하나를 선택하고 '홈' 탭에서 '조건부 서식' > '새 규칙'을 클릭합니다. 서식 규칙 편집 창에서 다음 단계를 따릅니다.

'주문 날짜에 대한 "변동" 값을 표시하는 모든 셀' 옵션을 선택합니다.
'서식 스타일' 목록에서 '아이콘 세트'를 선택합니다.
'아이콘 스타일' 목록에서 빨간색, 주황색, 녹색 삼각형을 선택합니다.
'유형' 열에서 목록 옵션을 백분율 대신 '숫자'로 변경합니다. 그러면 값 열이 0으로 변경됩니다. 이것이 우리가 원하는 것입니다.

'확인'을 클릭하면 조건부 서식이 피벗 테이블에 적용됩니다.

피벗 테이블은 매우 유용한 도구이며, 특히 시간 경과에 따른 값의 변화율을 간단하게 보여주는 데 탁월합니다.