【Googleスプレッドシート】売上集計をQUERYで動的レポート化!数式1つの集計テクニック

売上データを毎月手作業で集計していると、作業時間が増えてミスも発生しやすいものです。特に、期間や条件を変えて何度も集計し直す必要がある場合、効率が大きく低下します。GoogleスプレッドシートのQUERY関数を使えば、1つの数式でこれらの集計を自動化できます。この記事では、QUERY関数を使って売上データを動的に集計する方法を、実践的な手順とともに解説します。

QUERY関数が売上集計にもたらすメリット

QUERY関数は、データベース言語SQLに似た構文でスプレッドシートのデータを操作できる強力な関数です。売上集計では、月別・商品別・地域別などのグループ化、条件によるフィルタリング、並べ替え、計算列の追加などを1つの数式で実行できます。一般的なSUMIFSやピボットテーブルと比較して、数式が自動更新されるため元データを変更するたびに集計がリアルタイムで反映される点が大きな利点です。また、QUERYは配列を返すため、ARRAYFORMULAと組み合わせなくても複数行の結果を自動展開します。

QUERY関数を使うための前提条件として、データが表形式(1行目が見出し行、2行目以降がデータ)で整理されている必要があります。また、QUERY内で使う列はアルファベット(A, B, C…)で指定するか、列番号(Col1, Col2…)で指定します。日本語の列名を使う場合はバッククォートで囲むと安全です。これらの基本を押さえた上で、実際の集計手順を見ていきましょう。

売上データをQUERYで集計する基本手順

ここでは、以下のような売上データを例に説明します。データはシート「売上明細」のA列からD列(日付、商品名、担当者、金額)にあり、1行目は見出し行です。QUERY数式は別のシート「集計レポート」に記述します。

1. 月別売上合計を算出する
   集計レポートのA1セルに以下の数式を入力します。
   =QUERY(売上明細!A:D, "SELECT MONTH(A), SUM(D) WHERE A IS NOT NULL GROUP BY MONTH(A) LABEL MONTH(A) '月', SUM(D) '月別売上'", 1)
   この数式は、日付列から月を抽出し、金額列を合計します。第3引数の1は、元データに1行目が見出し行であることを指定します。結果として月と売上合計の2列が出力されます。
2. 商品別売上を集計する
   商品ごとの売上合計を得るには、GROUP BYに商品名列を指定します。
   =QUERY(売上明細!A:D, "SELECT B, SUM(D) WHERE B IS NOT NULL GROUP BY B LABEL B '商品名', SUM(D) '商品別売上'", 1)
   WHERE句で空白行を除外しているため、商品名が未入力の行は無視されます。
3. 条件を指定してフィルタリングする
   特定の担当者の売上だけを集計する場合は、WHERE句に条件を追加します。
   =QUERY(売上明細!A:D, "SELECT MONTH(A), SUM(D) WHERE C = '田中' GROUP BY MONTH(A) LABEL MONTH(A) '月', SUM(D) '田中さん売上'", 1)
   条件値はシングルクォーテーションで囲みます。セル参照を使いたい場合は、アンパサンドで結合します(例:WHERE C = ‘”&E1&”‘)。
4. クロス集計(PIVOT)で商品別月別売上を表示する
   行に月、列に商品を配置したクロス集計表を作成するには、PIVOT句を使います。
   =QUERY(売上明細!A:D, "SELECT MONTH(A), SUM(D) WHERE A IS NOT NULL GROUP BY MONTH(A) PIVOT B", 1)
   PIVOT句を指定するだけで、商品名ごとに列が分割され、各月の商品別売上が表示されます。ただし、商品の種類が多いと列数が増える点に注意してください。

QUERY集計を動的レポート化する応用テクニック

セル参照で期間や条件を切り替える

QUERY内でセル参照を使うと、集計条件を動的に変更できるようになります。例えば、E1セルに開始月、E2セルに終了月を入力し、その間のデータだけを集計するには、以下のように記述します。

