엑셀로 지역별 분기 매출 보고서 만들기 – 단계별 실전 가이드

이 글에서는 지역별 분기별 매출을 한눈에 보여주는 보고서를 엑셀로 만드는 방법을 소개합니다. 흔히 말하는 다이내믹하고 인터랙티브한 엑셀 대시보드이며, 데이터가 업데이트되면 결과도 자동으로 갱신되도록 구성할 수 있습니다.

아래는 이 가이드를 모두 따라 하면 완성되는 최종 보고서의 모습입니다.

실습에 사용된 워크북 파일을 다운로드할 수 있으니, 본문의 단계를 직접 따라 해보시길 권장합니다.

엑셀에서 지역별 분기 매출 보고서를 만드는 단계별 절차

설명을 위해 아래와 같은 데이터 세트를 사용하겠습니다.

이 데이터에는 날짜별 매출이 담겨 있는데, 엑셀의 표(Table) 기능과 피벗 테이블(Pivot Table) 기능을 활용하면 이를 분기 단위로 재구성할 수 있습니다.

1단계: 데이터를 표(Table)로 변환하기

데이터가 아직 표 형식이 아니라면 먼저 범위를 표로 변환하세요. 엑셀 표 기능은 참조, 필터링, 정렬, 데이터 업데이트 등 다양한 작업을 훨씬 쉽게 만들어 주는 엑셀의 대표적인 강력한 기능입니다.

• 표로 변환할 범위 안의 임의의 셀을 선택한 뒤, 키보드에서 Ctrl+T를 누릅니다. 또는 삽입 탭 → 표 그룹에서 테이블 옵션을 클릭해도 됩니다.

• 그러면 테이블 만들기(Create Table) 대화상자가 나타납니다. 범위는 자동으로 선택되며 '머리글 포함' 체크박스가 활성화되어 있습니다. 그대로 확인 버튼만 클릭하면 됩니다.

이제 데이터 세트가 하나의 표로 변환됩니다.

2단계: 표 이름 지정하기

다음으로 표에 이름을 붙여 두겠습니다. 이렇게 하면 이후 작업이 훨씬 수월해집니다.

표 이름은 디자인 탭 또는 화면 좌측 상단의 이름 상자(Name Box)에서 변경할 수 있습니다. 여기서는 표 이름을 Data로 지정했습니다.

3단계: 피벗 테이블 만들기

보고서 작성에는 엑셀에서 가장 널리 사용되는 도구인 피벗 테이블을 활용합니다. 다음 순서대로 진행하세요.

• 먼저 표 내부의 셀 하나를 선택합니다.
• 삽입 탭으로 이동한 뒤, 표 그룹에서 피벗 테이블 명령을 클릭합니다.

• 피벗 테이블 만들기 대화상자가 열립니다. 명령을 실행하기 전에 표 안의 셀을 선택했기 때문에, 테이블/범위 입력란에 우리가 만든 표 이름(Data)이 자동으로 입력됩니다.
• 새 워크시트에 피벗 테이블을 배치할 것이므로, '피벗 테이블 보고서를 배치할 위치 선택' 항목에서 기본값인 새 워크시트를 유지합니다.
• 확인을 클릭합니다.

새 워크시트가 생성되고, 피벗 테이블 필드 작업창이 자동으로 표시됩니다.

4단계: 카테고리별 피벗 테이블 보고서 준비하기

먼저 카테고리(Category)별 매출 보고서를 만들고, 이어서 원형 차트를 추가하겠습니다. 피벗 테이블 필드를 아래와 같이 배치합니다.

아래 이미지를 잘 살펴보세요. 매출(Sales) 필드를 값 영역에 두 번 넣었습니다. 그래서 열 영역에 추가 값 필드가 표시됩니다. 그리고 행 영역에는 카테고리 필드를 배치했습니다.

이미지 왼쪽에 보이는 것이 위 설정에 따른 피벗 테이블의 출력 결과입니다.

