【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句で時刻データを条件にして行を抽出する
  7. 【QUERY関数】where句でand,orを使用して複数条件を指定する
  8. 【QUERY関数】order by句で抽出結果を昇順・降順ソートする
  9. 【QUERY関数】group by句とcount関数で列のデータ数をカウントする
  10. 【QUERY関数】group by句とsum関数で列の値の合計値を求める
  11. 【QUERY関数】group by句とavg関数で列の値の平均値を求める
  12. 【QUERY関数】group by句とmax・min関数で列の最大値・最小値を求める
  13. 【QUERY関数】group by句で複数の列をグループ化して集計する

  投稿者プロフィール

もり
もりITライター
『もりさんのプログラミング手帳』の管理人です。

退屈なことはプログラミングで片づけよう!
自らの事務職経験を活かし、事務作業をとことんラクにできるITネタを発信していきます。

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

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

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