ピボット テーブルを使用して Excel でデータを分析する方法 (9 つの適切な例)

ピボット テーブル Excelの非常に便利な機能です。この機能を使用すると、ユーザーは希望どおりにデータ テーブルを操作できます。ピボット テーブルを使用して Excel でデータを分析する方法を知りたい場合は、この記事が役立ちます。この記事では、Excel でデータを分析する方法を紹介します。 ピボット テーブルの使用 入念な説明付き。

この練習用ワークブックを以下からダウンロードしてください。

ピボット テーブルの概要

前述のように、ピボット テーブル データを分析するための強力なツールです。 ピボット テーブル ユーザーは希望に応じて既存のデータ テーブルを変更できます。テーブルから重要または必要な列のみをスライスして分析することが、ピボット テーブルのメイン テーマです。 . データ分析に入る前に 、ピボット テーブルに慣れる必要があります 環境。

• ピボット テーブルを開始した後 、リボンに 3 つの個別のタブがすぐに表示されます。 パワーピボット 、ピボットテーブル分析 、デザイン .
• フィールドのサイド パネルもあります。フィールドは基本的に、プライマリ データセットにあった列です。
• フィルタという名前の以下の領域にフィールドをドラッグできます ,列 、行、 と価値 .

• 行のフィールドをドラッグすると、ピボット テーブルの行として一番左の列に表示されます .
• それらを列に追加すると、ピボット テーブルの列に値が配置されます .
• 平均値を表示するかどうか、値フィールドの設定を変更できます /最大 /最小 値など 値 フィールドエリア。

Excel でピボット テーブルを作成する手順

最初にピボット テーブルを挿入します 挿入物から タブ

• そこからピボットテーブルをクリックします > 表/範囲から。

• 次に、新しいダイアログ ボックスで、[テーブル/範囲] をクリックします。 範囲ボックスをクリックし、 新しいワークシート を選択します オプション
• これを行うとテーブルが選択され、ピボット テーブルが開きます 新しいシートで。
• [OK] をクリックします この後。

• [OK] をクリックした直後 PivotTable Field で新しいワークシートが開かれていることに気付くでしょう。 設定.
• 新しいワークシートは下の画像のようになります。

ピボット テーブルを使用して Excel でデータを分析するための 9 つの適切な例

デモンストレーションの目的で、以下のデータセットを使用します。これは架空の組織の架空の販売データで、これまでに販売した製品の ID を確認できます。地域 売った、タイプ 、数量 、 コスト 生産、評価 、収益、 利益率。 ピボット テーブルを実装します データ テーブルに関する重要な洞察を分析して抽出する

1.ピボット テーブルでデータを分析するためのさまざまなフィールドの選択

以下の領域にさまざまなフィールドを追加して、さまざまな形状と基準を持つテーブルを作成できます。

例えば。 地域をドラッグしました 行フィールドに移動し、収益をドラッグします 値に フィールド。

ピボットのこれらのフィールドに従って、ピボット テーブルが作成されます。

手順

• 収益を確認できるように、最初のテーブルを最小限の形状に切り詰めました。 各地域で獲得した合計 .これにより、各 地域 にアクセスするのに非常に役立ちます。

• この分野にさらに基準や値のフィールドを追加して、さまざまな分野でそれぞれのパフォーマンスを評価することができます。
• たとえば、 コスト をドラッグできます および 数量 フィールドで既存のテーブルを拡張してください。
• この表から、各地域の総生産コストを確認できます . 数量の合計 各地域で生産された製品の もここにあります。
• このタイプの表を使用すると、データをより効果的に分析でき、混乱を避けることができます。

• すべての値フィールドについて、フィールドは Sum として表示されます フィールドの。しかし、ユーザーはフィールド操作を非常に簡単に変更できます。
• たとえば、評価を追加できます エリア内のフィールド。 ピボット テーブルの値 合計として表示されます 評価の 各地域で .しかし、それは意味がありません。 評価の合計から利益を得ることはありません 、平均 評価 が必要です 各地域。

• フィールド評価の設定を変更するには、領域内のフィールドをクリックしてから、マウスを右クリックします。
• 次に、メニューから [値フィールドの設定] を選択します。

• 値フィールドの設定という名前の新しいダイアログ ボックスが表示されます .
• そのダイアログ ボックスで、[平均] を選択します Summarize value field by から グループ。
• [OK] をクリックします この後。

• 評価 SUM ではなく平均値で表示されるようになりました セル範囲の合計 E4:E11.
• このデータを使用して、どの地域を分析できますか 製品は、他の製品と比較してより良いカスタマー レビューを受けました。

• 上の表から、アジア地域が 他の地域と比べて好成績。
• 次に、利益率を追加します エリア内のフィールド。デフォルト値の設定は合計に設定されているため、平均、 に切り替えます。 平均を見る 利益率の 各地域の .
• これを行うと、各地域の利益率が加算されます セル範囲 F4:F12
• しかし、利益率 分数なので、パーセンテージに変換する必要があります。
• これを行うには、セルの範囲を選択します F4:F12 パーセンテージ をクリックします。 番号 のアイコン グループ。

