피벗 테이블을 사용하여 Excel에서 데이터를 분석하는 방법(9 적절한 예)

피벗 테이블 Excel의 매우 유용한 기능입니다. 이 기능을 사용하여 사용자는 원하는 대로 데이터 테이블을 조작할 수 있습니다. 피벗 테이블을 사용하여 Excel에서 데이터를 분석하는 방법이 궁금하다면 이 문서가 도움이 될 것입니다. 이 기사에서는 Excel에서 데이터를 분석하는 방법을 보여 드리겠습니다. 피벗 테이블 사용 자세한 설명과 함께.

아래에서 이 연습 워크북을 다운로드하십시오.

피벗 테이블 개요

앞서 언급했듯이 피벗 테이블 데이터를 분석하는 강력한 도구입니다. 피벗 테이블 사용자가 원하는 대로 기존 데이터 테이블을 수정할 수 있습니다. 테이블에서 중요하거나 필요한 열만 슬라이싱하여 분석하는 것이 피벗 테이블의 주요 테마입니다. . 데이터 분석에 대해 알아보기 전에 , 피벗 테이블에 익숙해져야 합니다. 환경.

• 피벗 테이블을 시작한 후 , 리본에 세 개의 개별 탭이 즉시 표시됩니다. 파워 피벗 , 피벗 테이블 분석 및 디자인 .
• 필드 측면 패널도 있습니다. 필드는 기본적으로 기본 데이터세트에 있던 열입니다.
• 필드를 필터라는 이름의 아래 영역으로 드래그할 수 있습니다. , 열 , 행, 및 가치 .

• 행의 필드를 드래그하면 가장 왼쪽 열에 피벗 테이블의 행으로 표시됩니다. .
• 그리고 열에 추가하면 피벗 테이블의 열에 값이 배치됩니다. .
• 평균 값을 표시할지 여부에 관계없이 값 필드 설정을 변경할 수 있습니다. /최대 /최소 값 등. 값 필드 영역.

Excel에서 피벗 테이블을 만드는 단계

먼저 피벗 테이블을 삽입합니다. 삽입에서 탭.

• 여기서 피벗 테이블을 클릭합니다. > 표/범위에서.

• 그런 다음 새 대화 상자에서 표/범위 범위 상자를 클릭한 다음 새 워크시트 를 선택하십시오. 옵션.
• 이렇게 하면 테이블이 선택되고 피벗 테이블이 열립니다. 새 시트에서.
• 확인 을 클릭합니다. 이 후.

• 확인, 을 클릭한 직후 피벗 테이블 필드가 있는 새 워크시트가 열렸음을 알 수 있습니다. 설정.
• 새 워크시트는 아래 이미지와 유사합니다.

피벗 테이블을 사용하여 Excel에서 데이터를 분석하는 데 적합한 9가지 예

데모 목적으로 아래 데이터 세트를 사용할 것입니다. 이것은 가상 조직의 가상 판매 데이터로, 지금까지 판매한 제품의 ID를 볼 수 있습니다. 여기서 지역 판매한 유형 , 수량 , 비용 생산, 등급 , 수익, 및 이익 마진. 피벗 테이블 을 구현합니다. 데이터 테이블에 대한 중요한 통찰력을 분석하고 추출합니다.

1. 피벗 테이블에서 데이터를 분석할 다양한 필드 선택

아래 영역에 다양한 필드를 추가하여 다양한 모양과 기준을 가진 테이블을 만들 수 있습니다.

예를 들어. 지역을 드래그했습니다. 행 필드로 이동한 다음 수익 가치로 필드.

피벗의 이 필드에 따라 피벗 테이블이 생성됩니다. 지역.

단계

• 초기 테이블을 최소화하여 수익을 볼 수 있습니다. 각 지역에서 획득한 총계 . 이는 각 지역 공연.

• 다른 부문에서 각각의 성과를 평가하기 위해 해당 영역에 더 많은 기준 또는 값 필드를 추가할 수 있습니다.
• 예를 들어, 비용 및 수량 필드에서 기존 테이블을 확장합니다.
• 이 표에서 각 지역의 총 생산 비용을 볼 수 있습니다. . 총 수량 각 지역에서 생산되는 제품의 여기도 있습니다.
• 이러한 유형의 테이블을 사용하면 데이터를 보다 효과적으로 분석할 수 있고 데이터가 복잡하지 않게 유지하는 데 도움이 됩니다.

