エクセルの各機能を利用してテスト業務自動化へステップアップ

皆さんこんにちは。　テストエンジニアの新村です。

昔に比べアプリの高性能化、汎用性が高まり、それに伴いテストの量が増えたと実感しております。

工数には限りがあるので出来る限り効率化していくうえで、必要に迫られるのがテスト業務の自動化です。この記事では主にテスト業務を自動化したい皆様向けに、自動化の課題と使用するツール、エクセルを使った自動化機能についてお話しできればと思います。
※なお本記事で扱う自動化機能はGoogleスプレッドシート+GAS等でもほぼ実現可能です。

テスト業務の自動化
- テストプロセスの説明
- テスト業務自動化のメリットとデメリット
テスト業務自動化に使えるエクセル機能
BTSを自動更新 (Lv5)
さいごに

テスト業務の自動化

テストプロセスの説明

テスト業務を自動化する前にまずはテストプロセス（工程）について説明します。

JSTQBによると、テストプロセスは計画から評価終了後の作業までを含めて、「テスト計画」「テストのモニタリングとコントロール」「テスト分析」「テスト設計」「テスト実装」「テスト実行」「テスト完了」の7つに大別されています。

それぞれの内容について簡単に説明しますと、

「テスト計画」「テストのモニタリングとコントロール」では、テストの目的を決めます。実施予定のテスト対象の開発理由、納期などを考慮し、テストで何をすべきかを計画します。

「テスト分析」「テスト設計」では、テスト計画をもとに具体的なテスト手法を作成します。

「分析」でテストの目的に合う実行すべきテストの手法や条件の決定、「設計」では分析結果をもとにテストを設計していきます。

「テスト実装」「テスト実行」では、テスト設計を元にテスト実施時に用いるテストケース、テストデータを作成し、それを実行する作業を行います。

「テスト完了」では、テスト完了の判定と結果報告を、開発者や管理者などに報告します。テスト完了の判定は終了基準に到達しているかどうかによります。テストの成果物を確認とテストの経験をもとに、今後のテストプロセスの改善活動を行います。

以上のテストプロセスの中で、文書がメインの「テスト計画」以外が自動化可能な対象となります。

テスト業務自動化のメリットとデメリット

自動化することのメリットとデメリットを記載します。

自動化するメリット

時間短縮:手動で行う手間を省けます。
エラーレスな処理: 手作業に比べてエラーを最小限に抑えられます。
費用を抑える：自動化した作業分、費用を抑えられます。
データの視覚化: グラフやチャートと連携して使えば、データを視覚的にわかりやすく表示できます。

自動化するデメリット

保守作業の追加：手動とは異なり、対象のシステムに変更があった場合、自動化のプログラムを修正する作業が増えます。
複雑な業務には不向き：対象が複雑なシステムやアプリには自動化には向きません。コードの作成に時間がかかり、自動化がそもそも不可能なケースもあります。
テストと別の費用が発生：通常のテスト業務に加え、自動化に使用するツールの費用、対応した技術者の確保が必要となります。

そこで自動化への第一歩としてエクセルを使ってのテスト業務自動化を紹介してきます。

エクセルを選んだ理由としては、多くの人が知っている使っている認知度とシェア率の高さ、開発の難易度がそれほど高くない点につき、先述したデメリットをある程度カバーしてくれるからです。更にエクセルは連携性に優れており、エクセルで作成したデータを他のシステムやアプリに展開、もしくは他システムとアプリのデータをインポートして利用することが可能です。

表計算ソフトの利用率は85%。 最も利用頻度の高い表計算ソフトはエクセルで表計算ソフト利用者の8割以上がメインで利用。 最も使いやすいと感じる表計算ソフトもエクセルが8割以上の支持を獲得。 表計算ソフト利用者のうち、アドオン機能利用経験者は約5割。

参考：「BtoBサービスの比較メディアUtilly 2022年5月 表計算ソフトの利用状況に関するアンケート調査」

テスト業務自動化に使えるエクセル機能

エクセルのどの機能がどのテスト業務の自動化に役立つかを載せていきます。エクセルを使っている方には今更と思うLv1から、こんな事も出来るよというLv5までテスト業務に使えそうな機能を掲載してみました。少しでも参考になれば幸いです。（※Lvの設定は著者の主観で付けさせていただきました。）

