スプレッドシートの使い方

【スプレッドシート】Query関数のcontainsで指定文字列を含む行を表示

スプレッドシート関数の中で情報整理に役立つQuery関数。Query関数で「特定の文字列を含む行」を指定するときに「contains条件」を使うと便利です。

このページでは、スプレッドシートのQuery関数のcontains条件の使い方と、具体例を4つ紹介していきます。ぜひ参考にして、スプレッドシートでの数値管理にお役立てください。

Query関数のcontainsの基本的な使い方

スプレッドシートのQuery関数のcontainsを使う際の基本形は次のようになります。

=QUERY(範囲, "where 列 contains '文字列' ")

このQuery関数は「指定した列の中で、指定した文字列を含む行を出力」という指示になります。

より具体的にしたものが次の例です。

=QUERY(A11:D17, "where B contains '書き方' ",true)

これは「A11:D17の範囲の行の中で、B列に’書き方’という文字列を含む行を表示する」という指示になります。実際のスプレッドシートでは次の図のように表示されます。

query関数のcontainsで文字列指定

Query関数のcontainsの使い方例4つ

ここからは、Query関数とcontains条件を使って、Googleスプレッドシート上で複雑な情報の条件表示を実例とともに勉強していきましょう。

①指定した文字列を含む:列 contains 文字列

こちらは先程の「containsの使い方」で説明したので解説は省略します。

=QUERY(範囲, "where 列 contains '文字列' ")
=QUERY(A11:D17, "where B contains '書き方' ",true)

query関数のcontainsで文字列指定

②指定した複数の文字列を含む:指定 AND 指定

次は指定する文字列を2つに増やしてみます。ここでは例えば、文字列Aかつ文字列BというAND指定をします。

=QUERY(範囲, "where 列 contains '文字列A' AND 列 contains '文字列B' ",true)

より具体的に「A11:D17の範囲の行で、B列に’書き方’と’転職’を含む行を表示する」を指示する、containsを使ったquery関数は次のようになります。

=QUERY(A11:D17, "where B contains '書き方' AND B contains '転職' ",true)

query関数のcontainsで文字列を複数指定

③指定した複数の文字列のいずれか含む:指定 OR 指定

次は指定するを、文字列Aまたは文字列BというOR指定をします。

=QUERY(範囲, "where 列 contains '文字列A' OR 列 contains '文字列B' ",true)

より具体的に「A11:D17の範囲の行で、B列に’書き方’または’転職’を含む行を表示する」を指示する、containsを使ったquery関数は次のようになります。

=QUERY(A11:D17, "where B contains '書き方' OR B contains '転職' ",true)

query関数のcontainsで文字列を複数指定条件or

④指定した文字列を含まない:not 列 contains 文字列

最後に、「指定した文字列を含まない行を表示せよ」という指示を、containsを使ったQuery関数を説明します。

=QUERY(範囲, "where not 列 contains '文字列'")

より具体的に「A11:D17の範囲の行で、B列に’書き方’を含まない行を表示せよ」というquery関数が次のようになります。

=QUERY(A11:D17,"where not B contains '書き方'")

query関数のcontainsで含めない文字列指定

QUERY関数の使い方」まとめ
QUERY関数の使い方使用頻度使用例
オプションOrder byで昇順・降順を指定する★★QUERY("範囲" "order by 列 asc")
オプションgroup byで同じ値をグルーピングして集計する★★★QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")
オプションpivotを使って、group byをさらにグルーピングして表示するQUERY(範囲, "select 列A, 集計関数(列A) where group by 列A pivot 列B")
オプションlabelを使って、出力される列のラベルを変更するQUERY(範囲, "label 列 '名前' ")
オプションformatを使って、出力される値の表示形式を変更するQUERY(範囲, "format 列 '表示形式' ")
オプションlimitを使って、表示する行数に制限をかけるQUERY(範囲, "limit 数")
オプションoffsetを使って表示データを上からスキップする方法QUERY(範囲, "offset 数")
オプションoptionsを使う
演算子containsを使って、指定文字列を含む行を表示する★★QUERY(範囲, "where 列 contains '検索文字列' "
演算子starts withを使って、行の先頭文字列に条件を指定するQUERY(範囲, "where 列 starts with '検索文字列' "
演算子ends withを使って、行の後尾文字列に条件を指定するQUERY(範囲, "where 列 ends with '検索文字列' "
演算子likeで複雑な条件を指定するQUERY(範囲,"where 列 like '検索文字列' ")
演算子★★★QUERY(範囲,"WHERE 列 matches '正規表現' ")
テクニックwhereの条件で空白を指定・除外する★★QUERY(範囲, "where 列 is not null")
テクニックQUERY関数にSUM関数を使う★★SUM(QUERY(範囲, "where 条件")
テクニックwhereの条件に日付を指定する★★★QUERY(範囲,"where 列 = date '"& TEXT(セル,"YYYY-MM-DD") &"'")
テクニックQUERY関数内でimportrange関数を使って、別シートを参照する★★★QUERY(importrange(シートID,参照範囲), "where 条件")
テクニック複数範囲・複数シートを結合する★★QUERY({IMPORTRANGE(シートURL,シート範囲);IMPORTRANGE(シートURL,シート範囲)},"WHERE 条件")
テクニックwhereの条件を複数指定する★★★QUERY("範囲", "where 条件A or 条件B and 条件C")