• 모든 값 필드의 경우 필드는 합계로 표시됩니다. 필드의. 그러나 사용자는 현장 작업을 매우 쉽게 변경할 수 있습니다.
• 예를 들어, 등급을 추가할 수 있습니다. 지역의 들판. 피벗 테이블의 값 합계로 표시됩니다. 평점 각 지역에서 . 하지만 의미가 없습니다. 우리는 평점 합계의 혜택을 받지 않습니다. , 평균 평점 이 필요합니다. 각 지역의

• 필드 평가 설정을 변경하려면 해당 영역의 필드를 클릭한 다음 마우스 오른쪽 버튼을 클릭합니다.
• 그런 다음 메뉴에서 값 필드 설정을 선택합니다.

• 값 필드 설정이라는 새 대화 상자가 표시됩니다. .
• 해당 대화 상자에서 평균을 선택합니다. 값 요약 기준 에서 그룹.
• 확인 을 클릭합니다. 이 후.

• 이제 평점 이제 SUM 대신 평균 값으로 표시됩니다. E4:E11 셀 범위의 합계
• 이 데이터를 사용하여 어떤 지역 다른 제품에 비해 더 나은 고객 리뷰를 받았습니다.

• 위의 표에서 아시아 지역 다른 지역에 비해 우수한 실적을 보였습니다.
• 다음으로 이익 마진을 추가합니다. 지역의 들판. 기본값 설정이 Sum으로 설정되어 있으므로 Average, 로 전환합니다. 평균 보기 이익 마진 각 지역의 .
• 이렇게 하면 각 지역의 이익 마진이 추가됩니다. 셀 범위 내 F4:F12
• 하지만 이익률 분수로 되어 있으므로 백분율로 변환해야 합니다.
• 이렇게 하려면 셀 범위를 선택합니다. F4:F12 그런 다음 백분율 을 클릭하십시오. 숫자 의 아이콘 그룹.

• 이제 평균 이익률 을 볼 수 있습니다. 각 지역의 백분율 형식 셀 범위에서 F4:F11.
• 이 요약 표를 통해 아프리카 지역 이익률 면에서 좋은 성과를 보이고 있습니다. . 반면에 우리는 중동 아시아 이익률 측면에서 최악의 성과를 거두고 있습니다. 그들은 기본적으로 제품을 판매하여 돈을 잃고 있습니다.

2. 여러 필드 중첩

여러 필드를 함께 그룹화하여 피벗 테이블에 기준 중첩을 만들 수 있습니다. . 중첩 또는 그룹화 필드에서 첫 번째 필드는 데이터를 먼저 표시합니다. 그런 다음 첫 번째 필드 아래에 두 번째 계층 필드가 모두 표시됩니다. 아래 이미지와 같습니다.

단계

• 이렇게 하려면 카테고리 를 드래그하십시오. 지역 바로 아래 필드 행 필드 지역.
• 그 후에 카테고리가 표시됩니다. 각 지역 필드 아래에 중첩됩니다.
• 표를 사용하여 지역뿐만 아니라 서로 뿐만 아니라 각 지역의 내부 필드 .

• 이제 지역을 드래그할 수 있습니다. 두 번째 위치로. 이렇게 하면 카테고리가 배치됩니다. 중첩의 첫 번째 위치에 있습니다. 아래 이미지와 같이

3. 피벗 테이블의 데이터 필터링

피벗 테이블을 만든 후 선택 항목에 필터를 추가하여 분석에 유연성을 더할 수 있습니다. .

단계

• 이렇게 하려면 카테고리 필터 필드 지역.
• 카테고리를 드래그한 직후 필드에 필터 가 있습니다. 워크시트의 왼쪽 상단에 있는 아이콘
• 필터 를 클릭하십시오. 상. 그리고 여러 항목 선택을 선택합니다.
• 그런 다음 전자제품을 선택합니다. 확인을 클릭합니다.

• 이제 표는 전자제품을 제외한 모든 항목을 필터링합니다. , 테이블을 자동으로 업데이트합니다.
• 업데이트된 표는 아래와 같습니다. 여기서는 아프리카만 , 유럽, 및 남아시아 지역 전자제품 아래에 제품이 있습니다. 카테고리 . 따라서 이 지역만 여기에 표시됩니다.

4. 피벗 테이블에서 데이터 정렬

데이터 세트를 필터링하는 것 외에도 요구 사항에 따라 데이터를 정렬할 수도 있습니다. 알파벳순으로 정렬하거나 작은 것에서 큰 것 순으로 정렬할 수 있습니다.

