Excelのデータ整理｜AIで集計する前の空欄・重複・単位チェック

「この表から売上をまとめて」と頼んだのに、合計が合わない。原因がAIの計算だけにあるとは限りません。同じ列に異なる単位が入り、小計行が明細と混ざり、空欄をゼロとして扱うと、集計の前提そのものが変わります。

この記事では、AIへ渡す前の表データの準備を扱います。高度な分析や関数の作成には広げず、担当者が入力表を点検できる状態を目指します。

データ整理の前に、元の表と修正履歴を残す

ここで扱うデータ整理は、AIへ集計を依頼する前に、表の意味と形式を揃える作業です。元のファイルを複製し、修正する行・列と理由を記録します。値を変更する作業と、表示形式を揃える作業は区別してください。

例えば同じ顧客名が2行あっても、注文番号が違えば別取引かもしれません。「同じ名前だから削除」ではなく、どの項目の組み合わせで同一と判断するかを先に決めます。自動修正の前に、例外を人が見られる形にしましょう。

最初に「1行が何を表すか」を決める

1行が注文単位なのか、商品明細単位なのか、顧客単位なのかを説明できるようにします。注文に商品が3つ含まれる場合、3行あるから注文が3件とは限りません。

同様に、注文金額が明細の各行へ繰り返し記載されていれば、その列を足すと二重計上になります。注文ID、明細ID、顧客IDなど、何を識別する列かを整理してから、集計単位を選びます。

入力表を点検する5項目

項目	よくある混在	揃える方針の例
列名	金額の列が「計」「値」など曖昧	税抜金額・円のように意味と単位を明示する
日付	年月日と月日だけの表記が混ざる	年が確認できるものを同じ形式にする。不明な年は補わない
数値	1,000円、千円単位、文字列が混ざる	元の単位を確認してから統一する
空欄	未入力、対象外、実績なしが同じ空欄	意味を確認し、区別する列を設ける
重複	同一注文の別明細と二重登録が混ざる	識別子と業務ルールで判定する

小計・総計・見出しだけの行が明細に混ざっている場合は、分析用の表から分けます。セルの色だけでキャンセルや確定を示しているなら、「状態」という列にも記録します。色の情報が取り込まれるかどうかに依存しない形にするためです。

空欄をゼロに置き換えてはいけない場面

未入力の金額と、売上がなかったことを示す0円は別の情報です。空欄を一律にゼロにすると、未確認のデータまで確定した数値として扱われます。

例えば、仮想の見積管理表に「提出前なので金額が未定」の案件があるとします。ここを0円とすると、平均見積額が下がり、実態と違う比較になります。未定の案件を集計に含めるか、別集計にするかは、集計の目的に合わせて人が決めます。

修正前後を追える、小さな整備例

以下はMakeAIが作成した仮想データです。実在の顧客情報ではありません。

元の記載	確認すること	整備後の扱い
注文A・金額「2」	単位が円か千円か	単位確認までは集計対象外として記録
注文B・金額が空欄	未確定か入力漏れか	金額は空欄を維持し、状態を「未確認」にする
注文C・同じIDが2行	別明細か二重登録か	明細IDを照合し、削除の要否を判断

すぐにきれいな表へ直すより、何が分からないかを残すことが大切です。変更履歴には、元の値、変更後の値、理由、確認者を記録します。

AIには、修正の前に「疑わしい箇所」を列挙させる

いきなり「整えて上書きして」ではなく、確認用の一覧を作らせます。原本から複製した、入力が許可された表で試してください。

この表の集計前点検をしてください。まだ値の変更や行の削除はしないでください。

1行の意味：[例：注文の商品明細1件]
識別する列：[注文ID、明細ID]
集計したい内容：[例：月別の商品別売上]

確認項目：
・列名や単位が不明な箇所
・日付と数値の形式が混在する箇所
・空欄の扱いを人が決める必要がある箇所
・二重登録の可能性がある行
・小計や総計が混ざっている行

出力：行ID／列名／問題の候補／理由／人への確認事項。
確定できない内容を推測で修正しないでください。

整備後は、件数と金額を別々に照合する

