スプレッドシートのNEGBINOM.DIST関数の使い方｜負の二項分布

スポンサーリンク

「合格者を3人出すまでに、何人落ちるかの確率ってわかるの？」。採用や検品の場面で、こんな疑問を感じたことはありませんか？

成功するまでに何回失敗するかは、感覚に頼りがちです。でも手計算で確率を求めるのは現実的ではないですよね。

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

NEGBINOM.DIST関数とは

NEGBINOM.DIST関数（読み方: ネガティブ・バイノム・ディスト関数）は、負の二項分布にもとづいて確率を返す関数です。負の二項分布とは、「成功がk回に達するまでに、失敗がちょうどn回起きる確率」の分布です。「NEGBINOM」は「Negative Binomial（負の二項）」、「DIST」は「Distribution（分布）」の略です。

たとえば「合格率20%の面接で3人採用するまでに、不合格がちょうど10人になる確率」を1つの数式で求められます。

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

  • 目標の成功回数に達するまでの失敗回数の確率を求める
  • 「n回以下の失敗で目標に達する確率」を累積で求める
  • 採用活動や品質検査で、必要な試行回数を見積もる
  • 営業目標の達成までに想定される不成約件数をシミュレーションする

NOTE

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

負の二項分布が成り立つ3つの条件

NEGBINOM.DIST関数を使うには、データが負の二項分布の前提を満たしている必要があります。

  1. 結果が2択: 各試行の結果は「成功」か「失敗」のどちらか
  2. 成功確率が一定: 毎回の試行で成功する確率が変わらない
  3. 各試行が独立: ある試行の結果が、次の試行の結果に影響しない

この3条件はBINOM.DIST関数と同じです。違いは「何を数えるか」だけです。BINOM.DISTは決まった回数の試行で成功回数を数えます。NEGBINOM.DISTは目標の成功回数に達するまでの失敗回数を数えます。

基本構文と4つの引数

=NEGBINOM.DIST(失敗回数, 成功回数, 成功確率, 累積)

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

引数必須/任意説明
失敗回数（number_f）必須失敗の回数（0以上の整数）
成功回数（number_s）必須目標とする成功の回数（1以上の整数）
成功確率（probability_s）必須各試行で成功する確率（0〜1の範囲）
累積（cumulative）必須TRUEで累積確率、FALSEで確率質量

TIP

第1引数は「失敗回数」です。BINOM.DIST関数の第1引数が「成功回数」なので、混同しないように注意してください。

NEGBINOMDIST（旧関数名）との関係

GoogleスプレッドシートにはNEGBINOMDISTという関数もあります。これはNEGBINOM.DISTの旧バージョンです。

=NEGBINOMDIST(5, 3, 0.2)             ← 旧関数名（累積引数なし・FALSEと同じ）
=NEGBINOM.DIST(5, 3, 0.2, FALSE)     ← 新関数名（推奨）

旧関数NEGBINOMDISTは引数が3つで、常に確率質量（FALSE相当）を返します。累積確率を求められない点が不便です。新しく数式を書くときはNEGBINOM.DISTを使いましょう。

NEGBINOM.DIST関数のTRUE/FALSEの違い

NEGBINOM.DIST関数の4番目の引数「累積」は、TRUEかFALSEで結果がまったく変わります。この違いを押さえることがポイントです。

FALSE（確率質量関数）――ちょうどn回失敗する確率

FALSEを指定すると確率質量関数（PMF: ちょうどn回失敗する確率）の値を返します。

=NEGBINOM.DIST(5, 3, 0.3, FALSE)

この数式は「成功確率30%の試行で3回成功するまでに、ちょうど5回失敗する確率」を返します。結果は約0.0794（7.9%） です。

TRUE（累積分布関数）――n回以下失敗する確率

TRUEを指定すると累積分布関数（CDF: n回以下失敗する確率の合計）の値を返します。

=NEGBINOM.DIST(5, 3, 0.3, TRUE)

この数式は「成功確率30%の試行で3回成功するまでに、5回以下失敗する確率」を返します。結果は約0.2557（25.6%） です。

つまり0回+1回+2回+3回+4回+5回失敗する確率を合計した値ですね。

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

成功回数3回・成功確率30%の場合で、失敗回数ごとの結果を比べてみましょう。

失敗回数FALSE（ちょうどn回）TRUE（n回以下）
00.0270（2.7%）0.0270（2.7%）
10.0567（5.7%）0.0837（8.4%）
20.0794（7.9%）0.1631（16.3%）
30.0926（9.3%）0.2557（25.6%）
50.0953（9.5%）0.4718（47.2%）
100.0542（5.4%）0.8507（85.1%）

FALSE列は失敗回数が5回前後で最大になり、その後は徐々に減っていきます。TRUE列は失敗回数が増えるほど1に近づきます。

「n回以上失敗する確率」を求めたいときは、=1 - NEGBINOM.DIST(n-1, k, p, TRUE) と書きます。

NEGBINOM.DIST関数の実務活用3パターン

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

採用面接――目標人数を確保するまでの見送り人数を予測

「合格率25%の面接で5人採用するまでに、見送りが20人以下で収まる確率は？」を求めてみましょう。

=NEGBINOM.DIST(20, 5, 0.25, TRUE)

結果は約0.7613（76.1%） です。約76%の確率で見送り20人以内に5人を採用できることがわかります。

面接枠の計画を立てるとき「最大何人と面接すればよいか」を見積もれるのが便利です。見送り20人 + 合格5人 = 合計25人分の面接枠を確保すればよい、という判断ができますね。

