피벗 테이블에서 값 계산

피벗 테이블의 값을 계산하려면 다음 유형의 계산 방법 중 하나 또는 모두를 사용할 수 있습니다.

• 값 필드의 요약 함수    값 영역의 데이터는 피벗 테이블의 기본 원본 데이터를 요약합니다. 예를 들어 다음과 같은 원본 데이터가 있습니다.

• 이 데이터는 다음과 같은 피벗 테이블과 피벗 차트를 생성합니다. 피벗 테이블의 데이터에서 피벗 차트를 만드는 경우 해당 피벗 차트의 값은 관련된 피벗 테이블 보고서의 계산을 반영합니다.

• 피벗 테이블에서 월 열 필드는 3월 및 4월 항목을 제공합니다. 지역 행 필드는 북부, 남부, 동부, 서부 항목을 제공합니다. 4월 열과 북부 행의 교차 부분에 있는 값은 월 값이 4월이고 지역 값이 북부인 원본 데이터 레코드의 총 매출 수익입니다.

• 피벗 차트에서 지역 필드는 북부, 남부, 동부 및 서부를 범주로 표시하는 범주 필드일 수 있습니다. 월 필드는 3월, 4월 및 5월 항목을 범례에 표시된 계열로 표시하는 계열 필드일 수 있습니다. 매출 합계라는 값 필드에는 각 지역의 월별 총 수익을 나타내는 데이터 표식이 포함될 수 있습니다. 예를 들어 한 개의 데이터 표식은 세로(값) 축의 위치로 북부 지역의 4월 총 매출을 나타냅니다.

• 값 필드를 계산하려면 OLAP(온라인 분석 처리) 원본 데이터를 제외한 모든 유형의 원본 데이터에 다음 요약 함수를 사용할 수 있습니다.

  함수	요약 정보
  Sum	값의 합입니다. 숫자 데이터에 대한 기본 함수입니다.
  Count	데이터 값의 개수입니다. Count 요약 함수는 COUNTA 함수와 동일하게 작동합니다. Count는 숫자가 아닌 데이터에 대한 기본 함수입니다.
  Average	값의 평균을 구합니다.
  Max	가장 큰 값을 구합니다.
  Min	가장 작은 값을 구합니다.
  Product	값의 곱을 구합니다.
  Count Nums	숫자인 데이터 값의 개수입니다. Count Nums 요약 함수는 COUNT 함수와 동일하게 작동합니다.
  StDev	전체 모집단의 부분 집합을 표본으로 모집단의 표준 편차에 대한 예상값을 구합니다.
  StDevp	모집단의 표준 편차를 구합니다. 여기에서 모집단은 요약할 모든 데이터입니다.
  Var	전체 목록의 부분 집합을 표본으로 모집단의 분산에 대한 예상치를 구합니다.
  Varp	모집단의 분산을 구합니다. 여기에서 모집단은 요약할 모든 데이터입니다.

