スプレッドシート QUERY 検索・抽出システム範囲全体からフリー入力キーワードで検索

帰家

kabuさん、スプレッドシートのデータを「フリーキーワード」で検索・抽出する！
みたいなこと出来ないでしょうか？！

帰家

Ctrl+Fでシート内を探すのは用途と違うし、
「フィルタ」機能では、列ごとで指定が面倒だし、

帰家

なんて言うか・・・
もっと簡単に”一つのキーワードでデータ全体から探せる！”
みたいなことがしたいです！！

kabu

おー
なんかいつになく熱いですね。

kabu

じゃあ今回は
① フリーキーワードから検索・抽出するシステム
② 作成方法・数式の解説
③ 数式に使用した関数の説明
ってことで話をしていきましょう。

フリーキーワードから検索・抽出するシステム
作成方法・数式の解説
- 数式に使用した関数の説明
まとめ

フリーキーワードから検索・抽出するシステム

kabu

システムとは書きましたが、そんな大それたものではありません。
スプレッドシート内にあるマスターデータを参照し、「検索キーワード」を含んだ項目を抜粋するという仕組みです。

こんなマスターデータがあるとします。

別のシートに、マスターデータを参照しているデータがあります。
ここには、C2セルに「検索キーワード」入力欄を設けてあります。
ここに入力されたキーワードを含んだ項目をマスターデータから抽出するって仕組みです。

今は「検索キーワード」には何も入っていないので、マスターデータがまるまる出力されていますが・・・

ここに、「1」と入力すると、数字の1が入っている項目（行）が抽出されました。
今回このキーワードに引っかかっているのはA列の番号ですね。

続いて「会社」と入力してみます。
すると、B列で「会社」と名のつくものが拾われてきます。

なんでもいいんですが、「n」とでも入れてみます。
するとC列で「n」と入っているのもが抽出されていますね。

kabu

値を変えたのはC2の「検索キーワード」セルのみですが、複数列に対してフィルタ機能が効いています。
これがスプレッドシートのデフォルトである「フィルタ」機能との違いですね。

帰家

おお・・・すごい！
ちゃんと全体から検索されていますね！

【SALE】ロジクールワイヤレスマウストラックボール無線 M575Sa Bluetooth Unifying トラックボールマウスワイヤレスマウス windows mac iPad 国内正規品 1年間無償保証

Anker PowerCore 10000 (10000mAh 大容量モバイルバッテリー)【PSE技術基準適合/PowerIQ搭載】 iPhone&Android対応 (ブラック)

【Amazon.co.jp限定】SanDisk SSD 外付け 1TB USB3.2Gen2 読出最大1050MB/秒防滴防塵 SDSSDE61-1T00-GH25 エクストリームポータブルSSD V2 Win Mac PS4 PS5 エコパッケージ 5年保証

Anker 547 USB-C ハブ (7-in-2, for MacBook) Thunderbolt 4 100W USB PD対応 4K HDMIポート microSD & SDカードスロット 5Gbps USB-Cポート USB-Aポート搭載高速データ転送

作成方法・数式の解説

kabu

・「[検証]検索システム」
・「[MasterData]」
って二つのシートを作っています。
「[MasterData]」の内容はこんな感じ。

「[検証]検索システム」シートは、上部A1:C3に見た目通りのテキストを入力していて、A4セルにのみ数式が入っています。

数式は次のとおり。

=QUERY('[MasterData]'!A3:C,"Where Col1 contains '"&C2&"'"&
SUBSTITUTE(CONCATENATE(ARRAYFORMULA(" or Col"&COLUMN('[MasterData]'!A3:C)&
" contains '"&C2&"'"))," or Col1 contains '"&C2&"'",""))

帰家

・・・なんだかすごく難しそうですね汗

kabu

分かりやすいよう、分解して解説していきますね。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　SUBSTITUTE(
　　　　　　　　　　CONCATENATE(
　　　　　　　　　　　　　　ARRAYFORMULA(" or Col"&
　　　　　　　　　　　　　　　　　　COLUMN('[MasterData]'!A3:C)&
　　　　　　　　　　　　　　　　　　" contains '"&C2&"'"
　　　　　　　　　　　　　　)
　　　　　　　　　　)," or Col1 contains '"&C2&"'",""
　　　　　　)
　　)

まずはこれ。これはいいですかね。
元データとして「[MasterData]」シートのA3:Cを参照しています。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　SUBSTITUTE(
　　　　　　　　　　CONCATENATE(
　　　　　　　　　　　　　　ARRAYFORMULA(" or Col"&
　　　　　　　　　　　　　　　　　　COLUMN('[MasterData]'!A3:C)&
　　　　　　　　　　　　　　　　　　" contains '"&C2&"'"
　　　　　　　　　　　　　　)
　　　　　　　　　　)," or Col1 contains '"&C2&"'",""
　　　　　　)
　　)

