【Googleスプレッドシート | クエリ関数シリーズ6】クエリ関数で正規表現使えんの知ってる?

スポンサーリンク

クエリ関数で正規表現使えんの知ってる?

クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。

このように、指定範囲から条件を指定してデータを取り出す関数は、EXCELでもお馴染みのデータベース関数各種がありますが、このクエリ関数を使うことによって、より柔軟に複雑な条件を簡単に指定することが出来るようになります。

EXCELではVBAでユーザー定義関数作らないと実現出来ない正規表現!スプレッドシートなら手軽に使えちゃいます!

どうせスプレッドシート使うなら、EXCELではできないことを覚えた方が良いですよね!(^^)

今回は、クエリ関数で正規表現を使う方法を紹介させていただきます!

これまでのシリーズはこちら

【Googleスプレッドシート | クエリ関数シリーズ1】Query関数使ってる??
クエリ関数って使ってる? クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。 このように、指定範囲から条件を指定して続きを読む
【Googleスプレッドシート | クエリ関数シリーズ2】条件を指定してデータを抽出出来る?
条件を指定してデータを抽出出来る? クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。 このように、指定範囲から条件続きを読む
【Googleスプレッドシート | クエリ関数シリーズ3】複雑な条件を指定してデータを抽出しよう!
複雑な条件を指定してデータを抽出しよう! クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。 このように、指定範囲か続きを読む
【Googleスプレッドシート | クエリ関数シリーズ4】クエリ関数をもっと便利に使う!
クエリ関数をもっと便利に使う! クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。 このように、指定範囲から条件を指続きを読む
【Googleスプレッドシート | クエリ関数シリーズ5】クエリ関数を使って集計しよう!
クエリ関数を使って集計しよう! クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。 このように、指定範囲から条件を指続きを読む

正規表現

突然ですが、「正規表現」って知ってますか?多分プログラミングをする人以外には普段接することがない言葉ですよね。スプレッドシートやEXCELなどをよく使っているかたでもあまり目にすることは無いんじゃないかと思います。

プログラミングをする人にはおなじみの言葉で、この「正規表現」というものだけで沢山の本が出ているくらいです。

という感じで書くと、難しそう。。。って感じますね(笑)

でも安心して下さい。この正規表現というのは基本的なことはそんなに難しく有りません。もちろん極めようと思えば大変な努力が必要だと思いますが、「スプレッドシートでちょろっと便利に使おう!」くらいのノリで使う分には、一番最初のサキッちょの分かる範疇をペロッとしたくらいでも「へーめっちゃ良いやん!」的な間隔になれると思います^_^

正規表現とは

前置きが長くなりましたが、正規表現とは簡単に言ってしまうと、

文字列のパターンを記号で表現したもの

です。

分かりにくいと思うので、早速ですがよく使われる正規表現の記号の一覧です。

よく使われる「正規表現」の記号

記号 意味 正規表現の例 マッチする文字列例
. 任意の1文字 . a , b, c なんでも
? 0回または1回の出現 ab?cd

acd → 0回

abcd → 1回

* 0回以上の繰り返しの出現 ab*cd

acd → 0回

abcd → 1回

abbcd → 2回 

abbbcd → 3回 

abbbb・・・cd → 4回以上何回でも

+ 1回以上の繰り返しの出現 ab+cd

abcd → 1回

abbcd → 2回 

abbbcd → 3回 

abbbb・・・cd → 4回以上何回でも

{n} n回の繰り返しの出現 ab{3}cd abbbcd → 3回 
{n,} n回以上の繰り返しの出現 ab{3,}cd

abbbcd → 3回

abbbbcd → 4回

abbbbbcd → 5回

abbbbbb・・・cd → 5回以上何回でも

{m,n} n回以上m回以下の繰り返しの出現 ab{3,4}cd

abbbcd → 3回

abbbbcd → 4回

^ 文字列の先頭 ^abcd abcd → abcで始まる
$ 文字列の末尾 abcd$ abcd → abcdで終わる
| |の左側の文字列もしくは、|の右側の文字列 a(b|c)d

abd

acd

 \w アルファベット、アンダーバー、数字 ab\wcd

abgcd

ab_cd

ab9cd

ab[a-zA-Z_0-9]cdと同じ

\W アルファベット、アンダーバー、数字以外の文字 ab\Wcd

abcd

abcd

ab[^a-zA-Z_0-9]cdと同じ

\s 空白文字(半角スペース,タブ,改行,キャリッジリターン) ab\scd

ab cd

※半角スペース

\S 空白文字以外 ab\Scd

abgcd

ab9cd

\d 半角数字(0~9) ab\dcd

ab8cd

ab5cd

\D 半角数字以外 ab\Dcd

abmcd

abcd

[~] ~のいずれかの1文字 ab[cde]fg

abcfg

abdfg

abefg

[^~] ~のいずれの1文字にも一致しない ab[^cde]fg

abafg

abbfg

abffg

ab1fg

※他にもたくさんありますので興味のある方はググったり本を買ったりして勉強して下さい!

このような、記号を組み合わせて文字列のパターンを作成し、そのパターンにマッチした文字列を検索するために使います。

要するに、「超便利なあいまい検索」って感じですね^_^

クエリ関数でも、この正規表現を使った検索を指定することが出来ます。

もともとあいまい検索には「contains」や「Like」がありますが、正規表現を使うことで更に柔軟なも文字列のあいまい検索が可能になります!

matches クエリ関数で正規表現を使う

クエリ関数で正規表現を使う場合は、「where」に続けて、「matches」のオプションを指定します。

構文

=QUERY(範囲,"where 列 matches '正規表現'")

※正規表現は「'」シングルクォーテーションで囲います。

それではサンプルで確認していきましょう

【サンプル】

また、なんちゃって個人情報さんからいただきました(いつもありがとうございます!)

名前と都道府県、カレーの食べ方のリストです。

「表示用」シートのA1セルにクエリ関数を入力していきます。

次の数式を入力します。

=QUERY('正規表現'!A1:C,"where B matches '.*府$'")

正規表現はこちらです。

.*府$

「.」ドットは、任意の一文字

「*」アスタリスクは直前の文字の0回以上の繰り返し

「$」ドルは、その文字列で終わる

でしたね。

つまり~府(~は何文字でもどんな文字でもOK)という意味になります。

それでは実行してみます。

図のように、大阪府と京都府。「府」で終わる行が抽出されました!

もうひとつやってみます。

次の数式を入力します。

=QUERY('正規表現'!A1:C,"where C matches '^(右|左).*攻め.*'")

正規表現はこちらです。

^(右|左).*攻め.*

「^」キャロットは、で始まる文字列

「|」パイプは、或いはの意味

「.*」は、さっきと一緒ですね。

つまり「'右'か'左'で始まってその後'攻め'が入ってる文字列という意味になります。

それでは実行してみます。

「左ルー・ルー攻め派」「右ルー・ルー攻め派」が抽出されました!合ってますね^_^

まとめ

このように、正規表現を使うことで、「contains」や「Like」では表現しきれなかった文字列のマッチングを表現することが出来ちゃいます!

今までの苦労はなに!?って感じになりません?

良いでしょ?^_^