Excel의 피벗 테이블 - 효과적인 데이터 분석

귀하의 Excel 분석을 한 단계 높이기 위해서는 피벗 테이블이 필수적입니다. 이 안내서에서는 유용한 데이터 통찰을 제공하는 포괄적인 피벗 분석을 수행하는 방법에 대해 안내합니다. 주어진 데이터 세트에서 다양한 지표를 생성하고 분석하는 데 필요한 단계를 안내합니다.

주요 통찰사항

• 피벗 테이블을 활용하면 유연한 데이터 분석이 가능합니다.
• 피벗 테이블에서의 계산을 통해 매출 및 이익에 대한 즉각적인 결과가 얻어집니다.
• 세부 분석을 통해 특정 데이터를 필터링하여 내보낼 수 있습니다.
• 데이터를 그룹화하여 더 명확한 개요를 작성할 수 있습니다.

단계별 안내

단계 1: 피벗 테이블 생성

피벗 테이블 생성으로 시작합니다. Excel을 열고 데이터를 선택하세요. 이를 위해 Ctrl + A를 누른 후 Ctrl + T를 눌러 모든 것을 선택하고 서식을 지정할 수 있습니다. 그런 다음 "피벗 테이블로 요약" 옵션을 사용하여 피벗 테이블을 만들고, "피벗 분석"이라는 새 워크시트에 배치합니다.

단계 2: 도시 및 사업 분야별 판매 개요

피벗 테이블이 생성되면 판매 개요를 작성하기 위한 첫 번째 단계를 수행합니다. "판매량" 필드를 값에 끌어다 놓고, "사업 분야"와 "도시" 필드를 행과 열에 드래그하세요. 이제 명확한 개요를 확인할 수 있어야 합니다. 개요를 더 읽기 쉽게 만들기 위해 도시와 사업 분야를 적절하게 해당시킵니다.

단계 3: 매출 계산

이제 매출 계산을 시작할 수 있습니다. 이는 판매량과 단가를 곱셈하여 진행됩니다. 마우스 오른쪽 버튼을 클릭하고 "셀 삽입"을 선택하여 새 열을 추가합니다. 새 열을 "매출"이라고 명명하고, "판매량 * 단가" 공식으로 매출을 계산합니다. 이 공식을 모든 행에 대해 아래로 끌어내기하면 테이블이 계속 업데이트되도록 할 수 있습니다.

단계 4: 이익 계산

이익을 계산하려면 추가적인 열을 만들 수 있습니다. 대신에 이익 및 비용에 대한 필드를 삽입하여 피벗 옵션을 간단히 사용할 수도 있습니다. 이익은 "매출 - 총원가"로 계산됩니다.

단계 5: 순이익 계산

순이익을 계산하려면 피벗 분석으로 돌아가세요. "분석"을 클릭한 다음 "필드, 요소, 및 계산된 필드"를 선택합니다. 여기에 순이익을 추가하고 이익에 0.7을 곱하여 총이익의 70%를 순이익으로 표시합니다.

단계 6: 내보내기를 통한 상세 분석

상세 분석 생성을 위해 새로운 피벗 테이블을 만들고 각 도시의 순이익을 중점으로 유지하세요. 사업 분야를 피벗 테이블에서 제거하고 "이익" 필드를 값 영역으로 드래그하면 예를 들어 햄부르크 판매 지역에 대한 특정 정보를 얻을 수 있습니다.

단계 7: 매출 데이터 내보내기

이제 매출 데이터의 내보내기를 위한 또 다른 피벗 테이블을 작성하세요. 이전 피벗 테이블을 복사하고, 쾰른과 슈투트가트의 매출에 초점을 맞춥니다. 도시 숫자를 두 번 클릭하여 데이터를 내보내세요. 데이터 시트를 적절히 구성하고 병합하는 데 주의하세요.

단계 8: 이익 정렬

이익을 분석하여 각 도시별 이익을 올림차순으로 정렬하여 도시별 이익 차이를 더 잘 이해할 수 있습니다. 새로운 피벗 테이블을 만들고 이익을 값에, 도시를 행에 드래그합니다. 그런 다음 결과를 선택한 다음 "데이터"를 클릭하여 필요에 따라 정렬하세요.

단계 9: 도시 그룹화

더 나은 개요를 위해 도시를 그룹화할 수 있습니다. 예를 들어, 뮌헨과 슈투트가 "남부" 그룹에, 베를린, 쾰른, 함부르크가 "북부" 그룹에 속하도록 할 수 있습니다. 이는 Pivot 테이블을 복사하고 조정하고 그룹화 기능을 사용하여 수행됩니다.

단계 10: 순수 이익의 백분율 개요

도시별 순수 이익을 백분율로 표시하려면 새로운 Pivot 테이블을 만듭니다. 순수 이익과 도시를 값과 행에 드래그합니다. 백분율을 표시하려면 값 필드 설정에서 "전체 결과의 백분율"을 선택합니다.

단계 11: 매출의 백분율 표시

마지막 작업으로 마지막 Pivot 테이블을 복사하고 순수 이익을 매출로 대체합니다. 사업 부문을 추가하고 값을 "전체 결과의 백분율"로 설정하려면 값 필드 설정으로 이동합니다. 이로써 중요한 모든 정보를 모았습니다.

요약

이 단계별 안내서를 통해 Pivot 테이블을 사용하여 복잡한 데이터를 분석하고 정리하는 방법을 배웠습니다. 매출부터 이익 계산까지 데이터를 구조화하고 더 깊은 통찰력을 얻는 방법을 배웠습니다. 이러한 기술을 습득하면 Excel 역량을 크게 향상시킬 수 있습니다.