次は一番内側の数式を見ていきます。
これは、「COLUMN」関数で「[MasterData]」シートの列数を取得しています。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　SUBSTITUTE(
　　　　　　　　　　CONCATENATE(
　　　　　　　　　　　　　　ARRAYFORMULA(" or Col"&
　　　　　　　　　　　　　　　　　　COLUMN('[MasterData]'!A3:C)&
　　　　　　　　　　　　　　　　　　" contains '"&C2&"'"
　　　　　　　　　　　　　　)
　　　　　　　　　　)," or Col1 contains '"&C2&"'",""
　　　　　　)
　　)

一つ上の「ARRAYFORMULA」関数によって、「COLUMN」関数がA列、B列、C列の複数列数を取得できます。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　SUBSTITUTE(
　　　　　　　　　　CONCATENATE(
　　　　　　　　　　　　　　" or Col1 contains '"&C2&"'"
　　　　　　　　　　　　　　" or Col2 contains '"&C2&"'"
　　　　　　　　　　　　　　" or Col3 contains '"&C2&"'"
　　　　　　　　　　)," or Col1 contains '"&C2&"'",""
　　　　　　)
　　)

当然A列の列数は「1」、B列は「2」、C列は「3」になりますね。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　SUBSTITUTE(
　　　　　　　　　　" or Col1 contains '"&C2&"' or Col2 contains '"&C2&"' or Col3 contains '"&C2&"'"
　　　　　　　　　　," or Col1 contains '"&C2&"'",""
　　　　　　)
　　)

これを「CONCATENATE」関数で1つのテキストにつなげています。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　SUBSTITUTE(
　　　　　　　　　　" or Col1 contains '"&C2&"' or Col2 contains '"&C2&"' or Col3 contains '"&C2&"'"
　　　　　　　　　　," or Col1 contains '"&C2&"'",""
　　　　　　)
　　)

その上で「SUBSTITUTE」を使い、テキストの中の「” or Col1 contains ‘”&C2&”‘”」を「””」に置き換えています。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '"&C2&"'"&
　　　　　　" or Col2 contains '"&C2&"' or Col3 contains '"&C2&"'"
　　)

置き換え後はこんな内容になっています。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains 'n' or Col2 contains 'n' or Col3 contains 'n'"
　　)

セル指定の「C2」のままだと分かりづらいので「n」にでも置き換えてみます。

それでこの式が何をしているかと言うと、「Col1」、「Col2」、「Col3」の中で、「n」が含まれているものを抽出(contains)しろって指示になります。
この書き方によって、全ての列で検索がひっかけられる仕組みができるわけです。

帰家

あのう・・・
関数がどういう動きをしているのかはなんとなく分かったんですけど・・・
最初になぜ「COLUMN」で列数をとってきたんですか？？

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains 'n' or Col2 contains 'n' or Col3 contains 'n'"
　　)

帰家

この数式を書くのなら、途中に使っていた「SUBSTITUTE」も「CONCATENATE」も「ARRAYFORMULA」も別にいらないんじゃないかな？って

kabu

その通りですね。

帰家

えー？？

SUBSTITUTE(CONCATENATE(ARRAYFORMULA(" or Col"&COLUMN('[MasterData]'!A3:C)&
" contains '"&C2&"'"))," or Col1 contains '"&C2&"'","")

今回書いているこの長い数式は、下のテキストを作りたいがためのものになります。

 or Col2 contains 'n' or Col3 contains 'n'"

じゃあなぜ、わざわざこんな長い数式を書いているかと言うと・・・

kabu

データの参照範囲が変わった際に、変更する箇所を少なくするためですね。

例えば、データの範囲が「‘[MasterData]’!A3:C」＞「‘[MasterData]’!A3:D」に変わったとします。

=QUERY('[MasterData]'!A3:D,
　　　　　　"Where Col1 contains '"&C2&"' or Col2 contains '"&C2&
　　　　　　"' or Col3 contains '"&C2&"' or Col4 contains '"&C2&"'"
　　)

すると、「SUBSTITUTE」以降を使わなかった数式の場合、これらの箇所を変更しなければなりません。

=QUERY('[MasterData]'!A3:D,
　　　　"Where Col1 contains '"&C2&"'"&SUBSTITUTE(CONCATENATE(
　　　　ARRAYFORMULA(" or Col"&COLUMN('[MasterData]'!A3:D)&
　　　　" contains '"&C2&"'"))," or Col1 contains '"&C2&"'","")
　　)

長い方の数式の場合、変更箇所はここ。

