AIが出した関数の答えを検算する手順。小さな表で結果を確かめる

検算シートの作り方(所要10分ほど)

1. 式の意図を、まず文章で確認する

AIに式を頼んだら、その式が何をするかを日本語1文で言わせます。自分の意図とずれていれば、検算以前の問題だからです。

先ほどの式が「どの列の、どの条件の行を、どう集計するか」を日本語1文で説明してください。条件に一致しない行、空白の行、数値でないセルをそれぞれどう扱うかも書いてください。

2. 本番から10行ほど抜き出して別シートに置く

新しいシートに、本番の列構成のまま10行前後だけ貼ります。顧客名や金額は架空の値に置き換えます。AIサービスの設定がどうであれ、機密や個人情報は最初から渡さないのが安全です。

例として、担当者「佐藤」の金額合計を求める式を検算します。AIの提案は次のものだったとします。

=SUMIFS(D2:D11,B2:B11,"佐藤")

• 2、B列 担当：佐藤、C列 商品：A01、D列 金額：1000、入れた理由：基本形
• 3、B列 担当：鈴木、C列 商品：A02、D列 金額：2000、入れた理由：対象外の行
• 4、B列 担当：佐藤、C列 商品：A01、D列 金額：1000、入れた理由：重複(2行目と同内容)
• 5、B列 担当：佐藤、C列 商品：A03、D列 金額：(空白)、入れた理由：金額が空
• 6、B列 担当：佐藤、C列 商品：A02、D列 金額：500、入れた理由：通常の値
• 7、B列 担当：佐藤、C列 商品：A04、D列 金額：300、入れた理由：文字列として入力
• 8、B列 担当：「佐藤␣」(末尾に空白)、C列 商品：A01、D列 金額：400、入れた理由：見えないスペース
• 9、B列 担当：(空白)、C列 商品：A05、D列 金額：700、入れた理由：担当が空
• 10、B列 担当：鈴木、C列 商品：A01、D列 金額：0、入れた理由：ゼロ
• 11、B列 担当：佐藤、C列 商品：A02、D列 金額：-200、入れた理由：最終行・負の値

7行目は、セルの表示形式を「文字列」にしてから入力すると作れます。8行目は「佐藤」の後ろに半角スペースを1つ入れます。

3. 式を見る前に、期待値を手で書く

「この表なら答えはいくつになるべきか」を、AIの式の結果を見る前に電卓か暗算で出します。ここでは、7行目の300と8行目の400も佐藤の売上として数えたい、という業務上の意図で考えます。

1000+1000+500+300+400−200=3000

重複の4行目をどう扱うかも、ここで決めます。別の取引なら含め、二重入力なら除外します。これは式ではなく業務の判断なので、人が決める部分です。

4. 判定列を作る

検算シートの右側に、次の欄を作ります。期待値は式ではなく手入力にします。

• 佐藤の合計、G列 期待値(手入力)：3000、H列 式の結果：=SUMIFS(D2:D11,B2:B11,"佐藤")、I列 差：=H2-G2、J列 判定：=IF(I2=0,"OK","要確認")

文字列の300と末尾スペースの400を式が拾わない場合、結果は2300、差は−700になるはずです。ただしこれは未実行の想定なので、実際の値は自分のファイルで確認してください。差が出たら、数字つきでAIに返します。

差が−700です。7行目と8行目が原因かもしれません。原因の候補と直し方を示してください。

小数を含む表では、差が0にならず「要確認」になることがあります。その場合は差を=ROUND(H2-G2,2)のように丸めて判定します。

どの行を確かめるか:テストデータ一覧

式の種類を問わず、次の表から該当する行だけを検算シートに足してください。「期待値の決め方」は人が決める部分です。