• 이제 매출 값의 표시 형식을 총합계 대비 비율(%)로 바꿔 보겠습니다. 해당 열의 셀을 마우스 오른쪽 버튼으로 클릭합니다.
• 컨텍스트 메뉴에서 값 표시 방법을 선택합니다.
• 이어서 총합계의 비율(% of Grand Total) 명령을 클릭합니다.

이제 열의 값들이 총합계 대비 백분율로 표시됩니다.

5단계: 카테고리 보고서용 원형 차트 만들기

데이터를 시각적으로 전달하기 위해 원형 차트(파이 차트)를 추가해 보겠습니다.

• 먼저 피벗 테이블 내부의 셀을 하나 선택합니다.
• 삽입 탭으로 이동한 뒤, 차트 그룹에서 원형 차트 아이콘을 클릭합니다.
• 드롭다운 목록에서 원형(Pie) 차트를 선택합니다.

워크시트에 원형 차트가 나타납니다.

약간의 서식 수정 후 차트는 아래와 같은 모습이 됩니다.

원형 차트에 카테고리 이름과 데이터 레이블 표시하기

데이터 레이블은 다음 순서로 추가할 수 있습니다.

• 먼저 원형 차트를 선택합니다.
• 디자인 탭의 차트 레이아웃 그룹에서 빠른 레이아웃을 클릭합니다.
• 드롭다운 목록에서 레이아웃 1(Layout 1) 옵션을 선택합니다.

대체 방법:

차트에 데이터 레이블을 넣는 또 다른 창의적인 방법은 GETPIVOTDATA 함수를 활용하는 것입니다. 이 함수로 피벗 테이블에서 데이터를 가져올 수 있습니다.

아래는 우리 데이터로 만든 피벗 테이블입니다. 이 표는 매출 합계(Sum of Sales)를 주(State)와 카테고리(Category) 기준으로 보여줍니다.

주 필드는 행 영역에, 카테고리 필드는 열 영역에, 매출 필드는 값 영역에 배치했습니다.

이제 엑셀의 GETPIVOTDATA 함수를 살펴보겠습니다.

GETPIVOTDATA 구문: GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

피벗 테이블에는 data_field가 하나만 있지만, 나머지 필드는 개수 제한 없이 지정할 수 있습니다.

위 피벗 테이블의 경우:

• data_field는 Sales(매출) 필드입니다.
• 나머지 두 필드는 State(주)와 Category(카테고리)입니다.

아래 이미지에서 셀 H9에 사용한 GETPIVOTDATA 수식을 확인할 수 있습니다.

=GETPIVOTDATA("Sales", A3, "State", H7, "Category", H8)

이 수식은 셀 H9에 950이라는 값을 반환합니다.

이 수식은 어떻게 작동할까요?

• data_field 인수는 당연히 Sales(매출)입니다.
• A3는 피벗 테이블 내부의 셀 참조입니다. 피벗 테이블 안의 어떤 셀이든 사용할 수 있습니다.
• field1, item1 = "State", H7. 즉, State 필드에서 H7 셀 값(Idaho)인 항목을 의미합니다.
• field2, item2 = "Category", H8. 마찬가지로 Category 필드에서 H8 셀 값(Office Supplies)인 항목을 의미합니다.
• Idaho 값과 Office Supplies 값이 교차하는 지점의 값, 즉 950이 반환됩니다.

레이블을 표시하려면:

GETPIVOTDATA 함수를 활용해 특정 셀에 카테고리 이름과 매출 비율(총액 대비 %)을 표시합니다(아래 이미지 참조).

이해를 돕기 위해 셀 D4의 수식을 설명하겠습니다.

=A4&" "&TEXT(GETPIVOTDATA("Sales", A3, "Category", A4), "0%")

