지역별 분기별 매출을 Excel로 표시하는 보고서 만들기

이 기사에서는 분기별 매출을 표시하는 보고서를 만들 것입니다. 영토별로. 데이터의 최신 업데이트를 반영하도록 자동으로 업데이트되는 동적 대화형 Excel 대시보드라고도 할 수 있습니다.

보고서입니다. 이 기사가 끝난 후 만들 것입니다.

지역별 분기별 매출을 표시하는 Excel 보고서 만들기

데모에 사용된 통합 문서를 다운로드하여 기사와 함께 이 단계를 직접 시도할 수 있습니다.

지역별 분기별 매출을 Excel로 표시하는 보고서를 만드는 단계별 절차

데모를 위해 다음 데이터세트를 사용할 것입니다.

여기에는 날짜별 판매가 포함되며 Excel의 테이블 및 피벗 테이블 기능을 사용하여 분기별로 재정렬할 예정입니다.

1단계:데이터 세트를 테이블로 변환

데이터가 테이블 형식이 아닌 경우 범위를 테이블로 변환합니다. Excel 표는 참조, 필터링, 정렬 및 업데이트와 같은 많은 작업을 보다 쉽게 ​​하는 Excel의 최고의 기능 중 하나입니다.

• 표로 변환하려는 범위에서 셀을 선택하고 Ctrl+T를 누릅니다. 당신의 키보드에. 또는 삽입으로 이동합니다. 탭 및 표에서 명령 그룹에서 표를 클릭합니다. 옵션.

• 결과적으로 테이블 만들기 대화 상자가 나타납니다. 범위는 내 표에 머리글이 있습니다를 사용하여 자동으로 선택됩니다. 체크박스가 선택되었습니다. 표를 만들려면 확인을 클릭하세요.

결과적으로 데이터 세트는 테이블로 변환됩니다.

2단계:이름 테이블 범위

이 시점에서 테이블의 이름을 지정해 보겠습니다. 이렇게 하면 나중에 작업을 보다 쉽게 ​​수행할 수 있습니다.

디자인에서 테이블 이름을 변경할 수 있습니다. 탭 또는 이름 상자 사용 . 데이터로 테이블 이름을 지정했습니다. .

자세히 알아보기: Excel에서 월별 보고서를 만드는 방법(빠른 단계 포함)

3단계:주어진 데이터로 피벗 테이블 만들기

보고서 작성에 Excel에서 가장 많이 사용되는 도구인 피벗 테이블을 사용할 것입니다. 테이블로 피벗 테이블을 만들려면 다음 단계를 따르세요.

• 먼저 표에서 셀을 선택합니다.
• 그런 다음 삽입으로 이동합니다. 탭을 클릭하고 피벗 테이블을 클릭합니다. 테이블의 명령 그룹.

• 이 순간 피벗 테이블 만들기 대화 상자가 나타납니다. 피벗 테이블을 클릭하기 전에 테이블의 셀을 선택했기 때문에 명령, 테이블 이름(데이터 )이 표/범위에 자동으로 표시됩니다. 대화 상자의 필드입니다.
• 새 워크시트에 피벗 테이블을 만들고자 하므로 기본 선택 사항인 새 워크시트를 유지합니다. 피벗 테이블 보고서를 배치할 위치 선택 제목 아래 .
• 그런 다음 확인을 클릭합니다. .

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

자세히 알아보기: Excel에서 요약 보고서를 만드는 방법(2가지 쉬운 방법)

4단계:범주별 피벗 테이블 보고서 준비

판매 보고서를 작성해 보겠습니다. 카테고리 현명한 다음 우리는 파이 차트를 만들 것입니다. 보고서를 작성하기 위해 이러한 방식으로 피벗 테이블 필드를 구성합니다.

다음 이미지를 주의 깊게 관찰하십시오. 판매 값 필드에 두 번 지역. 이러한 이유로 열에서 영역, 추가 값 필드가 표시됩니다. 행 영역에 카테고리를 배치했습니다. 필드.

이미지 왼쪽에 위의 필드 설정에 대한 출력 피벗 테이블이 표시됩니다.

• 이제 매출의 숫자 형식을 백분율로 변경하려고 합니다. 총계의(%) . 그렇게 하려면 열의 셀을 마우스 오른쪽 버튼으로 클릭합니다.
• 그런 다음 값 표시 형식을 선택합니다. 컨텍스트 메뉴에서.
• 그런 다음 총계의 % 명령을 클릭합니다. .

따라서 열 값은 총계의 백분율로 표시됩니다. .

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

데이터에 대한 보고서를 생성하기 위해 원형 차트를 추가해 보겠습니다. 데이터에서 원형 차트를 추가하려면 다음 단계를 따르세요.

• 먼저 피벗 테이블에서 셀을 선택합니다.
• 그런 다음 삽입으로 이동합니다. 탭을 클릭하고 파이 차트 를 클릭하십시오. 차트의 아이콘 그룹.
• 그런 다음 파이를 선택합니다. 드롭다운 목록에서 차트.

