ExcelのNEGBINOM.DIST関数の使い方｜負の二項分布で失敗回数を予測する

スポンサーリンク

「採用試験で10人内定するまでに、不合格者は何人くらい出るんだろう？」「不良品3個に達するまでに、良品はどれくらい流れるかな？」。こんな「成功○回までに失敗が何回出るか」を、感覚ではなく確率で答えられたらうれしいですよね。

実はExcelには、まさにそうした「成功回数を固定して、失敗回数の確率を計算する」関数が用意されています。それがNEGBINOM.DIST関数です。

この記事ではExcelのNEGBINOM.DIST関数の使い方を、構文の基本から実務での活用例まで丁寧に解説します。BINOM.DIST関数との使い分けも解説します。旧関数NEGBINOMDIST（読み方: ネグバイノム・ディスト関数）との互換性まで、まるごと整理しますよ。

ExcelのNEGBINOM.DIST関数とは

ExcelのNEGBINOM.DIST関数（読み方: ネグバイノム・ディスト関数）は、負の二項分布にもとづいて確率を返す関数です。

負の二項分布とは、目標の成功回数に到達するまでに失敗が何回起きるかを表す確率分布です。「成功か失敗か」の試行を繰り返す場面で、成功回数を固定して失敗回数の分布を考えるモデルです。「NEGBINOM」は「Negative Binomial（負の二項）」、「DIST」は「Distribution（分布）」の略ですね。

たとえば「成約率25%の営業活動で、5件成約するまでに10件失注する確率」を、たった1つの数式で求められます。

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

  • ちょうどx回失敗する確率を求める（確率質量）
  • x回以下失敗する確率を求める（累積確率）
  • 採用や品質管理での「目標達成までの失敗数」を試算する
  • 営業の成約目標に対して、失注がどの程度発生するかを予測する

NOTE

NEGBINOM.DIST関数はExcel 2010で追加された関数です。Excel 2016・2019・2021・2024、Microsoft 365、Mac版でも同じように使えますよ。

基本構文と4つの引数

ExcelのNEGBINOM.DIST関数の基本構文は次のとおりです。

=NEGBINOM.DIST(失敗回数, 成功回数, 成功確率, 関数形式)

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

引数必須/任意説明
失敗回数（number_f）必須起きる失敗の回数（0以上の整数）
成功回数（number_s）必須達成したい成功の閾値（1以上の整数）
成功確率（probability_s）必須各試行で成功する確率（0〜1）
関数形式（cumulative）必須TRUE: 累積確率／FALSE: 確率質量

ここで注意したいのが引数の順序です。第1引数が失敗回数、第2引数が成功回数になっています。

BINOM.DIST関数では第1引数が成功回数なので、ちょうど逆順になるんです。引数の意味を取り違えると結果が全然違ってしまうので、入力時はしっかり確認しましょうね。

NEGBINOMDIST（旧関数）との関係

ExcelにはNEGBINOM.DISTとは別に、NEGBINOMDIST（ピリオドなし）という旧関数もあります。

NEGBINOMDISTは互換性のために残されている関数で、引数は3つだけです。累積確率を選ぶ引数（cumulative）がないため、確率質量しか計算できません。

新規でファイルを作るときは、必ずNEGBINOM.DIST（ピリオドあり）を使うようにしてくださいね。旧関数との違いは後ほど詳しく解説します。

二項分布との違い――「試行回数固定」vs「成功回数固定」

NEGBINOM.DIST関数の理解で一番つまずきやすいのが、BINOM.DIST関数との違いです。

「どっちも成功と失敗を扱う関数なのに、何が違うの？」と感じる方が多いんですよね。ポイントは何を固定して何を求めるかにあります。

観点BINOM.DIST（二項分布）NEGBINOM.DIST（負の二項分布）
固定する変数試行回数成功回数
求める変数成功回数の確率失敗回数の確率
質問のかたち「100回試して3回成功する確率は？」「3回成功するまでに何回失敗する確率は？」
試行の終わり方決まった回数で打ち切る目標の成功数に達したら打ち切る
主な使いどころ検査・アンケート採用・品質管理・営業目標

たとえば「100個検査して不良品が3個出る確率」はBINOM.DISTの守備範囲です。試行回数（100個）が固定だからですね。

