Google Sheetsで汚いデータをクリーンにする方法
数式と関数、AI自動化を使用してデータクリーニング技法を習得します。汚いスプレッドシートを分析可能なデータセットに数分で変換します。
データクリーニングは、あらゆる分析プロジェクトにおいて最も時間がかかる部分です。多くの場合、全体の60～80%の時間を占めます。重複エントリ、形式の不統一、欠損値、数字であるべきテキストなど、どのような汚いデータに対応していても、データの質が低いと分析結果の信頼性は大きく損なわれます。
この包括的なガイドでは、トップアナリストが使用するプロのデータクリーニング技法を学びます。Google Sheetsの組み込み関数からAI駆動の自動化まで、混沌としたデータセットを分析可能なテーブルに変換します。最後には、データクリーニング時間を数時間から数分に短縮できるようになります。
必要なもの
- Google Sheetsの基本的な使い方の知識
- クリーニングが必要な汚いデータセット（一般的なシナリオを扱います）
- 基本的な数式（TRIM、CLEAN）の理解
ステップバイステップ
データ品質の問題を識別する
データセットをスキャンして、解決する必要がある問題のタイプを見つけます。
- 重複行：データ → 重複を削除で、重複の数を確認します
- 形式の不統一：日付形式の混在、テキストと数字、大文字小文字の問題を探します
- 欠損値：空白セル、「N/A」、「null」、プレースホルダーテキストをスキャンします
- 余分な空白：数式を壊すリーディング/トレーリングスペースをチェックします
- セル結合：ほとんどの分析関数を壊すため、フラグを立てて解除します
- データ型の不一致：テキストとして保存された数字、形式が正しくない日付
Pro Tip
「データ品質チェックリスト」シートを作成して、見つかったすべての問題を列挙します。クリーニング時に1つずつチェックしていくことで、問題の見落としを防ぎます。重複を削除する
分析を歪める重複エントリを見つけて削除します。
- ヘッダーを含むデータ範囲を選択します
- データ → 重複を削除に移動します
- 最初の行に列名が含まれている場合は「データにヘッダー行がある」にチェックを入れます
- 重複をチェックする列を選択します（通常はすべての列、またはキーIDフィールドのみ）
- Google Sheetsが削除した重複の数を表示します
- 代替方法：数式ベースのアプローチとしてUNIQUE()関数を使用します：=UNIQUE(A2:D100)
Pro Tip
重複を削除する前に、バックアップシートを作成します。時には「重複」が実は微妙な違いを持つ正当なエントリであることがあります。テキストをクリーンにする：スペースと特殊文字を削除
数式を壊すホワイトスペースと不要な文字を修正します。
- リーディング/トレーリングスペースを削除：=TRIM(A2)は余分なスペースを削除します
- すべてのスペースを削除：内部スペースも削除する必要がある場合は=SUBSTITUTE(A2," ","")を使用します
- 改行を削除：=CLEAN(A2)は印字不可能な文字を削除します
- 両方を組み合わせる：=TRIM(CLEAN(A2))は徹底的なテキストクリーニングです
- 特定の文字を削除：=SUBSTITUTE(A2,"$","")はドル記号を削除します
- 列全体に適用：ARRAYFORMULAを使用してすべての行を一度にクリーンにします
Pro Tip
シンプルな文字削除にはFind & Replace（Ctrl+H）を使用します。複雑なクリーニングには、数式がより多くの制御を提供し、再利用できます。テキストの形式を標準化する
適切なグループ化と分析のためにテキストを一貫させます。
- 大文字に変換：=UPPER(A2)で一貫した大文字小文字
- 小文字に変換：=LOWER(A2)はメールアドレスやURLの場合
- タイトルケースに変換：=PROPER(A2)は各単語の最初の文字を大文字にします
- 矛盾した名前を修正：Find & Replaceを使用して「Inc」対「Inc.」対「Incorporated」を標準化します
- 名前を抽出：=SPLIT(A2," ")でフルネームを列に分離します
Pro Tip
テキスト数式を適用した後、結果をコピーして「貼り付けの特殊オプション → 値のみ」で数式を削除して置き換えます。データ型と形式を修正する
テキストを数字に変換し、日付形式を修正し、データ型が一致するようにします。
- テキストを数字に変換：=VALUE(A2)または1を掛ける：=A2*1
- 日付形式を修正：形式 → 数字 → 日付、その後、希望の形式を選択します
- テキストから日付を解析：=DATEVALUE("2024-01-15")はテキストを日付に変換します
- 数字をテキストに変換：=TEXT(A2,"0")またはアポストロフィプレフィックスを追加します
- データ型の問題を識別：テキストとして保存された数字は左揃え、真の数字は右揃えです
Pro Tip
条件付き書式を使用してデータ型の問題があるセルをハイライトします。形式 → 条件付き書式 → 「テキストを含む」で問題を見つけることができます。欠損値を処理する
空白セルと不完全なデータの扱い方を決定します。
- 空白セルを見つける：フィルターを使用して空白行を特定します
- 空白をゼロで置き換える：=IF(ISBLANK(A2),0,A2)
- 空白を「不明」で置き換える：=IF(ISBLANK(A2),"不明",A2)
- 上から下にフィルダウン：範囲を選択してCtrl+Dでトップセルから下にフィルします
- 欠損データをフラグ：=IF(ISBLANK(A2),"欠損",A2)で不完全な行を追跡します
- FILTER関数を使用して重要フィールドの欠損行を削除します
Pro Tip
すべての空白をゼロで自動置き換えしないでください。これは平均値を歪める可能性があります。ビジネス上の意味がある場合にのみ空白を埋めます。列を分割して結合する
結合されたデータを分離するか、分割された情報をマージします。
- テキストを列に分割：データ → テキストを列に分割（区切り文字を選択：コンマ、スペースなど）
- 数式で分割：=SPLIT(A2,",")は「姓、名」を2つの列に分離します
- 列を結合：=A2&" "&B2または=CONCATENATE(A2," ",B2)で結合します
- 区切り文字で結合：=TEXTJOIN(", ",TRUE,A2:D2)でコンマで結合します
- 特定の部分を抽出：=LEFT(A2,3)は最初の3文字、=RIGHT(A2,4)は最後の4文字です
Pro Tip
列を分割する際、右側に十分な空の列があることを確認します。分割は既存のデータを上書きします。クリーンなデータの検証と確認
クリーニングが成功し、データが分析可能かどうかを確認します。
- 残存する重複を確認：データ → 重複を削除で「削除」をオフにして数を表示します
- データ型を確認：各列をソートします。数字と日付は論理的にソートされるべきです
- 空白をカウント：=COUNTBLANK(A2:A1000)は必須フィールドの場合ゼロを返すはずです
- アウトライアーをチェック：MIN/MAX関数を使用して不可能な値を見つけます
- テキスト数式をテスト：クリーンなデータが正しく機能することを確認するためにサンプル分析を実行します
Pro Tip
「データ品質ダッシュボード」を作成して、行数、空白、重複、データ型チェックを表示します。クリーニングの各ステップ後に更新します。まとめ
これであなたはプロのデータクリーニングスキルを習得し、汚いスプレッドシートを分析可能なデータセットに変換できます。重複削除から形式の標準化、欠損値の処理まで、最も一般的なデータ品質問題に自信を持って対応できるようになります。
しかし、繰り返しのクリーニングタスクに何時間も費やしているなら、もっと速い方法があります。ModelMonkeyはシンプルなAIプロンプトでデータクリーニングワークフロー全体を自動化できます。数式を記述したりメニューをクリックする代わりに、ModelMonkeyに必要なことを指示するだけです：
- 「重複を削除してすべてのホワイトスペースをトリミング」
- 「すべての日付をYYYY-MM-DD形式に変換して、空白を「不明」に置き換え」
- 「会社名を標準化して、フルネームを名/姓の列に分割」
ModelMonkeyはあなたのデータを分析し、適切なクリーニング関数を適用して、何をしたかを説明します。すべて数秒で完了します。さらに、あなたの好みを学習するので、繰り返しのクリーニングがワンクリックのタスクになります。
データクリーニングの無駄な時間を止めていませんか？ 無料の14日間トライアルを開始して、AIに退屈な作業を処理させます。
よくある質問
Google Sheetsで汚いデータを最速でクリーンにする方法は何ですか？
データ → 重複を削除やデータ → テキストを列に分割などの組み込みツールを使用して、素早く修正します。複雑なクリーニングの場合、TRIM()、CLEAN()、ARRAYFORMULAを組み合わせて列全体を一度に処理します。しかし、ModelMonkeyなどのAIツールはシンプルなプロンプトでプロセス全体を自動化でき、数時間の作業を数秒に短縮できます。
Google Sheetsで重要なデータを削除せずに重複を削除するにはどうしますか？
重複を削除する前に、シートのバックアップコピーを作成します。データ → 重複を削除を使用して、どの列をチェックするかを慎重に選択します。時には重複に見える行が重要なフィールドで異なることがあります。また、=UNIQUE(A2:D100)を使用してクリーンなコピーを作成しながら、元のデータを保持することもできます。
Google Sheetsでテキストを数字に変換するにはどうしますか？
VALUE()関数を使用します：=VALUE(A2)はテキストを実数に変換します。または1を掛けます：=A2*1。列全体について、形式 → 数字 → 数字を使用して、正しいデータ型を強制します。数字に通貨記号またはコンマがある場合、最初にSUBSTITUTE()で削除します。
Google Sheetsでデータをクリーンにする数式は何ですか？
不可欠なデータクリーニング数式：TRIM()は余分なスペースを削除、CLEAN()は印字不可能な文字を削除、UPPER()/LOWER()/PROPER()は大文字小文字を標準化、SUBSTITUTE()は不要な文字を置き換え、SPLIT()は結合されたデータを分離、VALUE()はテキストを数字に変換します。これらをARRAYFORMULAと組み合わせて列全体をすぐにクリーンにします。
データセット内の欠損値をどのように処理しますか？
分析目標によって異なります。=IF(ISBLANK(A2),0,A2)を使用して空白をゼロに置き換えるか、=IF(ISBLANK(A2),"不明",A2)でテキストプレースホルダーを使用します。重要フィールドについては、=FILTER(A2:D100, NOT(ISBLANK(A2:A100)))で不完全な行をフィルターアウトします。任意に空白を埋めないでください。統計分析を歪める可能性があります。