Excelデータ分析のプロになる！ピボットテーブルからダッシュボード作成まで完全マスター講座

Excelのテーブルには、作成時に「テーブル1」のような既定の名前が自動で割り当てられます。 この名前は、[テーブルデザイン]タブの[プロパティ]グループにある[テーブル名]ボックスで、より分かりやすい名前に変更することができます。 テーブルに固有の名前を付けることで、数式で参照する際などに管理がしやすくなります。

ピボットテーブルで集計表を作成するには、まず元となるデータリストを準備します。 次に、[挿入]タブから[ピボットテーブル]を選択し、作成場所（新規ワークシートまたは既存のワークシート）を指定します。 画面右側に表示される「ピボットテーブルのフィールド」作業ウィンドウで、集計したい項目を「行」「列」「値」の各エリアにドラッグ＆ドロップすることで、様々な角度からデータを集計した表を簡単に作成できます。

ピボットテーブルのフィールドとは、集計・分析の元となるデータリストの各列項目（見出し）のことです。 画面右側の「ピボットテーブルのフィールド」作業ウィンドウには、これらのフィールドが一覧で表示されます。 この一覧から、分析したいフィールドを下の4つのエリア（フィルター、列、行、値）にドラッグ＆ドロップすることで、集計表のレイアウトを自由に組み立てることができます。

作成したピボットテーブルは、後からデザインやレイアウト、表示形式などを変更して、より見やすく整えることができます。例えば、[デザイン]タブからは、表全体のデザインスタイルを変更できます。また、数値の表示形式（桁区切りカンマや通貨など）は、「値フィールドの設定」から変更可能です。

ピボットテーブルの集計値に3桁区切りのコンマを表示するには、「値フィールドの設定」を利用します。 値エリアに配置されたフィールドをクリックし、表示されるメニューから[値フィールドの設定]を選択します。ダイアログボックス左下の[表示形式]ボタンをクリックし、「セルの書式設定」で[数値]カテゴリを選び、「桁区切り(,)を使用する」にチェックを入れることで、金額にコンマを付けることができます。

ピボットテーブルのデータは、項目名や集計値を基準に並べ替えることができます。 並べ替えたい項目のドロップダウンリストから「昇順」または「降順」を選択するだけで、簡単にデータを並べ替えることが可能です。 また、複数の条件を組み合わせた複雑な並べ替えも、「並べ替え」ダイアログボックスから設定できます。

ピボットテーブルでは、各グループの集計結果として自動的に「小計」が表示されます。この小計を非表示にしたい場合は、[デザイン]タブの[小計]から「小計を表示しない」を選択します。

ピボットテーブル作成後に、元データの範囲が変更（行や列が追加）された場合、その変更をピボットテーブルに反映させる必要があります。 [ピボットテーブル分析]タブの[データソースの変更]をクリックし、新しいデータ範囲を再度選択することで、追加されたデータを含めて再集計することができます。

フィルター機能を使うと、ピボットテーブルに表示するデータを特定の条件で絞り込むことができます。 「ピボットテーブルのフィールド」にある項目を「フィルター」エリアにドラッグすると、テーブルの左上にフィルターが追加されます。 このフィルターで特定の項目を選択すると、その項目に関連するデータのみが表示されます。

ピボットテーブルでは、年齢や金額などの連続した数値を、特定の範囲で区切ってグループ化することができます。 例えば、売上金額を「1～1000円」「1001～2000円」のようにまとめることが可能です。数値の入った行または列ラベルの上で右クリックし、「グループ化」を選択して、開始値、終了値、間隔（単位）を指定します。

ピボットテーブルでは、複数のテキスト項目を選択して、手動で1つのグループにまとめることができます。 例えば、「東京」「神奈川」「千葉」を「関東」グループとしてまとめることが可能です。グループ化したい項目を複数選択した状態で右クリックし、「グループ化」を選択すると、新しいグループが作成されます。

日付データは、「年」「四半期」「月」「日」といった単位で自動的にグループ化することができます。 日付フィールドを行または列エリアに配置すると、多くの場合Excelが自動でグループ化を行いますが、手動で変更することも可能です。 日付のセルで右クリックし、「グループ化」を選択すると、グループ化の単位（年、月、日など）を自由に選べます。

