AIとExcelでデータを整理する
表記ゆれ・空欄・先頭ゼロ
「この表をきれいにして」と頼む前に、何を直し、何を残すかを決めます。AIに変換ルールを説明してもらい、原本と整理後を並べて照合する練習です。
1. 表記ゆれと空欄がある5行の表
A1:D6に次の架空データを入力します。顧客番号は列を文字列にしてから入力してください。空白が見えるよう、店舗名を引用符で囲んで示していますが、入力する文字に引用符は含めません。
| 注文番号(A) | 店舗(B) | 金額(C) | 顧客番号(D) |
|---|---|---|---|
| R001 | "東店" | 1200(数値) | 00123 |
| R002 | " 東店 "(半角空白) | 1,800(文字列) | 00123 |
| R003 | "東店 "(末尾全角空白) | 500(数値) | 00456 |
| R004 | "西店" | 1500(数値) | 00789 |
| R005 | "西店" | 空欄 | 00890 |
R002の金額は文字列として入力し、値にカンマを含めます。数値に表示形式でカンマを付けただけのセルとは区別してください。
2. 人が決める変換ルール
- 店舗名の前後の半角・全角空白を整理し、東店/西店へ統一する。
- 金額のカンマはこの課題では桁区切りなので、R002の文字列を数値1800へ変換する。
- R005の空欄は未確認の金額として残し、確認対象の印を付ける。0で補わない。
- 顧客番号の先頭ゼロを保つ。同じ顧客の別注文は残す。
- 元のA:D列は残し、整理結果をE列以降へ作る。
顧客00123が2回出るのは、注文番号が異なる2件の取引です。顧客番号だけを理由に行を削除すると、売上も消えてしまいます。
3. AIに、編集前の説明を依頼する
A1:D6の練習用データを整理したいです。
A列は注文番号、B列は店舗、C列は金額、D列は文字列の顧客番号です。
店舗名の前後の半角・全角空白を整理します。
C列のカンマ付き文字列は桁区切りなので数値に変換します。
空欄は未確認として残し、0にはしません。
顧客番号の先頭ゼロを保持し、同じ顧客の別注文は削除しません。
元のA:D列は変更せず、E列以降に整理結果と確認欄を作ります。
変換ルール、作成する列、変換できない場合の扱いを先に説明してください。
まだ編集はしないでください。通常チャットなら上のデータも添えて、式や手順の提案を受け取ります。アドインから編集する場合も、説明を確認してから進めます。
店舗名を整理する基準の式
=TRIM(SUBSTITUTE(B2," "," "))全角空白を半角空白に置き換えてからTRIMを使う例です。TRIMは前後の半角空白を取り除き、内部の連続した半角空白も1個へまとめます。店舗名の練習には使えますが、商品名・住所等へ一律に適用する場合は内部の空白を変えてよいか確認します。Microsoft公式のTRIM説明
Web由来の改行しない空白(文字コード160)はTRIMだけでは除去できません。見た目で決め付けず、残った文字を調べて変換対象に追加します。同じ文字に見えるものを全部まとめて消す依頼は避け、対象列とルールを具体化します。
4. 整理後の答えと、残っている情報を照合する
この課題で照合する結果は、注文5件、顧客番号00123の注文2件、未確認金額1件です。確定している金額は東店3500、西店1500、合計5000です。この5000にはR005の未確認金額を含めていません。
- 元の5行と、各注文番号が全部残っている。
- 店舗名が東店/西店へそろっている。
- 確定金額が数値になり、文字列の金額が残っていない。
- 顧客番号は00123のように先頭ゼロが保持されている。
- 未確認金額が0や推測値へ置き換わっていない。
先頭ゼロが既に失われたデータは、番号の桁数や原本を確認してから補います。AIが見た目を5桁に整えただけで、本来の番号が復元できたとは限りません。
繰り返す作業は本編へつなげる
毎月同じ加工をするなら、決めたルールをPower Queryのデータ型やSUBSTITUTEなどの手順に落とし込みます。AIには変換方法の候補と例外を相談し、再利用する処理は自分の環境で確認します。
店舗名の基準式で、前後の半角空白と末尾の全角空白を整理できることは、2026年10月9日にWindows版Excel 16.0(Build 20527)で実計算を確認しました。掲載したデータ・依頼文は当サイトの練習案で、AIによる表全体の編集結果は未検証です。次はAIの結果を検算するへ。AI × Excel活用ラボへ戻る