一方で「不良品が3個出るまでに良品が何個流れる確率」はNEGBINOM.DISTの出番です。ゴールが決まっていて、そこに到達するまでの過程を確率で測るイメージですよ。

TIP

「試行回数が決まっているならBINOM.DIST」「成功回数が決まっているならNEGBINOM.DIST」と覚えると、実務での使い分けに迷いません。

TRUE/FALSEで何が変わる？累積か確率質量かを選ぼう

NEGBINOM.DIST関数の第4引数（関数形式）は、TRUEとFALSEで結果の意味が大きく変わります。

ここはBINOM.DIST関数と同じ仕組みなので、シリーズで読んでいる方には馴染みのある考え方ですね。

関数形式意味数学用語答えるのは
TRUE累積分布関数（CDF）累積確率x回以下失敗する確率
FALSE確率質量関数（PMF）確率質量ちょうどx回失敗する確率

累積分布関数（CDF）は0回からx回までの確率を全部足し上げた値です。一方の確率質量関数（PMF）は、ちょうどx回ピッタリの確率だけを返します。

具体例で見てみましょう。Microsoft公式ドキュメントのサンプル値を使います。

=NEGBINOM.DIST(10, 5, 0.25, TRUE)
→ 0.3135141
=NEGBINOM.DIST(10, 5, 0.25, FALSE)
→ 0.0550487

どちらも「成功確率25%の試行で、5回成功するまでに失敗が発生する確率」を計算しています。引数はまったく同じで、違いは第4引数のTRUE/FALSEだけです。

TRUEの結果（約31.35%）は「5回成功するまでに失敗が0〜10回起きる確率」を意味します。FALSEの結果（約5.50%）は「5回成功するまでにちょうど10回失敗する確率」ですね。

実務でよく使うのは累積（TRUE）のほうです。「失敗がN回以内に収まる確率は？」というビジネス上の問いに直結するからですよ。

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

ここからは、NEGBINOM.DIST関数を実務でどう使うかを3パターン紹介します。品質管理・採用・営業の3シーンで具体的に見ていきましょう。

パターン1: 品質管理――不良品が出るまでの良品数を予測する

製造ラインの品質管理では、「不良品が一定数発生するまでに、良品がどれくらい流れるか」を試算したい場面があります。

たとえば良品率95%（不良品率5%）の製造ラインで、不良品3個に達するまでに良品が50個以上続く確率を求めるとします。

ここでの「成功」は不良品の発生、「失敗」は良品（不良品ではない）の発生として読み替えるのがポイントです。

=NEGBINOM.DIST(50, 3, 0.05, TRUE)

第1引数50が失敗（良品）の回数、第2引数3が成功（不良品）の閾値です。第3引数0.05が不良品の発生確率、第4引数TRUEが累積確率の指定ですね。

NOTE

実務で使うときは、「成功」をどちらに割り当てるかをチーム内で揃えておきましょう。文脈によって入れ替わる点に注意ですよ。

パターン2: 採用――内定者を確保するまでの不合格者数を試算する

人事の採用業務でも、NEGBINOM.DIST関数は便利に使えます。

たとえば合格率30%の採用試験で、10人内定するまでに不合格者が20人以上出る確率を見たいとします。

=NEGBINOM.DIST(20, 10, 0.3, TRUE)

ここでは「成功」が内定（合格）、「失敗」が不合格です。第1引数20が不合格者数、第2引数10が確保したい内定者数、第3引数0.3が合格率となります。

採用計画を立てるときに「目標人数を満たすには、面接母集団をどれくらい用意する必要があるか」を逆算する材料にもなりますよ。

パターン3: 営業――成約目標に対する失注件数を見積もる

営業の現場でも、NEGBINOM.DIST関数で目標達成までの失注を試算できます。

たとえば成約率15%の営業活動で、5件成約するまでに失注が30件以内に収まる確率を求めるケースです。

=NEGBINOM.DIST(30, 5, 0.15, TRUE)

第1引数30が失注件数、第2引数5が成約目標、第3引数0.15が成約率の見積もりですね。

コールセンターでも応用が利きます。「N件解決するまでの対応総件数」を予測すれば、シフト人数や稼働時間の見積もりに役立ちますよ。

「n回以上失敗する確率」を求めるテクニック