• これで、平均の 利益率 を確認できます 各地域のパーセンテージ形式で セル範囲 F4:F11.
• この要約表で分析できるのは、アフリカ 地域 利益率の点で好成績を収めています .一方で、中東アジア 利益率の点で最悪のパフォーマンスです。 彼らは基本的に商品を売ってお金を失っています。

2.複数のフィールドのネスト

複数のフィールドをまとめてグループ化し、ピボット テーブルで基準のネストを作成できます . ネストされた またはグループ化 フィールドでは、最初のフィールドが最初にデータを表示します。次に、最初のフィールドの下に、すべての第 2 層のフィールドが表示されます。下の画像のように。

手順

• これを行うには、カテゴリをドラッグします Region のすぐ下のフィールド 行 のフィールド
• その後、カテゴリに気付くでしょう 各地域フィールドの下にネストされます。
• 表を使用すると、地域だけでなく区別できます 互いにだけでなく、各 地域 の内側のフィールドから .

• 地域をドラッグできるようになりました セカンドポジションへ。それを行うと、カテゴリが配置されます 入れ子の最初の位置。下の画像に示すように。

3.ピボット テーブルでのデータのフィルタリング

ピボット テーブルの作成後 選択にフィルタを追加して、分析の柔軟性を高めることができます .

手順

• これを行うには、カテゴリをドラッグします フィルタ のフィールド
• カテゴリをドラッグした直後 フィールド、 フィルタ があります ワークシートの左上隅にあるアイコン。
• フィルタをクリックします アイコン。 [複数のアイテムを選択] にチェックマークを付けます。
• 次に電子機器を選択します [OK] をクリックします。

• これで、テーブルは Electronics 以外のすべてのエントリを除外します 、テーブルを自動的に更新します。
• 更新された表を以下に示します。ここはアフリカだけ 、ヨーロッパ、 および南アジア地域 Electronics の下に製品があります カテゴリ .したがって、これらの地域のみ ここに表示されます。

4.ピボット テーブルでのデータの並べ替え

データセットをフィルタリングするだけでなく、要件に従ってデータを並べ替えることもできます。アルファベット順または最小から最大の順に並べ替えることができるように.

手順

• ピボット テーブルを整理するには 行 の上にある並べ替えアイコンをクリックします。 ドロップダウン メニューが表示されます。そのドロップダウン メニューから、[その他の並べ替えオプション] をクリックします。

• その後、新しいダイアログ ボックスが開き、並べ替えを実行するフィールド名を選択します。この場合、地域を選択します .
• デフォルトでは、並べ替えオプションは昇順 (A から Z) に設定されています:

• だから地域 アルファベット順に整理されます。
• 新しく編成されたテーブルは、このようになります。

• しかし、並べ替えるだけではありません 地域 任意の基準による任意の分野でも。
• たとえば、ピボット テーブルを並べ替えることができます 数量の合計 各 地域 の製品数
• これを行うには、並べ替えアイコンを選択し、並べ替え ダイアログ ボックスで、 数量の合計 を選択します。 ドロップダウン メニューで。
• その後、[その他のオプション] をクリックします。 ダイアログ ボックスの右下にあります。

• 次のダイアログ ボックスで、[総計] をクリックします。 並べ替え オプション。つまり、基本的に 並べ替え テーブル 数量 最小から最大まで。 [OK] をクリックします この後。

• 前のダイアログ ボックスに戻ります。 OK をクリックします その箱にも。
• [OK] をクリックした後、 ピボット テーブル 最小の数量に従ってソートされるようになりました 製品の最大 数量
• 表はすでにかなり整理されているため、この機能は表を分析する際に非常に役立ちます。 製品の数だけではありません 地域ごとに配置 並べ替えも可能です どの地域を評価できるように より多くの製品を簡単に生産できるようになりました。

これが、並べ替えによってデータを分析する方法です ピボット テーブルでそれらを

続きを読む:Excel で大量のデータ セットを分析する方法 (6 つの効果的な方法)

類似の読み物

• Excel でリッカート尺度データを分析する方法 (クイック手順付き)
• アンケートの質的データを Excel で分析する
• Excel で時系列データを分析する方法 (簡単な手順)
• Excel で時系列データを分析する (簡単な手順で)
• Excel にデータ分析を追加する方法 (2 つの簡単な手順)

5.ピボット テーブルでのスライサーの使用

スライサー は、必要な情報を効率的にフィルタリングするための便利なツールです。

手順

• 次のステップでは、ピボット テーブルにいくつかのスライサーを追加できます。
• スライサーを追加するには、[スライサーの挿入] をクリックします。 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 地域 秒。 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 ドロップダウン メニューから。
• 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.

• After clicking OK, 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 [OK] をクリックします。

• After clicking OK, you will notice that the chart now shows only the Furniture and the Home Appliances 値。 And only 4 Regions actually have that Category 製品。 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 事業。 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 価値。 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 値。 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

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.

コメント欄から質問やフィードバックをお気軽にお寄せください。 Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.

関連記事

• Excel で質的データを分析する (簡単な手順で)
• Excel で qPCR データを分析する方法 (2 つの簡単な方法)
• Excel データ分析を使用してケース スタディを実行する
• Excel でテキスト データを分析する方法 (5 つの適切な方法)
• Excel で定性的データを定量的データに変換する方法