行数、列数が大量にある表を解析したいです。

例えば100店舗あるお店の1年間(日ベース)の売上を分析して傾向を探るイメージです。普通に目視で解析するのは限界があるのでいい方法があればご教示ください。よろしくお願いします。

回答の条件
  • 1人1回まで
  • 13歳以上
  • 登録:2015/02/13 14:56:36
  • 終了:2015/02/14 14:55:07

ベストアンサー

id:a-kuma3 No.5

a-kuma3回答回数4584ベストアンサー獲得回数19242015/02/14 14:25:24

ポイント80pt

今どきの Excel は 200×1000 くらいのデータは扱えるので、グラフを書けば分かるだろうくらいに思ってたのですが、コメントでも書いたように、試しにデータを作ってグラフを書いてみたら Excel が固まってしまいました。
数分待っても返ってこないので、昼飯を食って待ってみたのですが、30分経っても終わらず。
並みの性能のノートPC(目盛りだけ、多めに積んでる) で、Excel 2010 を使いました。


やりたいことは、手元にあるデータから、平均的な売上よりも、多かったり、少なかったりする店舗や日付を知りたい、ということなのだと思います。
ひと手間かかりますが、以下のような感じで、それほど待たなくても、Excel でデータを抽出できると思います。


(1) データを、一行に、日付、店舗、売上の形に編集する
こんな感じです。
f:id:a-kuma3:20150214142024p:image
200店舗×1000日で、20万行のデータになります。
Excel で読み込む(or 貼りつける)のに、少々待ちます(と言っても、数十秒)。


(2) 売上の平均と標準偏差を求める
平均は AVERAGE 関数、標準偏差は STDEVP 関数です。
先ほどのようなシートだと、以下のような式になります。

=AVERAGE(C2:C200001)
=STDEVP(C2:C200001)

計算は、あっと言う間です。


(3) フィルタで絞り込む
店舗の売り上げが、特に何かの要因によって偏らないのであれば、正規分布に従うと考えて良いと思います。
正規分布に従う場合、例えば、平均値±標準偏差×2 の間に入るデータは、全体の 95.45% です。
正規分布 #統計的な意味 @ Wikipedia

大きい方と、小さい方に外れている合計が、全体の約 4.5% ということなので、2σよりも大きいデータは、全体の約 2.2% になります。
(2) で求めた、平均と標準偏差から、2σの値を求めます((2)の結果の参照を使っても良いのですが)。

=AVERAGE(C2:C200001) + 2*STDEVP(C2:C200001)

(1) のシートでフィルタを設定して、売上の数値フィルターで「指定の値より大きい」を選んで、先の 2σの値を入れて絞り込みます。
絞り込みは、あっと言う間に終わります。



(4) 抽出結果の判断
絞り込んだ結果は、上位 2.2% ですが、その中で極端に大きいデータは、正規分布に従っていない可能性があります。
また、特定の日付、もしくは、店舗のデータが多ければ、その日、もしくは店舗の売り上げに偏りがある、ということです。

店舗の確認であれば、フィルタで絞り込んだだけで、だいたい判別がつくと思います。
日付であれば、散布図を描くのが分かりやすいと思います。

フィルタで絞り込んだ状態で、表の範囲を選択してコピーし、別のシートに貼り付けます。
こうすることで、絞り込んだ範囲だけが貼りつきます。
データが完全に正規分布に従うとしても、絞り込んだ結果は 400件程度、もし、大きなほうにずれている日付や店舗があれば、抽出結果は、もっと少なくなります。

X軸方向に日付、Y軸方向に売上を選択した散布図を描くと、特定の日付(例えば、GWとか、何かの試合があった日)に偏りがあった場合に見つけやすいと思います。
f:id:a-kuma3:20150214142025p:image
# この図はランダムなデータを使って描いてるので、面白味がありませんが :-)

id:handmadedude

すごい!!これ永久保存版ですね。ありがとうございます。
確かに標準偏差を使って2σ内に収まらないものを見るというのは大学の授業で習ったような記憶が・・・
ちょっとやってみます。

2015/02/14 14:47:44
id:a-kuma3

「数学の勉強なんて、何の役に立つんだあっ!」って、結構、役に立ちますよね :-)