整えた表を使う前に、元の件数、除いた件数、残した件数の関係を確かめます。例外として分けた行も、消えたのではなく別に保管されていることを確認してください。

金額の合計は、元の表にある小計を無条件に正解とせず、集計対象と単位が同じかを確認します。AIが作った集計結果だけで照合を終えず、表計算の集計機能など、再現できる計算方法でも確認します。

• 整備の前後で、対象期間は変わっていないか。
• キャンセルや未確定の扱いは一貫しているか。
• 単位の変換と除外の理由を説明できるか。
• 別の担当者が同じルールで再集計できるか。

毎回同じ混在が起きるなら、入力フォームや列のルールを変える余地があります。AIへ渡す直前だけでなく、情報が生まれる場所まで確認すると、次回の整備を減らせます。

明細と小計が混ざった表を、実際に分ける例

ここでは、架空の注文表を使って整備の順序を具体化します。表にあるのは商品明細で、注文そのものの件数とは異なります。金額はすべて円、税抜という前提です。実データへ使う際は、この前提を元の資料と確認してから適用してください。

元の行ID	注文ID	明細ID	金額	行の種類
R01	A100	01	1200	明細
R02	A100	02	800	明細
R03	A100	空欄	2000	小計
R04	A101	01	1500	明細
R05	A101	01	1500	明細。二重登録か未確認
R06	A102	01	空欄	明細。金額未確認

この6行をそのまま足すと、小計と二重登録の候補が混ざります。最初にR03を小計として別表へ分けます。次にR04とR05の原伝票を確認し、同じ注文・同じ明細を二度登録したものかを判断します。IDが似ているというだけでは削除を確定しません。

説明のため、担当者が原伝票を確認し、R05はR04の二重登録だったと確認できた場合を考えます。R05は除外記録へ移し、原本からは消しません。R06は有効な明細として残し、金額の状態を「未確認」にします。この段階の確定金額は1200＋800＋1500＝3500円ですが、R06の金額が未確認なので、対象期間の売上総額が3500円で確定したとは言えません。

件数も別々に数えます。元は6行、整備後の有効明細は4行、小計として分けたものが1行、二重登録として除外したものが1行です。4＋1＋1＝6となり、行の行き先を説明できます。有効な注文IDはA100、A101、A102の3件で、明細4行と注文3件を混同しません。ここに示す数値は仮の表を手計算した例で、顧客の実績ではありません。

整備後の表には、値だけでなく状態も残す

金額が空欄の行へ「未確認」という文字を直接入れると、数値列に文字列が混在します。金額は空欄のまま残し、隣に状態の列を設ける方法があります。何を数値として集計するかと、なぜ数値がないのかを別々に扱えるようにするためです。

元の行ID	注文ID	明細ID	税抜金額_円	金額の状態	採用区分
R01	A100	01	1200	確認済み	明細
R02	A100	02	800	確認済み	明細
R04	A101	01	1500	確認済み	明細
R06	A102	01		未確認	明細

上の例はコピー用のタブ区切りデータです。空欄を含むため、貼り付け後にR06の状態が正しい列へ入っているか確認してください。空欄の位置がずれた表をそのまま集計すると、状態の文字が金額欄に入ることがあります。小さな例でも、列の意味を見ながら確認します。

同じ考え方で、対象外、取消、確認待ちを区別します。「集計に含めるか」という判定列と、なぜ含めないかという理由列を残せば、担当者が後から条件を変えたときにも、削除した行を探し直す必要が減ります。除外は非表示と削除のどちらで行ったかも記録します。

重複の判定には、会社名だけを使わない

重複を探す前に、同じであれば同一の明細と考えられる項目を決めます。この仮の注文表なら、注文IDと明細IDの組み合わせが候補です。同じ顧客名や同じ金額は、別の取引でも起こり得るため、それだけでは二重登録と判断できません。

一致した項目	考えられる状態	確認すること
顧客名だけ	同じ顧客の別注文	注文IDと注文日
注文IDだけ	同じ注文に複数の商品明細	明細IDと商品
注文IDと明細ID	二重登録、訂正版、履歴行のいずれか	版、更新日時、原伝票、履歴の仕様
全ての値	重複の可能性。ただしデータの仕様による	同じ行を残す業務上の理由がないか

