幹葉図(幹葉表示)の作成 with Excel
イントロダクション
Step 0シチュエーションの設定
Excelで幹葉図(幹葉表示とも)を作成するための手続きです。幹葉図の特筆すべき利点のひとつとして,やはり,元のデータを維持したまま要約が可能なことを挙げておくべきでしょうか。nの大きさやデータの範囲など条件が整った場合には,使い勝手に関してヒストグラムにまさるツールともなりえます。
Excelでのアプローチのしかたについては,万能的な構成のシートを用意していくといった方法よりも,実用上の見地いうなればヒストグラムと比べたら,そうそう利用機会に恵まれるものでもないことから,都度必要な最低限のデータを導ける簡素なしくみで十分かなと筆者自身は感じます。以下の手続きはそうした点を念頭に置いてのものです。
具体的な流れとしては,どのような形式の幹,および葉が必要かをあらかじめ明確にし,これを導けるよう数式をフィットさせていく必要があります。ここでは,この過程を,たとえばの3つのケース,すなわち
-
正の整数の元データ幹に10の位,葉に1の位をあてて図を作りたい
-
正の実数の元データ幹に10の位以上,葉に1の位と1/10の位をあてて図を作りたい
-
正負の数,および0を含む実数の元データ幹に1の位,葉に1/10の位をあてて図を作りたい
にて差異を設けて記載しています。とはいえ,主要な作業そのものについてすべてが異となるわけではありません。したがって流れの細かな部分についてはCase 1でのみ触れ,Case 2およびCase 3では要点のみに触れるものとします。
正の整数からなるデータの処理の例
Case 1-Step 0
Case 1-Step 1
データを昇順に並べ替えます。
最低・最高は次のようになっています。これをふまえるに,ここでは幹を10の位,葉を1の位とするスタイルが明解なように思われます。このとき,41の幅をいくらかの幹で表現する必要があるので,具体的に幹の幅を10としたとしたらほどほどな頃合いの,5つの幹で間に合いそうです。したがって,このケースでは
- [幹にあてる位]10の位
- [葉にあてる位]1の位
- [幹の数]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 ) ) |
---|
この式の構造をことばで示すなら,下図のようになります。
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
幹葉図の完成です。
正の実数からなるデータの処理の例
Case 2-Step 0
Case 2-Step 1
最低・最高は下図のようになっています。一瞥して筆者の得る印象としては,幹を10の位以上で,また端数を丸めて葉を1の位であらわす選択をもつところのように思います。ただし,ここでは例示のため,あえて1/10位を落とさずに
- [幹にあてる位]10の位以上
- [葉にあてる位]1の位と1/10の位
とする設定を設けたいと思います。
同じように幹の数については,Case 1同様10程度の幅で5つほどの幹をとっていけばよいようには思うところですが,Case 1との設定の重なりを避けるため,ここでは幅を5にして手順を追っていきたいと思います。
ということでこの設定の場合,幹は9つ用意できます。ただし,ここでのケースのように幅を10の整数倍としなかった場合,Case 1のような10の位のみでの表記であれば少し計算が面倒になります。そこで,ここでは幹の表記は最後に手作業で修正するものとして,実際の数値で埋めていくといった方法をとりたいと思います(下図)。
- [幹の数]9
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
この式の構造をことばで示すなら,下図のようになります。
Case 2-Step 3
内容を別途複写し,幹の表示の修正,および書式等の適宜の調整を経て幹葉図の完成です。
正負の数, および0を含む実数からなるデータの処理の例
Case 3-Step 0
Case 3-Step 1
最低・最高は下図のようになっています。ここでは例示のため
- [幹にあてる位]1の位
- [葉にあてる位]1/10の位
とする設定を設けたいと思います。
また幹の幅については0.5とします。なお正負をともに扱うことから,幹「0」については先後に2つを用意して対応します。またCase 2と同じく,幹の表記は最後に手作業で修正するものとして,当該のセルには実際の数値を埋めていきます。
- [幹の数]11(現実的にはいささか過多とは思いますが)
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
この式の構造は,ことばで示すと下図のようになっています。
Case 3-Step 3
内容を別途複写し,幹の表示の修正,および書式等の適宜の調整を経て幹葉図の完成です。
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」の度数分布に関するHow-toです。