カテゴリ	Level	説明
指定語句で書式変更	Lv1	条件付き書式を使用して指定語句を強調
データ入力制限で語句統一	Lv1	データ入力制限でセルに入力する値を制限
No自動入れ	Lv2	関数を使用してセルに番号を自動付与
関数や計算式の結果に語句を追加	Lv2	ユーザー定義で計算結果のセルに語句をつける
レポート自動作成	Lv3	マクロでテスト結果から必要なデータを抜き出す操作を記録し以降自動化
テスト自動統計	Lv3	テスト結果やテスト項目処理数などの統計を関数で処理し、表で可視化
VBAを使用してのテスト自動統計	Lv4	VBAでテスト項目の中から該当のバグが起きる項目の合計数を抜き出す
ファイルチェック	Lv4	VBAのDirコマンドを使い提出するファイルの存在をチェック
テスト実施を自動化	Lv5	テスト業務自動化ツールSeleniumとVBAを使用してテストを自動化
BTSを自動更新	Lv5	テスト業務自動化ツールSeleniumとVBAを使用してデータを抜き出しBTSを自動更新

指定語句で書式変更 (Lv1)

条件付き書式とは、指定したセルの値や数式の計算結果に対して、条件（ルール）を設定し、その条件を満たしたセルを定めた書式にする機能です。この機能はテスト項目書やテストスケジュール表で役に立ちます。

テスト項目書で例にとると、場合によっては1000以上の項目があるテスト結果から「NG」である結果を強調するために、この条件付き書式で「NG」を指定し目立つ色で変えたり、発見したバグが修正済みで確認不要となったときは「修正済み」を指定し取り消し線や灰色で目立たなくしたり、といったことを自動で行ってくれます。

条件付き書式設定前の画面です。

結果欄にはOKとNGが混在していますが、どこにNGがあるかわかりづらい画面となっております。

条件書式設定で「NG」の時に「濃い赤の文字、明るい赤の背景」に設定しました。

前の画面に比べてNGが目立つようになりました。

データ入力制限で語句統一 (Lv1)

テスト項目書で統計を取る時は同じ語句でなければなりません。例えば「OK」の項目を「〇」「ＯＫ（大文字小文字違い）」「問題なし」などと入れてしまうとそれぞれ別語句とカウントされ、統計が取れなくなります。入力するのは人なのでどうしても書き方には差が出てしまいます。そこで統計に関わる所はセルへの入力を制限するエクセルの機能を使います。指定した値しか設定できないので前述のようなことは無くなります。

下の表でOK、NG、未回答数を計算してますが、E3で「OK」ではなく「〇」としているため、OK数が合っていない現象が発生しています。

現象を解決するために、データの入力規則で入力させたい値のみ指定します。

セルを選択すると指定された語のみ入力可能で、他の語を入れようとするとエラーになります。

これで数が合わなくなる現象は起きなくなります。

No自動入れ (Lv2)

テスト項目書を作る時に作成済みのテスト項目の間に途中に項目を挿入、もしくは既存のテスト項目を削除するとNoがずれて最初から打ち直すことになり、手間となります。そこで以下の関数を使って自動的に番号を割り振れば、テスト項目数を変更しても自動でNoが更新されます。

A列にNoを入れてますが、追加や削除があるとNoをもう一度最初から記入しなければならず面倒です。

方法は色々ありますが、一番簡単なのは関数ROWを使った

=ROW(A1(対象の列の頭))-ROW($A$1(対象の列の頭))+1

です。行を削除しても追加しても既存のNoは自動更新されます。

関数や計算式の結果に語句を追加 (Lv2)

関数や計算式を使うと結果のみのセルとなり見栄えが悪くなります。関数や計算式がセルに入っており、結果も変わってしまうので変更することも出来ません。そこでセルのユーザー定義機能を使うことで結果の文頭や文末に語句を追加することが出来ます。ユーザー定義とはデフォルトの書式ではなく、ユーザーが独自に作成した表示形式のことをいいます。前述のNoや回や件など単位を結果に表示する時に便利です。

合計を下の表のF列に載せていますが、関数COUNTAを使っているので数字だけで見栄えが良くなく、隣のE列に項目名を入れても他のセルの長さに合わせた影響で不格好な表示になっています。

[セルの書式設定]の[ユーザー定義]で数字の前に語句を入れることで、計算結果の数字の前に好きな語句を入れることが出来ます。

レポート自動作成 (Lv3)

毎日の進捗状況やバグを報告するのに毎回毎回最初から記述するのは大変でミスも出てしまいます。そこでマクロで自動化すると効率的です。マクロでテスト項目書から必要なデータを抜き出す操作を記録させてボタン一つで作成することが出来ます。

例えば上記のように上長（青い背景）と顧客先（黄色い背景）に出す内容が異なる時、以下のようにマクロで記憶します。

マクロ作成手順