• 사용자 지정 계산    사용자 지정 계산에서는 데이터 영역의 다른 항목 또는 셀을 기준으로 값을 표시합니다. 예를 들어 매출 합계 데이터 필드의 값을 3월 매출의 백분율로 표시하거나 월 필드에 있는 항목의 누계로 표시할 수 있습니다.

  다음 함수는 값 필드의 사용자 지정 계산에 사용할 수 있습니다.

  함수	결과
  계산 없음	필드에 입력된 대로 값을 표시합니다.
  총합계 비율	값을 보고서의 모든 값이나 데이터 요소의 총합계에 대한 백분율로 표시합니다.
  열 합계 비율	각 열 또는 계열에 있는 모든 값을 해당 열 또는 계열의 합계에 대한 백분율로 표시합니다.
  행 합계 비율	각 행 또는 범주에 있는 값을 해당 행 또는 범주의 합계에 대한 백분율로 표시합니다.
  [기준값]에 대한 비율	값을 기준 필드에 있는 기준 항목 값의 백분율로 표시합니다.
  상위 행 합계 비율	다음과 같이 값을 계산합니다. (항목에 대한 값)/(행의 상위 항목에 대한 값)
  상위 열 합계 비율	다음과 같이 값을 계산합니다. (항목에 대한 값)/(열의 상위 항목에 대한 값)
  상위 합계 비율	다음과 같이 값을 계산합니다. (항목에 대한 값)/(선택한 기준 필드의 상위 항목에 대한 값)
  [기준값]과의 차이	값을 기준 필드에 있는 기준 항목 값과의 차이로 표시합니다.
  [기준값]에 대한 비율의 차이	값을 기준 필드에 있는 기준 항목 값과의 백분율 차이로 표시합니다.
  누계	기준 필드에 있는 연속 항목의 값을 누계로 표시합니다.
  누계 비율	누계로 표시되는 기준 필드에 있는 연속 항목의 값을 백분율로 계산합니다.
  오름차순 순위 지정	특정 필드에 있는 선택한 값의 순위를 오름차순으로 표시합니다. 필드의 가장 작은 항목이 1로 표시되고 값이 커질수록 더 높은 순위 값을 갖습니다.
  내림차순 순위 지정	특정 필드에 있는 선택한 값의 순위를 오름차순으로 표시합니다. 필드의 가장 큰 항목이 1로 표시되고 값이 작아질수록 더 높은 순위 값을 갖습니다.
  색인	다음과 같이 값을 계산합니다. ((셀에 있는 값) x (총합계의 총합계)) / ((행 총합계) x (열 총합계))

• 수식    요약 함수와 사용자 지정 계산이 원하는 결과를 제공하지 않을 경우 계산 필드와 계산 항목에 고유한 수식을 만들 수 있습니다. 예를 들어 각 지역마다 다를 수 있는 판매 수수료에 대한 수식을 사용하여 계산 항목을 추가할 수 있습니다. 그러면 테이블의 부분합과 총합계에 자동으로 커미션이 포함됩니다.

보고서에서 사용할 수 있는 계산과 옵션은 원본 데이터가 OLAP 데이터베이스 또는 OLAP가 아닌 데이터 원본에서 제공되었는지 여부에 따라 다릅니다.

• OLAP 원본 데이터를 기반으로 하는 계산    OLAP 큐브에서 만든 피벗 테이블의 경우 Excel에서 결과를 표시하기 전에 요약된 값이 OLAP 서버에서 미리 계산됩니다. 피벗 테이블에서 이러한 미리 계산된 값이 계산되는 방식은 변경할 수 없습니다. 예를 들어 데이터 필드 또는 부분합을 계산하거나 계산 필드 또는 계산 항목을 추가하는 데 사용되는 요약 함수는 변경할 수 없습니다.

  또한 OLAP 서버가 계산 멤버로 알려진 계산 필드를 제공하는 경우 피벗 테이블 필드 목록에 이러한 필드가 표시됩니다. 또한 VBA(Visual Basic for Applications)에서 작성되어 통합 문서에 저장된 매크로에 의해 생성된 계산 필드와 계산 항목도 표시되지만 이러한 필드나 항목은 변경할 수 없습니다. 추가적인 계산 유형이 필요한 경우 OLAP 데이터베이스 관리자에게 문의하세요.

  OLAP 원본 데이터의 경우 부분합과 총합계를 계산할 때 숨겨진 항목의 값을 포함하거나 제외할 수 있습니다.

• OLAP 원본이 아닌 데이터를 기반으로 하는 계산    다른 유형의 외부 데이터 또는 워크시트 데이터를 기반으로 하는 피벗 테이블에서 Excel은 Sum 요약 함수를 사용하여 숫자 데이터가 포함된 값 필드를 계산하고, Count 요약 함수를 사용하여 텍스트가 포함된 데이터 필드를 계산합니다. 각 요약 함수(예: Average, Max 또는 Min)를 선택하여 데이터를 추가로 분석하고 사용자 지정할 수 있습니다. 또한 계산 필드나 필드 내의 계산 항목을 만들어 보고서 또는 다른 워크 시트 데이터의 요소를 사용하는 고유한 수식을 만들 수도 있습니다.