商品の種類とか、その他の要因(天候とか気温とか)などについては触れてませんが、もっと扱うデータが増えてきたら、因子分析とか、クラスター分析とかを使うことになると思います。
その辺りに踏み込むと、R のような統計処理のソフトを使うことになると思います。
R だと、グラフも描けます。

2015/02/14 19:15:29

その他の回答(4件)

id:takejin No.1

たけじん回答回数1482ベストアンサー獲得回数1922015/02/13 16:36:50スマートフォンから投稿

ポイント5pt

グラフ化すれば、分かるのでは?

他3件のコメントを見る
id:a-kuma3

質問から察するに、最低でも、店舗・日付・商品・売上の 4軸あるから、3D グラフでも微妙に面倒なんだと思いますよ。

z軸は売り上げに決まりだとして、「店舗にかかわらず、日付によって、なにがしかの商品が売れ行きが良いことがあるかも」などという仮定をおきながら、いくつもグラフを書くことになるかな。

2015/02/14 01:26:58
id:handmadedude

皆さん、ありがとうございます。
わかりにくい説明で申し訳ありません。
例えば
A B C ・・・
1/1 100万 110万 90万
1/2 300万 200万 100万
1/3 300万 90万 120万
1/4 100万 160万 140万
・・・
のような表があってその中で200万以上売り上げているような日の分布がどれだけあるかという調査になります。店舗数が10件程度で30日間とかならたけじんさんの仰るようなグラフを作ってもいいのですが、店舗数が100とか200とかあって、計測期間が2,3年分(1000日位)あるとグラフに出来ないのでどうしたものかと悩んでおります。

2015/02/14 09:11:45
id:Baku7770 No.2

Baku7770回答回数2832ベストアンサー獲得回数1812015/02/13 17:22:28

ポイント5pt

 ランキングで並び替える。まずはそんなところでしょう。

 他には指標を決めて算出する。

 まずはそんなところです。データをどうするかは問題ではありません。どう活用するか、データを利用してどのような行動に出るかが肝心です。

 昔聞いた半分笑い話のような話しですが、
 昔のコンピュータですから、ソートなんかも難しかった時代です。ある会社の電算室長が商品別の売上順位を出して、社長に届けた。
社長は、そこに置いといてで他は何も言わない。
頭にきた電算室長は、悪戯でそれまでベストで並べていたのをワーストで並び替えて提出した。
翌月同じものを社長に持って行ったところ、「待ってたんだ!おい、どうせだからこいつをどう使うかお前見ておけ」
で、一目商品別売上順位表に目を通すと、工場に電話して、
「〇〇と△△生産中止。売れていない」
次に商品開発部長に
「〇〇と△△の生産を中止させたから、代わりの商品を開発するように」
と目の前で電話したそうです。

その月から、電算室長が退職するまでずっとこの光景は続きました。

id:handmadedude

ありがとうございます。
ランキングで並び替えるでもいい案なんですが、上で書いたように
日付  A   B   C ・・・
1/1 100万 110万 90万
1/2 300万 200万 100万
1/3 300万 90万 120万
1/4 100万 160万 140万
・・・
のような表で200万とか300万売り上げている日がいつなのか、傾向があるのかというのを探りたいので並び替えてしまうと傾向が読み取りにくいです。
かつ店舗ごとにある場合100店舗あったらすべてやるのに膨大な時間がかかってしまいます。

2015/02/14 09:17:36
id:adlib No.3

adlib回答回数1950ベストアンサー獲得回数1202015/02/14 03:54:46

ポイント5pt

 
 棒線と曲線 ~ まゆつば論さまざま ~
 
 一日の総売上を棒線グラフにすると、雨の日は少ないはずだ。
 曜日ごとの売上を比較すると、定休日の翌日が多くあるべきだ。
 俗説や先入観で、月別の売上を予測すると、給料日直前は財布が堅い。
 
 ほんとにそうか、と確認すべきところ、手間ヒマかけたわりに確証が
得られない。会議資料にして配ると、てんでに屁理屈を述べはじめる。
(そもそも、図表やグラフに先天的アレルギーの連中が過半数を占める)
 
 つぎの試みは、第一日の売上に、第二日の売上を加え、さらに三日目
には過去二日分を加算する「累計ルール」で、パラパラ・ブックを作る。
 曜日単位(年52週)で、不自然なカーブを、担当者に説明させる。
 
