Excel

Excelでゴンペルツ曲線を作りたいならこの方法!

しんたろ。

Excelでゴンペルツ曲線を作りたいけど、やり方が分からないという方はいませんか?
この記事では、ExcelのEXP関数とソルバー(いちばん合う値を探してくれる機能)を使って、ゴンペルツ曲線をデータに当てはめる方法を解説しています。

また、ゴンペルツ曲線を作る際の情報収集方法も紹介していますので併せて見てみてください♪

それでは解説に入りましょう!

Excelで工程表を作っている方へ
生産計画アプリの体験デモ

著者がいま作っているアプリを、架空の工場のデータで試せます。
インストールは要りません。

Excelの散布図を使ったゴンペルツ曲線の作成方法

ゴンペルツ曲線を作るのに要るのは、テストの日ごと(または消化したテスト項目の数ごと)に見つかったバグの数です。これを累計にして、日数(またはテスト項目の数)と組にして入力します。

ゴンペルツ曲線は、次の式で表します。
y = K × exp(-b × exp(-c × t))
expは「e(約2.718)の何乗か」を表し、ExcelではEXP関数で計算します。式の文字の意味は次のとおりです。

  • t:テストを始めてからの日数(消化したテスト項目数でもかまいません)
  • y:t日目までに見つかったバグの累計
  • K:最終的に見つかるバグの総数の見込み(tが大きくなるほど、yはKに近づきます)
  • b・c:曲線の形を決める正の数

本や資料によっては、同じ式をy = K × p^(q^t)の形で書いています。p = exp(-b)、q = exp(-c)と置きかえると、同じ式になります。

例えば、テストを15日続けて、日ごとにバグの累計を記録したとします。次の表を見本にして、手順を試してみましょう。

日数(A列)バグの累計(B列)
13
28
316
427
539
653
765
876
986
1094
11100
12105
13108
14111
15114

この見本は、K=120、b=5、c=0.3として式で計算した値を、整数に丸めて作りました。手順のとおりに進めると、この3つに近い値が求まります。

手順8で使うソルバーは、Excelのアドイン(あとから足して使う機能)です。初めに有効にしておきます。
Windows版は「ファイル」タブの「オプション」で「アドイン」を開き、「管理」の一覧で「Excel アドイン」を選んで、その横のボタンを押します。Mac版は「ツール」メニューの「Excel アドイン」を開きます。どちらも「ソルバー アドイン」にチェックを入れて「OK」を押すと、「データ」タブに「ソルバー」が出ます。
Web版のExcelではソルバーを使えないので、デスクトップ版のExcelで行います。