스프레드시트에 원형 차트 팝업이 표시됩니다.

약간의 수정을 가하면 이제 차트가 다음과 같이 보일 것입니다.

파이 차트에 카테고리 이름 및 데이터 레이블 표시

다음 단계에 따라 데이터 레이블을 추가할 수 있습니다.

• 먼저 원형 차트를 선택합니다.
• 그런 다음 디자인으로 이동합니다. 탭 및 차트 레이아웃 명령 그룹에서 빠른 레이아웃을 클릭합니다. .
• 드롭다운에서 레이아웃 1을 선택합니다. 드롭다운에서 옵션을 선택하세요.

대체 방법:

차트에 데이터 레이블을 추가할 수 있는 또 다른 창의적인 방법은 GETPIVOTDATA 함수를 사용하는 것입니다. . 함수를 사용하여 피벗 테이블에서 데이터를 가져옵니다.

아래에 당사 데이터에서 생성된 피벗 테이블이 표시됩니다.

이 피벗 테이블에는 매출액 합계가 표시됩니다. , 상태 및 카테고리 현명한.

주 행 필드 지역, 카테고리 열 필드 지역 및 판매 값 필드 지역.

이제 Excel의 GETPIVOTDATA를 살펴보겠습니다. 기능.

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

피벗 테이블에는 data_field가 하나만 있습니다. 그러나 다른 필드는 얼마든지 가질 수 있습니다.

위의 피벗 테이블의 경우:

• data_field 판매 입니다. 필드
• 다른 두 필드는 상태입니다. 및 카테고리 .

다음 이미지에서 GETPIVOTDATA를 사용한 것을 볼 수 있습니다. 셀 H9:의 수식

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

이 공식은 어떻게 작동합니까?

• data_field 인수는 판매입니다. 의심의 여지가 없습니다.
• A3 피벗 테이블 내의 셀 참조입니다. 피벗 테이블 내의 모든 셀 참조가 될 수 있습니다.
• field1, item1 = "상태", H7 . 아이다호 와 같이 번역할 수 있습니다. (셀 H7 값 아이다호 ) 상태 항목
• 필드2, 항목2 = "카테고리", H8 . 사무용품으로 번역할 수 있습니다. (셀 H8의 값 사무용품입니다. ) 카테고리 항목
• 아이다호의 횡단면 가치 및 사무용품 값은 950의 값을 제공합니다.

라벨을 표시하려면:

GETPIVOTDATA 사용 기능을 사용하면 다음 이미지와 같은 일부 셀에 카테고리 이름과 판매 값(총계의 %)을 표시합니다.

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

• A4&”” 부분은 이해하기 쉽습니다. 그러면 셀 참조가 출력에 공백을 만듭니다.
• 그런 다음 Excel의 TEXT 값으로 TEXT의 인수 함수에서 GETPIVOTDATA를 전달했습니다. 기능 및 format_text 인수, 우리는 다음 형식을 사용했습니다:“0%”
• GETPIVOTDATA 부분은 이해하기 쉽습니다. 따라서 GETPIVOTDATA 기능은 여기에서 작동합니다.

이제 이 데이터를 차트에 표시하겠습니다.

텍스트 상자를 삽입했습니다. 삽입에서 탭 => 그림 명령 그룹 => 모양

이제 텍스트 상자를 삽입합니다. 차트에 => 수식 입력줄에 등호 표시 그런 다음 D4 셀을 선택합니다. .

Enter 키를 누르면 , 텍스트 상자 D4 셀의 값을 표시합니다. .

같은 방법으로 다른 텍스트 상자를 만듭니다. 관련 셀을 참조하십시오.

참고: 하나의 텍스트 상자가 새 텍스트 상자를 만들 수 있습니다. 이것부터. 다음과 같이 할 수 있습니다.

• 생성된 텍스트 상자 테두리 위로 마우스 포인터를 가져갑니다. Ctrl 키를 누르십시오. 키보드의 키. 더하기 기호가 나타납니다.
• 이제 마우스를 드래그합니다. 새로운 텍스트 상자가 표시됩니다. (객체)가 생성되면 이 새로 생성된 텍스트 상자를 드롭합니다. 원하는 장소에서

따라서 범주별 판매를 동적으로 표시하는 원형 차트 만들기가 완료되었습니다.

이 피벗 테이블의 이름을 PT_CategorySales로 변경합니다. .

자세히 알아보기: Excel에서 일일 판매 보고서를 만드는 방법(빠른 단계 포함)

유사한 수치

• Excel에서 재고 에이징 보고서를 만드는 방법(단계별 지침)
• Excel 데이터에서 PDF 보고서 생성(4가지 쉬운 방법)
• Excel에서 MIS 보고서를 준비하는 방법(2 적절한 예)
• 계정용 Excel에서 MIS 보고서 만들기(빠른 단계 사용)

6단계:분기별 판매를 위한 피벗 테이블 준비

