2014年04月06日

Excelでデータの作り方

今更なんだけど、Excelで集計表を作る時のデータの作り方について。。。
特にピボットテーブルを使う時のデータソース(元データ)を作る時はこの方法で作らないと、ピボットテーブルが上手く作れなくなってしまします。

特にExcelを使いこなしていない人に多いのが、始めから見やすい表を作りがちになってしまうことがあります。
勿論、エクセルにはその様な作表をサポートするテンプレートやデザインが用意されていて、それはそれで事足りてしまったりもするんだけど、、、
データ集計や、ピボットテーブルをつかった集計、分析を行おうとするんならそれなりのデータの作り方をした方が後々データの活用の幅が広がって来るんです。

例えば、日々の商品の売り上げを集計して表にまとめていく時、始めからこんな表を作っていってしまう人がいる。
2014040601.jpg
一見分かりやすくまとめられているけど、始めからこんな形でデータを作っていってしまうと、少なくとも2つの問題がでてくる。
●4月に売れたりんごの売上の合計を計算したい時に売上データが1行おきになっているから、範囲指定で合計を表示させたり、SUM関数がつかえない。
●りんごが売れた順にデータを並び変えようとしても、日付の欄がセルの結合を行っているんでデータの並び変えが出来ない。
後は、4月にみかんの売れた日数が集計できないとか、他にも色々ありそうだ。。。

だったら、どんな集計が良いかというと、
2014040602.jpg
こんな表になる。集計表と言うより、集計表を作るためのデータベースと言ったところだろか。
但し、こういう風に作っておくと、オートフィルタでデータの並び替えや、抽出も出来るし、勿論ピボットテーブルも作成可能になるのだ。
肝は、「1件のデータ(1件の出荷)は1行に記入する」と言うこと。1件のデータとは何かと言うのを明確にしておく必要がありそうだ。
それから、フィールド(列の項目)から、計算できる項目は省くと言うこと。
例えば、“単価”と“数量”がフィールドに設定してあれば“金額”は不要。なぜなら“単価x数量”で求めることができるから、ピボットテーブルの集計フィールドの追加で求めることができるからだ。
つまり、“単価”・“数量”と“金額”はだぶったフィールド(項目)になるということなのです。。。
データソースは“Simple is best”なのです。

ただ、この集計も一つ抜けているのがある、それは其々の1件、1件のデータを特定できるものが無いということ。
2014040603.jpg
この様に左端にIDと言う列をつくって重複しない記号を入力しておくことも肝だ。
なんでこれが必要かと言うと、データの件数を求める場合に重複が無く漏れ(空白)も無いデータを作っておくと何かと便利な時があります。このIDの数を数えればデータの件数が求めたりすることが出来ます。(データベースではこれを“ユニークなナンバー”等と言ったりします)
で、こんな集計表があれば、ピボットテーブルで次のようなクロス集計も可能になるんです。
2014040604.jpg
ピボットテーブルの集計結果だけでは表の体裁が良くないので、最終的にはここからコピーして体裁を整える必要があるけど、データを活用する(どう活用するか)と言う目的のためには、この様なデータの作り方が必要になってくるんです。


関連ページ
ピボットテーブルを作ってみた
posted by haku1569 at 15:36| Comment(2) | Excelの基本 | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
ご指導ありがとうございます。
Posted by 井上 勝美 at 2016年07月14日 02:34
山本と言います。
エクセルの分析処理を学習しています。
数学をあまり勉強していなかったので、大変です。
@貴サイトで行っている処理のデータをどこかでダウンロードできますか?
もし、出来なければ、データを画像でもいいので、示してもらえませんか?
実際に同じことを実施することで、正しく処理できたと確信できるし、もし違っていればどこが
間違いなのかを質問もできるかなと思います。
A「haku1569」とはエクセルのいわゆるアプリですか?そうだとしたらダウンロード可能ですか?
宜しくお願いします。

Posted by 山本 益夫 at 2017年09月18日 08:25
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。