【Googleスプレッドシート】部門別売上を行・列・値で組み替える!集計切替えの操作

部門別の売上データを行と列と値で自由に組み替えたいと思ったことはありませんか。ピボットテーブルやQUERY関数を使えば、集計の軸を簡単に切り替えられます。本記事では、行・列・値を自在に入れ替えて集計する具体的な操作方法を解説します。これを読めば、動的なレポート作成がスムーズになるでしょう。

行・列・値の組み替えとは

行・列・値の組み替えとは、集計テーブルの軸を自由に入れ替える操作のことです。例えば、部門別売上データで、行に「部門」、列に「月」、値に「売上合計」を置いた表があるとします。この行と列を入れ替えれば、月ごとの部門別売上に変わります。また、値フィールドに「売上平均」を追加すれば、平均も同時に表示できます。この機能を活用することで、一枚のデータから多角的な分析が可能になります。スプレッドシートでは、ピボットテーブルとQUERY関数の2つの方法で実現できます。

ピボットテーブルで集計を切り替える手順

基本のピボットテーブルを作成する

1. データ範囲を選択する
   部門、月、売上の列を含むセル範囲を選択します。
2. メニューからピボットテーブルを挿入する
   「データ」メニューから「ピボットテーブル」を選び、新しいシートに作成します。
3. 行と列と値を設定する
   ピボットテーブルエディタの「行」に部門、「列」に月、「値」に売上を追加します。売上が数値の場合は自動で合計になります。

行と列を入れ替える

1. エディタを開く
   ピボットテーブルを選択して右側のエディタを表示します。
2. フィールドをドラッグする
   「行」にある部門を「列」の枠にドラッグし、「列」にある月を「行」にドラッグします。これで行と列が入れ替わります。
3. 結果を確認する
   テーブルが自動更新され、月が行方向、部門が列方向に並びます。

値の集計方法を変更する

1. 値フィールドの設定を開く
   エディタの「値」に追加した「売上」の横にある歯車アイコンをクリックします。
2. 集計方法を選ぶ
   「集計方法」から「平均」「最大値」「最小値」「カウント」など目的の方法を選択します。
3. 設定を適用する
   「OK」をクリックすると、値の集計方法が変更され、ピボットテーブルに反映されます。

QUERY関数で集計を切り替える手順

QUERY関数の基本構文を書く

1. セルにQUERY関数を入力する
   =QUERY(範囲, “クエリ”, 見出し)の形式で入力します。範囲はデータ全体を指定します。
2. 基本的なselect句を書く
   例: =QUERY(A1:C100, “select A, B, sum(C) group by A, B”) これで部門(A列)と月(B列)ごとの売上合計が出力されます。
3. 見出しを設定する
   第3引数に1を指定すると、見出し行を考慮します。

行・列・値を指定する

1. pivot句を使って列を指定する
   例: =QUERY(A1:C100, “select A, sum(C) group by A pivot B”) これで、行に部門、列に月、値に売上合計のクロス集計表が作成されます。
2. select句で複数列を扱う
   select A, B, sum(C) のように複数の列や集計を指定できます。
3. 行と列を入れ替えるには
   pivot句で指定する列を変更します。例えばpivot Aとすれば、部門が列方向になります。

集計方法を変更する

1. 集計関数を変える
   sumの代わりにavg, max, min, countなどを使います。例: =QUERY(A1:C100, “select A, avg(C) group by A pivot B”)
2. 複数の集計を同時に表示する
   select A, sum(C), avg(C) group by A pivot B とすれば、合計と平均が並びます。
3. 条件を追加する
   where句で特定の部門だけを集計することもできます。

集計切り替えでよくあるミスと注意点

ピボットテーブルのデータ更新が反映されない

元データを追加してもピボットテーブルに反映されない場合があります。その原因は、ピボットテーブルの範囲が固定されているからです。解決するには、元データをテーブル形式に変換するか、範囲を広めに取っておきます。また、ピボットテーブルを右クリックして「更新」を実行するのも有効です。

QUERY関数の範囲がずれる

QUERY関数で指定した範囲が絶対参照になっていないと、行を追加したときに範囲がずれることがあります。これを防ぐには、範囲を$A$1:$C$100のように絶対参照にするか、データ範囲を名前付き範囲に設定します。範囲を広めに取り、空行を許容することもできます。

日付のグループ化が思うようにいかない

月ごとに集計したい場合、日付列がそのまま年月日形式だと、QUERY関数のpivot句で月ごとにまとめられません。日付列をテキスト形式の年月に変換するか、ピボットテーブルでは日付グループ化機能を使います。ピボットテーブルなら、日付フィールドを右クリックして「月ごとにグループ化」を選べば自動で月単位になります。

ピボットテーブルとQUERY関数の比較

ピボットテーブルとQUERY関数を使い分ければ、部門別売上の集計軸を自在に切り替えられます。まずはシンプルなピボットテーブルから試し、必要に応じてQUERY関数で高度な集計を行ってみてください。動的なレポート作成が効率化されるでしょう。