OLAP가 아닌 원본 데이터를 기반으로 하는 보고서에서만 수식을 만들 수 있습니다. OLAP 데이터베이스를 기반으로 하는 보고서에서는 수식을 사용할 수 없습니다. 피벗 테이블에서 수식을 사용하는 경우 다음 수식 구문 규칙 및 수식 동작에 대해 알고 있어야 합니다.

• 피벗 테이블 수식 요소    다른 워크시트 수식과 마찬가지로 계산 필드와 계산 항목에 대해 만든 수식에 연산자와 식을 사용할 수 있습니다. 상수를 사용하여 보고서의 데이터를 참조할 수 있지만 셀 참조 또는 정의된 이름은 사용할 수 없습니다. 셀 참조 또는 정의된 이름이 인수로 필요한 워크시트 함수와 배열 함수는 사용할 수 없습니다.

• 필드 및 항목 이름    Excel은 필드와 항목 이름을 사용하여 수식에서 보고서 요소를 식별합니다. 다음 예제에서는 C3:C9 범위의 데이터가 필드 이름 유제품을 사용합니다. Dairy 매출을 기준으로 새 제품의 매출을 추정하는 유형 필드의 계산 항목은 =Dairy * 115%와 같은 수식을 사용할 수 있습니다.

  

  참고: 피벗 차트에서 필드 이름은 피벗 테이블 필드 목록에 표시되고 각 필드 드롭다운 목록에서 항목 이름을 확인할 수 있습니다. 대신 계열 및 데이터 요소 이름을 반영하는 차트 팁에 표시되는 이름과 이러한 이름을 혼동하지 마세요.

• 수식은 개별 레코드가 아니라 총합에서 작동함    계산 필드의 수식은 수식에 있는 필드의 기본 데이터 합계에서 작동하지 않습니다. 예를 들어 계산 필드 수식 =Sales * 1.2는 각 유형 및 지역의 매출 합계에 1.2를 곱합니다. 각 개별 매출에 1.2를 곱한 다음 곱해진 금액을 합합니다.

  계산 항목의 수식은 개별 레코드에서 작동합니다. 예를 들어 계산 항목 수식 =Dairy *115%는 유제품의 각 개별 매출에 115%를 곱하며, 그 후에 곱해진 금액이 값 영역에 함께 요약됩니다.

• 이름의 공백, 숫자, 기호    필드가 둘 이상 포함된 이름의 필드는 임의 순서로 표시될 수 있습니다. 위의 예제에서 C6:D6 셀은 '4월 북부' 또는 '북부 4월'일 수 있습니다. 둘 이상의 단어로 구성되거나 숫자 또는 기호를 포함하는 이름은 작은따옴표로 묶습니다.

• 합계    수식은 합계(예: 예제의 3월 합계, 4월 합계 및 총합계)를 참조할 수 없습니다.

• 항목 참조의 필드 이름    항목 참조에 필드 이름을 포함할 수 있습니다. 항목 이름은 대괄호로 묶어야 합니다(예: 지역[북부]). #NAME? 오류를 방지하려면 이 형식을 사용합니다. 보고서에 있는 두 필드의 두 항목이 동일한 이름을 갖는 경우 발생하는 오류 예를 들어 보고서의 유형 필드에 고기라는 항목이 있고 범주 필드에 고기라는 다른 항목이 있는 경우 #NAME? 오류를 방지할 수 있습니다. 항목을 유형[고기] 및 범주[고기]로 참조하여 발생하는 오류

• 위치로 항목 참조    현재 정렬되어 표시된 보고서 내의 위치로 항목을 참조할 수 있습니다. 유형[1]은 유제품이고 유형[2]는 해산물입니다. 이 방법으로 참조된 항목은 항목의 위치가 변경되거나 다른 항목이 표시되거나 숨겨질 때마다 변경될 수 있습니다. 숨겨진 항목은 이 인덱스에 계산되지 않습니다.

  상대 위치를 사용하여 항목을 참조할 수 있습니다. 위치는 수식을 포함하는 계산 항목에 따라 결정됩니다. 남부가 현재 지역인 경우 지역[-1]은 북부입니다. 북부가 현재 지역인 경우 지역[+1]은 남부입니다. 예를 들어 계산 항목은 =Region[-1] * 3% 수식을 사용할 수 있습니다. 지정한 위치가 첫 번째 항목 앞이나 필드의 마지막 항목 뒤에 오는 경우 수식에서 #REF! 오류가 발생합니다.

