Google スプレッドシート備忘録（2022/09/03update）

query関数

query関数の中のselect文の中でセルを参照させる方法

=query(data!A:J,"select A,B,C where A =  ' " & A10 & " ' order by B",1)

 =if(C10="注文",query(data!A:J,"select A,B,C,D,E where A =  ' " & A10 & " ' order by B",1),query(data!A:J,"select A,B,C,D,E where I = '"&C10&"' AND A =  ' " & A10 & " '  order by B",1))

参照させたいセル番地を、シングルクォート、ダブルクォート、アンパサンド、でくくる。

半角スペースはあってもいいけどいらない。あったほうが分かりやすい。

query関数の中のselect文の中で日付の入ったセルを参照させる方法

A10 2022-10-25

=query(data!A:J,"select A,B,C where A =  ' " & text(A10, "YYYY-MM-DD" ) & " '  ",1)

テキスト関数で整形してあげないとエラーになる

exact関数の結果をqueryで参照する方法

=exact(C2,U2)

とした結果が 'TRUE' のものを抜き出す。

=query('シート'!A:AB, "select C,U where AA!=true and AB=true")

文字列として'TRUE'としてはだめ。

他のスプレッドシートからvlookupで探す方法（importrangeで読み込みqueryで整形し、vlookupでさがす）

=vlookup ( B5,query ( importrange ( " https://docs.google.com/spreadsheets/d/1RiI72uJHpYZViKRixx_SNQ/edit " , " 'シート1'!A:AK " ) , " select Col25,Col13 " , 2 ) , 2 , false ) )

ポイントは、Queryのselect文はAとかBとかではなくて、読み込んだ列番号を Colをつけて指定する（例ではCol25 ）。

同じものをqueryだけで書くと、

=query ( importrange ( " https://docs.google.com/spreadsheets/d/1RiI72uJHpYZViKRixx_SNQ/edit " , " 'シート1'!A:AK " ) , " select Col25,Col13 where Col13=  ' " & B5 & " ' , 0 ) 

 

となる。

vlookupと違い結果が複数ある時は全件だせるのがメリット。

queryの特徴でタイトルがでてしまうので、不要であれば0を指定する。

デメリットは指定するB5を配列形式でB5:Bとかで書くことができないこと。arrayformulaと組み合わせて使うことができないので、組み合わせるときは上のvlookup方式で書く。

〆(ﾟ▽ﾟ*) ﾒﾓﾒﾓ

=query(ArrayFormula(split(FLATTEN(F1:F1100&","&G1:J1100),",")),"select Col2,Col1 where Col2 is not null",4)

=query(A:B,"select A,count(A) where count(A)>2 group by B")

=query(query(A:K,"Select D,Count(D) Group by D"),"Where Col2>2")