Excel - 統計関数

概要

Excelの統計関数は、データの分析や傾向の把握において不可欠な機能である。
基本統計量の算出、分散や標準偏差の計算、データの順位付け、相関分析等、幅広い統計処理機能を提供している。

主な機能として、基本統計量 (平均、中央値、最頻値)、散らばりの指標 (分散、標準偏差)、順位・パーセンタイル、頻度分布、相関・回帰分析等がある。

これらの関数は、データ分析、品質管理、市場調査、学術研究、ビジネスインテリジェンス等、様々な分野で活用される。

基本統計量

AVERAGE関数

数値の算術平均を計算する基本的な関数である。

構文:

=AVERAGE(数値1, [数値2], ...)

使用例:

• =AVERAGE(A1:A10)

  A1からA10までの平均

• =AVERAGE(A1:A10, C1:C10)

  複数範囲の平均

※注意

• 空白セルや文字列は無視される。
• 0は計算に含まれる。
• 論理値と文字列表現の数値は無視される。

MEDIAN関数

データの中央値を返す関数である。

構文:

=MEDIAN(数値1, [数値2], ...)

使用例:

• =MEDIAN(A1:A10)

  A1からA10までの中央値

• =MEDIAN(1, 2, 3, 4, 5)

  3 (奇数個のデータ)

• =MEDIAN(1, 2, 3, 4)

  2.5 (偶数個の場合は中央2つの平均)

※中央値とは
データを大きさ順に並べたときの中央の値である。
外れ値の影響を受けにくい統計量として利用される。

MODE / MODE.SNGL / MODE.MULT関数

データの最頻値 (モード) を返す関数である。

構文:

=MODE.SNGL(数値1, [数値2], ...)
=MODE.MULT(数値1, [数値2], ...)
=MODE(数値1, [数値2], ...)    ※互換性のため残存

MODE.SNGL関数 / MODE.MULT関数の違い:

• MODE.SNGL

  最頻値を1つ返す

• MODE.MULT

  最頻値が複数ある場合、全て返す。(配列数式)

使用例:

• =MODE.SNGL(A1:A10)

  最も頻繁に出現する値

• =MODE.SNGL(1, 2, 2, 3, 3, 3, 4)

  3

※注意
最頻値が存在しない場合は #N/A エラーを返す。

分散・標準偏差

VAR.S / VAR.P関数

データの分散を計算する関数である。

構文:

標本分散
=VAR.S(数値1, [数値2], ...)

母集団の分散
=VAR.P(数値1, [数値2], ...)

使用例:

• =VAR.S(A1:A10)

  標本分散 ($n-1$ で除算)

• =VAR.P(A1:A10)

  母分散 ($n$ で除算)

※標本分散と母分散の違い

• 標本分散 (VAR.S)

  データが母集団の一部である場合に使用する。

  不偏分散。

• 母分散 (VAR.P)

  データが母集団全体である場合に使用する。

STDEV.S / STDEV.P関数

データの標準偏差を計算する関数である。

構文:

標本標準偏差
=STDEV.S(数値1, [数値2], ...)

母標準偏差
=STDEV.P(数値1, [数値2], ...)

使用例:

• =STDEV.S(A1:A10)

  標本標準偏差

• =STDEV.P(A1:A10)

  母標準偏差

※標準偏差とは
データのばらつきを示す指標で、分散の平方根である。
単位が元のデータと同じなので解釈しやすい。

VARA / VARPA関数

論理値と文字列も含めて分散を計算する関数である。

構文:

=VARA(値1, [値2], ...)
=VARPA(値1, [値2], ...)

VAR.S / VAR.Pとの違い:

• VARA / VARPA

  論理値TRUE=1、FALSE=0、文字列=0として計算に含む

• VAR.S / VAR.P

  論理値と文字列は無視する。

使用例:

• =VARA(A1:A10)

  論理値・文字列を含む標本分散

