EXCEL ピボットテーブル（？） ご教授下さい

質問

仕事で売上データの分析を行う事になりました。
データベースは、５年間の四半期ごと、品種ごと　になっています（添付画像ご参照ください）
以下のような事をするにはどんな分析が適しているでしょうか？
ピボットテーブル（経験無し）を使えば良いのでしょうか？それとももっと高度なスキルが必要でしょうか？

・季節変動のある商品のため、（例えば）５年間の３Ｑだけの比較をしたい。
　それをグラフ化したい。

・会議でみんなに見せたいので、グラフを簡単に切り替えられるようにしたい。

データを品種ごとに抽出してそれぞれグラフ化すれば良いのですが、品種が１００ほどありますので
できる範囲が限られます。これを機会に勉強したいのでご教授よろしくお願いします。

回答No.2

iapetus · ベストアンサー

投稿主さんが「データベース」と仰るこの表は、「広義のデータベース」ではありますが、「Excelが処理し易い狭義のデータベース」の構造ではありません。

コンピュータデータベースシステムは、列タイトル（これを「カラム」といいます）が定義された行単位データ（これを「レコード」といいます）の纏まり（これを「テーブル」といいます）が、いくつか集合し、テーブルのカラム同士が「論理的な関係性」で結びついた状態（これを「リレーションシップ」といいます）のデータ集合体のことを言います。

本来は、こういった情報管理は、リレーショナル・データベース・マネジメント・システム（リレーショナルDBMS、RDBMS）が担当すべき分野ですが、Excelでも、小規模なデータベース管理をするための機能があります。
しかし、Excelはそもそも表計算ソフトウェアであり、セルのカラムにタイトルを付け、データを行単位にすることで、簡易的なDBMSとして利用できるものの、自ずと限界があるのです。

また、データも、DBMSとしての「縦横表というマトリックス形式」の体裁になっていないと、非常に使い辛いものになってしまいます。
主さんがお示しの例には、合計行はあるわ、「くだもの」、「やさい」という大項目と、「いちご」、「はくさい」という小項目が、同じ列に混在しており、これは集計を煩雑にしてしまうデータの持ち方です。

また、行方向に品名の区分があり、列方向に年＋Qの情報がある、ということは、これは既に「集計結果」を取り終わった状態の表と何ら変わりません。

人には解り易くとも、コンピュータの処理には向きません。
データは、重複しないよう、区分は明確に、後は行方向に積み上げて保持するべきなのです。

また、年とQを区分するのに、年とQとて２行使っていますが、タイトルは１行しかExcelは認識できませんし、このような持ち方は、DBMSではしません。
YYYY年-nQ　というように、年とQを纏めてしまってもよいのです。

但し、年でもQでも、となれば話は違います。
そのためには、Excelの場合は、年の列、年＋Qの列、と言う風に、コンピュータに対し、データの区分を明示してやる必要があります。

データは次のように持つべきです。

もし、年も、年＋Qも、どちらでも集計を取る必要があるのなら、

　品目　　品名　　　年　　　年・Q　　　売上

という項目だけを用意し、縦方向にデータを積み上げます（数字は適当です）。
具体には、次のようにし、入力の順番は、ランダムで全く問題ありませんし、必要であればデータを入力し終わったか、使う直前に並び変えしておけばよいです。
当然、この表には、オートフィルタでデータの抽出ができるようにしておきます。

（｜は、セルの切れ目）

品目｜品名｜年｜年・Q｜売上
くだもの｜いちご｜2010年｜2010年-1Q｜200
くだもの｜いちご｜2010年｜2010年-2Q｜100
くだもの｜いちご｜2010年｜2010年-3Q｜300
くだもの｜いちご｜2010年｜2010年-4Q｜150
くだもの｜みかん｜2010年｜2010年-1Q｜100
くだもの｜みかん｜2010年｜2010年-4Q｜150
くだもの｜みかん｜2010年｜2010年-4Q｜300
やさい｜白菜｜2010年｜2010年-1Q｜100
やさい｜白菜｜2010年｜2010年-4Q｜150
やさい｜白菜｜2010年｜2010年-4Q｜300
やさい｜白菜｜2010年｜2010年-4Q｜250
　　　　　：
　　　　　：

あとは、ピボットテーブルレポート機能で、どのような集計でも取れます。
逆に、ピボットテーブルレポート側から、データ項目のタイトルから、対象のデータを表示することもできますね。

また、グラフへのデータ供給も、ここからしてもよいし、集計から渡してもよいので、自由度が飛躍的に上がります。

いろいろと自動的に集計、グラフ化する場合は、マクロを組む必要があるでしょう。

また、もし、ほかにも要求されている、或いはデータ管理上必要な集計があれば、どのようなデータの持ち方をすればそれが取れるか、データの持ち方を変更しなくてよいか、全てを見通して、或いは盛り込んで、データ設計をすりょうに心がけましょう。
一度運用に供してしまうと、後でデータ構造を変えるのは、非常に難儀な作業になってしまいますので。
最初が肝心です。

お礼

ご回答ありがとうございます。

今までまわりにうまくエクセルを使える人がいませんので、
我流で調べながら使ってきましたが、ピボットについては、うまく使える時と使えない時があり、
理由がわかっていませんでした。

ご回答者様のご説明でようやくもやっとしたものが晴れました。

ようするにデータがたまたまピボットに向いていたら使えた、向いてなかったら
使えなかった、→この理由がいつも分かっていなかった。

です。

今回私としては大きく前進できました。ありがとうございます。

回答No.4

tsubuyuki

構造については皆さんがおっしゃるとおり、不適な構造ではあります。

