この記事で分かること

  • 実務データに潜む「汚れ」の5つの類型と、対応する関数・機能の対応関係
  • TRIM・CLEAN・SUBSTITUTE・VALUE・ASCを組み合わせた整形の実践手順（検算付き）
  • 区切り位置・重複の削除・フラッシュフィルの正しい使いどころと危うさ
  • 「数値に見える文字列」がVLOOKUPやSUMを狂わせる典型パターンと見分け方

結論：分析作業の8割は「データを揃える」時間である

財務モデリングやアドホック分析の実務では、分析そのものより前の「データを分析可能な形に整える」時間の方が長くなりがちです。基幹システムやERPからのダウンロードデータ、他部署から回ってくるExcelファイルには、余分な空白・セル内改行・全角半角の混在・数値の文字列化・結合セルといった「汚れ」がほぼ必ず含まれています。これらを放置すると、SUMが正しい合計を返さない、VLOOKUPやXLOOKUPが一致するはずの行を見つけられない、といった不具合が発生します。TRIM・CLEAN・SUBSTITUTE・VALUE・ASCなどの関数と、区切り位置・重複の削除・フラッシュフィルという3つの機能を使い分けられれば、こうした汚れの大半は数分で解消できます。まずは汚れの類型を整理し、それぞれの対処法を対応づけて覚えるのが最短ルートです。

データの汚れの類型：何が起きているかを見分ける

データの汚れの類型 → 対処法の対応マップ ①余分な空白（半角・全角の混在） TRIM ／ SUBSTITUTE ②セル内の改行コード（CHAR(10)） CLEAN ③全角文字と半角文字の混在 ASC（全角→半角） ④数値が文字列として入力されている VALUE ⑤結合セル・1セル内の複数項目 結合解除 ／ 区切り位置 ⑥同一データの表記ゆれ・部分抽出 フラッシュフィル
図：データの汚れ6類型と、それぞれに対応する関数・機能の対応マップ。①〜④は関数で、⑤〜⑥は機能（区切り位置・フラッシュフィル）で対処するのが基本方針。

汚れの多くは目視では判別できません。セルが左揃えになっている＝文字列として認識されていることが多く、右揃えの数値と見分けるだけでも整形の第一歩になります。以下、関数による整形と、機能による整形を順に見ていきます。

関数での整形：TRIM・CLEAN・SUBSTITUTE・VALUE・ASC・TEXT

まず押さえるべき6つの関数を整理します。

関数働き注意点
TRIM先頭・末尾の空白を削除し、語間の連続空白を1個に圧縮半角スペース（コード32）専用。全角スペース（コード12288）は除去されない
CLEAN印刷できない制御文字を削除（改行 CHAR(10) を含む）文字を削除するだけで空白に置換しないため、削除後に単語同士が連結される
SUBSTITUTE指定した文字列を別の文字列に置換全角スペースの除去など、TRIMが対応しない文字の処理に使う
VALUE数値に見える文字列を真の数値に変換全角数字や余分な文字が残っていると#VALUE!エラーになる
ASC全角（2バイト）文字を半角（1バイト）文字に変換全角数字や全角英字が混じったデータをVALUEに渡す前の下処理に使う
TEXT数値を書式コード付きの文字列に変換（逆方向の処理）整形後の数値を「1,250,000」のような表示用文字列に戻す、他システムへの再出力時に使う

これらを使い、基幹システムから出力された売上データを整形する設例で検算します。6行の売上データには、勘定科目名の空白・改行の混入と、金額の文字列化（全角数字を含む）が同時に起きているとします。

行勘定科目名（元データ）金額（元データ）型整形後の勘定科目名整形後の金額
1　売上高（A事業）［先頭に全角スペース］1250000数値売上高（A事業）1,250,000
2売上高（B事業）　［末尾に全角スペース］980000［末尾に半角スペースを含む文字列］文字列売上高（B事業）980,000
3売上　高（C事業）［語中に全角スペース］3400000数値売上高（C事業）3,400,000
4売上高（D事業）［セル内改行の後に（速報値）が続く］750000［システム出力由来の文字列］文字列売上高（D事業）（速報値）750,000
5　売上高（E事業）［先頭に半角スペース］1600000数値売上高（E事業）1,600,000
6売上高（F事業）２１０００００［全角数字の文字列］文字列売上高（F事業）2,100,000

