Excel에서 원시 데이터 분석하는 방법 9가지 (단계별 완벽 가이드)

Excel에서 원시 데이터를 분석하는 방법을 찾고 계신가요? 그렇다면 잘 찾아오셨습니다. 이 글에서는 Excel의 다양한 기능을 활용해 원시 데이터를 분석할 수 있는 9가지 방법을 단계별로 자세히 소개합니다.

글 말미에 제공되는 예제 파일을 내려받아 직접 따라 해볼 수도 있습니다.

Excel에서 원시 데이터를 분석하는 9가지 방법

여기서는 한 회사의 제품(Product), 월(Month), 매출(Sales) 정보가 담긴 데이터셋을 예시로 사용합니다. 이제 이 원시 데이터를 Excel에서 분석하는 9가지 방법을 하나씩 살펴보겠습니다.

1. 정렬 및 필터로 원시 데이터 분석하기

첫 번째 방법은 정렬(Sort)과 필터(Filter)를 활용해 원시 데이터를 분석하는 것입니다. 아래 단계를 따라 해보세요.

단계:

• 먼저 필터를 적용할 필드를 선택합니다. 여기서는 C4 셀을 선택합니다.
• 그다음 데이터 탭 → 정렬 및 필터 → 필터를 클릭합니다.

• 나타난 드롭다운 버튼을 클릭합니다.

• 모두 선택 옵션을 해제하고 January(1월)만 선택합니다.
• 확인을 클릭합니다.

• 이제 1월 매출 데이터만 남은 필터링된 결과를 확인할 수 있습니다.

• 추가로 매출 값을 정렬하려면 아래와 같이 드롭다운 버튼을 클릭합니다.

• 오름차순 정렬(Sort Smallest to Largest)을 선택합니다.

• 이렇게 하면 원시 데이터를 손쉽게 정렬하고 필터링하여 분석할 수 있습니다.

2. 조건부 서식으로 원시 데이터 분석하기

다음으로 조건부 서식(Conditional Formatting)을 활용해 원시 데이터를 분석하는 방법을 알아보겠습니다.

단계:

• 먼저 B5:D13 셀 범위를 선택합니다.
• 홈 탭 → 조건부 서식을 클릭합니다.

• 상위/하위 규칙(Top/Bottom Rules) → 하위 10개 항목(Bottom 10 Items)을 선택합니다.

• 대화상자가 열리면 값 입력란에 2를 입력하고 서식으로 연한 빨간색 채우기와 진한 빨간색 텍스트를 선택합니다.
• 확인을 클릭합니다.

• 이제 데이터셋에서 가장 낮은 매출 2개가 강조 표시되어 한눈에 파악할 수 있습니다.

• 같은 방식으로 상위 항목 강조, 데이터 막대, 색조 등 다양한 조건부 서식을 활용해 원시 데이터를 분석할 수도 있습니다.

3. 가상 분석(What-If Analysis)으로 데이터 검토하기

3개월간의 매출 데이터가 있고, 4개월 목표 매출액이 정해져 있다고 가정해 보겠습니다. Excel의 가상 분석(What-If Analysis) 기능을 사용하면 목표 달성에 필요한 나머지 한 달의 매출액을 역산할 수 있습니다.

단계:

• 먼저 현재 총 매출을 계산하기 위해 C9 셀을 선택하고 아래 수식을 입력합니다.

• Enter 키를 누릅니다.

여기서 SUM 함수는 C5:C8 범위의 값을 모두 더해 4개월치 총 매출을 구합니다.

• 이제 데이터 탭 → 예측(Forecast) → 가상 분석(What-If Analysis) → 목표값 찾기(Goal Seek)를 클릭합니다.

• 목표값 찾기 대화상자에서 값을 바꿀 셀(Set Cell)에 C9, 목표값(To value)에 60000, 변수 셀(By changing cell)에 C8을 입력합니다.
• 확인을 클릭합니다.

• 결과 창이 뜨면 다시 확인을 클릭합니다.

• 이렇게 하면 목표 매출 달성에 필요한 나머지 월의 매출액이 자동으로 계산됩니다.

4. 데이터 분석(Analyze Data) 기능으로 원시 데이터 스캔하기

Excel의 데이터 분석(Analyze Data) 기능을 사용하면 원시 데이터를 자동으로 스캔하고 인사이트를 얻을 수 있습니다.

