【QUERY関数】where句とlike演算子を使用して指定の文字を含む行を抽出する


QUERY関数4アイキャッチ

みなさんこんにちは!
もり(@moripro3)です。

GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています!

前回は、where句を使用して行を抽出する方法を紹介しました。

【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第三回目は、where句と比較演算子を使用して、指定条件に一致する行を抽出する方法を紹介しています。

比較演算子を使用して、「○○と一致する行」「数字のxx以上の行」などを抽出しました。

実務では「○○と一致」という検索だけでなく、「○○を含む」「先頭が○○で始まる」などの部分的な検索をすることもありますよね。

そこで今回は、部分的なキーワード検索ができるlike演算子を紹介します。行の抽出に役立つこと間違いなしです!

前回のおさらい:where句で行を抽出する

where句とは、データの中から、指定条件に一致する行を抽出する句です。
前回は、比較演算子(=, !=, <>,<=, <, >, >=)で条件を指定しました。

where比較演算子

データの中から、B列がOA機器に一致する行を抽出するQUERY関数の書き方です。条件に文字列を指定する場合は、文字列をシングルクォートで囲みます(’OA機器’)

query3-15

完全一致と部分一致とは

普段、私たちがインターネットなどでデータ検索する場面を考えてみましょう。
「検索キーワード」を指定すると、検索キーワードに一致する結果が取得できます。

このとき、「キーワードに一致」の「一致」には、下記の2種類があります。

  1. 完全一致 … 完全に一致する
  2. 部分一致 … 部分的に一致する

前回の記事で紹介した比較演算子のイコール(=)が、完全一致の検索です。B列の値がOA機器であるもの、つまり、”OA機器”と完全に一致する行が抽出されました。

部分一致の検索とは、「検索対象のデータ」に対して、「検索キーワード」が部分的に一致する行を抽出する方法です。部分的に一致するとは、○○で始まる、○○を含む、などのことです。

今回の記事で紹介するlike演算子が、QUERY関数で部分一致の検索をする演算子です。

部分一致の検索ができるlike演算子

like演算子とは、部分一致の検索をする演算子です。「部分一致の検索」は、「あいまいな検索」と呼ぶこともあります。

where句での使い方がこちらです。

where 列 like パターン

部分一致の3パターン

部分一致には代表的な下記の3パターンがあります。

  1. 前方一致(○○で始まる)
  2. 後方一致(○○で終わる)
  3. 中間一致(1,2以外のパターン)

‘モリ’という単語を例に、「完全一致」と「部分一致の3パターン」をまとめた一覧です。

パターン 説明 クエリの書き方 抽出例
完全一致 モリと一致 = ‘モリ’ モリ
部分一致(前方) モリで始まる like ‘モリ%’ モリアーティ
部分一致(後方) モリで終わる like ‘%モリ’ モリ
部分一致(中間) モリを含む like ‘%モリ%’ ナカモリアキナ

 

後方一致の「’モリ’で終わる」で詳しく解説します。

後方一致「モリで終わる」とは、[任意の文字列] + [モリ] というパターンです。[任意の文字列]は、0文字以上の適当な文字と考えておきましょう。

like演算子のポイントは、[任意の文字]を記号の%(パーセント)で表すことです。
[任意の文字列] + [モリ] = %モリ と記述します。

たとえば、[タ] + [モリ] = [タモリ] という単語が、このパターンに一致します。

※任意の文字列は0文字以上なので、0文字(文字が無い)も含みます。つまり、[] + [モリ] = [モリ] も後方一致検索でヒットします。

それでは、次の項からlike演算子を使用して行の抽出をしていきます!

前方一致(○○から始まる)で検索する

使用するサンプルデータがこちらです。シート名が「機器購入リスト」、データ範囲が「A列~E列」なので、QUERY関数の第1引数は ‘機器購入リスト’!A:E と指定します。

query4-1-2

このデータから、C列の商品名が[モニタ]から始まる行を抽出します。

[モニタ]という文字の後ろに[任意の文字]が続くパターンです。
[モニタ] + [任意の文字] で、[任意の文字]は記号の%で表すので、 モニタ% と記述します。