勘定科目名（A2セル）の整形には =TRIM(SUBSTITUTE(CLEAN(A2),”　”,””)) を使います。CLEANでセル内改行を削除し、SUBSTITUTEで全角スペースを除去し、TRIMで残った半角スペースの連続を整えます。金額（B2セル）の整形には =VALUE(TRIM(ASC(B2))) を使います。ASCで全角数字を半角に変換し、TRIMで余分な空白を除き、VALUEで文字列を数値に変換します。数値セルに対してこの式を適用しても、Excelは内部で文字列に変換してから処理するため、同じ数値がそのまま返ります。

ここで検算します。整形前の金額列（B2:B7）に対して =SUM(B2:B7) を実行すると、SUMは文字列型のセルを無視するため、数値型の行1・3・5だけが合算されます。

整形前： =SUM(B2:B7) = 1,250,000 ＋ 3,400,000 ＋ 1,600,000 = 6,250,000円（行2・4・6の980,000／750,000／2,100,000が集計から漏れている）

整形後： =SUM(D2:D7) = 1,250,000 ＋ 980,000 ＋ 3,400,000 ＋ 750,000 ＋ 1,600,000 ＋ 2,100,000 = 10,080,000円（6行すべてが数値として合算された正しい合計）

差額の3,830,000円（980,000＋750,000＋2,100,000）がまるごと漏れていた計算になります。実務では、この種の漏れは合計欄の値が「なんとなく小さい」程度にしか見えず、セルの色や配置を確認しない限り気づけません。SUM結果を検算する習慣と、型（文字列か数値か）を確認する習慣をセットで持つことが重要です。

実践演習で手を動かしたい方へ：本記事の整形手順は 教材ページ（/materials/） のExcel演習ファイルで、実際のダウンロードデータを使って練習できます。

機能での整形：区切り位置・重複の削除・フラッシュフィル

関数だけでなく、Excelのメニューから実行する3つの機能も併用します。

区切り位置（データ→区切り位置）は、1セルに「部門コード_勘定科目」のようにまとまって入っている文字列を、区切り文字（カンマ・スペース・アンダースコアなど）や固定長で複数列に分割する機能です。CSVをそのまま貼り付けた際、日付や先頭がゼロの番号が数値・日付として誤変換されることがあるため、ウィザードの最終画面で列のデータ形式を「文字列」に指定できる点も覚えておくと事故を防げます。

重複の削除（データ→重複の削除）は、選択範囲内の完全に一致する行を削除する機能です。実行すると元のデータは復元できないため、必ず作業用シートを複製してから実行するのが実務上の鉄則です。また「重複」の判定はチェックした列の組み合わせ単位で行われるため、意図せず広い範囲・狭い範囲を指定すると、本来残すべき行まで削除してしまう危険があります。

フラッシュフィル（データ→フラッシュフィル、Ctrl+E）は、隣接セルに入力したパターンをExcelが推測し、残りの行に自動反映する機能です。氏名の姓と名の分割・結合、電話番号のハイフン統一など、規則的な文字列操作に強力ですが、パターン推測に基づく一括処理であり、元データが1行変わっても再計算されない「静的な結果」である点が危うさです。関数と違って再現性・追跡可能性がないため、①元データを別列に残す、②結果を目視で全件確認する、③恒常的に繰り返す処理には使わない、という3点を守って使うのが安全です。

「数値に見える文字列」の罠：VLOOKUP・SUMが失敗する典型原因

整形作業でもっとも見落とされがちなのが、セルの左上に表示される緑の三角形（エラーインジケーター）です。これは「数値が文字列として保存されています」という警告で、見た目は数字でもExcel内部では文字列として扱われていることを示します。この状態のセルは、以下の不具合を引き起こします。

SUMで無視される：前章の設例のとおり、SUMやAVERAGEなどの集計関数は文字列型のセルを計算対象から除外します。エラーは出ないため、合計が小さくなっていることに気づきにくいのが厄介な点です。