STDEVA / STDEVPA関数

論理値と文字列も含めて標準偏差を計算する関数である。

構文:

=STDEVA(値1, [値2], ...)
=STDEVPA(値1, [値2], ...)

使用例:

• =STDEVA(A1:A10)

  論理値・文字列を含む標本標準偏差

順位・パーセンタイル

RANK.EQ / RANK.AVG関数

データの順位を求める関数である。

構文:

=RANK.EQ(数値, 範囲, [順序])
=RANK.AVG(数値, 範囲, [順序])

引数:

• 数値

  順位を調べる値

• 範囲

  順位の母集団となる範囲

• 順序

  0または省略で降順、1で昇順

RANK.EQ関数 / RANK.AVG関数の違い:

• RANK.EQ

  同順位がある場合、最上位の順位を返す。

• RANK.AVG

  同順位がある場合、平均順位を返す。

使用例:

• =RANK.EQ(A1, $A$1:$A$10, 0)

  A1の降順での順位

• =RANK.AVG(85, A1:A10)

  85点の平均順位

応用例:

• 成績表での順位付け
• 売上ランキングの作成

PERCENTILE.INC / PERCENTILE.EXC関数

指定したパーセンタイル値を返す関数である。

構文:

=PERCENTILE.INC(配列, k)
=PERCENTILE.EXC(配列, k)

引数:

• 配列

  データの範囲

• k

  パーセンタイル値 (0～1の小数)

PERCENTILE.INC関数 / PERCENTILE.EXC関数の違い:

• PERCENTILE.INC

  0と1を含む $\quad (k:0\leq k\leq 1)$

• PERCENTILE.EXC

  0と1を除く $\quad (k:0<k<1)$

使用例:

• =PERCENTILE.INC(A1:A100, 0.9)

  90パーセンタイル値

• =PERCENTILE.INC(A1:A100, 0.25)

  第1四分位数

• =PERCENTILE.INC(A1:A100, 0.5)

  第2四分位数(中央値)

QUARTILE.INC / QUARTILE.EXC関数

四分位数を返す関数である。

構文:

=QUARTILE.INC(配列, 戻り値)
=QUARTILE.EXC(配列, 戻り値)

戻り値の指定:

• 0

  最小値

• 1

  第1四分位数 (25パーセンタイル)

• 2

  第2四分位数 (中央値、50パーセンタイル)

• 3

  第3四分位数 (75パーセンタイル)

• 4

  最大値

使用例:

• =QUARTILE.INC(A1:A100, 1)

  第1四分位数

• =QUARTILE.INC(A1:A100, 3)

  第3四分位数

応用例:

• 箱ひげ図の作成に使用
• データの分布の把握

PERCENTRANK.INC / PERCENTRANK.EXC関数

特定の値がデータセット内で何パーセンタイルに位置するかを返す関数である。

構文:

=PERCENTRANK.INC(配列, 値, [有効桁数])
=PERCENTRANK.EXC(配列, 値, [有効桁数])

使用例:

• =PERCENTRANK.INC(A1:A100, 85)

  85点が何パーセンタイルかを返す。

• =PERCENTRANK.INC(A1:A100, B1, 3)

  B1セルの値が何パーセンタイルか、小数第3位まで表示して返す。

最大値・最小値・範囲

LARGE / SMALL関数

k番目に大きい値、小さい値を返す関数である。

構文:

=LARGE(配列, k)
=SMALL(配列, k)

使用例:

• =LARGE(A1:A10, 1)

  最大値 (MAX関数と同じ)

• =LARGE(A1:A10, 2)

  2番目に大きい値

• =SMALL(A1:A10, 1)

  最小値 (MIN関数と同じ)

• =SMALL(A1:A10, 3)

  3番目に小さい値

応用例:

• 上位3位までの平均

  =AVERAGE(LARGE(A1:A10, 1), LARGE(A1:A10, 2), LARGE(A1:A10, 3))

