Googleスプレッドシート QUERY関数の条件(WHERE)での空白セルの扱い方

Google SpreadsheetのIMPORTRANGE関数で他のシートを参照させ、QUERY関数で表示させる条件をつける時、空白のセルがあるものを除外・空白があるものだけ表示させる方法のメモ。

QUERY
QUERY(データ, クエリ, [見出し])

QUERY - ドキュメント エディタ ヘルプ

基本的にQUERY関数の第二引数のクエリ部分だけを書いています。

空白セルのがある行を除外したい

例: A列のセルが空白の行は場合は表示させない

select * where Col1 != ''

※ A列が日付・数字場合上記では上手く取得できない

IS NOT NULLを使う

select * where Col1 IS NOT NULL

※ A列が日付・数字でも文字列でも上手く取得できる

空白のセルがある行だけを表示したい

例: A列のセルが空白の行だけ場合は表示させる

select * where Col1 = ''

※ A列が日付・数字の場合上記では上手く取得できない

IS NULLを使う

select * where Col1 IS NULL

※ A列が日付・数字でも文字列でも上手く取得できる

空のセルの判定をするのはIS NOT NULL、IS NULLを使った方が確実だなぁと思いました。 そして、数字になっている列がのセルに---とか数字以外が入力されているものは空白扱いになるようですね。。。
そして、空白セルに全角スペースを入れるヤツは滅びるべし！
٩(๑`^´๑)۶༄༅༄༅༄༅༄༅)`Д´)ｱ

---

[参考]

• QUERY - ドキュメント エディタ ヘルプ
• Googleスプレッドシート 他のシートから特定のセルを条件付きで取ってきて表示させたい - かもメモ
• Googleスプレッドシート QUERY関数の条件(WHERE)に日付を使いたい。 - かもメモ
• Googleスプレッドシート Query関数 変数でキーワード検索(LIKE)したい - かもメモ

TVアニメ/データカードダス 「アイカツ!」4thシーズン挿入歌ミニアルバム「タイトル未定」 (デジタルミュージックキャンペーン対象商品: 400円クーポン)

Googleスプレッドシート QUERY関数の条件(WHERE)に日付を使いたい。

スプレッドシートのIMPORTRANGE関数とQUERY関数の組み合わせ便利ですね。
データがまとまったシートを作成しておいて、必要な情報をだけを表示させるのに使っています。
今回 日付を条件にしてデータを取得しようとしてハマったのでメモします。

例えばA列に日付がはいっているシートを取得し日付が2010年1月15日以降のデータを取得したいとします。A列はCol1です
下記のような書き方をしても#VALUE!になってしまい取得できません。

SELECT * WHERE Col1 > '2010-01-15'

日付の入ったセルをWHERE文で使うには date 'YYYY-MM-DD'の形式にする

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

Examples:

where salary >= 600
where dept != 'Eng' and date '2005-01-21' < hireDate
where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null

A列が2010年1月15日以降のデータを取得したい場合は下記のようにします。

SELECT * WHERE Col1 > date '2010-01-15'

条件の日付を変数にしたい場合

"& A1 &"のフォーマットでセルの値をqueryに埋め込めるので、これを参考に

SELECT * WHERE Col1 > date '"& A1 &"'

としても#VALUE!になってしまい取得できませんでしたξ(Ծ‸Ծ)ξ
セルに入力された日付を使う場合は明示的にYYYY-MM-DDの形にフォーマットしたテキストにしてあげる必要がありました。 TEXT()を使うことでフォーマットしたテキストにすることができます。

TEXT(数値, 表示形式)

• 数値 - 変換する番号、日付、時刻です。
• 表示形式 - 変換する数値の表示形式を、二重引用符で囲って指定します。

TEXT - Docs Editors Help

クエリを下記のように修正します。
条件の日付をセルに入力された日時にする方法

SELECT * WHERE Col1 > date '"& TEXT(A1, "YYYY-MM-DD") &"'

これでは意図したとおりに取得することができました ζ*'ヮ')ζ

年だけ・月だけ・日にちだけを条件にしたい

年月日ではなく、年だけ、月だけ、日にちだけを条件にする場合は日付の入っているセルをYEAR()、MONTH()、DAY()で変換してあげれば簡単です。

年で検索

SELECT * WHERE YEAR(Col1) = 2010'

検索条件を変数にする

SELECT * WHERE YEAR(Col1) = "& A1 &"'

検索条件がYYYY-MM-DDの形式になっている

SELECT * WHERE YEAR(Col1) = "& TEXT(A1, "YYYY") &"'

月で検索

WHERE文中のMONTH()は1月が0になるので注意が必要です。

month()
Note: the months are 0-based, so the function returns 0 for January, 1 for February, etc.

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

SELECT * WHERE MONTH(Col1) + 1 = 12'

検索条件を変数にする

SELECT * WHERE MONTH(Col1) + 1 = "& A1 &"'

検索条件がYYYY-MM-DDの形式になっている

SELECT * WHERE MONTH(Col1) + 1 = "& TEXT(A1, "MM") &"'

TEXT(A1, "M")でもOK

日で検索

SELECT * WHERE DAY(Col1) = 18'

検索条件を変数にする

SELECT * WHERE DAY(Col1) = "& A1 &"'

検索条件がYYYY-MM-DDの形式になっている

SELECT * WHERE DAY(Col1) = "& TEXT(A1, "DD") &"'

TEXT(A1, "D")でもOK

　
サンプル: ソビエト連邦の指導者リスト - Google スプレッドシート 　

---

[参考]

• QUERY - ドキュメント エディタ ヘルプ
• Query Language Reference (Version 0.7)  |  Charts  |  Google Developers
• TEXT - Docs Editors Help
• Googleスプレッドシート 他のシートから特定のセルを条件付きで取ってきて表示させたい - かもメモ
• Googleスプレッドシート Query関数 変数でキーワード検索(LIKE)したい - かもメモ

スマホアプリ「アイカツ!フォトonステージ!!」シングルシリーズ01「カメレオントーク★」

Googleスプレッドシート Query関数 変数でキーワード検索(LIKE)したい

前回 他のスプレッドシートからデータを取ってきて表示するセルの指定と、WHEREでの表示させるデータの条件を付ける方法を書きました。この時は検索条件が=とか>とかでしたが、例えば住所を入れてあるセルから新宿区というキーワードがるものだけ取ってきたいとかって場合もあります。こんな時はsqlと同じようにLIKEで検索条件を指定することができるようです。

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

Where like - A text search that supports two wildcards: %, which matches zero or more characters of any kind, and _ (underscore), which matches any one character. This is similar to the SQL LIKE operator. Example: where name like fre% matches 'fre', 'fred', and 'freddy'.

where name like fre% という様な書き方でキーワード検索ができるようです。
キーワードの最後に%を追加する形でキーワード部分をセルの値から取得するようにすればOKなので、次のようになります

where name like '"& A1 &"%'

試したのが日本語だったからかもしれませんが、likeの条件全体を'(シングルコーテーション)で囲まないと%を追加した文字列として認識されませんでした。

他のスプレッドシートからデータを取ってきて〜とする場合は次のような感じにすればOKです。
ex: 取ってきたシートのA, C, D のセルを表示させ Dのセルの値を今のシートのA1の値でlike検索する

= query(importrange("spreadsheet_key", "シート1!A1:Z"), "select Col1, Col3, Col4  where Col4 like '"& A1 &"%'")

ね。カンタンでしょ？ (スプレッドシート すごい!!)

---

[参考]

• Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

なもり百合姫表紙画集 truth