このコラムでは毎回、パソコンを使った操作が「楽々」になるチップスをWindows・Excel・PowerPointなどのテーマから選んでお届けいたします。

チップスとは「ちょっとしたヒント」のことです。

さて第19回は「データ分析を視覚化！ピボットテーブルで楽々」をテーマに、お届けします。前回はピボットテーブルを使えば縦横クロス集計表作成も、項目の追加も入れ替えも、ドラッグだけで楽々になることをご紹介しました。

今回はピボットテーブル第2弾、応用的な活用と、ピボットテーブルの書式設定や比率を表示する方法についてお届けします。

「ピボットテーブルって縦横合計だけ？」
「ホントはもっと活用できるのではないかな？」
など前回の「ピボットテーブル」作成だけでは物足りなかったところにも触れてまいります。ぜひお試しください。
*この記事ではMicrosoft365 Excel の最新版を使用した操作例を記載しています。Excel のバージョン2016・2019ではほぼ同じ操作を確認できます。


■概要 「前回は・・・」
前回はピボットテーブルを作成し、フィールドを入れ替えるところまでご紹介しました。

 

■操作1 「要約されたデータをひも解いて分析」
要約されたピボットテーブルの気になる値がどういった明細の積み上げなのか、ピボットテーブルで選択したセルの詳細データーを表示できます。
1.ヒートマップ（条件付き書式）を適用して数値を読む
（範囲選択→ホームタブ→条件付き書式→カラースケール→任意の色を選択）

 

2.気になるセルをダブルクリック
(サンプルではセルF8、黒の旅行鞄、2,160,000)
3.新しいシートへ、選択した条件の明細だけが表示されることを確認
(この明細シートは、オリジナルの「売上データ」とも「ピボットテーブル」とも連動しておらず、単にピボットテーブルの該当セルの明細がコピーされたシート)

 

■操作2 「オリジナルのデータに変更があった場合の更新」
オリジナルの売上データシートに変更があった場合、ピボットテーブルの値は自動的に更新されません。ピボットテーブルの値を手で修正することもできません。オリジナルのデータに変更があった場合には「更新」を実行します。
1.更新前のピボットテーブルの値を確認
(サンプルではB5 ヒョウ柄キャリーバッグの値が空欄であることを確認)

 

2.オリジナルの売上データシートに変更を加える
(サンプルではG6のセルをCに変更して商品カテゴリー名をキャリーバッグに変更)

 

3.ピボットテーブルシートへ切り替える
4.あらかじめ1.で確認して置いたセルに変更がないことを確認
(サンプルではセルB5)

 

5.[ピボットテーブル分析]タブ
6.[更新▼]ボタンから[更新]を選択
([すべて更新]はデータソース（集計元データの範囲）の変更があった場合に使用)
7.ピボットテーブル内のあらかじめ確認して置いたセルに変更が反映されたことを確認
(サンプルではセルB5)

 

■操作3 「ピボットテーブルの値を参考に別の表を作成する」
ピボットテーブルの値を手で修正することもできません。前期の値を元に来期予算を作りたいという場合にもう一歩、応用が利くといいですね。そこでピボットテーブルの値を参考に別の表を作成したい場合は、貼り付け時に形式を選択します。
1.ピボットテーブルの値へ直接変更を試みる
2.変更を行うことができないメッセージを確認

 

3.ピボットテーブルの範囲を選択
4.[CTRL] ＋ [C] でコピー
5.[貼り付け▼]ボタン
6.[値の貼り付け]
7.貼り付けた値を手動で変更できることを確認

 

■操作4 「フィルターフィールドの展開」
1. ピボットテーブルのフィールドリストから任意のフィールドを[フィルター]へドラッグアンドドロップ
(サンプルでは[シリーズ名])
2.ピボットテーブルの上部にフィルターフィールドが表示されたことを確認
3.[フィールド名（すべて）▼]から集計方法をフィルターできることを確認

4.[フィールド名▼]から集計方法を[(すべて)]に戻しておく
5.[ピボットテーブル分析]タブ
6.[ピボットテーブル▼]ボタン
7.[オプション▼]ボタン
8.[レポートフィルター ページの表示]
9. レポートフィルター ページの表示ダイアログボックスから[OK]
10.フィルターで選択したフィールド名ごとにシート名が展開され、それぞれのシートに該当の明細が表示されていることを確認

 

■操作5 「異なる集計方法で視点を変える」「更新に影響されない書式を設定する」
ピボットテーブルの集計方法には、合計だけでなく、「データの個数」「平均」などを選択できます。さらに計算の種類では「比率」などが使えます。
また計算方法の指定ウィンドウの中から[表示形式]を利用すればピボットテーブルを更新しても、その都度書式が戻ってしまう再設定の手間を省くことができます。
1.ピボットテーブルの任意の値を右クリック
2.[値フィールドの設定]
3.各種の集計方法を確認（ここでは合計のまま）
3.[計算の種類]タブ
4.[計算の種類]に[列集計に対する比率]を指定
5.[表示形式]ボタン
6.[パーセンテージ]
7.[小数点以下の桁数]を[1]
8.[OK]
9.[OK]

 

 

以上今回は、Excelの「データ分析を視覚化！ピボットテーブル」をテーマに、応用的な活用と、ピボットテーブルの書式設定や比率を表示する方法についてお届けしました。

縦横クロス集計だけでなく、もう一歩踏み込んだピボットテーブルの活用へ、繋げていただければさいわいです。

 

「他にもこんなことできたらいいな？」のリクエストやご感想がございましたらぜひ、お問い合わせまでお知らせください。それでは次回までごきげんようお過ごしくださいませ。

ＭＳ技術情報担当　井殿　寿代