• 最高点と最低点を除いた平均

  =(SUM(A1:A10)-LARGE(A1:A10,1)-SMALL(A1:A10,1))/(COUNT(A1:A10)-2)

MAXIFS / MINIFS関数

条件に一致する最大値・最小値を返す関数である。

構文:

=MAXIFS(最大範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
=MINIFS(最小範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)

使用例:

• =MAXIFS(C1:C100, A1:A100, "東京")

  東京の最大値

• =MINIFS(C1:C100, A1:A100, "東京", B1:B100, ">=2024/1/1")

  東京かつ2024年以降の最小値

頻度分布

FREQUENCY関数

度数分布表を作成する配列数式関数である。

構文:

=FREQUENCY(データ配列, 区間配列)

使用例:
データ範囲がA1:A50、区間が10, 20, 30, 40, 50とする場合:

1. B1:B6を選択する。(区間数+1のセルを選択)
2. =FREQUENCY(A1:A50, {10;20;30;40;50}) を入力する。
3. [Ctrl] + [Shift] + [Enter] で配列数式として確定する。

結果:

• B1

  10以下の個数

• B2

  10超～20以下の個数

• B3

  20超～30以下の個数

• ...
• B6: 50超の個数

応用例:

• ヒストグラムの作成
• 成績分布の集計

COUNTIFS関数

複数条件でのカウントに使用する関数である。

構文:

=COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2], ...)

使用例:

• 成績が60点以上80点未満の人数

  =COUNTIFS(A1:A50, ">=60", A1:A50, "<80")

• 東京支店で売上100万以上の件数

  =COUNTIFS(B1:B100, "東京", C1:C100, ">=1000000")

相関・回帰分析

CORREL関数

2つのデータセットの相関係数を計算する関数である。

構文:

=CORREL(配列1, 配列2)

使用例:

• =CORREL(A1:A100, B1:B100)

  A列とB列の相関係数

※相関係数とは
-1から1の値をとり、以下に示すように解釈される。

• 1に近い

  強い正の相関

• 0に近い

  相関なし

• -1に近い

  強い負の相関

応用例:

• 広告費と売上の相関分析
• 気温と来客数の関係分析

COVARIANCE.P / COVARIANCE.S関数

共分散を計算する関数である。

構文:

母共分散
=COVARIANCE.P(配列1, 配列2)

標本共分散
=COVARIANCE.S(配列1, 配列2)

使用例:

• =COVARIANCE.P(A1:A100, B1:B100)

  母共分散

• =COVARIANCE.S(A1:A100, B1:B100)

  標本共分散

※共分散とは
2つの変数の関係性を示す指標である。
相関係数の分子に相当する値。

SLOPE / INTERCEPT関数

線形回帰の傾きと切片を求める関数である。

構文:

傾き
=SLOPE(y範囲, x範囲)

切片
=INTERCEPT(y範囲, x範囲)

使用例:

• =SLOPE(B1:B100, A1:A100)

  回帰直線の傾き

• =INTERCEPT(B1:B100, A1:A100)

  回帰直線のy切片

回帰直線の式:
$y=SLOPE\times x+{\mbox{INTERCEPT}}$

予測値の計算例:
=SLOPE(B1:B100, A1:A100) * C1 + INTERCEPT(B1:B100, A1:A100)

応用例:

• 売上予測
• トレンド分析

FORECAST / FORECAST.LINEAR関数

線形回帰を使って将来の値を予測する関数である。

構文:

=FORECAST.LINEAR(x, y範囲, x範囲)

使用例:

• =FORECAST.LINEAR(12, B1:B11, A1:A11)

  1～11月のデータから12月を予測

※注意

• FORECAST関数は互換性のために残されている。
• 新しいブックでは、FORECAST.LINEAR を使用することが推奨される。

RSQ関数

決定係数 (R²) を計算する関数である。

構文:

