手順解説 | Excel(エクセル)でおこなうビジネスデータの分析
スライス&ダイス分析は,データを切り出す(スライス),ないしはサイコロ(ダイス)を転がすようにして視点を変える…といった方法を組み合わせながらすすめるデータ分析の手法です。漠然としたデータのかたまりを分析の対象とするとき,ある程度の多面性・深度を視座に分析に取り組むことができます。
こうした特徴から,スライス&ダイス分析には決まった手続きがあるわけではありません。むしろ,総当たり的な手続きをとることにより “想定しない発見” を探索しうる点にこそ意義があると考えるべきでしょうか。その意味では,トライアル&エラーを前提とした手法と言えるところがあります。
もっとも,(エクセルのピボットテーブルを使用するとき)膨大なデータを対象とした総当たり的な手続きは,常識的にはむずかしいところもあるはずです。したがって,実際にはある程度のアテをつけた利用の仕方が不可欠ではあり,この点,経験に資する部分も少なくはない手法と言っていいのかもしれません。
以下,Excel 2016 のピボットテーブル機能を使ったスライス&ダイス分析の手続きの一例です。とりわけ以下の内容では,具体的な操作手順といったものよりも,「思い付き」や「感覚」で見たいものを手繰っていく空気感のようなものに注視していただければ,と思います。なお,Excel 2010 および Excel 2013 では一部操作内容が異なる個所があります。その場合,おおきな変更点のみ当該箇所に明記していきます。
元のデータです。リサーチサービス社のここ 2 年にわたる主要商品の売上データ(一部)です。取引 1 件につき,1 つのレコードが対応しています。
このデータをスライス&ダイスしながら,データを多面的に見てみること,ないしは,何らの特徴的な事象が見られないか,探ってみることが目的です。
スライス&ダイス分析の出発点はさまざまですが,ここでは設定として… 先ごろ,売上データの業種別構成比を計算していたときに,ちょっと意外な結果が出て気になっていた…というシチュエーションを作っておきたいと思います。
具体的には,「金融」業種のそれが,なんとなくイメージしていたよりもはるかに少ないものでした。
ある販売担当部員について「販促に活路を見いだすための材料がどこかに転がっていないかな」―――私がそんなふうに思っていたとき,先の驚きを受けてスライスダイスに至ったと考えてくださるとうれしいです。
シートの任意のデータ(ここではセルA1)をアクティブにした状態で,リボンの挿入タブテーブルグループにあるピボットテーブルの挿入ボタンをクリックします。
ピボットテーブルの作成ダイアログが開きます。ここでは正しいデータ領域がテーブル/範囲のボックスに指定されているかを確認して,問題がなければ既定の状態のままOKボタンをクリックします。
ピボットテーブルのフィールドリストから月-年をドラッグし,下の行ラベル枠内でドロップします。
2010
以前の表記:行行ラベル
同様にして,商品コードを列ラベルに,金額を値枠内にドラッグ&ドロップします。
2010
以前の表記:列列ラベル
2016
月-年を行へドロップすると,“時刻のグループ化” がおこなわれ年および四半期といった項目も自動的に追加されます(cf. ピボットテーブル レポートでデータをグループ化またはグループ化解除する ―"Office")。
ここでは他バージョンと話の整合性を維持するため,以下の展開においては自動で加えられるグループ化設定を解除して(ピボット表のセルA5を選択→[分析]タブ→「グループ」グループ―[グループ解除]ボタン) Step を進めるものとします。
その状態のまま,リボンの分析タブツールグループにあるピボットグラフボタンをクリックします。
2010
オプションタブツールグループにあるピボットグラフボタン
グラフの挿入ダイアログ・「面」グループの積み上げ面をクリックし,OKボタンをクリックします。
商品種別 1 ~ 8 について,ここ 2 年の売上の推移をグラフにすることができました(商品別売上高|時系列)。
下の 2 つの図の上段がそれです。これは「積み上げ面」…なので,この最上辺がこの商品グループの金額そのものの推移となります。したがってこの商品グループ全体については右下がりの傾向が明らかではあるのですが,じゃあ商品ごとの販売構成比なんかに大きな変化があったのか…と言われればハッキリとしたものが見えてこないように感じます。
ということで,この場合により適切なのかもしれない「100%積み上げ面」に下段で変更してみましたが,こちらでも層のバランスなどで一瞥してわかるような特徴的な事象をうかがい知ることができません。つまり,全体の低下傾向は全商品種による影響下にありそうな気がします。
はたしてこの感覚がどれほど確からしいのか…いっそのこと,2 時点間で単純化して眺めてみようかと思います。具体的には,ファンチャートを呼び出して,最初と最後のタイムシリーズを線分で結んでみます。
…ということで,ピボット表の「合計/金額」をアクティブにした状態から,分析タブアクティブなフィールドグループのフィールドの設定をクリックしします。
2010
オプションタブアクティブなフィールドグループのフィールドの設定ボタン
値フィールドの設定ダイアログが開きます。
計算の種類タブに切り替えたのち…
計算の種類を基準値に対する比率に,基準フィールドを月-年に,基準アイテムをJul-10(つまり当該期間のうちの最も古いもの)に設定します。
すべて設定できたら,OKボタンをクリックします。
ピボットグラフをアクティブにして,デザインタブ種類グループのグラフの変更から,元のグラフを折れ線グラフに変更します。
つづけて,グラフ内のタイムシリーズに関するフィルタ(ここでは「年-月」)から “最も古い期” と “最も新しい期” の 2 点のみが選択された状態に設定します。
以上の操作で,当該期間のうちの最も古い実績を基準としたファンチャートが作成されました。
途中の推移を無視したこのきわめて単純な 2 時点比によれば,商品種別 6 …これはこの商品群に言う基幹種ではあるはずなんですが…は,分析時点で最も大きく落ち込んだ商品となっていることがわかります(およそ 50 ポイント)。
また前時点比でやや増の商品種別 4 に関しては,直近に 6 の不振を埋めるためにとられた間に合わせに近いテコ入れの影響がほぼ全てであって,経過を加味すれば額面通りに受け取ることは難しそうです。
一旦ここで流れを仕切り直したいので,先のデータを比率から観測値に戻し,グラフについてはタイムシリーズに加えたフィルタも解除しますNe。
さて,以上の作業では,「商品」の売上高を時系列で…つまり売上高を「商品」と「時間」の 2 つの軸から眺めたことになります。
ある意味ではエクセルの最大の強みとも言えるピボット機能の 直感的な UI をフル活用して,本来は,ここでいろいろな変数の対を試して俯瞰しておくべきところでしょうか。ですが,今回は十分なデータと時間をもたないので,これをとっかかりとして…
先の 2 軸にあらたに「部門」軸を加えて,3 つの視座から売上を考えてみようかな…なんてふうに考えています。
したがって「部門」に相当する変数を行枠にドラッグ&ドロップして追加していきたいのですが…なんとも思慮が至らずここではそのものズバリの変数を用意していませんでした。
ただ,当社の場合…RS 部に関しては「社員コード」の先頭桁がそのまま所属部門を意味することから,これをグループ化して代用したいと思います(下図上段・中段・下段の順に)。
以上の操作によって,ピボットグラフは下図上段にかたちを変えました。
これで先の 3 つの軸を組み込むことができたのですが,横軸にて明らかなように,現状では 3 つの不連続な群の塊となっています(下図最上段: 1 部の 2 年間の売上~ 2 部の 2 年間の売上~ 3 部の 2 年間の売上)。
とりあえず…この修正は後回しにして…まずは先におこなったようにして,ここでも各部門について最初と最後の 2 時点間で推移を単純化してみたいと考えます。…この結果が,下図 2 段目です。
そしてこの時点でグラフにスライサー※を追加して,不連続な群が一連のデータとして表示される不都合を回避していきたいと思います(下図 3・4 段目)。
「スライサー」はバージョン2010以降で利用できます。
はて,スライサーで各部門を切り替えながら見てみると,次のようなことが見えてきます。
赤線は商品種別 6,黄線は商品種別 4 をあらわすとして…先に見た商品種別 6 の売上高の大幅な減少は,RS 1 部におけるその動向の強い影響のもとにあったことが比較的ハッキリと窺えます。また商品種別 4 については,たとえば RS 3 部ではテコ入れによっても上乗せが難しかったことが推察できます。
今おこなったように,ある軸からひとつの項目要素(ここでは「部門」軸の持つ「RS1部・2部・3部」要素)で切り出して(クローズアップして),残りの軸について観察していくこと …例えるなら下の図のように 1 点でカットしてその断面を覗き見るようなアプローチですが…これはその様を以て スライス分析 と呼ばれます。
ところで,先に「部門」という視点を加えたことによって,私にはその一つ下の “販売担当者”レベルでの動きにもあたらしい興味が沸いてきたりしますです。
「部門」を構成する個々の販売員の別に 2 軸に関する掘り下げをおこなっていくこうした視点の移動は,とりわけ ドリルダウン なんて呼ばれます。視点が階層をまたぐことから,これは同質・異質(大勢と同じか異なるか)…ときにそれが大きな流れを説明したり局所的な問題を提示したりするのですが…を際立たせることに秀でるアプローチだと感じるところです。
具体的な作業の前に…ええと…2 時点間ではこの場合あまりにも貧弱なので,下図「大きな形状」の図のように年単位の集計値を使いたいと思います。ただし,この事例の場合には,それぞれの年に内包される月次の数が異なるので,推移そのものに評価を与えることは難しいとは思います。
そうした条件のもと,スライサーによるドリルダウンを通じて,販売部員別の “折れ線” の形状に関し,同質・異質の評価を目視によって加えてみました。
これによって,あらたにいくつかの調べてみたい点が目につくこととなったのですが…そうした諸点は,私が仕事の合間に進めるとして,ここでは下の図の彩色したグラフ…これは私が動向を気にしている社員コード 1005 の人なのですが,この人のみ商品種別 6 の販売実績について調べてみると,下の 2 番目のグラフのように示されることがわかりました(積み上げ棒)。つまり,主力商品たる位置づけの商品種別 6 が,1005 さんに限ってみれば,いささか販売機会に恵まれないように感じられるところです。
またまた仕切り直しをしたいので,ここでタイムシリーズのフィルタを解除しますNe。
では,今度は…先に「部門」を据えた軸を他の変数「業種」と入れ替えてみようと思います。
このような解析の手続きは,眼下にサイコロを置いたとき必ず視野に入る 3 つの面…つまり 3 つの変数が,そのサイコロを転がすことで他の異なる組み合わせとなる様にたとえて ダイス分析 と呼ばれます。
またそれに加え,今度は追加した「業種」と,従来からの「商品」の 2 軸を同時にスライスできるよう,スライサーを 2 つ差し入れていこうと思います。
…ということで,この場合「ピボットグラフのフィールド」ウインドウにて,
と割り振り,かつグラフを積み上げ面に変更したのが下の 1 段目の図となります。
そして 2 段目の図でスライサーを 2 つ追加し…
とりわけ…最初の設定で掲げた「金融」業種…これは業種コード 3 ですが…におけるいくつかのスライスをテストして,商品コード 4 および 6 の積み上げ面グラフが返した結果を目に留めました(3 段目の図)。
2010
以前の表記:軸軸フィールド
凡例凡例フィールド
期間中のふり幅を加味すれば,強い言葉でこの業種への基幹商品に関する売上が “減った” ことを主張してもよさそうですが,一方でこれまでの推移の追い方を顧みれば,少し恣意的すぎるかなとも思うところです。
そこでこれまでと同じように,最初と最後の 2 時点間を結んで評価したいと思うのですが,このとき,この商品群全体について他業種との比較が必要かもしれないな…と考えています。つまり「商品コード 4, 6」や「業種コード 3」といった括りを取り払って,1 つ上の「商品群全体」「全業種」という括りでデータを観察してみたいのですが,こうした視点の動きを以て,これをとくに ドリルアップ と呼んだりします。
ということで,あらたにこの商品群におけるすべての商品を俎上に乗せる意味合いで,凡例枠から商品コードを外します。ここで軸枠の業種コードを凡例枠に移し,そのうえで「業種コード」に関するフィルタを解除してしまえば,改めてすべての業種を視野に置くことができます。そしてタイムシリーズに 2 時点のフィルタを掛け,折れ線グラフに変更します(下図上段)。
…さて,下段の図によると,「金融」業種の動向は当該区間の開始時点では 3 位の位置にありました。それゆえ「金融」業種はリサーチサービス社にとってある意味影響力の大きな業種と言えましたが,直近に至っては 7 位の位置にあることが気がかりです。これについては,何らの背景があるのかもしれません。
ただ,こうした点についてはどうやら別のデータを引いてくる必要に迫られそうです。…とまれ,ここでは以上をもってスライス&ダイスによる分析の手続きをひとまず完了としたいと思います。
LastUpdate
2018.2.25