きょうはエクセルのピボットテーブル、ピボットグラフについて学びます。
エクセルには知っていれば便利な機能がたくさんありますが、ピボットテーブルもそんな機能の一つです。ピボットとは「軸回転する」という意味です。
あっという間にクロス集計表（基本情報をかけ合わせて集計）方法を作成したり、比較したい項目名を選ぶだけで、表に表示する項目を変更できます。
もっと早く知っておきたかったと思われるのがうなずけます。
きょうはあなたと、 Excel のピボットテーブルとピボットグラフを学習します。
１．Excel ピボットテーブル、ピボットグラフ基本＆便利技
ピボットテーブルっていったい何だ？
１．大量のデータを分析・集計
Excelのピボットテーブルとピボットグラフは、大量のデータを分析・集計する際に非常に便利な機能です。
ピボットテーブルっちゅうのはな、Excelとかの表計算ソフトで使う便利な機能やで。たくさんのデータを簡単にまとめて、いろんな角度から分析できるんや。例えば、売上データがあったら、地域別とか、商品別とか、月別に簡単に集計できるんやで。自分が見たいデータをポチポチっと選んだら、パパッと結果が出てくるから、めっちゃ便利やわ。データの管理や分析が簡単になるから、一回使ってみたらハマるで！
ピボットテーブルの作成
- データを選択して、「挿入」タブから「ピボットテーブル」を選択します。
- ピボットテーブルフィールドリストが表示され、集計したいデータとその軸をドラッグ＆ドロップで選択します。
値フィールドの設定
- ピボットテーブルに表示するデータの計算方法を選択します（合計、平均、カウントなど）。
- 値フィールドのセルを右クリックして「設定値フィールド」を選択し、計算方法を変更できます。
フィルターの追加
- ピボットテーブルにフィルターを追加して、表示するデータを制限します。
- フィルターによって、特定の条件を満たすデータだけを表示したり、期間を指定したりできます。
グループ化
- 日付や数値データをグループ化して、より詳細な集計を行います。
- 日付を月、四半期、年などにグループ化することが一般的です。
集計方法の変更
- ピボットテーブル内のセルで右クリックし、「値フィールド設定」を選択して、集計方法を変更します。
ピボットグラフの作成
- ピボットテーブルからピボットグラフを作成することで、視覚的なデータ分析が可能になります。
- 挿入タブからグラフの種類を選択し、必要なデータ範囲を指定して作成します。
ダイナミックなグラフの更新
- ピボットテーブルのデータを更新したり追加したりすると、ピボットグラフは自動的に更新されます。
- シートを開いたときやデータを変更したときに、「データの更新」ボタンを押すと手動で更新できます。
複数の値フィールドの使用
- ピボットテーブルに複数の値フィールドを追加して、異なる集計を同時に表示します。
集計レポートのデザイン
- ピボットテーブルとピボットグラフの見た目をカスタマイズして、分析結果を分かりやすく伝えるようにしましょう。
スライサーの使用
- スライサーを追加すると、ピボットテーブルやピボットグラフのフィルタリングが直感的に行えます。
これらの基本的な機能と便利なテクニックを使いこなすことで、Excelのピボットテーブルとピボットグラフを効果的に活用できます。
使いこなせば便利なツールかも？
たとえば、このリストを見ただけでは、何がどれくらい売れているかわかりません。
このリストがあっという間に下のような集計表になります。比較したい項目だけ選べば、表に表示する項目を変更できます。
「どこで（京都支店）、いつ（日付）、どんな商品が、いくらで売れているかわかります。このデータをもとにデータの項目を自由に指定できる表やグラフを作ります。ピボットテーブルとは、データの項目を自由に設定し、集計した表のことで、項目別の集計表や、割合で表した集計表など、表の形式や集計方法を切り替えて表示できます。
２．ピボットテーブルを作成
データを選択して、「挿入」タブから「ピボットテーブル」を選択します。
１．まずは集計したいデータの範囲を選ぶ
データの見出し（ヘッダー）も含めて選ぶんがポイントやで。
ピボットテーブルの挿入
- メニューの「挿入」タブをクリックして、「ピボットテーブル」を選ぶんや。
- そしたら、「テーブル/範囲を選択」ってダイアログボックスが出るから、そこに先ほど選んだデータ範囲が入ってることを確認してな。
- 「新しいワークシート」を選ぶか「既存のワークシート」を選んで、ピボットテーブルの位置を決めるんや。
フィールドを配置
- ピボットテーブルの作成画面が出てきたら、右側に「ピボットテーブルフィールド」リストが表示されるんや。
- ここで、行、列、値、フィルターのエリアにデータの項目をドラッグアンドドロップして配置するんや。
- 行：行見出しにしたいデータをここに入れる。
- 列：列見出しにしたいデータをここに入れる。
- 値：集計したいデータをここに入れる（例：売上合計とか）。
- フィルター：特定の条件でデータを絞り込みたいときに使う。
カスタマイズ
- フィールドを配置したら、自動でピボットテーブルが生成されるから、必要に応じてフィールドを追加したり、並べ替えたりしてな。
- データの形式を変更したり、計算方法（合計、平均、カウントなど）を調整したりもできるで。
これでピボットテーブルの完成や！どんどん試して、自分なりのデータ分析を楽しんでや～！
２．日付別・商品名別の売上金額の集計
リストの途中に空白行や空白列がないようにします。1件分のデータを1行で入力します。半角と全角の文字が混ざっていると別の商品として集計されます。そのばあい、《検索と置き換え》から《置き換え》をクリックし《オプション》から《検索する文字列》に「正しい名前」を入力し《すべて検索》をクリックします。
①セル【A4】が選択されていることを確認します。（リスト内ならどのセルでもかまいません）
②《挿入》をクリックします。
③《ピボットテーブルとピボットグラフ》が表示されました。
④《ビボットテーブル》をクリックし、《テーブルまたは範囲から》をクリックします。
《テーブルまたは範囲からのピボットテーブル》が表示されました
⑤《表または範囲の選択》に「リスト｜$A$4:$H$115 」と表示されていることを確認します。
《ピボットテーブルを配置する場所を選択してください》の《新規ワークシート》のボタンにチェックが入っていることを確認します。”新しくワークシートを作りますよ”という意味です。
⑥《OK》をクリックします。
- ピボットテーブルに表示するデータの計算方法を選択します（合計、平均、カウントなど）。
- 値フィールドのセルを右クリックして「設定値フィールド」を選択し、計算方法を変更できます。
ピボットテーブルフィールドリストが表示され、集計したいデータとその軸をドラッグ＆ドロップで選択します。
空白のピボットテーブルのレイアウトが表示されました。自動的にフィールドボタンの一覧を含む《ピボットテーブル》ツールバーが表示されます。下のツールバーに《Sheet2》と表示されます。
《ピボットテーブル》の空白に自由に配置できます
集計するフィールドをデータエリアに指定します。集計の方法は、合計・平均などの関数以外に割合を求めるなど、集計方法を選択できます。
⑦《日付》のフィールドボタンを行エリアにドラッグします。
⑧《商品名》のフィールドボタンを列エリアにドラッグします。
⑨《売上金額》のフィールドボタンをデータエリアにドラッグします。
クリックするのではなくて「ドラッグ」です。それぞれドラッグすると、マウスポインタが長方形から小さくなります。ドラッグする場所によってマウスポインタの形が変わります。また、下のエリアにもドラッグすると簡単に集計表が作れます。
ピボットテーブルのレイアウトは、あとから何度でも変更できます。ピボットテーブルは縦にも横にも自由に切り替えることができます。レイアウトを変更した直後に元のレイアウトに戻すには、クイックアクセスツールバーの《元に戻す》をクリックします。
３．日付のグループ化
日付や数値データをグループ化して、より詳細な集計を行います。
日付を月、四半期、年などにグループ化することが一般的です。
①日付のフィールドを選択します。
②セル【A5】を右クリックします。
ショートカットメニューが表示されます。
③《グループ化》をクリックします。
《単位》の《月》が選択されていることを確認します。
④《OK》をクリックします。
日付を《行》にドラッグします。
４．空白セルに値を表示
アクティブセルがピボットテーブル内にあることを確認します。
①右クリックします。
②オプションをクリックします。
③《レイアウトと書式》から《空白セルに表示する値》を「0」と入力します。
④《OK》をクリックします。
５．表示形式の変更
ピボットテーブルの作成直後は、集計結果にカンマ記号がついていないので、数値データを3桁区切りカンマの表示にします。
①セル【B5】をクリックします。
右クリックします。
②《値フィールドの設定》をクリックします。
《ピボットテーブルフィールド》が表示されます。
③《表示形式》をクリックします。
④《分類》の一覧から《数値》を選択します。
⑤《桁区切り(,)を使用する》をクリックし、☑にします。
⑥《OK》をクリックします。
《ピボットテーブルフィールド》ダイアログボックスに戻ります。
⑦《OK》をクリックします。
ピボットテーブルの売上金額に、3桁区切りカンマが表示されました。
３．ピボットテーブルを変更する（１）
作成したピボットテーブルは、全支店のデータが日付別・商品別に集計されています。
ピボットテーブルのページエリアに《支店》のフィールドボタンを配置して、支店ごとの日付別・商品名別の集計表に変更します。
【☑】して配置することもできます。このとき、数値データのフィールドは自動的に【値エリア】に配置されますが、それ以外のフィールドは《行エリア》に配置されます。
①アクティブセルがピボットテーブル内にあることを確認します。
②《支店》のフィールドボタンをページエリアにドラッグします。
ページエリアに《支店》のフィールドボタンが追加され、「すべて」と表示されます。これは現在の集計結果が全支店の集計結果であることを示します。
ページ替えをして「京都」の集計結果を表示します。
③《支店》フィールドの「（すべて）の▼をクリックします。
④支店の一覧から《京都》を選択します。
⑤《OK》をクリックします。
抽出したい支店（ここでは京都）のボタンをクリックすると、指定した支店だけのデータを抽出できます。
１．フィールドの入れ替え
①アクティブセルがピボットテーブル内にあることを確認します。
②《取引先》のフィールドボタンを行エリアに、《日付》を《支店》のプロフィールドボタンの左下にドラッグします。
行エリアに《取引先》のフィールドボタンが追加され、取引先別に分類された支店別・商品名別の集計表が作成されます。
初期設定ではフィールドボタンは縦に並ぶようになっていますが、データを選択しやすいように《支店》と《日付》のフィールドボタンを横にならべることもできます。
①アクティブセルがピボットテーブル内にあることを確認します。
②右クリックから《ピボットテーブルオプション》をクリックします。
③《レイアウトと書式》から《フィールドの表示》の《左から右》を選択します。
④《OK》をクリックします。
フィールドの削除
①《日付》のフィールドボタンをピボットテーブルのエリア外にドラッグします。
これは便利だわ！
４．ピボットテーブルを変更する（２）
ピボットテーブルを使って集計結果を並べ替えることにより、売れ筋商品や売り上げが低迷している商品を分析することができます。
ピボットテーブルは、複数のデータを集計して、異なる視点からデータを分析するための強力なツールです。主に以下のような情報を分析するのに適しています：
- 売れ筋商品の特定: ピボットテーブルを使って商品別の売り上げを集計し、売り上げの高い商品や人気のある商品を見つけることができます。
- 売り上げの低迷している商品の特定: 同様に、売り上げの低い商品を見つけることも可能です。こうした商品について詳細な分析を行い、なぜ低迷しているのかを考察することができます。
- カテゴリ別の売り上げ比較: ピボットテーブルを使ってカテゴリ別に売り上げを集計し、どのカテゴリがより収益性が高いか比較することができます。
- 時間別の売り上げ変動: 日付や月別に売り上げを集計し、季節性やトレンドを分析することができます。
- 地域別の売り上げ比較: 地域別に売り上げを集計し、地域ごとの売り上げの違いを分析することができます。
ピボットテーブルを使って集計結果を適切に並べ替えることで、データの洞察を得ることができます。こうした洞察を元に、ビジネス上の意思決定や戦略立案に役立てることができます。
1．詳細データの表示
ピボットテーブルの集計結果を並べ替えると、売れ筋商品や売り上げが低迷している商品を分析できます。
《支店》が《京都》で「スマホ」に詳細データを表示します。
①セル【C6】をクリックします。
②右クリックし《詳細の表示》をクリックします。
シート「Sheet2」が挿入され、丸井のデータがリスト形式で表示されます。
２．オートフォーマット
《行エリア》や《列エリア》に複数のフィールドを配置するときは、大きな分類を上に配置し、分類を更に分けた《種別》、《商品名》の順で並べます。
ピボットテーブルにオートフォーマット（文書に自動的に書式を設定する機能）を設定します。
①アクティブセルがピボットテーブル内にあることを確認します。
②《デザイン》をクリックします。
《オートフォーマット》ダイアログボックスが表示されます。
③一覧から好みの《テーブル》を選びます。
④《OK》をクリックします。
表に選択した書式が設定されます。設定した書式を削除する場合は、《オートフォーマット》ダイアログボックスに一覧から《なし》を選択します。
3．集計方法の変更
ピボットテーブル内のセルで右クリックし、「値フィールド設定」を選択して、集計方法を変更します。
総計に対する比率
現在のピボットテーブルは、全取引先に対する支店別・商品名別の集計表（合計）です。このピボットテーブルを、総計に対する売上高構成比を表す集計表に変更します。
データエリア内の任意のセルをクリックします。
①右クリックから《値フィールドの設定》をクリックします。
《ピボットテーブルフィールド》ダイアログボックスが表示されます。
③《集計の方法》に《合計》が選択されていることを確認します。
④《計算の種類》から《総計に対する比率》を選択します。
⑤《OK》をクリックします。
全体の総計を100％にした、それぞれの割合を求める集計表が表示されました。
列方向や行方向の比率の選択もできます。また、《基準値に対する比率》も求めることができます。
フィールドの並び順を変更する
値エリアの《数量》を《合計/売上金額》の下にドラッグすると、《合計/数量が》右側に表示されます。
オリジナルの計算式で消費税を集計する
①ピボットテーブル内をクリックする。
②《ピボットテーブル分析》から《計算方法》→《フィールド/アイテム》→《集計フィールド》
クリック。*10/100）と入力し、《OK》を押します。
税込み金額から消費税を求めるには《=INT(金額×10÷110》です。税込価格に潜んだ、私たちが実際に払っている消費税です。税率10％の場合には「8,550,000円×10％ ÷（1＋10％）＝777,272円」となります。
税抜き分を計算するには《数式》欄の《0》を削除し、《フィールド》から《売上金額》をクリックして《フィールドの挿入》をクリック、《売上金額》が表示されるので《-》を入力し、《フィールド》から《消費税分》をクリックして《フィールドの挿入》をクリックすると《消費税分》として表示されるので《OK》をクリックすると《税抜き分の金額》が表示されます。
税抜き分の金額まで表示されるのね。
５．ピボットグラフを作成する
ピボットテーブルからピボットグラフを作成することで、視覚的なデータ分析ができます。
- 挿入タブからグラフの種類を選択し、必要なデータ範囲を指定して作成します。
1．ピボットグラフの作成
ピボットテーブルからピボットグラフを作ると、数値の大きさや推移、割合など、数値の全体的な傾向を把握しやすくなります。
①「Sheet1 」が表示されていることを確認します。
②ピボットテーブル内のセルをクリックします。
③《挿入》から《おすすめグラフ》をクリックします。
全取引先の支店別・商品別のグラフが作成されます。
2．表示の切り替え
ピボットグラフの「取引先」が「丸井」に支店別・商品名別の売上グラフに変更します。
①《取引先》のフィールドボタンの▼をクリックして、一覧から《丸井》を選択します。
②《OK》をクリックします。
フィールドの追加
①《担当者》のフィールドボタンを《商品名》の下にドラッグします。
②《商品名》のフィールドボタンをエリア外にドラッグして削除します。
数値軸の書式設定
数値軸の表示単位を【万】に変更します。
《グラフ》ツールバーの《軸の書式設定》使います。
①図を右クリックして数値軸をポイントします。
②《軸の書式設定》と表示されたことを確認します。
③《表示単位》の▼から、《万》を選びます。
④《表示単位のラベルをグラフに表示する》が☑になっていることを確認します。
⑤《OK》をクリックします。
- データを入力します。Excelの表形式で、集計したいデータを入力します。
- ピボットテーブルを作成します。データを選択し、[挿入]タブの[ピボットテーブル]を選択します。ピボットテーブルウィザードが表示されますので、必要な設定を行い、ピボットテーブルを作成します。
- ピボットグラフを作成します。ピボットテーブルを選択し、[挿入]タブの[ピボットグラフ]を選択します。ピボットグラフウィザードが表示されますので、必要な設定を行い、ピボットグラフを作成します。
- ピボットグラフをカスタマイズします。作成したピボットグラフを選択し、グラフの種類や色、軸のラベルなどをカスタマイズします。
Excelでピボットグラフを作成する一般的な手順です。ピボットグラフを使うことで、膨大なデータを視覚的に表現することができるため、データ分析やビジネスの意思決定などに役立てることができます。
ピボットグラフは、Excelのピボットテーブルを使ってデータを集計して、視覚的にわかりやすく表現するグラフです。
あとがき
ピボットテーブルを使えば、複数の表を表示したりとか、色々な機能を使って表示したりとか、デザインとか表示方法を変更したりとか、色々な機能があります。集計する場合はしっかり使いこなしたいものです。
ただし、ピボットテーブルのデメリットとしては、自由にデザインができないということがあります。たとえば、不要な列を削除しょうとするとエラーになります。
コメント