1. 日付範囲の指定
   =QUERY(売上明細!A:D, "SELECT MONTH(A), SUM(D) WHERE A >= DATE '"&TEXT(E1,"yyyy-mm-dd")&"' AND A <= DATE '"&TEXT(E2,"yyyy-mm-dd")&"' GROUP BY MONTH(A) LABEL MONTH(A) '月', SUM(D) '売上'", 1)
   DATE関数を使うことで、セルに入力された日付を条件に含めます。TEXT関数で日付をyyyy-mm-dd形式に変換します。
2. 複数条件の組み合わせ
   担当者も同時に指定したい場合は、さらにWHERE句に条件を追加します。
   =QUERY(売上明細!A:D, "SELECT MONTH(A), SUM(D) WHERE C = '"&F1&"' AND A >= DATE '"&TEXT(E1,"yyyy-mm-dd")&"' AND A <= DATE '"&TEXT(E2,"yyyy-mm-dd")&"' GROUP BY MONTH(A)", 1)
   F1セルに担当者名を入力することで、リアルタイムに集計結果が変わります。

QUERY結果をグラフで可視化する

QUERYで出力した集計表は、そのままグラフのデータ範囲に指定できます。グラフを追加する手順は以下の通りです。

1. グラフを作成する
   QUERY数式が入力されたセル範囲(例:A1:B13)を選択し、メニューから「挿入」→「グラフ」を選びます。
2. グラフの種類を変更する
   グラフエディタの「設定」タブで、グラフの種類を縦棒グラフや折れ線グラフに変更します。QUERYの結果が更新されると、グラフも自動的に更新されます。
3. フィルタ条件をグラフに連動させる
   先述のセル参照を使った条件入力セルをグラフのタイトルなどに反映させると、より見やすいレポートになります。例えば、=F1&" さんの月別売上" といった数式をグラフタイトルに設定できます。

QUERY関数を使う際の注意点とよくあるエラー

列指定のミスで「#VALUE!」エラーが発生する

QUERYのSELECT句やWHERE句で指定する列は、データ範囲内の列番号(AならCol1、BならCol2)またはアルファベット(A, B...)で指定します。見出し行を省略してデータ範囲を指定すると、列指定がずれてエラーになります。必ず第3引数に1を指定して見出し行を明示するか、データ範囲に見出し行を含めてください。

空白セルが原因で集計結果がおかしくなる

データに空白行や空白セルがあると、QUERYの結果に予期しない値が混入することがあります。特にGROUP BYを使う場合、空白行がグループに含まれないようにWHERE句で除外するのが安全です。例えば「WHERE A IS NOT NULL」と指定すれば、日付が空の行を集計対象から外せます。

PIVOT句で列数が多くなりすぎる

PIVOT句はユニークな値の数だけ列を生成します。商品名の種類が数百にも及ぶ場合、列数が膨大になりスプレッドシートのパフォーマンスに影響を与えます。そのような場合は、QUERYの前にフィルタで商品を絞り込むか、別の方法(ピボットテーブル)を検討しましょう。また、PIVOT結果の列順は不定なので、見出しを固定したい場合は別途ソート用の列を用意する必要があります。

QUERY関数とSUMIFS関数の比較

このように、QUERY関数は1つの数式で複雑な集計を実現できるため、売上集計の動的レポート作成に特に有効です。一方、SUMIFSは単一条件の集計や他関数との組み合わせでは使いやすい面もあります。状況に応じて使い分けると良いでしょう。

まとめ

QUERY関数を使えば、売上データの月別集計、商品別集計、条件指定フィルタリング、クロス集計までを1つの数式で動的に生成できます。セル参照を活用すれば、入力条件を変えるだけで集計結果が自動更新されるレポートが完成します。さらにグラフと連携させることで、視覚的なダッシュボードとしても活用できます。次に、複数シートやインポート関数(IMPORTRANGE)と組み合わせて、複数ファイルをまたいだ統合レポートにも挑戦してみてください。QUERYの応用力はスプレッドシート業務を大幅に効率化してくれるでしょう。