あぷつう.com

弱ニートだから楽しいことだけやっていく

*

グーグルスプレッドシートでカテゴリー別に抽出したいときに便利なクエリ関数

   

グーグルスプレッドシートでカテゴリー毎に別のシートに展開したいなーっと考えて、なんか便利な関数ないかと探していたら見つけたのが、クエリ関数。

クエリは、SQLという言語で書くことになりますが、グーグルスプレッドシートではExcelの関数並みに簡単にできます。もちろん、複雑に書くことでより集計などもしやすくなります。
Excelでは、関数として使うことはできませんが、グーグルスプレッドシートでは関数と同じように簡単に使うことができます。

どのように使うか?

クエリ関数をどのように使うかについて簡単に説明。

例えば、Excelではデータ抽出をする際はフィルターを使用すると思います。
フィルターを押して、チェックを外して、そのデータをコピーして別のシートに貼り付けという作業をよくすると思います。

グーグルスプレッドシートではクエリを使用すれば、もっと簡単に目的のデータのみを取得することができます。

今回使うクエリはSELECTWHEREの二つになります。
まあ、このままではよくわからないので実例を挙げておきます。

下記のデータでカテゴリーに貯蓄消費があります。
Webアプリケーション009

ALLのシートの貯蓄のデータをID・Task・Pointだけ別のシートに出したい場合は、このように表記しています。

=Query(ALL!A:E, “select A,C,D where E = ‘貯蓄'”)

selectで列を指定して、whereで抽出する文字を指定しています。もう少し詳しい内容については後程説明させていただきます

関数を一回書くだけで簡単にすべてのデータを返してくれるのでかなり楽にできちゃいます。
Webアプリケーション010

クエリ関数の書き方

簡単にクエリの書き方について説明していきます。

書き方は以下のようになります。

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

項目 説明 記入例
データ 参照するデータ A:E,’シート名’!A:E,A1:E3 etc
クエリ 処理内容 SELECT,WHERE,ORDER BY,GROUP BY etc
見出し 見出し入れるか -1,0

最終的にはこのようになります。

=Query(範囲,“select 列 where 条件列 = 条件”, 見出し)

見出しは、デフォルトだと表示することになりますので、特に変更がない場合は書く必要はありません。

SELECT(表示する列の選択)

SELECTでは、全ての列の指定か列を指定するのかの2つだけです。

説明 記入例
列を指定 select B,C,D
全ての列を指定 select *
=Query(ALL!A:E, “select A,C,D”)

例えば上記のように実行した場合は、A,C,Dの列が表示されます。
Webアプリケーション011

WHERE(条件に一致する行)

WHEREでは、指定された条件に一致する行のみを返すために使用されます。
比較演算子を使用することでExcelのフィルター機能のチェックと同じようなことができます。

比較演算子は以下のものが使用できます。

比較演算子 説明 記入例
< より小さい where D < 30
> より大きい where D > 30
<= 以下 where D <= 30
>= 以上 where D >= 30
= 等しい where D = 30,where E = ‘文字’
<>,!= 等しくない where D <> 30,where E != ‘文字’
is null 空白 where D is null
is not null 空白でない where D is not null
and 2つの条件を結合し、両方の条件が真 where D >= 40 and D <= 50
or 2つの条件のうち、どちらか一方が真 where D = 40 or D = 50
like ‘文字’ パターンに一致 where E like ‘%文字%’,where E like ‘文字%’
=Query(ALL!A:E, “select A,C,D where D = 40”)

例えば上記のように実行した場合は、Dが40と等しいデータが表示されます。

Webアプリケーション012

like演算子は結構使えます。

%文字%で文字が一文字でも含まれていれば表示されます。

=Query(ALL!A:E, “select * where E like ‘%貯%'”)

例えば上記のように実行した場合は、貯の文字が含まれている行を返します。

Webアプリケーション013

前方一致の場合は、文字%
後方一致の場合は、%文字

まとめ

今回使用したクエリはSELECTWHEREの二つになりますが、他にももっと便利なクエリはあります。

もし、クエリを勉強したい場合は、SQLの勉強をするとさらに理解が深まります。
個人的にオススメしたい本は、スッキリわかるSQL入門が初学者向けでオススメです。
SQLはデータベースの操作のための言語なので覚えておくとかなり便利にできます。

 - program