단계

• 피벗 테이블 정렬 , 행 상단에 있는 정렬 아이콘을 클릭하십시오. 레벨 드롭다운 메뉴가 나타납니다. 해당 드롭다운 메뉴에서 정렬 옵션 더보기를 클릭합니다.

• 그러면 정렬을 수행할 필드 이름을 선택하는 새 대화 상자가 열립니다. 이 경우 지역을 선택합니다. .
• 기본적으로 정렬 옵션은오름차순(A~Z):으로 설정됩니다.

• 그래서 지역 알파벳순으로 구성됩니다.
• 새로 정리된 테이블은 대략 다음과 같을 것입니다.

• 하지만 정렬 뿐만 아니라 지역 또한 모든 기준에 따라 모든 분야에서.
• 예를 들어, 피벗 테이블을 정렬할 수 있습니다. 총 수량 기준 각 지역 의 제품 수 생산.
• 이렇게 하려면 정렬 아이콘을 선택한 다음 정렬 대화 상자에서 수량 합계 를 선택합니다. 드롭다운 메뉴에서
• 그런 다음 추가 옵션 을 클릭합니다. 대화 상자의 오른쪽 하단에 있습니다.

• 다음 대화 상자에서 총계를 클릭합니다. 정렬 기준 옵션. 즉, 기본적으로 정렬 수량 표 가장 작은 것부터 가장 큰 것까지. 확인을 클릭합니다. 이 후.

• 이제 이전 대화 상자로 돌아갑니다. 확인 을 클릭하십시오. 그 상자에도.
• 확인을 클릭한 후 피벗 테이블 이제 가장 작은 수량에 따라 정렬됩니다. 제품의 최대 수량 제품의.
• 이 기능은 테이블이 이미 꽤 정리되어 있기 때문에 테이블을 분석하는 데 큰 도움이 됩니다. 우리는 제품의 수 뿐만 아니라 각 지역 별로 정렬 그러나 정렬도 가능합니다. 어떤 지역을 평가할 수 있도록 더 많은 제품을 쉽게 생산했습니다.

이것이 정렬을 통해 데이터를 분석하는 방법입니다. 피벗 테이블에서 엑셀에서.

자세히 알아보기:Excel에서 대용량 데이터 세트를 분석하는 방법(6가지 효과적인 방법)

유사한 수치

• Excel에서 리커트 척도 데이터를 분석하는 방법(빠른 단계 포함)
• Excel에서 설문지의 정성 데이터 분석
• Excel에서 시간 척도 데이터를 분석하는 방법(간단한 단계 사용)
• Excel에서 시계열 데이터 분석(간단한 단계 사용)
• Excel에서 데이터 분석을 추가하는 방법(빠른 2단계 포함)

5. 피벗 테이블에서 슬라이서 사용

슬라이서 필요한 정보를 효율적으로 걸러내는 편리한 도구입니다.

단계

• In the next step, we can add a couple of slicers to the Pivot Table.
• In order to add the slicer, click on the Insert Slicer command from the PivotTable Analyze 탭.

• After clicking the Insert Slicer command, there will be a new dialog box named Insert Slicers.
• On that dialog box, select the slicers that you are going to add by tick marking the checkboxes. In this case, we checked the Category , Region , and Rating boxes.
• After finishing the ticked marking, click on OK.

• Right after clicking on the OK, there will be 3 different slicers spawned in the worksheet. Each one for every field and the entries in these fields.

• Click on Asia  and South Asia in the first slicer. This will eliminate other entries which have Regions other than Asia and South Asia.
• Then select the Electronics and the Home Appliances.
• This will select only products only in that two Category.
• Then choose Ratings  4 and 4.5 .
• the table is going to update regularly as we click on Slicer .
• Finally, we got different products and their different performance parameter only from the Asia and the South Asia Region 에스. The list is further filtered by the only two Category of products. Then we proceed to choose only 4 and 4.5  Ratings . That is how we narrow down products according to performance parameters.

And this is how we analyze the data by adding the slicers in the Pivot tables 엑셀에서.

6. Analyzing Data with PivotChart

We can further enhance our analysis by showing our Pivot Table with some charts. For instance, we can use the stacked bar chart with dual-axis to visualize the Sum of Revenue and the Profit Margin with respect to the Regions.

단계

• We need to visualize how the Profit Margin earned by each Region is related to the revenue generated by them. Are they in proportion to each other, or do they act inversely?
• In order to do this, first of all, we need to go to the Insert tab and then click on the PivotChart. Then from the drop-down menu click on the PivotChart.