단계:

• 먼저 B4:D13 셀 범위를 선택합니다.
• 홈 탭 → 데이터 분석(Analyze Data)을 클릭합니다.

• 데이터 분석 창이 열리면 데이터셋을 기반으로 미리 생성된 다양한 피벗 테이블(PivotTable)과 피벗 차트(PivotChart) 제안을 볼 수 있습니다. 필요한 것을 골라 삽입하면 됩니다.
• 여기서는 제품·월별 매출(Sales by Product and Month) 피벗 테이블을 삽입해 보겠습니다.
• PivotTable 삽입(Insert PivotTable)을 클릭합니다.

• 이처럼 데이터 분석 기능으로 다양한 피벗 테이블 또는 피벗 차트를 손쉽게 만들 수 있습니다.

• 또한 추천(Suggested) 옵션을 통해 데이터를 분석할 수도 있습니다.
• 데이터에 대해 질문하기(Ask a Question about your data) 입력창을 클릭합니다.
• 여러 추천 옵션이 나타나면 필요한 항목을 선택합니다.
• 여기서는 총 매출 기준 상위 3개 제품(Top 3 'Product' by total 'Sales') 옵션을 클릭합니다.

• 미리보기 피벗 테이블이 나타납니다.
• PivotTable 삽입을 클릭합니다.

• 마지막으로 새 워크시트에 피벗 테이블이 추가된 것을 확인할 수 있습니다.

5. Excel 표(Table)를 만들어 원시 데이터 조사하기

다섯 번째 방법은 Excel에서 표(Table)를 만들어 원시 데이터를 조사하는 것입니다.

단계:

• 먼저 B4:D13 셀 범위를 선택합니다.
• 키보드 단축키 Ctrl + T를 누릅니다.

• 표 만들기(Create Table) 대화상자가 열리면 셀 범위가 이미 지정되어 있는지 확인합니다.
• 머리글 포함(My table has headers) 옵션을 체크합니다.
• 확인을 클릭합니다.

• 이제 원시 데이터가 표로 변환되었으며, 이 표를 활용해 데이터를 분석할 수 있습니다.

예를 들어 $3800보다 큰 매출만 필터링한 뒤 오름차순으로 정렬하고 싶다면 다음과 같이 하면 됩니다.

• Sales 필드 옆의 드롭다운 버튼을 클릭합니다.
• 숫자 필터(Number Filter) → 보다 큼(Greater Than)을 선택합니다.

• 자동 필터 사용자 지정(Custom Autofilter) 대화상자가 열립니다.
• 드롭다운에서 ~보다 큼(is greater than)을 선택하고 입력란에 3800을 입력합니다.
• 확인을 클릭합니다.

• 이제 표를 사용해 데이터가 필터링됩니다.

• 다음으로 매출 값을 정렬하려면 드롭다운 버튼을 클릭합니다.
• 오름차순 정렬을 선택합니다.

• 마지막으로 표를 통해 매출 값이 정렬된 것을 확인할 수 있습니다.

6. Power Query 편집기로 데이터 분석하기

이번에는 Power Query 편집기를 활용해 원시 데이터를 분석하는 방법을 살펴보겠습니다.

단계:

• 먼저 B4:D13 셀 범위를 선택합니다.
• 데이터 탭 → 표/범위에서(From Table/Range)를 클릭합니다.

• 표 만들기 대화상자가 열리면 셀 범위를 확인합니다.
• 머리글 포함 옵션을 체크합니다.
• 확인을 클릭합니다.

• 그러면 Power Query 편집기에서 해당 표가 열립니다.

• 제품을 필터링하려면 열 머리글의 드롭다운 버튼을 클릭합니다.

• 모두 선택을 해제하고 Printer만 선택합니다.
• 확인을 클릭합니다.

• 닫기 및 로드(Close & Load) → 닫기 및 로드 대상...(Close & Load To)을 선택합니다.

• 데이터 가져오기(Import Data) 대화상자가 열립니다.
• 기존 워크시트(Existing worksheet)를 선택합니다.
• 데이터를 배치할 위치로 C15 셀을 지정합니다.
• 확인을 클릭합니다.

• 이제 Power Query 편집기를 통해 필터링된 데이터가 기존 워크시트에 로드된 것을 확인할 수 있습니다.