実務では「失敗がN回未満で済む確率」だけでなく、「N回以上失敗する確率」を知りたい場面もあります。

NEGBINOM.DIST関数自体には「以上」の確率を直接求めるオプションはありません。でも、ちょっとした工夫で計算できますよ。

使うのは「全体の確率は1（=100%）」という性質です。N回以上失敗する確率は、（N-1）回以下に収まる確率を1から引けば求められます。

=1 - NEGBINOM.DIST(n-1, 成功回数, 成功確率, TRUE)

たとえば「成功確率25%の試行で、5回成功するまでに11回以上失敗する確率」を求めるなら次のとおりです。

=1 - NEGBINOM.DIST(10, 5, 0.25, TRUE)
→ 1 - 0.3135141
→ 0.6864859

10回以下に失敗が収まる確率が約31.35%なので、その裏側、つまり11回以上失敗する確率は約68.65%という計算になりますね。

このテクニックはBINOM.DIST関数やBINOM.DIST.RANGE関数とも共通の考え方です。シリーズで覚えておくと応用が利きますよ。

TIP

「以上」の確率を求めるときは、引数を（n-1）にするのを忘れずに。「ちょうどn回」を含めるかどうかで結果が変わります。

NEGBINOMDIST（旧関数）との違いと互換性

ExcelにはNEGBINOM.DISTとは別に、NEGBINOMDIST（ピリオドなし）という旧関数があります。

「両方ともExcelに残っているけど、どう違うの？」「古いファイルを受け取ったら直すべき？」と気になっている方も多いですよね。ここで整理しておきましょう。

観点NEGBINOM.DISTNEGBINOMDIST（旧）
追加バージョンExcel 2010以降Excel 2010より前から存在
引数の数4つ（cumulativeあり）3つ（cumulativeなし）
累積確率の計算できる（TRUE指定）できない（PMFのみ）
計算精度改善された精度旧来の精度
Microsoftの推奨推奨互換性目的のみ

NEGBINOMDISTは互換性関数（古いファイルとの互換性のために残された関数）です。Microsoftは公式に新関数への移行を推奨しています。「NEGBINOM.DISTが改善された精度を提供し、使用目的をより適切に反映した名称を持つ」と説明しているんです。

将来のExcelバージョンで削除される可能性もあるので、新規作成では必ずNEGBINOM.DISTを使ってくださいね。

旧関数ファイルを受け取ったときの判断ポイント

社内で旧関数NEGBINOMDISTを使ったファイルを受け取ったら、どう対処すればいいでしょうか。

実は、PMF（確率質量）の値だけなら旧関数でも問題ありません。NEGBINOMDIST（10, 5, 0.25）と NEGBINOM.DIST（10, 5, 0.25, FALSE）は同じ結果（0.0550487）を返すからです。

=NEGBINOMDIST(10, 5, 0.25)
→ 0.0550487
=NEGBINOM.DIST(10, 5, 0.25, FALSE)
→ 0.0550487

ただし累積確率を求めたい場合は別です。旧関数には累積引数がないため、計算自体ができません。

旧関数で累積を求めるには、PMFをx=0から順に足し上げる必要があります。これが手間なので、累積を扱うならNEGBINOM.DISTに置き換えるのがおすすめですよ。

WARNING

旧関数NEGBINOMDISTは将来削除される可能性があります。重要な業務ファイルは早めに新関数へ移行しておくと安心です。

よくあるエラーと対処法

NEGBINOM.DIST関数でつまずきやすいエラーを4パターン紹介します。

エラー原因対処法
#VALUE!引数に数値以外が入っている文字列や空白を数値に修正
#NUM!失敗回数が0未満（number_f<0）失敗回数を0以上の整数に修正
#NUM!成功回数が1未満（number_s<1）成功回数を1以上の整数に修正
#NUM!成功確率が0〜1の範囲外確率を0〜1の小数で指定（30%なら0.3）

特に多いのが、成功確率を「30%」のようにパーセント表記で直接書いてしまうミスです。Excelのセル書式が%表示なら自動で0.3に変換されますが、手入力では0〜1の小数で書くのが安全ですよ。

また、第1引数（失敗回数）と第2引数（成功回数）の順番を間違える方も多いです。前述のとおり、BINOM.DISTとは引数の順序が逆になっているので、入力時に表示されるツールチップで確認するクセをつけましょう。