• 空白(条件列)、入れるデータ：担当が空の行、見つかりやすい不具合：空白行が意図せず集計や除外される、期待値の決め方：含めるか除くかを先に決める
• 空白(値の列)、入れるデータ：金額が空の行、見つかりやすい不具合：平均や件数の分母がずれる、期待値の決め方：0扱いか無視かを決める
• 重複、入れるデータ：同一内容の行を2つ、見つかりやすい不具合：二重計上、重複除去の漏れ、期待値の決め方：業務ルールで1件か2件かを決める
• 先頭・最終行、入れるデータ：範囲の1行目と最終行に該当データ、見つかりやすい不具合：範囲指定が1行短い、見出し行を含む、期待値の決め方：両端の行を手で数える
• 範囲の外、入れるデータ：範囲の直下にデータを追加、見つかりやすい不具合：追加行が集計されない、期待値の決め方：含めたいなら範囲や表の設定を見直す
• ゼロ・負の値、入れるデータ：0、マイナス、見つかりやすい不具合：条件の「>0」で落ちる、返品が混ざる、期待値の決め方：符号つきで手計算する
• 文字列の数値、入れるデータ：「300」を文字列で入力、見つかりやすい不具合：合計や比較で拾われない、期待値の決め方：数値に直す前提で期待値を出す
• 余分なスペース、入れるデータ：末尾や先頭に空白、見つかりやすい不具合：完全一致で別人扱いになる、期待値の決め方：同一と見なすかを決める
• 全角・半角、入れるデータ：「Ａ01」と「A01」、見つかりやすい不具合：検索でヒットしない、期待値の決め方：同一と見なすかを決める
• 該当なし、入れるデータ：条件に合う行がない状態、見つかりやすい不具合：0が返るのか、エラーかの確認、期待値の決め方：0または「該当なし」と決めておく
• 日付の境界、入れるデータ：基準日ちょうど、月末、月初、見つかりやすい不具合：「以上」「より大きい」の取り違え、期待値の決め方：境界日を含むかを決める
• 小数・端数、入れるデータ：小数第3位までの値、見つかりやすい不具合：表示上の丸めと実際の値のずれ、期待値の決め方：表示でなく実際の値で手計算する

日付やコード列を扱う式では、特に「日付の境界」と「全角・半角」を入れてください。

差の理由が分からないときの確認

数式の評価で途中の値を見る

Microsoftの説明では、対象のセルを1つ選び、「数式」タブの「ワークシート分析」グループから「数式の評価」を開きます。「検証」を選ぶと、下線のある部分の値が順に確認できます(ネストした数式を 1 レベルずつ検証する)。同じページには、次の制限も書かれています。

• IF関数やCHOOSE関数を使う式の一部が評価されず、#N/Aが表示されることがある
• 空白のセル参照は0として表示される
• RAND、OFFSET、NOWなど、再計算のたびに値が変わる関数は、セルの表示と異なる場合がある

空白が0と表示されるため、この機能だけでは「空白」と「0」の違いが見えません。空白の扱いは、先ほどのテストデータで別に確かめてください。

SUMIFSで見落としやすい点

Microsoftの説明で押さえておきたい点は次のとおりです(SUMIFS 関数)。

• 条件範囲は、合計対象範囲と同じ行数・列数にする
• 文字列の条件は引用符で囲む
• ?と*はワイルドカードとして働く。記号そのものを探すなら、前に~を付ける
• 合計対象範囲にTRUEがあると1、FALSEがあると0として評価される
• 引数の順序がSUMIFと違う。合計対象範囲はSUMIFSでは第1引数、SUMIFでは第3引数

AIがSUMIFSをSUMIFに、またはその逆に書き換えた式では、引数の順序が入れ替わっていないかを見てください。範囲の行数がそろっているかも、検算で見つかりやすい点です。

SUMIFについては、255文字より長い文字列を照合すると誤った結果が返される、と説明されています(SUMIF 関数)。長い文字列を条件にするときは、長い値もテストデータに混ぜてください。

テストデータをAIに作らせるときの依頼文

テストデータの案はAIに作らせて構いません。ただし、期待値まで任せると、式と同じ誤解が混ざります。

次の式を検算するための10行のテスト表を作ってください。式:(ここに式を貼る)。列構成:(列名と内容)。次の条件を各1行以上含めてください:空白、重複、先頭行と最終行、ゼロ、負の値、文字列の数値、末尾スペース、該当なし。実在の氏名や金額は使わず、架空の値にしてください。期待値は、各行の寄与が分かる計算式の形で示してください。

返ってきた期待値は、そのまま貼らずに自分で電卓で足し直し、検算シートには手入力します。

本番に戻すときの最終確認

検算シートで「OK」が並んだら、本番の表に式を移します。そのとき、範囲の終わりが本番のデータの最終行に届いているかを確認してください。さらに、本番から条件を1つ選び、フィルターで絞って目視で数え直します。式とは別の方法でも同じ数になれば、安心材料になります。ただし、検算で確かめられるのは試したケースだけです。本番に想定外の値がないかは、別に見る必要があります。

一度でも差が出た式は、直した後に同じ検算シートで再度確かめます。直したことで別のケースが崩れることがあるためです。検算シートは式ごとに保存しておくと、似た式を頼むときの雛形になります。