【Excel】エクセルで中央値を出す方法（MEDIAN関数・第一四分位数・四分位範囲）
Excelで複数の数値を集計するとき、平均値だけではデータの実態をつかみにくいことがあります。
特に売上、作業時間、テスト結果、アンケート点数などに極端に大きい値や小さい値が混ざる場合は、中央値、第一四分位数、四分位範囲を確認することが大切です。
中央値はデータを小さい順に並べたときの中央の値であり、外れ値の影響を平均値より受けにくい統計量です。
中央値はMEDIAN関数で求めます。
第一四分位数はQUARTILE.INC関数またはPERCENTILE.INC関数で求めます。
四分位範囲は第三四分位数から第一四分位数を引いて求めます。
この記事では、見出し行が1行目にあるサンプルデータを使い、Excelで中央値と四分位数を扱う方法を順番に解説します。
MEDIAN関数による中央値の計算
それではまず、Excelで中央値を出す基本操作について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 1 | 担当者 | 処理時間 分 |
| 2 | 田中 | 18 |
| 3 | 佐藤 | 22 |
| 4 | 鈴木 | 25 |
| 5 | 高橋 | 31 |
| 6 | 伊藤 | 90 |
この例では、B列に担当者名、C列に処理時間が入力されています。
1行目はヘッダーなので、計算対象はC2からC6です。
MEDIAN関数の入力手順
中央値を表示したいセルを選び、数式バーまたはセルにMEDIAN関数を入力します。
=MEDIAN(C2:C6)
Enterキーを押すと、C2からC6の数値を並べ替えたときに中央となる値が返されます。
今回の18、22、25、31、90では、中央に位置する25が中央値です。
セル範囲はマウスでドラッグして指定してもよく、数式を入力してからC2からC6を選択しても問題ありません。
【操作のポイント】
MEDIAN関数では、見出しのC1を含めず、数値が始まるC2から範囲指定します。
偶数個の数値における中央値
続いては、データ数が偶数のときのMEDIAN関数の動きを確認していきます。
たとえばC2からC7に18、22、25、31、45、90の6件がある場合、中央付近の値は25と31です。
Excelは2つの中央値を平均するため、結果は28になります。
=MEDIAN(C2:C7)
計算結果 28
このように、データ数が奇数なら中央の1件、偶数なら中央にある2件の平均という規則です。
元の表が入力順のままでも、関数の内部で数値を比較するため、手作業で並べ替える必要はありません。
ただし、文字列として保存されている数字やエラー値があると、意図した集計にならない可能性があります。
【操作のポイント】
偶数件の中央値は、中央の2件の平均値になるため、実データに存在しない数値が返る場合があります。
空白セルと文字列を含む範囲
続いては、空白セルや文字が混在した範囲をMEDIAN関数で集計するときの注意点を確認していきます。
セル範囲内の空白セルや文字列は、通常は数値として計算されません。
そのため、処理時間の未入力セルがある表でも、入力済みの数値だけを対象に中央値を求められます。
一方で、セル内にエラー値がある場合は、MEDIAN関数の結果もエラーになることがあります。
数値として見えても文字列扱いになっているデータは、左寄せ表示、警告マーク、VALUE関数などで確認すると安心です。
【操作のポイント】
空白をゼロとして扱いたい集計では、MEDIAN関数だけでなく、元データの入力ルールも確認しましょう。
中央値と平均値の使い分け
続いては、中央値と平均値をどのように使い分けるかを確認していきます。
| 項目 | 数式 | 結果 |
|---|---|---|
| 平均値 | =AVERAGE(C2:C6) | 37.2 |
| 中央値 | =MEDIAN(C2:C6) | 25 |
90分という大きな値が含まれるため、平均値は37.2分まで上がります。
一方、中央値は25分であり、多くの担当者の処理時間に近い水準を示します。
外れ値の影響を受けにくい中央値
続いては、中央値が外れ値に強い理由について解説していきます。
平均値はすべての値を合計して件数で割るため、極端に大きい値や小さい値が結果を動かします。
対して中央値は並び順の中央だけを見るため、最小値や最大値がさらに大きくなっても、中央の順位が変わらなければ結果は変わりません。
18、22、25、31、90の平均値は37.2です。
90を180に変更しても中央値は25のままです。
残業時間、配送日数、住宅価格、顧客単価のように外れ値が発生しやすいデータでは、中央値が実態把握に役立ちます。
【操作のポイント】
平均値と中央値を並べて表示し、差が大きい場合は外れ値や分布の偏りを確認します。
平均値が適する集計場面
続いては、平均値を優先したい場面を確認していきます。
月間の総売上を顧客数で割る、一人当たりの利用量を把握する、全体コストを人数で配分するといった場面では平均値が有効です。
平均値は総量との関係が明確であり、予算管理や原価計算では欠かせない指標です。
ただし、少数の異常値が判断をゆがめる可能性があるときは、中央値も併記すると読み手に伝わりやすくなります。
平均値は全体量、中央値は典型的な水準を把握するための数値として考えると使い分けやすいでしょう。
【操作のポイント】
報告書では平均値だけを示さず、中央値や最大値、最小値も添えると判断材料が増えます。
中央値を求める範囲の固定
続いては、数式をコピーするときのセル参照について解説していきます。
複数列の中央値を横方向に計算する場合、対象範囲の列や行がずれないように参照を確認します。
たとえば月別データがC列からN列にあり、各列の中央値を計算するなら、C2からC31のように各月の範囲を指定します。
別シートの固定範囲を参照する場合は、$記号を使った絶対参照も便利です。
=MEDIAN($C$2:$C$31)
数式をコピーする前に、相対参照で動かしたい部分と固定したい部分を分けて考えましょう。
【操作のポイント】
数式をコピーした後は、先頭セルと末尾セルをクリックし、数式バーで参照範囲を必ず確認します。
第一四分位数と第三四分位数の算出
続いては、データの下側と上側の位置を表す四分位数について解説していきます。
| 集計項目 | 数式例 | 意味 |
|---|---|---|
| 第一四分位数 | =QUARTILE.INC(C2:C21,1) | 下位25パーセントの境目 |
| 中央値 | =QUARTILE.INC(C2:C21,2) | 下位50パーセントの境目 |
| 第三四分位数 | =QUARTILE.INC(C2:C21,3) | 下位75パーセントの境目 |
四分位数はデータを4つの区間に分けたときの境目の値です。
第一四分位数はQ1、第三四分位数はQ3と表記されることもあります。
QUARTILE.INC関数の基本式
続いては、QUARTILE.INC関数で第一四分位数を計算する手順を確認していきます。
=QUARTILE.INC(C2:C21,1)
第2引数の1は第一四分位数、2は中央値、3は第三四分位数を指定する値です。
INCは両端の値を含める方式であり、一般的なExcelの四分位数計算でよく使われます。
数式を入力するセルはデータ列とは別の場所に置くと、元データを保持したまま分析できます。
【操作のポイント】
QUARTILE.INCの第2引数は1から3を使い、0や4は最小値と最大値を返す指定です。
第一四分位数が示すデータの位置
続いては、第一四分位数の読み方を確認していきます。
第一四分位数は、データを昇順に並べたとき、下位およそ25パーセントの地点にある数値です。
処理時間のQ1が22分なら、全体の約4分の1は22分以下で完了していると読み取れます。
これは最も速い人の記録だけを見るよりも、下位側の分布を穏やかに把握できる指標です。
Q1は低い側の基準値、Q3は高い側の基準値として使うと理解しやすくなります。
【操作のポイント】
データ件数が少ない場合は、四分位数の計算結果が補間値になることもあるため、個々の値も併せて確認します。
QUARTILE.EXC関数との違い
続いては、QUARTILE.INCとQUARTILE.EXCの違いについて解説していきます。
QUARTILE.EXCは、両端を除外する考え方で四分位数を計算する関数です。
データ数や位置によっては、INCとEXCで異なる結果が返されます。
=QUARTILE.INC(C2:C21,3)
=QUARTILE.EXC(C2:C21,3)
社内規程、統計ソフト、取引先から指定された計算方法がある場合は、その定義に合わせる必要があります。
特に指定がなければ、Excelで広く使われるQUARTILE.INCを基準にそろえると、ファイル間で比較しやすくなります。
【操作のポイント】
同じ報告書の中でINC方式とEXC方式を混在させず、使った関数名を注記しておくと誤解を防げます。
四分位範囲の求め方
続いては、データのばらつきを確認できる四分位範囲の計算方法を確認していきます。
| セル | 内容 | 数式 |
|---|---|---|
| F2 | 第一四分位数 | =QUARTILE.INC(C2:C21,1) |
| F3 | 第三四分位数 | =QUARTILE.INC(C2:C21,3) |
| F4 | 四分位範囲 | =F3-F2 |
四分位範囲は、第一四分位数から第三四分位数までの幅です。
IQRとも呼ばれる四分位範囲は、中央に集まる50パーセントのデータの広がりを示します。
第三四分位数から第一四分位数を引く式
続いては、四分位範囲を計算する数式について解説していきます。
=QUARTILE.INC(C2:C21,3)-QUARTILE.INC(C2:C21,1)
この式では、Q3からQ1を引くことで四分位範囲を一つのセルに表示できます。
あらかじめ別セルにQ1とQ3を計算している場合は、セル参照で引き算をするほうが数式を読みやすく管理できます。
四分位範囲が小さいほど、中央付近のデータは比較的まとまっています。
反対に数値が大きい場合は、一般的な範囲でも値の差が大きい状態かもしれません。
【操作のポイント】
四分位範囲は必ずQ3からQ1を引き、負の値にならないように計算します。
外れ値判定の基準値
続いては、四分位範囲を外れ値の確認に利用する方法を確認していきます。
代表的な考え方では、Q1から四分位範囲の1.5倍を引いた値より小さいデータ、またはQ3に四分位範囲の1.5倍を足した値より大きいデータを外れ値候補とします。
下限値 = Q1 – 1.5 × 四分位範囲
上限値 = Q3 + 1.5 × 四分位範囲
Excelでは、Q1がF2、Q3がF3、四分位範囲がF4なら、下限値は=F2-F4*1.5、上限値は=F3+F4*1.5で求められます。
外れ値候補は必ずしも入力ミスではありません。
繁忙期、特殊案件、障害対応など、業務上の理由がある場合もあるため、元データの背景を確認しましょう。
【操作のポイント】
外れ値判定は削除の指示ではなく、確認すべきデータを見つけるための基準です。
条件付き書式による値の確認
続いては、外れ値候補を表で見つけやすくする方法を解説していきます。
対象範囲を選択し、ホームタブの条件付き書式から新しいルールを選び、数式を使用して書式設定するセルを決定します。
たとえばC2を先頭セルとする範囲なら、上限値がF6、下限値がF5にある場合、=OR(C2<$F$5,C2>$F$6)という数式で候補を強調できます。
塗りつぶし色を薄い赤に設定すれば、一覧の中から大きく外れた値を見つけやすくなります。
【操作のポイント】
条件付き書式では、先頭セルの参照は相対参照、基準値のセルは絶対参照にすることが重要です。
中央値と四分位数を活用する集計表
続いては、中央値、第一四分位数、第三四分位数、四分位範囲を一つの集計表にまとめる方法を確認していきます。
| 項目 | 結果例 | 確認できる内容 |
|---|---|---|
| 中央値 | 25 | 典型的な処理時間 |
| 第一四分位数 | 22 | 速い側の境目 |
| 第三四分位数 | 31 | 遅い側の境目 |
| 四分位範囲 | 9 | 中央50パーセントの幅 |
集計用セルを別エリアに配置する方法
続いては、元データと集計結果を見やすく分ける配置について解説していきます。
元データがA列からC列にあるなら、E列からG列を統計量の集計エリアにすると、スクロールせずに比較できます。
項目名をE2からE5に入力し、F列に各関数を入力すると、再計算後の値もすぐ確認できます。
見出しには中央値、Q1、Q3、四分位範囲のように、意味が分かる名称を入れましょう。
数式だけでなく集計項目名を残すことが、後からファイルを見直すときの助けになります。
【操作のポイント】
元データのすぐ横に集計欄を置く場合は、データ追加用の列を確保しておくと表が崩れにくくなります。
テーブル機能と自動拡張
続いては、データが増える表で集計範囲を管理する方法を確認していきます。
データ範囲を選択してCtrlキーとTキーを押し、テーブルとして設定すると、新しい行を追加したときに範囲を拡張しやすくなります。
テーブル名がTable1で、処理時間の列名が処理時間なら、=MEDIAN(Table1[処理時間])のような構造化参照も利用できます。
追加データを含めて中央値を更新したい定期集計では便利な方法です。
【操作のポイント】
構造化参照では列名の変更が数式に影響するため、見出し名はむやみに変更しないようにします。
グラフと箱ひげ図による分布確認
続いては、数値の分布を視覚化する方法を確認していきます。
Excelの挿入タブには、統計グラフとして箱ひげ図を作成する機能があります。
箱ひげ図では、箱の下端と上端が第一四分位数と第三四分位数を表し、箱の内部に中央値が示されます。
ひげの外側に点が表示される場合は、外れ値候補として扱われることがあります。
中央値と四分位範囲を表とグラフで確認すると、部署別、月別、商品別のばらつきを比較しやすくなります。
【操作のポイント】
グラフを作る前に、空白行や文字列、異なる単位のデータが範囲に混ざっていないか確認します。
まとめ エクセルで第一四分位数・四分位範囲と中央値を出す方法
Excelで中央値を求める基本の式は、=MEDIAN(C2:C6)です。
中央値はデータを並べた中央の値であり、平均値よりも外れ値の影響を受けにくい特徴があります。
第一四分位数は=QUARTILE.INC(範囲,1)、第三四分位数は=QUARTILE.INC(範囲,3)で求められます。
四分位範囲は第三四分位数から第一四分位数を引いて計算します。
中央値、Q1、Q3、四分位範囲をセットで確認することで、典型的な値とデータの広がりをより正確に把握できます。
売上、作業時間、点数、単価などの集計では、平均値だけに頼らず、これらの関数を使ってデータの特徴を確認していきましょう。
