スプレッドシートのLOGNORM.INV関数の使い方｜対数正規分布逆関数

スポンサーリンク

「売上の上位10%に入るボーダーラインはいくらだろう？」。対数正規分布に従うデータで、こんな疑問を感じたことはありませんか？

平均と標準偏差はわかっていても、「確率から具体的な値」を手計算するのは大変ですよね。

そんなときに使うのがLOGNORM.INV関数です。この記事ではGoogleスプレッドシートでのLOGNORM.INV関数の使い方を、基本構文から実務活用まで解説します。LOGNORM.DIST関数との逆関係やエラー対処法もあわせて紹介しますよ。

スプレッドシートのLOGNORM.INV関数とは

LOGNORM.INV関数（読み方: ログノーム・インバース関数）は、対数正規分布の逆関数です。確率を指定すると、その確率に対応する値を返してくれます。「LOGNORM」は「Lognormal（対数正規）」、「INV」は「Inverse（逆）」の略です。

対数正規分布とは、データの自然対数をとると正規分布になる分布のことです。売上や年収のように「値が必ず正で、右に裾が長い」データによく当てはまります。

たとえば「月間売上の対数平均が2、対数標準偏差が0.5のとき、上位10%の売上ボーダーはいくらか」を1つの数式で求められます。

LOGNORM.INV関数にできることをまとめると、次のとおりです。

  • 確率から対数正規分布の値を逆算する
  • 売上や年収の上位○%ボーダーラインを算出する
  • 待ち時間や処理時間の上限目標を設定する
  • LOGNORM.DIST関数と組み合わせて双方向の分析を行う

NOTE

LOGNORM.INV関数はGoogleスプレッドシートの全バージョンで使えます。Excelにも同名の関数があり、動作は同じです。

LOGNORM.INV関数の基本構文と引数

基本構文

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

カッコの中に3つの引数を指定します。

引数の意味

引数必須/任意説明
確率（probability）必須0より大きく1より小さい確率値
平均（mean）必須LN関数で対数変換した値の平均
標準偏差（standard_deviation）必須対数変換した値の標準偏差（0より大きい値）

確率には0と1そのものは指定できません。0や1を入力すると#NUM!エラーになります。

ここで大事なポイントがあります。2番目と3番目の引数は「元データの平均・標準偏差」ではなく、「対数変換後の平均・標準偏差」です。元データをそのまま渡さないように注意してくださいね。

TIP

確率に0.9を指定すると「下位90%の境界値」、つまり「上位10%のボーダーライン」が返ります。「上位○%」で考えるときは「1 – 上位の割合」を確率に指定しましょう。

LOGINV（旧関数名）との関係

GoogleスプレッドシートにはLOGINVという関数もあります。これはLOGNORM.INVの旧バージョンで、計算結果は同じです。

=LOGINV(0.9, 2, 0.5)         ← 旧関数名（動作は同じ）
=LOGNORM.INV(0.9, 2, 0.5)    ← 新関数名（推奨）

どちらを使っても結果は変わりませんが、Googleの公式ドキュメントではLOGNORM.INVが推奨されています。新しく数式を書くときはLOGNORM.INVを使いましょう。

LOGNORM.INV関数の使い方（基本例）

まずはシンプルな例で動きを確認してみましょう。対数平均2・対数標準偏差0.5の対数正規分布を想定します。

=LOGNORM.INV(0.5, 2, 0.5)

結果は約7.39です。確率0.5（50%）を指定すると中央値が返ります。これはEXP関数でEXP(2) ≈ 7.39と同じ値です。対数正規分布では中央値がEXP(対数平均)になるという性質がありますよ。

確率を変えて、いくつかの値を見てみましょう。

確率数式結果意味
0.1=LOGNORM.INV(0.1, 2, 0.5)約3.89下位10%の境界値
0.25=LOGNORM.INV(0.25, 2, 0.5)約5.27下位25%の境界値
0.5=LOGNORM.INV(0.5, 2, 0.5)約7.39中央値
0.75=LOGNORM.INV(0.75, 2, 0.5)約10.35上位25%のボーダー
0.9=LOGNORM.INV(0.9, 2, 0.5)約14.03上位10%のボーダー

確率が大きくなるほど、返される値も大きくなります。正規分布の場合と違い、中央値と平均値が一致しないのが対数正規分布の特徴ですよ。

実務で使えるLOGNORM.INV関数の活用例

基本がわかったところで、実際の業務で使えるパターンを紹介します。

売上のボーダーライン算出

各店舗の月間売上（万円）の対数平均が3.5、対数標準偏差が0.8だとします。「上位10%に入る売上はいくらか」を求めてみましょう。

=LOGNORM.INV(0.9, 3.5, 0.8)

結果は約92.30（万円） です。月間売上が約92万円以上なら上位10%に入るとわかります。

逆に、下位20%のボーダーも求めてみましょう。

=LOGNORM.INV(0.2, 3.5, 0.8)

結果は約16.89（万円） です。売上17万円以下の店舗が下位20%に該当します。テコ入れの目安として使えますよね。

実際のデータから計算するなら、AVERAGE関数とSTDEV関数をLN関数と組み合わせましょう。

=LOGNORM.INV(0.9, AVERAGE(LN(B2:B100)), STDEV(LN(B2:B100)))

