BDAstyle

ビジネスデータ分析ツールの作成 with Excel

マリメッコチャート(モザイク図)の作成 with Excel

イントロダクション

Step 0

ここでは「Marimekko Chart」あるいは「Mekko Chart」,もしくは「モザイク図」などと呼ばれるところのビジュアライズを扱います(以下,「マリメッコチャート」を使用します)。

たとえば,ある製品の不満な点について顧客からいくらかのフィードバックを得ることができたとします。このフィードバックでは,コンテクストがおおむね3つに大別でき,これらを寄せた顧客を

  • グループ1
  • グールプ2
  • グループ3

とグルーピングしました。そしていくらかの時間をおいたあと,先の不満に対して当該顧客が実際にとったアクションが,分類

  • A
  • B
  • C

のいずれであったかを追跡調査した結果,次のクロス表を得たとの想定を置きます(単位:人,重複なし)。

DL

以下,この頁では表の各部位を示す手段が必要となるので,便宜的に次の図のような呼称を利用していくものとします。

さて,こうしたデータのビジュアライズを考えるとき,視野の一端を埋めるものとして想起されるツールのひとつに,「100%積み上げグラフ」があるかと思います。

であれば早速エクセルでそれをおこなってみるとして,上で示したデータ範囲のすべてをソースとして差し込むと,同グラフは下の図のように作成されます。これにより,アクションの構成比率が,グループの別に描出されたことがわかります……

……が,どこかしら何というべきか「何かが足らない」感のようなものを覚えないでもありません。というのもこの例にいう「グループ」は,それぞれ層の厚みを異にしています。したがって,上図のようにそれらの情報を切り捨ててカテゴリとして扱ってしまうのは,状況によっては少しもったいない気もします。

ということで,横軸も縦と同様数値(各グループの構成比率)として扱いたく思うのです。この望みにかなうツールが,ここで扱うマリメッコチャートとなります。

とはいえ残念なことに,そのグラフを作成するためのインタフェースを(この記事の公開時点で)エクセルの標準機能のなかに見つけることができません。したがって現状では既存の機能を転用し,あれこれこねくりまわすしか目的のものを手に入れる方法がありません。換言すれば,“力業”なら可能だということです。

力業たることの是非は無視したうえで,具体的にエクセルでこれを実現することを考えたとき,筆者の肌感覚の上では

にて解説される手続きが,もっとも広く活用されているそれのような気もします。

ここで扱うのは上の手続きとは異なりますが,散布図を素地にした解決策を例示していきたいと思います。これにより,下のような表現が可能となります。

またここでの方法においては,シートの役割を入出力で分けていきたいと思います。これにともない,元表のシートには"input"という名前を与えたものとして話を進めます。

表の横セグはグラフの横軸に,縦セグはグラフの縦軸に沿って配置されます。ただし,縦セグの内容はより上方に位置するものから順にグラフに積み上げられていく仕様とします。

―――では,step 1より具体的な手続きです。

工程

比率を求める

Step 1

"ratio"シートを作成し,これに"input"シートと同様の枠組みを作ります。

なおセルA1については必ず空白としておきます。

この表の最下部,左端には「比率」および「0」を足しておきます。前者は横セグごとの構成比を計算する領域,後者は同累積構成比を計算するための領域です。見出しを「0」としたのは中段での処理に関する効率を優先としたゆえです。

最初の見出しの交点のエレメント(セルB2は,すなわち「グループ1」に占める「行動A」の比率を意味します)を求めます。

セルB2に次式を入力し,これを残りのエレメントにコピーします。

B2 =input!B2/SUM(input!B$2:B$4)

横セグの全体に対して占める割合を求めます。

セルB5に次式を入力し,これを残りのセグメントに関してコピーします。

B5 =SUM(input!B$2:B$4)/SUM(input!$B$2:$D$4)

上の比率を積み上げます。

セルB6に次式を入力し,これを残りのセグメントに関してコピーします。

B6 =SUM($B$5:B$5)

"output" の準備

