Google Sheets Forkus

【保存版】正規表現でデータクレンジングする方法・便利テクニックまとめ


今回は、スプレッドシートでデータクレンジングする方法を学んでいきましょう。
どういう意味？
ざっくり言うと、データを分かりやすく揃えたり、抜き出すことよ。


※ Data cleansing は、データクリーニング・データ処理（Data cleaning, processing）とも呼ばれます。



データがバラつくのは、作成者の指示不足による所が大きく、本当はこうしたことが起きないように選択リストを用意するのが望ましいです。

一方で、やむを得ず結果的にデータがバラけることもあります。以下では、上の解決方法を学習し、基本的なテクニックも合わせて確認します。


　Step # 1：　データを抜き出す方法
　Step # 2：　データをきれいにする関数

Step # 1：データを抜き出す方法


ばらつきのあるデータに直面したら、キーワードを抜き出すことが近道です。本例では「WIFI」・「有線」を、RegexExtract関数で抜き出します。


RegexExtractとは

RegexExtract関数は、正規表現（Regular expression）という検索のルールを用いて、データを効率的に抜き出し（Extract）ます。正規表現は様々なプログラミング言語でも使用されています。以下では、基本的な正規表現をつかって、データの抜き出しを行います。

  1. 　“wifi|有線”

    2. 　 正規表現 “A|B” は、AかBを意味します。しかし、”WIFI” や “wi-fi” では、 “wifi”に一致しないためエラーが表示されていますね。

    = RegexExtract(C3, “wifi|有線”)
    ※ C3は検索範囲。“wifi|有線”は抜き出したいデータです。


  2. 　[\w-]

    3. 　 正規表現 [ ] は、[ ]内の文字に一致する1文字を抜き出します。\wはword charactersを意味し、全英文字を大・小文字関係なく表します。今回は、”wi-fi”中央の”-“（ハイフン）も必要なので、合わせて[\w-]となりますね。しかし、1文字というのがボトルネックで、先頭のWかwしか抽出できません。

    = RegexExtract(C3, “[\w-]|有線”)
    ※ シンプルに”[a-zA-Z-]|有線”でも可能です。


  3. 　〜+

    4. 　 正規表現 + は直前の文字を1回以上繰り返します。今回の直前の文字は[\w-]、つまり全英文字と”-“なので、これらに当てはまる文字を１つずつ一致する限り返します。

    = RegexExtract(C3, “[\w-]+|有線”)

    かなり完成に近づいてきたわね。後はwi-fiを大文字にして–（ハイフン）をなくすだけよ。以下の関数を使って、完成させましょう。
    別にもうこれでよくね..


    データをきれいにする関数１


  4. 　UPPER

    5. 　 Upper関数はその名の通り、文字をupper case（大文字）にするシンプルな関数です。全て大文字になっていますね。

    = UPPER(RegexExtract(C3, “[\w-]+|有線”))


  5. 　 SUBSTITUTE

    6. 　 Substitute関数はその名の通り、何かをSubstitute（〜を代わりに使う）する関数です。無事”-“（ハイフン）を””（空白文字）に置き換えることができました。

    = SUBSTITUTE(UPPER(RegexExtract(C3, “[\w-]+|有線”),”-“,””))


    これで完成ね。後はおまけなんだけど、arrayformulaを使ってコピペ作業も効率化しましょう。
    長いなあ..


  6. 　ARRAYFORMULA （おまけ）


    7. 　Before （相対参照で、最終行までコピペ）



    　After （RegexExtract関数内で範囲C3:C14を指定し、ArrayFormulaを使用）


    = ARRAYFORMULA(SUBSTITUTE(UPPER(RegexExtract(C3:C14, “[\w-]+|有線”),”-“,””)))

    　ArrayFormulaによって、一瞬で入力ができました。実際のデータでは数百行データが続くこともあります。手作業でコピペしていては大変です。ArrayFormulaは、次のショートカットキーによって一瞬で入力可能です。（ショートカットが付与されている関数は、要するに重要ということです。）

    Windows　Ctrl + Shift + Enter　 |　Mac　⌘ + Shift+ Enter


    Step # 2：データをきれいにする関数２


    以下では、使用頻度の高いデータをきれいにする関数のまとめをみていきましょう。


    　ASC

    　 ASC関数は全角入力される文字を半角にして返します。全角で入力してあるデータがあれば、下処理でASC関数を使うことで後々楽になりますね。

    = TRIM(A2)

    　TRIM

    　 TRIM関数はデータに含まれる文字間の空白をなくします。空白が２つ以上の場合や、文字の前後にある場合でもきれいにまとめあげることが可能です。

    = TRIM(A2)

    　CONCATENATE

    　 CONCATENATE関数は、２つのセルにまたがる値を１つのセルにまとめる関数です。空欄を中央に入れた状態でまとめることもでき、その場合中央に空欄文字を挿入します。

    = CONCATENATE (① 値1, [② 中央に挿入する値,] ③ 値2)

    = CONCATENATE(A2, “_”, B2)

RECOMMENDこちらの記事も人気です。