BDAstyle

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

幹葉図(幹葉表示)の作成 with Excel

イントロダクション

Step 0シチュエーションの設定

エクセルで幹葉図(幹葉表示とも)を作成するための手続きです。幹葉図の特筆すべき利点のひとつとして,やはり,元のデータを維持したまま要約が可能なことを挙げておくべきでしょうか。nの大きさやデータの範囲など条件が整った場合には,使い勝手に関してヒストグラムにまさるツールともなりえます。

エクセルでのアプローチのしかたについては,万能的な構成のシートを用意していくといった方法よりも,実用上の見地――いうなればヒストグラムと比べたら,そうそう利用機会に恵まれるものでもないことから,都度必要な最低限のデータを導ける簡素なしくみで十分かなと筆者自身は感じます。以下の手続きはそうした点を念頭に置いてのものです。

具体的な流れとしては,どのような形式の幹,および葉が必要かをあらかじめ明確にし,これを導けるよう数式をフィットさせていく必要があります。ここでは,この過程を,たとえばの3つのケース,すなわち

にて差異を設けて記載しています。とはいえ,主要な作業そのものについてすべてが異となるものでもありません。したがって流れの仔細に関してはCase 1でのみ触れ,Case 2およびCase 3では要点のみに触れるものとします。


1.正の整数からなるデータの処理の例

Case 1-Step 0

Case 1では,下のデータをもとに幹葉図を作ります。

初期データ

Case 1-Step 1

データを昇順に並べ替えます。

最低・最高は次のようになっています。これをふまえるに,ここでは幹を10の位,葉を1の位とするスタイルが明解なように思われます。このとき,41の幅をいくらかの幹で表現する必要があるので,具体的に幹の幅を10としたとしたら……ほどほどな頃合いの,5つの幹で間に合いそうです。したがって,このケースでは

とする条件に沿う図の作成を目的とします。

…と,いうことで,早速幹に値を振っていくと下図のようになります。

scrollable

Case 1-Step 2

幹の値に対応する度数を求めます。その準備として,ここでは下図の場所に「0」を振っておきます。なおこの値は後段の計算式でのみ使用するもので,表記の上では特段の意味を持つものではありません。

「0」のとなりに下の式で度数を求め,これを残りの幹の数だけ右方にコピーします。なお下式の強調部分は,幹に指定した値がリクツの上では10の位であることと,実際のデータ的には1桁の値であることの齟齬を埋めるものです(入力された値は2桁でないただしく計算できない)。また幹葉図の特性に照らせばやや的が外れる感はありますが,たとえば 100, 120, 210, 350... といった桁が大きくかつ幅の広いデータを対象とするときなどには,幹の設定を 1, 2, 3... とし,葉を 00, 20, 10... とする1桁以外の葉の設定を用いることもできなくはありません。このとき,ここにいう強調部分やRight関数の引数(式末端の “1”)に調整の必要が生じえます(こうした特異な可能性については以下工程でも同様に抱えますが,キリがないのでここ以降の言及は省きます)。

F1 =COUNTIFS( $A$2:$A$31, ">="&F$3*10, $A$2:$A$31, "<"&F$3*10+$C$8 )

Case 1-Step 3

葉の抽出も計算式でおこないます。その前に,ここでは,その計算式を何番の行までコピーすればよいかの目処を,あらかじめ計算によってつけておきます。具体的には,これは下式によって求められます。

C11 =ROW( INDIRECT( ADDRESS( MAX(F1:J1)+3,1 ) ) )

なおこの例の場合には,「14」が返ります。

ここで実際に葉を抽出していきます。最初の幹の直下に次のような計算式を組み立て,表の右端,および上で求めた行までそれをコピーして,元データから適切な葉を取り出します。

F4 =IF(ROW()-3>F$1, "", RIGHT( SMALL( OFFSET( $A$2,SUM($E$1:E$1),0,F$1 ), ROW()-3 ), 1 ) )

この式の構造をことばで示すと,下図のようになろうかと思います。

当該度数を満たしていなかったら,データのうちの当該区間のセル範囲を探して,そこから小さい順にデータを抜き出す。ただし,右の1文字(1の位)だけを文字列として取り出せればいい。

Case 1-Step 4

これまでに処理した領域(下図・赤い囲みの部分)をコピーし,別の場所……可能であれば他シートにでも貼り付けます。

ただし一般的な幹葉図の外形的な要素を整える意味合いから,これを貼り付ける際,「行列を入れ替え」たほうが better かとは思います(cf. Google画像検索の結果)。もっとも,これによってセルの参照構造も意味をなさなくなるので,同時に「値」の設定を加え計算式を飛ばしてやる必要が生じます。

(貼り付けによってあたらしく作られた)表,左上角の「0」を「度数」ないしは「頻度」などの表現に置き換えます。

Case 1-Step 5

