顧客リストやアンケート結果など、複数のデータを集計・分析する際に欠かせないのがデータクレンジングです。表記揺れや重複、不要な空白などが混在したままのデータでは、正しい分析結果が得られないだけでなく、システム連携時の思わぬエラーを引き起こす原因にもなります。
しかし、膨大なデータを手作業で一つひとつ修正していくのは現実的ではありません。そこで活躍するのが、身近なツールであるGoogleスプレッドシートです。
本記事では、Googleスプレッドシートを活用したデータクレンジングの具体的なやり方を解説します。クリック操作だけで完結する標準機能から、効率を劇的にあげる便利な関数、正規表現を用いた高度なテクニック、さらには生成AI（Gemini）を活用した最新の自動化手法まで網羅しています。便利な機能やツールを活用して手作業から卒業し、データ処理の大幅な効率化を図りましょう。
データクレンジングが必要な理由とGoogleスプレッドシートを活用するメリット
データクレンジングとは、データの重複や誤記、表記揺れ、欠損などを探し出し、修正・削除してデータを綺麗な状態に保つ作業のことです。
データの世界には、「ゴミを入れたらゴミしか出ない（GIGO：Garbage In, Garbage Out）」という有名な原則があります。例えば、「株式会社A」と「㈱A」が混在していると、データを集計した際に別々の会社としてカウントされてしまい、「正しい分析結果が得られない」「メール配信やシステム連携時にエラーが生じる」といった事態を招きかねないため、事前のデータクレンジングが不可欠です。
Googleスプレッドシートは、手軽に始められるだけでなく、データ整形に特化した強力な関数や機能が標準で備わっているため、データクレンジングを行うのに最適なツールになり得ます。
【標準機能編】クリックだけで完結するGoogleスプレッドシートのデータクレンジング術
Googleスプレッドシートでは関数を使わなくても、標準機能だけで次のような簡易的なデータクレンジングが可能です。
- 「重複を削除」機能でダブりを一掃
- 「空白の削除（トリム）」機能で見えないスペースを消す
- 「検索と置換」機能で一括修正
すべて簡単なクリックやマウス操作のみで実行できるので、特別な知識や技術はいりません。まずはここから始めてみるのがおすすめです。
「重複を削除」機能でダブりを一掃
名刺管理やアンケート集計など、複数のデータを統合した際に必ずといって良いほど発生するのがデータの重複です。「同じ顧客に二重でメールを送ってしまう」といったミスを防ぐためにも、重複の削除は最初に行うべきステップだといえます。機能を利用する手順は次の通りです。
1, シート上の範囲を選択
2, メニューバーから［データ > データクリーンアップ > 重複を削除］をクリック
3, 重複を判定する列を指定
4, 削除完了
どの列を基準に重複を判定するかもチェックボックスで細かく指定できるため、「メールアドレスが同じなら重複とみなす」といった柔軟な対応も可能です。手作業で目視確認するのに比べ、圧倒的なスピードと正確さでリストを整理できます。
「空白の削除（トリム）」機能で見えないスペースを消す
システムからエクスポートしたデータや、Webサイトからコピペしたテキストには、文字の先頭や末尾に見えないスペース（空白）が入り込んでいることがよくあります。人間の目には同じ「山田太郎」に見えても、後ろにスペースがある「山田太郎 」は、コンピュータ上では別のデータとして扱われてしまい、検索や集計の際にエラーの原因となります。
この空白を削除（トリム）を利用する手順は次の通りです。
1, シート上の範囲を選択
2, メニューバーから［データ > データクリーンアップ > 空白文字を削除］をクリック
3, 削除完了
この機能を実行すると、セル内の先頭や末尾にある不要なスペースだけが綺麗に削除されます。コピペ作業が多いシートでは、ひとまず実行しておくだけで未然にトラブルを防げる便利な機能です。
「検索と置換」機能で一括修正
特定の文字列の表記揺れをまとめて修正したい場合に活躍するのが、検索と置換の機能です。例えば、データ内に混在している「㈱」や「（株）」という表記を、すべて正式な「株式会社」に統一するようなケースで非常に役立ちます。
検索と置換の機能を利用する手順は次の通りです。
1, シート上の範囲を選択（シート全体を指定する場合は省略可）
2, 「Ctrl + H」（Macの場合は「Cmd + Shift + H」）を押す
3, ［検索］欄に変更前の文字、［置換後の文字列］欄に変更後の文字を入力し、［すべて置換］をクリック
4, 置換完了
大文字・小文字の区別や、正規表現を使った複雑な検索にも対応しているため、単純な文字の置き換えから高度な一括変換まで幅広く対応できます。
【関数編】簡単な数式を用いたGoogleスプレッドシートのデータクレンジング術
Googleスプレッドシートの関数を活用すれば、ほかのセルにも効率良く同じ処理を反映できるため、データクレンジングの作業効率が大きく向上します。なかでも特に、以下の関数は扱いが簡単で、誰でも容易に整形や表記揺れ対策として活用できます。
- 余分なスペースを綺麗に消去する（TRIM関数）
- 不要な改行や制御文字を取り除く（CLEAN関数）
- 全角・半角の混在を統一する（ASC / JIS関数）
- アルファベットの大文字・小文字を揃える（UPPER / LOWER関数）
- 特定の文字を置き換える（SUBSTITUTE関数）
- データを分割する（SPLIT関数）
各関数の特徴や使い方を解説します。
余分なスペースを綺麗に消去する（TRIM関数）
TRIM関数では、先ほど紹介したデータクリーンアップの「空白の削除」機能と同じ処理が可能です。以下のように数式を入力すると、対象となる文字列の先頭および末尾にある不要なスペースを完全に削除してくれます。
| 構文 | =TRIM(文字列またはセル番地） |
| 入力例 | =TRIM(A2） ※A2セルの先頭および末尾の不要スペースを削除 |
さらに優秀な点として、単語間に入っているスペースが複数連続している（例：「山田 太郎」）場合、自動的に一つのスペースに整えてくれます。TRIM関数をうまく活用すれば、Webサイトからのコピペや、複数人での手入力によって生じた「見えない空白」によるデータの不一致を防げます。
不要な改行や制御文字を取り除く（CLEAN関数）
社内システムや外部のデータベースからエクスポートしたCSVファイルを読み込むと、セル内に不要な改行や印刷できない制御文字が混入していることがよくあります。これらを一掃するのがCLEAN関数です。目に見えない不要な文字コードが消去され、データが1行の綺麗なテキストに整形されます。
| 構文 | =CLEAN(文字列またはセル番地） |
| 入力例 | =CLEAN(A2） ※A2セル内の不要な改行や制御文字を削除 |
さらに実践的なテクニックとして、「=TRIM(CLEAN(A2))」のように、2つの関数を組み合わせる方法がおすすめです。これにより、余分なスペースの削除と改行・制御文字の消去を同時に処理する、強力なデータクレンジングが可能になります。
全角・半角の混在を統一する（ASC / JIS関数）
日本語のデータを取り扱う際、特に厄介なのが全角と半角の混在です。例えば、電話番号の「０９０」と「090」、あるいはカタカナの「ア」と「ｱ」は、人間の目には同じに見えてもシステム上はまったく別物として扱われ、集計エラーの原因となります。これを一挙に解決するのがASC関数で、全角の英数字やカタカナをすべて半角に変換し、データ形式を統一できます。
| 構文 | =ASC(文字列またはセル番地） |
| 入力例 | =ASC(A2） ※A2セル内の全角文字を半角文字に変換 |
反対に、半角文字を全角文字にそろえたい場合は、JIS関数を使用します。使い方はASC関数とまったく同じです。データの用途に合わせて使い分けましょう。
アルファベットの大文字・小文字を揃える（UPPER / LOWER関数）
顧客のメールアドレスや商品の型番、IDなどでよく発生するのが、アルファベットの大文字・小文字の表記揺れです。例えば、「apple・Apple・APPLE」といった表記が混在していると、正しい検索や集計ができません。
アルファベットの大文字・小文字をそろえるには、UPPER関数が便利です。すべての文字を大文字に統一できます。
| 構文 | =UPPER(文字列またはセル番地） |
| 入力例 | =UPPER(A2） ※A2セル内の英字を大文字に統一 |
反対に、LOWER関数を使用することで、英字を小文字に統一できます（使い方はUPPER関数と同じ）。
特定の文字を置き換える（SUBSTITUTE関数）
先ほど紹介した「検索と置換」機能の関数版といえるのが、SUBSTITUTE関数です。特定のテキスト内の指定した文字列を、別の文字列に自動的に置き換えられます。
| 構文 | =SUBSTITUTE(文字列またはセル番地, 検索文字列, 置換文字列, [置換回数]) |
| 入力例 | =SUBSTITUTE(03-1234-5678, "-", "") ※ハイフンを取り除いた「0312345678」に変換される |
置換回数を指定しない場合は、検索文字列が出現するすべての箇所が置き換えられます。例えば、置換回数を1回に指定すると、検索文字列が2回出現したとしても、最初の出現箇所のみが置き換えられる仕組みです。
SUBSTITUTE関数を活用することで、商品の型番の一部を変更したり、特定の記号だけを消去したりと、ルールが決まっている文字の置き換え作業を自動化できるため、日々の定型業務の時短に大きく貢献します。
データを分割する（SPLIT関数）
一つのセルに詰め込まれたデータを、複数のセルに綺麗に分割したいときに欠かせないのがSPLIT関数です。例えば、「山田 太郎」のように姓と名がスペースで区切られて入力されているセルを、隣り合う2つのセルに「山田」と「太郎」を別々に表示させるといった活用が可能です。
| 構文 | =SPLIT(文字列またはセル番地, 区切り文字, [各文字での分割], [空のテキストを削除]) |
| 入力例 | =SPLIT(山田 太郎, " ") ※スペースを起点に「山田」と「太郎」を分割し別々のセルに表示 =SPLIT(info@example.com, "@") ※@を起点にユーザー名とドメイン名を別々の列に抽出 |
カンマ区切りのCSVデータを展開する際や、住所を都道府県とそれ以降で分けたい場合など、データの構造を整理・再配置する際に効果的です。
【応用編】正規表現を使った高度なGoogleスプレッドシートのデータクレンジング術
少し難易度はあがりますが、正規表現（文字列のパターンを単一の文字列を用いて表現する手法）を使った関数をマスターすると、データクレンジングの幅が大きく広がります。特に次の2つの関数が代表的です。
- 複雑な条件での置換（REGEXREPLACE関数）
- 特定のパターンを抽出（REGEXEXTRACT関数）
各関数の特徴や使い方を解説します。
複雑な条件での置換（REGEXREPLACE関数）
SUBSTITUTE関数では、特定の固定文字しか置換できませんが、REGEXREPLACE関数を使えば、特定のパターンに一致する文字をすべて置換できます。
| 構文 | =REGEXREPLACE(文字列またはセル番地, パターン, 新しい文字列) |
| 入力例 | =REGEXREPLACE(A2, "[^0-9]", "") ※A2セルの0から9の数字以外の文字をすべて空白に置き換える |
例えば、入力された電話番号に「03-1234-5678」や「090.1234.5678」、「06 1234 5678」のように、ハイフンやピリオド、スペースなどの区切り文字が混在しているとします。そこで、上記のサンプル数式を入力すれば、数字以外の箇所がすべて空白になり、「0312345678」といった形で数字のみの表記に統一できます。
このようにREGEXREPLACE関数では、どのような記号が入っていても純粋な数字の羅列に変換可能です。複雑な表記揺れをまとめて修正できる非常に便利な関数です。
特定のパターンを抽出（REGEXEXTRACT関数）
文章のなかから特定のパターンやルールの文字列だけを抜き出したい場合は、REGEXEXTRACT関数が役立ちます。
| 構文 | =REGEXEXTRACT(文字列またはセル番地, 正規表現, [抽出モード], [大・小文字の区別]) |
| 入力例 | =REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}") ※A2セル内からメールアドレスと思われる文字列を抽出 |
条件に該当する箇所が複数ある場合は、先頭に近い箇所のみ抽出されます。ただし、3つ目の引数を設定することで、全箇所を抽出するといった設定も可能です。
上記のサンプル数式を使えば、長文のアンケート回答のフリーテキスト欄から、メールアドレスと思われる文字列のみをピンポイントで抽出できます。また、URLのリストから「https://」や末尾のディレクトリを省き、純粋なドメイン部分（example.comなど）だけを取得したい場合にも最適です。マーケティングデータの解析やスクレイピング後のデータ抽出といった場面でも大きな威力を発揮するでしょう。
【AI活用編】作業を自動化するGoogleスプレッドシートのデータクレンジング術
Google製の生成AI「Gemini」を活用すれば、Googleスプレッドシートのデータクレンジングをより少ない工数で実行できます。自然言語での指示だけで作業を完結できるので、関数のような難解な知識を習得する必要もありません。具体的な活用例を紹介します。
Geminiにデータ整形を直接指示する
Google Workspaceの「Business Standard以上」、Google Oneの「Google AI Pro以上」のエディションに契約している場合は、Googleスプレッドシート上でGeminiサイドパネルを利用できます。画面右上の［Geminiに質問する］のアイコンをクリックすれば、シート右側に専用のインターフェースが現れ、プロンプトの入力や回答の表示が可能です。
データクレンジングを行う際は、Geminiに次のような指示を与えられます。
【プロンプトの入力例】
| ・A1～G9のテーブルにおいて重複データを修正してください。 ・A列の住所データから都道府県名だけを抽出してB列に書き出してください。 ・C列にある会社名の「株式会社・（株）・㈱」をすべて「株式会社」に統一してください。 |
回答後は新たなテーブルが生成され、［挿入］や［上書き］をクリックして反映できます（以下は元データ（表_4）から重複行を排除して新たなテーブル（表_5）を作成した画像）。
AIが文脈を理解してデータを処理してくれるため、関数を作成する手間を省けます。特に、ルール化しにくい微妙な表記揺れ（例：「引っ越し・引越・引越し」の統一）など、人間の目で判断していたような曖昧なデータのクレンジングにおいて、AIの提案力が大いに効果を発揮します。
複雑な関数や数式を生成してもらう
「やりたいことは明確だが、どの関数を使えば良いかわからない」「正規表現の書き方がわからない」という壁にぶつかったときも、Geminiが頼りになります。
Geminiサイドパネルで、「A列のテキストからカッコのなかの文字だけを抽出する関数を教えて」と質問すれば、即座にREGEXEXTRACT関数などを組み合わせた適切な数式を回答してくれます。さらに、「エラーが出た場合は空白にするようにIFERROR関数を追加して」といった追加の要望にも応えてくれるため、自分専用の高度なデータクレンジングツールをAIとの対話を通じて簡単に構築できます。
関数辞典での検索時間を大幅に削減できる画期的な活用法です。
Googleスプレッドシートにおけるデータクレンジングの手間を減らす予防のコツ
データクレンジングの効率性を高めるには、「そもそも不整合なデータを入力させない」という事前対策が不可欠です。ここでは、Googleスプレッドシートで設定できる予防策を紹介します。
プルダウンリストの活用
複数人でシートを共有して入力作業を行う場合、人によって入力方法が異なるため、表記揺れが発生しやすくなります。「東京・東京都・tokyo」などのブレを防ぐ最も確実な方法は、自由記述をやめさせることです。プルダウンリストを活用し選択肢から選ぶ方式にすれば、表記揺れは物理的に発生しなくなります。
プルダウンリスト機能を有効にするには、セル範囲を選択した状態で右クリックメニューから［プルダウン］をクリックします。または、［データ > データの入力規則］を開き、［データを追加］からプルダウンの条件を適用させることも可能です。
アンケートフォームを作成する際も、可能な限りプルダウンリストやラジオボタンを活用することで、集計前のデータクレンジング作業を抑制できます。
表示形式の設定
日付や金額に関するデータを入力する際は、担当者によって「2026/01/01」や「2026年1月1日」、「1000」や「1,000円」などフォーマットがバラバラになりがちです。このような事態を防ぐには、列全体にあらかじめ表示形式を設定するのがおすすめです。
対象の列を選択し、メニューバーの［表示形式 > 数字］から、適切なフォーマットを指定します。これにより、ユーザーが「1/1」とラフに入力しても自動的に「2026/01/01」に変換されます。内部的なデータ形式が統一されるため、その後の関数計算やピボットテーブルでの集計をスムーズに行えるようになります。
Googleスプレッドシートを最大限に活用するならGoogle Workspace
ここまで紹介したGoogleスプレッドシートの機能や、GeminiのAI機能を本格的に業務へ組み込むのであれば、企業向けグループウェアである「Google Workspace」の導入がおすすめです。無料のGoogleアカウントでも多くの機能を利用できますが、Google Workspaceを導入することで、組織全体でのセキュアなファイル共有や、より高度なデータアクセス権限の管理が可能です。
また、Geminiサイドパネルをフル活用して組織全体の業務効率を底上げできるほか、Googleフォームで収集したデータをGoogleスプレッドシートで自動クレンジングし、Looker Studioで可視化するといったシームレスな連携にもつながります。データドリブンな組織づくりを目指すなら、ぜひ検討したいプラットフォームです。
Googleスプレッドシートの関数やAIを活用してデータクレンジングの効率性を高めよう
データの重複や空白の削除、表記揺れの統一といった作業は、正しい集計や分析を行うための重要な土台づくりです。まずはGoogleスプレッドシートの手軽な標準機能から始め、慣れてきたらTRIM関数やSUBSTITUTE関数などを取り入れることで、作業効率は飛躍的に向上します。さらに、正規表現を用いた高度な置換や、Geminiを活用した自動化など、自社のデータ状況に合わせて最適なアプローチを選んでみてください。
データクレンジングの手間を最小限に抑えるためには、プルダウンリストや表示形式の統一といった不整合なデータを入力しないための事前対策も欠かせません。本記事で紹介した機能やツールをフル活用し、Googleスプレッドシートでのデータ管理をより正確で快適なものにしていきましょう。
電算システムでは、環境構築やコンサルティングなど、Googleサービスの導入支援サービスを提供しています。GmailやGoogleドライブといった個別のサービスはもちろん、Google Workspaceのサポートにも対応しています。専門領域に精通した数多くのエンジニアが在籍しているので、スピーディかつ質の高いサポートを行えるのが強みです。「Googleサービスを活用したいが具体的なイメージが湧かない」といったお悩みを抱える方は、ぜひ電算システムへと気軽にお問い合わせください。