…… 私の経験では、国立大学農学部獣医科を卒業したという変り者に
経理を任せたところ、異常な関心をもって取組み、みごとな帳票美学を
展開したケースがあります(業績不振のため活用できませんでしたが)。
http://q.hatena.ne.jp/1304727286#a1070187(No.1 20110507 10:33:55)
 
 日付ごとの「前年比」よりも、曜日ごとの「偏差値」が優先する。
 さらに「9.11」や「3.11」の出現で、過去の通説が壊滅する。
 つぎの事例は、たんなる武勇伝で、株主総会では通用しない。
 
 自社の中華麺「美味しくない」とその場で電話して全国で発売中止。
鈴木 敏文「語録集」—カリスマ経営者のハッタリ伝説なら“食わせもの”
── 《本当のようなウソを見抜く 20050121 プレジデント社》
http://booklog.jp/users/awalibrary/archives/1/4833450070
 
…… 迷信の偏見は他のすべての偏見にまさり、その理屈は他のすべて
の理屈に勝つ(Montesquieu)。
https://oshiete.goo.ne.jp/qa/2603535.html(No.2 20061222 10:29)
 

他1件のコメントを見る
id:adlib

 
>分析そのものを否定されても困るのですが<
…… 第一日の売上に、第二日の売上を加え、さらに三日目には過去二
日分を加算する「累計ルール」で、パラパラ・ブックを作る。
 

日付  A   B   C ・・・
1/1 100万 110万 090万
1/2 300万 200万 100万
1/3 300万 090万 120万
1/4 100万 160万 140万
 
 ↑原型 ↓累計
 
  A   B   C   D   E   F
1/1 100万 110万 090万
1/2 400万 310万 190万
1/3 700万 400万 410万
1/4 800万 560万 550万
 
 ↑累計 ↓横線グラフ(曜日 or 月別)
 
  1/1  1/2  1/3  1/4  1/5  1/6  1/7
A 100万 400万 700万 800万 _____/── ──
B 110万 310万 400万 560万 __________/----
C 090万 190万 410万 550万 _______________/

 
 関連Q&A
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1374751868
 エクセルの累計について(20111104 16:01:51)
 

2015/02/14 13:58:24
id:handmadedude

なるほど、累計に注目というのは考えもしなかったです。ありがとうございます。

2015/02/14 14:51:09
id:Qthulu No.4

Qthulu回答回数12ベストアンサー獲得回数32015/02/14 09:59:34

ポイント10pt

以下、Excelで。

1.店舗ごとの全期間・全項目のデータを1シートにまとめる。100店舗であれば100シートになる。
2.串刺し計算で平均やσを算出する。必要なら正規化する。
3.各店舗のデータと平均やσとの差分を3D-等高線グラフで表示する。

突出した値ならスパイク状の突起として視認できるのではないでしょうか。
日付を曜日で並べ替えたり、項目をカテゴリごとに並び替えたりすれば、グラフの起伏から傾向?が見えるのかもしれません。
VBAなら自動化できるのではないでしょうか。

他1件のコメントを見る
id:Qthulu

下のa-kuma3氏のコメントによると、3Dグラフ描画は厳しいようですね。
グラフではなく、条件付き書式を利用してセルの色を変える等したほうが良さそうです。

2015/02/14 14:28:55
id:handmadedude

ありがとうございます。条件付き書式も駆使していますが、例えば100店舗分設定するのは骨が折れます。まあ、ここでVBAなのかもですが・・・。

2015/02/14 14:48:48
id:a-kuma3 No.5

a-kuma3回答回数4584ベストアンサー獲得回数19242015/02/14 14:25:24ここでベストアンサー

ポイント80pt

今どきの Excel は 200×1000 くらいのデータは扱えるので、グラフを書けば分かるだろうくらいに思ってたのですが、コメントでも書いたように、試しにデータを作ってグラフを書いてみたら Excel が固まってしまいました。
数分待っても返ってこないので、昼飯を食って待ってみたのですが、30分経っても終わらず。
並みの性能のノートPC(目盛りだけ、多めに積んでる) で、Excel 2010 を使いました。


やりたいことは、手元にあるデータから、平均的な売上よりも、多かったり、少なかったりする店舗や日付を知りたい、ということなのだと思います。
ひと手間かかりますが、以下のような感じで、それほど待たなくても、Excel でデータを抽出できると思います。