Step 2

"output"シートを作成し,これに次のような見出しを用意します。

見出し部分の何たるかについては随時触れます。

すべてのエレメントを「横セグ―縦セグ」の表記にて書き出します。

これは識別のための符号なので必ずしも厳密な表記である必要はありません(作成者のみが理解できれば事足りるので下のように略号で構いません)。この見出しは横セグの順を優先に作っていきます。

scrollable

上の工程で作成したエレメントが,"ratio"シートのデータ領域にいう何行目,何列目にあたるかを指示します(ただし見出しを除いて考えます)。

「row」が行,「col」が列です。たとえば,セルC5の「G1-B1」は,"input"シートの見出しを含めずに考えると,11列目に位置します。

―――と書くと何だかとても面倒なようにも聞こえますが,下図に明らかなようにrowは縦セグの数だけ1よりのカウントをループ,colは同じ横セグの間は同じ数字を指定するだけといった具合に法則性があることを踏まえて作っていくと,頭を悩ますことがありません。

scrollable

"ratio"シートの「0」の行位置を見出しを含めずに調べます。

C1 =COUNTA(ratio!A:A)

「散布図」でブロックの組み合わせをいかに描くか

Step 3

ところで,エクセルの標準機能の中ではいろいろと使い勝手のよい「散布図」ですが,性格上当然のことながら間隙のないヒストグラムのような“ブロック”を描くことを得意としません。

直線散布図の線幅(pt)をおもいっきり太くすればそれらしく見せることはできますが,ここでの用途に限っては,サイズ変更などを契機として“らしさ”がもろくも崩壊してしまうので,できれば避けたいところです。

結論から言えば,柔軟性を保ったまま“らしさ”を演出する方法として,ここでは「散布図」の上に無数の鋸刃様の直線を連ねていく方法をとっています。微細な間隔の鋸刃であれば,(常識的なグラフサイズのうちは)それはあくまで“ブロック”にしか見えません。

Step 4

さて,下の図にいう「X」はグラフのX座標を作るために利用します。具体的には,0-1000までの連続データを作っていきます。

もっとも,この場合は真の意味での連番ではなく,同じ数字を反復(二度)するそれとなります。その端緒として,最初の2セルには「0」を突っ込んでおきます。

セルA8,A9に下式を入れ,これらを下方にコピーします。―――この例の場合,2007行目までのコピーで,ちょうど1000までの番号を作ることができます。

A8 =A6+1
A9 =A8

X'」は横方向の「100%積み上げグラフ」の目盛りに相当する部分です。

セルB6に下式を入力し,これを表の最下行までコピーします。

この式により,最小単位を0.001とする仮想の目盛りを作っています。すなわち,横セグの比率は1/1000位に丸めて描画することになります。

B6 =A6/1000

横セグの最初の1つについて,数式を完成させる

Step 5

鋸刃を描くために座標を作ります。

C6,D6に次式を入れ,後者のみを同じ横セグの範囲内にコピーします。

C6 =IF(AND($B6>=OFFSET(ratio!$A$1, $C$1, C$3-1), $B6<OFFSET(ratio!$A$1, $C$1, C$3)), 0, NA())
D6 =IF(AND($B6>=OFFSET(ratio!$A$1, $C$1, D$3-1), $B6<OFFSET(ratio!$A$1, $C$1, D$3)), C7, NA())

scrollable

C7に次式を入れ,同じ横セグの範囲内にコピーします。

C7 =IF(AND($B7>=OFFSET(ratio!$A$1, $C$1, C$3-1), $B7<OFFSET(ratio!$A$1, $C$1, C$3)), SUM(OFFSET(ratio!$A$1, 1, C$3, C$2, 1)), NA())

scrollable

このstepで作成したすべての範囲を表の最下行までコピーします。

残りの横セグに数式をコピーする

Step 6

1番目の横セグに関するデータ範囲をすべて選択し,それをクリップボードにコピーします(Ctrl+C)。