品質検査――不合格品が出るまでの合格数を予測

「不良品率5%の製品を検査して、不良品が3個見つかるまでに合格品が50個以上になる確率は？」。ここでは視点を逆にして考えます。

不良品を「成功」、合格品を「失敗」と読み替えます。成功確率=0.05、成功回数=3、失敗回数=50です。

=1 - NEGBINOM.DIST(49, 3, 0.05, TRUE)

結果は約0.6674（66.7%） です。不良品3個が見つかるまでに50個以上の合格品が出る確率は約67%です。

TIP

NEGBINOM.DIST関数では「成功」と「失敗」の定義を自由に決められます。注目したいイベントを「成功」として設定するのがコツです。

営業目標――目標成約までの失注件数をシミュレーション

「成約率15%の営業担当が10件成約するまでに、失注が60件以下で済む確率は？」を計算します。

=NEGBINOM.DIST(60, 10, 0.15, TRUE)

結果は約0.5765（57.7%） です。約58%の確率で失注60件以内に10件の成約を達成できます。

もう少し余裕を持たせて、失注70件以内にしてみましょう。

=NEGBINOM.DIST(70, 10, 0.15, TRUE)

結果は約0.7226（72.3%） です。失注70件 + 成約10件 = 合計80件の商談をこなせば、約72%の確率で目標を達成できるとわかります。

BINOM.DISTとの違いを理解しよう

BINOM.DIST関数と混同しやすいので、違いを整理しておきましょう。

比較項目BINOM.DISTNEGBINOM.DIST
確率分布二項分布負の二項分布
問いの立て方n回試行してk回成功する確率は？k回成功するまでにn回失敗する確率は？
固定するもの試行回数成功回数
数えるもの成功回数失敗回数
第1引数成功回数失敗回数
典型的な場面「100件送ってx件返る確率」「x件返るまでに何件失敗するか」

使い分けのポイントはシンプルです。

  • 試行回数が決まっている → BINOM.DIST
  • 目標の成功回数が決まっている → NEGBINOM.DIST

たとえば「20件商談して5件以上成約する確率」ならBINOM.DISTです。「5件成約するまでに何件失注するか」ならNEGBINOM.DISTです。

確率分布関数ファミリー

Googleスプレッドシートには、確率分布に関連する関数がいくつかあります。目的に応じて使い分けましょう。

関数用途特徴
BINOM.DIST固定回数の試行で成功回数の確率を求める試行回数が決まっている場面に最適
NEGBINOM.DIST目標成功までの失敗回数の確率を求める目標回数が決まっている場面に最適
BINOM.INV累積確率から成功回数を逆算するBINOM.DISTの逆関数
NORM.DIST正規分布にもとづく確率を求める連続データ向け。試行回数が大きいとき近似に使える
COMBIN組み合わせの数を求める分布関数の数学的背景を理解するときに使う

迷ったときの判断基準は「何が固定されているか」です。試行回数が固定ならBINOM.DIST、成功回数が固定ならNEGBINOM.DIST、データが連続値ならNORM.DISTを選んでくださいね。

よくある間違いと注意点

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

成功回数に0を指定して#NUM!エラー

成功回数は1以上の整数でなければなりません。「0回成功するまでの失敗回数」は意味をなさないので、エラーになります。

=NEGBINOM.DIST(5, 0, 0.3, FALSE)   ← #NUM! エラー

成功確率が0〜1の範囲外で#NUM!エラー

成功確率は0から1の間で指定します。30%なら「30」ではなく「0.3」と入力してください。

=NEGBINOM.DIST(5, 3, 30, FALSE)   ← #NUM! エラー（30ではなく0.3）

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

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

第1引数と第2引数を間違える

NEGBINOM.DISTの第1引数は「失敗回数」、第2引数は「成功回数」です。BINOM.DISTの第1引数は「成功回数」なので、順番を混同しないように注意してください。

TRUE/FALSEの指定を間違える

4番目の引数を省略することはできません。TRUEとFALSEでは結果がまったく異なります。「ちょうどn回失敗する確率」を求めたいのにTRUEを指定すると、累積値が返ってきてしまいます。

Excelとの違い

NEGBINOM.DIST関数は、GoogleスプレッドシートとExcelで構文・動作ともにほぼ同じです。

=NEGBINOM.DIST(5, 3, 0.3, TRUE)

この数式はどちらの環境でも同じ結果を返します。Excelにも旧関数名NEGBINOMDISTがありますが、MicrosoftのドキュメントではNEGBINOM.DISTが推奨されています。

ExcelとGoogleスプレッドシートを併用している方は、NEGBINOM.DISTで統一しておけば問題ありませんよ。

まとめ

NEGBINOM.DIST関数は、負の二項分布にもとづいて「目標の成功回数に達するまでの失敗回数の確率」を求める関数です。

  • 4番目の引数にFALSEで「ちょうどn回失敗する確率」が返る
  • TRUEで「n回以下失敗する確率」が返る
  • 採用面接・品質検査・営業目標シミュレーションなど幅広く活用できる
  • BINOM.DIST関数との違いは「試行回数固定 vs 成功回数固定」
  • 成功回数が0以下や、成功確率が0〜1の範囲外だと#NUM!エラー
  • 第1引数が「失敗回数」である点に注意（BINOM.DISTとは逆）
  • 連続データの確率にはNORM.DIST関数が向いている

「目標を達成するまでに何回くらい失敗しそうか」を数字で見積もれると、計画の精度がぐっと上がります。ぜひ実際のデータで試してみてくださいね。

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