피벗 차트에서 수식을 사용하려면 연결된 피벗 테이블에 수식을 만듭니다. 여기서 데이터를 구성하는 개별 값을 확인한 다음 피벗 차트에서 그래픽으로 결과를 볼 수 있습니다.

예를 들어 다음 피벗 차트는 지역별 각 영업 사원의 매출을 보여 줍니다.

10% 증가할 경우의 매출을 확인하려면 = Sales * 110%와 같은 수식을 사용하는 계산 필드를 연결된 피벗 테이블에 만들 수 있습니다.

다음 차트와 같이 결과가 피벗 차트에 바로 표시됩니다.

북부 지역의 매출에서 8% 운송비를 뺀 금액에 대해 별도의 데이터 표식을 표시하려면 =North – (North * 8%)와 같은 수식을 사용하여 지역 필드에 계산 항목을 만들 수 있습니다.

결과 차트는 다음과 같습니다.

그러나 영업 사원 필드에 생성된 계산 항목은 범례에 표시된 계열로 나타나며 각 범주의 데이터 요소로 차트에 표시됩니다.

시작하기 전에 필드 내에 계산 필드 또는 계산 항목을 만들지를 결정합니다. 수식에서 다른 필드의 데이터를 사용하려는 경우 계산 필드를 사용합니다. 수식에서 필드에 있는 하나 이상의 특정 항목 데이터를 사용하려는 경우 계산 항목을 사용합니다.

계산 항목의 경우 셀별로 다른 수식을 입력할 수 있습니다. 예를 들어 OrangeCounty라는 계산 항목이 모든 월에 =Oranges * .25 수식을 사용하는 경우 6월, 7월, 8월에 대해 수식을 =Oranges *.5로 변경할 수 있습니다.

계산 항목이나 수식이 여러 개 있는 경우 계산 순서를 조정할 수 있습니다.

계산 필드 추가

1. 피벗 테이블을 클릭합니다.

   이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

2. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 계산 필드를 클릭합니다.

   

3. 이름 상자에 필드 이름을 입력합니다.

4. 수식 상자에 필드에 대한 수식을 입력합니다.

   수식에 다른 필드의 데이터를 사용하려면 필드 상자에서 필드를 클릭한 다음 필드 삽입을 클릭합니다. 예를 들어 매출 필드에 있는 각 값에 대해 15% 커미션을 계산하려면 = Sales * 15%를 입력할 수 있습니다.

5. Add(추가)를 클릭합니다.

필드에 계산 항목 추가

1. 피벗 테이블을 클릭합니다.

   이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

2. 필드의 항목이 그룹화된 경우 분석 탭의 그룹 그룹에서 그룹 해제를 클릭합니다.

   

3. 계산 항목을 추가할 필드를 클릭합니다.

4. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 계산 항목을 클릭합니다.

   

5. 이름 상자에 계산 항목의 이름을 입력합니다.

6. 수식 상자에 항목에 대한 수식을 입력합니다.

   수식에 항목 데이터를 사용하려면 항목 목록에서 항목을 클릭한 다음 항목 삽입을 클릭합니다(항목은 계산 항목과 동일한 필드에서 가져와야 함).

7. Add(추가)를 클릭합니다.

계산 항목의 경우 셀별로 다른 수식 입력

1. 수식을 변경할 셀을 클릭합니다.

   여러 셀에 대한 수식을 변경하려면 Ctrl 키를 누른 상태로 추가 셀을 클릭합니다.

2. 수식 입력줄에서 수식에 대한 변경 내용을 입력합니다.

계산 항목이나 수식이 여러 개 있는 경우 계산 순서 조정

1. 피벗 테이블을 클릭합니다.

   이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

2. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 계산 순서를 클릭합니다.

   

3. 수식을 클릭한 다음 위로 이동 또는 아래로 이동을 클릭합니다.

