Excel：幹葉図を作成する

Microsoft 365で追加された動的配列数式とスピル機能を利用してエクセルで幹葉図を作成してみました。

幹葉図とは

幹葉図は階級を幹、各階級のデータを葉として表現し、ヒストグラムのように階級ごとの度数の分布を視覚的にとらえることができ、それと同時に各階級に属するデータの値を読むことができます。

次の幹葉図は10の位の数字を幹として表現、1の位の数字を葉として表現しています。

幹葉図のイメージ

幹の数字は下へ向かって昇順、葉の数字は右に向かって昇順に並べます。また幹葉図を90度傾けて葉を塗りつぶすとヒストグラムになります。

参考リンク

Excelで幹葉図を作成する

それではExcelで幹葉図を作成していきます。

幹の部分は配列を返す数式の結果をスピル機能でセルに表示させ、葉の部分は結果を空白でつなげた文字列を返す数式を作成してオートフィルで幹の範囲に広げます。

手順の詳細は以下になります。

  1. 幹の部分を作成する数式を作成する
    1. 対象データの最小値の10の位を求める
    2. 対象データの最大値の10の位を求める
    3. 最小値の10の位から最大値の10の位までの連番を生成する
  2. 葉の部分を作成する数式を作成する
    1. 対象データから10の位が幹の値と一致するデータを抽出する
    2. 抽出したデータを1の位に変換し昇順に並べる
    3. 抽出したデータを空白でつなげる
  3. 2.で作成した数式をオートフィルで幹の部分全体の範囲に広げる

幹の部分は以下の数式で求めました。
対象データから10の位の最小値と最大値を求め最小値から最大値までの連番の配列を返す数式です。

=LET(
  最小, INT(MIN(対象データ)/10),
  最大, INT(MAX(対象データ)/10),
  SEQUENCE(最大-最小+1,1,最小)
)

幹葉図を作成する対象データは参考にしたサイト（統計WEB）にある「あるクラスの国語の点数」のデータをA2~A51に入力して範囲に「対象データ」と名前をつけて使用しました。

幹を作成する数式をC3に入力した結果は以下です。

幹を作成

数式から返された配列がC3からC7にスピル機能で出力されています。

幹の部分の結果を求められたので続いて葉の部分の数式を作成します。

葉の部分は以下の数式で求めました。
幹に属するデータを抽出して1の位の数字を昇順に空白区切りで返す数式です。

=LET(
  幹, C3,
  マッチ, FILTER(対象データ, INT(対象データ/10)=幹),
  葉, SORT(MOD(マッチ,10)),
  TEXTJOIN(" ",TRUE,葉)
)

数式をD3に入力してオートフィルでD7まで範囲を広げた結果は以下です。

葉を作成

また葉の部分を値ごとにセルに出力したい場合は数式の結果を空白でつなげた値ではなく配列を返すように変更します。
対象データを縦方向に入力しているので結果を返す前にTRANSPOSE関数で横方向に変換しています。

=LET(
  幹, C3,
  マッチ, FILTER(対象データ, INT(対象データ/10)=幹),
  葉, SORT(MOD(マッチ,10)),
  TRANSPOSE(葉)
)

数式をD3に入力してオートフィルでD7まで範囲を広げた結果は以下です。

葉を作成（配列）

数式から配列が返されるのでスピル機能で値が展開されます。

まとめ

Excelで幹葉図を作成する場合に動的配列数式とスピル機能を利用することで効率よく作成することができます。