엑셀은 판매 데이터를 저장하고 분석하는 데 가장 널리 사용되는 도구 중 하나입니다. 데이터 유형에 따라 분석 방법도 달라지기 때문에, 어떤 데이터를 다루고 있는지 정확히 이해한 후 적절한 분석 기법을 선택하는 것이 중요합니다.
예를 들어 여러 영업사원의 반기 판매 데이터가 있다고 가정해 보겠습니다. 이 데이터를 체계적으로 분석하려면 어떤 방법들을 활용할 수 있을까요?
이 글에서는 엑셀에서 판매 데이터를 분석할 수 있는 다양한 방법을 단계별로 자세히 소개합니다.
10가지 쉬운 판매 데이터 분석 방법
데이터의 종류에 따라 적합한 분석 방식이 다르며, 일부 데이터는 분석 전에 사전 정리 과정이 필요할 수 있습니다. 아래에서 각 방법을 하나씩 살펴보세요.
방법 1: 조건부 서식으로 판매 추세 시각화
엑셀의 조건부 서식(Conditional Formatting)은 판매 데이터를 순위 매기거나 강조하고, 아이콘 또는 색상 스케일로 표현하는 강력한 도구입니다.
1단계: 각 영업사원의 기간 내 총 판매액을 구합니다. 인접한 셀(예: I4)에 아래 수식을 입력하세요.
=SUM(C4:H4)
2단계: 홈 탭으로 이동한 후 스타일 그룹에서 조건부 서식을 선택합니다. 셀 규칙 강조, 위쪽/아래쪽 항목 규칙, 데이터 막대, 색조, 아이콘 집합 등 다양한 옵션 중 원하는 것을 선택하여 데이터 추세를 표현할 수 있습니다. 여기서는 아이콘 집합을 선택해 보겠습니다.
🔺 잠시 후 아래 그림처럼 판매 값에 따라 추세 아이콘이 자동으로 삽입됩니다.
방법 2: 피벗 테이블로 백분율 분석하기
피벗 테이블(Pivot Table)은 데이터를 체계적으로 정리하고 자동 정렬 기능까지 제공합니다. 행과 열에 다양한 필드를 자유롭게 배치할 수 있으며, 기존 값을 행 또는 열 기준 백분율로 표시할 수도 있습니다.
1단계: 원하는 범위를 선택한 후 삽입 > 테이블 그룹의 피벗 테이블 > 테이블/범위에서를 선택합니다.
2단계: 피벗 테이블 만들기 대화 상자가 나타납니다. 범위를 미리 선택했다면 엑셀이 테이블/범위를 자동으로 불러옵니다. 피벗 테이블 위치는 새 워크시트 옵션을 선택하고 확인을 클릭합니다.
🔺 아래와 같이 새 워크시트에 피벗 테이블이 생성됩니다.
3단계: 필요한 피벗 테이블 필드를 체크하고 원하는 위치에 배치합니다(아래 이미지 참고).
4단계: 아무 셀이나 클릭한 후 마우스 오른쪽 버튼을 누르고, 바로 가기 메뉴에서 값 필드 설정을 선택합니다.
5단계: 값 필드 설정 대화 상자에서 값 표시 형식 탭을 클릭하고 % 열 합계 옵션을 선택한 후 확인을 누릅니다.
6단계: 이제 모든 값이 월별 총 판매액 대비 백분율로 표시됩니다. 시각적으로 확인하려면 피벗 차트를 추가할 수 있습니다. 피벗 테이블 내부를 클릭한 후 피벗 테이블 분석 탭 > 도구 그룹의 피벗 차트를 선택합니다.
7단계: 차트 삽입 대화 상자가 나타나면 원하는 차트 유형(여기서는 세로 막대형 차트 선택)을 고른 뒤 확인을 클릭합니다.
🔺 확인 버튼을 누르면 각 월별 영업사원의 판매 비중을 보여주는 피벗 차트가 삽입됩니다.
방법 3: RANK 함수로 판매 데이터 순위 매기기
RANK 함수는 주어진 값이 숫자 목록 전체에서 몇 번째 순위인지 반환합니다. 함수 구문은 다음과 같습니다.
RANK(number, ref, [order])
1단계: 빈 셀(예: K4)에 아래 수식을 입력합니다.
=RANK(C4,$C$4:$H$13,0)수식에서 C4는 순위를 구할 값(number), $C$4:$H$13은 비교 대상 범위(ref), 0은 내림차순 정렬을 의미합니다.
2단계: 먼저 채우기 핸들을 오른쪽으로 드래그한 후, 아래로 드래그하여 모든 셀에 수식을 적용합니다.
판매 순위는 영업사원들 간의 판매 실적 비교 위치를 한눈에 보여줍니다.
방법 4: 슬라이서로 개인별 차트 조회하기
슬라이서(Slicer)는 필터와 비슷하게 작동하며, 개별 영업사원의 판매 데이터를 차트로 손쉽게 확인할 수 있게 해줍니다. 슬라이서를 사용하려면 먼저 데이터를 엑셀 테이블로 변환해야 합니다.
1단계: 원하는 범위를 선택한 후 삽입 탭 > 테이블을 클릭합니다.
2단계: 테이블 만들기 대화 상자에서 테이블에 머리글 포함 옵션에 체크한 후 확인을 누릅니다.
3단계: 다시 삽입 탭에서 필터 > 슬라이서를 선택합니다.
4단계: 슬라이서 삽입 창에서 열 머리글(Salesman 또는 월) 중 하나를 선택하여 해당 기준으로 데이터를 분류한 후 확인을 클릭하면 슬라이서가 삽입됩니다.
5단계: 삽입 탭에서 원하는 분산형 차트 유형을 선택합니다.
🔺 전체 판매 데이터를 담은 분산형 차트가 아래와 같이 생성됩니다.
6단계: 영업사원별 판매 진행 상황을 개별적으로 보려면 행과 열을 전환해야 합니다. 차트를 클릭한 후 차트 디자인 > 행/열 전환을 선택하세요.
🔺 축이 전환되면서 새로운 형태로 데이터가 표현됩니다.
7단계: 슬라이서에서 특정 영업사원(예: Bob)을 선택하면 차트가 자동으로 Bob의 판매 실적만 표시합니다.
선택을 초기화하려면 슬라이서 창 우측 상단의 필터 지우기(X 아이콘)를 클릭하면 됩니다.
방법 5: 인덱스 차트로 성장 추세 표시하기
판매 값을 100 기준으로 환산하면 성장 추세를 명확하게 파악할 수 있습니다. 모든 판매 데이터를 100 기준 지수로 변환하면 사용자는 거의 즉시 판매 흐름을 이해할 수 있습니다.
여기서는 아래 이미지처럼 3개월간의 판매 데이터를 사용하지만, 필요에 따라 더 많은 데이터를 활용할 수 있습니다.
1단계: 판매 데이터셋과 동일한 크기의 범위를 만든 후, 첫 번째 영업사원의 월별 판매 값에 100을 입력합니다.
2단계: 셀 H5에 아래 수식을 입력합니다.
=H$4*(C5/C4)
3단계: Enter 키를 누른 후 채우기 핸들을 오른쪽, 그리고 아래로 드래그하여 모든 셀에 수식을 적용합니다.
4단계: 인덱스 데이터 범위를 선택한 후 삽입 > 2차원 꺾은선형 차트를 선택합니다.
🔺 잠시 후 영업사원별 월간 판매 흐름을 보여주는 인덱스 꺾은선형 차트가 완성됩니다.
방법 6: 가중 평균 계산으로 종합 분석하기
대량의 데이터셋을 요약·통합한 경우에는 가중 평균이 효과적인 분석 도구입니다. 예를 들어 데이터를 영업사원당 월간 판매액과 월간 총 판매액으로 통합한 후, 전체 판매 흐름을 파악하기 위해 가중 평균을 구한다고 가정해 보겠습니다.
1단계: 빈 셀(예: D11)에 아래 수식을 입력합니다.
=SUMPRODUCT(C4:C9,D4:D9)/SUM(C4:C9)SUMPRODUCT 함수는 영업사원 수와 영업사원당 월간 판매액을 곱한 값들의 합을 계산하고, SUM 함수는 총 영업사원 수를 반환합니다.
2단계: Enter 키를 누르면 가중 평균이 계산되어 표시됩니다.
방법 7: 데이터 분석 기능(Excel 365)으로 다양한 분석 실행
Excel 365에서는 데이터 분석(Analyze Data) 기능을 통해 판매 데이터에 대한 여러 분석을 한 번에 실행할 수 있습니다. 피벗 테이블 생성부터 다양한 차트 제안까지, 제공된 분석 옵션 중 원하는 것을 골라 바로 적용할 수 있습니다.
실행 방법: 원하는 범위를 선택한 후 홈 탭 > 분석 그룹의 데이터 분석을 클릭합니다. 창이 열리면 제시된 분석 옵션 중 하나를 선택하여 데이터를 분석하세요. 앞서 설명한 엑셀 기능들도 이 창에서 함께 활용할 수 있습니다.
방법 8: 추세선으로 연간 판매 흐름 분석하기
연간 누적 판매 데이터를 분석할 때는 꺾은선형 차트를 활용하면 판매 추세를 직관적으로 확인할 수 있습니다. 아래 화면은 연간 판매 데이터를 보여줍니다.
실행 방법: 전체 데이터셋을 선택한 후 삽입 탭 > 차트 그룹의 2차원 꺾은선형을 선택합니다.
🔺 아래와 같이 판매 추세를 보여주는 꺾은선형 차트가 삽입됩니다.
방법 9: 데이터 정렬로 최대·최소값 찾기
데이터 정렬은 판매 데이터를 빠르게 파악할 수 있는 간편한 방법입니다. 정렬 자체는 특정 통계값을 반환하지 않지만, 최대값이나 최소값을 신속하게 찾는 데 유용한 요령입니다.
1단계: 홈 탭 > 편집 그룹의 정렬 및 필터 > 사용자 지정 정렬을 클릭합니다.
⧭ 정렬 시 반드시 전체 범위를 선택해야 합니다. 그렇지 않으면 값과 열/행 머리글이 어긋날 수 있습니다.
2단계: 정렬 대화 상자에서 아래 이미지처럼 정렬 기준 항목을 지정한 후 확인을 클릭합니다.
3단계: 확인을 누르면 지정한 조건대로 데이터가 정렬됩니다.
방법 10: 데이터 분석 도구로 기술 통계 확인하기
엑셀은 데이터 탭에서 데이터 분석 기능을 제공합니다. 이 기능에는 다양한 분석 도구가 포함되어 있으며, 그중 하나가 기술 통계(Descriptive Statistics)입니다.
1단계: 기술 통계를 실행하려면 데이터 탭 > 분석 그룹의 데이터 분석을 클릭합니다.
2단계: 데이터 분석 창이 나타나면 기술 통계 분석 도구를 선택한 후 확인을 누릅니다.
3단계: 기술 통계 대화 상자에서 데이터와 분석 목적에 맞게 옵션을 입력하거나 선택한 후 확인을 클릭합니다.
🔺 그러면 판매 데이터를 정리한 월간 수익 통계가 자동으로 생성됩니다.
평균(Mean), 중앙값(Median), 최대값(Maximum), 최소값(Minimum) 등 다양한 통계 지표로 판매 데이터를 종합적으로 파악할 수 있습니다.
※ 참고: 데이터 분석 메뉴가 보이지 않는다면 파일 > 옵션 > 추가 기능에서 '분석 도구' 추가 기능을 활성화해야 합니다.
마무리
이번 글에서는 엑셀에서 판매 데이터를 분석하는 다양한 방법을 소개했습니다. 데이터 유형에 따라 두세 가지 방법만으로도 충분히 분석이 가능합니다. 위에서 설명한 방법들 중 여러분의 데이터에 맞는 방법을 찾으시길 바랍니다. 추가 질문이나 보완할 내용이 있다면 댓글로 남겨주세요.
함께 읽으면 좋은 글
- 엑셀에서 리커트 척도 데이터 분석하는 방법
- 엑셀로 설문지 정성 데이터 분석하기
- 엑셀에서 시간 기반 데이터 분석하는 방법
- 엑셀에서 정성 데이터 쉽게 분석하는 방법