集計フィールドとは、ピボットテーブルの元データには存在しない、独自の計算式を設定して作成する新しいフィールドのことです。 例えば、「単価」と「数量」のフィールドがあれば、「売上（単価 × 数量）」という集計フィールドを追加することができます。

作成した集計フィールドは、後から数式を変更したり、不要になったら削除したりすることができます。変更・削除を行うには、[ピボットテーブル分析]タブの[フィールド/アイテム/セット]から[集計フィールド]を選択します。ダイアログボックスで、変更したいフィールドを選んで数式を編集するか、[削除]ボタンをクリックします。

集計フィールドの数式には、SUMやAVERAGEといった基本的な集計関数だけでなく、IF関数などの論理関数も使用できます。これにより、単純な四則演算だけでなく、条件に応じた計算結果を表示するなど、より高度な分析が可能になります。

ピボットテーブルの集計フィードに関数などを入力すると、数式を確認する機能をご紹介します。

スライサーは、データを視覚的にフィルタリングするためのツールです。 ピボットテーブルを選択した状態で、[挿入]タブから[スライサー]を挿入すると、ボタン形式のフィルターが表示されます。このボタンをクリックするだけで、直感的にデータの絞り込みや切り替えができ、分析作業がよりインタラクティブになります。

売上データが含まれる表を選択し、「挿入」タブから「ピボットテーブル」を選択します。新しいワークシートにピボットテーブルを作成し、「行」に「日付」、「値」に「金額」をドラッグ＆ドロップすることで、日付ごとの売上集計表が完成します。

作成したピボットテーブル内を選択した状態で、「ピボットテーブル分析」タブから「ピボットグラフ」をクリックします。売上の増減を直感的に把握しやすい縦棒グラフを選択し、データラベルを表示させることで、各月の売上金額が一目で分かるようになります。

全体の売上推移を把握した後は、商品ごとの売上動向を分析します。クロス集計を用いることで、特定の商品が全体の売上にどのように貢献しているかを明らかにすることができます。

ピボットテーブルのフィールドリストから、「商品分類」を「フィルター」ボックスにドラッグ＆ドロップします。、商品カテゴリーごとにデータを絞り込んで表示することが可能になります。

売上の大部分を占める重要商品を特定するためには、パレート図が非常に有効です。パレート図は、売上高の大きい順に商品を並べた棒グラフと、その累積構成比を示す折れ線グラフを組み合わせたもので、「売上の8割は2割の商品が生み出している」といった法則（パレートの法則）を視覚的に確認することができます。

ピボットテーブルの「値」ボックスに「金額」を2つ追加し、2つ目の「金額」の「値フィールドの設定」で「計算の種類」を「比率の累計」に変更します。

売上金額の列を降順（大きい順）に並び替えます。

「ピボットグラフ」で「組み合わせ」を選択し、売上金額を「集合縦棒」、累積比率を「折れ線」（第2軸）に設定することで、パレート図が完成します。

優良顧客の特定や、顧客層の分析を行うためには、顧客ごとの売上を比較することが重要です。ピボットテーブルのフィルター機能を用いることで、特定の顧客や地域に絞った分析が容易になります。

データの中から特定の条件に合致するセルを目立たせることで、注目すべき情報を素早く見つけ出すことができます。Excelの「条件付き書式」機能は、このような場合に非常に役立ちます。

売上が伸び悩んでいる商品を特定するために、平均売上を下回る商品をハイライトします。

売上貢献度の高い商品を明確にするために、売上上位の商品をハイライトします。

商品ごと、かつ期間ごとの売上の強弱を俯瞰的に把握するためには、ヒートマップが有効です。ヒートマップは、数値の大小に応じてセルの色を変化させることで、データ全体の傾向や特異点を視覚的に捉えることを可能にします。

元データシートを「売上データ」に変更する手順

シート名変更の目的と重要性

「結果」シートの作成

「PIVOT」シートの作成•各シートの役割と使い分け