☰

Excel でグループ化されたデータから四分位数を推定するにはどうすればよいでしょうか?

tag-icon • tag-icon microsoft-excel
Excel でグループ化されたデータから四分位数を推定するにはどうすればよいでしょうか?

犬の質量に関する次のようなグループ化されたデータがあるとします。

Mass         Frequency

0 to 5          13

6 to 10         28

11 to 15        47

16 to 20        21

21 to 25        11

25 to 30         6

Excel を使用して第 1 四分位数を推定するにはどうすればよいですか?

オイゲイブ (滑らかな曲線で結ばれた点を持つ散布図を使用) を作成する方法は知っています。また、オイゲイブと目 (および画面上に置かれた定規) を使用して、大まかに第 1 四分位数を見つけることもできます。しかし、Excel ではより厳密な答えが得られるのでしょうか?

トレンドラインは実際にはオイゲイブではないため (トレンドラインはすべてのポイントを通過しません)、「トレンドラインの追加」機能は使用したくありません。

答え1

これはグループ化されたデータなので、生のデータがない限り、データの変動を再現するために何らかの操作を行う必要があります。簡単にするために、平坦な分布を想定できます。つまり、犬が 0 になる可能性と 5 になる可能性は同じです。

このREPT()関数を使用して、各グループを表す数値のコンマ区切りリストを出力します。

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

REPTを使用する

下部で、各文字列を 1 つの大きな文字列に連結します。

長い文字列

この文字列をコピーして貼り付け形式として価値観新しいワークシートにテキストを列に分割ツールのデータリボンタブでデータを値ごとに1列に分割します。この範囲全体をコピーし、貼り付け形式そして転置これを値の垂直リストに変換します。データは次のようになります。

転置されたデータ

水平行は不要なので、削除してもかまいません。次に、次のような数式を使用して、列 B のいくつかの値を補間します。

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

これを詳しく見てみると、次のようになります。

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

これで、QUARTILE()この推定値のリストの関数を使用して、四分位数を概算できます。

=QUARTILE($B$5:$B$130,1)

ここに画像の説明を入力してください

答え2

グラフィック的には、ヒストグラム「ビン」を四分位数に設定します。その方法については、こちらを参照してください。

あるいは、数式を使うこともできます。言葉で説明するので、計算は皆さんにお任せします。

犬は 126 匹います。その 25% は 31.5 です。最も軽い 31.5 の犬の体重がそれより軽くなる体重を見つけたいのです。

一番軽い箱には 13 匹の犬が入っているので、数えます。残りは 18.5 匹ですが、次の箱には 28 匹の犬が入っています。そこで質問です。2 番目の箱に入っている 18.5 番目に軽い犬の体重はいくらでしょうか?

線形補間と呼ばれるものを使用すると、未知の重量が 6 ポンドと 10 ポンドの間の 18.5/28 であると推定できます。つまり、6+4*(18.5/28) です。

必要に応じて、同じアプローチを使用して他の四分位数を見つけることができます。

言葉遣いが少しぎこちないですが、要点は伝わると思います。お役に立てれば幸いです。

編集: 個々の犬の体重がすべてわかっている場合は、順番にランク付けして、31 番目と 32 番目の犬の平均体重を計算します。

関連情報