四半期末のP&Lをまとめる直前、CRMからエクスポートした売上データのSUM関数がゼロを返す。エラーメッセージはない。金額も正しく表示されている。それなのに合計が出ない。
別のよくあるシナリオ：アニュアルレポートからコピーした数値にTRIMを適用してもスペースが消えない。VLOOKUPが正しいはずのキーで一致を返さない。原因究明に30分かかって、ようやく「これ、ふつうのスペースじゃない」と気づく。
どちらも財務・オペレーションの実務で繰り返し発生する問題です。この記事では、Google Sheetsで頻発する5種類のデータ品質の問題と、それぞれを確実に修正する数式を解説します。
Google Sheetsのネイティブ機能でできることとできないこと
Googleは2022年にデータクリーンアップメニューを追加しました（「データ」→「データのクリーンアップ」）。空白のトリミングと重複の削除はワンクリックで実行できます。ただし財務モデルで実際に問題になるケースの多くは対応範囲外です。
| 問題 | ネイティブメニュー | 数式 |
|---|---|---|
| 先頭・末尾の空白 | 対応（空白のトリミング） | TRIM() |
| 重複行 | 対応（重複の削除） | UNIQUE() |
| テキスト形式の数値 | 非対応 | VALUE()、-- |
| カテゴリ名・科目名の表記ゆれ | 非対応 | SUBSTITUTE()、PROPER() |
| 制御文字・非印刷文字 | 非対応 | CLEAN() |
| 日付フォーマットの混在 | 非対応 | DATEVALUE() |
問題①：TRIMが効かない空白——CHAR(160)の罠
TRIM()は先頭・末尾の空白と単語間の連続空白を除去します。
=TRIM(A2)
ただしこれはASCIIコード32（通常の半角スペース）にのみ対応しています。IR資料・有報・企業サイトからコピーしたテキストには、ノーブレークスペース（CHAR(160)）が混入していることがよくあります。見た目は通常のスペースと区別がつかず、TRIMを適用してもスペースが残る、VLOOKUPが一致を返さない、という場合の原因の多くはこれです。
対処はSUBSTITUTEと組み合わせます。
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
CHAR(160)を通常のスペースに置換してからTRIMを適用することで、両方を除去できます。
IR資料や有報から数値を取得する場合、TRIM単体で済むかどうかを事前に判断することはほぼ不可能です。これらのソースを使う作業では、最初からこの数式を使うことをおすすめします。
列全体に適用するにはARRAYFORMULAと組み合わせます。
=ARRAYFORMULA(TRIM(SUBSTITUTE(A2:A500, CHAR(160), " ")))
詳細はこちら：Google Sheetsの空白トリミング：メニューと数式の比較
問題②：テキスト形式の数値——SUMがゼロを返す原因
冒頭のシナリオに戻ります。CRMや基幹システムからエクスポートした売上データのSUMがゼロを返す現象、その原因はほぼこれです。数値に見えていても実際にはテキスト形式で保存されているため、SUM・SUMIF・AVERAGEIF・集計関数が値を無視します。セルが左揃えになっていること（数値は通常右揃え）も判断の目安になります。
まずISNUMBERで型を確認します。
=ISNUMBER(A2)
数値に見えてFALSEが返る場合は型の問題です。
財務モデルで特に重要なのは、この確認を入力シートの段階で行うことです。 売上入力シートにテキスト形式の数値が1列あるだけで、それを参照している損益計算書・営業利益計算・キャッシュフロー分析の全シートでSUMがゼロになります。上流の入力シートでこのチェックを仕込んでおくと、下流の集計シートへの波及を事前に防げます。多タブモデルではとくに有効です。
修正の最短ルートは二重否定（ダブルネガティブ）による型変換です。
=--A2
A2を2回否定することで強制的に数値型に変換します。VALUE()でも同じ結果が得られます。
=VALUE(A2)
"¥1,250"のような通貨記号付きの文字列は、記号とカンマを先に除去します。
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"¥",""),",",""))
問題③：重複行
UNIQUE()は元データを変更せずに、重複を除いた配列を別の場所に出力します。
=UNIQUE(A2:D500)
「データ」→「データのクリーンアップ」→「重複を削除」メニューはデータを直接編集します。取引先コード＋取引日のような複合キーで重複判定をカスタマイズしたい場合、メニュー操作の方が設定しやすいことがあります。
取引先ごとに最新レコードのみを残す場合はSORTと組み合わせます。
=UNIQUE(SORT(A2:D500, 3, FALSE))
3列目（例：取引日）を降順でソートしてからUNIQUEを適用することで、取引先ごとに最新のレコードが残ります。
問題④：勘定科目・カテゴリ名の表記ゆれ
複数の担当者が入力するスプレッドシートで必ず発生する問題です。「販管費」「販売管理費」「販売費及び一般管理費」は意味が同じでも、ピボットテーブルでは3つの別カテゴリとして集計されます。部門名（「営業部」vs「営業部門」）や費用科目（「交通費」vs「旅費交通費」）でも同様のことが起きます。
特定の表記ゆれを統一するにはSUBSTITUTEを使います。
=SUBSTITUTE(A2, "販売管理費", "販管費")
複数のパターンをまとめて正規化する場合はネストします。
=SUBSTITUTE(SUBSTITUTE(A2, "販売費及び一般管理費", "販管費"), "販売管理費", "販管費")
英語のベンダー名やカテゴリコードに大文字・小文字のゆれがある場合はLOWER()またはUPPER()で統一します。
=LOWER(A2) → "saas"
=UPPER(A2) → "SAAS"
表記ゆれのパターンが多い場合は、正規化テーブルを別シートに用意してVLOOKUPまたはINDEX/MATCHで参照する構成が保守しやすいです。
問題⑤：特殊文字・非印刷文字
基幹システムからのエクスポート、PDF変換後のデータ、APIを経由したデータには、目に見えない制御文字が混入していることがあります。CLEAN()はASCIIコード0〜31の非印刷文字を除去します。
=CLEAN(A2)
TRIMと組み合わせることで、特殊文字と空白を同時に処理できます。
=TRIM(CLEAN(A2))
インポートデータに改行文字（CHAR(10)）が残存している場合もCLEANで除去できます。セル内に見えない改行があると、VLOOKUP・SUMIF・ピボットテーブルでの参照が一致しない原因になります。
複数の問題が混在する場合
実際のCRMや基幹システムからのエクスポートデータでは、問題が1種類だけで済むことはほとんどありません。取引先リストに先頭の空白・テキスト形式の数値・非印刷文字が同時に混在しているケースが典型です。その場合は関数を連結します。
=PROPER(TRIM(CLEAN(A2)))
通貨記号と空白が混在する数値データには次のように処理します。
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(A2),"¥",""),",","")))
内側から順に：非印刷文字の除去 → 通貨記号の削除 → カンマの削除 → 空白のトリミング → 数値への変換を1つの数式で実行します。
列全体に適用するにはARRAYFORMULAを使います。
=ARRAYFORMULA(TRIM(CLEAN(A2:A500)))
数式では対応しきれないケース
複数フォーマットが混在する電話番号の統一、費用明細からの勘定科目の自動分類——こうした処理は、数式ロジックの範囲を超えます。
ModelMonkeyでは、こうした分類・変換作業を自然言語で処理できます。「電話番号をXX-XXXX-XXXX形式に統一して」「第1四半期の範囲外の日付が含まれる行にフラグを立てて」と説明するだけで、シート上で直接変換を実行します。
よくある質問
Q. TRIMを適用してもスペースが消えません。どう対処すればよいですか？
A. ノーブレークスペース（CHAR(160)）が原因の可能性があります。=TRIM(SUBSTITUTE(A2, CHAR(160), " "))を使ってください。IR資料・有報・企業サイトからコピーしたテキストに特によく混入します。これらのソースを使う場合は最初からこの数式を適用することをおすすめします。
Q. テキスト形式の数値かどうかを素早く確認する方法は？
A. =ISNUMBER(A2)でFALSEが返る場合は型の問題です。セルが左揃えになっている、またはSUMが0を返すことも目安になります。多タブモデルでは入力シートの段階でこのチェックを仕込んでおくと、下流の集計シートへの波及を事前に防げます。
Q. UNIQUE関数とメニュー操作の重複削除、どちらを使うべきですか？
A. 元データを変更せずに別の場所に出力したい場合はUNIQUE()関数が適しています。取引先コード＋取引日のような複合キーで重複判定をカスタマイズしたい場合や、その場で削除したい場合はメニュー操作（「データ」→「データのクリーンアップ」→「重複を削除」）の方が直感的です。
Q. CLEAN・TRIM・VALUEの連結数式はパフォーマンスに影響しますか？
A. 数百行では体感できる差はほぼありません。処理対象をヘルパー列に限定し、元データを変更しない設計にしておくと、シート全体の再計算負荷を抑えられます。
まとめ
Google Sheetsのネイティブ機能（データクリーンアップメニュー）は空白のトリミングと重複の削除に対応しています。それ以外のクレンジング処理にはすべて数式が必要です。
財務モデルへの影響が大きい2つの問題から始めることをおすすめします。
①CHAR(160)の空白——IR資料・外部レポートからのコピーペーストで発生します。TRIMが効かないと気づいたときの最初の対処として覚えておいてください。
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
②テキスト形式の数値——CRMや基幹システムのエクスポートで頻発します。=--A2で修正し、=ISNUMBER(A2)で上流の入力シートを検証する習慣をつけてください。
複数の問題が混在するデータには、この1つの数式でほとんどのケースに対応できます。
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(A2),"¥",""),",","")))