更にデータの範囲が「‘[MasterData]’!A3:D」＞「‘[MasterData]’!A3:F」に変わったとします。

=QUERY('[MasterData]'!A3:F,
　　　　　　"Where Col1 contains '"&C2&"' or Col2 contains '"&C2&
　　　　　　"' or Col3 contains '"&C2&"' or Col4 contains '"&C2&
   "' or Col5 contains '"&C2&"' or Col6 contains '"&C2&"'"
　　)

短い方の数式では、これらの箇所を変更し、

=QUERY('[MasterData]'!A3:F,
　　　　"Where Col1 contains '"&C2&"'"&SUBSTITUTE(CONCATENATE(
　　　　ARRAYFORMULA(" or Col"&COLUMN('[MasterData]'!A3:F)&
　　　　" contains '"&C2&"'"))," or Col1 contains '"&C2&"'","")
　　)

長い方の数式では、変更箇所はここのみです。

kabu

どうでしょう？
長い方の数式の方が編集が簡単、分かりやすく見えませんか？

帰家

そういう意味でこの数式が組まれていたんですね！
確かに簡単かもしれませんね！！
でも私は短い方が良いです。

kabu

・・・。

数式に使用した関数の説明

kabu

今回使用した関数について、Aiによる解説も載せておきますね。

QUERY

QUERY関数は、Google スプレッドシートで提供されている強力な関数の一つで、指定された範囲からデータをクエリ（問い合わせ）し、条件に一致するデータを抽出または操作することができます。この関数は、Google Visualization API Query Languageを使用して、データをフィルタリング、ソート、集計するための複雑なクエリを記述することが可能です。

QUERY(データ範囲, クエリ文字列, [ヘッダー数])

データ範囲: クエリを実行するデータの範囲を指定します。
クエリ文字列: 実行するクエリを文字列として指定します。この言語はSQLに似ており、SELECT, WHERE, ORDER BY, LIMITなどの命令を使用してデータを操作します。
ヘッダー数 (オプション): データ範囲の最初の何行をヘッダーとして扱うかを指定します。この引数を省略すると、Google スプレッドシートが自動でヘッダーを判断します。

QUERY関数は、大量のデータから特定の情報を抽出したい場合や、データを特定の条件でフィルタリングして表示したい場合、またはデータをソートや集計したい場合などに非常に便利です。たとえば、特定の条件を満たす行のみを抽出したり、特定の列のデータを基に集計を行ったりする場合に使用できます。

SUBSTITUTE

SUBSTITUTE関数は、テキスト内の特定の文字列を別の文字列で置き換えます。たとえば、あるセルの内容を変更したいが、元のテキストは保持したい場合に便利です。式の形式は以下の通りです：

SUBSTITUTE(テキスト, 古い文字列, 新しい文字列, [出現回数])

出現回数はオプションで、指定した場合はその回数目に出現する古い文字列のみが新しい文字列で置き換えられます。

CONCATENATE

CONCATENATE関数は、複数のテキスト文字列を一つに結合します。例えば、名前と姓を別々のセルに分けて入力している場合、この関数を使ってフルネームを作成することができます。

CONCATENATE(テキスト1, [テキスト2, ...])

ARRAYFORMULA

ARRAYFORMULA関数は、範囲や配列に対して一括で式を適用します。通常の関数がセルごとに適用されるのに対し、ARRAYFORMULAを使うと、一度に複数のセルに同じ操作を適用できます。

ARRAYFORMULA(式)

COLUMN

COLUMN関数は、参照されたセルや範囲の列番号を返します。特定のセルの位置情報が必要な場合に使います。

COLUMN([セル参照])

できるYoutuber式 Googleスプレッドシート現場の教科書できるYouTuber式シリーズ

ブラウザだけで学べる Googleスプレッドシートプログラミング入門

図解！ Googleスプレッドシートのツボとコツがゼッタイにわかる本

頭がよくなるスプレッドシート問題集入門編

まとめ

kabu

今回は
① フリーキーワードから検索・抽出するシステム
② 作成方法・数式の解説
③ 数式に使用した関数の説明
について話をしました。

帰家

スプレッドシートには、デフォルトで「フィルタ」機能がありますが、
それとは違う、”フリーキーワードでデータ全体を検索・抽出”できるシステムが出来ましたね ♪

kabu

「QUERY」関数は他の関数と大きく仕様が違い、扱いづらいと思う方もいるかと思いますが、どの関数よりも自由度が高く、僕がもしスプレッドシート内で最強関数を選ぶとしたら、間違いなくこの「QUERY」関数を選びますね。この関数は「FILTER」や「VLOOKUP」、「XLOOKUP」など・・・ゴニョゴニョ