スプレッドシート 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] シート

kabu

「[MasterData]」の内容はこんな感じです。

kabu

一度コピペしてもらい、システムの構造を理解したのち、列や内容を更新していってもらうのが分かりやすいかな、と思います。

[検証]検索システムシート

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

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&"'",""
　　　　　　)
　　)

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

ちなみにQUERY関数については、この記事の一番下で解説していますので、確認したい方は覗いてみてください。

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

はい。次はこれ。
実はこれ、QUERY関数のクエリを指定している一つの文章です。
この赤字部分は結果的には次のクエリを指定しています。

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

これを出力するために、長々とした関数を組んでいるわけですね。

帰家

えっと・・・

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

▲これが、▼最終的にこれになる

"Where Col1 contains 'n' or Col2 contains 'n' or Col3 contains 'n'"

帰家

ってことで良いですか？

kabu

その通りです。
では、どうやって上のコードが下のコードになっていくか見ていきます。

=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列の）複数列番号を取得できます。
A、B、C列の場合は、「1」、「2」、「3」が返されます。
これにプラスして、先頭に「 or Col」、後尾に「 contains ‘」と「C2」セルと「‘」をつなげています。

=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&"'",""
　　　　　　)
　　)

すると、こうなりますね。

=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」など・・・ゴニョゴニョ