実現したいこと

・指定したキーワードを含む行を取得して別のスプレッドシートで参照したい
・参照先のシート内にあるセルA2~A6に記載している文字列を、参照元のC,D,E列内で探して、ヒットすればその行を参照したい

例：
A2 りんご
A3 みかん
A4 バナナ
A5 メロン
A6 いちご

・C,D,E列にA2～A6の果物の名前がどれか一つでも含まれていたら別のスプレッドシートでその行を参照する。

発生している問題・分からないこと

・QUERY関数のWhereで条件を指定しているが、セルA2～A6の文字列を含むはずの行がすべて表示されていない（表示されているものもある）
・whereの指定の仕方が間違っている可能性がある

該当のソースコード

▼①エラーは出ないが参照元のC列しか探せない

=ARRAYFORMULA(QUERY(
{TO_TEXT(IMPORTRANGE("参照元スプシのURL","参照先のシート名!A:E"))},
"select * where
Col3 = '"&'文字列指定シート'!A2&"' or
Col3 = '"&'文字列指定シート'!A3&"' or
Col3 = '"&'文字列指定シート'!A4&"' or
Col3 = '"&'文字列指定シート'!A5&"' or
Col3 = '"&'文字列指定シート'!A6&"'
"))

▼②エラーが出る（#VALUE!)関数 QUERY のパラメータ 2 のクエリ文字列を解析できません。

=ARRAYFORMULA(QUERY(
{TO_TEXT(IMPORTRANGE("参照元スプシのURL","参照先のシート名!A:E"))},
"select * where
Col3,Col4,Col5 = '"&'文字列指定シート'!A2&"' or
Col3,Col4,Col5 = '"&'文字列指定シート'!A3&"' or
Col3,Col4,Col5 = '"&'文字列指定シート'!A4&"' or
Col3,Col4,Col5 = '"&'文字列指定シート'!A5&"' or 
Col3,Col4,Col5 = '"&'文字列指定シート'!A6&"'
"))

▼③エラーは出ないがすべて探し出せていない

=ARRAYFORMULA(QUERY(
{TO_TEXT(IMPORTRANGE("参照元スプシのURL","参照先のシート名!A:E"))},
"select * where
Col3= '"&'文字列指定シート'!A2&"' or
Col4= '"&'文字列指定シート'!A2&"' or
Col5= '"&'文字列指定シート'!A2&"' or
Col3= '"&'文字列指定シート'!A3&"' or
Col4= '"&'文字列指定シート'!A3&"' or
Col5= '"&'文字列指定シート'!A3&"' or
Col3= '"&'文字列指定シート'!A4&"' or
Col4= '"&'文字列指定シート'!A4&"' or
Col5= '"&'文字列指定シート'!A4&"' or
Col3= '"&'文字列指定シート'!A5&"' or
Col4= '"&'文字列指定シート'!A5&"' or
Col5= '"&'文字列指定シート'!A5&"' or
Col3= '"&'文字列指定シート'!A6&"' or
Col4= '"&'文字列指定シート'!A6&"' or
Col5= '"&'文字列指定シート'!A6&"'
"))

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

whereで複数条件指定ができないかもしれないと思い、③で一つずつ指定してみたのですがA2~A6すべての文字列を拾うことができませんでした。

有識者の方いらっしゃいましたら、どうかご教授いただければ幸いです。
よろしくお願いいたします。

補足

空のシートで「"select * where Col3= '"&'文字列指定シート'!A2&"' or Col4= 略」を貼り付けてみて出た結果が以下です。

select * where

Col3= 'りんご' or
Col4= 'りんご' or
Col5= 'りんご' or
Col3= 'みかん' or
Col4= 'みかん' or
Col5= 'みかん' or
Col3= 'バナナ' or
Col4= 'バナナ' or
Col5= 'バナナ' or
Col3= 'メロン' or
Col4= 'メロン' or
Col5= 'メロン' or
Col3= 'いちご' or
Col4= 'いちご' or
Col5= 'いちご' or

条件取得はできているように見えます・・・

▼実現したいこと

・何が抽出できていて何が抽出できていないのか
→ ③の数式の抽出結果ですが、Col3（C列）の中しか探せていないようです。
Col3（C列）に該当キーワードがなく、Col4,Col5（D,E列）にキーワードが含まれている場合は無視されてしまっています。
（上記の画像だと行3や行6の場合は抽出できていません。）

### 2024/8/26 18:00 補足
pecmm様
補足にご回答いただきありがとうございます！

仰る通り不明点がある状況です。
whereの書き方が問題なのでしょうか。。

queryの書き方についてlikeを使って少し変更してみました。
また、後述しますが同じ状況をスプレッドシートで作成してみました。

▼試したコード

=ARRAYFORMULA(QUERY(
 {TO_TEXT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rzfj2umQkpvQHgSKjUcJRwvYiiDXNnSGsJzGArpQa9M/edit?gid=1122809158#gid=1122809158","シート!A:E"))},

"select * where

Col3 like '%"&'文字列指定シート'!A2&"%' or 
Col4 like '%"&'文字列指定シート'!A2&"%' or 
Col5 like '%"&'文字列指定シート'!A2&"%' or 
Col3 like '%"&'文字列指定シート'!A3&"%' or 
Col4 like '%"&'文字列指定シート'!A3&"%' or 
Col5 like '%"&'文字列指定シート'!A3&"%' or 
Col3 like '%"&'文字列指定シート'!A4&"%' or 
Col4 like '%"&'文字列指定シート'!A4&"%' or 
Col5 like '%"&'文字列指定シート'!A4&"%' or 
Col3 like '%"&'文字列指定シート'!A5&"%' or 
Col4 like '%"&'文字列指定シート'!A5&"%' or 
Col5 like '%"&'文字列指定シート'!A5&"%' or 
Col3 like '%"&'文字列指定シート'!A6&"%' or 
Col4 like '%"&'文字列指定シート'!A6&"%' or 
Col5 like '%"&'文字列指定シート'!A6&"%'
"))

▼結果
成功〇：行2、行5、行6、行7、行8
失敗×：行3の「トマトハウス」のみ参照されない

D列の「バナナ」は参照できているのに、E列の「りんご」が参照できておりません。
現在の環境を再現しましたので、差支えなければ見ていただけますでしょうか？
よろしくお願いいたします。

【参照元データ】
https://docs.google.com/spreadsheets/d/1rzfj2umQkpvQHgSKjUcJRwvYiiDXNnSGsJzGArpQa9M/edit?gid=1122809158#gid=1122809158

【参照先シート】
https://docs.google.com/spreadsheets/d/15nHGw1RNFnuFqyFwB-oEwpiio0CakovT6YlmQUH-Ss8/edit?gid=0#gid=0

行動規範の内容に同意します

回答2件

=LET(
data,IMPORTRANGE( "シートURL", "シート名!A:E"),
kywd,TEXTJOIN("|",1,'シート名'!A2:A),
FILTER(data,
REGEXMATCH(CHOOSECOLS(data,3),"(?i)"&kywd)+
REGEXMATCH(CHOOSECOLS(data,4),"(?i)"&kywd)+
REGEXMATCH(CHOOSECOLS(data,5),"(?i)"&kywd)))

投稿2024/08/29 12:33

nachou

総合スコア12

ベストアンサー

現状正確な回答は難しそうなので色々見直す方法を書いておきます

実際のデータ

▼③エラーは出ないがすべて探し出せていない

『すべて探し出せていない』というのは、具体的には

どのような元データに対して
どのような抽出条件で
どのような抽出結果を想定していて
実際にどのような結果でどう過不足があった

のでしょうか。
これらをちゃんと見直せばクエリ内のどの辺りに問題がありそうか見当をつけられる可能性があります。

例えば、実際のシートでは以下のような不正データが混ざっていた…という場合、現在の質問文では回答者は絶対に発見できません。
A2 りんご
A3 みかん
A4 バナナ ←誤って末尾にスペースが入っている
A5 メロン
A6 いちご
この場合は例えば「実際の抽出結果には、想定していた”バナナ”のデータが一切含まれていない」などから怪しい箇所を推測できるでしょう。

もしくは別の例で「Col4に抽出条件が含まれていても、実際の抽出結果に含まれていない」とかも原因箇所が特定しやすいです。

外部に出せないようなデータであるなら、上記を参考に自力で見直してみて下さい。

数式全体からクエリ文字列のみを表示させて確認

どこか適当なセルに
"select * where Col3= '"&'文字列指定シート'!A2&"' or Col4= 略
を貼り付けてみれば、想定していたクエリとズレがないかの確認ができるかもしれません。

クエリ文字列の一部を改行して見やすくしてみる

数式の&で文字列連結している部分は、セル内で改行（Alt+Enter）しても結果の文字列に影響がないので
これを利用して数式を見やすくできます。

例 before

"select * where Col3= '"&'文字列指定シート'!A2&"' or Col4= '"&'文字列指定シート'!A2&"' or Col5= '"&'文字列指定シート'!A2&"' or Col3= '"&'文字列指定シート'!A3&"' or Col4= '"&'文字列指定シート!A3&"' or Col5= '"&'文字列指定シート'!A3&"' or Col3= '"&'文字列指定シート'!A4&"' or Col4= '"&'文字列指定シート'!A4&"' or Col5= '"&'文字列指定シート'!A4&"' or Col3= '"&'文字列指定シート'!A5&"' or Col4= '"&'文字列指定シート'!A5&"' or Col5= '"&'文字列指定シート'!A5&"' or Col3= '"&'文字列指定シート'!A6&"' or Col4= '"&'文字列指定シート'!A6&"' or Col5= '"&'文字列指定シート'!A6&"'  "

例 aflter1…現状から改行のみ

"select * where Col3= '"&'文字列指定シート'!A2
&"' or Col4= '"&'文字列指定シート'!A2
&"' or Col5= '"&'文字列指定シート'!A2
&"' or Col3= '"&'文字列指定シート'!A3
&"' or Col4= '"&'文字列指定シート!A3
&"' or Col5= '"&'文字列指定シート'!A3
&"' or Col3= '"&'文字列指定シート'!A4
&"' or Col4= '"&'文字列指定シート'!A4
&"' or Col5= '"&'文字列指定シート'!A4
&"' or Col3= '"&'文字列指定シート'!A5
&"' or Col4= '"&'文字列指定シート'!A5
&"' or Col5= '"&'文字列指定シート'!A5
&"' or Col3= '"&'文字列指定シート'!A6
&"' or Col4= '"&'文字列指定シート'!A6
&"' or Col5= '"&'文字列指定シート'!A6
&"'  "

例 aflter2…クエリ構造を再現

"select * where "&
        "Col3= '"& '文字列指定シート'!A2 &"'"
&" or "&"Col4= '"& '文字列指定シート'!A2 &"'"
&" or "&"Col5= '"& '文字列指定シート'!A2 &"'"
&" or "&"Col3= '"& '文字列指定シート'!A3 &"'"
&" or "&"Col4= '"& '文字列指定シート'!A3 &"'"
&" or "&"Col5= '"& '文字列指定シート'!A3 &"'"
&" or "&"Col3= '"& '文字列指定シート'!A4 &"'"
&" or "&"Col4= '"& '文字列指定シート'!A4 &"'"
&" or "&"Col5= '"& '文字列指定シート'!A4 &"'"
&" or "&"Col3= '"& '文字列指定シート'!A5 &"'"
&" or "&"Col4= '"& '文字列指定シート'!A5 &"'"
&" or "&"Col5= '"& '文字列指定シート'!A5 &"'"
&" or "&"Col3= '"& '文字列指定シート'!A6 &"'"
&" or "&"Col4= '"& '文字列指定シート'!A6 &"'"
&" or "&"Col5= '"& '文字列指定シート'!A6 &"'"

※③について、『エラーは出ないが』とありますが
aflter1…現状から改行のみを見れば一目瞭然のように'が足りず、そのまま貼り付けると実際にはエラーが出ます。
質問文のクエリ文字列では一見 col3～col5 × 文字列指定シート!A2:A6 で組み合わせの漏れは無さそうですが
実際のクエリから何らかの書き換えがあると思われるので、問題があるかどうかは「回答者からはなにも判断できない」状況です。

本題と外れますがクエリ部分改善案

where 以降は col3～col5 × 文字列指定シート!A2:A6 の全組み合わせが必要ですが、これは直積を得るような自作関数を用意すれば生成しやすくなるかもしれません。

もしくはSQLでいうin句の代用としてmatches演算子が使えるので

gas:before
1"select * where "
2&       "Col3= '"& '文字列指定シート'!A2 &"'"
3&" or "&"Col3= '"& '文字列指定シート'!A3 &"'"
4&" or "&"Col3= '"& '文字列指定シート'!A4 &"'"
5&" or "&"Col3= '"& '文字列指定シート'!A5 &"'"
6&" or "&"Col3= '"& '文字列指定シート'!A6 &"'"

gas:after
1"select * where "
2&       "Col3 matches '"& join("|",'文字列指定シート'!A2:A6) &"'"

多分こんな感じで一部まとめることができます

補足について 2024/08/24 02:45 追記

クエリ文字列のみを表示させた結果について

クエリの末尾が "or" で終わっていて、文法的に不正なクエリに見えるのですが大丈夫なのでしょうか…？

実際のデータ＆クエリの修正について

本来やりたい内容としては、以下でよいでしょうか？

以下のいずれかを満たした列を抽出する
1. Col3（C列）のテキストが、該当キーワードのいずれかを含む
2. Col4（D列）のテキストが、該当キーワードのいずれかを含む
3. Col5（E列）のテキストが、該当キーワードのいずれかを含む

https://developers.google.com/chart/interactive/docs/querylanguage?sjid=15617819590153891286-AP&hl=ja#where

上記の公式ドキュメントの通り、演算子=は完全一致の検出用なので
部分一致を検出したいのであれば全てcontains演算子にそのまま置き換えるのが手っ取り早いでしょう。

（もし複雑な条件であれば like や matches が役に立ちますが、クエリがやや複雑になります）

現状の不可解な点

現時点でC列のみ部分一致で抽出できているのであれば、実際はこういう条件のクエリが書かれているはずです。

以下のいずれかを満たした列を抽出する
1. Col3（C列）のテキストが、該当キーワードのいずれかを含む
2. Col4（D列）のテキストが、該当キーワードのいずれかと完全一致する (またはCol4の条件が記述されていない)
3. Col5（E列）のテキストが、該当キーワードのいずれかを完全一致する (同上)

ですが、質問で挙げられているクエリでは全て=になっています。
本当にそのクエリ＆そのデータであれば、C列に完全一致する行が見当たらないので一行も抽出されないはずなのですが……。

というわけで、クエリを前述のcontainsとかに書き直して上手くいけばまあそれはそれで問題なしかもしれませんが
思った通りに行かない場合は、この不可解な現象についても解決しなければいけない可能性がなくもないです。

投稿2024/08/22 22:42

編集2024/08/23 17:49

pecmm

総合スコア617

nachou

2024/08/23 02:41

ご回答ありがとうございます。補足に追記したので、ご確認お願いいただけますでしょうか…！よろしくお願いいたします。

pecmm

2024/08/23 17:49

回答追記しました

nachou

2024/08/26 09:05

pecmm様ご回答ありがとうございます！ ### 2024/8/26 18:00 補足というタイトルより下に追記させていただきました。どうぞよろしくお願いいたします。

pecmm

2024/08/26 10:59

検索対象が1セル内に複数行なのが問題かもしれなくて to_textの後にsubstituteとかreplcce関数で改行文字を消せばうまくいく…のかもしれません。

nachou

2024/08/26 14:21

ありがとうございます！参照元データの改行文章のE3を試しに改行なしに編集してみたところ、無事参照されました。仰る通り改行されていることが取得できない原因のようです。。 SUBSTITUTE関数を挿入してみましたが、REF#!エラーとなり表示されません。書き方が違うのでしょうか。。 =ARRAYFORMULA(QUERY(C22 {TO_TEXT(SUBSTITUTE("シート!E:E",CHAR(10),"")),(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rzfj2umQkpvQHgSKjUcJRwvYiiDXNnSGsJzGArpQa9M/edit?gid=1122809158#gid=1122809158","シート!A:E"))}, "select * where Col3 like '%"&'文字列指定シート'!A2&"%' or Col4 like '%"&'文字列指定シート'!A2&"%' or Col5 like '%"&'文字列指定シート'!A2&"%' or Col3 like '%"&'文字列指定シート'!A3&"%' or Col4 like '%"&'文字列指定シート'!A3&"%' or Col5 like '%"&'文字列指定シート'!A3&"%' or Col3 like '%"&'文字列指定シート'!A4&"%' or Col4 like '%"&'文字列指定シート'!A4&"%' or Col5 like '%"&'文字列指定シート'!A4&"%' or Col3 like '%"&'文字列指定シート'!A5&"%' or Col4 like '%"&'文字列指定シート'!A5&"%' or Col5 like '%"&'文字列指定シート'!A5&"%' or Col3 like '%"&'文字列指定シート'!A6&"%' or Col4 like '%"&'文字列指定シート'!A6&"%' or Col5 like '%"&'文字列指定シート'!A6&"%' "))

pecmm

2024/08/27 03:46

あー、私の昨日のコメントがちょっと言葉足らずだったかもしれません。「to_textの後に」というのは関数適用の順番的に「後」であって IMPORTRANGEした結果 → { TO_TEXT → SUBSTITUTE } ※ARRAYFORMULAでRANGEの各セルを文字列化＆改行削除 ……という意図です。実際の数式としてはTO_TEXTの外側にさらに関数を記述することになりますね。ただ、コメントに書かれている数式は`C22`とかいうのが入り込んでいたり TO_TEXT関数の引数が大幅に書き換わってIMPORTRANGEが変な位置に移動していたり元の改行以外はうまくいっていた数式からかけ離れてしまっています。

nachou

2024/08/27 05:52

ありがとうございます。以下のように記述してみました。が、ERROR（数式の解析エラー）となります。。 =ARRAYFORMULA(SUBSTITUTE(QUERY( {TO_TEXT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rzfj2umQkpvQHgSKjUcJRwvYiiDXNnSGsJzGArpQa9M/edit?gid=1122809158#gid=1122809158","シート!A:E")}; "select * where Col3= '"&'文字列指定シート'!A2&"' or Col4= '"&'文字列指定シート'!A2&"' or Col5= '"&'文字列指定シート'!A2&"' or Col3= '"&'文字列指定シート'!A3&"' or Col4= '"&'文字列指定シート'!A3&"' or Col5= '"&'文字列指定シート'!A3&"' or Col3= '"&'文字列指定シート'!A4&"' or Col4= '"&'文字列指定シート'!A4&"' or Col5= '"&'文字列指定シート'!A4&"' or Col3= '"&'文字列指定シート'!A5&"' or Col4= '"&'文字列指定シート'!A5&"' or Col5= '"&'文字列指定シート'!A5&"' or Col3= '"&'文字列指定シート'!A6&"' or Col4= '"&'文字列指定シート'!A6&"' or Col5= '"&'文字列指定シート'!A6&"' ") "シート!E:E", CHAR(10), "" )))

nachou

2024/08/29 12:32

pecmm様以下のコードで解決いたしましたので、ここに共有します！ご回答とご協力いただき、ありがとうございました！ =LET( data,IMPORTRANGE( "シートURL", "シート名!A:E"), kywd,TEXTJOIN("|",1,'シート名'!A2:A), FILTER(data, REGEXMATCH(CHOOSECOLS(data,3),"(?i)"&kywd)+ REGEXMATCH(CHOOSECOLS(data,4),"(?i)"&kywd)+ REGEXMATCH(CHOOSECOLS(data,5),"(?i)"&kywd)))

行動規範の内容に同意します

あなたの回答