Qeruy関数を使ってデータ抽出をしたい
Query関数は、2024年7月時点でプレッドシートでしか使えない関数です。
先日、Query関数でキーワードを含む文字列を抽出するようにしたのですが、
where句で指定した文言を含んでいるにもかかわらず、抽出できない問題が発生しました。
以下はサンプルです。
C2 ~ C4の3セルに検索キーワードを3つ指定できます。
入力したキーワードが、右のマスタの商品説明(H列)に該当するものがあると、
商品ID(E列)と商品名(F列)を抽出してくれます。
// B6に入力してます
=QUERY(E2:H,"select E,F where H matches '.*" & TEXTJOIN(".*|.*", TRUE, C2:C4) & ".*'" )
▼キーワードを何も指定していない場合
▼「回復」を指定した場合
▼「回復」「爆発」を指定した場合
キーワードに該当するはずなの抽出できない！
▼「回復」を指定した場合
の画像をもう一度見てください。
スニッカーズの商品説明に「回復」が含まれているのに、Query関数で拾えていません！
原因はぱっと見でわかると思いますが、「セル内改行がある」ためです。
当時、何百行とあるデータに対してQuery関数を使っていたため、デバックしても気づくのに2時間ぐらいかかりました...
セル内改行が悪さをしている
これは恐らく仕様なのですが、ネット上に情報がなくどうすればいいのか悩みました。
というより、セル内改行がない部分は正常に取れてきているというのがたちが悪いです。
この世の中でQuery関数は結構使われているものだと思いますが、この仕様を知らないと抜け漏れが発生します。
じゃあどうすれば正しくとれるのかというと、Query関数内でセル内改行を削除するだけです。
データのセル内改行を削除する必要はありません。
=QUERY(
ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(E2:H, CHAR(10), ""), CHAR(13), "")),
"select Col1,Col2 where Col4 matches '.*" & TEXTJOIN(".*|.*", TRUE, C2:C4) & ".*'" )
当たり前と思う人もいるかと思いますが、
改行ってな、3種類あんねん（\r、\n、\r\n）
これをすべて削除します
それが
SUBSTITUTE(SUBSTITUTE(E2:H, CHAR(10), ""), CHAR(13), "")
の部分です。
ただし、ARRAYFORMULAを使わないとすべてのセルに対して処理が行われませんので、一緒につかって下さい。
そしてもう一つ注意があります。
抽出対象のセルです。
先ほどはH列を条件に、E,Fを抽出するという書き方でしたが、
select Col1,Col2 where Col4
Col（カラム）を使う必要があります。
SUBSTITUTEで元データを破壊（加工）してしまっているため、
EやFという列番号でデータを指定できなくなってしまいます。
そのため、Col1やCol2のように番号で列を指定する必要があります。
▼修正後に「回復」で検索
無事、スニッカーズも抽出できました！
よかったね！スニッカーズ！