• A4&" " 부분은 간단합니다. 셀 참조 뒤에 공백 하나를 붙여 출력을 만듭니다.
• 그다음 엑셀의 TEXT 함수를 사용했습니다. TEXT 함수의 value 인수로 GETPIVOTDATA 함수를 전달하고, format_text 인수로는 "0%" 형식을 지정했습니다.
• GETPIVOTDATA 부분은 앞서 설명했으므로 생략하겠습니다.

이제 이 데이터들을 차트 위에 표시하겠습니다.

삽입 탭 → 일러스트레이션 그룹 → 도형(Shapes)에서 텍스트 상자(Text Box)를 삽입합니다.

차트 위에 텍스트 상자를 놓고, 수식 입력줄에 등호(=)를 입력한 뒤 셀 D4를 클릭합니다.

Enter 키를 누르면 텍스트 상자에 셀 D4의 값이 표시됩니다.

같은 방식으로 나머지 텍스트 상자들을 만들고 해당 셀들을 참조하도록 연결합니다.

팁: 텍스트 상자 하나를 만들어 두면, 이를 복제해 새 텍스트 상자를 빠르게 만들 수 있습니다. 방법은 다음과 같습니다.

• 마우스 포인터를 만들어 둔 텍스트 상자의 테두리 위에 올린 뒤, 키보드의 Ctrl 키를 누릅니다. 플러스(+) 표시가 나타납니다.
• 이제 마우스를 드래그하면 새 텍스트 상자(개체)가 생성됩니다. 원하는 위치에 놓으면 됩니다.

이로써 카테고리별 매출을 동적으로 보여주는 원형 차트가 완성되었습니다.

마지막으로 이 피벗 테이블의 이름을 PT_CategorySales로 변경해 두겠습니다.

6단계: 분기별 매출 피벗 테이블 준비하기

경우에 따라 연도별로 분기가 바뀔 때 매출이 어떻게 변화하는지 확인하고 싶을 수 있습니다.

아래 이미지와 같은 보고서를 만들어 보겠습니다.

이 보고서는 분기별 총 매출 기준 상위 15개 미국 주를 보여주며, 각 분기의 추세를 한눈에 파악할 수 있도록 스파크라인까지 추가했습니다.

다음 순서대로 분기별 매출 피벗 테이블을 준비합니다.

• 먼저 데이터 표 안의 셀을 하나 선택합니다.
• 삽입 탭 → 표 그룹에서 피벗 테이블을 선택합니다.

• 피벗 테이블을 배치할 위치를 선택하고 확인을 클릭합니다. 이번 예제에서는 새 워크시트를 선택했습니다.

• 이제 다음과 같이 필드를 배치합니다: 주문 날짜(Order Date) 필드를 열 영역에, 주(State) 필드를 행 영역에, 매출(Sales) 필드를 값 영역에 놓습니다.

• 분기별 보고서를 표시하려면 열 레이블의 아무 셀이나 마우스 오른쪽 버튼으로 클릭한 뒤, 컨텍스트 메뉴에서 그룹화(Group)를 선택합니다.

• 그룹화 대화상자의 단위(By) 섹션에서 분기(Quarters)를 선택합니다.

• 확인을 클릭하면 피벗 테이블이 아래처럼 바뀝니다.

7단계: 매출 상위 15개 주 표시하기

이전 단계의 결과물에는 데이터 세트에 있는 모든 주의 분기별 보고서가 담겨 있습니다. 전체가 필요하다면 그대로 사용해도 되지만, 더 심층적인 분석을 위해 상위 주만 필요하다면 아래 방법이 유용합니다.

• 먼저 주(State) 열(또는 행 레이블)의 아무 셀이나 마우스 오른쪽 버튼으로 클릭합니다.
• 컨텍스트 메뉴에서 필터(Filter)에 마우스를 올린 뒤 상위 10개(Top 10)를 선택합니다.

• 상위 10개 필터(상태) 대화상자의 표시 옵션에서 15를 입력합니다.