QUERY関数の条件指定に文字列を指定する場合、文字列全体をシングルクォートで囲みます。任意の文字を表す%もシングルクォートの中に入れて、’モニタ%’ を指定します。

query4-3

[モニタ]から始まる2行が抽出されました。

後方一致(○○で終わる)で検索する

C列の購入商品名が[モニタ]で終わる行を抽出します。

[任意の文字列]の後ろに[モニタ]が続くパターンです。
[任意の文字列] + [モニタ] なので、 %モニタ と表します。

query4-4

中間一致で検索する

中間一致とは、前方一致・後方一致のどちらでもないパターンです。

2つの例を紹介します。

中間一致①(○○を含む)

C列の購入商品名に[モニタ]を含む行を抽出します。

[モニタ] が [任意の文字列] に挟まれているパターンです。
[任意の文字列] + [モニタ] + [任意の文字列] なので、 %モニタ% と表します。

query4-2

ここでのポイントは、前方一致・後方一致のパターンも抽出されていることです。それは、[任意の文字列]は0文字以上の適当な文字なので、空文字も含むためです。

つまり、’%モニタ%’ と指定することで、下記の4通りをまとめて抽出することができます。

  1. [任意の文字列] + [モニタ] + [任意の文字列] … デュアルモニタアーム
  2. [任意の文字列] + [モニタ] + [] … 21インチモニタ
  3. [] + [モニタ] + [任意の文字列] … モニター27インチ
  4. [] + [モニタ] + [] … モニタ(サンプルデータに無)

中間一致②(○○で始まり○○で終わる)

つぎに、B列「申請部署」に条件を指定して行の抽出をします。
もう一度全体のデータをみてみましょう。

query4-1-2

このデータから、下記の3部署が申請した機器の一覧を抽出します。

  1. 開発1課
  2. 開発2課
  3. 開発3課

部署名がすべて、[開発]で始まるので、前方一致を使って’開発%’と指定すると・・・

query4-6

不要な[開発統括部]も抽出されてしまいます。

そこで、条件を加えます。[開発]で始まり[課]で終わると指定すれば目的の3部署のみ取得できますね。

[開発] + [任意の文字列] + [課] = 開発%課 と表します。

query4-5

目的の3部署のみを抽出することができました。

まとめ

今回の記事では、where句とlike演算子を使用して、部分一致の検索で行を抽出する方法を紹介しました。

  • like演算子を使うと、部分一致の検索(あいまいな検索)ができる
    • 前方一致(○○で始まる)
    • 後方一致(○○で終わる)
    • 中間一致(前方一致と後方一致以外のパターン)
  • 任意の文字列は記号の%で表す

部分一致の検索が使えると、部署コードが9から始まる、科目コードにxxxを含む、などのあいまいな検索で威力を発揮します!ぜひ使用してみてください。

次回も引き続きwhere句です。これまで、条件指定に使っていたのは数値・文字列のみですが、実務では「日付」を扱うことも多いですよね。

今日期限のタスク一覧・今月末が期限のタスク一覧を抽出する、など、where句では日付を指定した抽出も可能です!

【QUERY関数】where句で日付データを条件にして行を抽出する
GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第五回目は、where句の条件に日付を指定する方法です。今日期限のタスク一覧・今月期限のタスク一覧の抽出方法を紹介しています。

連載目次:GoogleスプレッドシートQUERY関数をマスターしよう

スプレッドシートのQUERY関数を使って、データ抽出・集計を効率化する方法を紹介しています。

  1. スプレッドシートのQUERY関数を使う最初の一歩!クエリを理解する
  2. QUERY関数の基本!別シートのデータからselect句で列を取得する方法
  3. 【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
  4. 【QUERY関数】where句とlike演算子を使用して指定の文字を含む行を抽出する
  5. 【QUERY関数】where句で日付データを条件にして行を抽出する
  6. 【QUERY関数】where句で時刻データを条件にして行を抽出する

  投稿者プロフィール

もり
もりシステムエンジニア・Webライター
「ラクするために全力を尽くす」をモットーに日々勉強。退屈なことはプログラミングで片づけよう。
事務作業をとことんラクにできるITネタを発信していきます。

お気軽にフォローしてくださいね!

もりの詳しいプロフィールはこちら
▽▽▽

コメント

タイトルとURLをコピーしました