等幅フォントに変更,セル幅を調整するなどし,一般的な幹葉図の外形に近づけます(下段は見やすくするため一時的にセルの枠線を非表示にしたものです)。

いわゆる “方眼紙” にする作業が憚られるなら(もっともこの場合は適当な部類の使い方だとは思いますが),葉をつなげて1つのセルに表示させてやってもいいかもしれません。

たとえば,Excel 2016の新関数,textJoinがこうした場合の適例になるように思われます。

D2 =TEXTJOIN( " ", TRUE, OUTPUT!D2:N2 )

Case 1-Step 6

幹葉図の完成です。

2.正の実数からなるデータの処理の例

Case 2-Step 0

Case 2では,下のデータをもとに幹葉図を作ります(要 昇順での並べ替え)。次Stepに示す条件に準じて,あらかじめ小数第2位で丸めてあります。

初期データ

Case 2-Step 1

最低・最高は下図のようになっています。一瞥して筆者の得る印象としては,幹を10の位以上で,また端数を丸めて葉を1の位であらわす選択をもつところのように思います。ただし,ここでは例示のため,あえて1/10位を落とさずに

…とする設定を設けたいと思います。

同じように幹の数については,Case 1同様10程度の幅で5つほどの幹をとっていけばよいようには思うところですが,Case 1との設定の重なりを避けるため,ここでは幅を5にして手順を追っていきたいと思います。

…ということでこの設定の場合,幹は9つ用意できます。ただし,ここでのケースのように幅を10の整数倍としなかった場合,Case 1のような10の位のみでの表記であれば少し計算が面倒になります。そこで,ここでは幹の表記は最後に手作業で修正するものとして,実際の数値で埋めていくといった方法をとりたいと思います(下図)。

scrollable

Case 2-Step 2

幹に対応する度数を求めます。

F1 =COUNTIFS( $A$2:$A$31, ">="&F$3, $A$2:$A$31, "<"&F$3+$C$8 )

scrollable

先の条件に合致するデータを抽出できるような,任意の式を組み立てます。たとえば

F4 =IF( ROW()-3>F$1, "", TEXT( (SMALL( OFFSET( $A$2,SUM($E$1:E$1),0,F$1 ), ROW()-3 ) ) -( QUOTIENT(F$3,10)*10), "0.0" ) )

とする式などで,抽出が可能です。

scrollable

この式の構造をことばで示すと,下のようになろうかと思います。

当該度数を満たしていなかったら,データのうちの当該区間のセル範囲を探して,そこから小さい順にデータを抜き出す。この値より,幹の値に応じて丸められた値(10の整数倍)を引き,結果を 0.0 形式のテキストにより返させる。

Case 2-Step 3

内容を別途複写し,幹の表示の修正,および書式等の適宜の調整を経て幹葉図の完成です。

3.正負の数, および0を含む実数からなるデータの処理の例

Case 3-Step 0

Case 3では,下のデータをもとに幹葉図を作ります(要 昇順での並べ替え)。次Stepに示す条件に準じて,あらかじめ小数第2位で丸めてあります。

初期データ

Case 3-Step 1

最低・最高は下図のようになっています。ここでは例示のため

…とする設定を設けたいと思います。

また幹の幅については0.5とします。なお正負をともに扱うことから,幹「0」については先後に2つを用意して対応します。またCase 2と同じく,幹の表記は最後に手作業で修正するものとして,当該のセルには実際の数値を埋めていきます。

scrollable

Case 3-Step 2

度数をカウントします。ただしこのケースの場合には,下図の彩色された個所でそれぞれ異なる式を充てる必要が生じます(幹「0」の直前まで|先の幹「0」|後の幹「0」以降 の3つのブロックで分ける)。なお「0」ちょうどについては,より右の幹で処理をおこなうものとします。

F1 =COUNTIFS($A$2:$A$31, "<="&F$3, $A$2:$A$31, ">"&F$3-$C$8)
I1 =COUNTIFS($A$2:$A$31, "<"&I$3, $A$2:$A$31, ">"&I$3-$C$8)
J1 =COUNTIFS($A$2:$A$31, ">="&J$3, $A$2:$A$31, "<"&J$3+$C$8)

scrollable

先の条件に合致するデータを抽出できるような,任意の式を組み立てます。たとえば

F4 =IF( ROW()-3>F$1, "", RIGHT( TEXT( SMALL( OFFSET($A$2,SUM($E$1:E$1),0,F$1),ROW()-3 ), "0.0" ),1 ) )

とする式で抽出が可能です。

scrollable

この式の構造は,ことばで示すと下図のようになっています。

当該度数を満たしていなかったら,データのうちの当該区間のセル範囲を探して,そこから小さい順にデータを抜き出す。このとき,結果は 0.0 形式のテキストにより返させ,右の1文字(1/10の位)だけを手に入れる。

Case 3-Step 3

内容を別途複写し,幹の表示の修正,および書式等の適宜の調整を経て幹葉図の完成です。

その他の参照