スプレッドシートのLOGNORM.DIST関数の使い方｜対数正規分布の確率を求める

スポンサーリンク

「売上データを分析したいけど、ばらつきが大きすぎて正規分布ではうまく扱えない」。そんな経験はありませんか？

売上や年収のように、少数の大きな値が分布を右に引っ張るデータは正規分布にフィットしません。無理に当てはめると、確率計算がずれてしまいますよね。

そんなときに使うのがLOGNORM.DIST関数です。この記事ではGoogleスプレッドシートでのLOGNORM.DIST関数の使い方を、基本構文から実務活用まで解説します。TRUE/FALSEの違いや、NORM.DIST関数との使い分けもあわせて紹介しますよ。

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

LOGNORM.DIST関数（読み方: ログノーム・ディスト関数）は、対数正規分布にもとづいて確率を返す関数です。「LOGNORM」は「Lognormal（対数正規）」、「DIST」は「Distribution（分布）」の略です。

対数正規分布とは、データの自然対数をとると正規分布になる分布のことです。もう少しかみ砕くと、「値が必ず正で、右に裾が長い」データによく当てはまるモデルです。

たとえば「月間売上の対数平均が2、対数標準偏差が0.5のとき、売上10万円以下の確率は何%か」を1つの数式で求められます。

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

  • ある売上額が全体の何%に位置するかを求める（累積確率）
  • 対数正規分布グラフ上の密度（高さ）を取得する
  • 待ち時間や処理時間が一定以内に収まる確率を計算する
  • 正規分布では扱いにくい、右に偏ったデータを正しく分析する

NOTE

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

基本構文と4つの引数

=LOGNORM.DIST(x, 平均, 標準偏差, 累積)

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

引数必須/任意説明
x必須確率を求めたい数値（0より大きい値）
平均（mean）必須LN関数で対数変換した値の平均
標準偏差（standard_deviation）必須対数変換した値の標準偏差（0より大きい値）
累積（cumulative）必須TRUEで累積分布、FALSEで確率密度

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

TIP

xに0以下を指定すると#NUM!エラーになります。対数正規分布は正の値だけで定義されるためです。標準偏差に0以下を指定した場合も#NUM!エラーです。

LOGNORMDIST（旧関数名）との関係

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

=LOGNORMDIST(10, 2, 0.5)          ← 旧関数名（累積のみ）
=LOGNORM.DIST(10, 2, 0.5, TRUE)   ← 新関数名（推奨）

旧関数には累積/密度の切り替え引数がありません。新しく数式を書くときはLOGNORM.DISTを使いましょう。

TRUE/FALSEで何が変わる？累積と密度の違い

LOGNORM.DIST関数の4番目の引数「累積」は、TRUEかFALSEで結果がまったく変わります。この違いを理解しておくことが使いこなすポイントです。

TRUE（累積分布関数）――x以下は全体の何%？

TRUEを指定すると累積分布関数（CDF）の値を返します。「ある値以下になる確率の合計」を求めるものです。実務で使う場面のほとんどがこちらです。

=LOGNORM.DIST(10, 2, 0.5, TRUE)

この数式は「対数平均2・対数標準偏差0.5の対数正規分布で、10以下になる確率」を返します。結果は約0.7275（72.8%） です。

FALSE（確率密度関数）――その値の密度は？

FALSEを指定すると確率密度関数（PDF）の値を返します。対数正規分布グラフ上のy座標の値です。

=LOGNORM.DIST(10, 2, 0.5, FALSE)

結果は約0.0664です。これは「確率」ではなくグラフの高さなので、直接「何%」とは読めません。分布グラフを描くときに使います。

2つを比較してみる（サンプルデータつき）

対数平均2・対数標準偏差0.5の対数正規分布で、xの値を変えたときの結果を比べてみましょう。

xTRUE（累積確率）FALSE（確率密度）
30.0357（3.6%）0.0524
50.2173（21.7%）0.1176
70.4570（45.7%）0.1133
100.7275（72.8%）0.0664
150.9216（92.2%）0.0195

TRUEの列はxが大きくなるほど1に近づきます。FALSEの列は正規分布と違い、ピークが左寄りで右側にゆっくり下がっていくのが特徴です。