• 확인을 클릭하면 피벗 테이블이 매출 기준 상위 15개 주만 표시합니다.

8단계: 표에 스파크라인 추가하기

스파크라인을 추가하기 전에 먼저 행/열 총합계를 제거하겠습니다. 자세한 방법은 다음과 같습니다.

• 먼저 피벗 테이블 안의 셀을 하나 선택합니다.
• 리본 메뉴에서 디자인 탭으로 이동합니다.
• 레이아웃 그룹에서 총합계를 선택합니다.
• 드롭다운 목록에서 행 및 열에 대해 끄기를 선택합니다.

이제 총합계 영역이 제거됩니다.

• 스파크라인을 추가하려면 셀 F5를 선택한 뒤 리본 메뉴의 삽입 탭으로 이동합니다.
• 스파크라인 그룹에서 꺾은선형(Line)을 선택합니다.

• 스파크라인 만들기 상자에서 B5:E19 범위를 데이터 범위로, F5:F19 범위를 위치 범위로 지정합니다.

• 확인을 클릭하면 피벗 테이블이 최종적으로 아래와 같은 모습이 됩니다.

• 더욱 돋보이게 만들기 위해 마커도 추가해 보겠습니다. 스파크라인이 들어 있는 셀을 선택하면 나타나는 스파크라인 탭에서 표시 그룹의 마커(Markers)를 체크합니다.

이것이 스파크라인의 최종 결과물입니다.

9단계: 슬라이서(Slicer)를 추가해 결과 필터링하기

다음의 간단한 단계로 피벗 테이블에 슬라이서를 추가할 수 있습니다.

• 먼저 슬라이서를 만들려는 피벗 테이블을 선택합니다.
• 삽입 탭으로 이동한 뒤, 필터 그룹에서 슬라이서 명령을 클릭합니다.

• 슬라이서 삽입 대화상자에 피벗 테이블의 사용 가능한 모든 필드가 나타납니다. 슬라이서를 만들 필드를 선택합니다. 여기서는 데모를 위해 고객 이름(Customer Name), 주(State), 카테고리(Category) 필드를 선택했습니다.

• 확인을 클릭하면 스프레드시트 상단에 3개의 슬라이서가 나타납니다.

10단계: 최종 보고서 완성하기

지금까지 만든 요소들을 하나의 스프레드시트에 모아 최종 보고서를 완성하겠습니다.

이제 슬라이서에서 항목을 선택하거나 해제하면 결과가 실시간으로 변경됩니다. 예를 들어 주(State) 슬라이서에서 Arizona를 선택하면 해당 주의 데이터만 보고서에 반영됩니다.

여러 항목을 동시에 선택할 수도 있습니다. 예를 들어 여기에 Alabama를 추가하면 아래와 같이 표시됩니다. 이것으로 지역별 분기 매출 보고서 만들기를 마칩니다.

마무리

지금까지 엑셀에서 지역별 분기 매출 보고서를 만드는 데 필요한 모든 단계를 살펴봤습니다. 이 가이드를 따라 하면 누구나 손쉽게 직접 만들 수 있을 것입니다. 도움이 되었기를 바라며, 궁금한 점이나 제안 사항이 있다면 댓글로 알려주세요.

함께 읽으면 좋은 글

• 엑셀로 경비 보고서 만들기 (쉬운 단계별 가이드)
• 엑셀로 수입·지출 보고서 만들기 (예제 3가지)
• 엑셀 VBA로 PDF 형식 보고서 생성하기 (빠른 팁 3가지)
• 엑셀로 생산 보고서 만들기 (대표적인 2가지 방식)
• 엑셀로 일일 활동 보고서 만들기 (예제 5가지)
• 엑셀 일일 생산 보고서 만들기 (무료 템플릿 다운로드)
• 엑셀로 성적표 만들기 (무료 템플릿 다운로드)