VLOOKUP・XLOOKUPで一致しない：検索値が数値（例：123）で、検索範囲側が文字列（例：”123″）の場合、Excelは両者を別物として扱い、一致するはずの行が見つからず#N/Aを返します。基幹システムのコード番号（取引先コード・勘定科目コードなど）は先頭ゼロを保持するために文字列で出力されることが多く、Excel側の検索値が数値のままだと典型的にここで失敗します。

対処の基本は、両者の型をVALUE関数で数値に統一するか、逆にTEXT関数で文字列に統一するかのどちらかに揃えることです。どちらに揃えるかは、そのデータを今後計算に使うか（数値に統一）、コードとして扱うか（文字列に統一、先頭ゼロを残したい場合はこちら）で判断します。

Power Queryへの移行タイミング：毎月繰り返すならPQ

ここまでの関数・機能はすべて「1回限りの整形」には有効ですが、同じ整形作業を毎月・毎週繰り返すのであれば、関数を組んだシートを使い回すよりPower Query（データの取得と変換）に移行すべきです。理由は次の3点です。

  • 再現性：一度「クエリ」として整形手順（ステップ）を記録すれば、元データを差し替えて「更新」ボタン一つで同じ整形が再実行される。関数コピーの貼り付けミスが起きない。
  • 可読性：各整形ステップが「適用したステップ」欄に日本語で並ぶため、TRIMやSUBSTITUTEを何重にもネストした複雑な数式より、後任者が処理内容を追いやすい。
  • 負荷：大量行・複数シートの結合を伴う整形では、セル単位の数式よりPower Queryのエンジンの方が高速に処理できる。

目安として、整形対象が数十行程度・一度きりならTRIM等の関数で十分です。数百行以上、あるいは月次で同じ形式のファイルが繰り返し届く場合は、最初からPower Queryでクエリ化しておくと、2回目以降の作業時間がほぼゼロになります。Power Queryの具体的な操作手順は別記事で解説します。

面接での答え方（30秒回答例）

Q：Excelでのデータクレンジングで気をつけていることは？
「データを受け取ったら、まず数値列に緑の三角形やセルの左揃えがないかを確認し、文字列化した数値がないかをチェックします。全角半角の混在にはASC、余分な空白にはTRIMとSUBSTITUTE、セル内改行にはCLEANを使い分けて整形し、整形前後でSUM等の集計値が変わっていないかを必ず検算します。毎月同じ形式のデータが届く業務であれば、都度の関数整形ではなくPower Queryでクエリ化し、更新一つで再現できる状態にしています。」

よくある質問（FAQ）

TRIMを使っても空白が消えないのはなぜですか。

TRIMが除去できるのは半角スペース（文字コード32）のみです。全角スペース（文字コード12288）や、Webからのコピー時に混入しやすい「改行なしスペース」（文字コード160）には効果がないため、SUBSTITUTEで該当の空白文字を明示的に除去する必要があります。

フラッシュフィルと関数（LEFT・RIGHT・SUBSTITUTEなど）は、どちらを使うべきですか。

その場限りの1回限りの整形で、元データの変更が今後想定されないならフラッシュフィルが速く簡単です。一方、元データが今後も更新される、他の人が処理内容を検証する必要がある、といった場合は、処理過程が数式として残り再計算もされる関数（または前章のPower Query）を使う方が安全です。

まとめ

実務データの「汚れ」は、余分な空白・セル内改行・全角半角混在・数値の文字列化・結合セルの5〜6類型にほぼ集約されます。それぞれにTRIM・CLEAN・SUBSTITUTE・VALUE・ASCという対応する関数があり、区切り位置・重複の削除・フラッシュフィルという機能で補完します。整形後は必ずSUMなどで検算し、集計漏れがないかを確認する習慣をつけましょう。1回限りの整形は関数で、毎月繰り返す整形はPower Queryで、という使い分けの基準を持っておくと、実務での判断が早くなります。

次に読む

出典・参考（2026-08-01確認）

※本記事は教育目的の一般的な解説であり、法務・税務・投資助言ではありません。設例は理解のための仮設例です。