確認できるExcel数式をAIで作る方法
6行の注文表を使い、AIにSUMIFS数式を尋ね、想定合計を確認し、変更をテストしてから自分のExcelデータへ応用します。
支払済みオンライン注文の金額が必要です。AIアシスタントはExcel数式を提案できますが、「売上を合計して」だけでは二つの判断が抜けています。どの注文を数えるのか、金額列は何を表すのかです。
まず、自分でも答えを計算できる小さな表を使います。数式、説明、対象行をAIに求めます。次に入力を変え、結果が正しく反応するか確かめます。大きなブックへ数式を入れる前に確認できます。
対象に含める規則を明示する
この架空データは1注文1行です。Amountは単価ではなく、注文全体の米ドル金額です。ChannelがOnlineかつStatusがPaidの行だけを数えます。ゼロは記録済み金額ですが、欠落金額なら調査が必要です。
タブ区切りのブロックを空のワークシートのA1へ貼り付けます。見出しがA1:D1、6件の注文が2〜7行目に入ったことを確認してください。すべてA列に入ったら、続ける前にタブ区切りで分割します。
Order Channel Status Amount
O101 Online Paid 120
O102 Store Paid 80
O103 Online Pending 60
O104 Online Paid 90
O105 Online Refunded 40
O106 Online Paid 0
Excelの表示言語が別でも、この練習では標本ラベルを変えないでください。数式の文字条件は画面言語ではなくセル内容を参照します。
AIを使わず対象行を選ぶと、O101、O104、O106です。合計は120 + 90 + 0 = 210です。店舗での支払済み注文、オンラインでも保留中・返金済みの注文は数えません。
合計だけでなく数式を尋ねる
この作業には文章アシスタントを使えます。実際の業務ブックへのアクセスは不要です。小さな架空表とセル位置を渡します。
Excelを使っています。見出しはA1:D1、データはA2:D7です。
AはOrder、BはChannel、CはStatus、Dは米ドル建てAmountです。
1行が1注文で、Dには注文全体の金額が入っています。
ChannelがOnline、かつStatusがPaidの場合だけAmountを合計する、
F2用の数式を書いてください。両方の条件を含め、通常のExcel関数と
英語の関数名を使ってください。各範囲を説明し、対象になる注文IDを
列挙してください。元データは変更しないでください。
[標本表を貼り付け]
この課題の参照数式は次のとおりです。
=SUMIFS(D2:D7,B2:B7,"Online",C2:C7,"Paid")
これは筆者による参照解答で、すべてのアシスタントが同じ応答を返すという主張ではありません。MicrosoftのSUMIFSドキュメントは、複数条件を満たす値の合計方法を定義しています。
順に読むと、D2:D7を合計しますが、B2:B7がOnline、C2:C7がPaidに一致する行だけです。三つの範囲は同じ6行をカバーします。チャネル条件だけなら未払い注文も入ります。
F1にPaid online orders (USD)のような説明ラベルを置き、F2に数式を入れます。結果を見る人が210の意味を分かるよう、ラベルにも規則を残します。
結果と選ばれた行を確認する
F2が210でも、対象IDも比べます。別のデータでは偶然正しい合計になることがあります。ここで寄与する行は2、5、7行目です。
誤った結果も手がかりになります。たとえば310は標本のオンライン金額すべて、120 + 60 + 90 + 40 + 0と一致します。支払状態の条件を省いていないか確認する手がかりですが、すべてのブックで原因を証明するものではありません。
Excelが数式を拒否したら、インストール環境の関数名と引数区切りを確認します。例は英語関数名とカンマを使います。セミコロンを使う環境では引数間に;が必要かもしれません。OnlineとPaidを囲む通常の二重引用符を飾り引用符へ変えないでください。これは構文調整で、対象注文は変わりません。
Microsoftの数式エラーガイドには、エラーと想定外結果の確認方法があります。すぐにIFERROR(...,0)を加えると、理解する前に症状を隠します。
予測できる変更を試す
一つずつ実行し、各テスト後に元データへ戻します。
| 変更 | F2の想定値 | 確認内容 |
|---|---|---|
C4をPendingからPaidへ | 270 | O103が対象になり60を追加 |
| D3を80から800へ | 210 | 店舗注文は除外されたまま |
| D7を0から5へ | 215 | データ最終行が含まれる |
| 元の表へ戻す | 210 | テスト編集を除去済み |
元の数式がたまたま数字を表示するかだけでなく、注文が対象へ入る場合、対象外の大きな変更、範囲の最終行を試しています。
この記事では、数式と変更を独立した表計算エンジンで確認しました。AIアプリのブック編集機能を試したものではありません。応用前に自分のExcel環境でも実行してください。
小さな例が動いてからデータを増やす
この参照例は意図的に7行目で終わります。8行目へ注文を加えても既存数式には入りません。三つすべての範囲を一緒に広げるか、行に追従する参照を持つExcel Tableを使います。範囲が広がったと決めつけず、新しい対象注文で確認します。
自分のデータでは、標本ラベルを実際のセル値へ置き換えます。不完全な金額や一貫しない状態の扱いを決めてから、結果を報告として扱ってください。文字を通貨表示にしても、すべての元値が数値だとは限りません。
AIに修正を頼むときは、失敗した確認を伝えます。「C4をPaidにすると60増えるはずですがF2が変わりません。範囲と二つの条件を確認してください」。元の表を残し、説明がもっともらしいだけでなく、規則、対象行、予測した変更が一致したとき数式を採用します。
参考資料
- Microsoft：SUMIFS関数：引数順、複数条件、範囲要件。
- Microsoft：数式のエラーを検出する：数式エラーと意図しない結果の確認。