경우에 따라 판매액 지난 몇 년 동안 분기별 변화

다음 이미지와 같은 보고서를 만들 것입니다.

이 이미지는 분기별 총 판매액에 따른 상위 15개 미국 주를 보여줍니다. 또한 다양한 분기의 추세를 보여주기 위해 스파크라인을 추가했습니다.

분기별 판매를 위한 피벗 테이블을 준비하려면 다음 단계를 따르십시오.

• 우선 데이터 표에서 셀을 선택합니다.
• 그런 다음 피벗 테이블 을 선택합니다. 표 에서 삽입  그룹 탭.

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

• 이제 다음을 수행합니다. 주문 날짜를 추가합니다. 열 필드 지역, 주 행 필드 지역 및 판매 값 필드

• 지금 분기별 보고서를 표시하려면 열 레이블 에서 아무 셀이나 마우스 오른쪽 버튼으로 클릭하십시오. 그룹 을 선택하십시오. 컨텍스트 메뉴에서.

• 그런 다음 분기 를 선택하십시오. 작성자 아래 그룹화 섹션

• 확인을 클릭한 후 , 피벗 테이블은 이제 다음과 같이 보일 것입니다.

7단계:판매에서 상위 15개 주 표시

이전 단계의 결과에는 데이터세트의 모든 상태에 대한 분기별 보고서가 포함됩니다. 모두 원하는 경우 이 항목으로 진행할 수 있습니다. 그러나 상위 상태가 필요한 더 자세한 분석의 경우 다음과 같은 몇 가지 편리한 단계가 있습니다.

• 먼저, 상태의 아무 셀이나 마우스 오른쪽 버튼으로 클릭합니다. 열(또는 행 레이블 ).
• 그런 다음 필터 위로 마우스를 가져갑니다. 컨텍스트 메뉴에서 상위 10개 를 선택하십시오.

• 다음으로 15 를 선택합니다. 쇼 에서 상위 10개 필터(주) 의 옵션

• 확인을 클릭하면 , 이제 피벗 테이블에 매출에 따른 상위 15개 주가 표시됩니다.

8단계:테이블에 스파크라인 추가

스파크라인을 추가하기 전에 , 두 총계를 모두 제거하고 싶습니다. 자세한 방법은 다음 단계를 따르세요.

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

총계 따라서 섹션이 제거됩니다.

• 스파크라인을 추가하려면 F5 셀을 선택합니다. 을 클릭한 다음 삽입 으로 이동합니다. 리본의 탭입니다.
• 이제 줄 을 선택합니다. 스파크라인 에서

• 스파크라인 생성 상자에서 B5:E19 범위를 선택합니다. 데이터 범위 로 및 범위 F5:F19 위치 범위로 .

• 그런 다음 확인을 클릭합니다. . 피벗 테이블은 이제 다음과 같이 보일 것입니다.

• 또한 좀 더 매력적으로 보이도록 마커를 추가해 보겠습니다. 그렇게 하려면 Sparkline 으로 이동하십시오. 리본의 탭(스파크라인이 포함된 셀을 선택하면 나타남)을 선택한 다음 마커 를 선택합니다. 쇼 에서

이것이 스파크라인의 최종 출력입니다.

자세히 알아보기: How to Make Monthly Sales Report in Excel (with Simple Steps)

Step 9:Add Slicer to Filter Output

Follow these simple steps to add slicers to the pivot table.

• First, select the pivot table for which you want to create the slicers.
• Then go to Insert Tab and from the Filters group of commands, click on the Slicer

• Next, the Insert Slicers dialog box will appear with all the available fields of the Pivot Table. Select the fields for which you want to create the slicers. Here, we have selected the Customer Name , 상태 , and Category fields for the demonstration.

• 확인을 클릭한 후 , 3 slicers will appear on top of the spreadsheet.

자세히 알아보기: How to Make MIS Report in Excel for Sales (with Easy Steps)

Step 10:Prepare Final Report

With all the detached stuff created let’s finally combine them all into a single spreadsheet to create a final report.

Now if you select/deselect an option from the slicer, the result will change accordingly in real-time. For example, let’s select Arizona from the State slicers. It will only report that.

You can select multiple ones now too. For example, adding Alabama with it will look like this. And that’s how you can create a report that displays the quarterly sales by territory.

자세히 알아보기: How to Automate Excel Reports Using Macros (3 Easy Ways)

결론

These were all the steps required to create a report that displays quarterly sales by territory in Excel. Hopefully, you can make one on your own with ease now. I hope you found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

For more guides like this, visit Exceldemy.com .

관련 기사

• How to Create an Expense Report in Excel (With Easy Steps)
• Create an Income and Expense Report in Excel (3 Examples)
• How to Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)
• Make Production Report in Excel (2 Common Variants)
• How to Make Daily Activity Report in Excel (5 Easy Examples)
• Make Daily Production Report in Excel (Download Free Template)
• How to Make Report Card in Excel (Download Free Template)