모든 비즈니스에서는 회사의 상태를 평가하기 위해 데이터 분석이 필수적입니다. 데이터 분석을 통해 잠재적 리스크를 사전에 파악하고, 손실을 줄이기 위한 적절한 조치를 취할 수 있습니다. 엑셀에서는 몇 가지 강력한 기능만 활용하면 이러한 작업을 손쉽게 수행할 수 있습니다. 이번 글에서는 엑셀에서 분석을 위해 데이터를 입력하는 2가지 쉬운 방법을 단계별 설명과 함께 자세히 소개하겠습니다.
아래 무료 엑셀 파일을 다운로드하여 직접 실습해볼 수도 있습니다.
엑셀에서 분석용 데이터를 입력하는 2가지 방법
먼저 이번 글에서 사용할 데이터셋을 살펴보겠습니다. 이 데이터셋은 지역별 연간 매출과 수익 정보를 담고 있습니다.
1. 피벗 테이블(Pivot Table)을 활용한 데이터 분석
데이터를 표 형식으로 정리하면 관리와 분석이 훨씬 수월해집니다. 엑셀의 피벗 테이블 기능은 분석을 위해 데이터를 재구성할 때 매우 유용한 도구입니다. 여기서는 지역별 수익을 분석해 보겠습니다.
실행 단계:
- 먼저 데이터셋 안의 임의의 셀 하나를 클릭합니다.
- 그다음 삽입 > 피벗 테이블을 차례로 클릭합니다.
- 대화상자가 나타나면 워크시트 옵션을 선택합니다. 그러면 데이터 범위가 자동으로 지정됩니다. 여기서는 새 워크시트를 선택했습니다.
- 이후 확인 버튼을 누릅니다.
워크시트 오른쪽에 피벗 테이블 필드 창이 나타납니다.
- 지역별 수익을 분석하려면 Region(지역) 헤더를 행 영역으로, Profit(수익) 헤더를 값 영역으로 드래그합니다.
이제 피벗 테이블이 각 지역별 수익 합계를 보여줍니다. 덕분에 어떤 지역이 가장 높은 수익을 내고 있고, 어느 지역의 수익이 낮은지 한눈에 파악할 수 있습니다.
2. 데이터 분석 추가 기능(Data Analysis Add-in)을 활용한 데이터 분석
엑셀의 기본 기능 외에도 공식 추가 기능과 서드파티 추가 기능을 활용하면 더욱 전문적인 데이터 분석이 가능합니다. Analysis ToolPak(분석 도구 팩)은 대표적인 공식 추가 기능입니다. 이번에는 이 추가 기능을 사용해 데이터를 정리하고 분석하는 방법을 알아보겠습니다. 먼저 데이터 분석 추가 기능을 활성화하는 과정부터 살펴보겠습니다. 이미 활성화되어 있다면 해당 단계는 건너뛰어도 됩니다.
실행 단계:
- 홈 리본 옆에 있는 파일 탭을 클릭합니다.
- 메뉴가 열리면 옵션을 선택합니다.
- 그다음 추가 기능 > Excel 추가 기능 > 이동을 차례로 클릭합니다.
- 추가 기능 대화상자가 나타나면 Analysis ToolPak에 체크하고 확인을 누릅니다.
① 기술 통계(Descriptive Statistics)
먼저 데이터 분석 ToolPak의 기술 통계 기능을 사용해 통계 요약 보고서를 만드는 방법을 알아보겠습니다.
실행 단계:
- 데이터 리본을 클릭하고 분석 섹션에서 데이터 분석 명령을 선택합니다.
곧바로 데이터 분석 대화상자가 나타납니다.
- 도구 목록에서 기술 통계(Descriptive Statistics)를 선택하고 확인을 누릅니다.
- 여기서는 Profit(수익) 열을 기준으로 요약 통계를 작성하겠습니다. 입력 범위에 Profit 열의 범위를 지정합니다.
- 출력 범위 옵션이 여러 개 표시되는데, 여기서는 새 워크시트를 선택했습니다.
- 마지막으로 하단 섹션에서 원하는 통계 옵션에 체크하고 확인을 누릅니다. 여기서는 요약 통계에 체크했습니다.
잠시 후 아래 이미지처럼 새 시트에 결과 보고서가 생성됩니다. 평균(Mean), 중앙값(Median), 합계(Sum), 개수(Count) 등 유용한 통계 지표가 함께 포함되어 있어 데이터의 전반적인 특징을 빠르게 파악할 수 있습니다.
② 히스토그램(Histogram)
히스토그램 차트는 특정 범위 내 값들의 분포를 시각적으로 보여주는 도구입니다. 데이터 분석 추가 기능을 사용해 히스토그램을 만드는 방법을 알아보겠습니다.
실행 단계:
- 이전 섹션의 첫 번째 단계와 동일하게 데이터 분석 대화상자를 엽니다.
- 마찬가지로 Profit 열에서 입력 범위를 선택하고 출력 옵션을 지정합니다.
- 마지막으로 원하는 차트 옵션에 체크하고 확인을 누릅니다. 여기서는 차트 출력에 체크했습니다.
새 시트에 차트와 함께 히스토그램 결과가 생성됩니다. 이를 통해 값들이 어떤 구간에 얼마나 자주 분포하는지 쉽게 확인할 수 있습니다.
③ 회귀 분석(Regression)
데이터 분석 ToolPak을 활용하면 엑셀에서 회귀 분석도 손쉽게 수행할 수 있습니다.
실행 단계:
- 앞선 방법과 동일하게 데이터 분석 대화상자를 엽니다.
- 목록에서 회귀 분석(Regression) 도구를 선택하고 확인을 누릅니다.
- 여기서는 입력 Y 범위에 Profit 열을, 입력 X 범위에 Sales 열을 선택했습니다. 수익을 기준으로 매출을 예측하고자 하기 때문입니다.
- 마지막으로 출력 옵션을 선택하고 확인을 누릅니다. 여기서도 새 워크시트를 설정했습니다.
잠시 후 아래 이미지와 같은 회귀 분석 요약 결과를 확인할 수 있습니다.
민감도 분석(Sensitivity Analysis)을 위한 데이터 입력 방법
민감도 분석은 주어진 데이터를 바탕으로 다양한 예측 결과를 도출하는 기법입니다. 엑셀의 데이터 표 기능을 활용한 민감도 분석 방법을 알아보기 위해, 제품의 판매 수량·단가·단위당 비용이 담긴 데이터셋을 준비했습니다. 민감도 분석을 적용해 목표 수익을 달성하기 위한 조건들을 구해보겠습니다.
실행 단계:
- 먼저 C7 셀에 아래 수식을 입력해 수익을 계산합니다.
=(C5-C6)*C4
- 그다음 아래 이미지처럼 새 표에 수익을 알고 싶은 조건값들을 입력합니다.
- 새 데이터가 교차하는 셀에 앞서 계산한 수익 값을 넣습니다.
- 새 데이터 범위를 선택한 후 데이터 > 예측 > 가상 분석 > 데이터 표를 차례로 클릭합니다.
- 행 입력 셀 상자에는 C4 셀을, 열 입력 셀 상자에는 C5 셀을 지정합니다.
- 그다음 확인을 누릅니다.
이제 표가 새로운 조건에 따른 수익을 자동으로 계산해 보여줍니다. 이를 통해 다양한 시나리오를 손쉽게 비교할 수 있습니다.
결론
지금까지 엑셀에서 분석을 위해 데이터를 입력하고 활용하는 다양한 방법을 살펴보았습니다. 피벗 테이블과 데이터 분석 추가 기능, 그리고 민감도 분석까지 익혀두면 업무에서 데이터 기반 의사결정을 훨씬 빠르게 내릴 수 있습니다. 궁금한 점이 있다면 댓글로 남겨주세요. 소중한 피드백도 환영합니다.
관련 글
- 엑셀에서 설문조사 질적 데이터 분석하는 방법
- 엑셀에서 질적 데이터 분석하기 (쉬운 단계별 가이드)
- 엑셀에서 qPCR 데이터 분석 방법 (2가지 쉬운 방법)
- 엑셀 데이터 분석 기능으로 사례 연구 수행하기
- 엑셀에서 리커트 척도 데이터 분석 방법 (빠른 단계별 가이드)
- 엑셀에서 시계열 데이터 분석하기 (쉬운 단계별 가이드)
- 엑셀에 데이터 분석 기능 설치하는 방법