はじめに
クエリ関数を使って集計しよう！
クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。
このように、指定範囲から条件を指定してデータを取り出す関数は、EXCELでもお馴染みのデータベース関数各種がありますが、このクエリ関数を使うことによって、より柔軟に複雑な条件を簡単に指定することが出来るようになります。
EXCELにはない関数なので、せっかくスプレッドシートを使うなら覚えておきたい関数ですね(^^)
今回は、クエリ関数を使って集計する方法を紹介させていただきます！
これまでのシリーズはこちら
集計関数
クエリ関数シリーズではこれまで、データからの抽出を中心に紹介してきました。今回は抽出に加えて、実務で重要な「集計」について開設したいと思います。
集計といえば、スプレッドシートやEXCELでおなじみの「sum」や「count」「average」ですが、クエリ関数も内部に集計関数という形で持っており、クエリ式の中に入れ込むことで集計をすることができます。
集計関数の一覧
| 関数 | 意味 |
|---|---|
| sum() | 合計を求める |
| avg() | 平均を求める |
| count() | 個数を求める |
| max() | 最大値を求める |
| min() | 最小値求める |
【サンプル】こちらのサンプルで試しながら進めたいと思います。
※政府の総合窓口「e-Stat」から取ってきた都道府県の人口データを少し加工したものです。
「表示用」シートのA1セルにクエリ関数を入力していきます。
単純集計
まずは単純に集計をしていきます。
構文
=QUERY(範囲,”select 集計関数(列)”,[見出し])
基本的な構文は上記のようになっています。[見出し]は何列目を見出しにするかを指定するオプションで、数字で指定します。省略した場合は「1」になります。
それでは、試しにやってみましょう。
次の数式を、「集計用」シートのA1セルに入力します。
※今回は、sum関数を選んでいます。
=QUERY(‘人口データ’!A6:E,”select sum(E)”)
※[見出しは]省略しているので「1」が入ってるのと同じです。
A1セルに「sum 人口」と見出しが
A2セルに「1256285(元データの人口列の合計)」と表示された
今回、見出しは省略しているので、合計しているE列の1行目の項目と、関数の「sum」が集計後の見出しとしてA1セルに、集計の結果はA2セルに表示されます。
[見出し]に「0」と「2」を入れた数式を、それぞれ「C１セル」「E１セル」に入力してみます。
「見出し」と「集計の結果」が変わりました。
[見出し]を「0」に設定した場合は、単純に元データのE列の項目「人口」がなくなります。
[見出し]に「2」を設定した場合は、データ範囲の2行目までを[見出し]とみなすので、「人口 2537」と最初の2537という数値までが見出しとみなされています。集計にも2537が入りませんので、1256285から2537を引いた、1253748が結果として表示されています。
他の集計関数も同じようにすることで使えます。
「group by」「pivot」グループごとに分けて集計する
ここまでは、ただ単純に指定した1列を計算するだけの方法を説明しました。しかしこんな程度のことであれば普通にSUM関数とかを使えばいいだけです。実務役に立つのは、項目ごとのグループに分けて集計を行いたいときですよね。ここでは、そんなグループごとに集計を行う「group by」と「pivot」の使い方を見ていきます。
まずは「group by」です。
構文
=QUERY(範囲,”select 基準列, 集計関数(列) group by 基準列”)
「select」でグループの基準になる列を選びます。集計関数の後に「group by」と続け、グループ分けをしたい基準の列を指定します。
サンプルで試しましょう。
次の数式を、「表示用」シートのA1セルに入力して下さい。
=QUERY(‘人口データ’!A6:E,”select A, sum(E) group by A”)
A列の実施年ごとに、E列の人口の合計(sum)を表示させる数式です。
図のように、実施年ごとの人口合計が表示されます。
「order by」を組み合わせて降順や昇順で表示させることも出来ます。
「group by」に続けて「order by」を追加します。
=QUERY(‘人口データ’!A6:E,”select A, sum(E) group by A order by sum(E) asc”)
合計した結果を「昇順」で並べ替えて表示します。「order by」の基準列は、合計した結果になるので「sum(E)」と集計関数付きで指定します。
また、「label」を使うことで、合計した結果の見出しを変更することも出来ます。
=QUERY(‘人口データ’!A6:E,”select A, sum(E) group by A order by sum(E) asc label sum(E) ‘人口の合計'”)
「order by」に更に続けて、「label」を追加します。こちらの列指定も、合計した結果の列になるので、「sum(E)」と集計関数付きで指定します。指定した列の後に、「’」シングルクォ―テーションで囲って、新しく見出しにしたい文字列を指定します。
「group by」には、複数の列を指定することも可能です。
「実施年」に加えて、「男女別」でも分けてみます。
次の数式を入力してみます。
=QUERY(‘人口データ’!A6:E,”select A,B, sum(E) group by A,B”)
※「,」カンマのついてる位置に中止してくださいね。
別れましたね^_^
最後に、列方向にグルーピングしたい場合です。
この場合は「pivot」を使います。
構文
=QUERY(範囲,”select 基準列,集計関数(列) group by 基準列 pivot 基準列“)
「group by」は行方向、「pivot」は列方向へのグルーピングの基準列を指定します。
試しましょう。
=QUERY(‘人口データ’!A6:E,”select A, sum(E) group by A pivot B”)
「group by：行方向」に「実施年」のA列、「pivot：列方向」に「男女別」のB列を指定します。
うまく行きました^_^
まとめ
クエリ関数についてシリーズで紹介してきました。あとは色々組み合わせてみることで、結構なんでもできちゃいますよ。思った形になるように繰り返しやってみて慣れていって下さい^_^
コメント