更新履歴を一行ずつ持つ表なら、同じIDが複数あること自体は正しい状態です。最新だけを使うのか、各時点の状態を分析するのかで残す行が変わります。AIへ「重複を削除」と頼む前に、履歴を含む表かを説明してください。

単位と日付を変える前に、変換の根拠を決める

金額の「2」が2円なのか2千円なのかは、セルの見た目だけで確定できません。列見出し、帳票の注記、元システムの項目定義を調べます。千円単位だと確認できた場合に限って円へ換算し、変換前の値と単位を履歴へ残します。

日付も同様です。「10/1」に年がなければ、今年の10月1日と決めつけないようにします。対象年度が帳票に明記されているならその根拠を記録し、複数年度が混ざる可能性があるなら確認待ちにします。文字の並びが日付らしく見えることと、正しい日付として確定できることは別です。

変更ID：C-001
元の行ID：R07
対象列：金額
元の値：2
元の単位：千円（元帳票の列見出しで確認）
変更後の値：2000
変更後の単位：円
変更理由：集計対象の金額単位を円へ統一
確認者・確認日：［実際の確認者と日付］

変更の記録は全てのセルに長文を書く必要はありません。同じルールで一括変換したなら、対象行、適用したルール、例外の一覧を保存します。対象外の行へ同じ変換をかけていないか、変換後に再確認する行も決めておきます。

AIへ整備を任せる範囲を、確認後に広げる

最初は、疑わしい行を挙げる段階と、承認したルールで変換する段階を分けます。AIが「重複」と判断した理由を人が確認し、削除するのか別表へ移すのかを決めます。原本を直接更新する必要はありません。

以下は確認が済んだ整備ルールです。
・R03は小計表へ移す。
・R05は原伝票との照合で二重登録と確認したため、除外記録へ移す。
・R06の金額は空欄を維持し、金額の状態を「未確認」とする。
・その他の値は変更しない。

出力は、整備後の明細表、除外・別表へ移した行の一覧、変更履歴。
元の行IDを残し、処理前後で各行の行き先を照合してください。
ルールにない問題を見つけたら、修正せず確認事項へ出してください。

整備後の件数が合っていても、正しい行が残っているとは限りません。元の行IDで追い、同じ行が二つの表へ重複して入ったり、どの表にも入らなかったりしていないかを確認します。金額を照合するときは、確定分と未確認分を分けて表示してください。

次回のデータを受け取る前に、入力側のルールを揃える

毎月同じ列で単位が混ざるなら、列名へ単位を入れ、入力時に選べる値を決めるといった対処を考えます。色だけで表す状態は列へ、備考へ書かれていた取消は状態の選択肢へ移すと、後の処理で意味を読み取りやすくなります。

ただし、既存の帳票を変えると他部署の作業へ影響することがあります。元の入力表を直す担当、分析用に変換する担当、例外を確認する担当を決め、変更する列と理由を共有します。整備ルールの最新版と、そのルールが使われた集計結果を対応させておきましょう。

入力表が揃ったら、AIにExcel関数を作らせ、期待値で確かめる手順へ進めます。整理段階で未確認の行が残っている場合は、関数が正しく動いても結果が業務上の確定値にならないことを、集計する人へ伝えてください。

整備を完了した表に、未確認の残件を添える

分析担当へ渡すときは、整備済みの表だけを送らず、使ったルールと残る未確認事項を添えます。この例なら「金額を確認できた明細は3行、未確認はR06の1行。小計1行と二重登録1行は別表へ保存」と説明できます。受け取った人は、どの範囲なら集計できるかを判断できます。

未確認の金額が後から分かった場合には、該当行を更新した版を作り、前回の集計値へ何が加わったかを記録します。同じファイル名へ無言で上書きすると、前回の報告と今回の報告が違う理由を追いにくくなります。ファイルの版、更新日、変更した行IDを残し、比較する期間や対象が変わっていないかも確認してください。

完了の条件は、空欄が一つもないことではありません。値の意味が揃い、判断できない箇所の所在が分かり、採用・除外の理由を説明できることです。未確認を明示した表は、根拠のないゼロや推定値で埋めた表より、後から正しく更新しやすくなります。