이 글에서는 엑셀에서 대용량 데이터 세트를 효율적으로 분석하는 방법을 소개합니다. 비즈니스 활동의 장단점을 파악하는 것은 매우 중요하며, 조직의 계정과 판매 기록을 관리하는 것은 지속적으로 이루어지는 동적인 과정입니다. 판매, 구매, 교환이 짧은 기간에도 수시로 발생하기 때문에, 최근 3~4개월간의 매출이나 수익 기록을 확인하려면 방대한 양의 데이터를 다뤄야 할 수 있습니다. 다행히 엑셀에는 이러한 작업을 도와주는 강력한 기능들이 있습니다. 대용량 데이터를 부분별로 나누어 분석하면 계산 작업을 훨씬 수월하게 처리할 수 있습니다.
엑셀에서 대용량 데이터 세트를 분석하는 6가지 방법
이 글에서는 아래와 같은 데이터 세트를 예시로 사용합니다. 실제 데이터 양은 많지 않지만, 이를 통해 대용량 데이터 세트를 분석하는 방법을 충분히 익힐 수 있습니다. 이 데이터 세트에는 일부 전자 제품에 대한 판매 정보가 담겨 있습니다.
데이터를 분석할 때는 테이블(Table) 형식으로 변환해 두면 편리합니다. 데이터를 테이블로 변환하는 방법은 다음과 같습니다.
- 먼저 데이터 범위를 선택한 후 삽입 >> 테이블로 이동합니다.
- 대화 상자가 나타나면 '머리글 포함' 옵션에 체크되어 있는지 확인합니다.
- 마지막으로 확인을 클릭합니다.
이 과정을 거치면 데이터 세트가 엑셀 테이블로 변환됩니다.
1. 피벗 테이블(Pivot Table)로 대용량 데이터 분석하기
엑셀에서 대용량 데이터를 분석하는 가장 대표적인 방법 중 하나가 삽입 탭의 피벗 테이블입니다. 피벗 테이블을 사용하면 필요한 행과 열을 기준으로 제품 판매 정보를 한눈에 파악할 수 있으며, 월별 또는 연도별로도 볼 수 있습니다. 아래 순서를 따라 해 보세요.
실행 단계:
- 먼저 데이터 테이블을 선택하고 삽입 >> 피벗 테이블로 이동합니다.
- 대화 상자가 나타나면 새 워크시트 또는 기존 워크시트 중 원하는 위치를 선택합니다.
- 여기서는 새 워크시트를 선택했으므로, 새 시트에서 피벗 테이블 필드 영역을 찾을 수 있습니다. 필요한 범위(날짜, 가격, 판매자 이름 등)를 피벗 테이블 필드로 드래그하면 판매 정보 요약이 표시됩니다.
- 예제에서는 날짜 범위를 행 필드로 드래그했습니다. 그러면 월(Month) 범위가 자동으로 생성되어 월별 판매 정보를 확인할 수 있습니다.
- 다음으로 판매자 이름을 열 필드에 추가했습니다. 어떤 직원이 가장 많은 제품을 판매했는지 파악하여 보너스를 지급하는 등의 활용이 가능하기 때문입니다.
- 마지막으로 값 필드에 가격 범위를 드래그하여 해당 기간의 총 매출액을 확인했습니다.
피벗 테이블을 완성하면 아래와 같은 정보를 확인할 수 있습니다. 선택한 필드에 따라 월별 총 매출, 각 판매자별 매출액, 기간 말 기준 총합계 등이 한눈에 들어옵니다.
- 날짜 단위로도 정보를 볼 수 있습니다. 월 이름 옆의 더하기(+) 아이콘을 클릭하기만 하면 됩니다.
이처럼 피벗 테이블을 활용하면 대용량 데이터 세트를 손쉽게 분석할 수 있으며, 특히 매일 쌓이는 판매 데이터를 다룰 때 매우 유용합니다.
2. 필터 명령으로 대용량 데이터 분석하기
대용량 데이터를 분석하는 또 다른 방법은 필터 명령을 활용하는 것입니다. 설정한 기준에 따라 원하는 정보만 골라볼 수 있습니다.
실행 단계:
- 먼저 데이터 범위를 선택한 후 홈 >> 정렬 및 필터 >> 필터로 이동합니다.
- 그러면 각 열 머리글에 필터 아이콘이 나타납니다.
- 필터 명령을 사용하면서 부가세(VAT)가 포함된 총 매출까지 함께 보고 싶다면, 아래 수식을 활용하세요.
=SUBTOTAL(9,Table13[Net Price])
이 수식은 SUBTOTAL 함수를 사용하여 필터링된 데이터의 순매출 합계를 반환합니다.
- 이제 원하는 기준으로 필터링해 봅니다. 먼저 월별 필터링 방법입니다. 날짜 머리글 옆의 드롭다운 아이콘을 클릭하고 하나 또는 여러 개의 월을 체크합니다.
- 5월을 체크하고 확인을 누르면 해당 월의 판매 정보와 함께 그 달의 부가세 포함 총 매출도 확인할 수 있습니다.
- 마찬가지로 특정 판매자의 매출 실적을 알고 싶다면 해당 이름을 체크하고 확인을 클릭합니다.
- 그러면 엑셀 시트에 해당 판매자의 판매 정보가 표시됩니다.
이렇게 필터 명령을 사용하면 대용량 데이터 세트를 원하는 기준으로 빠르게 분석할 수 있습니다.
3. 파워 쿼리 편집기(Power Query Editor) 활용하기
파워 쿼리 편집기는 엑셀에서 대용량 데이터를 분석할 때 매우 유용한 도구입니다. 아래 과정을 따라 해 보세요.
실행 단계:
- 먼저 데이터 테이블을 선택한 후 데이터 >> 테이블/범위에서로 이동합니다.
- 그러면 파워 쿼리 편집기에서 데이터 세트를 확인할 수 있습니다. 이 기능에도 필터가 있어, 판매나 직원 관련 특정 기록만 추려서 요약할 수 있습니다.
- 필수는 아니지만, 날짜 열에서 시간 부분을 제거하면 편리합니다. 절차가 간단하므로 스크린샷 없이 설명하겠습니다. 파워 쿼리 편집기의 변환 탭에서 날짜 >> 날짜만을 선택하면 됩니다.
- 이후 드롭다운 아이콘을 사용해 날짜를 필터링합니다. 개별 날짜별로도, 월별로도 필터링할 수 있습니다.
- 여기서는 월별 필터링을 선택해 5월을 골랐습니다. 사용자 지정 필터를 사용하면 특정 기간 동안의 데이터를 필터링할 수도 있으니, 여러 달 또는 특정 기간의 판매 기록을 보고 싶을 때 활용하세요.
- 그러면 5월의 판매 내역이 표시됩니다.
- 이 데이터를 엑셀 시트로 불러오려면 닫기 및 로드를 클릭합니다.
그러면 새 시트에 5월 판매 기록이 테이블 형태로 나타납니다.
같은 방법으로 파워 쿼리 편집기에서 판매자 이름이나 가격 범위로도 필터링한 후 새 시트에 불러올 수 있습니다. 이처럼 파워 쿼리 편집기를 활용하면 대용량 데이터 세트를 체계적으로 분석할 수 있습니다.
4. 피벗 차트(Pivot Chart)로 대용량 데이터 시각화하기
차트 형태로 데이터를 분석하고 싶다면 피벗 차트를 효과적으로 활용할 수 있습니다.
실행 단계:
- 먼저 위의 1번 섹션 절차를 따라 피벗 테이블을 생성합니다.
- 피벗 테이블이 있는 시트에서 피벗테이블 분석 >> 피벗 차트로 이동합니다. 이때 피벗 테이블 내 임의의 셀이 선택되어 있어야 합니다.
- 다양한 피벗 차트 유형이 표시되는데, 원하는 것을 선택하고 확인을 누릅니다. 여기서는 간단한 막대형 차트를 선택했습니다.
- 그러면 피벗 차트에 월별 총 매출이 표시됩니다.
- 추가로 날짜별 매출을 보고 싶다면, 피벗 테이블 필드에서 날짜 범위를 월 범위 위로 드래그합니다.
- 그러면 차트에 날짜별 매출이 표시됩니다.
이렇게 피벗 차트를 활용하면 대용량 데이터 세트를 시각적으로 분석할 수 있습니다. 연간 매출이나 대량의 거래 데이터를 다룰 때 피벗 차트를 사용하면 훨씬 직관적인 인사이트를 얻을 수 있습니다.
5. 파워 피벗(Power Pivot)으로 대용량 데이터 분석하기
파워 피벗 기능을 사용해서도 대용량 데이터 세트의 피벗 테이블 분석을 수행할 수 있습니다. 아래 지침을 따라 해 보세요.
실행 단계:
- 먼저 현재 통합 문서를 닫고 새 통합 문서를 연 다음, 파워 피벗 >> 관리로 이동합니다.
- 파워 피벗 창에서 홈 >> 다른 원본에서를 선택합니다.
- 테이블 가져오기 마법사가 나타나면 Excel 파일 >> 다음을 선택합니다.
- 데이터 세트가 저장된 엑셀 통합 문서를 찾아 선택합니다.
- 이후 다음을 클릭합니다.
- 해당 통합 문서의 파워 피벗 시트를 사용할 것이므로 체크한 후 마침을 클릭합니다.
- 확인 메시지가 나타나면 닫기를 클릭합니다.
- 이 작업을 마치면 선택한 시트의 데이터 세트가 파워 피벗 창에 테이블 형태로 불러와집니다.
- 불러온 데이터에는 적절한 머리글 이름이 없을 수 있습니다. 열 머리글을 마우스 오른쪽 버튼으로 클릭한 후 바로 가기 메뉴에서 열 이름 바꾸기를 선택하여 이름을 변경합니다.
- 피벗 테이블 분석을 위해 피벗 테이블 옵션을 선택합니다.
- 대화 상자가 나타나면 기존 워크시트에 피벗 테이블을 만들려면 해당 위치를 지정하고 확인을 누릅니다. 여기서는 새 워크시트에 표시되도록 선택했습니다.
- 새 시트에 피벗 테이블 필드가 나타납니다. 파워 피벗을 선택하면 날짜, 가격, VAT 등 포함된 모든 범위가 표시됩니다.
- 이후 1번 섹션의 내용을 참고하여 피벗 테이블로 데이터를 분석하면 됩니다.
- 월별 판매 정보를 차트로 시각화할 수도 있습니다. 피벗 차트를 활용한 분석 방법은 4번 섹션을 참고하세요.
6. 데이터 분석(Analyze Data) 기능 활용하기
마지막으로, 모든 데이터 분석 결과를 한 시트에서 확인하고 싶다면 데이터 탭의 데이터 분석 기능을 사용하는 것이 좋습니다. 많은 시간을 절약할 수 있습니다.
실행 단계:
- 먼저 테이블을 선택한 후 데이터 분석을 클릭합니다.
- 그러면 엑셀 시트 오른쪽에 데이터 분석 창이 열립니다.
아래로 스크롤하면 데이터를 분석할 수 있는 다양한 옵션이 나타납니다. 그중 피벗 테이블 분석 옵션도 확인할 수 있습니다.
아래 이미지는 제품의 가격과 순가격을 비교하는 막대형 차트 분석 옵션을 보여줍니다.
- 분석 예시로 제품별 순가격 차트를 선택해 보겠습니다.
그러면 제품별 순가격 변화를 보여주는 막대형 차트 분석 결과가 표시됩니다.
더 아래로 스크롤하면 다른 분석 옵션들도 확인할 수 있습니다. 원하는 옵션을 선택하여 분석을 진행하면 됩니다.
이처럼 데이터 분석 기능을 활용하면 대용량 데이터 세트를 손쉽게 분석할 수 있습니다.
연습하기
아래는 이 글에서 사용한 데이터 세트입니다. 소개한 방법들을 직접 연습해 보세요.
결론
지금까지 엑셀에서 대용량 데이터 세트를 분석하는 매우 생산적인 6가지 방법을 살펴보았습니다. 대용량 데이터를 분석하는 방법을 모르면 수작업으로 처리해야 하므로 많은 시간이 소요됩니다. 이 글에 대해 더 나은 방법이나 궁금한 점, 피드백이 있다면 댓글로 공유해 주세요. 앞으로 더 좋은 글을 작성하는 데 큰 도움이 됩니다. 추가 문의 사항은 저희 웹사이트 ExcelDemy를 방문해 주세요.
관련 글
- 엑셀에서 정성적 데이터 분석하는 방법 (쉬운 단계별 가이드)
- 엑셀에서 qPCR 데이터 분석하는 방법 (2가지 쉬운 방법)
- 엑셀 데이터 분석으로 케이스 스터디 수행하기
- 엑셀에서 텍스트 데이터 분석하는 방법 (5가지 방법)