4. 수식이 원하는 계산 순서로 배치될 때까지 계속합니다.

현재 피벗 테이블에서 사용되는 모든 수식 목록을 표시할 수 있습니다.

1. 피벗 테이블을 클릭합니다.

   이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

2. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 수식 보고서 작성을 클릭합니다.

   

수식을 편집하기 전에 수식이 계산 필드에 있는지, 또는 계산 항목에 있는지 확인합니다. 수식이 계산 항목에 있는 경우 수식이 계산 항목에 대한 유일한 수식인지도 확인합니다.

계산 항목의 경우 계산 항목의 특정 셀에 대해 개별 수식을 편집할 수 있습니다. 예를 들어 OrangeCalc라는 계산 항목이 모든 월에 =Oranges * .25 수식을 사용하는 경우 6월, 7월, 8월에 대해 수식을 =Oranges *.5로 변경할 수 있습니다.

수식이 계산 필드에 있는지, 또는 계산 항목에 있는지 확인

1. 피벗 테이블을 클릭합니다.

   이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

2. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 수식 보고서 작성을 클릭합니다.

   

3. 수식 목록의 계산 필드 또는 계산 항목 아래에서 변경할 수식을 찾습니다.
   
   계산 항목에 대한 수식이 여러 개 있을 경우 항목을 만들 때 입력한 기본 수식의 B 열에 계산 항목 이름이 있습니다. 계산 항목에 대한 추가 수식의 경우 B 열에 계산 항목 이름과 교차 항목 이름이 둘 다 포함됩니다.

   예를 들어 MyItem이라는 계산 항목에 대한 기본 수식과 MyItem 1월 매출로 식별된 이 항목에 대한 다른 수식이 있을 수 있습니다. 피벗 테이블의 MyItem 행과 1월 열의 매출 셀에서 이 수식을 찾습니다.

4. 다음 편집 방법 중 하나를 사용하여 계속합니다.

계산 필드 수식 편집

1. 피벗 테이블을 클릭합니다.

   이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

2. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 계산 필드를 클릭합니다.

   

3. 이름 상자에서 수식을 변경할 계산 필드를 선택합니다.

4. 수식 상자에서 수식을 편집합니다.

5. 수정을 클릭합니다.

계산 항목에 대한 단일 수식 편집

1. 계산 항목이 포함된 필드를 클릭합니다.

2. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 계산 항목을 클릭합니다.

   

3. 이름 상자에서 계산 항목을 선택합니다.

4. 수식 상자에서 수식을 편집합니다.

5. 수정을 클릭합니다.

계산 항목의 특정 셀에 대한 개별 수식 편집

1. 수식을 변경할 셀을 클릭합니다.

   여러 셀에 대한 수식을 변경하려면 Ctrl 키를 누른 상태로 추가 셀을 클릭합니다.

2. 수식 입력줄에서 수식에 대한 변경 내용을 입력합니다.

   팁: 계산 항목이나 수식이 여러 개 있는 경우 계산 순서를 조정할 수 있습니다. 자세한 내용은 계산 항목이나 수식이 여러 개 있는 경우 계산 순서 조정을 참조하세요.

1. 수식이 계산 필드에 있는지, 또는 계산 항목에 있는지 확인합니다.

   계산 필드는 피벗 테이블 필드 목록에 표시됩니다. 계산 항목은 다른 필드에 항목으로 표시됩니다.

2. 다음 중 하나를 수행합니다.

   • 계산 필드를 삭제하려면 피벗 테이블의 아무 곳이나 클릭합니다.

   • 계산 항목을 삭제하려면 피벗 테이블에서 삭제할 항목이 포함된 필드를 클릭합니다.

     이렇게 하면 피벗 테이블 도구가 표시되고 분석 및 디자인 탭이 추가됩니다.

3. 분석 탭의 계산 그룹에서 필드, 항목 및 집합을 클릭한 다음 계산 필드 또는 계산 항목을 클릭합니다.

   

4. 이름 상자에서 삭제할 필드 또는 항목을 선택합니다.

5. 삭제를 클릭합니다.