• There will be a new dialog box named Insert Chart, where you can choose your favorite types of charts.
• From that option, we choose the Combo option, as we need to show two separate values on the vertical axis.
• In that menu, for the Sum of Revenue , choose the chart type as Clustered Column from the dropdown menu.
• Then for the Average of Profit Margin, choose the Line chart type. And also set this chart as the secondary axis by ticking the box right side of the dropdown menu.

• 확인을 클릭한 후 we will notice that the new chart is now present. With the two axes, The left side one iis for the Sum of Revenue and the right side one is for the Profit Margin.

<강한> From the chart, we can see that the Profit Margin and the Sum of the Revenue are not exactly proportional here. Higher revenue doesn’t mean that the Profit Margin would be higher accordingly.

• The charts have filters by default in it. For example, you can see the Category filter already set in the top left corner of the chart. Click on it and see that all the products Category are now selected.

• If you want to select some Categories and ignore the rest of them, select your desired one and then click OK. For example, we choose Furniture and Home Appliances 그런 다음 확인을 클릭합니다.

• 확인을 클릭한 후 you will notice that the chart now shows only the Furniture and the Home Appliances values. And only 4 Regions actually have that Category product. That is why the chart is now shrunken and showing only the 4 Region data.

This is how we can analyze and visualize the Pivot Tables in Excel and then narrow down the data using the filter option in the chart.

7. Updating Data in Existing Pivot Table

Updating the data is considered to be routine work in any kind of data analysis project. Suppose we got some new raw sales data , as shown in the highlighted part of the table below. Now creating a new Pivot Table from the scratch can be a tedious thing to do. To resolve this, we will update the data source internally by keeping the original Pivot Table structure intact.

단계

• In the beginning, from the Pivot Table Analysis tab, click on the Change Data Source command.
• Then from the drop-down menu, click on the Change Data Source.

• Then in the Move PivotTable range box, select the full range of the updated table.

• After selecting the full range, you are going to see that the full range is now added to the existing PivotTable , and it’s updated also.

자세히 알아보기:[고정:] Excel에 데이터 분석이 표시되지 않음(2가지 효과적인 솔루션)

8. Getting Top 10 Values from Pivot Table

You can extract the top 10 or 20 values in a dataset, using the Sort icon on the top left corner.

단계

• In order to do this, first, click on the dropdown icon on top of the Row Levels.
• Then from the drop-down menu, click on the Value Filters> Top 10.

• Ther is a new dialog box whose name is Top 10 Filter (Product Id).
• In the first dropdown box, select whether you want the top 10 value or the bottom 10 value. We choose Top.
• From that window, select whether you want to select the top 10 or  20.   We choose 10.
• Then choose Items from the next dropdown box.
• Then choose based on which column are you going to filter the top 10 values. We select Average of Profit Margin.
• Click OK 이 후.

After clicking OK, you will notice that the Top 10 values according to the Average of the Profit Margin are showing.

And this is how we analyze the data by trimming the top 10 data in the Pivot tables in Excel.

9. Grouping Data in Pivot Table

In the Pivot Table , it is also possible to group data altogether.

단계

• In order to do this, select multiple values by pressing Ctrl , and then right-click on the mouse.
• Then from the context menu, select Group .

• After clicking the Group command, we can see the Product ID which is now together.

• We can also group data by Region . For example, we can group the whole Asia Region  under one group. And then group others as the rest of the world group
• To do this, first, create the group as shown before.

• Then select the cell and press F2.
• Doing this will make the cell enter into editing mode.

• Then we enter the group name “Rest of the World ".

• Repeat the same process for Group 2 .
• Here we will enter Asia Region, as the name of the group.
• Finally, they all together will look like this.

And this is how we analyze the data by grouping them in the Pivot tables in Excel.

결론

To sum it up, the question “how to analyze data in excel using pivot tables ” is answered here with 11 different processes. Those processes can make any kind of data analysis project seamless with the Pivot Table.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.

관련 기사

• Analyze Qualitative Data in Excel (with Easy Steps)
• Excel에서 qPCR 데이터를 분석하는 방법(2가지 쉬운 방법)
• Excel 데이터 분석을 사용하여 사례 연구 수행
• Excel에서 텍스트 데이터를 분석하는 방법(5가지 적절한 방법)
• How to Convert Qualitative Data to Quantitative Data in Excel