要求仕様と試験項目の対応表をClaude for Excelで作り試験漏れを探す

トレーサビリティマトリクスは、要求仕様の項目と試験項目を結びつけた対応表です。この表があると、試験を1本も当てていない要求が一目で分かります。逆に、どの要求にも結びつかない試験も見つかります。

手作業で作ると、要求が百を超えたあたりで見落としが出ます。Claude for Excelなら、対応を書いたブックの中で、突合の列と色分けを数式で組めます。この記事では、対応を「1要求・1試験の組」に開く設計から、試験漏れの判定、人が確かめる部分までを順に示します。システム名・項番・試験項目は、説明用に作った架空の例です。

洗い出したいのは3種類の穴

対応表で見つけたい穴は3種類あります。どれも「対応の組」を数えれば機械的に拾えます。

3つ目は見落とされがちです。試験一覧の「対応要求」欄にREQ-009と書いてあっても、要求一覧にREQ-009がなければ、その試験は実質、根拠なしです。数える前に、IDの存在を確かめる列が要ります。

題材にする架空の在庫管理システム

要求は8件、試験は9件とします。要求一覧は次のとおりです。

試験一覧は、試験ID、試験項目、「対応要求」欄の3列です。「対応要求」欄は、試験の作成者が手で書いたものとします。

この例には、あらかじめ3つの穴を仕込んであります。REQ-007とREQ-008は試験が1本もありません。TC-008は対応要求が空欄です。TC-009が指すREQ-009は、要求一覧にありません。手順の最後に、この3つが拾えるかを見ます。

手順1: 対応を「1要求・1試験の組」に開いて持つ

試験一覧の「REQ-006, REQ-009」のように、1つのセルに複数のIDが入っていると、数式で数えにくくなります。そこで、対応だけを別シートに、1行1組で持ちます。

シート「対応」
A列(要求ID)  B列(試験ID)
REQ-001       TC-001
REQ-001       TC-002
REQ-002       TC-003
REQ-003       TC-004
REQ-004       TC-005
REQ-005       TC-006
REQ-006       TC-007
REQ-006       TC-009
REQ-009       TC-009

試験が複数の要求にまたがる、または1つの要求に試験が複数つくという多対多の関係も、この形なら行を足すだけで表せます。空欄のTC-008は、組が1行もないことで表れます。

セルに複数IDを詰めた元データがある場合は、この開く作業をClaudeに頼みます。

シート「試験」のC列は、試験が対応する要求IDをカンマ区切りで書いた欄です。
空欄の行もあります。
シート「対応」を新しく作り、A列に要求ID、B列に試験IDを、
1行1組になるよう書き出してください。
C列が空欄の試験は、組を作らずに、あとで一覧にできるよう教えてください。
IDの前後の空白と全角半角の違いは、そろえてから分けてください。

分けた結果は、元の件数と照らします。試験一覧のC列に入っているIDの総数と、対応シートの行数が一致しなければ、どこかで取りこぼしています。

手順2: 判定列を数式で足す

次に、3つの穴に対応する判定列を、各シートに足します。数式はセルに入れる指定にします。値の貼り付けだと、要求や試験を足したときに再計算されません。

次の列を追加してください。数式はセルに入れ、値の貼り付けにしないでください。
シート「要求」(A列が要求ID): 
  C列 試験件数 = シート「対応」のA列で、この要求IDが現れる行数
  D列 判定 = 試験件数が0のとき「試験漏れ」
シート「試験」(A列が試験ID):
  C列 要求件数 = シート「対応」のB列で、この試験IDが現れる行数
  D列 判定 = 要求件数が0のとき「根拠なし」
シート「対応」:
  C列 = A列の要求IDが、シート「要求」のA列にないとき「要求IDなし」
  D列 = B列の試験IDが、シート「試験」のA列にないとき「試験IDなし」

組み上がる数式は、2行目の場合で、例えば次の形になります。

要求!C2:  =COUNTIF(対応!$A:$A,A2)
要求!D2:  =IF(C2=0,"試験漏れ","")
試験!C2:  =COUNTIF(対応!$B:$B,A2)
試験!D2:  =IF(C2=0,"根拠なし","")
対応!C2:  =IF(COUNTIF(要求!$A:$A,A2)=0,"要求IDなし","")
対応!D2:  =IF(COUNTIF(試験!$A:$A,B2)=0,"試験IDなし","")

この例に当てると、要求シートではREQ-007とREQ-008に「試験漏れ」が付きます。試験シートではTC-008に「根拠なし」が付きます。対応シートでは、REQ-009の行に「要求IDなし」が付きます。仕込んだ3つの穴が、そのまま拾われました。

ここで大事なのは、結果を見る前に、自分で予想を持っておくことです。要求一覧を一度目で追い、試験がなさそうな要求を数件、書き出しておきます。数式の結果と合わなければ、数式かデータのどちらかに問題があります。

手順3: 対応のマトリクスを作り、色分けする

穴の一覧が出たら、全体を一枚で見渡せる形にします。要求を縦に、試験を横に並べ、対応があるところに印を付けます。

新しいシート「マトリクス」を作ってください。
A列に「要求」シートの要求IDを縦に並べ、1行目に「試験」シートの試験IDを横に並べます。
交点のセルには、シート「対応」にその要求IDと試験IDの組があるとき「●」、なければ空にしてください。
右端に、行ごとの「●」の数を入れてください。

交点の数式は、例えば次の形です。

=IF(COUNTIFS(対応!$A:$A,$A2,対応!$B:$B,B$1)>0,"●","")

色分けは、条件付き書式として頼みます。

