LoginSignup
20
16

More than 5 years have passed since last update.

@madilloar(madi lloar)

GoogleSpreadSheetでバグ収束予測をするには？

Last updated at Posted at 2018-01-06

テストフェーズの中盤で、この先、いつ頃バグが収束するのか？、この先何件バグが発生するのか？将来を予測したい時があると思います。

そんな時は、これまで発生したバグの件数を元に、今後どのように成長曲線が引けるかと予測するとよいです。
こんな感じです。青棒グラフは実際に発生したバグの累積です。赤棒グラフは予測値です。
image.png

今回道具として用いるのは「GoogleSpreadSheet」とそれのアドオンの「Solver」です。
Excelの分析ツールのソルバーでも同じことができるのですが、その辺はググれば出てくるので、ここは敢えて「GoogleSpreadSheet」でやろうかと。

今回使うアドオンの「Solver」ですが、「GoogleSpreadSheet」に登録する際、「Googleドライブファイルの表示と管理」や「外部サービスへの接続」等、いくつか許可を求めてきます。ちょっと気持ち悪いのでサブアカウントで試しました。
下記で試したGoogleSpreadSheetを公開しています。
式を埋めるのが面倒な方は、こちらをコピーして利用してください。
公開リンク:バグ収束予測GoogleSpreadSheet

目次

  1. 「GoogleSpreadSheet」にアドオン「Solver」を登録する。
  2. 代表的な成長曲線3種類を引いてみる。
    2.1.ロジスティック曲線
    2.1.1.データと式の設定
    2.1.2.「Solver」の実行
    2.1.3.グラフ化
    2.2.ゴンペルツ曲線
    2.3.遅れS字曲線
  3. バグ実績件数と一番マッチしている成長曲線で将来を予測する。
  4. 参考文献

　ところで、「Solver」って何に使うの？

今回は、バグの実績件数(下図の青の点)に、近似曲線(下図の点線)を引くときの、最適な傾き、切片等の関数の最適なパラメータを求めるのに「Solver」を使います。

下図の赤の線の誤差が最小になるように(最小二乗法 - Wikipedia)、傾き、切片をうまいこと算出するツールとして、「Solver」を使います。

image.png

1．「GoogleSpreadSheet」にアドオン「Solver」を登録する。

「メニュー」バーの「アドオン」メニューをクリックします。
「メニュー」アイテムから、「アドオンを取得...」をクリックします。

image.png

「アドオン」ダイアログが開きます。
image.png

右上の「アドオンを検索」テキストボックスに、「Solver」と入力して、「Enter」キーを押して検索します。
image.png

検索結果から「Solver」を選び、「＋無料」ボタンをクリックします。
※非線形モデル(non linear)に対応したアドオンを選ぶ必要があります。
image.png

「ログイン」ダイアログが表示されますので、自身のアカウントアイコンのあたりをクリックします。
image.png

「Solver」がアクセス許可を求めてくるので、「許可」ボタンをクリックします。
ちょっと気持ち悪いです。
image.png

アドオンの登録が上手くいくと、このようなツールチップが表示されます。
image.png

2. 代表的な成長曲線3種類を引いてみる。

バグ実績は、20週分採取している前提です。

2.1. ロジスティック曲線

ロジスティック曲線モデルの式＝＞y=a/(1+b*exp(-c*x)

2.1.1. データと式の設定

K1セルに「=H5/(devsq(E7:E26))」を入力。
※バグ実績の最終週(E26セル)までを範囲としています。ご利用の際には、この範囲を適宜変えてください。
image.png

K2セルに「=1-K1*(C26+3+1)/(C26-3-1)」を入力。
※最終週(C26セル)を対象としています。ご利用の際には、ここを適宜変えてください。
image.png

K3セルに「=1-K1*(C26-1)*(C26+3+1)/((C26+1)*(C26-3-1))」を入力。
image.png

K4セルに「=C26*LN(K1)+2*3)」を入力。
image.png

F2セルに「1000」、F3セルに「100」、F4セルに「0.5」を入力。
※このa,b,cの値は、この後「Solver」で最適な値に算出しなおされますので、大体の数字を入力しておきます。今回は20週目のバグ累積件数が917件なので、aは「1000」としました。bはその10分の1の「10」、cは適当の「0.5」。
H5セルに「=sum(H7:H26)」を入力。
image.png