・・・と言うより、実際に作ってみるとわかりますが、
「出来上がったピボットテーブル」は、おそらく質問文に添付された図に
非常に良く似た構造になるでしょう（まるっきり同じとは言いません）。

さて、質問文中には「どのデータに関してグラフにしたいのか」書かれていませんね。
いちご・みかんなど、細目に関してグラフにするのか、
くだもの・野菜など、小計についてグラフにするのか、
あるいは、２軸を使って細目・小計ともグラフにまとめるのか、
その辺りがあいまいですね。

とりあえず、
> これを機会に勉強したい
とのことですので、「このままの表のフォーマットでなんとか出来る」やりかたを。
関数の勉強です。
かなり見づらい図であると思いますが、判読ください。
（セル番地は実際の表と合わせてくださいね。）

> 季節変動のある商品のため
> ５年間の３Ｑだけの比較
この辺りがキーワードっぽいと勝手に判断して、
横軸に「商品（細目）」、縦軸に「個数」、値として「各年のデータ」を持つとして、作ります。

まず、データの表を作ります（既に出来ていると思いますが）。

Ｂ１８セルに「抽出四半期」を入力する場所を作ります（図の青いセル）。
とりあえず、第３四半期「Ｑ３」と入力しておきましょう。
（今後、入力規則・リストを使って選択できるようにするとなお良いかもしれませんね。）

で、図のグラフの左側にあるような表を作ります（Ａ１９～Ｃ２３）。
横には「年度」、縦に「商品（細目）」を持つようにします。
２０１１年のいちごの欄（図ではＢ２０セル）に、
　　Ｂ２０セル：=HLOOKUP($B$18,$B$2:$E$8,MATCH(A20,$A$4:$A$8,0)+2,FALSE)
と入力し、以下必要分コピー（フィル）します。
２０１２年のいちごの欄（図ではＣ２０セル）にも同様に、
　　Ｃ２０セル：=HLOOKUP($B$18,$F$2:$I$8,MATCH(A20,$A$4:$A$8,0)+2,FALSE)
とし、これも必要分コピー（フィル）します。

この表を基にグラフを作ってやります（詳細割愛）。

出来上がったら、Ｂ１８セルを第１四半期（１Ｑ）～第４四半期（４Ｑ）まで切り替えてみましょう。
多分、連動してグラフも変わるはずですよ。
応用すれば、例えば「いちごの第３四半期」のデータだけを抽出することも出来ます。

そんなわけで、ＨＬＯＯＫＵＰ関数とＭＡＴＣＨ関数に関しては
「勉強のため」ですから、ご自身で別途調べてみてくださいね。
そう応用したら良いかを教えてしまったら勉強にならないですからね。

お礼

他のみなさんと違った視点からのご回答でとても参考になります。

色んな関数の組合せでパズルのような使い方はとても好きです。

新しい関数を知る事ができました。

ありがとうございます。

回答No.3

MarcoRossiItaly

ご質問の目的のためには、No.1 さん、No.2 さんのおっしゃるデータ構造の件が最重要です。構造の作り変え、今後のデータ蓄積の方法について、検討してください。

ピボットテーブルに限ったことではないのですが、きちんと勉強されたいのなら、次のような意識を持たれるとよいでしょう。

●まずは、インターネット上に「無数の」情報がありますので、検索しまくってみてください。必ずそうしてください。
●分からないことがたくさんあっても、とにかく自分の手であれこれ操作してみることです。はっきり言って時間がかかるので、そのつもりで。コンピュータ関係は、理解してからというよりも、使いながら覚える部分の比重が大きいように思います。そういうものです。
●こうした QA サイトで人に尋ねるのは、実際に操作して、「具体的な」質問が出てきたときに利用したほうが効率的です。そうでないと回答者としても、抽象的な答えしかできません。ここは教室みたいにカリキュラムに沿った指導を受けるためにあるのではなく、質問をする場なのですから、当然ですね。
●同じ、人に尋ねるのでも、身近なところに Excel が得意な人がいるようでしたら、その方に聞いたほうが早いかもしれません。ここは詳しい人が多数いらっしゃいますが、お手元のデータを見ながらはできないし、質疑応答の繰り返しで日数もかかります。
●必須ではないですが、Excel の分厚い書籍を手元に 1 冊用意して、辞書的に使ってみるのもよいかと思います。ネットで検索するのに比べると、無駄な情報へのアクセスを減らせるかもしれません。ただ本は有限なので、自ずと限界もあります。

お礼

ご回答ありがとうございます。

今回データ構造に問題があったことが大きな発見です。

色々ネットで探したんですけど、スキルが無くて見つけられませんでした。

参考書買って見ます。

回答No.1

keithin

＞これを機会に勉強したい

ホントにそう思っているのでしたら、確かにピボットテーブルを使うと便利に出来ます。
そのためには、まず添付図のように元の表を作り変えるところから始めなくてはいけませんね。

ご利用のエクセルのバージョンも不明のご相談なので詳細な手取り足取り操作手順はお話ししませんが、手抜きせずキチンと元の表を添付図のように作成し直してしまえば、ピボットテーブルとピボットグラフの操作は単なるドラッグ＆ドロップやオートフィルタの類似品に過ぎません。手さえ動かせば、直感的に簡単に操作できます。

メンドクサがって（たとえば今の表の体裁のまま）中途半端にやろうとすると、まず失敗します。

お礼

ご回答ありがとうございます。

＞まず添付図のように元の表を作り変えるところから始めなくてはいけませんね。

会社で得られるデータがあの形なので、それをうまく使えないかなと思いましたが、
表の構造が不向きな事がここで初めてわかりました。

で、めんどくさがらずに、表を何とか作り直し（2,000行にもなりました　＾＾；）ますと、
うまくピボットテーブルが使えました。

ありがとうございました。