こんにちは、QAエンジニアの ノナカ です！
QAのプロジェクトにおいて「今のテストがどれくらい進んでいるのか」または「どれくらい遅れているのか」といった進捗を測定して状況を把握することは、テストの実行を管理する際にとても重要なタスクとなります。本記事ではそういった進捗管理業務において、スプレッドシートに設計したテスト項目書を想定し、テストの「結果」および「実施日」を 、「1.手動」「2.関数」「3.Google Apps Scripts（以下GAS）」の3つパターンで取得する方法をご紹介します。様々なプロジェクトやQA業務、またはGASの使い方などに少しでもお役立てできれば幸いです！
事前準備
「テスト項目書」シートの作成
まずはじめに、今回の取得対象となる「テスト項目書」を準備しましょう。スプレッドシートにて、簡易的なものを下記に用意しました。今回取得したい部分は、赤枠の「結果」列及び「実施日」列となります。※結果はシンプルに「OK」「NG」のみとし、実施期間は「2024/4/1」～「2024/4/5」の5日間と設定しています。
「進捗管理」シートの作成
続いて、「テスト項目書」シートとは別に１日ごとのテストの進捗を管理できるような「進捗管理」シートを準備します。こちらも簡易的なものを下記に用意しました。
「テスト項目書」シートの「結果」列及び「実施日」列に対応するものが、上図赤枠の各結果行と各日付列となります。上節の「テスト項目書」シートを例に挙げると、F列：「2024/4/1」の8行目「OK」が3件、G列「2024/4/2」の8行目「OK」が1件…というように、各テスト項目に対して「いつ」「結果がどうなったか」をそれぞれ入力する必要があります。
ポイント! – 消化する項目数の予定（予定完了率）について
進捗状況を把握するためには、計画（理想）に対する実績を比較することが必要となります。今回の「進捗管理」シートでは、１日ごとに消化する（テスト結果：OKとなる）項目数を事前に計画して算出しています。算出方法としては、「全体の項目数からテスト全期間を割って平均値を算出する方法」や「テスト項目の消化に掛かる工数・ウェイトや優先度合等から日毎に消化できる項目数を厳密に算出する方法」等があります。今回は前者のパターンで算出しています。
- 「予定」の算出
今回だと「テスト対象の項目数：10項目」に対して「テスト全期間：5日」なので、「1日あたり2項目の消化」が必要となる計算です。（10 / 5 = 2） - 「実績」の算出
実績（テスト結果：OK）の算出方法は、「OK」の値をそのまま関数を使って参照するだけでよいでしょう。
※今回はテスト結果が「OK」しか設定していませんが、例えば「NG→OK（不具合が解消されたケース）」といったテスト結果もある場合は取得範囲が変わるため注意が必要です。 - 「◆予定完了率」の算出
「予定完了率」は「その日まで（から）に消化する予定の項目数」に対して「テスト対象の項目数」を割った値を算出する必要があります。ただし、関数の場合は先頭のセルと先頭以降のセルで算出方法が変わるため、下記の通り書き方を少し工夫する必要があります。- 先頭のセル（F5セル）のサンプル関数
=F3/$E3
※「先頭＝テスト初日」の場合、その日「から」消化する予定の項目数となるため、「F3 ＝ 2024/4/1 の予定項目数」を算出する必要があります。 - 先頭以降のセル（Ｇ5セル）のサンプル関数
=SUM($F3:G3)/$E3
※「先頭以降＝テスト初日以降」の場合、その日「までに」消化する予定の項目数となるため、「SUM($F3:G3) ＝ 2024/4/1 + 2024/4/2 の予定項目数」を算出する必要があります。
- 先頭のセル（F5セル）のサンプル関数
- 「◆実績完了率」の算出
「実績完了率」でも「予定完了率」と同様の算出方法となります。ただし、参照する行は「予定」の3行目ではなく、「実績」の4行目が対象となります。
補足
今回のテスト項目書は１シートだけですが、例えば複数の項目書を一元で管理したい場合などは、下記のような書き方をすることで全体の進捗を把握することができます。
※「サマリ」エリアの「予定」および「実績」、「項目数」～「NG」は、各項目書からSUM関数で引っ張ってきています。
いかがでしょうか。デイリーごとの進捗を把握したいケースの場合、テスト項目書をその都度参照しながら確認していくよりも、格段に把握しやすくなったのではないでしょうか。
それでは次章より、各テスト項目に入力された「結果」および「日付」を取得し、「進捗管理」シートに反映する方法を 「1.手動」、「2.関数」「3.Google Apps Scripts（以下GAS）」の3パターンを紹介していきます。また、それぞれ「手軽さ（誰でも取得可能そうか）」「取得時間（取得に時間がかからなさそうか）」「精密度（取得する値が正確か）」「汎用性（他のファイル等でも管理できそうか）」を筆者の所感として3段階の「★（多ければ多いほど良！）」で評価していきたいと思います。
パターン1 – 手動で取得する
評価
- 手軽さ ：★★★
- 取得時間 ：★☆☆
- 精密度 ：★☆☆
- 汎用性 ：★☆☆
解説
テスト項目書に入力された各テスト項目の結果・実施日は、当然ながら人力でカウントすることで手動で取得することができます。ただし人力の場合、ヒューマンエラーによるカウントミスや入力ミスが起きてしまったり、項目数が膨大な場合は取得するだけで工数が膨らむといった問題点があります。反対に項目数が極端に少なく、またテスト期間が極端に短いプロジェクトなどでは非常に有効な手段となるでしょう。
パターン2 – 自動で取得する（関数編）
評価
- 手軽さ ：★★☆
- 取得時間 ：★★★
- 精密度 ：★★☆
- 汎用性 ：★☆☆
解説
手動での問題点（カウントや入力のミス、取得に掛かる工数）を回避する方法として、関数を使用する方法が挙げられます。本記事では、「countifs関数」を例に紹介します。
- 凡例
=COUNTIFS('項目シート名'!「結果」列のセル範囲,結果ステータス,'項目シート名'「日付」列のセル範囲,日付)
- 「事前準備」章のF8セルに入力する場合のサンプル関数
=COUNTIFS(INDIRECT("'"&$C$10&"'!G:G"),$D8,INDIRECT("'"&$C$10&"'!H:H"),F$2)
上記関数を「進捗管理」シートの各結果行と各日付列のセル（「進捗管理シートの作成」節の赤枠内）に埋め込むことで、対応する結果を自動で取得することができます。メリットとしては手動に比べて遥かに容易、かつリアルタイムでテスト結果を取得できる点となり、デメリットとしては項目書における項目数やテスト期間が増えれば増えるほど（プロジェクト規模が大きくなるほど）、比例して関数の数が増えてしまうため、スプレッドシートに処理の負荷がかかる（例えば起動元のPCの状態によっては開くまでに時間がかかる 等）といった点が挙げられます。そのため、規模感がそこまで大きくないプロジェクトでない限りは、十分有効な手段といえるでしょう。
パターン3 – 自動で取得する（GAS編）
評価
- 手軽さ ：★☆☆
- 取得時間 ：★★☆
- 精密度 ：★★☆
- 汎用性 ：★★★
解説
関数のように逐一リアルタイムでテスト結果を取得したくない場合や関数を使用するにはプロジェクト（項目書）の規模が大きすぎる場合、または項目書とは別に用意したスプレッドシートで進捗を管理したい場合などは、Google Apps Scripts（GAS）を使用することが有効です。本記事では、簡単な要件定義からコーディングまでを解説していきます。
要件定義
コーディングするにあたって、まずは要件を定義していきましょう。必要な情報は、取得対象である「テスト項目書」シートの「結果」列及び「実施日」列のデータと、取得した後に入力する「進捗管理」シートの「結果」行と「日付」列の位置、および入力条件となります。それらをまとめた処理のフローを下記に掲載しました。
コーディング
それでは、上記でまとめた内容を基に、コーディングしていきましょう。要点を絞って解説していきます。
対象のスプレッドシート・シートの指定
まずはじめに、前提として対象となるスプレッドシートおよびそのシート名を指定していきます。
/**対象のスプレッドシート・シートの取得*/
let activeSheet = SpreadsheetApp.getActiveSpreadsheet(); //現在アクティブなスプレッドシート
let activeSheetName = SpreadsheetApp.getActiveSheet().getName(); //現在アクティブなシート
let progressSheet = activeSheet.getSheetByName(activeSheetName); //「進捗管理」シートの設定
let testSheet = activeSheet.getSheetByName('項目シート'); //「テスト項目書」シートの設定
「progressSheet」を「進捗管理」シート、「testSheet」を「テスト項目書」シートとしてます。今回はテスト項目シート名を直値（’項目シート’）で指定していますが、項目シートが複数ある場合は、「事前準備」章の「進捗管理シートの作成 – 補足」の例にあるように「進捗管理」シートのC列に各項目シート名を記載したうえで、そこから各値を取得する方法などでも対応できます。
「進捗管理」シートの入力位置設定とデータの取得
続いて、「進捗管理」シートに入力するセル位置を設定していきましょう。
/**「進捗管理」シートの入力位置設定とデータの取得*/
let progSheetFirstRow = 8; //「進捗管理」シートの入力セルの先頭行位置
let progSheetResult = 2; //「進捗管理」シートの結果の数（OK/NG）
let progSheetFirstCol = 6; //「進捗管理」シートの入力セルの先頭列位置
let testPeriod = 5; //テスト期間（人日）
let testTerm = progressSheet.getRange(2, progSheetFirstCol, 1, testPeriod).getValues().flat(); //「進捗管理」シートの入力範囲の取得
progressSheet.getRange(progSheetFirstRow, progSheetFirstCol, progSheetResult, testPeriod).setValue(0); //「進捗管理」シートを初期化する
let inputData = progressSheet.getRange(progSheetFirstRow, progSheetFirstCol, progSheetResult, testPeriod).getValues(); //値を代入する変数を定義するこちらも「セルの先頭行位置」などを直値で指定していますが、体裁などによって行・列やセルの位置が頻繫に変わる場合などは、検索キーでセル位置を自動で取得する方法なども有効です。
「テスト項目書」シートの入力位置設定とデータの取得
続いて、同様に「テスト項目書」シートに入力するセル位置を設定していきましょう。
/**「テスト項目書」シートの入力位置設定とデータの取得*/
let datafirstRow = 3; //「テスト項目書」シートの入力セルの先頭行位置
let datafirstColumn = 7; //「テスト項目書」シートの入力セルの先頭列位置
let datalastRow = testSheet.getLastRow(); //「テスト項目書」シートの入力セルの先頭列位置
let testDate = testSheet.getRange(datafirstRow, datafirstColumn, datalastRow, 1).getValues().flat(); //「結果」列を配列に格納する
let dataDate = testSheet.getRange(datafirstRow, datafirstColumn + 1, datalastRow, 1).getValues().flat(); //「実施日」列を配列に格納する取得元となる「テスト項目書」シートに対しても、同じように設定・取得していきます。こちらも直値です。
結果および日付の比較判定（「OK」の例）
「要件定義」で定義したように結果や日付の判定を行う処理を最後に記載すれば完了となります。判定処理は「テスト結果が空欄かどうか」「日付が空欄かどうか」「日付が指定したフォーマットかどうか」をそれぞれ判定したうえで、適切な結果だった場合は最後に「OKか」を判定して対象の日付のOK数をカウントし、最後にその値を「進捗管理」シートに転記する、といった処理にしてみました。
/**結果および日付の比較判定*/
//for文で「テスト項目書」シートの最終行までループで比較判定を行う
for (i = 0; i < datalastRow; i++)
//結果が空欄のケース（何もしない）
if (testDate[i] === "") {
;
//結果が空欄以外＋日付が空欄のケース（何もしない）
} else if (testDate[i] != "" && dataDate[i] == "") {
;
//結果が空欄以外＋日付が指定フォーマット以外のケース（何もしない）
} else if (testDate[i] != "" && dataDate[i] != "") {
let objectType = Object.prototype.toString.call(dataDate[i]);
if (objectType != "[object Date]") {
;
//結果が「OK」かつ日付が指定フォーマットのケース
} else if (testDate[i] === "OK" && objectType == "[object Date]") {
key = Utilities.formatDate(dataDate[i], 'JST', 'yyyy/MM/dd');
for (j = 0; j < testPeriod; j++) {
result = Utilities.formatDate(testTerm[j], 'JST', 'yyyy/MM/dd')
keySearch = result.indexOf(key)
if (keySearch > -1) {
inputData[0][j]++;
break;
} else {
;
}
};
//判定結果を「進捗管理」シートに入力する
progressSheet.getRange(progSheetFirstRow, progSheetFirstCol, progSheetResult, testPeriod).setValues(inputData);今回は「OK」「NG」の２パターンの結果のみしか定義していませんが、「BK（Blocking）」や「保留」、「NT（No Test ）」などの結果ステータスがある場合も同じような記載方法で処理することができます。
サンプルコード
サンプルコードも掲載しておきます。折角なので、確認ダイアログや完了ダイアログの表示も併せてコーディングしてみました。
function AggregateResults() {
/**確認DLGの設定*/
//集計実行日の取得
let today = Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd/EE')
//確認DLGの表示
let confirmdlg = Browser.
msgBox("実行確認", today + " 時点のテスト結果を集計しますか？", Browser.Buttons.YES_NO);
if (confirmdlg == "yes") {
;
} else {
return false;
};
/**対象のスプレッドシート・シートの取得*/
let activeSheet = SpreadsheetApp.getActiveSpreadsheet(); //現在アクティブなスプレッドシート
let activeSheetName = SpreadsheetApp.getActiveSheet().getName(); //現在アクティブなシート
let progressSheet = activeSheet.getSheetByName(activeSheetName); //「進捗管理」シートの設定
let testSheet = activeSheet.getSheetByName('項目シート'); //「テスト項目書」シートの設定
/**「進捗管理」シートの入力位置設定とデータの取得*/
let progSheetFirstRow = 8; //「進捗管理」シートの入力セルの先頭行位置
let progSheetResult = 2; //「進捗管理」シートの結果の数（OK/NG）
let progSheetFirstCol = 6; //「進捗管理」シートの入力セルの先頭列位置
let testPeriod = 5; //テスト期間（人日）
let testTerm = progressSheet.getRange(2, progSheetFirstCol, 1, testPeriod).getValues().flat(); //「進捗管理」シートの入力範囲の取得
progressSheet.getRange(progSheetFirstRow, progSheetFirstCol, progSheetResult, testPeriod).setValue(0); //「進捗管理」シートを初期化する
let inputData = progressSheet.getRange(progSheetFirstRow, progSheetFirstCol, progSheetResult, testPeriod).getValues(); //値を代入する変数を定義する
/**「テスト項目書」シートの入力位置設定とデータの取得*/
let datafirstRow = 3; //「テスト項目書」シートの入力セルの先頭行位置
let datafirstColumn = 7; //「テスト項目書」シートの入力セルの先頭列位置
let datalastRow = testSheet.getLastRow(); //「テスト項目書」シートの入力セルの先頭列位置
let testDate = testSheet.getRange(datafirstRow, datafirstColumn, datalastRow, 1).getValues().flat(); //「結果」列を配列に格納する
let dataDate = testSheet.getRange(datafirstRow, datafirstColumn + 1, datalastRow, 1).getValues().flat(); //「実施日」列を配列に格納する
/**結果および日付の比較判定*/
//for文で「テスト項目書」シートの最終行までループで比較判定を行う
for (i = 0; i < datalastRow; i++)
//結果が空欄のケース（何もしない）
if (testDate[i] === "") {
;
//結果が空欄以外＋日付が空欄のケース（何もしない）
} else if (testDate[i] != "" && dataDate[i] == "") {
;
//結果が空欄以外＋日付が指定フォーマット以外のケース（何もしない）
} else if (testDate[i] != "" && dataDate[i] != "") {
let objectType = Object.prototype.toString.call(dataDate[i]);
if (objectType != "[object Date]") {
;
//結果が「OK」かつ日付が指定フォーマットのケース
} else if (testDate[i] === "OK" && objectType == "[object Date]") {
key = Utilities.formatDate(dataDate[i], 'JST', 'yyyy/MM/dd');
for (j = 0; j < testPeriod; j++) {
result = Utilities.formatDate(testTerm[j], 'JST', 'yyyy/MM/dd')
keySearch = result.indexOf(key)
if (keySearch > -1) {
inputData[0][j]++;
break;
} else {
;
}
};
//結果が「NG」かつ日付が指定フォーマットのケース
} else if (testDate[i] === "NG" && objectType == "[object Date]") {
key = Utilities.formatDate(dataDate[i], 'JST', 'yyyy/MM/dd');
for (j = 0; j < testPeriod; j++) {
result = Utilities.formatDate(testTerm[j], 'JST', 'yyyy/MM/dd')
keySearch = result.indexOf(key)
if (keySearch > -1) {
inputData[1][j]++;
break;
} else {
;
}
};
};
};
//判定結果を「進捗管理」シートに入力する
progressSheet.getRange(progSheetFirstRow, progSheetFirstCol, progSheetResult, testPeriod).setValues(inputData);
/**完了DLGの設定*/
Browser.msgBox("実行完了", "集計が完了しました。", Browser.Buttons.YES);
return false;
};応用編 – グラフで表現してみよう！
取得した結果をスプレッドシートのグラフと組み合わせて表現することで、より視覚的に状況を把握することができます。
「進捗管理」シート × バーンアップチャート
本記事でご紹介した方法の場合、「完了率」といった積み上げする測定方式のため、「バーンアップチャート」で表現すると、もっともシンプルに進捗率が可視化できるでしょう。
また、グラフで表現することは、例えば上記の場合は「2024/4/2」までは予定以上の前倒し気味でテストが進行されてそうですが、「2024/4/3」以降は消化速度が停滞気味になっていることが分かります。「進捗管理」エリア上では「2024/4/3」に不具合が2件検出されているため、これがテスト項目の消化の阻害要因になってそうなので、不具合の状況確認が必要だな、必要に応じて開発側に連携をとる必要があるな、といったわざわざ各テスト項目書を確認しに行かなくても、一目で一次的な状況が推量できる といった利点などがあります。このようなスピード感を持った一次的な状況把握から二次的なネクストアクションを円滑に行うことができれば、適切なインシデントの管理やエスカレーション、場合によってはプロジェクト自体を上手くコントロールできる場合があります。
「進捗管理」シート × テスト結果積み上げグラフ × 信頼度成長曲線
さらに応用編としてより実践的に活用するために、「進捗管理」シートに対してテスト結果の「積み上げグラフ」と「信頼度成長曲線」を組み合わせて下図に表現してみました。コチラはより実践的な運用がイメージできるのではないでしょうか。
※本格的なプロジェクトに近づけるために、「進捗管理」シートを少し加工して見せ方を変えています。
いかがでしたしょうか。他にも、プロジェクトや進捗状況に応じて様々な取得対象や表現方法等と組み合わせたりして、色々とカスタマイズしてみてください！
おわりに
本記事では、テスト項目書に入力された「結果」および「実施日」を 「1.手動」、「2.関数」「3.Google Apps Scripts」 の3つパターンで取得することで、進捗状況を測定する方法をご紹介しました。進捗を測定して状況を把握・管理することは、プロジェクトにとってボトルネックとなる問題点を認識できたり、またプロジェクトにおける円滑な進行を助長したりすることができます。
ただし、これらはあくまでも進捗管理における「手段」にしか過ぎません。進捗を管理する「目的」とは、『プロジェクトを問題なく成功に導き、高品質な商品やサービスを提供する』ということを忘れてはいけません。進捗状況を測定することだけに囚われて無駄な工数をかけてしまうといった本末転倒とならないようくれぐれも注意しましょう。またそうならないためにも、日々のテスト管理業務に対して上手くツール等を扱い、よりよいQA業務・品質管理に努めていきましょう！