まず上長用のフォーマットを新規のシートに作成します。
マクロの記録を開始し、項目名（この場合は総項目数、実施数、新規バグ数、既存バグ数）を記入、書式を設定、取り出す数値とコメントを関数で指定し、記録を完了。
次に一旦上長用のフォーマットを削除して、同じシートに顧客様用のフォーマットを作成。
マクロの記録を開始し、上長時と同じく項目名（この場合は実施数、残存項目数、新規バグ数、既存バグ数）を記入、書式を設定、取り出す数値とコメントを関数で指定し、記録を完了。
それぞれの記録したマクロを呼び出すボタンを最後に作成することで、今後はボタン一つでレポートが作成できます。

テスト自動統計 (Lv3)

テスト項目書で必要なのは項目の結果を記載するだけではなく、進捗状況を把握しておく必要があります。テスト項目数が膨大になると結果を数えるのは手動では骨が折れてしまいます。そこで、関数や表を使います。テスト結果や日々のテスト項目処理数など様々な統計を関数で処理でき、表を使うことで進捗状況を可視化できます。

項目数が増えたり、シートが複数にまたがったりすると進捗を確認するのが大変になります。

進捗というシートを別に作り、テスト結果を集計した数字を利用して関数や計算、表やグラフで進捗状況を表示するとわかりやすくなります。

VBAを使用してのテスト自動統計 (Lv4)

Lv3テスト自動集計より細かい設定ができます。例えば複数のシートのテスト項目の中から該当のバグが起きる項目の合計数を抜き出すなど、関数やマクロでは難しい場合はVBAを使用して統計を取ります。

VBAの起動方法

エクセルの開発タブをクリック
開発タブが表示されていない場合は、
[ファイル] タブ、[オプション]、[リボンのユーザー設定] の順に移動します。
[リボンのユーザー設定] および [メインタブ] の下の [開発] チェックボックスをオンにします。
Visual Basicアイコンをクリック

■VBAコード

Sub 複数シート検索()
Dim i As Long, j As Long, keyword As String, intPerfectMatch As Integer

'起票レポートにあるレポート番号を取得
MaxRow = Worksheets("起票レポート").Range("A2").SpecialCells(xlLastCell).row
MaxRow = MaxRow - 2
	For j = 21 To MaxRow

    keyword = Worksheets("起票レポート").Cells(j, 2).Value
    intPerfectMatch = 0
    k = Worksheets("起票レポート").Index
    k = k - 1
   '各シートで対象文字があるか検索。
    For i = 1 To k

         Set Rng = Worksheets(i).Cells.Find(keyword)

         If Rng Is Nothing Then
         Else
            intPerfectMatch = intPerfectMatch + 1
            adr = Rng.Address

          Do
            Set Rng = Worksheets(i).Cells.FindNext(After:=Rng)
            If Rng.Address = adr Then
              Exit Do
            Else
              intPerfectMatch = intPerfectMatch + 1
            End If
          Loop
         End If

    Next i

    '対象セルに数を記入。
    'intPerfectMatch = intPerfectMatch - 1
    Worksheets("起票レポート").Cells(j, 8).Value = intPerfectMatch

	Next j
End Sub

上記モジュールは、各テスト項目書のシートを検索し、FindNextメソッドを使ってBTSのバグNoが何個あるかの検索を行い、個数をバグの統計列に自動記入するようにしています。

ファイルチェック (Lv4)

テストの成果物にはテスト項目書だけでなく、エビデンスのファイルを提出する必要があります。エビデンスは時には膨大な量となり、ファイルの存在を目視で調べるのは骨が折れます。VBAでファイル存在チェック機能を作成すれば自動で確認することが出来ます。

■VBAコード

Sub ファイルチェック()
Dim Sheet As Worksheet
Set Sheet = ThisWorkbook.Sheets("メイン") ' シート名を適切に変更
Dim ReslutCell As Range
Dim Cell As Range
Dim Path As String
	' A1からA5までのセルに記載されたパスをチェック
	For Each Cell In Sheet.Range("G3:G8")
	    Path = "C:\\\\" + Cell.Value + ".png"
	    Set ReslutCell = Cell.Offset(0, 1) ' 隣のセル
	
	    If Path <> "" Then
	        If Dir(Path) <> "" Then
	            ReslutCell.Value = "〇"
	        Else
	            ReslutCell.Value = "×"
	        End If
	    Else
	        MsgBox "セル " & Cell.Address & " にパスが記入されていません。"
	    End If
	Next Cell
End Sub

ファイルの存在を調べるにはDirコマンドを使います。上記モジュールはセルに記載されているファイル名が特定のパス（今回はCドライブ直下）にあるかを調べて、隣のセルに結果を記載するようにしてます。

テスト実施を自動化 (Lv5)