=RSQ(y範囲, x範囲)

使用例:

• =RSQ(B1:B100, A1:A100)

  決定係数

※決定係数とは
回帰モデルの当てはまりの良さを示す指標で、0から1の値をとる。

• 1に近い

  モデルの当てはまりが良い

• 0に近い

  モデルの説明力が低い

決定係数は相関係数の2乗に等しい。

=RSQ(B1:B100, A1:A100) = CORREL(B1:B100, A1:A100)^2

正規分布・標準化

NORM.DIST関数

正規分布の確率密度または累積分布を返す関数である。

構文:

=NORM.DIST(x, 平均, 標準偏差, 関数形式)

引数:

• x

  分布の値

• 平均

  分布の平均値

• 標準偏差

  分布の標準偏差

• 関数形式

  TRUE=累積分布、FALSE=確率密度

使用例:

• =NORM.DIST(100, 80, 10, TRUE)

  平均80、標準偏差10の正規分布で100以下になる確率

• =NORM.DIST(100, 80, 10, FALSE)

  x=100での確率密度

NORM.S.DIST関数

標準正規分布(平均0、標準偏差1)の確率密度または累積分布を返す関数である。

構文:

=NORM.S.DIST(z, 関数形式)

使用例:

• =NORM.S.DIST(1.96, TRUE)

  約0.975 (z≦1.96の確率)

• =NORM.S.DIST(0, TRUE)

  0.5 (z≦0の確率)

NORM.INV / NORM.S.INV関数

正規分布の累積分布の逆関数である。

構文:

=NORM.INV(確率, 平均, 標準偏差)
=NORM.S.INV(確率)

使用例:

• =NORM.INV(0.95, 100, 15)

  上位5%の境界値

• =NORM.S.INV(0.975)

  約1.96 (片側2.5%点)

応用例:

• 信頼区間の計算
• 検査基準値の設定

STANDARDIZE関数

値を標準化 (zスコア化) する関数である。

構文:

=STANDARDIZE(x, 平均, 標準偏差)

使用例:

• =STANDARDIZE(85, 70, 10)

  1.5 (平均から標準偏差1.5個分上)

zスコアの計算式:

z = (x - 平均) / 標準偏差

応用例:

• 異なる尺度のテストスコアの比較
• 外れ値の検出 (|z| > 3を異常値とする等)

その他の分布関数

T.DIST / T.INV関数

t分布に関する関数である。

構文:

=T.DIST(x, 自由度, 関数形式)
=T.DIST.RT(x, 自由度)
=T.DIST.2T(x, 自由度)
=T.INV(確率, 自由度)
=T.INV.2T(確率, 自由度)

使用例:

• =T.DIST.2T(2.262, 9)

  自由度9のt分布で両側確率

• =T.INV.2T(0.05, 9)

  自由度9で有意水準5%の臨界値

応用例:

• t検定の実施
• 少標本での信頼区間の計算

CHISQ.DIST / CHISQ.INV関数

カイ二乗分布に関する関数である。

構文:

=CHISQ.DIST(x, 自由度, 関数形式)
=CHISQ.DIST.RT(x, 自由度)
=CHISQ.INV(確率, 自由度)
=CHISQ.INV.RT(確率, 自由度)

使用例:

• =CHISQ.DIST.RT(7.815, 3)

  カイ2乗値7.815の上側確率

• =CHISQ.INV.RT(0.05, 3)

  有意水準5%の臨界値

応用例:

• カイ2乗検定
• 適合度検定

F.DIST / F.INV関数

F分布に関する関数である。

構文:

=F.DIST(x, 自由度1, 自由度2, 関数形式)
=F.DIST.RT(x, 自由度1, 自由度2)
=F.INV(確率, 自由度1, 自由度2)
=F.INV.RT(確率, 自由度1, 自由度2)

使用例:

• =F.DIST.RT(3.86, 5, 10)

  F値3.86の上側確率