実務では「x以下の確率を知りたい」場面がほとんどなので、TRUEを使うケースが圧倒的に多いですよ。

実務活用3パターン

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

売上データの分析――一定額以下の割合を把握する

月間売上のように「ゼロ以上で右に裾が長い」データは対数正規分布にフィットしやすいです。

たとえば各店舗の月間売上（万円）の対数平均が3.5、対数標準偏差が0.8だとします。売上50万円以下の店舗はどのくらいの割合でしょうか。

=LOGNORM.DIST(50, 3.5, 0.8, TRUE)

結果は約0.6967（69.7%） です。売上50万円以下の店舗が全体の約70%だとわかります。

「売上100万円を超える店舗は？」と聞かれたら、1から引けばOKです。

=1 - LOGNORM.DIST(100, 3.5, 0.8, TRUE)

待ち時間・処理時間の分析

コールセンターの対応時間やシステムの処理時間も、対数正規分布に従いやすいデータです。短い時間に集中しつつ、たまに長くなるケースがありますよね。

対応時間（分）の対数平均が1.5、対数標準偏差が0.6のコールセンターで、10分以内に対応が完了する確率を求めてみましょう。

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

結果は約0.9095（91.0%） です。約91%の問い合わせが10分以内に終わることがわかります。SLA（サービスレベル合意）の設定やスタッフ配置の判断に活用できますよ。

NORM.DISTとの使い分け

「LOGNORM.DISTとNORM.DIST関数、どちらを使えばいいの？」と迷うこともあるかもしれません。使い分けの基準は次のとおりです。

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

迷ったときは、データの分布をヒストグラムで確認してみましょう。右に長く裾を引いていたらLOGNORM.DIST、左右対称ならNORM.DISTを選べば大丈夫です。

LN・EXP関数との関係

LOGNORM.DIST関数は、内部的には「LN関数で対数変換 → 正規分布として計算」という処理を行っています。

つまり、次の2つの数式は同じ結果を返します。

=LOGNORM.DIST(10, 2, 0.5, TRUE)
=NORM.DIST(LN(10), 2, 0.5, TRUE)

LN(10)は約2.3026です。「10の自然対数」を求めてからNORM.DISTに渡しているわけですね。

この関係を知っておくと、対数平均や対数標準偏差の意味が直感的に理解できます。「元データにLN関数を適用した値の平均と標準偏差」がそのまま引数になるということです。

逆に、対数正規分布の平均値（元データのスケール）を知りたいときはEXP関数を使います。

=EXP(対数平均 + 対数標準偏差^2 / 2)

対数平均2・対数標準偏差0.5のとき、EXP(2 + 0.25/2) = EXP(2.125) ≈ 8.37です。元データの平均は約8.37万円ということがわかりますよ。

よくある間違いと注意点

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

xに0以下を指定して#NUM!エラー

対数正規分布は正の値だけで定義されます。0や負の値を指定すると#NUM!エラーです。売上データに「0円」が含まれている場合は、フィルタで除外するか、IF関数でスキップしましょう。

=LOGNORM.DIST(0, 2, 0.5, TRUE)   ← #NUM! エラー

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

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

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

TIP

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

TRUE/FALSEの指定を間違える

4番目の引数は省略できません。「x以下の確率」が欲しいのにFALSEを指定すると、密度値が返ってきて意味が変わります。目的に合った値を選んでくださいね。

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

標準偏差は0より大きい値が必要です。データが全部同じ値の場合、STDEV関数は0を返すので注意しましょう。

まとめ

LOGNORM.DIST関数は、対数正規分布にもとづいて確率を求める関数です。

  • 4番目の引数にTRUEを指定すると「x以下の確率」が返る
  • FALSEを指定するとグラフの密度値が返る（実務ではTRUEが主役）
  • 売上・年収・待ち時間など「右に偏ったデータ」の分析に最適
  • NORM.DIST関数との使い分けは「データの形状」で判断する
  • 引数の平均・標準偏差は「対数変換後」の値。元データの値をそのまま渡さないように注意
  • LN関数・EXP関数との関係を理解すると、対数正規分布の考え方がぐっとクリアになる

「正規分布ではうまく分析できないデータ」に出会ったら、LOGNORM.DIST関数の出番です。ぜひ実際のデータで試してみてくださいね。

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