テストの結果や設計をサポートするだけではなく、テスト業務自動化ツールを利用すれば自動でテスト実施もエクセルは出来てしまいます。テスト業務自動化ツールはここではブラウザのテスト業務自動化ツールSeleniumを使って説明いたします。

※　テスト対象がSeleniumで動かせるものでないと使えません。事前に確認してください。

Seleniumのインストールとエクセルの準備の仕方

Selenium Basicをインストール
テストで使うブラウザの同バージョンのドライバーをダウンロードし、Selenium Basicをインストールしたフォルダにコピー＆上書き

■VBAコード

Sub sample()
	Dim driver As New WebDriver
	Dim strURL As String
	Dim strWebBroeser As String
	Dim i As Integer
	
	strURL = "<https://www.google.co.jp/>"
	strWebBroeser = "chrome"
	
	driver.Start (strWebBroeser) 'ブラウザ起動
	driver.Get (strURL) 'URL指定
	
	'検索文字列入力
	driver.FindElementByName("q").SendKeys ("EXCEL")
	
	'検索ボタンクリック
	Set searchButton = driver.FindElementByName("btnK")
	searchButton.Click
	
	
	driver.TakeScreenshot(0).Copy
	DoEvents
	Range("A2").PasteSpecial
End Sub

SeleniumをVBA上で動かします。Chromeを立ち上げてテスト対象サイト（今回はGoogle）でエクセルを検索し、結果をスクリーンショットでエクセルに貼り付けるコードとなります。

BTSを自動更新 (Lv5)

バグを起票した時にBTSに登録しますが、テスト項目書にもバグを記載するケースがあります。BTSで対象のバグに更新があった場合、テスト項目書でも更新状況を反映しなければラグが出てしまいテストで混乱するケースも出てしまいます。Seleniumを使えばDBから自動的にデータを取得し、更新分を反映させることが出来ます。

※　BTSがSeleniumで動かせるものでないと使えません。事前に確認してください。

■VBAコード

Sub Test()
Dim driver As New WebDriver
Dim strURL As String
Dim strWebBroeser As String
Dim i As Integer
	TARGET_URL = "<https://weather.yahoo.co.jp/weather/jp/13/4410.html>"
	strWebBroeser = "chrome"
	
	driver.Start (strWebBroeser) 'ブラウザ起動
	driver.Get (strURL) 'URL指定
	
	'Chromeを起動し、指定されたURLのページを表示する
	driver.Get TARGET_URL
	
	'テーブル情報を取得し、エクセルに貼り付ける
	driver.FindElementById("yjw_week").FindElementByTag("table").AsTable.ToExcel ThisWorkbook.Worksheets("DB").Range("A2")
	
	'ドライバをクローズし、Chromeを終了する
	driver.Close
	Set driver = Nothing
End Sub

SeleniumをVBA上で動かします。Chromeを立ち上げてDB対象サイト（今回はYahoo!天気）から対象データ（東京の天気）をコピーする作業です。ここから更に条件を付けくわえれば、更新があったもののみ記入といった自動化も可能になります。

さいごに

これから自動化を目指す人にとって、テスト業務の自動化には高いハードルがあります。

自動化できれば作業が効率化できるが、自動化するための作成・保守の工程と費用が追加される。
自動化に向かない作業もある。

この二点を意識して自動化を進めることが大事です。

自動化できれば作業が効率化できるが、自動化するための作成・保守の工程と費用が追加される。

自動化を推し進めようとしてもプログラミングスキルの不足や自動化の対象が曖昧なまま進めて失敗すれば、自動化に費やした費用や時間も無駄になってしまい、テストを自動化しない方がかえって費用がかからずに済んだということも間々あります。

自動化に向かない作業もある。

複雑なアプリやシステムには向かないことは前述しましたが、他にも「頻繁に画面やシステムが変わる」「テスト実施は一回のみで十分」な対象にも向きません。まずはそのテスト対象が自動化する価値があるか、自動化できそうかを判別する必要があります。

手軽に見える自動化は実は綿密な計画が必要とされます。まずは身近なツールであるエクセルで自動化のメリット・デメリットを体験して、対象のアプリが自動化に適していることが判明できれば、他の自動化専用のアプリやツールへとより複雑な自動化プロセスへステップアップし、更なる自動化への道に進んでいけると思います。

最後まで読んでいただき、ありがとうございました。

Python in Excelを使ってみた

更新：2024.09.17こんにちは。バックエンドエンジニアのカズです。今日はExcelのベータ版に提供されているPython in Excelを紹介します。※正式リリース2024年9月16日（現地時間）米Microsoftより「Python in Excel」の一般公開（GA）が発表されたため記事を更新して...

Sqripts