マトリクスの右端の件数が0の行は、行全体を赤の背景にしてください。
下端に試験ごとの「●」の数を足し、0の列は見出しを黄色にしてください。

Claude for Excelは、並べ替えやフィルタ、ピボットテーブルの編集、条件付き書式、データ検証のドロップダウンの作成を、直接頼める操作として挙げています。色分けも、手でセルを塗るのではなく、条件付き書式の規則として頼めます。規則なら、対応シートに行を足したときに色も追従します。

件数が1以上の要求も、人が見る

試験漏れの判定は、件数が0かどうかしか見ません。件数が1以上でも、危うい要求はあります。人が目で見るのは、次の3点です。

• 件数が1だけの要求: REQ-002は試験がTC-003の1本だけです。「5回続けて誤るとロックされる」に対し、4回では掛からないこと、ロックの解除が別の要求でないかは、1本の試験で足りるかを読んで決めます
• 要求に複数の条件が入っている要求: REQ-006は「在庫が減る」と「在庫を超える出庫はエラーにする」の2つを含みます。TC-007は前半、TC-009は後半を確かめているので、試験の中身を見て初めて、2つとも当たっていると分かります
• 試験が正常系ばかりの要求: 対応表は、正常系と異常系の区別を持ちません。REQ-001は、TC-001が正常、TC-002が異常です。試験項目を読まないと、この区別は分かりません

こうした確認は、Claudeに試験項目の文言を読ませて、観点の候補を挙げさせる使い方が合います。

シート「要求」のREQ-006について、シート「対応」でこの要求に結びついている試験項目を一覧にし、
要求の文に含まれる条件のうち、どの試験項目も確かめていなさそうなものがあれば、
その条件と理由を挙げてください。判断のつかないものは「要確認」としてください。

返ってきた指摘は、試験を足すかどうかの判断材料です。Claudeの指摘をそのまま対応表に反映するのではなく、要求の元の文書で確かめてから、組を足すか決めます。

つまずきやすい点

数式が正しくても、元のデータの状態で結果がずれます。

ほかに、次の点が結果を左右します。

• ワイルドカードで数えない: 「REQ-1」を"*REQ-1*"で数えると「REQ-10」「REQ-100」も数えます。1セルに複数IDを詰めたままCOUNTIFを使うと、こうした取り違えが起きます。組に開いて完全一致で数えるのが安全です
• 要求の改版: 要求が削除や統合されたとき、対応シートに古いIDが残ると「要求IDなし」の行になります。この行は、試験の紐づけ直しの作業リストとして使えます
• 粒度の違い: 要求が「ログイン機能」のように大きいと、試験が1本あるだけで漏れに見えなくなります。要求を試験できる単位まで割ってから、対応を取ります
• 要求の追加と試験の追加を別のブックで管理している場合: 突合の前に、最新の一覧を同じブックのシートへ取り込みます。古い版との突合は、穴ではなく差分を拾います

要求一覧と試験一覧の名寄せの考え方は、ABC分析で仕入先を絞る手順にある表記ゆれの扱いと同じです。別の台帳との突合の流れは、運賃請求書を運賃表と突合する手順でも扱っています。

数式の中身を、セルの引用つきで確かめる

組み上がった判定列を信じる前に、Claudeに数式の意味を説明させて検算します。Claude for Excelの回答には、セル単位の引用が付き、クリックで該当のセルに移れるとされています。

シート「要求」のD列で「試験漏れ」になっている行について、
C列の件数が0になっている根拠を、シート「対応」のどの行を見たかとあわせて説明してください。

返った説明で、対応シートに該当のIDがないことを自分で確かめます。全件を見る必要はありません。「試験漏れ」と判定された行をすべてと、件数が1の行を数件、手で検索して確かめれば足ります。

使う前に知っておく制限と取り扱い

Claude for Excelは、Pro、Max、Team、Enterpriseの各プランで使えるアドインです。この作業に関わる条件は次のとおりです。

• マクロとVBAの操作、データテーブルは未対応です。要求や試験が増えるたびに突合を自動で回すマクロを、Claudeに組ませる使い方はできません。数式で組んだ判定列を、そのまま使い続ける形になります
• 最終成果物を人の確認なしに出すこと、監査で使う計算を検証なしに使うことは推奨されていません。試験計画の網羅性を第三者に示すときの資料にする場合は、判定結果を人が確かめた上で出します
• 外部のファイルには、隠れた指示が含まれていることがあります。取引先や委託先から受け取った要求仕様書や試験一覧を、そのまま同じブックに取り込まず、信頼できる自社のブックに必要な列だけ貼って使います
• Claudeが既存のデータを上書きするときは、事前に警告が出ます。元の一覧のコピーで作業すれば、元のデータは守られます
• Excel 2016と2019のボリュームライセンス版、iPad、Androidでは動きません。動くのは、Excel on the web、Microsoft 365のWindows版、Mac版です

要求仕様の項目をほかの規程や基準と対応づける別の例は、ISMSの適用宣言書(SoA)をClaudeで下書きする手順にあります。Excelの読み込みと作成の基本は、Claude Excel読み込みと作成にまとめています。

まとめ

トレーサビリティマトリクスは、対応を「1要求・1試験の組」に開いて持つと、数式で数えるだけで試験漏れと根拠なしの試験を拾えます。Claude for Excelに任せられるのは、複数IDを詰めたセルを組に開く作業、判定列の数式、条件付き書式での色分けです。件数が1の要求、複数の条件を含む要求、正常系しかない要求は、件数では見分けられません。そこは、要求と試験の中身を読める人が判断します。