応用例:

• 分散分析(ANOVA)
• 等分散性の検定

偏差値・変動係数

偏差値の計算

標準化された得点を偏差値に変換する方法である。

計算式:

=ROUND(50 + 10 * (A1 - AVERAGE($A$1:$A$100)) / STDEV.S($A$1:$A$100), 1)

または、STANDARDIZE関数を使用:

=ROUND(50 + 10 * STANDARDIZE(A1, AVERAGE($A$1:$A$100), STDEV.S($A$1:$A$100)), 1)

A1のテストの偏差値を計算し、小数第1位に丸める。

※偏差値とは
平均を50、標準偏差を10とした標準得点である。
日本の教育現場で広く使用される。

変動係数の計算

データのばらつきを相対的に評価する指標である。

計算式:

=STDEV.S(A1:A100) / AVERAGE(A1:A100)

パーセント表示:

=STDEV.S(A1:A100) / AVERAGE(A1:A100) * 100 & "%"

※変動係数とは
標準偏差を平均で除した値で、単位の異なるデータのばらつきを比較できる。

実用的な使用例

成績分析

テストの基本統計量を一覧表示する例。

• 平均点

  =AVERAGE(B2:B100)

• 中央値

  =MEDIAN(B2:B100)

• 最高点

  =MAX(B2:B100)

• 最低点

  =MIN(B2:B100)

• 標準偏差

  =STDEV.S(B2:B100)

• 60点以上の人数

  =COUNTIF(B2:B100, ">=60")

• 合格率(60点以上)

  =COUNTIF(B2:B100, ">=60") / COUNT(B2:B100) * 100 & "%"

売上データ分析

月別売上の傾向を分析する例。

• 月平均売上

  =AVERAGE(B2:B13)

• 売上の標準偏差

  =STDEV.S(B2:B13)

• 変動係数

  =STDEV.S(B2:B13) / AVERAGE(B2:B13) * 100 & "%"

• 最高売上月

  =INDEX(A2:A13, MATCH(MAX(B2:B13), B2:B13, 0))

• 上位3ヶ月の平均

  =(LARGE(B2:B13, 1) + LARGE(B2:B13, 2) + LARGE(B2:B13, 3)) / 3

品質管理

製品の寸法データから管理限界を計算する例。

• 平均値

  =AVERAGE(A2:A100)

• 標準偏差

  =STDEV.S(A2:A100)

• 上方管理限界 (UCL)

  =AVERAGE(A2:A100) + 3 * STDEV.S(A2:A100)

• 下方管理限界 (LCL)

  =AVERAGE(A2:A100) - 3 * STDEV.S(A2:A100)

• 規格外の個数

  =COUNTIFS(A2:A100, ">"&(AVERAGE(A2:A100)+3*STDEV.S(A2:A100))) + COUNTIFS(A2:A100, "<"&(AVERAGE(A2:A100)-3*STDEV.S(A2:A100)))

相関分析

広告費と売上の関係を分析する例。

• 相関係数

  =CORREL(B2:B13, C2:C13)

• 決定係数

  =RSQ(C2:C13, B2:B13)

• 回帰直線の傾き

  =SLOPE(C2:C13, B2:B13)

• 回帰直線の切片

  =INTERCEPT(C2:C13, B2:B13)

• 予測売上 (広告費100万円の場合)

  =SLOPE(C2:C13, B2:B13) * 100 + INTERCEPT(C2:C13, B2:B13)

• 予測売上 (広告費100万円の場合)
  FORECAST.LINEAR関数を使用

  =FORECAST.LINEAR(100, C2:C13, B2:B13)

データのランク付け

売上に基づいて営業担当者をランク付けする例。

• 順位 (降順)

  =RANK.EQ(B2, $B$2:$B$20, 0)

• 上位10%に入っているか判定

  =IF(B2>=PERCENTILE.INC($B$2:$B$20, 0.9), "A", "")