Excelでは、次の順に作ります。

  1. A1に「日数」、B1に「累計」、C1に「曲線」と入れます。A2:B16には、上の表の数を入れます。
  2. H1に=MAX(B2:B16)*1.2を入れます。Kの仮の値です。累計のいちばん大きい値より、大きくしておきます。累計と同じか小さい値だと、次の手順3の計算がエラー(#NUM!)になるためです。
  3. D2に=IF(B2>0,LN(LN($H$1/B2)),"")を入れて、D16までコピーします。曲線を直線の形に直すための値です。累計が0の行は空になり、次の計算に入りません。
  4. H2に=EXP(INTERCEPT(D2:D16,A2:A16))、H3に=-SLOPE(D2:D16,A2:A16)を入れます。SLOPE関数は直線の傾き、INTERCEPT関数は直線の切片(tが0のときの値)を求めます。H2がbの初めの値、H3がcの初めの値です。
  5. H1:H3をコピーし、F1:F3に「値」として貼り付けます。F1がK、F2がb、F3がcです。ソルバーは、このF1:F3を書きかえます。
  6. C2に=$F$1*EXP(-$F$2*EXP(-$F$3*A2))を入れて、C16までコピーします。F1:F3の値で計算した、曲線の値です。
  7. F5に=SUMXMY2(B2:B16,C2:C16)を入れます。実際の累計と曲線の値の差を、2乗して足した値です。小さいほど、曲線がデータに合っています。
  8. 「データ」タブの「ソルバー」を開きます。「目的の設定」にF5を指定し、「最小」を選びます。「変数セルの変更」にはF1:F3を指定します。解決方法は「GRG 非線形」(なめらかな曲線の問題に使う解き方)を選んで、「解決」を押します。結果の画面では「ソルバー解を保持」を選んで「OK」を押します。
  9. A1:C16を選び、「挿入」タブから「散布図」を作ります。次に、グラフの「曲線」を右クリックして「系列グラフの種類の変更」を選び、「散布図 (平滑線)」に変えます。点が実際の累計、線が当てはめた曲線になります。

見本のデータでは、K・b・cは次のように変わります。

Kbc
初めの値(H1:H3)136.83.9180.220
ソルバーのあと(F1:F3)120.24.960.299
見本を作った値12050.3

ずれの合計(F5)は、約231から約1.44まで小さくなります。ソルバーのあとの値は、見本を作った値にほぼ戻ります。小数の最後の桁は、ソルバーの止まり方で少し変わることがあります。

見本のデータ15日分を点で、ソルバーで当てはめたゴンペルツ曲線を線で描いたグラフ。横軸はテストの日数、縦軸はバグの累計。曲線はS字型で、上限K(約120件)の点線に近づいていく。変曲点は約5.4日目・約44件で、累計がK÷e(Kの約37%)になる点。ここで1日に見つかる数がいちばん多くなる
見本のデータに当てはめたゴンペルツ曲線

グラフを散布図にするのは、横軸の日数を数として扱うためです。折れ線グラフは、横軸の値を等間隔の項目として並べます。日数やテスト項目数の間隔がそろっていないと、曲線の形がずれてしまいます。

注意: Excelには、ゴンペルツ曲線を求める関数はありません。グラフの近似曲線(データに合わせて引く線)の種類にも、ゴンペルツはありません。そのため、上の手順のようにEXP関数で式を組み、ソルバーでK・b・cを求めます。

ゴンペルツ曲線の紹介

ゴンペルツ曲線は、はじめはゆっくり増え、途中で速く増え、最後は上限に近づいてほとんど増えなくなる、S字型の曲線です。式はy = K × exp(-b × exp(-c × t))で、指数関数の中にもう1つ指数関数が入った形をしています。ソフトウェアのテストでは、横軸にテストの日数(または消化したテスト項目数)、縦軸に見つかったバグの累計をとって使います。

累計は日がたつにつれて増え続け、上限のKに近づきます。累計のグラフには、山(ピーク)はありません。
山が出るのは、1日あたりに見つかるバグの数のほうです。1日あたりの数は、累計がKの約37%(K÷e。eは約2.718)になる所で、いちばん多くなります。この点を変曲点(曲がり方が変わる点)といいます。見本では、変曲点は=LN(F2)/F3で約5.4日目、そのときの累計は=F1*EXP(-1)で約44件です。

ゴンペルツ曲線は、主にテストの工程で、バグの総数を見積もるのに使われます。見つかったバグの累計の伸び方から、最終的に見つかる総数(K)を見積もります。ただし、見積もりは外れることもあります。IPA(情報処理推進機構)の資料『定量的品質予測のススメ』でも、ゴンペルツモデルが中心だったころの予測は外れることも少なくなかった、と紹介されています。また、ゴンペルツ曲線のように変曲点のあるモデルでは、変曲点を過ぎたあとのデータが要る、とされています。テストの前半に使っても、高い精度は得られないということです。

ゴンペルツ曲線を描くと、テストのどの時期にバグが多く見つかったか(累計の傾きが急な所)が分かります。最終的に見つかるバグの総数の見込み(K)と、残りのバグの見込み(Kから、いまの累計を引いた数)も分かります。ただし、アプリのどの機能にバグが多いかは分かりません。それは、機能ごとにバグの数を数えて調べます。

ゴンペルツ曲線のための情報収集

集めるのは、テストの日ごと(または消化したテスト項目の数ごと)に見つかったバグの数です。日ごとの数を足していき、累計にして記録します。式に入るのは日数と累計だけで、プロジェクトの規模などは入りません。

データを集めるときは、新しい機能の追加や開発のやり方の変更など、途中で起きた変更も記録しておきましょう。

さらに、環境の変化や新しい技術の導入など、バグの発見数に影響を与える可能性のある外部要因も忘れずに考慮することが重要です。

まとめ

要約すると、Excelでゴンペルツ曲線を作るには、テストの日ごと(または消化したテスト項目の数ごと)に見つかったバグの数を集めて、累計にします。

この累計をExcelに入力し、EXP関数で式を組んで、ソルバーでK・b・cを求めましょう。
散布図にデータと曲線を重ねると、曲線の上の点は、その日までに見つかると見込まれるバグの累計を表します。Kが、最終的に見つかるバグの総数の見込みです。

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

ABOUT ME
萩原 慎二
萩原 慎二
OMNIYLUX代表・AI講師
製造の現場で、採算と納期をExcelで管理してきました。いまは工程表のアプリを作り、小さな会社やお店にAIの使い方も教えています。
記事URLをコピーしました