피벗 테이블은 Excel에 내장된 놀라운 보고 도구입니다. 일반적으로 합계가 있는 데이터를 요약하는 데 사용되지만 값 사이의 변화 백분율을 계산하는 데 사용할 수도 있습니다. 더 좋은 점: 간단합니다.
이 기술을 사용하여 모든 종류의 작업을 수행할 수 있습니다. 거의 모든 곳에서 한 값을 다른 값과 비교하는 방법을 확인하려는 경우입니다. 이 기사에서는 총 판매 가치가 월별로 변하는 비율을 계산하고 표시하는 간단한 예를 사용할 것입니다.
여기 우리가 사용할 시트가 있습니다.
주문 날짜, 고객 이름, 영업 담당자, 총 판매 금액 및 기타 몇 가지 사항을 보여주는 판매 시트의 아주 일반적인 예입니다.
이 모든 작업을 수행하기 위해 먼저 Excel에서 표로 값 범위의 서식을 지정한 다음 백분율 변경 계산을 만들고 표시하기 위해 피벗 테이블을 만들 것입니다.
목차
범위를 테이블로 포맷하기
데이터 범위가 아직 표 형식이 아닌 경우 그렇게 하는 것이 좋습니다. 테이블에 저장된 데이터는 특히 피벗 테이블(테이블 사용의 이점에 대해 자세히 알아보기).
범위를 표로 지정하려면 셀 범위를 선택하고 삽입 > 표를 클릭합니다.
범위가 올바른지, 해당 범위의 첫 번째 행에 머리글이 있는지 확인한 다음 “확인”을 클릭합니다.
이제 범위가 테이블 형식으로 지정됩니다. 테이블 이름을 지정하면 나중에 피벗 테이블, 차트 및 수식을 만들 때 참조하기가 더 쉬워집니다.
표 도구 아래의 “디자인” 탭을 클릭하고 리본 시작 부분에 제공된 상자에 이름을 입력합니다. 이 테이블의 이름은 “Sales”입니다.
원하는 경우 여기에서 테이블 스타일을 변경할 수도 있습니다.
백분율 변경을 표시하는 피벗 테이블 만들기
이제 피벗 테이블을 만들어 보겠습니다. 새 테이블 내에서 삽입 > 피벗 테이블을 클릭합니다.
피벗 테이블 만들기 창이 나타납니다. 그것은 자동으로 당신의 테이블을 감지할 것입니다. 그러나 이 시점에서 피벗 테이블에 사용할 테이블이나 범위를 선택할 수 있습니다.
날짜를 월로 그룹화
그런 다음 그룹화할 날짜 필드를 피벗 테이블의 행 영역으로 끕니다. 이 예에서 필드의 이름은 Order Date입니다.
Excel 2016부터 날짜 값은 자동으로 연도, 분기 및 월로 그룹화됩니다.
사용 중인 Excel 버전에서 이 작업을 수행하지 않거나 단순히 그룹화를 변경하려는 경우 날짜 값이 포함된 셀을 마우스 오른쪽 버튼으로 클릭한 다음 “그룹화” 명령을 선택합니다.
사용하려는 그룹을 선택합니다. 이 예에서는 연도와 월만 선택합니다.
연도와 월은 이제 분석에 사용할 수 있는 필드입니다. 월은 여전히 Order Date로 이름이 지정됩니다.
피벗 테이블에 값 필드 추가
연도 필드를 행에서 필터 영역으로 이동합니다. 이렇게 하면 사용자가 너무 많은 정보로 피벗 테이블을 복잡하게 만드는 대신 1년 동안 피벗 테이블을 필터링할 수 있습니다.
계산하고 변경 사항을 표시할 값(이 예에서는 총 판매액)이 포함된 필드를 값 영역으로 두 번 드래그합니다.
아직 많지 않아 보일 수 있습니다. 그러나 그것은 곧 바뀔 것입니다.
두 값 필드 모두 기본적으로 합계로 설정되며 현재 형식이 없습니다.
합계로 유지하려는 첫 번째 열의 값입니다. 그러나 형식이 필요합니다.
첫 번째 열에서 숫자를 마우스 오른쪽 버튼으로 클릭하고 바로 가기 메뉴에서 “숫자 서식”을 선택합니다.
셀 서식 대화 상자에서 소수점 이하 자릿수가 0인 “회계” 형식을 선택합니다.
이제 피벗 테이블이 다음과 같이 표시됩니다.
백분율 변경 열 만들기
두 번째 열의 값을 마우스 오른쪽 버튼으로 클릭하고 “값 표시”를 가리킨 다음 “% 차이” 옵션을 클릭합니다.
기본 항목으로 “(이전)”을 선택합니다. 즉, 현재 월 값은 항상 이전 월(주문 날짜 필드) 값과 비교됩니다.
이제 피벗 테이블에 값과 백분율 변경이 모두 표시됩니다.
행 레이블이 포함된 셀을 클릭하고 해당 열의 머리글로 “월”을 입력합니다. 그런 다음 두 번째 값 열의 머리글 셀을 클릭하고 “Variance”를 입력합니다.
일부 분산 화살표 추가
이 피벗 테이블을 실제로 다듬기 위해 녹색 및 빨간색 화살표를 추가하여 백분율 변경을 더 잘 시각화하고 싶습니다.
이것들은 변화가 긍정적인지 부정적인지 보는 멋진 방법을 제공할 것입니다.
두 번째 열의 값 중 하나를 클릭한 다음 홈 > 조건부 서식 > 새 규칙을 클릭합니다. 서식 규칙 편집 창이 열리면 다음 단계를 수행하십시오.
“주문 날짜에 대한 “차이” 값을 표시하는 모든 셀” 옵션을 선택합니다.
형식 스타일 목록에서 “아이콘 세트”를 선택합니다.
아이콘 스타일 목록에서 빨간색, 주황색 및 녹색 삼각형을 선택합니다.
유형 열에서 목록 옵션을 백분율 대신 “숫자”로 변경합니다. 그러면 값 열이 0으로 변경됩니다. 정확히 우리가 원하는 것입니다.
“확인”을 클릭하면 조건부 서식이 피벗 테이블에 적용됩니다.
피벗 테이블은 놀라운 도구이자 값에 대한 시간 경과에 따른 백분율 변화를 표시하는 가장 간단한 방법 중 하나입니다.