• 四分位グループ分け

  =IF(B2>=QUARTILE.INC($B$2:$B$20,3),"上位",IF(B2>=MEDIAN($B$2:$B$20),"中上",IF(B2>=QUARTILE.INC($B$2:$B$20,1),"中下","下位")))

アンケート集計

5段階評価のアンケート結果を分析する例。

• 平均評価

  =AVERAGE(B2:B100)

• 最頻値 (最も多い回答)

  =MODE.SNGL(B2:B100)

• 各評価の回答数

  評価1: =COUNTIF(B2:B100, 1)

  評価2: =COUNTIF(B2:B100, 2)

  評価3: =COUNTIF(B2:B100, 3)

  評価4: =COUNTIF(B2:B100, 4)

  評価5: =COUNTIF(B2:B100, 5)

• 肯定的回答率 (4以上)

  =COUNTIF(B2:B100, ">=4") / COUNT(B2:B100) * 100 & "%"

外れ値の検出

データの中から統計的外れ値を検出する例。

• zスコアによる判定 (C2セルに入力)

  =IF(ABS((B2-AVERAGE($B$2:$B$100))/STDEV.S($B$2:$B$100))>3,"外れ値","正常")

• 四分位範囲 (IQR) 法による判定

  Q1: =QUARTILE.INC($B$2:$B$100, 1)

  Q3: =QUARTILE.INC($B$2:$B$100, 3)

  IQR: =QUARTILE.INC($B$2:$B$100, 3) - QUARTILE.INC($B$2:$B$100, 1)

  下限: =QUARTILE.INC($B$2:$B$100, 1) - 1.5 * (QUARTILE.INC($B$2:$B$100, 3) - QUARTILE.INC($B$2:$B$100, 1))

  上限: =QUARTILE.INC($B$2:$B$100, 3) + 1.5 * (QUARTILE.INC($B$2:$B$100, 3) - QUARTILE.INC($B$2:$B$100, 1))

• 判定 (C2セルに入力)

  =IF(OR(B2<下限のセル, B2>上限のセル), "外れ値", "正常")

信頼区間の計算

標本データから母平均の95%信頼区間を計算する例。

• 標本平均

  =AVERAGE(A2:A100)

• 標本標準偏差

  =STDEV.S(A2:A100)

• 標本サイズ

  =COUNT(A2:A100)

• 標準誤差

  =STDEV.S(A2:A100) / SQRT(COUNT(A2:A100))

• t値(自由度n-1、両側5%)

  =T.INV.2T(0.05, COUNT(A2:A100)-1)

• 誤差範囲

  =T.INV.2T(0.05, COUNT(A2:A100)-1) * STDEV.S(A2:A100) / SQRT(COUNT(A2:A100))

• 誤差範囲 (CONFIDENCE.T関数を使用する場合)

  =CONFIDENCE.T(0.05, STDEV.S(A2:A100), COUNT(A2:A100))

• 信頼区間下限

  =AVERAGE(A2:A100) - <誤差範囲のセル>

• 信頼区間下限 (CONFIDENCE.T関数を使用する場合)

  =AVERAGE(A2:A100) - CONFIDENCE.T(0.05, STDEV.S(A2:A100), COUNT(A2:A100))

• 信頼区間上限

  =AVERAGE(A2:A100) + <誤差範囲のセル>

• 信頼区間上限 (CONFIDENCE.T関数を使用する場合)

  =AVERAGE(A2:A100) + CONFIDENCE.T(0.05, STDEV.S(A2:A100), COUNT(A2:A100))

• 信頼区間

  下限: =AVERAGE(A2:A100) - CONFIDENCE.T(0.05, STDEV.S(A2:A100), COUNT(A2:A100))

  上限: =AVERAGE(A2:A100) + CONFIDENCE.T(0.05, STDEV.S(A2:A100), COUNT(A2:A100))