なお、失敗回数や成功回数に小数を入れた場合、Excelは自動で整数に切り捨てて処理します。意図しない結果を避けるため、最初から整数で指定するのが安心ですね。

負の二項分布関連の関数ファミリー早見表

NEGBINOM.DIST関数を理解できたら、関連する確率分布関数とセットで覚えておくと実務の幅が広がります。

関数分布何を求める？主な用途
NEGBINOM.DIST負の二項分布成功N回までの失敗回数の確率採用・品質管理・営業目標
BINOM.DIST二項分布試行N回中の成功回数の確率検査・アンケート
BINOM.DIST.RANGE二項分布成功回数が範囲内に入る確率「○回以上○回以下」の予測
BINOM.INV二項分布の逆関数累積確率がP以上になる最小成功数必要試行数の逆算
POISSON.DISTポアソン分布単位時間あたりの発生回数の確率アクセス数・故障率

ポアソン分布と負の二項分布は、実は近い関係にあります。負の二項分布はPOISSON.DIST関数の上位互換とも呼ばれています。データの分散が平均より大きい（過分散）場合に向いているんです。

二項分布シリーズも合わせて押さえておきましょう。試行数を固定するならBINOM.DIST関数、範囲指定ならBINOM.DIST.RANGE関数です。必要試行数の逆算ならBINOM.INV関数と、ケースに応じて使い分けてくださいね。

TIP

「何を固定するか」を最初に整理すると、どの関数を使うべきかが自然に決まります。試行回数か、成功回数か、確率の閾値か。この問いを習慣にしましょう。

GoogleスプレッドシートとExcelの違い

NEGBINOM.DIST関数はExcelとGoogleスプレッドシートのどちらでも使えますが、いくつか注意点があります。

ExcelとSheetsで同じ引数を入れれば結果は同じです。基本的な使い勝手に大きな差はありません。

ただ、互換性まわりの事情はExcel固有のものがあります。

  • Excel固有事情: 旧関数NEGBINOMDIST（3引数）が今も残っており、Office 2007以前との混在環境では旧関数が使われていることがある
  • バージョン依存: NEGBINOM.DIST自体はExcel 2010以降で追加されたため、それ以前のバージョンのファイルでは旧関数しか使えない
  • 将来の互換性: Microsoftは旧関数の将来削除を示唆しているため、長期保管するExcelファイルはNEGBINOM.DISTへの移行が望ましい

Googleスプレッドシートは元々NEGBINOM.DIST形式で関数を提供しているため、こうした旧関数互換問題はあまり気にする必要がありません。

スプレッドシート側の使い方を詳しく知りたい方は、スプレッドシートのNEGBINOM.DIST関数の記事もあわせてどうぞ。旧関数の解説はスプレッドシートのNEGBINOMDIST関数の記事で扱っていますよ。

まとめ

ExcelのNEGBINOM.DIST関数の使い方を、構文から実務活用までまとめてきました。最後に要点を整理しておきますね。

  • NEGBINOM.DIST関数は負の二項分布の確率を返す関数。「成功N回までに失敗が何回起きるか」を計算できる
  • BINOM.DISTとの違いは『何を固定するか』。試行回数固定ならBINOM.DIST、成功回数固定ならNEGBINOM.DIST
  • 引数は4つ。失敗回数・成功回数・成功確率・関数形式（TRUE/FALSE）の順で指定する
  • TRUEは累積、FALSEは確率質量。実務では累積（TRUE）の出番が多い
  • 「N回以上失敗する確率」は1から引く。=1-NEGBINOM.DIST(n-1, 成功数, 確率, TRUE)で求められる
  • 旧関数NEGBINOMDISTは累積が計算できない。新規作成では必ずNEGBINOM.DISTを使う
  • 品質管理・採用・営業の3シーンで実務に応用できる。目標達成までの失敗予測に強い

確率分布の関数は最初こそ難しく感じますが、「何を固定するか」を意識すれば一気に整理されます。シリーズのBINOM.DIST関数・BINOM.DIST.RANGE関数も合わせてどうぞ。BINOM.INV関数やPOISSON.DIST関数も読むと、確率計算の幅がぐっと広がりますよ。

ぜひ実務のシミュレーションに取り入れてみてくださいね。

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