A7セルに実績採取を開始した日、この例では「2018/01/01」を入力し、A8セルからA106セルまでは、一つ上のセルに「+7」した式をずっと埋めます。
B7セルに「=text(A7,"yyyyMM-")&text(WEEKNUM(A7)-WEEKNUM(DATE(YEAR(A7),MONTH(A7),1))+1,"00)」を入力し、A7セルをコピーし、B8セルからB106セルまで式を貼り付けます。
image.png

C7セルに「1」を入力し、C8セルからC106セルまでは、一つ上のセルに「+1」した式をずっと埋めます。
D7:D26セル範囲に、発見したバグ件数を入力します。
E7セルに「=D7」を入力し、E8セルに「=F7+D8」を入力し、E8セルをコピーし、E9セルからE106セルまで貼り付けます(バグ数を週ごとに累積しています)。
F7セルに「=$F$2/(1+$F$3*exp(-$F$4*$C7))」を入力し、F7セルをコピーし、F8セルからF106セルまで式を貼り付けます(ロジスティック曲線の式です)。
image.png

G7セルに「=E7-F7」を入力し、G7セルをコピーし、G8セルからG106セルに貼り付けます。
image.png

H7セルに「=G7^2」を入力し、H7セルをコピーし、H8セルからH106セルに貼り付けます。
image.png

2.1.2. 「Solver」の実行

「メニュー」バーの「アドオン」メニューをクリックします。
「メニュー」アイテムから、「Solver-Start」をクリックします。
image.png

右サイドメニューに「Solver」のパラメータを設定する画面が表示されます。
「Set Objective:」(Excelソルバーで言うところの「目的セル」)に「H5」を入力。
「To:」(Excelソルバーで言うところの「目標値」)を「Min」に。
「By Changing：」(Excelソルバーで言うところの「変化させるセル」)を「F2:F4」を入力。
※H5セルは誤差2乗の合計。この値が最小(Min)になるように「Solver」がうまいことa,b,cの値(F2:F4)を変える。
「Solve」ボタンをクリックすると、計算が始まります。
image.png

計算が終わると次のようなダイアログが右下に表示されます。
image.png

すると、a,b,cの値(F2:F4)が変わっています。
20週目(2018年5月3週)のバグ実績が917件。aの値が約1,148なので、今後1,148件まで成長する見込み。
56週目(2019年1月4週)で予測値が「1,147.582件」で四捨五入して1,148件。
大体この時期にバグが収束すると予測。
image.png

2.1.3.グラフ化

青の棒グラフが、バグの実績値(累積)。赤の棒グラフがロジスティック曲線で引いた予測です。
image.png

2.2. ゴンペルツ曲線

ゴンペルツ曲線モデルの式＝＞y=a*exp(-b*c^x)
ちょっと手抜き。

ロジスティック曲線で作ったシートをコピーして、ゴンペルツ曲線用のシートを作ります。

F7セルに「=$F$2*exp(-$F$3*$F$4^$C7)」を入力し、F7セルをコピーし、F8セルからF115セルまで式を貼り付けます(ゴンペルツ曲線の式です)。
※収束時期がずっとあとなので、A、B、C、Fの列の式を115行までコピーします。
image.png

ロジスティック曲線同様に「Solver」を使った結果、
すると、a,b,cの値(F2:F4)が変わっています。

aの値を見ると1,621.136件。四捨五入で今後1,621件まで成長する見込み。
109週目(2020年1月5週)で予測値が「1,620.521件」で四捨五入して1,621件。
大体この時期にバグが収束すると予測。

ロジスティック曲線と比べて、ゴンペルツ曲線で予測すると、今後発生するバグ数と収束時期がだいぶ違います。
どちらを選んだ方がよいでしょうか？それは3章でやります。

image.png

2.3. 遅れS字曲線

遅れS字曲線モデルの式＝＞y=a*(1-(1+b*x)*exp(-b*x))

ロジスティック曲線で作ったシートをコピーして、遅れS字曲線用のシートを作ります。
遅れS字曲線は、パラメータがa,bの2つなので、Ru、Rh、AICの式を少し変えます。
K2セル「=1-K1*(C26+2+1)/(C26-2-1)」
K3セル「=1-K1*(C26-1)*(C26+2+1)/((C26+1)*(C26-2-1))」
K4セル「=C26*LN(K1)+2*2」
F7セルに「=$F$2*(1-(1+$F$3*$C7)*exp(-$F$3*$C7))」を入力し、F7セルをコピーし、F8セルからF106セルまで式を貼り付けます(遅れS字曲線の式です)。
image.png

ロジスティック曲線同様に「Solver」を使った結果、
すると、a,bの値(F2:F4)が変わっています。

aの値を見ると1,130.473件。四捨五入で今後1,130件まで成長する見込み。
67週目(2019年4月2週)で予測値が「1,129.539件」で四捨五入して1,130件。
大体この時期にバグが収束すると予測。

ロジスティック曲線、ゴンペルツ曲線と比べて、遅れS字曲線で予測すると、こちらも今後発生するバグ数と収束時期がだいぶ違います。
image.png

3. バグ実績件数と一番マッチしている成長曲線で将来を予測する。

難しい話はよくわからないですが、最適なモデルを選択する基準がいくつかあって、今回は、(1)上田選択基準(Ru)、(2)芳賀ほかの選択基準(Rh)、(3)赤池の情報量基準(AIC)で決めます。

3つの曲線のRu、Rhの値が最大で、AICが最小の曲線を選択します。
今回の例では、ゴンペルツ曲線がこれに該当します。

image.png
image.png
image.png

(1)上田選択基準(Ru)=1-(1-R^2)*(n+k+1)/(n-k-1)
(2)芳賀ほかの選択基準(Rh)=1-(1-R^2)*((n-1)*(n+k+1)/(n+1)*(n-k-1))
(3)赤池の情報量基準(AIC)=n*LN((1-R^2))+2*k
(4)(1-R^2)=誤差の平方和/実測値の偏差平方和=H5/(devsq(E7:E26))
n=データ件数
k=パラメータ(未知の係数)の個数

4.参考文献

Excelでできる最適化の実践らくらく読本―ソルバーで自由自在に解く 単行本 – 2003/6
苅田 正雄  (著),‎ 上田 太一郎  (著),‎ 中西 元子  (著)
単行本: 233ページ
出版社: 同友館 (2003/06)
言語: 日本語
ISBN-10: 4496035804
ISBN-13: 978-4496035807

※新版が出ている。

20
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
20
16