【徹底解説】Power QueryとGoogle Sheets REGEX関数で実現する高度なデータクレンジング

データ分析や計算を正確に行うためには、データクレンジング(データ cleansing)が不可欠です。データクレンジングは、データ準備と整形の最初のステップであり、不整合やエラー、不要な書式を取り除く作業を指します。Microsoft Excelの「Power Query」とGoogle Sheetsの「REGEX関数」は、どちらも高度なデータクレンジングに役立つ強力なツールです。本記事では、両ツールの使い方を具体的な手順とともに解説し、それぞれの特徴を比較していきます。

ExcelのPower Queryとは

Power QueryはExcelに標準搭載されている機能で、直感的なインターフェースを使ってデータ変換やデータクレンジングを行えます。複雑な数式を一切使わずにデータの整形ができるのが大きな魅力です。さらに、さまざまなデータソースからインポートでき、強力なデータ接続機能を備えているため、柔軟かつ効率的に複雑なクリーニング作業を処理できます。

Google SheetsのREGEX関数とは

Google SheetsのREGEX関数は、正規表現を使ってテキストデータ内のパターンを検出する関数群です。テキストの解析、書式設定、入力値の検証などに最適です。主な関数は以下の3つです。

1. REGEXREPLACE: 正規表現に一致するテキストを指定した文字列に置き換えます。
   • =REGEXREPLACE(テキスト, 正規表現, 置換後の文字列)
2. REGEXMATCH: テキストが指定した正規表現に一致するかどうかを判定します。
   • =REGEXMATCH(テキスト, 正規表現)
3. REGEXEXTRACT: 正規表現に一致する部分文字列をテキストから抽出します。
   • =REGEXEXTRACT(テキスト, 正規表現)

これらの関数は、データクレンジング、パターン認識、動的なテキスト操作などに幅広く活用できます。

ここでは、誤った書式、不要な文字、エラー、余分な空白などを含む生データを例に、Power QueryとREGEX関数それぞれによる高度なデータクレンジング手法を比較しながら紹介します。

不要な文字の削除

まずは、括弧やハイフン、スペースなどの不要な文字が混在した電話番号データをクリーンアップしてみましょう。

Power Queryでの削除方法

• データ範囲を選択します。
• データタブ >> テーブルまたは範囲から を選択します。
• Power Queryエディターが起動するので、対象の列を選択します。ここではPhone Number(電話番号)列を選択しました。
• 変換タブ >> 値の置換 を選択します。
• 値の置換ダイアログボックスが表示されます。
  • 検索する値: 不要な文字((、)、- など)を1つずつ入力します。
  • 置換後の値: 空欄のままにするか、置き換えたい値があれば入力します。
  • OKをクリックします。

• 閉じて読み込むを選択すると、クリーニング済みのデータがExcelに戻されます。

Google SheetsのREGEXREPLACE関数での整形

Google Sheetsでは、REGEXREPLACE関数を使ってデータのクリーンアップと書式設定を同時に行えます。セルG2に以下の数式を入力してください。

=ARRAYFORMULA(IF(LEN(REGEXREPLACE(D2:D6, "[^0-9]", ""))=10,
                 "(" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 1, 3) & ") " &
                 MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 4, 3) & "-" &
                 MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 7, 4),
                 "Invalid"))

この数式は、まずすべての数字以外の文字を削除し、次にクリーニング後の番号がちょうど10桁であるかどうかをチェックします。有効な場合は (XXX) XXX-XXXX の形式に整形し、無効な場合は「Invalid」を返します。

特殊文字だけを除去したい場合

英数字以外の不要な特殊文字だけを取り除きたい場合は、以下のシンプルな数式が便利です。

数式:

=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")

パターン [^a-zA-Z0-9] は、アルファベットと数字以外のあらゆる文字を削除します。

正規表現に慣れている方なら、REGEXREPLACE関数ひとつで、1つの数式だけで英数字以外の文字をまとめて除去できるので非常に効率的です。

テキストの大文字・小文字の統一

データセットには大文字と小文字が混在したテキストが含まれることがあります。すべてのテキストを小文字やタイトルケース(各単語の先頭のみ大文字)に統一しましょう。

Power Queryでの統一方法

データ範囲を選択し、データタブ >> テーブルまたは範囲から を選択してPower Queryを開きます。

• 変換したいテキストが含まれる列を選択します。
• 変換タブ >> 各単語の先頭を大文字にする を選択します。

Google SheetsのPROPER関数での統一方法

Google Sheetsでは、REGEXREPLACE単体で各単語の先頭を大文字化することはできません。REGEXREPLACEは正規表現パターンや置換文字列の中でUPPER関数やLOWER関数を直接サポートしていないためです。そのため、PROPER、UPPER、LOWERなどの外部関数と組み合わせて使います。

この組み合わせにより、各単語の先頭文字は大文字に、それ以外の文字は小文字に変換され、表記が統一されます。

区切り文字によるデータ分割

結合されたテキストや氏名データは、区切り文字を基準に別々の列へ分割できます。ここでもPower QueryとGoogle SheetsのREGEX関数を使ってデータを整理してみましょう。

Power Queryでの分割方法

データ範囲を選択し、データタブ >> テーブルまたは範囲から を選択してPower Queryを開きます。

• 氏名が含まれる列を選択します。
• ホームタブ >> 列の分割 > 区切り記号別 を選択します。

• 区切り記号別の列分割ダイアログボックスで以下を設定します。
  • 区切り記号の選択または入力: スペースを選択します。
  • OKをクリックします。

• 生成された列を「First Name(名)」「Last Name(姓)」などにリネームすれば完成です。

Google SheetsのREGEXEXTRACT関数での分割方法

REGEXEXTRACT関数を使えば、区切り文字を基準にデータを分割できます。任意のセルに以下の数式を入力してください。

=REGEXEXTRACT(E9, "^([^ ]+) (.+)$")

この数式は、姓名を2つのセルに分離します。必要に応じて補助列を追加して利用します。

Power QueryとREGEX関数の使い分け

Power Queryは、基本的なクリーニングから高度な処理まで幅広く対応できる万能ツールです。大規模で構造化されたデータ変換に適しており、ユーザーフレンドリーなインターフェースを持ちます。複雑なデータセットの処理、構造化された結合、データのマージなどにも高い効率を発揮します。

一方、Google SheetsのREGEX関数は、個々のセルに対する素早いテキスト操作や特定の文字列抽出に最適です。正規表現の構文に慣れている方にとっては、特に強力な武器になります。

まとめ

Power QueryとGoogle SheetsのREGEX関数は、互いを補完するデータクレンジングツールです。Power Queryは大規模データセットの一括変換が必要なExcelユーザーに理想的であり、Google SheetsのREGEX関数は小規模データセットでの柔軟なテキスト操作に優れています。両方のツールを使いこなせば、強力かつ効率的なデータクレンジングが実現できます。どちらのツールも理解しておけば、ExcelでもGoogle Sheetsでも、ほとんどのクリーニング作業に対応できるでしょう!