(1) データを、一行に、日付、店舗、売上の形に編集する
こんな感じです。
f:id:a-kuma3:20150214142024p:image
200店舗×1000日で、20万行のデータになります。
Excel で読み込む(or 貼りつける)のに、少々待ちます(と言っても、数十秒)。


(2) 売上の平均と標準偏差を求める
平均は AVERAGE 関数、標準偏差は STDEVP 関数です。
先ほどのようなシートだと、以下のような式になります。

=AVERAGE(C2:C200001)
=STDEVP(C2:C200001)

計算は、あっと言う間です。


(3) フィルタで絞り込む
店舗の売り上げが、特に何かの要因によって偏らないのであれば、正規分布に従うと考えて良いと思います。
正規分布に従う場合、例えば、平均値±標準偏差×2 の間に入るデータは、全体の 95.45% です。
正規分布 #統計的な意味 @ Wikipedia

大きい方と、小さい方に外れている合計が、全体の約 4.5% ということなので、2σよりも大きいデータは、全体の約 2.2% になります。
(2) で求めた、平均と標準偏差から、2σの値を求めます((2)の結果の参照を使っても良いのですが)。

=AVERAGE(C2:C200001) + 2*STDEVP(C2:C200001)

(1) のシートでフィルタを設定して、売上の数値フィルターで「指定の値より大きい」を選んで、先の 2σの値を入れて絞り込みます。
絞り込みは、あっと言う間に終わります。



(4) 抽出結果の判断
絞り込んだ結果は、上位 2.2% ですが、その中で極端に大きいデータは、正規分布に従っていない可能性があります。
また、特定の日付、もしくは、店舗のデータが多ければ、その日、もしくは店舗の売り上げに偏りがある、ということです。

店舗の確認であれば、フィルタで絞り込んだだけで、だいたい判別がつくと思います。
日付であれば、散布図を描くのが分かりやすいと思います。

フィルタで絞り込んだ状態で、表の範囲を選択してコピーし、別のシートに貼り付けます。
こうすることで、絞り込んだ範囲だけが貼りつきます。
データが完全に正規分布に従うとしても、絞り込んだ結果は 400件程度、もし、大きなほうにずれている日付や店舗があれば、抽出結果は、もっと少なくなります。

X軸方向に日付、Y軸方向に売上を選択した散布図を描くと、特定の日付(例えば、GWとか、何かの試合があった日)に偏りがあった場合に見つけやすいと思います。
f:id:a-kuma3:20150214142025p:image
# この図はランダムなデータを使って描いてるので、面白味がありませんが :-)

id:handmadedude

すごい!!これ永久保存版ですね。ありがとうございます。
確かに標準偏差を使って2σ内に収まらないものを見るというのは大学の授業で習ったような記憶が・・・
ちょっとやってみます。

2015/02/14 14:47:44
id:a-kuma3

「数学の勉強なんて、何の役に立つんだあっ!」って、結構、役に立ちますよね :-)

商品の種類とか、その他の要因(天候とか気温とか)などについては触れてませんが、もっと扱うデータが増えてきたら、因子分析とか、クラスター分析とかを使うことになると思います。
その辺りに踏み込むと、R のような統計処理のソフトを使うことになると思います。
R だと、グラフも描けます。

2015/02/14 19:15:29
  • id:Qthulu
    「特異点」「傾向」の具体例を知りたいですね。
    表は各店舗で商品数×365(日)って感じのシンプルなものですか?
  • id:takejin
    知りたい傾向に応じて、多次元を3次元以下に射影する。時系列と数量(数もしくは金額)は固定次元だから、あとはどの項目をチョイスするかということでしょう?品目を一つにして、傾向の近い店だけ抜き出すとか、地域別にするとか、ばらつきの大きい品目を探す、ばらつかない品目を探す。等々のアプローチからしてみました?
  • id:a-kuma3
    試しに、列方向 200(店舗をイメージ)、行方向 1000(期間をイメージ)で、乱数を使ったデータを作って、3D棒グラフを Excel 2010 で書いてみた。
    項目数の制限という意味では、受け取ってくれたのですけれど、グラフの描画を指定してから2分ほど経ちますが、いっこうに応答がない(並みレベルのノートPCなんですが) orz

    駄目だな、こりゃあ。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません