売り上げた商品の管理のために、
「日付＋商品名」の用にシンプルな形で
データ化することは珍しくありません。
このような日付と商品のデータがあると、
結局、その日にその商品は何個売れたのかを集計したくなります。
実は、Google スプレッドシートでは、
関数を一発叩くだけで、下の図のように、
日付とカテゴリーの二軸に分けた集計ができてしまいます。
QUERY関数
今回利用するのはQUERY関数です。
QUERY関数は、データに対する処理を、
SQLのようなデータベースに対するクエリ形式で記述する関数です。
データの集計や、細かい条件指定など、
柔軟に処理を記述できるので、
クエリ一発で望みの操作を実現できることも多々あります。
ここでは、このQUERY関数を使って、
2つの軸に分けたカウント処理を実現します。
基本的な使い方
QUERY関数は、
QUERY(データの範囲,クエリ)
という形で、QUERYの対象となるデータの範囲と、
データに対する処理を記述するクエリを与えて利用します。
クエリでは、
- select文で取得するデータの指定
- where文で条件の指定
のように、データに対する操作を構文に従って記述します。
このクエリを適切に指定すれば、
望みの操作が実現できるというわけです。
行数をカウントする
目的は、日付とカテゴリー別の二軸に分けてカウントすることですが、
段階を踏んでクエリを完成させていきましょう。
まずは、行数のカウントからです。
=QUERY(A:B,"select count(A)")
上の表の場合データはA列とB列にあるため、
範囲はA:Bを指定しています。
select文は取り出すデータを指定していて、
count(A)でA列のデータ数をカウントしています。
今の場合、48行のデータがあるので、結果は48となっています。
日付でまとめてカウントする
続いて、日付で集計してみましょう。
=QUERY(A:B,"select A,count(A) group by A")
今度は、select文でカウントした値だけではなく、
日付を表すA列そのものも取得するようにしています。
こうすることで、どの日付に対応しているかを確認しやすくなります。
肝心のデータをまとめる操作はgroup byで記述します。
ここでは、A列が同じもの同士、つまり同じ日付をグループ化して、
グループ化されたものについて、A列とそのカウント数が表示されています。
日付とカテゴリー別にカウントする
それでは本題の日付とカテゴリーの二軸に分けて、
カウントするクエリーを完成させましょう。
=QUERY(A:B,"select A,count(A) group by A pivot B")
pivotが重要な役割を果たしています。
pivotで指定されたB列について、
Aでまとめられたグループがさらに展開されています。
空白を排除する
上のクエリで日付とカテゴリーに分けてカウントをするという
目的は達成しましたが、
日付の一番上や、カテゴリーの一番左の欄に
空白ができています。
これは、空白の日付や空白のカテゴリーも集計の対象に含めているためで、
ちょっと不恰好に見えてしまいます。
もう少しこだわるなら、空白は集計の対象から外すように、
whereで条件指定をするといいでしょう。
=QUERY(A:B,"select A,count(A) where A is not null and B is not null group by A pivot B")
クエリは長々しくなってしまいましたが、
これで空白は排除できました。
まとめ
日付とカテゴリーを記録したデータから、
日付別・カテゴリー別の二軸に分けたデータ数のカウントを、
QUERY関数で行いました。
同様の操作は、COUNTIFS関数を使って、
行と列のタイトルに一致する数を数えるなども考えられますが、
タイトルを用意したりCOUNTIFSを表にコピペする操作を考えると、
QUERY関数一つで集計表を作る方がスマートな感じがします。