エキスパート ガイド:高度なデータ クリーニングのための Power Query と Google Sheets REGEX

データ クリーニングはデータ分析と計算を実行するために不可欠であり、データの準備とフォーマットの最初のステップです。データ クリーニングとは、不整合、エラー、不要なフォーマットを削除することを意味します。 Microsoft Excel の Power Query と Google Sheets の REGEX 関数はどちらも高度なクリーニングに強力です。 Power Query と Google スプレッドシートの REGEX 関数を使用して、高度なデータ クリーニングを行う方法を比較しながら説明します。

Excel の Power Query

Power Query は、データ変換とデータ クリーニングのための使いやすいインターフェイスを提供する Excel の組み込み機能です。複雑な数式を使用せずにデータをクリーンアップしてフォーマットします。 Power Query は、さまざまなソースからデータをインポートし、堅牢なデータ接続を提供します。複雑なデータ クリーニング タスクを実行するための柔軟な機能を備えています。

Google スプレッドシートの REGEX 関数

Google スプレッドシートの REGEX 関数は、正規表現を使用してテキスト データ内のパターンを検索するため、テキスト エントリの解析、書式設定、検証に最適です。

<オル>

REGEXREPLACE: 正規表現に一致するテキストを指定されたテキストに置き換えます。

• =REGEXREPLACE(テキスト、正規表現、置換)

正規表現一致: テキスト文字列が指定された正規表現と一致するかどうかを確認します。

• =REGEXMATCH(テキスト, 正規表現)

REGEXEXTRACT: 正規表現に基づいてテキストから一致する部分文字列を抽出します。

• =REGEXEXTRACT(テキスト, 正規表現)

これらの関数は、データ クリーニング、パターン認識、動的なテキスト操作に役立ちます。

間違った書式設定、不要な文字、エラー、空白などが含まれる生のデータセットを考えてみましょう。 Excel の Power Query と Google Sheets の REGEX 関数を使用して、相互に比較しながら高度なデータ クリーニングを示します。

不要な文字の削除

括弧、ダッシュ、スペースなどの不要な文字を含む電話番号のデータセットをクリーンアップします。

パワークエリ

• データ範囲を選択します。
• データに移動します。 タブ>> テーブル/範囲からを選択します .
• パワークエリ エディターがポップアップします。列を選択します。 電話番号を選択しました。 列。
• 変換に移動します。 タブ>> 値の置換を選択します .
• 値を置換 ダイアログボックスが表示されます;
  • 見つける価値 ボックス:不要な文字 ((、)、-、) を個別に入力します。
  • 置換内 ボックス:空のままにするか、置換する値がある場合は、その値を挿入できます。
  • [OK] をクリックします。 .

• [閉じてロード] を選択します。 クリーンアップされたデータを Excel に戻すため。

Google スプレッドシートの REGEXREPLACE 関数

REGREPLACE 関数を使用して、Google スプレッドシートで書式設定とともにデータをクリーンアップしましょう。次の数式をセル 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 の形式にします。;それ以外の場合は、「無効」を返します。

Google スプレッドシートの REGREPLACE 関数

次の式を使用して、不要な特殊文字を削除できます。

式:

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

これは[^a-zA-Z0-9]です。 pattern は、文字でも数字でもない文字を削除します。

正規表現に精通している場合は、REGREPLACE 関数を使用できます。この関数は、単一の式内の文字または数字以外の文字を削除します。

テキストケースの標準化

データセットには混合テキストが含まれる場合があります。すべてのテキストエントリを小文字またはタイトルケースに変換できます。

パワークエリ

データ範囲を選択し、データ に移動して Power Query を開きます。 タブ>> テーブル/範囲からを選択します .

• 変換するテキストが含まれる列を選択します。
• 変換に移動します。 タブ>> 各単語を大文字にするを選択します .

Google スプレッドシートの適切な機能

Google スプレッドシートの REGEXREPLACE は、正規表現パターンや置換内で UPPER 関数と LOWER 関数を直接サポートしていないため、純粋に正規表現を使用して各単語を大文字にする方法はありません。 PROPER、UPPER、LOWER などの外部関数を使用できます。

この数式により、各単語が必ず大文字になり、先頭以外の文字は小文字に変換されます。

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

結合されたテキストまたは名前を区切り文字によって別の列に分割できます。 Power Query と Google スプレッドシートの REGEX 関数を使用してデータをクリーンアップしましょう。

パワークエリ

データ範囲を選択し、データ に移動して Power Query を開きます。 タブ>> テーブル/範囲からを選択します .

• 名前を含む列を選択します。
• ホーム に移動します 列の分割からタブ>>> [区切り文字による] を選択します。 .

• 区切り文字による列の分割 ダイアログボックス;
  • 区切り文字を選択して入力します: スペース ( ) を選択してください .
  • [OK] をクリックします。 .

• 結果の列の名前を「名」と「姓」に変更します。

Google スプレッドシートの REGEXEXTRACT 関数

REGEXEXTRACT 関数を使用すると、区切り文字によってデータを分割できます。選択したセルに次の数式を挿入します。

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

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

Power Query と Google Sheets REGEX を使用する場合

Power Query は、基本から高度なデータ クリーニングに最も役立ちます。ユーザーフレンドリーなインターフェイスを備えた、より広範な構造化データ変換に最適です。複雑なデータセット、構造化結合、データの結合を操作する場合に効率的です。

Google スプレッドシートの REGEX 関数は、テキストベースの素早い操作や個々のセル内の特定のテキストの抽出に最適です。 REGEX 関数は、正規表現の構文に慣れている場合に特に役立ちます。

結論

Power Query と Google Sheets の REGEX 関数は、データ クリーニングのための補完的なツールを提供します。 Power Query は、大規模なデータセットでバッチ変換を必要とする Excel ユーザーに最適ですが、Google スプレッドシートの REGEX 関数は、小規模なデータセットでのテキストベースの操作に柔軟性を提供します。両方を効果的に使用して、強力かつ合理的なデータ クリーニングを行うことができます。両方のツールを理解すれば、Excel であっても Google スプレッドシートであっても、ほとんどのクリーニング タスクを処理できるようになります。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!