エクセルで売上額、テストの点数、作業時間などのデータを確認するとき、平均値だけではデータのばらつきを十分に把握できない場合があります。
そのようなときに役立つ指標が、中央のデータがどの程度広がっているかを示す四分位範囲です。
エクセルではQUARTILE関数やQUARTILE.INC関数を使うことで、第1四分位数と第3四分位数を求め、四分位範囲を計算できます。
四分位範囲は第3四分位数から第1四分位数を引いて求める数値です。
極端に大きい値や小さい値の影響を平均値より受けにくく、データの中心部分の散らばりを確認しやすい特徴があります。
この記事では、1行目に見出しがある表を例にしながら、QUARTILE関数で四分位範囲を求める数式、結果の読み方、データ分析での活用方法を解説します。
エクセルで四分位範囲を求める数式
それではまず、QUARTILE関数を使って四分位範囲を計算する基本操作について解説していきます。
| A | B |
|---|---|
| 1 | 売上額 |
| 2 | 120 |
| 3 | 135 |
| 4 | 150 |
| 5 | 165 |
| 6 | 180 |
第1四分位数と第3四分位数の計算
四分位範囲を求めるには、最初に第1四分位数と第3四分位数を計算します。
第1四分位数は、数値を小さい順に並べたときの下位25パーセント付近の値です。
第3四分位数は、同じデータの上位25パーセントが始まる境目にある値を意味します。
B列のB2からB11に売上額が入力されている場合、第1四分位数は次の数式で求められます。
=QUARTILE.INC(B2:B11,1)
第3四分位数を求める場合は、関数の2つ目の引数を3に変更します。
=QUARTILE.INC(B2:B11,3)
第2引数の1は第1四分位数、3は第3四分位数を指定する番号です。
なお、旧バージョンとの互換性を重視する場合はQUARTILE関数も使えますが、新しいファイルではQUARTILE.INC関数を使うと分かりやすいでしょう。
第3四分位数から第1四分位数を引く手順
続いては、2つの四分位数を使って四分位範囲を求める手順を確認していきます。
たとえばD2セルに第1四分位数、D3セルに第3四分位数を表示した場合、D4セルには次の数式を入力します。
=D3-D2
1つのセルだけで計算を完了させたい場合は、以下のように第3四分位数の式から第1四分位数の式を引きます。
=QUARTILE.INC(B2:B11,3)-QUARTILE.INC(B2:B11,1)
四分位範囲は第3四分位数－第1四分位数という順番で計算します。
引く順番を逆にすると負の値になるため、数式を入力するときは第3四分位数を先に置くことが大切です。
【操作のポイント】見出し行であるB1は数値データではないため、範囲指定には含めずB2から指定します。
直接入力で使える四分位範囲の数式
続いては、集計用セルを作らずに四分位範囲を表示する方法を確認していきます。
データ範囲がB2:B101であれば、結果を表示したいセルに次の式を入力するだけです。
=QUARTILE.INC(B2:B101,3)-QUARTILE.INC(B2:B101,1)
数式の中で同じ範囲を2回指定していますが、これは第1四分位数と第3四分位数をそれぞれ計算するためです。
集計表を簡潔にしたいときは1セルの数式、途中の値も確認したいときは別セルに分ける方法が向いています。
複数の月や部門のデータを比較する場合は、数式を横方向または下方向へコピーして活用しましょう。
【操作のポイント】数式をコピーする前に、対象範囲が相対参照のままでよいか、$記号で固定すべきかを確認します。
四分位範囲とQUARTILE関数の仕組み
続いては、四分位数の意味とQUARTILE関数の引数を確認していきます。
| 区分 | 意味 | quartの値 |
|---|---|---|
| 第1四分位数 | 下位25パーセントの境目 | 1 |
| 第2四分位数 | 中央値 | 2 |
| 第3四分位数 | 上位25パーセントの境目 | 3 |
データを四つに分ける考え方
続いては、四分位数がどのようにデータを分けるのかを確認していきます。
四分位数では、数値データを小さい順に並べ、全体をおおむね4つの区間に分けて位置を考えます。
最小値から第1四分位数までが下位の区間、第1四分位数から中央値までが次の区間です。
中央値から第3四分位数まで、そして第3四分位数から最大値までを加えることで、データ全体の分布を確認できます。
四分位範囲が示すのは、中央50パーセントのデータが広がる幅です。
一部だけ極端な値があっても中央付近の傾向を見失いにくいため、実務データのばらつきを捉える指標として便利です。
QUARTILE関数の引数
続いては、QUARTILE.INC関数の構文を確認していきます。
=QUARTILE.INC(配列,quart)
配列には、四分位数を求めたい数値が入力されたセル範囲を指定します。
quartには0から4までの整数を指定でき、0は最小値、1は第1四分位数、2は中央値、3は第3四分位数、4は最大値です。
第2引数には小数や文字列ではなく、四分位数を表す整数を入力します。
空白セルや文字列は通常の数値計算の対象になりませんが、エラー値が混じると関数全体がエラーになることがあります。
【操作のポイント】売上額や点数など、同じ単位で比較できる数値だけを1つの範囲にまとめます。
QUARTILEとQUARTILE.INCの違い
続いては、QUARTILE関数とQUARTILE.INC関数の使い分けを確認していきます。
QUARTILE関数は以前から使われている関数で、現在のエクセルでも互換性維持のため使用できます。
QUARTILE.INC関数は、端点を含める方式で四分位数を求める新しい関数です。
通常の業務で四分位範囲を求めるなら、既存ファイルとの互換性が必要な場合はQUARTILE、新規作成ではQUARTILE.INCという考え方で問題ありません。
QUARTILE.EXCという関数もありますが、計算方式が異なるため、他の集計結果と比較するときは同じ関数を統一して使う必要があります。
【操作のポイント】同じ報告書や同じデータセットでは、INC方式とEXC方式を混在させないことが重要です。
四分位範囲の結果の読み方
続いては、計算した四分位範囲をデータ分析に活かす読み方を確認していきます。
| 部門 | 第1四分位数 | 第3四分位数 | 四分位範囲 |
|---|---|---|---|
| 営業A | 120 | 180 | 60 |
| 営業B | 130 | 155 | 25 |
数値が小さい場合の見方
続いては、四分位範囲が小さい場合の意味を確認していきます。
四分位範囲が小さいときは、中央50パーセントのデータが比較的狭い範囲に集まっています。
営業Bの四分位範囲が25であれば、多くの担当者の売上額は一定の範囲に収まっていると考えられます。
四分位範囲が小さいことは、中心部分のばらつきが小さいことを示します。
ただし、数値がそろっていることが常に良いとは限りません。
たとえば評価点が低い位置でそろっている場合は、安定していても改善が必要な状態かもしれません。
数値が大きい場合の見方
続いては、四分位範囲が大きい場合の意味を確認していきます。
四分位範囲が大きい場合、中央付近のデータにも幅があります。
営業Aのように四分位範囲が60であれば、担当者ごとの売上に差が出ている可能性があります。
平均との差だけでなく四分位範囲も見ることで、典型的なばらつきを判断しやすくなります。
差が生じる理由は、担当エリア、顧客数、経験年数、季節要因などさまざまです。
数値だけで結論を急がず、背景となる条件を照らし合わせて分析しましょう。
外れ値との関係
続いては、四分位範囲と外れ値の関係を確認していきます。
四分位範囲は、外れ値を判断する目安としても使われます。
一般的には、第1四分位数から四分位範囲の1.5倍を引いた値より小さい値、または第3四分位数に四分位範囲の1.5倍を足した値より大きい値を外れ値候補として確認します。
下限値 = 第1四分位数 – 四分位範囲 × 1.5
上限値 = 第3四分位数 + 四分位範囲 × 1.5
外れ値候補は入力ミスとは限らず、重要な例外や新しい傾向を示す場合もあります。
削除や修正を行う前に、元データや業務上の事情を確認する姿勢が欠かせません。
【操作のポイント】外れ値を見つけたときは、元データの桁数、単位、入力日を確認して原因を切り分けます。
QUARTILE関数の入力とオートフィル操作
続いては、複数の項目に対して四分位範囲を計算する操作を確認していきます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 月 | 第1四分位数 | 第3四分位数 | 四分位範囲 |
| 2 | 4月 | 120 | 180 | 60 |
数式バーへの数式入力
続いては、先頭セルに数式を入力する手順を確認していきます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 売上額 | 四分位範囲 | ||
| 2 | 4月 | 120 | 60 |
結果を表示したい先頭セルを選択し、数式バーまたはセルにQUARTILE.INCの式を入力します。
Enterキーを押すと、指定した範囲に基づく四分位範囲が表示されます。
数式の先頭には必ず半角の=を入力します。
全角の記号や日本語入力のまま数式を打ち込むと、文字列として扱われることがあるため注意しましょう。
フィルハンドルによる数式コピー
続いては、先頭セルの数式をオートフィルでコピーする操作を確認していきます。
数式が入ったセルを選択すると、セル右下に小さな四角形のフィルハンドルが表示されます。
このフィルハンドルを下方向へドラッグすると、数式を後続の行へコピーできます。
相対参照のセル範囲はコピー先に応じて自動でずれるため、月別や部門別の集計に便利です。
ただし、比較対象となる元データの範囲を常に同じにしたい場合は、B2:B11を$B$2:$B$11のように絶対参照へ変更します。
【操作のポイント】オートフィル後は、各行の数式バーを確認し、参照範囲が意図どおりに移動しているか確認します。
エラー表示が出た場合の確認
続いては、QUARTILE関数でエラーが出る場合の確認ポイントを解説していきます。
引数quartに0から4以外の数値を指定すると、エラーが表示されます。
また、範囲内にエラー値が入っていると、QUARTILE.INC関数もその影響を受けます。
数値の入力範囲、見出し行の除外、quartの値の3点を最初に確認すると原因を見つけやすくなります。
データ件数が少ない場合や、すべての値が同じ場合でも計算自体はできますが、分析結果の解釈は慎重に行う必要があります。
【操作のポイント】#NUM!や#VALUE!が表示された場合は、数式を修正する前に対象範囲のセル内容を確認します。
四分位範囲を活用したデータ分析
続いては、四分位範囲を日常の集計やレポートで活用する方法を確認していきます。
| A | B | C | |
|---|---|---|---|
| 1 | 商品 | 平均販売数 | 四分位範囲 |
| 2 | 商品A | 86 | 12 |
| 3 | 商品B | 88 | 35 |
平均値と組み合わせる分析
続いては、平均値と四分位範囲を組み合わせる考え方を確認していきます。
平均販売数が近い商品でも、四分位範囲が異なることがあります。
商品Aは平均値が86で四分位範囲が12、商品Bは平均値が88で四分位範囲が35なら、商品Bのほうが通常時の販売数にばらつきがあると読み取れます。
平均値は水準、四分位範囲は中心部分の安定性を見るための指標です。
二つの数値を並べることで、単純な順位付けでは見えない特徴を発見できるでしょう。
箱ひげ図への利用
続いては、四分位範囲を箱ひげ図に活かす方法を確認していきます。
箱ひげ図は、最小値、第1四分位数、中央値、第3四分位数、最大値を視覚的に表すグラフです。
グラフの箱にあたる部分は第1四分位数から第3四分位数までであり、箱の長さが四分位範囲を表します。
複数グループのばらつきを比較したい場合、箱ひげ図は数表だけより直感的です。
エクセルの挿入タブから統計グラフを選ぶと、箱ひげ図を作成できる環境があります。
期間別データの比較
続いては、月別や四半期別のデータを比較する方法を確認していきます。
月ごとの作業時間や問い合わせ件数に対して四分位範囲を求めると、繁忙期だけでなく通常時の変動も確認できます。
ある月だけ四分位範囲が大きくなった場合は、担当者の入れ替わり、キャンペーン、システム障害などの要因を調べるきっかけになります。
同じ条件で集計した四分位範囲を時系列で比べることが、変化の発見につながります。
【操作のポイント】期間比較では、各期間で同じ件数や同じ集計基準になっているかを確認してから判断します。
まとめ エクセルで四分位範囲を求める方法
エクセルで四分位範囲を求めるには、第3四分位数から第1四分位数を引きます。
実務では、=QUARTILE.INC(B2:B11,3)-QUARTILE.INC(B2:B11,1)のような数式を使うと、1つのセルで計算できます。
四分位範囲は、データ中央50パーセントのばらつきを確認するための指標です。
平均値だけでは見えにくい安定性や変動を把握でき、売上、点数、作業時間、顧客対応件数など幅広いデータ分析に役立ちます。
見出し行を計算範囲から外し、QUARTILE.INCの第2引数に1と3を指定することを意識しましょう。
四分位範囲の大小を背景情報とあわせて確認し、エクセルでのデータ分析に活用してみてください。
