ピボット テーブル ＆ピボット グラフ
家計簿や商店の売上データの記帳の仕方にいろいろな方法があり、また、工夫されると思いますが、ピボット テーブルを使うことを前提に考えれば、
その仕分け方に、それほど注意を払わずに済みそうです。
用意された項目、例えば「月日、商品名(家計簿なら、支出科目)数量、金額、取引先、備考」の５〜６列位に分類(集計の目的によっては＋α)
されていれば、
後はピボット テーブルでいろいろな角度からデータの抽出や集計ができるからです。
月毎の小計、期別小計なども必要ありません。(連続したデータで、節目毎にピボット テーブル レポートを作成すれば良いことになります。)
Microsoft-資料より
ピボットテーブル レポートをどのようなときに使用するか
- ピボットテーブル レポートは、大量の複雑なデータをわかりやすく、ひとめで理解できるようにしたいときに使用します。
- 特に、集計する値の数が多く、各数値についての複数の要因を比較するような場合に適しています。
- ピボットテーブル レポートでは、フィールドをレイアウト上のさまざまな場所にドラッグすることができるので、 データの表示を変更して、より詳細な情報を表示したり、集計内容を変更したりすることが可能です。
ピボットテーブル レポートを作成するには
- ワークシートで、データが入力されている任意のセルを選択します。
- [データ] メニューの[ピボットテーブルとピボットグラフ レポート] をクリックします。
- [ピボットテーブル/ピボットグラフ ウィザード - 1/3] ダイアログ ボックスで、[Excel のリスト/データベース] が 選択されていることを確認します。
- [作成するレポートの種類を指定してください。] の [ピボットテーブル] が選択されていることを確認します。
- [完了] をクリックします。
(レポートのワークシート領域が表示され、[ピボットテーブルのフィールド リスト] に選択可能なフィールドの一覧が表示されます。)
メモ
フィールド リストが表示されていない場合は、[ピボットテーブル] ツールバーの (フィールド リストの表示) をクリックします。
必要な情報を表示するには、フィールド リストからドラッグするフィールドを選択し、レイアウト上のどの場所にドロップするかを決めます。
データ アイテムのドロップ エリアでは数値が自動的に合計されるため、通常はこのドロップ エリアに数値データをドラッグします。
(ピボットテーブル レポートを作成した後で、レイアウト、書式、詳細データの表示レベルを変更できます。)
データを設定するには、次の手順に従います。
- Excel のデータが十分に整理されていることを確認します。データを整理しておくことは重要です。
- たとえば、先頭行はレポートのフィールド名に使用されるため、列ラベルが含まれるようにします。
- 1 つの列に表示されているデータの種類がすべて同じであることを確認します。
- たとえば、1 つの列にはテキスト データのみ、別の列には数値データのみが表示されるようにします。
- 自動計算される小計を削除します。小計および総計はピボットテーブル レポートで計算されます。
(以上 MS−資料より)
視覚的に分かり易い「ピボット グラフ」の説明から入ります。
ピボット グラフ
カタカナ語が頻繁に使われますので、用語を図示しました。
左図の段階では、「ピボットテーブルレイアウト エリア」、「グラフ レイアウト エリア」と言います。 「フイールドボタン」が各エリア配置されたときに、始めて「ピボットテーブル レポート」、「ピボットグラフ レポート」と言われます。
図示 作成手順
第１図
- 表の中の任意のセルをクリックします。(第１図参照)
- [データ]メニューから →[ピボットテーブルとピボットグラフレポート(P)]をクリックします。(第２図参照)
- [ピボットテーブル/ピボットグラフウィザード-1/3]が現れます。(第３図参照)
[Excelのリスト/データベース(M)]と、今回は [ピボットグラフレポート(ピボットテーブルレポート付き)(R)]を選択しました。(学習を進めている内に分かりますが、どちらを選択しても大差が無く、 グラフ化が不必要であれば、「ピボットテーブル」になります。)
[次へ]をクリックします。
第２図 「データ メニューから選択」 第３図 「グラフウィザード-1/3」ダイアログボックス
第４図 元の表と「グラフウィザード-2/3」画面
- 使用するデータの範囲が、ムービングボーダー(点滅点線)で囲まれています。[ピボットテーブル/ピボットグラフウィザード-2/3]で表示される
範囲に間違いがないか確認します。
(第４図参照) - 「ピボットテーブル/ピボットグラフウィザード-3/3」で、ピボットテーブルの作成先を指定します。
今回は、「新規ワークシート」を選択しました。(第５図参照)
[完了]をクリックします。
グラフは前回の「グラフ一発作成」にありましたように、新しいシート「Graph1」に、画面 78％に表示されます。
列見出しが全部埋まっていれば、100%信用できます。[A1]が空欄になっていると、その場合は×です。
若し、何千行の場合確認が大変です。何百何千行の表の場合、ノートパソコンの場合では、予め[Ctrl＋FN＋→(END)]キーを押して、 最終行を見ておいて、[ウィザード-2/3)]に直記入するのも手です。
合計欄のある表の場合は、一緒に含まれてしまうので注意しましょう。
[次へ]をクリックします。
--☆--ウィザードを使用すると、ワン,ツー,スリーの簡単な操作で「ピボットテーブル ビュー」(または、「ピボットグラフ ビュー」) の作成に必要なすべての機能を備えた新しいワークシートがすぐに作成できます。--☆--
第５図 「グラフウィザード-3/3」画面
- 新しいシートに「Sheet n」が挿入され、ピボットテーブルが作成されます。ピボットテーブル ツールバーも表示されます。
更に、新しいシート「Graph1」に「グラフ用テーブル」が作成され、ピボットテーブル ツールバーも表示されます。(第６図参照)
(ピボットテーブルツールバーが表示されていない場合は、メニューバーの[表示]→[ツールバー]→[ピボットテーブル]で表示されます。)
第６図 新しいワークシート[Graph1]に配置された、「グラフ レイアウト エリア」
「Graph1」ワークシート
ピボットグラフの構成
各パーツの名称
ピボットテーブルのフィールド リスト
[ピボットグラフのフィールド リスト] には、
「元データ」の列見出し名 「(採用者,性別,年齢・・・支店地,売上額,等１０項目」が格納されます。
この中からドラッグするアイテム(項目)を選択し、ドラッグして、各レイアウト エリアにドロップします。
ピボットグラフのレイアウト エリア
１．上のエリア「ここにページのフイールドをドラッグします」
２．下のエリア「ここに項目のフイールドを追加します」
３．系列欄「ここに系列のフイールドを追加します」(グラフの凡例に当たります。)
４．中央灰色部分(グラフのプロットエリアの当たる)「ここにデータアイテムをドラッグします」
(数値データなら何でも可。例：年齢をドロップして、下のエリアに社員をドロップすると年齢構成が分かります。)
第６図右端の「ピボットテーブルのフィールド リスト」からアイテムをドラッグし、
ピボットテーブルの「レイアウト 各エリア」にアイテムをドロップします。
--☆--ドロップしたフィールドボタンの削除は欄外(マウスポインターが×印に変わる)にドラックして削除します。--☆--
配置例
１．の上のエリアに「残りのアイテム」の必要部分をドロップします。(フィルタ機能で更に細かいデータが選択されます。)
(１つのアイテムを2つのエリアに配置することはできません。)
２．の下のエリアに「正社員」と「契約社員」をドロップします。
３．の系列欄に「採用者」をドロップして、系列を色分けします。
４．の中央部に[売上額]をドロップします。
結果は下図の通りになりました。
第７図
白斜め矢印先の性別をクリックすると、更に男女別に分離されて片方のみ表示されます。全てのフィルタでそれができます。
下図がそれです。
第８図 (女性を選びました。)
「ピボットテーブル/ピボットグラフ ウィザード-3/3」 で[レイアウト]を使用する方法
第５図で「完了」にしないで、左端の[レイアウト]タブ(左下の白矢印)をクリックしてみましょう。
第５-２図 ウイザード-レイアウト
第5-３図 右側にあるフィールド ボタンを夫々ドラッグ
第５-４図 レイアウト画面は[OK],ウイザード3/3画面は[完了]でグラフが表示されました。
「ピボットグラフ」は新しい[Graph１]シートに、「ピボットテーブル レポート」は指定どおり同シート内に作成されています。
グラフの編集
一般的グラフと殆ど同様です。しかし、ピボットの場合は、ツールバーに「グラフの書式設定」のボタンがありません。また、名前ボックスからグラフのパーツを呼び出すこともできません。
そこで、メニューバー →グラフから[グラフの種類],[元のデータ],[グラフオプション]を選び夫々の編集を行います。
グラフエリア夫々の「書式設定」を呼び出す方法
- エリア上で、右クリックし、エリア名の書式設定をクリックします。
- エリアを選択してから、[Ctrl＋１]のショートカット キーを使用します。(グラフに限らずEXCELの全ての書式設定に有効ですので、私は専らこれで行っています。)
- 系列や凡例の個別の要素を検出するには、１回目のクリックで系列を選択し、２回目のクリックでデータの要素を検出します。 (また、何でも良いから何か１つを選択してから、矢印キーを押し続ける法もあります。) その上で右クリックなり、[Ctrl＋１]を使い「書式設定」をします。
詳細は、いろいろなグラフから「グラフ作成要領の追補]をご覧ください。
元データに関する注意事項 (EXCEL 技術資料より)
各列の見出し
最初の行には、各列の見出しを設定する必要があります。ウィザードは、これらの列見出しを
[フィールド] の名前 (データのグループに付けられる名前) として使用します。
(フィールドは、ピボットテーブルのレイアウト エリアにドラッグ アンド ドロップできます。)
空の行や列について
レポートで使用されるデータの範囲に空の行や列を設定しないようにします。 たとえば、データを区別するために空の行が使用されている場合、その空の行は削除する必要があります。
リストやデータベースの作成
1 つの列に表示されているデータの種類はすべて同じである必要があります。
たとえば、1 つの列にはテキスト データのみ、別の列には数値データのみが含まれるようにします。
Excel はピボットテーブル レポートに自動的に小計と総計を作成します。
合計欄について
[データ] メニューの [集計] コマンドを実行して作成された、自動計算を行う集計および総計が 元データに含まれる場合、同じコマンドを使って、これらの集計と総計を削除してから、レポートを作成します。
[集計] コマンドを使って作成される自動的に計算された集計と総計のデータは、 その構造を確認できるようにアウトライン表示されています。
その他
メモ
外部データを使用するには、Microsoft Query と Open Database Connectivity (ODBC)、
またはデータ ソース ドライバが必要になります。このため、この一連のコースでは、
外部データ ソースではなく Excel のリストまたはデータベースの元データを使った場合を説明します。
データ ソースの詳細については、Excel のヘルプを参照してください。
成功への秘訣３項目 (EXCEL 技術資料より)
その１
「開始する前に元データを確認すること。」
その２
「知りたい情報を整理して、使用するフィールドを決定すること。」
その３
第３の秘訣は「行で表示するフィールドは必ず[行エリア]にドラッグすること。」
最後の秘訣は簡単です。
ただし、完璧なレポートを作成したいなら、レイアウトをもう一工夫した方がよいかもしれません。
それに、レイアウト設定を間違えたとしても、心配は要りません。
ピボットテーブル レポートの作成とは、さまざまな要素を移動して、その表示結果を確認することです。
要素の移動は、何度でも簡単に行うことができます。「レイアウトの変更」 と呼ばれるこの操作は、
ほんの数秒で完了するもので、ピボットテーブル作成の通常のプロセスの一環として予期されている操作です。
これは、さまざまな壁に絵を掛けて、どこに絵を掛けるのが一番見栄えがするかを確認するプロセスに似ています。
ピボット テーブル
下記資料について、「ピボットテーブルの作成」をします。
元資料
上図のエクセル フアイルです。宜しかったら、ダウンロードしてください。(23Kb ウイルススキャン すみ)
野菜売り上げ表
ピボットテーブル作成手順
- テーブルの任意のセルをクリックします。
- [データ(D)]メニューをクリックします。
- [ピボットテーブルとピボットグラフレポート(P)]をクリックします。
- [ピボットテーブル/ピボットグラフウイザード1/3]が現れます。
- [EXCELのリスト/データベース(M)]と[ピボットテーブル(T)]にチエックが入っているのを確認して[次へ]進みます。
- [ピボットテーブル/ピボットグラフウイザード2/3]が現れます。
- [範囲(R)]に表全体が示されているのを確認します。ここでは８５行なので、
テキストボックスを[折り畳みボタン]で畳んで確認するのも良いでしょう。
若し、違っていればマウスでドラッグして選択します。(合計欄があった場合は要注意です。)[次へ]をクリックします。
- [ピボットテーブル/ピボットグラフウイザード3/3]が現れます。
- ここでは、表が大きいので、「新規ワークシート」を選択して、[完了]ボタンを押します。
すると、下図(第８図)が作成されます。(ピボットグラフビューアとはほんの少し異なり、下に有ったエリアが左辺になりました。)
ピボットテーブルの構成
第９図
各ぱパーツの名称
- ページエリア
ページ見出しの残りの必要項目を格納します。 - 列エリア
列見出しを表示します。(標本の対象を表示) - 行エリア
行見出しを表示します。(例えば月日) - データエリア
数値データを表示し、集計・総計が行われる
若し、グラフにしたかったら、ピボットツールバーの「グラフ ウイザード」で簡単に作成できます。
アイテム(項目)の配置例
１．「行エリア」に日付をドロップしましました。
２．「列エリア」に品名をドロップしました。
３． 中央の「データエリア」に売上をドロップしました。フィールドボタンは自動的に、ここでは[A7]に表示されています。
４． 残りの［項目］は「単価」を除いて全て「ページエリア」に入れました。(MS-ヘルプでは、全てでなくても良いとしています。)
第１０図
データの分析
この表は「仮定」です。このような傾向にあったとすれば、分析にも役立つと思います。
10月１〜７日の1週間(土〜日)の売上表です。(月曜日は定休日)
「天気の日の売上」と「曜日による売上」＆担当者売上成績
- 「行エリア」に「日付]「曜日」「天候」をドロップしましました。
この場合気を付けることは、「日付]の右側に「曜日」その右側に「天候」のように配置することです。
また、[名前],[性別],[年齢]などの場合も同様です。 - 「データエリア」に「売上」をドロップしました。
なお、「Excel はピボットテーブル レポートに自動的に小計と総計を作成します。」ので、各日の２行を「非表示」にして、見易くしたのが第１１と１２図です。
(データ例では日常食品なので、お天気にそれ程左右されませんが、雨具類は天気、幼児・児童用品は曜日・祭日といったように、 品目によっては顕著に現れるかと思います。また、価格でなく数量を表示([その他の機能欄]ー[データの個数]参照)すれば、コンビニなどでは在庫管理に役立つことになるでしょう。)
結果表示
第１１図 第１２図ー担当者売上成績(列エリアに[担当者]をドロップ)
データの編集と追加
データの編集
書式設定
これまでの「元のデータ」や「ピボットテーブル レポート」には、区切り点[,]が表示されていません。
若し、「元のデータ」に[,]を付けたとしても、「ピボットテーブル レポート」には、[,]無しで表示されます。下図参照
第１３図 「元データ」と「ピボットテーブル レポート」を同一シート内に配置
そこで「書式設定」が必要になりますが、その前に「 レポート」の並びが滅茶苦茶です。(なにを基準に並べているのか分かりません。) ピボットテーブルツールバーの「順序」で、「列順」は左右に、「行順」は上下に移動できます。
ピボットテーブルの書式設定を使う
数値に書式を設定する[,]の設定
- 数値に書式を設定するには、データ エリア内の任意のセルを右クリックします。
- 現れたメニューから[フィールドの設定(N)] をクリックます。
(または、[ピボットテーブル ツールバー]右から２番目の[フィールドの設定]ボタンをクリック） - [ピボットテーブルフィールド]ダイアログで[表示形式] をクリックします。
- [分類]ボックス一覧の[数値]を選択します。
- [桁区切り(,)を使用する]にチェックを入れ、OKをクリックします。
[ピボットテーブルフィールド]ダイアログボックスで表示形式を設定する場合は、データエリアのセル１つを選択するだけで、
データエリア全体に表示形式を設定することができます。
EXCELで一般に使用している[書式設定]ツールバーや[書式]メニュー →[セル]→[書式設定ダイアログ]のコマンドで各種設定ができますが、
その場合はデータ範囲をすべて選択する必要がありますので、何百行もあるデータの場合一寸面倒な作業になります。
データの追加
元データに「追加」,「削除」等変更が有った場合
ピボットテーブルのツールバーより
１．ピボットテーブル内のセルのどれかをクリックします。
２．ツールバーの左端[ピボットテーブル(P)]をクリックします。
３．現れたメニューから、[データの更新(R)]をクリックして完了です。
(また、ツールバーのピンクのバルーン風ボタンをクリックしてもできます。)
元データに「追加」,「削除」等変更が有った場合の「データ自動更新機能」
ピボットテーブルのツールバーより
- ツールバーの左端[ピボットテーブル(P)]をクリックします。
- 現れたメニューから、[オプション(O)]をクリックします。
- [ピボットテーブル オプション]ダイアログが表示されます。
- データソースオプション欄の[開くときに更新する(R)]にチエックを入れます。
- これで、次回EXCELを開いた時に「自動的に更新」されています。
- ですから、売上日誌等をただ書き続ければ良いことになります。
その他の機能
[フィールドの設定]から、各集計が行われます。
- ツールバーの左端[ピボットテーブル(P)]をクリックします。
(データ エリア内の任意のセルを右クリックしてもOKです。) - 現れたメニューから[フィールドの設定(N)] をクリックます。
- 「ピボットテーブル フィールド」ダイアログが現れます。
合計
データの個数
平均
最大値
最小値
積
数値の個数
標本標準偏差
標本偏差
標本分散
分散
(「データエリア」の表示ボタン[合計/＊＊＊]をダブルクリックしても、「ピボットテーブル フィールド」ダイアログが現れます。)
例えば、最大値を選択したのが第１４図です。
表内の最大値は黄色枠で表示されます。、行計、列計、総計欄にも表示されます。また、項目ごとに(この図では名前)最大値が表示されます。色分けをご覧ください。
第１４図
日付けのグループ化
ピボットテーブルの特性
第１５図
２列棒状で左図のデータがあったとします。これを、月別に纏めたいときに、「日付けのグループ化」を使います。
方法は「日付けのセルを選択する必要があります。例えば「7月1日」をクリックして、第１６図の手順になります。その後に「月」か「四半期」のダイアログがでますので、どちらかを選択します。」
「月」を選択すると、第１６図左上のようにデータは「月別」に表示されます。
四半期を選択すると、第１７図のようになります。「四半期」「第３四半期」「第４四半期」の文字はEXCELが判断して記述します。
第１８図は、第１７図の「日付」「集計」欄のの「数値データ」部分を選択し、ピボット ツールバーの「グラフウイザード」で グラフ縦棒を選択しました。グラフ系列が青１色だったものを各色に着色しました。 「ピボットテーブル レポート」から、グラフを作るのは簡単ですので、 「ピボットグラフウィザード-1/3」で「ピボットテーブル」を選択しても大差はないです。
第１６図
第１７図 第１８図
「関数(SUMIF)で集計」したのと、「ピボットテーブル」集計の比較
SUMIF 関数で集計
第１９図
SUMIF 関数は[SUM]と[IF]関数を兼ね備えた、非常に便利な関数です。
１．元データは、[A2：D23]で、[日付],[入金],[支出金額],[備考]の４列です。
２．集計科目を[E列]にとり、[集計値]を[F列]にとり[SUMIF]関数で表示させました。
関数入力は「 =SUMIF(D5:D22,"電気料",C5:C22) 」で、「水道料」の場合は同右に「電気料の数式」を貼り付けて「電気料」を「水道料」に替えるだけで、OK です。
同様に、各科目についても行います。
日常生活での支出科目は、大きく分類しても１４〜５項目位にはなりそうですので、上の貼り付け、項目の書き換えを１０数回はしなければなりません。
ところが、下記「ピボットテーブル レポート」では、１分足らずで集計ができてしまうのです。
「ピボットテーブル」集計
第２０図
元データは、第１９図の[A2：D23]で、[入金、支出金額]を１列にまとめ、[日付]、[備考]の３列にしました。その分、支出は[-]に表示してあります。
右図が「元データ」になります。
各科目の集計額は、２０行目に表示されました。
「ピボットテーブル レポート」の[O6]セルには、同図数式バーに表示された数式を入力し、現在日の残額を表示しました。
「元の帳簿(右図)に出入金を記入するだけで、何年でも使用できます。また、「日付けのグループ化」を行えば、
１ヶ月を１行で表示することもできます。第２１図
このことから、場合によっては、便利な関数より便利な「ピボットテーブル」と言うことができます。
第２１図
MS-家計簿テンプレートとピボットテーブル
MS-家計簿テンプレート
モデル１
至れり尽くせりの家計簿です。下図は或る４月の７日までですが、３１日まであります。Shieet１２枚で１年分記帳します。
これだけ、細分類して几帳面に付けるのは大変のようですが、結構利用者がいて、ダウンロード数が９万件あり、評価は３３３０でした。
関数を使用し、各項目の合計が表示されるようになっています。
第２２図
モデル２
第２３図
上の図は、半月支出170,000円親子２世代４人家族のようで一般的な家計のようです。この家計簿は意外と人気が無く、ダウンロード数２７００件、評価は９４で、モデル１の「３５分の１」でした。
しかし、ピボットテーブルを使うとこれで十分なのです。(合計欄とNo、それに費目欄、摘要欄の数字(集計に使用している)も必要ありませんので、
カットしてピボットテーブルで集計してみました。下図をご覧ください。)
モデル２を「ピボットテーブル」で集計
第２４図
- 第２３図の日付順羅列型家計簿が「ピボットテーブル」を使うと、項目毎に分類されて、かなり、明瞭に表示されることが分かります。
- ここの例では、列エリアに「費目」と「詳細」の２つをドラッグしました。
(注：「行エリア」なり「列エリア」に２つの項目をドラッグすると、必ず小計欄ができます。 (３つ入れると３行表示)従って数値が重複しますので、上図では「小計欄」の列は非表示にしてあります。) - 若し、列エリアに「費目」１項目のみをドラッグし、同様に,「詳細」,「摘要」の３種類の 「ピボットテーブル レポート」を作りますと、３つの観点から支出の分析ができます。また、年間を通して、夫々「日付のグループ化（月毎）」をすると、１２行で「年間の支出状況」が把握できます。そこで「グラフ表示」すると傾向が一目瞭然なるかと思います。
- 第２２図の「家計簿」のように、横長の記帳「１２ケ月分」よりは勝ると思います。
マイクロソフトでは下記のように言ってます。
- Excel のワークシートに、会社の売上を入力した何百行、何千行にもわたるデータがあるとします。 このデータの持つ意味をひとめで把握できる人はいないでしょう。 では、どうすればデータの意味を読み取れるでしょうか。
- また、ピボットテーブル レポートは、データを比較したり、パターンや関連性を明らかにしたり、 傾向を分析したりするのにも役立ちます。
- さまざまな形式で表示したデータを比較し、明確にし、分析することで、データを意味のある情報にすることができます。
- ピボットテーブル レポートを使うと、データを無意味なものにしないよう、編集や集計を行うことができます。
第２５図 (参考)
ピボットグラフの場合は、下の「項目軸」に[日付]または各項目を配置します。図は、[詳細]を項目軸に配置し、
[詳細]の▼ボタンを押して、現れた画面から[Ctrl]キーを押しながら３点を選択してグラフ表示したものです。
この様に任意の個々を抽出することもできます。