일반적으로 피벗 테이블을 만든 후에는 분석을 확장하고 더 많은 데이터/계산을 포함해야 합니다.
피벗 테이블의 기존 데이터 포인트를 사용하여 얻을 수 있는 새 데이터 포인트가 필요한 경우 돌아가서 소스 데이터에 추가할 필요가 없습니다.대신 다음을 사용할 수 있습니다.피벗 테이블 계산 필드이것을하기 위해.
내용
피벗 테이블 계산 필드란 무엇입니까?
피벗 테이블의 기본 예부터 시작하겠습니다.
소매업체 데이터 세트가 있고 다음과 같은 피벗 테이블을 생성한다고 가정합니다.
위의 피벗 테이블에는 소매업체의 매출 및 이익 가치가 요약되어 있습니다.
이제 이러한 소매업체의 이윤이 얼마인지도 알고 싶다면 어떻게 합니까(이윤 마진은 "이익"을 "매출"로 나눈 값임)?
이를 수행하는 몇 가지 방법이 있습니다.
- 원래 데이터 세트로 돌아가서 이 새 데이터 포인트를 추가하십시오.따라서 소스 데이터에 새 열을 삽입하고 그 안에 있는 이윤을 계산할 수 있습니다.이 작업이 완료되면 이 새 열을 일부로 가져오려면 피벗 테이블의 소스 데이터를 업데이트해야 합니다.
- 이 접근 방식이 작동하는 동안 수동으로 데이터 세트로 돌아가서 계산을 수행해야 합니다.예를 들어, 단위당 평균 매출(매출/수량)을 계산하기 위해 다른 열을 추가할 수 있습니다.다시 말하지만, 이 열을 소스 데이터에 추가한 다음 피벗 테이블을 업데이트해야 합니다.
- 이 방법은 또한 새 데이터를 추가할 때 피벗 테이블을 부풀립니다.
- 피벗 테이블 외부에 계산을 추가합니다.피벗 테이블 구조가 변경되지 않을 경우 이 옵션을 선택하십시오.그러나 피벗 테이블을 변경하면 그에 따라 계산이 업데이트되지 않고 잘못된 결과나 오류가 발생할 수 있습니다.아래와 같이 소매업체가 연속으로 있을 때의 마진율을 계산했습니다.그러나 고객에서 지역으로 변경하면 공식이 잘못됩니다.
- 피벗 테이블을 사용하여 필드를 계산합니다.這 是기존 피벗 테이블 데이터를 사용하고 계산가장 효율적인 방법.계산된 필드를 피벗 테이블의 기존 열을 사용하여 추가하는 가상 열로 생각하십시오.피벗 테이블 계산 필드를 사용하면 많은 이점이 있습니다(나중에 살펴보겠지만).
- 수식으로 작업하거나 소스 데이터를 업데이트할 필요가 없습니다.
- 피벗 테이블에 추가할 수 있는 새 데이터를 자동으로 고려하기 때문에 확장 가능합니다.계산된 필드를 추가한 후 피벗 테이블의 다른 필드처럼 사용할 수 있습니다.
- 업데이트 및 관리가 쉽습니다.예를 들어 메트릭이 변경되거나 계산을 변경해야 하는 경우 피벗 테이블 자체에서 쉽게 변경할 수 있습니다.
피벗 테이블에 계산된 필드 추가
기존 피벗 테이블에 피벗 테이블 계산 필드를 추가하는 방법을 살펴보겠습니다.
다음과 같은 피벗 테이블이 있고 각 소매업체의 이윤을 계산하려고 한다고 가정합니다.
다음은 피벗 테이블 계산 필드를 추가하는 단계입니다.
계산된 필드를 추가하면 피벗 테이블 필드 목록의 필드 중 하나로 나타납니다.
이제 이 계산된 필드를 다른 피벗 테이블 필드로 사용할 수 있습니다(피벗 테이블 계산된 필드를 보고서 필터 또는 슬라이서로 사용할 수 없음).
앞서 언급했듯이 피벗 테이블 계산 필드를 사용하면 피벗 테이블의 구조를 변경할 수 있고 자동으로 조정된다는 이점이 있습니다.
예를 들어, 지역을 행 영역으로 끌어다 놓으면 다음과 같은 결과를 얻을 수 있으며, 여기서 소매업체 및 지역에 대한 이윤 값이 보고됩니다.
위의 예에서는 간단한 수식(=Profit/Sales)을 사용하여 계산된 필드를 삽입했습니다.그러나 일부 고급 수식을 사용할 수도 있습니다.
고급 수식을 사용하여 피벗 테이블 계산 필드를 만드는 예를 보여드리기 전에 알아야 할 몇 가지 사항이 있습니다.
- 피벗 테이블 계산 필드를 만들 때 참조 또는 명명된 범위를 사용할 수 없습니다.VLOOKUP, INDEX, OFFSET 등과 같은 많은 수식은 제외됩니다.그러나 참조 없이 작동하는 수식을 사용할 수 있습니다(예: SUM, IF, COUNT 등...).
- 수식에 상수를 사용할 수 있습니다.예를 들어, 10% 증가할 것으로 예상되는 예상 매출 성장을 알고 싶다면 =Sales*1.1(여기서 1.1은 상수) 공식을 사용합니다.
- 계산된 필드를 생성하는 수식의 우선 순위를 따릅니다.가장 좋은 방법은 괄호를 사용하여 우선 순위를 기억할 필요가 없도록 하는 것입니다.
이제 고급 수식을 사용하여 계산된 필드를 만드는 예를 살펴보겠습니다.
아래에 표시된 데이터 세트가 있고 피벗 테이블에 예측된 판매를 표시해야 한다고 가정합니다.
예측 값의 경우 대형 소매업체(판매 300만 개 이상)에 대해 5% 판매 증가를 사용하고 중소기업(300만 개 미만 판매)에 대해 10% 판매 증가를 사용합니다.
참고: 여기에 있는 판매 수치는 가짜이며 이 자습서의 예를 설명하는 데 사용되었습니다.
방법은 다음과 같습니다.
이렇게 하면 판매 예측 값이 있는 피벗 테이블에 새 열이 추가됩니다.
피벗 테이블 계산 필드 문제
계산된 필드는 모든 것을 확장 가능하고 관리하기 쉽게 유지하면서 필드 계산으로 피벗 테이블의 가치를 실제로 높이는 놀라운 기능입니다.
그러나 피벗 테이블 계산 필드를 사용하기 전에 이해해야 하는 문제가 있습니다.
아래와 같은 피벗 테이블이 있고 계산된 필드를 사용하여 예측 판매 수치를 얻는다고 가정합니다.
소계와 합계가 올바르지 않습니다.
여기에는 각 소매업체에 대해 별도의 판매 예측 값이 추가되어야 하지만 실제로는 우리가 만든 동일한 계산된 필드 공식을 따릅니다.
따라서 South Total의 경우 값이 22,824,000이어야 하지만 South Total은 22,287,000으로 잘못 보고합니다.이것은 값을 얻기 위해 수식 21,225,800*1.05를 사용하기 때문에 발생합니다.
안타깝게도 이 문제를 해결할 수 없습니다.
이를 처리하는 가장 좋은 방법은 피벗 테이블에서 부분합과 총합계를 제거하는 것입니다.
당신은 또한 통과 할 수 있습니다 데브라 제공이 문제를 해결하기 위한 몇 가지 혁신적인 해결 방법입니다.
피벗 테이블 계산 필드를 수정하거나 삭제하는 방법은 무엇입니까?
피벗 테이블 계산 필드를 만든 후 다음 단계를 사용하여 수식을 수정하거나 삭제할 수 있습니다.
모든 계산된 필드 수식 목록을 얻는 방법은 무엇입니까?
피벗 테이블 계산 필드를 많이 만드는 경우 각 필드에 사용된 수식을 추적하는 것에 대해 걱정할 필요가 없습니다.
Excel을 사용하면 계산된 필드를 만드는 데 사용되는 모든 수식 목록을 빠르게 만들 수 있습니다.
모든 계산된 필드 수식 목록을 빠르게 가져오는 단계는 다음과 같습니다.
- 피벗 테이블에서 아무 셀이나 선택합니다.
- 피벗 테이블 도구 -> 분석 -> 필드, 항목 및 집합 -> 수식 나열로 이동합니다.
목록 수식을 클릭하면 Excel에서 피벗 테이블에서 사용한 모든 계산된 필드/항목의 세부 정보가 포함된 새 워크시트를 자동으로 삽입합니다.
클라이언트에게 작업을 보내거나 팀과 공유해야 하는 경우 매우 유용한 도구가 될 수 있습니다.