残りの横セグに関して,必ず1セグメントずつ個別にペースト(Ctrl+V)していきます(下図にいう彩色したセルに1つずつ)。

scrollable

上の作業を終えた段階で,表は下のような状態となります。

scrollable

用意したデータでグラフを描く

Step 7

"output"シートの「X」を除く表のすべてを選択し……

散布図(直線)を作成します。

これにより,下図のようなグラフが出来上がります。

実際の値に より忠実であるために

Step 8

彩色(もちろん「線」に対して)をおこないます。

特段の理由でもない限り,縦セグごとに同一色で統一したほうが無用な誤解も生まないかとは思います。

つづいて,この時点でのグラフが抱える最も大きな問題に対処します。

上図の……たとえば縦軸の100%ラインが例として明瞭だと思いますが,その近傍を注意して見ると,鋸刃たればこそデフォルトの線の形状による影響でこのラインをごくわずかにはみ出してしまっていることに気づきます。つまり各エレメントがY軸の目盛りに対し,“ふつうの棒グラフ”との比較でいうところの,“多少のズレ”を含んでいる状態にあります。

これを,大勢に影響を与えるレベルの問題でない―――と考えて無視するのもひとつの方策です。

ただ,ここではせっかくなので,この問題を解消するための手続きを加えたいと思います。具体的には,各系列の線の書式を「先端」および「結合点」について,順に「フラット」「面取り」に変更します。……もっとも,系列が多くなるとこの修正はラクではないので,F4キーを上手に使って1つの設定項目ごとに流れ作業で済ませていくのがbetterかと思います。

負方向・キャップなし・パーセンテージ100

目的に照らし,いくらかの書式設定をおこなってこの時点で完成とする選択もあります。―――終了とする場合,凡例は削除して別途自作した方がいいかもしれません。

+α:境界線

Step 9

以下追加的なグラフの整形です。

資料の閲覧者の理解をたすけるいくつかのパーツをグラフに加えるため,"additional"シートを作成したうえで,たとえば下図のようなデータを用意しました。

scrollable

左の小さな表は横セグ間の仕切り線の座標です。

グラフにあたらしい系列を追加して,下図にいう青く囲った領域を系列のX,ピンクで囲ったそれを系列のYとして振ってやれば………

グラフは下図のようになります(線色を白に指定)。どの横セグも似たような内訳を揃えた場合など,一瞥して境界が判断しづらいときには描きこんでおいたほうがbetterなパーツだとは思います。

右の大きな表は,ブロックの中心と横セグの上部に,任意のデータを表示するための座標となります。

こちらもグラフにあたらしい系列を追加して,図にいう青く囲った領域を系列のX,ピンクで囲ったそれを系列のYとして振ってやれば………

グラフは下図のようになります(次のstepへ)。

+α:データラベル

Step 10

下の図は,上の状態のグラフにデータラベルを追加したところです(初期値はyの値です)。

この例では実際の観測値を表示させたいと考えます。

かかるデータは,下表の「caption」列ですでに"input"シートから引いてきてあるので,この領域をラベルの表示内容として指定すれば………

――グラフは下図のようになります(ラベル描画用の線は不可視としておきます)。

+α:凡例

Step 11

ここでの方法により作ったグラフの凡例はいささか冗長で,同時に“伝える”ことにも不向きな状態かと考えます。したがって,これを書き換えたいと思います。

この例において縦セグは3つのみなので,凡例内の要素は3つあれば事足りることがわかります。

したがって,凡例の中の要素をそれぞれ個別に選択削除を繰り返して,最初の横セグ分のみを残すようにしてやります(この処理を終えると下図の状態となります)。

"output"シートの一部を縦セグの名称に書き換えます。……この作業はシートの内容を直接加工することになるので,場合によってはシートごとコピーして,あたらしい別シートの方を加工していった方がよい選択となるかもしれません。

scrollable

ともあれ,直前の処理によってグラフは下のような状態となります。

Step 12

残る任意の書式設定をおこなって,目的のマリメッコチャートの完成です。

その他の参照