일반적으로 원본에 새로운 필드를 계산해 추가하는 방식보다는 피벗테이블에서 계산 필드를 만들어 사용하는 것이 더 효율적입니다.
DB에서 테이블에 새로운 필드를 계속 추가하는 것이 딱히 좋은 방법은 아니듯이....
이번 포스팅은 계산 필드의 원론적인 얘기보다는 교육 현장에서 많이 하는 질문과 관련해서 포스팅하겠습니다.
현장에서 많이 하는 질문은 원본 필드를 피벗테이블에서 집계하면 원하는 방식으로 나오지 않는데 왜 그런것입니까??라는 질문입니다. 그래서 오늘은 이 질문에 포커스를 맞춰 설명하겠습니다.
(피벗테이블 만드는 방법은 알고 있다는 전제 하에 설명합니다.)
질문이해)
원본에 일자, 검사수량, 양품, 부적합,
부적합율 필드가 있습니다.
피벗테이블을 이용하여 월별 수량의 합을 집계하면 부적합율이 엉뚱한 결과로 나타나는 것 같습니다.
예를 들어 피벗테이블 7월의 부적합수량은 15, 검사수량은 1085이므로 부적합율은 15/1085의 결과인 0.0138(1.38%)이 나오길 기대합니다. 그러나 결과는 8.46%가 나옵니다.
이렇게 나오는 이유는 피벗테이블에서 「부적합수량/검사수량」으로 계산되는 것이 아니라 원본 데이터의 부적합율의 합을 계산하기 때문입니다.
원하는 결과)
그래서 원본의 부적합율의 합이 아닌, 「부적합수량/검사수량」으로 계산하기 위해서 피벗테이블에서 계산필드를 만들어 사용합니다. 그러면 기대하는 부적합율을 계산할 수 있습니다.
이유)
다시 한번 이유를 살펴보면 원본 필드의 부적합율을 피벗테이블 작업창 '∑값' 영역에 넣으면 원본 데이터를 기반으로 월별 부적합율을 더한(sum)결과인 8.46%가 계산됩니다. 자, 원본에서 7월에 해당하는 부적합율 [E2:E6]을 범위지정한 후 상태 표시 줄의 합계를 확인하면 8.46%가 나오지요? 7월달의 부적합율의 합계가 집계되는 것입니다.
계산 필드 만드는 방법)
이제 피벗테이블에서 계산 필드 만드는 방법을 알아보겠습니다. 먼저, 계산 필드를 만들기 위해서 몇가지 기억해야 할 사항이 있습니다.
▶ 계산 필드는 수식에 필드와 숫자를 사용하여 계산합니다.
▶ 계산 필드는 수식에 셀을 참조하거나 정의된 이름을 사용할 수 없습니다.
▶ 그러므로 vlookup처럼 셀을 참조하는 함수는 사용할 수 없습니다.
▶ 원본을 데이터 모델에 등록한 경우엔 계산필드를 사용할 수 없습니다.
(이런 경우엔 파워피벗의 DAX식을 사용해야합니다.)
1) 계산 필드로 부적합율 계산하기
피벗테이블 위 임의의 위치에 셀 포인트를 두고 [피벗 테이블 분석] 탭의 [계산 - 필드, 항목 및 집합 - 계산 필드]를 클릭합니다.
2) 계산 필드 수식 작성하기
[계산 필드 삽입] 대화상자가 나타나면 필드 이름과
수식을 입력한 후 [확인] 버튼을 클릭합니다.
3) 추가된 계산 필드 확인하기
피벗테이블에 새로운 필드가 추가 된 것을 확인합니다.
4) 표시형식 지정하기
새로 추가된 계산 필드의 표시 형식을 지정하기 위해 추가된 필드 위에서 마우스 오른쪽 버튼을 클릭한 후
[필드 표시 형식]을 클릭합니다.
5) 백분율로 지정하기
[셀 서식] 대화상자가 나타나면 [백분율]을 선택한 후 소수 자릿수를
'2'로 지정하고 [확인] 버튼을 클릭합니다.
6) 결과확인하기
다음과 같이 원하는 형태로 계산필드가 생성되었습니다.
7) 계산 필드 수정 및 삭제하기
생성한 계산 필드를 수정하거나 삭제하기 위해서 피벗테이블 위 임의의 위치에 셀 포인트를 두고
[피벗 테이블 분석] 탭의 [계산 - 필드, 항목 및 집합 - 계산 필드]를 클릭합니다.
8) 수정할 계산 필드 선택하기
[계산 필드 삽입] 대화상자가 나타나면 확장 버튼을 클릭하여 만들어진 계산 필드 리스트 중에서 수정 및 삭제할 필드를 선택합니다.