データが変わってもボーダーラインが自動更新されるので便利ですよ。

待ち時間の上限目標を設定する

コールセンターの対応時間（分）の対数平均が1.5、対数標準偏差が0.6のとき、「95%の問い合わせがこの時間内に終わる」という上限を設定してみます。

=LOGNORM.INV(0.95, 1.5, 0.6)

結果は約12.02（分） です。「対応時間の目標は12分以内」とSLA（サービスレベル合意）に設定できます。

LOGNORM.DIST関数と組み合わせれば、検証もかんたんです。

=LOGNORM.DIST(12.02, 1.5, 0.6, TRUE)

結果は約0.95（95%）と返ります。逆関数で求めた値を順関数に戻すと元の確率になることが確認できますよ。

LOGNORM.DISTとの違い・使い分け

LOGNORM.INV関数は、LOGNORM.DIST関数の逆関数です。2つの関数は「入力と出力が逆」の関係にあります。

関数入力出力方向
LOGNORM.DIST値（x）確率（p）値 → 確率
LOGNORM.INV確率（p）値（x）確率 → 値

具体例で確認してみましょう。対数平均2・対数標準偏差0.5の場合です。

=LOGNORM.DIST(14.03, 2, 0.5, TRUE)   → 約0.9（90%）
=LOGNORM.INV(0.9, 2, 0.5)            → 約14.03

LOGNORM.DISTに14.03を入れると確率0.9が返り、LOGNORM.INVに0.9を入れると14.03が返ります。お互いの結果を入れ替えても元に戻るということですね。

使い分けのポイントは次のとおりです。

  • 「売上10万円以下は全体の何%？」 → LOGNORM.DIST（値がわかっていて確率を知りたい）
  • 「上位10%の売上ボーダーはいくら？」 → LOGNORM.INV（確率がわかっていて値を知りたい）

「値から確率」か「確率から値」か、どちらの方向で計算したいかで使い分けてください。

NORM.INVとの違い

「NORM.INV関数とどう違うの？」と迷うこともあるかもしれません。使い分けの基準は「データの分布の形」です。

判断基準NORM.INVLOGNORM.INV
データの形状左右対称（釣鐘型）右に裾が長い
値の範囲マイナスもあり得る必ず正の値
代表例テスト成績、身長、気温売上、年収、待ち時間
対数をとると？正規分布のまま正規分布になる

じつは、LOGNORM.INVの計算は内部的にNORM.INV関数とEXP関数で再現できます。

=LOGNORM.INV(0.9, 2, 0.5)
=EXP(NORM.INV(0.9, 2, 0.5))

この2つは同じ結果（約14.03）を返します。「NORM.INVで正規分布の値を求めてからEXPで元のスケールに戻す」という仕組みですね。

よくあるエラーと対処法

LOGNORM.INV関数でつまずきやすいポイントをまとめました。

確率に0以下や1以上を指定して#NUM!エラー

確率は「0より大きく1より小さい値」でなければなりません。0や1、負の数を指定すると#NUM!エラーになります。

=LOGNORM.INV(0, 2, 0.5)    ← #NUM! エラー
=LOGNORM.INV(1, 2, 0.5)    ← #NUM! エラー
=LOGNORM.INV(-0.5, 2, 0.5) ← #NUM! エラー

他のセルの計算結果を確率として渡すときは、値が0〜1の範囲内か確認しておきましょう。

「元データの平均」を引数に渡してしまう

2番目の引数は「対数変換後の平均」です。元データの平均（たとえば売上の平均50万円）をそのまま渡すと、結果が大きくずれます。元データから計算するには、次のようにします。

=AVERAGE(LN(B2:B100))   ← 対数平均を求める
=STDEV(LN(B2:B100))     ← 対数標準偏差を求める

TIP

AVERAGE(LN(範囲))はArrayFormula不要で動作します。LN関数が配列を自動展開してくれますよ。

標準偏差に0以下を指定して#NUM!エラー

標準偏差は0より大きい値が必要です。データがすべて同じ値のとき、STDEV関数は0を返します。その結果をそのままLOGNORM.INVに渡すとエラーになるので注意してください。

引数に文字列を渡して#VALUE!エラー

数値であるべき引数にテキストが入ると#VALUE!エラーになります。セル参照を使うときは、参照先が数値になっているか確認してくださいね。

まとめ

LOGNORM.INV関数は、対数正規分布で確率から値を逆算する関数です。

  • 3つの引数（確率・対数平均・対数標準偏差）を指定するだけで使える
  • 確率0.5を指定すると中央値が返る。EXP関数でEXP(対数平均)と同じ値
  • 売上のボーダーライン算出、待ち時間の上限設定に活用できる
  • LOGNORM.DIST関数の逆関数。「確率→値」の方向で計算したいときに使う
  • NORM.INV関数との違いは「データの分布の形」。右に裾が長いデータならLOGNORM.INVを選ぶ
  • 引数の平均・標準偏差は「対数変換後」の値。元データの値をそのまま渡さないように注意
  • 確率に0や1を指定すると#NUM!エラー。範囲は0より大きく1より小さい値

「上位○%のボーダーラインはいくら？」を対数正規分布で即座に答えられるようになると、売上分析やSLA設定がぐっとラクになります。ぜひ実際のデータで試してみてくださいね。

スポンサーリンク
タイトルとURLをコピーしました