7. 함수와 Excel 차트로 원시 데이터 조사하기

다양한 함수로 원시 데이터를 집계한 뒤 Excel 차트로 시각화할 수도 있습니다. 여기서는 SUMIF 함수로 데이터를 분석하고 막대 차트(Bar Chart)를 만드는 과정을 보여드리겠습니다.

단계:

• 먼저 G5 셀을 선택하고 아래 수식을 입력합니다.

• Enter 키를 누른 후, 채우기 핸들(Fill Handle)을 아래로 끌어 나머지 셀에도 수식을 자동으로 채웁니다(AutoFill).

여기서 SUMIF 함수는 C5:C13을 조건 범위(range)로, F5 셀을 조건(criteria)으로, D5:D13을 합계 범위(sum_range)로 지정한 것입니다.

• 이제 각 제품별 3개월 총 매출이 계산됩니다.

• 이 값으로 막대 차트를 만들려면 F4:G7 셀 범위를 선택합니다.
• 삽입 탭 → 세로 막대형 또는 가로 막대형 차트 삽입을 클릭합니다.

• 묶은 세로 막대형(2-D Clustered Column) 차트를 선택합니다.

• 이렇게 함수와 차트를 활용하면 원시 데이터를 효과적으로 분석하고 시각화할 수 있습니다.

8. 데이터 분석 도구(Data Analysis ToolPak)로 데이터 분석하기

다음은 Excel 추가 기능 프로그램인 데이터 분석(Data Analysis) 도구를 활용해 원시 데이터를 분석하는 방법입니다.

단계:

• 먼저 데이터 탭 → 데이터 분석(Data Analysis)을 선택합니다.

• 데이터 분석 대화상자가 열리면 원하는 분석 도구를 선택합니다. 여기서는 히스토그램(Histogram)을 선택합니다.
• 확인을 클릭합니다.

• 히스토그램 대화상자가 나타나면 입력 범위(Input Range)에 D5:D13을 입력합니다.
• 출력 범위(Output Range) 옵션을 체크하고 F4:I13을 입력합니다.
• 차트 출력(Chart Output) 옵션을 체크합니다.
• 확인을 클릭합니다.

• 이제 데이터 분석 도구로 생성된 데이터셋의 히스토그램을 확인할 수 있습니다.

9. 데이터 유효성 검사로 원시 데이터 분석하기

마지막 방법은 Excel의 데이터 유효성 검사(Data Validation) 기능을 활용하는 것입니다.

단계:

• 먼저 H4:H7 셀 범위에 제품 이름들을 입력합니다.
• 이 범위를 선택한 상태에서 이름 상자(Name Box)에 Product라고 입력합니다.
• Enter 키를 누릅니다.

• 이제 F4 셀을 선택합니다.
• 데이터 탭 → 데이터 도구(Data Tools) → 데이터 유효성 검사(Data Validation) → 데이터 유효성 검사를 클릭합니다.

• 데이터 유효성 검사 대화상자에서 제한 대상(Allow) 드롭다운에서 목록(List)을 선택하고, 원본(Source)에 앞서 만든 이름 범위 Product를 입력합니다.
• 확인을 클릭합니다.

• 생성된 드롭다운 버튼을 클릭합니다.
• 원하는 제품을 선택합니다. 여기서는 Television을 선택합니다.

• 이어서 F5 셀을 선택하고 아래 수식을 입력합니다.

• Enter 키를 누릅니다.

여기서 SUMIF 함수는 B5:B13을 조건 범위로, F4 셀을 조건으로, C5:C13을 합계 범위로 지정하여 선택한 제품의 매출 합계를 구합니다.

• 이것으로 Excel에서 원시 데이터를 쉽게 분석하는 다양한 방법을 모두 살펴보았습니다!

연습 섹션

이 글에서 소개한 내용을 직접 연습해볼 수 있도록 아래 이미지와 같은 Excel 통합 문서를 함께 제공합니다.

결론

지금까지 Excel에서 원시 데이터를 분석하는 다양한 방법을 알아보았습니다. 이 글이 도움이 되었기를 바랍니다. 이해하기 어려운 부분이 있다면 댓글로 질문을 남겨주세요. 또한 놓친 다른 방법이 있다면 알려주시기 바랍니다. 더 많은 유용한 팁은 ExcelDemy에서 확인하세요. 감사합니다!